MySQL(三)全面优化 - 硬件/参数/OS/表设计

硬件设备的优化

影响数据库最大的性能问题就是磁盘I/O 为提高数据库的IOPS性能,可使用SSD或PCIE-SSD高速磁盘设备

数据库配置参数的优化

  • innodb_buffer_pool_size:50%~80%
  • innode_flush_log_at_trx_commit和sync_binlog:redo log刷新和binlog刷新。设置为1,数据库的双一概念,保证主从架构中数据的一致性
  • innodb_max_dirty_pages_pct:脏页占比达值触发刷新到磁盘,建议25%~50%
  • innodb_io_capacity:InnoDB后台进程最大的I/O性能指标,默认值200.SSD可调整为5k~2w,PCIE-SSD可调整为5w左右
  • innodb_data_file_path = ibdata:1G:autoextend:默认10M,一般设置为1GB
  • long_query_time:0.1s~0.5s,纪录执行较慢的SQL
  • binlog_format:建议binlog的记录格式为row模式
  • interactive_timeout,wait_timeout:交互等待时间和非交互等待时间,值一致,建议300~500s,默认8小时
  • max_connections:数据库最大连接数
  • innodb_log_file_size:redo log值太大,实例恢复时消耗大量时间,值太小造成日志切换频繁
  • general log:全量日志建议关闭

Linux操作系统层面的优化

  • I/O调度:选择deadline或noop,不要使用cfg
cat /sys/block/vda/queue/scheduler
[mq-deadline] kyber none
  • 文件系统的选择:xfs推荐,ext4其次,ext3基本废弃
  • 调整内核参数 vm.swappiness:0~100; 内存快用尽时,释放内存或倾向使用swap; 0-禁用,可能会导致OOM,60-默认值,100-积极使用swap,影响性能 vm.dirty_blackground_ratio:指定系统缓存脏页达到系统内存百分比时,触发pdflush/flush/kdmflush等后台回写进程将脏页异步刷入磁盘。建议小于10 vm.dirty_ratio:指定系统缓存脏页达到系统内存百分比时,系统不得不开始处理脏页。此过程很多进程需要去处理文件I/O而出现阻塞现象。建议小于20
查看:cat /proc/sys/vm/swappiness
修改:cat /etc/sysctl.conf

数据库表设计的优化

表结构的优化
  • 命名用小写字母,采用下划线“_”分割 lower_case_table_names参数区分表名大小写,默认0:大小写敏感;1:代码不敏感,以小写存储
  • 显示为表创建一个使用自增列INT或BIGINT类型作为主键,保证写入顺序是自增的,和B+tree叶子节点分裂顺序一致。写入更加高效,TPS性能更高,存储效率最高
  • 用int来存储金钱,让int单位为分,不用四舍五入,数值更精确
  • 用int来存储IPv4字段,通过转换函数inet_aton和inet_ntoa来实现
SELECT INET_ATON('127.0.0.1');
SELECT INET_NTOA(2130706433);
  • 日期时间用datatime:可用范围比timestamp大,物理存储上仅多占一个字节的空间
  • 性别sex字段、状态status字段等用tinyint
  • 主要业务表切忌出现大类型的字段,像text和blob等
  • 字段加not null约束,避免出现null。select count()统计计数时值为null的数据不会被计算进去
SQL语句的优化
  • select获取必要字段,避免使用select * :可减少网络带宽消耗,可能用到覆盖索引
  • SQL语句避免出现or和union,判断由程序完成,使用union all可减少去重和排序
  • 很长的字符串可以创建前缀索引
  • 执行计划extra项出现Using filesort或Using temporary时考虑创建排序索引和分组索引
避免索引失效
  • 尽量不要使用模糊查询 select ... like '%**%' ,可以使用搜索引擎sphinx代替
  • 索引字段上不要使用函数,否则索引失效,也不要创建函数索引
  • 复合索引不能跨列(最佳左前缀)
  • 避免索引列的数据类型存在隐形转换
参数监控
查看profile参数:show variables like 'profiling';
开启profile:set profiling=1;
执行SQL语句后可查看运行结果:show profile;
查看消耗CPU和磁盘I/O的情况:show profile block io,cpu for query 1;
end
  • 作者:suoyue_zhan(联系作者)
  • 发表时间:2021-01-29 07:39:33
  • 版权声明:自由转载-非商用-非衍生-保持署名(创意共享3.0许可证)
  • 转载声明:如果是转载栈主转载的文章,请附上原文链接
  • 公众号转载:请在文末添加作者公众号二维码(公众号二维码见右边,欢迎关注)
  • 评论