MySQL 性能优化 -慢查询日志分析

序言

前面讲到了使用 explain 来分析 SQL 语句的性能，那么在真实的生产环境中，如果出现了性能慢的问题，不可能卓条 SQL 语句去分析，这个时候，就需要开启慢查询日志，将影响性能的SQL 语句定位出来，然后再通过执行计划来分析优化相对应的 SQL 语句。

一、什么是慢查询日志？

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

默认情况下，MySQL 数据库没有开启慢查询日志 ，需要我们手动来设置这个参数。

如果不需要调优的话，一般 不建议启动该参数，因为开启慢查询日志会或多或少的带来一定的性能影响。慢查询日志支持将日志记录写入到文件中。

二、使用慢查询日志

• 查看是否开启慢查询日志

  show variables like '%slow_query_log%';
  

  从图中可以看出，我本地的已经开启了慢查询日志分析，默认情况下，是关闭的 OFF

• 开启慢查询语句（重启后失效）：

  set global slow_query_log=1;
  

  执行此语句命令后，开启慢查询日志功能。注意：只对当前数据库生效，MySQL 重启后会失效。

• 开启慢查询语句（永久生效）：

  #如果要永久生效，需要修改配置文件 my.cnf；
  #[mysqld] 下增加或修改参数，slow_query_log 和 slow_query_log_file，重启即可，如下：
  
  #开启慢查询
  slow_query_log=1
  #慢查询日志存放的位置
  slow_query_log_file=/home/services/mysqllog/mysql-slow.log
  #规定慢 SQL 的查询阙值，超过这个值将会被记录到慢查询文件中，单位：秒
  long_query_time=3
  #慢查询日志以文件的形式输出
  log_output=FILE
  

• 什么样的 SQL 才会被记录到慢查询日志文件中？

  这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒。
  命令: SHOW VARIABLES LIKE ‘long_query_time%’;
  

  可以使用命令修改，也可以在mycnf参数里面修改
  假如运行时间正好等于long_querytime的情况，并不会被记录下来。也就是说，在mysql源码里是 判断大于long_query_time，而非大于等于。

• 如何设置慢的阙值时间？

  通过命令： set global long_query_time = 3;

• 为什么设置后看不出变化？

  需要重新连接或新开一个会话，才能看到修改后的值。
  使用命令： show global variables like ‘long_query_time’;

• 记录慢 SQL 并后续分析

  根据上面的配置，可以到服务器对应的目录下找到对应的慢查询日志，里面会将查过阙值的慢 SQL 记录到里面去。

• 查询当前系统中有多少条慢查询记录

  查看系统中记录了多少次慢查询的 SQL，值越小说明性能越高；
  命令： show global status like ‘%slow_queries%’;
  

• 慢查询日志分析工具 - mysqldumpslow

  在生产环境中，如果要手动分析日志，查找、分析 SQL，显然是个体力活，MySQL 提供了日志分析工具 – mysqldumpslow

  常用命令：
  mysqldumpslow -help

  -s:表示按照合种方式排序；
  -c:访问次数
  -l:锁定时间
  -r:返回记录
  -t:查询时间
  -al:平均锁定时间
  -ar:平均返回记录数
  -at:平均查询时间
  -t: 返回前面多少条的数据
  -g: 后面搭配一个正则匹配模式，大小写不敏感
  

  工作中常用的语句举例：

  # 得到返回记录集最多的 10 个 SQL
  mysqldumpslow -s r -t 10 /home/services/mysqllog/mysql-slow.log
  
  # 得到访问次数最多的 10 个 SQL
  mysqldumpslow -s c -t 10 /home/services/mysqllog/mysql-slow.log
  
  # 得到按照时间排序的前 10 条里面含有左连接的查询语句
  mysqldumpslow -s t -t 10 -g "left join" /home/services/mysqllog/mysql-slow.log
  
  # 另外建议再使用这些命令时结合 | 和 more 使用，否则有可能出现爆屏情况
  mysqldumpslow -s r -t 10 /home/services/mysqllog/mysql-slow.log | more
  

  通常情况，使用慢查询日志即可定位到性能不好的 SQL。下面也可以使用 show profile 进行分析排查。

三、Show Profile 使用

• show profile 是什么？

  是 mysql 提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于 SQL 的调优测量。
  默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果。

• 如何使用？

  1 查看当前的 mysql 版本是否支持：

  show variables like 'profiling';
  

  
  默认是关闭状态，使用前需要开启。

  2 开启功能

  set profiling=on;
  

  3 执行程序中的 SQL 语句，此时均会被 profile 记录；
  4 查看记录结果

  show profiles;
  

  

  5 诊断 SQL，show profile cpu,block io for query 上一步前面的问题 SQL 数字号码，可以看到整条 SQL 的执行生命周期；

  show profile cpu,block io for query 8;
  

  6 日常开发需要注意的结论，出现下面 4 个钟的任何一个，都需要优化：

  converting HEAP to MyISAM 查询结果太大，内存不够用了往磁盘上搬。
  Creating tmp tabel 创建临时表（拷贝数据到临时表，用完再删除）
  Copying to tmp table on dist 把内存中临时表复制到磁盘
  locked