MySQL参数优化的选项

一些能够带来最大性能收益的参数调整

1. 调整InnoDB缓冲区

InnoDB是MySQL启动后使用最多的引擎，分配一个足够大的缓冲区能够最大程度上提升MySQL性能。最佳比例控制在机器内存的70~75%左右，比如一台服务器的内存为32GB，将innodb_buffer_pool_size = 22938M（23GB）左右最合理。

InnoDB缓冲区空间大于1GB时，会自动将缓冲区划分为多个实例空间，好处在于：多线程并发执行时，可以减少并发冲突。MySQL官方的建议是每个缓冲区实例必须大于1GB，如果机器内存较小时，例如8/16GB，可以指定为1GB，但是机器内存够大时，达到了32GB/64GB甚至更高，可以适当将每个缓冲区实例调整到2GB左右。

比如现在假设缓冲区共计拥有40GB内存，哪设置将缓冲区实例设置为innodb_buffer_pool_instances = 20个比较合适。

2. 调整工作线程的缓冲区

可以调大sort_buffer、read_buffer、join_buffer几个区域，属于线程私有区域，每条线程都拥有这些区域：

• sort_buffer_size：排序缓冲区大小，影响group by、order by...等排序操作。
• read_buffer_size：读取缓冲区大小，影响select...查询操作的性能。
• join_buffer_size：联查缓冲区大小，影响join多表联查的性能。

这些区域根据机器内存设置1~2倍MB，4G内存将其调整为4/8MB，控制在64MB以下。线程每执行完一条SQL就会释放这些区域，没必要调整的太大。

还可以调整一个参数：max_length_for_sort_data，这个参数关乎着MySQL排序的方式，排序字段值的最大长度小于该值，会将所有要排序的字段值载入内存排序，但如果大于该值时，则会一批一批的加载排序字段值进内存，然后一边加载一边做排序。第一种效率更高，可以适当调大该参数的值，具体根据业务来做选择，或者交给MySQL自己控制。

3. 调整临时表空间

可以调整tmp_table_size、max_heap_table_size，这两个参数主要是限制临时表可用的内存空间，当创建的临时表空间占用超过tmp_table_size时，就会将其他新创建的临时表转到磁盘中创建，十分违背创建临时表加快查询速度的设计初衷。

参数的大小根据show global status like 'created_tmp%';的统计信息来决定，用统计出来的信息：Created_tmp_disk_tables / Created_tmp_tables * 100% = 120%，达到这个标准就比较合适，调整这个区域的值需要反复重启MySQL以及压测，比较费时间，在项目中很少使用临时表，也可以不关心这块参数的调整。

4. 调整空闲线程的存活时间

对于MySQL最大连接数无需做过多控制，客户端连接池那边做了调整即可，可以通过下述命令查看数据库连接的峰值：

• show global status like 'Max_used_connections';

一般在客户端做了连接数控制后，这个峰值都会在客户端最大连接数的范围之内，对于数据库连接唯一需要稍微调整的即是空闲连接的超时时间，即wait_timeout、interactive_timeout，这两个参数必须一同设置，否则不会生效，MySQL内部默认为8小时，也就是一个连接断开后，默认也会将对应的工作线程缓存八小时后再销毁，可以手动调整成30min~1h左右，让无用的连接能及时释放，减少资源的占用。