定位SQL慢查询，具体环境中，运行时间

定位SQL慢查询，具体环境中，运行时间

一、概念

• MySQL的慢查询（慢查询日志）：是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句。
• 具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是记录运行10秒以上的语句。
• 默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。（如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响）
  慢查询日志支持将日志记录写入文件和数据库表。

二、在数据库中设置SQL慢查询

1. 开启慢查询

   • 方式一：

     修改配置文件。在 my.ini 增加几行：**主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）

     [mysqlld]
     ;定义查过多少秒的查询算是慢查询（比如2秒）
     long_query_time=2
     
     ;5.0、5.1等版本配置如下选项
     log_slow_queries='mysql_slow_query.log'
     ;5.5以上版本配置如下选项
     slow_query_log='ON'
     slow_query_log_file='mysql_slow_query.log'
     
     ;记录没有使用索引查询语句
     log-queries-not-using-indexe
     

     MySQL和慢查询相关的系统变量如下：

     参数	含义
     slow_query_log	是否启用慢查询日志， ON为启用，OFF为没有启用，默认为OFF
     log_output	日志输出位置，默认为FILE，即保存为文件，若设置为TABLE，则将日志记录到mysql.show_log表中，支持设置多种格式
     slow_query_log_file	指定慢查询日志文件的路径和名字
     long_query_time	执行时间超过该值才记录到慢查询日志，单位为秒，默认为10

   • 方式二：

     通过MySQL数据库开启慢查询。

     mysql>set global slow_query_log = ON;
     mysql>set global long_query_time = 3600;
     mysql>set global log_queries_not_using_indexes = ON;
     

2. 分析慢查询日志

   直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句

   explain SQL语句
   

   比如：

​ explain的参数：

3. 常见的慢查询优化

   1. 索引没起作用的情况

      • 使用like关键字的查询语句时“%”不在第一个字段
      • 使用多列索引时遵从最左原则

   2. 优化数据库结构

      • 将字段很多的表分解成多个表

      • 增加中间表

        对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
        

   3. 分解关联查询

      例如：
      SELECT * FROM tag 
              JOIN tag_post ON tag_id = tag.id
              JOIN post ON tag_post.post_id = post.id
              WHERE tag.tag = 'mysql';
      #分解为：
      SELECT * FROM tag WHERE tag = 'mysql';
      SELECT * FROM tag_post WHERE tag_id = 1234;
      SELECT * FROM post WHERE post.id in (123,456,567);
      
      

   4. 优化limit分页

   5. 分析具体的SQL语句

三、定位慢查询

1、查看慢查询日志确定已经执行完的慢查询

• 确定慢查询日志路径（默认是 MySQL 的数据目录）

  mysql> show global cariables like "datadir";
  

• 确定慢查询日志的文件名

  show global variables like "slow_query_log_file";
  

• 获取慢日志文件已经执行完的慢查询

  tail -n5 log文件路径
  

2、show processlist 查看正在执行的慢查询

​ 有时慢查询正在执行，已经导致数据库负载偏高了，而由于慢查询还没执行完，因此慢查询日志还看不到任何语句。此时可以使用 show processlist 命令判断正在执行的慢查询。show processlist 显示哪些线程正在运行。如果有 PROCESS 权限，则可以看到所有线程。否则，只能看到当前会话的线程。

如果不使用 FULL 关键字，在 info 字段中只显示每个语句的前 100 个字符，如果想看语句的全部内容可以使用 full 修饰（show full processlist）。

mysql> show processlist\G`

`*************************** 10. row ***************************`

   `Id: 7651833`

   `User: one`

   `Host: 192.168.1.251:52154`

   `db: ops`

   `Command: Query`

   `Time: 3`

  `State: User sleep`

  `Info: select sleep(10)`

`......`

`10 rows in set (0.00 sec)`

Time表示执行时间，Info表示SQL语句。

参考资料：https://zhuanlan.zhihu.com/p/149338523#:~:text=MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。,具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。 long_query_time的默认值为10，意思是记录运行10秒以上的语句。
https://www.jianshu.com/p/7c9e2c479c15