mysql wb bbu_比较全面的 MySQL 优化参考？那还得看这篇了~

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 这两个参数，我经过测试后，发现对读写混合为主的 OLTP 环境影响其实不年夜(应该是对读敏感的场景更有效果)，不过没准是我测试体例有问题，可自行斟酌是否调剂；

3、MySQL 层相关优化

3.1、关于版本选择

官方版本我们称为 ORACLE MySQL，这个没什么好说的，相信绝年夜大都人会选择它。

我个人强烈建议选择 Percona 分支版本，它是一个相对比较成熟的、优秀的 MySQL 分支版本，在性能提升、可靠性、管理型方面做了很多改良。它和官方 ORACLE MySQL 版本基本完全兼容，并且性能年夜约有 20% 以上的提升，因此我优先推荐它，我自己也从 2008 年一直以它为主。

另一个重要的分支版本是 MariaDB，说 MariaDB 是分支版本其实已经不太合适了，因为它的目标是取代 ORACLE MySQL。它主要在原来的 MySQL Server 层做了年夜量的源码级改进，也是一个很是可靠的、优秀的分支版本。但也由此产生了以 GTID 为代表的和官方版本无法兼容的新特性(MySQL 5.7 开始，也支持 GTID 模式在线动态开启或关闭了)，也考虑到绝年夜大都人还是会跟着官方版本走，因此没优先推荐 MariaDB。

3.2、关于最重要的参数选项调剂建议

建议调剂下面几个关键参数以获得较好的性能(可使用本站提供的 my.cnf 生成器生成配置文件模板)：

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:1G:autoextend，千万不要用默认的 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 的年夜小；

10、常见的误区是把 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 功能；

3.3、关于 Schema 设计规范及 SQL 使用建议

下面列举了几个常见有助于提升 MySQL 效率的 Schema 设计规范及 SQL 使用建议：

1、所有的 InnoDB 表都设计一个无业务用途的自增列做主键，对绝年夜大都场景都是如此，真正纯只读用 InnoDB 表的其实不多，真如此的话还不如用 TokuDB 来得划算；

2、字段长度满足需求前提下，尽可能选择长度小的。另外，字段属性尽量都加上 NOT NULL 约束，可一定水平提高性能；

3、尽可能不使用 TEXT/BLOB 类型，确实需要的话，建议拆分到子表中，不要和主表放在一起，避免 SELECT * 的时候读性能太差。

4、读取数据时，只选取所需要的列，不要每次都 SELECT *，避免产生严重的随机读问题，尤其是读到一些 TEXT/BLOB 列；

5、对一个 VARCHAR(N) 列建立索引时，通常取其 50%(甚至更小)左右长度建立前缀索引就足以满足 80% 以上的查询需求了，没需要建立整列的全长度索引；

6、通常情况下，子查询的性能比较差，建议革新成 JOIN 写法；

7、多表联接查询时，关联字段类型尽量一致，并且都要有索引；

8、多表连接查询时，把结果集小的表(注意，这里是指过滤后的结果集，不一定是全表数据量小的)作为驱动表；

9、多表联接并且有排序时，排序字段必须是驱动内外的，不然排序列无法用到索引；

10、多用复合索引，少用多个自力索引，尤其是一些基数(Cardinality)太小(好比说，该列的值总数少于 255)的列就不要建立自力索引了；

11、类似分页功能的 SQL，建议先用主键关联，然后返回结果集，效率会高很多；

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 杀失落超长时间的 SQL 请求，Percona 版本中有个选项 innodb_kill_idle_transaction 也可实现该功能；

7、使用 pt-online-schema-change 来完成年夜表的 ONLINE DDL 需求；

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

写在最后：

这次的优化参考，年夜部分情况下我都介绍了适用的场景，如果你的应用场景和本文描述的不太一样，那么建议根据实际情况进行调剂，而不是生搬硬套。欢迎质疑拍砖，但拒绝不经过年夜脑的习惯性抵制。

更多内容回复查看:

游客，如果您要查看本帖隐藏内容请回复