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

MySQL 数据库的使⽤是⾮常的⼴泛，稳定性和安全性也⾮常好，经历了⽆数⼤⼩公司的验证。仅能够安装使⽤是远远不够的，MySQL 在使⽤中需要进⾏不断的调整参数或优化设置，才能够发挥 MySQL 的最⼤作⽤。下边的内容是我在⼯作中经验的总结，也作为⾃⼰的⼯作笔记，如果能够帮助到有需要的同志就更好了。MySQL 的优化可以从个⽅⾯来做：⼀、架构层⾯1、做主从复制。2、实现读写分离。3、分库分表。⼆、系统层⾯

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和cache1、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 ⾜够。----- 待更新 -----