MySQL数据库调优实战经验总结

MySQL 数据库的使用是非常的广泛，稳定性和安全性也非常好，经历了无数大小公司的验证。仅能够安装使用是远远不够的，MySQL 在使用中需要进行不断的调整参数或优化设置，才能够发挥 MySQL 的最大作用。下边的内容是我在工作中经验的总结，也作为自己的工作笔记，如果能够帮助到有需要的同志就更好了。MySQL 的优化可以从个方面来做：

一、架构层面

1、做主从复制。
2、实现读写分离。

二、系统层面

1、增加内存。
2、硬盘使用固态硬盘 SSD。
3、给磁盘做 raid0 或者 raid5 以增加磁盘的读写速度。
4、可以重新挂载磁盘，并加上 noatime 参数，这样可以减少磁盘的 I/O。

三、MySQL本身的优化

1、如果未配置主从同步，可以把 bin-log 功能关闭，减少磁盘 I/O。
2、在 my.cnf 中加上 skip-name-resolve ，这样可以避免由于解析主机名延迟造成 M有SQL 执行慢。
3、调整几个关键的 buffer 和 cache。调整的依据，主要根据数据库的状态来调试。如何调优可以参考五。
4、根据具体的使用场景，选择合适的存储引擎。

四、应用层次

查看慢查询日志，根据慢查询日志优化程序中的 SQL 语句，比如增加索引

五、调整关键的buffer和cache

1、key_buffer_size

首先可以根据系统的内存大小设定它，大概的一个参考值：1G以下内存设定 128M；2G/256M; 4G/384M; 8G/1024M；16G/2048M。这个值可以通过检查状态值 Key_read_requests 和 Key_reads，可以知道 key_buffer_size 设置是否合理。比例 key_reads / key_read_requests 应该尽可能的低，至少是 1:100，1:1000更好(上述状态值可以使用 SHOW STATUS LIKE 'key_read%' 获得)。注意：该参数值设置的过大反而会是服务器整体效率降低!

2、table_open_cache

打开一个表的时候，会临时把表里面的数据放到这部分内存中，一般设置成 1024 就够了，它的大小我们可以通过这样的方法来衡量： 如果你发现 open_tables 等于 table_cache，并且 opened_tables 在不断增长，那么你就需要增加 table_cache 的值了(上述状态值可以使用 SHOW STATUS LIKE 'Open%tables' 获得)。注意，不能盲目地把 table_cache 设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

3、sort_buffer_size

查询排序时所能使用的缓冲区大小,该参数对应的分配内存是每连接独占! 如果有 100 个连接，那么实际分配的总共排序缓冲区大小为100 × 4 = 400MB。所以，对于内存在 4GB 左右的服务器推荐设置为：4-8M。

4、read_buffer_size

读查询操作所能使用的缓冲区大小。和 sort_buffer_size 一样，该参数对应的分配内存也是每连接独享!

5、join_buffer_size

联合查询操作所能使用的缓冲区大小，和 sort_buffer_size 一样，该参数对应的分配内存也是每连接独享!

6、myisam_sort_buffer_size

这个缓冲区主要用于修复表过程中排序索引使用的内存或者是建立索引时排序索引用到的内存大小，一般 4G 内存给 64M 即可。

7、query_cache_size

MySQL查询操作缓冲区的大小，通过以下做法调整：SHOW STATUS LIKE ‘Qcache%’; 如果Qcache_lowmem_prunes该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。如果该值非常大，则表明经常出现缓冲不够的情况，需要增加缓存大小Qcache_free_memory：查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，我们可以根据实际情况做出调整。一般情况下 4G 内存设置 64M 足够了。

8、thread_cache_size

表示可以重新利用保存在缓存中线程的数，参考如下值：1G  —> 8; 2G  —> 16; 3G  —> 32; 3G  —> 64
除此之外，还有几个比较关键的参数

9、thread_concurrency

这个值设置为 CPU 核数的2倍即可。

10、wait_timeout

表示空闲的连接超时时间，默认是：28800s，这个参数是和 interactive_timeout 一起使用的，也就是说要想让 wait_timeout 生效，必须同时设置 interactive_timeout，建议他们两个都设置为10。

11、max_connect_errors

是一个 MySQL 中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。与性能并无太大关系。为了避免一些错误我们一般都设置比较大，比如说10000。

12、max_connections

最大的连接数，根据业务请求量适当调整，设置 500 足够。

13、max_user_connections

是指同一个账号能够同时连接到 mysql 服务的最大连接数。设置为 0 表示不限制。通常我们设置为 100 足够。