简单的MySQL慢查询日志实战
MySQL的慢查询日志是一种记录数据库中执行时间超过特定阈值的查询语句的日志文件。当查询的执行时间超过预先设定的阈值时，MySQL会将该查询的相关信息记录到慢查询日志中，以便数据库管理员进行性能分析和优化。
查看实例慢SQL配置
可以通过以下语句查看MySQL实例慢SQL相关的设置：
SHOW VARIABLES LIKE '%query%';
其中：
-
long_query_time：慢sql阈值，超过此时间的query将会被写到慢日志 -
slow_query_log：是否开启慢日志 -
slow_query_log_file：慢日志存放位置
也可以在MySQL控制台执行以下语句即可查看慢查询执行的次数：
show status like '%slow_queries%'
开启配置
临时生效
- 登录到MySQL服务器。
- 设置慢查询阈值（这里以0.2秒为例）：
SET GLOBAL long_query_time = 0.2;
- 启用慢查询日志（如果尚未启用）：
SET GLOBAL slow_query_log = 'ON';
- 可以选择指定慢查询日志文件的位置：
SET GLOBAL slow_query_log_file = '/path/to/your/log/file.log';
注意：设置全局变量时使用GLOBAL关键字，会影响所有新的连接。如果你只想对当前会话生效，可以去掉GLOBAL关键字。
永久生效
如果你想让这些设置在MySQL服务重启后依然有效，可以在MySQL配置文件（通常是my.cnf或my.ini）中进行设置：
[mysqld]
long_query_time=0.1
slow_query_log=ON
slow_query_log_file=/path/to/your/log/file.log
保存配置文件，然后重启MySQL服务。
实战
创建测试数据库和表格：
CREATE DATABASE IF NOT EXISTS slow_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE slow_db;
CREATE TABLE IF NOT EXISTS slow_db.slow_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age TINYINT UNSIGNED NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
使用存储过程灌入测试数据：
DELIMITER $$
CREATE PROCEDURE InsertLoop()
BEGIN
DECLARE v_counter INT DEFAULT 1;
DECLARE randomAge INT;
WHILE v_counter <= 1000000 DO
SET randomAge = FLOOR(1 + RAND() * 100);
INSERT INTO slow_table(name,age) VALUES(CONCAT("name",v_counter),randomAge);
SET v_counter = v_counter + 1;
END WHILE;
END$$
DELIMITER ;
CALL InsertLoop();
执行一条未走索引的sql：
select * from slow_table WHERE `name` = 'name1000000';
这条语句在我本地执行了0.23s，达到了之前设置的慢查询阈值，所以这条sql执行记录会被插入到慢日志中：
TCP Port: 3306, Named Pipe: (null)
Time Id Command Argument
# Time: 240425 7:36:20
# User@Host: root[root] @ localhost [::1]
# Thread_id: 3 Schema: slow_db QC_hit: No
# Query_time: 0.233478 Lock_time: 0.001243 Rows_sent: 1111 Rows_examined: 1000000
# Rows_affected: 0 Bytes_sent: 49925
use slow_db;
SET timestamp=1714001780;
select * from slow_table WHERE `name` LIKE 'name945%';
可以看到日志中记录了该条慢sql执行的时间（Query_time）以及扫描了多少行（Rows_examined），之后可以拿这条慢sql使用explain进一步的分析：
EXPLAIN select * from slow_table WHERE `name` = 'name1000000';
执行结果如下：
explain分析得出该条sql未走索引导致全表扫描，那么就可以基于该分析结果给对应字段加上索引：
ALTER TABLE slow_table ADD INDEX idx_name (name);
再执行一遍之前的慢sql：
发现执行时间从0.23s缩短到了0.047s，索引的效率提升效果还是很明显的！
再做一遍explain：
发现之前走全表扫描的sql现在走了索引，至此优化结束。
附explain的extra字段类型：
- Using index：使用覆盖索引
- Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
- Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
- Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
- Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。
总结
本文简单的讲解了使用慢日志排查慢sql的步骤，实际工作中造成慢sql的情况往往更为复杂，但是排查步骤大差不差。