MySQL的全面优化 - 硬件/参数配置/操作系统/表设计

硬件设备的优化

影响数据库最大的性能问题就是磁盘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;