MySQL优化的方法是什么

共计 3887 个字符，预计需要花费 10 分钟才能阅读完成。

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

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

目标一：全面关闭节能模式，让 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 优化的方法是什么”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！