本文整理了一些MySQL的通用优化方
法，做个简单的总结分享，旨在帮助
那些没有专职MySQL DBA的企业做
好基本的优化工作，至于具体的SQL
优化，大部分通过加适当的索引即可
达到效果，更复杂的就需要具体分析
了，可以参考本站的一些优化案例或
者联系我，下方有我的联系方式。
在服务器的BIOS设置中，可调整下
面的几个配置，目的是发挥CPU最大
性能，或者避免经典的NUMA问题：
1
、选择Performance Per Watt
Optimized(DAPC)模式，发挥CPU最
大性能，跑DB这种通常需要高运算
量的服务就不要考虑节电了；
2
、关闭C1E和C States等选项，目的
也是为了提升CPU效率；
3
、Memor y Frequency（内存频率）选
择Ma xi mum Performance（最佳性
能）；
4、内存设置菜单中，启用Node
Interleaving，避免NUMA问题；
下面几个是按照IOPS性能提升的幅度
排序，对于磁盘I/O可优化的一些措
施：
1
、使用SSD或者PCIe SSD设备，至
少获得数百倍甚至万倍的IOPS提升；
2
、购置阵列卡同时配备CACHE及
BBU模块，可明显提升IOPS（主要是
指机械盘，SSD或PCIe SSD除外。同
时需要定期检查CACHE及BBU模块
的健康状况，确保意外时不至于丢失
数据）；
3、有阵列卡时，设置阵列写策略为
WB，甚至FORCE WB（若有双电保
护，或对数据安全性要求不是特别高
的话），严禁使用WT策略。并且闭
阵列预读策略，基本上是鸡肋，用处
不大；
4、尽可能选用RAID-10，而非RAID-
5；
5、使用机械盘的话，尽可能选择高
转速的，例如选用15KRPM，而不是
.2KRPM的盘，不差几个钱的；
7
在文件系统层，下面几个措施可明显
提升IOPS性能：
1
、使用deadline/noop这两种I/O调度
器，千万别用cfq（它不适合跑DB类
服务）；
2
、使用xfs文件系统，千万别用
ext3；ext4勉强可用，但业务量很大
的话，则一定要用xfs；
3
、文件系统mount参数中增加：
noati me, nodiratime, nobarrier几个选项
nobarrier是xfs文件系统特有的）；
（
针对关键内核参数设定合适的值，目
的是为了减少swap的倾向，并且让内
存和磁盘I/O不会出现大幅波动，导
致瞬间波峰负载：
1
、将vm.swappiness设置为5-10左右
即可，甚至设置为0（RHEL 7以上则
慎重设置为0，除非你允许OOM kill
发生），以降低使用SWAP的机会；
2
、将vm.dirty_background_ratio设置
为5-10，将vm.dirty_ratio设置为它的
两倍左右，以确保能持续将脏数据刷
新到磁盘，避免瞬间I/O写，产生严
重等待（和MySQL中的
innodb_max_dirty_pages_pct类似）；
3、将net.ipv4.tcp_tw_recycle 、
net.ipv4.tcp_tw_reuse都设置为1，减
少TIME_WAIT，提高TCP效率；
4、至于网传的read_ahead_kb、
nr_requests这两个参数，我经过测试
后，发现对读写混合为主的OLTP环
境影响并不大（应该是对读敏感的场
景更有效果），不过没准是我测试方
法有问题，可自行斟酌是否调整；
官方版本我们称为ORACLE
MySQL，这个没什么好说的，相信绝
大多数人会选择它。
我个人强烈建议选择Percona分支版
本，它是一个相对比较成熟的、优秀
的MySQL分支版本，在性能提升、可
靠性、管理型方面做了不少改善。它
和官方ORACLE MySQL版本基本完
全兼容，并且性能大约有20%以上的
提升，因此我优先推荐它，我自己也
从2008年一直以它为主。
另一个重要的分支版本是MariaDB，
说MariaDB是分支版本其实已经不太
合适了，因为它的目标是取代
ORACLE MySQL。它主要在原来的
MySQL Server层做了大量的源码级改
进，也是一个非常可靠的、优秀的分
支版本。但也由此产生了以GTID为
代表的和官方版本无法兼容的新特性
（MySQL 5.7开始，也支持GTID模式
在线动态开启或关闭了），也考虑到
绝大多数人还是会跟着官方版本走，
因此没优先推荐MariaDB。
建议调整下面几个关键参数以获得较
好的性能（可使用本站提供的my. c nf
生成器生成配置文件模板）：
1
、选择Percona或MariaDB版本的
话，强烈建议启用thread pool特性，
可使得在高并发的情况下，性能不会
发生大幅下降。此外，还有extra_port
功能，非常实用， 关键时刻能救命
的。还有另外一个重要特色是
QUERY_RESPONSE_TIME 功能，也能
使我们对整体的SQL响应时间分布有
直观感受；
2、设置
default-storage-engine=InnoDB ，
也就是默认采用InnoDB引擎，强烈建
议不要再使用MyISAM引擎了，
InnoDB引擎绝对可以满足99%以上的
业务场景；
3、调整 innodb_buffer_pool_size
大小，如果是单实例且绝大多数是
InnoDB引擎表的话，可考虑设置为物
理内存的50% ~ 70%左右；
4、根据实际需要设置
innodb_flush_log_at_trx_commit
、
sync_binlog 的值。如果要求数据
不能丢失，那么两个都设为1。如果
允许丢失一点数据，则可分别设为 2
和10。而如果完全不用care数据是否
丢失的话（例如在slave上，反正大不
了重做一次），则可都设为0。这三
种设置值导致数据库的性能受到影响
程度分别是：高、中、低，也就是第
一个会另数据库最慢，最后一个则相
反；
5、设置
innodb_file_per_table = 1 ，使用
独立表空间，我实在是想不出来用共
享表空间有什么好处了；
6、设置
innodb_data_file_path = ibdata1:
，千万不要用默认的10M，否则在有
高并发事务时，会受到不小的影响；
7、设置
innodb_log_file_size=256M ，设置
innodb_log_files_in_group=2 ，基
本可满足90%以上的场景；
8、设置 long_query_time = 1 ，而
在5.5版本以上，已经可以设置为小
于1了，建议设置为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 的大小；
1
0、常见的误区是把
tmp_table_size 和
max_heap_table_size 设置的比较
大，曾经见过设置为1G的，这2个选
项是每个连接会话都会分配的，因此
不要设置过大，否则容易导致OOM
发生；其他的一些连接会话级选项例
如： sort_buffer_size 、
join_buffer_size 、
read_buffer_size 、
read_rnd_buffer_size 等，也需要
注意不能设置过大；
11、由于已经建议不再使用MyISAM
引擎了，因此可以把
key_buffer_size 设置为32M左右，
并且强烈建议关闭query cache功能；
下面列举了几个常见有助于提升
MySQL效率的Schema设计规范及SQL
使用建议：
1
、所有的InnoDB表都设计一个无业
务用途的自增列做主键，对于绝大多
数场景都是如此，真正纯只读用
InnoDB表的并不多，真如此的话还不
如用To kuDB来得划算；
2
、字段长度满足需求前提下，尽可
能选择长度小的。此外，字段属性尽
量都加上NOT NULL约束，可一定程
度提高性能；
、尽可能不使用TEXT/BLOB类型，
3
确实需要的话，建议拆分到子表中，
不要和主表放在一起，避免SELECT
*
的时候读性能太差。
4、读取数据时，只选取所需要的
列，不要每次都SELECT *，避免产
生严重的随机读问题，尤其是读到一
些TEXT/BLOB列；
5、对一个VARCHAR(N)列创建索引
时，通常取其50%（甚至更小）左右
长度创建前缀索引就足以满足80%以
上的查询需求了，没必要创建整列的
全长度索引；
6、通常情况下，子查询的性能比较
差，建议改造成JOIN写法；
7、多表联接查询时，关联字段类型
尽量一致，并且都要有索引；
8、多表连接查询时，把结果集小的
表（注意，这里是指过滤后的结果
集，不一定是全表数据量小的）作为
驱动表；
9、多表联接并且有排序时，排序字
段必须是驱动表里的，否则排序列无
法用到索引；
10、多用复合索引，少用多个独立索
引，尤其是一些基数（Cardinality）
太小（比如说，该列的唯一值总数少
于255）的列就不要创建独立索引
了；
11、类似分页功能的SQL，建议先用
主键关联，然后返回结果集，效率会
高很多；
关于MySQL的管理维护的其他建议
有：
1
1
、通常地，单表物理大小不超过
0GB，单表行数不超过1亿条，行平
均长度不超过8KB，如果机器性能足
够，这些数据量MySQL是完全能处理
的过来的，不用担心性能问题，这么
建议主要是考虑ONLINE DDL的代价
较高；
2
、不用太担心mysql d进程占用太多
内存，只要不发生OOM kill和用到大
量的SWAP都还好；
、在以往，单机上跑多实例的目的
3
是能最大化利用计算资源，如果单实
例已经能耗尽大部分计算资源的话，
就没必要再跑多实例了；
4、定期使用
pt-duplicate-key-checker 检查并
删除重复的索引。定期使用
pt-index-usage 工具检查并删除使
用频率很低的索引；
5、定期采集 slow query log ，用
pt-query-digest 工具进行分析，可
结合Anemometer系统进行
slow query 管理以便分析
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 来检查并修复mys ql
主从复制的数据差异；
后记：本文根据个人多年经验总结，
个别建议可能有不完善之处，欢迎留
言或者加我 微信公众号：MySQL中
文网、QQ：4700963 相互探讨交流。
写在最后：这次的优化参考，大部分
情况下我都介绍了适用的场景，如果
你的应用场景和本文描述的不太一
样，那么建议根据实际情况进行调
整，而不是生搬硬套。欢迎质疑拍
砖，但拒绝不经过大脑的习惯性抵
制。
