MySQL开启慢查询定位优化SQL

启用慢查询日志

  要想优化查询SQL语句时，首先要清楚我们应该优化哪些SQL？毫无疑问，肯定是要优化查询时间长的SQL语句。假设一条SQL语句的执行时间是：1s，5s，10s。。。哪些算是查询时间较长的呢？

  这涉及到如何定义查询时间过长？以及在哪个地方能够搜集查看到这些查询时间过长的SQL？只有清楚了这2个问题，我们才能够有针对性的优化SQL语句。

  在MySQL中，只需要简单的设置3个变量就可以；

• long_query_time：它的值是数字（默认值10），单位秒；SQL查询时间超过该数字，那么SQL将会被定义为查询时间过长记录在慢查询日志里；
• slow_query_log ：ON/OFF ; ON :表示开启慢查询日志，SQL查询时间超过long_query_time定义的时间，就会被记录在日志里； OFF ：表示关闭慢查询日志；
• slow_query_log_file：日志的路径；也有默认值；

• 查询

查询变量
show variables like ‘%变量名%’ ;

• 设置

设置变量
set long_query_time=0.1
set global slow_query_log = on;

慢查询日志

D:\IT_SOFT\DATABASE\MySQL\mysql\bin\mysqld, Version: 8.0.19 (MySQL Community Server - GPL). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 2022-07-18T04:39:05.766496Z
# User@Host: root[root] @ localhost [::1]  Id:     8
# Query_time: 0.349242  Lock_time: 0.000339 Rows_sent: 14  Rows_examined: 14
use ssm01;
SET timestamp=1658119145;
select * from books;

  这是在使用：select * from books;查询时，产生的慢查询日志；设置的long_query_time=0.1s, 因此超过这个时间的查询SQL都会被记录在这个日志里 ；

• Query_time: 表示SQL查询时间。
• Lock_time：表示等待获取锁的时间。
• Rows_sent: 表示查询结果的行数；
• Rows_examined：表示执行这段SQL扫描的行数；

  SQL的实际执行时间：exeTime = Query_time + Lock_time；有一种情况会导致SQL运行时间很长但是不会被记录到慢日志中：如果等待获取锁的时间很长，但是查询时间很短没有超过long_query_time，这种情况SQL是不会被记录在慢日志中；如果生产环境遇到这种情况，就要考虑其他原因了；
  在实际环境中，可能会产生一个很大的慢日志文件，如果这样直接看日志文件可能不太方便，因此MySQL自带了一个分析慢日志文件的工具：mysqldumpslow；在windows平台还要安装perl环境才能运行mysqldumpslow；(windows平台使用mysqldumpslow)

  在确定了哪些SQL需要优化之后，用explain,查看SQL的执行计划，重点关注type，key，Extra；

• key表示用到的索引，
• type表示索引类型，几种索引类型的效率比较：system > const>eq_ref>ref>range>index>all
• Extra包含MySQL解决查询的额外信息；