MySQL优化的方法是什么

这篇文章主要介绍“MySQL优化的方法是什么”，在日常操作中，相信很多人在MySQL优化的方法是什么问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL优化的方法是什么”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

硬件层的优化
新采购的服务器默认跑在节能模式下，在并发访问量很大的业务场景，会导致数据库性能跟不上，造成大量延迟，最终将拖垮业务系统。与此同时，磁盘选择与阵列卡设置不当也会使数据库性能成为整个业务系统的瓶颈。

目标一：全面关闭节能模式，让MySQL跑在高性能模式下
1.关闭CPU节能模式

找到OPI Link Speed Select选项，选择Max Performance
2.关闭内存节能模式

找到Memory Speed选项，选择Max Performance
找到Power C-States选项，选择Disable
找到C1 Enhanced Mode选项，选择Disable
目标二：关闭NUMA，让CPU能始终高效地使用内存
关闭NUMA

找到Socket Interleave选项，选择Non-NUMA
目标三：全面提升IOPS性能，让磁盘I/O不再拖后退
1.资金充足时，采购SSD甚至PCIe-SSD

SSD和PCIe-SSD带来的不只是惊喜，更有踏实，从此磁盘I/O不再是恶魔
2.机械盘搭配阵列卡，Cache策略，BBU电池，RAID-10，15KRPM

阵列卡从容面对多块机械盘，BBU电池保障高性能模式下的Cache策略不丢数据
Cache策略选择Write Back甚至Always Write Back
阵列预读的Read Policy选项，选择Normal
使用RAID-10,性能高于RAID-5
使用15KRPM高速磁盘，性能高于7.2KRPM磁盘
备注：服务器硬件设置的参数来源于IBM X3650M3

系统层的优化
操作系统方面也存在多处值得优化的地方，同样能明显提升IOPS性能。另外，SWAP要少用，不但不能救命，反而会让业务系统处于崩溃边缘。

目标一：全面提升IOPS性能，让数据库不再背锅
1.配置合理的I/O调度器

机械盘配deadline，执行命令echo deadline >/sys/block/sda/queue/scheduler
固态盘配noop，执行命令echo noop >/sys/block/sda/queue/scheduler
注意sda是数据文件所在分区
2.文件系统尽量使用XFS，假如还在使用ext4，希望只是过度阶段

3.mount参数增加noatime，nodiratime，nobarrier

vi /etc/fstab
/dev/sda1 /data xfs defaults,noatime,nodiratime,nobarrier 0 0
/dev/sda2 / xfs defaults,noatime,nodiratime,nobarrier 0 0
/dev/sda3 swap swap defaults,noatime,nodiratime,nobarrier 0 0
mount -o remount /data
mount
目标二：减少SWAP使用倾向甚至禁掉，稳定磁盘I/O和网络减少等待时间，让MySQL表现更加稳定
1.vm.swappiness设为5甚至0，假如不关心发生OOM

echo 'vm.swappiness = 5' >>/etc/sysctl.conf
/sbin/sysctl -p
2.vm.dirty_background_ratio设为5，vm.dirty_ratio设为10，让脏页持续刷入磁盘，避免磁盘I/O瞬间写产生TIME_WAIT

echo 'vm.dirty_background_ration = 5' >>/etc/sysctl.conf
echo 'vm.dirty_ratio = 10' >>/etc/sysctl.conf
/sbin/sysctl -p
3.net.ipv4.tcp_tw_recycle和net.ipv4.tcp_tw_reuse设为双1，减少网络等待时间，提高效率

echo 'net.ipv4.tcp_tw_recycle = 1' >>/etc/sysctl.conf
echo 'net.ipv4.tcp_tw_reuse = 1' >>/etc/sysctl.conf
/sbin/sysctl -p
MySQL层的优化
选对MySQL版本尤为重要，找到适合业务系统的版本，才能发挥出更大性能。运行参数亦是如此，需要反复斟酌与调校。规范schema设计与sql编写，还有规范上线后的运维管理流程，同样也会带不小的收益。

目标一：选对版本，让MySQL起跑底气十足
1.优先推荐Oracle MySQL，越来越多的新上系统在拥抱官方5.7.x版本

2.其次推荐Percona分支版本，在这里能享受免费的thread pool和audit plugin

3.最后是MariaDB分支版本，除了线程池和审计插件，在这里能享受免费的黑科技

目标二：调校合适的参数，让MySQL的性能更加稳定
1.如果选择使用Percona或MariaDB分支版本，强烈推荐开启thread pool

2.设置default-storage-engine=innodb，innodb可以满足99%以上的业务场景

3.设置合适的innodb_buffer_pool_size大小，单实例多数是innodb表，建议设置物理内存的50%-70%

4.设置合适的innodb_flush_log_at_trx_commit和sync_binlog值

设置双1，不丢数据，性能较低
设置2和10，丢失一点数据，性能一般
设置双0，数据不×××全，性能最高
5.设置innodb_file_per_table = 1，使用独立表空间

6.设置innodb_data_file_path = ibdata1:1G:autoextend，在高并发事务时获得良好性能

7.设置innodb_log_file_size=256M，innodb_log_files_in_group=2

8.设置long_query_time = 0.05，记录超过50毫秒的慢SQL

9.适当调大max_connection，建议设置max_connection_error为10万以上，设置open_files_limit、innodb_open_files、table_open_cache、table_definition_cache约10倍于max_connection

10.不宜设置过大的参数tmp_table_size、max_heap_table_size、sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size

11.设置key_buffer_size = 32M，关闭query cache功能

关闭QC需要在启动MySQL前配置
query_cache_type = 0
query_cache_size = 0
目标三：Schema设计和SQL编写根据参考规范设定，有助于提高MySQL效率
1.所有innodb表都设计一个无业务用途的自增列做主键

2.字段类型在满足够用时，尽量选长度小的；字段属性尽量都加上NOT NULL约束

3.尽量不用TEXT和BLOB字段类型，一定需要时拆分至子表

4.查询时，尽量填写需要的列，不要查询所有列，避免严重随机读问题

5.一般varchar(n)列建索引是，取前50%长度即可

6.子查询处理时性能低，建议改使用JOIN改写SQL

7.多表连接查询时，关键字类型尽量一致，且都要有索引

8.多表连接查询时，把过滤后的结果集小的表作为驱动表

优势：不需要的数据不会出现，SQL查询范围小，执行效率高
9.多表连接查询并且有排序时，排序字段必须是驱动表里的，否则排序列不走索引

10.多用复合索引，少用多个独立索引，尤其是基数太小的列则不建议创建索引

11.使用分页功能的SQL时，选把关键字与主键做符合索引，再来执行，效率会高很多

目标四：管理维护的优化，让运维更高效
1.online DDL代价太高，机器性能足够时，建议单表物理不超过10G，单表行数不超过1亿，行平均长度不超过8KB

2.不出现OOM KILL和大量使用SWAP，不必担心MySQL进程占用过多内存

3.单实例运行中硬件资源还是比较紧张时，不要跑多实例

4.定期用pt-duplicate-key-checker检查和删除重复索引，定期用pt-index-usage检查和删除不太用的索引

5.定期采集slow query log，用pt-query-digest工具进行分析，再结合Anemometer等系统进行slow query管理，以便于分析和优化

6.可以使用pt-kill杀掉超长时间的SQL请求，Percona版本中有个选项 innodb_kill_idle_transaction也能实现该功能

7.可以使用pt-online-schema-change来完成大表的ONLINE DDL需求

8.定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异

核心纲领：在上线之前，变更任何一个参数，都要做压力测试，避免漏网之鱼导致MySQL出现各种CRASH。

到此，关于“MySQL优化的方法是什么”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！