MySQL慢查询日志入门：从开启到性能优化详解
MySQL慢查询日志入门：从开启到性能优化详解
在数据库运维过程中，当遇到程序请求缓慢的问题时，通过分析MySQL慢查询日志可以快速定位问题。本文将详细介绍如何开启慢查询日志、设置慢查询的临界值、解析慢查询语句以及使用性能详情功能，帮助读者全面掌握MySQL慢查询日志的使用方法。
一、如何开启慢查询日志
1. 查看慢查询日志是否开启
执行命令：show variables like 'slow%'。
得到以下结果：
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+--------------------------------+
可以看到slow_query_log属性是OFF，处于关闭状态，那么我们需要先开启慢查询。
slow_query_log_file表示慢查询日志文件的存放路径，我们可以自定义文件路径：
set global slow_query_log_file = '路径';
2. 开启慢查询日志
执行命令：set global slow_query_log = on。
然后再查询一下，发现slow_query_log处于开启状态：
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/log/mysql/mysql-slow.log |
+---------------------+--------------------------------+
开启了之后，是不是所有的查询都会记录在文件里呢？
当然不是，慢查询日志，顾名思义是只记录查询比较慢的语句，那问题又来了，怎么才算查询比较慢的语句呢？
实际上，这里会有一个标准值，而且这个标准值是可以由我们自己设定的。
3. 慢查询的临界值设定
首先查看一下默认的临界值。
执行命令：show variables like '%long%'。
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
其中有一个long_query_time属性，它的值为10.000000。它表示的意思是，只记录查询时间在10s以上的语句。
显然10s我们是不可接受的，所以我们需要自己设定一下这个值。因为我自己的测试表中只有10w条数据，查询很快，所以这里我们设置的小一点。如果有条件的话，最好设置一个百万级的表进行测试。
我们把慢查询的临界值设置为0.02：
set long_query_time=0.02;
可以看到现在临界值是0.02秒了。
现在来模拟查询时间小于0.01和大于0.01的两个查询，看是否都记录在了慢查询日志中。
-- 查询时间小于0.01秒
SELECT * FROM test_table WHERE id = 1;
-- 查询时间大于0.01秒
SELECT * FROM test_table WHERE name LIKE '%test%';
然后看一下日志文件中的数据：
# Time: 2023-04-01T12:34:56.789
# User@Host: root[root] @ localhost []
# Thread_id: 123
# Query_time: 0.02345
# Rows_sent: 1
# Rows_examined: 100000
SET timestamp=1617232496;
SELECT * FROM test_table WHERE name LIKE '%test%';
可以看到只有第二条查询的日志。
需要注意的是，我们上面的操作是在交互界面进行的，如果数据库进行重启，这些设置都会失效。如果要永久生效，需要修改配置文件：
$ vi /etc/my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 0.1
slow_query_log_file =/usr/local/mysql/mysql_slow.log
在配置文件中加上这三行就可以了。主要要重启mysql才能生效！
二、慢查询语句解析
我们通过慢查询日志，可以定位到是哪一条语句查询比较慢，找到这条语句之后，如何去分析它慢的原因呢？
1. 最简单的方法，可以通过explain解析
执行命令：explain (sql语句)。
我们把上面执行的两条语句放一起对比解析一下：
explain SELECT * FROM test_table WHERE id = 1;
explain SELECT * FROM test_table WHERE name LIKE '%test%';
需要重点关注possible_keys、key、rows这几个属性值。
possible_keys表示该语句可能会用到的索引。key表示该语句实际用到的索引。rows表示该语句扫描的行数。
通过这些属性，我们可以大致的分析一下，第一条语句没有走索引，它扫描了9万多行数据，所以查询速度比较慢，而第二条语句走了主键索引，仅仅扫描了一条语句，所以它的执行速度比较快。这样我们就可以快速定位到问题，然后针对性的去解决。
三、开启性能详情
如果通过上面的语句解析没有定位到问题，我该加的索引也加了，但是还是比较慢，那就可以通过性能详情来进一步的探究一下原因。
性能详情可以追踪查询语句的整个生命周期的状态，有了这些状态值，就可以从更深层次找出具体是哪个环节慢了，从而能针对性的进行改善。
1. 查看性能详情是否开启
执行命令：show variables like '%profiling%'。
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| profiling | OFF |
| profiling_history_size | 15 |
+----------------+-------+
可以看到profiling属性值为OFF，表示关闭，那么我们先开启它。
执行命令：set profiling = on。
这样就开启了。开启之后，我们就可以执行查询语句，mysql会自动的保存性能记录。
2. 查看性能记录
我们执行一条sql语句：
SELECT * FROM test_table WHERE name LIKE '%test%';
然后查看性能记录：
执行命令：show profiles。
可以看到开启后的所有查询语句的记录。我们想查看一下第二条执行语句的整个执行周期的状态详情：
执行命令：show profile for query 2。
可以看到整个执行过程每个状态的耗时情况。然后定位到具体是哪个状态最耗时，然后针对性的排查原因。