比较全面的MySQL优化技术

文中梳理了某些MySQL的通用性提升方式，做个简易的小结共享，亟需协助这些沒有职业MySQL DBA的公司搞好基础的提升工作中，置于主要的SQL提升，绝大多数根据加适度的数据库索引只能超过实际效果，更繁杂的就必须深入分析了，能够参照本网站的某些提升实例。

1、硬件配置层有关提升

1.1、CPU有关

在网络服务器的BIOS设定中，可调节下边的好多个配备，目地是充分发挥CPU较大特性，或是防止經典的NUMA难题：

1、挑选Performance Per Watt Optimized(DAPC)方式，充分发挥CPU较大特性，跑DB这类一般必须高运算量的服务项目就不必考虑到省电了；

2、关掉C1E和C States等选择项，目地都是以便提高CPU高效率；

3、Memory Frequency（内存频率）挑选Maximum Performance（最好特性）；

4、运行内存设定莱单中，开启Node Interleaving，防止NUMA难题；

1.2、硬盘I/O有关

下边好多个是依照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，而并不是7.2KRPM的盘，不差好多个钱的；

2、系统软件层有关提升

2.1、文件系统层提升

在文件系统层，下边好多个对策可显著提高IOPS特性：

1、应用deadline/noop这二种I/O生产调度器，不要再用cfq（它不宜跑DB类服务项目）；

2、应用xfs文件系统，不要再用ext3；ext4凑合能用，但货运量挺大得话，则必须得用xfs；

3、文件系统mount主要参数中提升：noatime, nodiratime, nobarrier好多个选择项（nobarrier是xfs文件系统独有的）；

2.2、别的内核主要参数提升

对于重要内核主要参数设置适合的值，目地是以便降低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这2个主要参数，我历经检测后，发觉对读写能力混和主导的OLTP环境危害并不多（应当是对读比较敏感的情景更有实际效果），只有没定就是我测试标准不太好，可自主斟酌是不是调节；

3、MySQL层有关提升

3.1、有关版本号挑选

官方网版本号人们称之为ORACLE MySQL，这一没有什么好说的，坚信绝大部分人要挑选它。

我本人强烈要求挑选Percona支系版本号，这是1个相对来说成熟期的、出色的MySQL支系版本号，在性能提升、可信性、企业型层面干了至少改 善。它和官方网ORACLE MySQL版本号基础彻底适配，而且特性大概有20%左右的提高，因而我优先选择强烈推荐它，自己也从2008年始终以它主导。

另外关键的支系版本号是MariaDB，说MariaDB是支系版本号我觉得早已不太适合了，由于它的总体目标是替代ORACLE MySQL。它关键在原先的MySQL Server层干了很多的源代码级改善，都是1个十分靠谱的、出色的支系版本号。但也从而造成了以GTID为意味着的和官方网版本号没法适配的新特点（MySQL 5.7刚开始，也适用GTID方式免费在线动态性打开或关掉了），也充分考虑绝大部分人还会跟随官方网版本号走，因而没优先选择强烈推荐MariaDB。

3.2、有关最关键的主要参数选择项调节提议

提议调节下边好多个重要主要参数以得到不错的特性（可应用本网站出示的 my.cnf生成器 转化成配置文件模版）：

1、挑选Percona或MariaDB版本号得话，强烈要求开启thread pool特点，可促使在分布式系统的状况下，特性不容易产生大幅度降低。除此之外，也有ex

6、一般来说，子查询的特性较为差，提议更新改造成JOIN写法有哪些；无线网络网速慢怎么办？为何家中的Wi-Fi很慢？怎样加速wifi速。

7、多表连接查寻时，关系字段名种类尽可能相同，而且必须有数据库索引；

8、多表联接查寻时，把結果集小的表（特别注意，这儿就是指过虑后的結果集，未必是全表信息量小的）做为驱动器表；

9、多表连接而且有排列时，排列字段名必需是驱动器表中的，不然排序列没法采用数据库索引；

10、要用复合型数据库索引，不要用好几个单独数据库索引，特别是在是某些基数（Cardinality）很小（例如，该列的惟一值数量低于255）的列就不必建立单独数据库索引了；

11、相近分页查询作用的SQL，提议先加主键关系，随后回到結果集，高效率会高许多；

3.3、别的提议

有关MySQL的管理方法维护保养的别的提议有：

1、一般地，单表物理学尺寸不超出10GB，单表行数不超出1亿条，行平均长度不超出8KB，要是设备特性任何，这种信息量MySQL是彻底能解决的回来的，担心特性难题，那么提议关键是考虑到ONLINE DDL的付出代价较高；

2、无需太担忧mysqld系统进程占有过多运行内存，要是不产生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干掉较长時间的SQLpost请求，Percona版本号中有一个选择项 innodb_kill_idle_transaction 也可建立该作用；

7、应用pt-online-schema-change来进行大表的ONLINE DDL要求；

8、每季度应用pt-table-checksum、pt-table-sync来查验并修补mysql主从复制的统计数据差别；

写在最终：此次的提升参照，绝大多数状况下我还详细介绍了可用的情景，当你的应用领域和文中叙述的不太一样，那麼提议依据具体情况开展调节，而并不是照本宣科。热烈欢迎提出质疑拍砖，但回绝不历经人的大脑的下意识遏制。