MySQL性能分析思路总结

MySQL性能分析思路总结

总结一下对mysql性能分析的大概思路。

分析

• 开启慢查询日志（slow_query_log）
  设定一个阈值，通过查询日志记录下这些慢的SQL语句，然后进行分析
  1、是否建立了索引？
  2、建立了索引，是否有达到走索引的效果？
  如：遵循最左前缀原则；
  是否使用了范围查找（>、<、in、between）；
  是否使用了not in、!=、<>导致全表扫描；
  是否使用了is null、is not null导致索引失效；
  是否使用了like 并且以%开头导致索引失效（可以通过覆盖索引查询进行优化）
  是否在使用VARCHAR字符串类型查找时没有加单引号；
  是否使用了OR导致索引失效；
  使用order by和group by是遵循使用规范（按照符合索引顺序）；
  3、多表查询分析是否遵循小表驱动大表，要合理使用 in 和 exists（解析）

• 通过explain进行性能分析，主要观察字段有type、key、key_len、ref、Extra字段查看，查看是否达到了走索引的目的，分析并调整SQL语句或索引设计
  1、出现using filesort：没有使用到索引实现排序，需要mysql对数据使用一个外部索引排序（第一个索引元素确定值，跳过中间索引元素直接以后面的索引元素进行排序分组），影响性能
  2、出现using temporary：出现了临时表，不合理使用order by或group by（第一个索引元素列是范围查找后，直接使用第二个索引元素作为排序分组），影响性能

• 通过show profiles进行语句消耗时间的具体分析，查看是否出现了内存不足导致的重排序、建立临时表等问题影响性能（出现以下四种字段）

show profiles;#查询最新一定量的语句查询时间，默认15条
show profile cpu.block io for query 3; #查询id为3的语句

--查询结果太大，内存不够用往磁盘上搬
converting HEAP to MyISAM
--拷贝数据到临时表、用完在删除
Creating tmp
--把内存中临时表复制到磁盘，危险！！！
Copying to tmp table on disk
--
locked

• 运维调优，通过上面分析的结果进行参数的一些调整来提高性能，如sort_buffer_size、max_length_for_sort_data（这里filesort有双路排序和单路排序两种算法）之类的参数，不过一般不是有后端开发人员来调整。

总结：
在分析SQL语句性能时的大概过程
发现问题-》慢查询日志-》抓取慢SQL-》explain分析优化SQL-》show profiles分析-》参数调优