MySQL 慢查询日志详解
慢查询日志（Slow Query Log）是 MySQL 提供的一种核心性能优化工具，用于记录执行时间超过指定阈值的 SQL 语句。通过分析这些日志，可以定位数据库性能瓶颈，优化低效查询，提升系统整体效率。
一、慢查询日志的作用
- 性能瓶颈定位
识别执行时间过长的 SQL 语句，帮助找出数据库性能问题的根源（如索引缺失、复杂查询、全表扫描等）。 - 优化依据
提供真实的 SQL 执行数据（如执行时间、锁等待时间、扫描行数等），为优化提供依据。 - 系统监控
监控数据库长期性能变化趋势，及时发现潜在问题。 - 索引优化
记录未使用索引的查询（需配置log_queries_not_using_indexes），帮助发现索引缺失或无效使用的情况。
二、如何开启慢查询日志
1. 配置文件设置（永久生效）
编辑 MySQL 配置文件（my.cnf 或 my.ini），在 [mysqld] 段中添加以下参数：
[mysqld]
slow_query_log = 1 # 开启慢查询日志（1 表示开启）
slow_query_log_file = /path/to/slow.log # 指定日志文件路径
long_query_time = 2 # 定义慢查询阈值（单位：秒，默认 10 秒）
log_queries_not_using_indexes = 1 # 记录未使用索引的查询（可选）
log_output = FILE # 日志输出方式（FILE 或 TABLE）
保存后重启 MySQL 服务：
# Linux 系统
sudo systemctl restart mysql
2. 动态设置（临时生效）
通过 MySQL 命令行临时启用（重启后失效）：
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询阈值（需重新连接会话才能生效）
SET GLOBAL long_query_time = 2;
-- 指定日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 1;
3. 验证配置
执行以下命令检查配置是否生效：
SHOW VARIABLES LIKE 'slow_query%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
三、核心参数详解
| 参数名 | 类型 | 默认值 | 说明 |
|---|---|---|---|
slow_query_log | Boolean | OFF | 慢查询日志总开关 |
slow_query_log_file | String | hostname-slow.log | 慢查询日志文件路径 |
long_query_time | Float | 10 | 慢查询阈值（单位：秒） |
log_queries_not_using_indexes | Boolean | OFF | 是否记录未使用索引的查询 |
min_examined_row_limit | Integer | 0 | 记录扫描行数超过该值的查询 |
log_slow_admin_statements | Boolean | OFF | 是否记录慢管理语句（如 ALTER/ANALYZE） |
log_slow_slave_statements | Boolean | OFF | 是否记录从库慢查询 |
四、日志内容与格式
慢查询日志记录了以下信息：
- SQL 语句：被记录的查询语句。
- 执行时间（Query_time）：SQL 执行的总时间。
- 锁等待时间（Lock_time）：SQL 等待锁的时间。
- 返回行数（Rows_sent）：返回给客户端的行数。
- 扫描行数（Rows_examined）：SQL 扫描的行数。
- 用户信息：执行查询的用户和主机信息。
- 时间戳：查询执行的日期和时间。
示例日志内容：
# Time: 2025-06-30T17:50:07.123456Z
# User@Host: root[root] @ localhost []
# Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 100 Rows_examined: 10000
SET timestamp=1720000000;
SELECT * FROM users WHERE created_at > '2025-01-01';
五、日志分析方法
1. 直接查看日志文件
使用命令行工具查看日志文件：
# 查看日志内容
less /var/log/mysql/slow.log# 统计慢查询数量
grep -c "Query_time" /var/log/mysql/slow.log
2. 使用 MySQL 自带工具 mysqldumpslow
mysqldumpslow 可以汇总和排序慢查询日志中的 SQL 语句。
# 按执行时间排序，显示前10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log# 按查询次数排序，显示前10条
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
3. 使用第三方工具 pt-query-digest
Percona Toolkit 中的 pt-query-digest 提供更详细的分析报告：
pt-query-digest /var/log/mysql/slow.log > analysis_report.txt
4. 查询 mysql.slow_log 表
如果日志输出到表（log_output=TABLE），可通过 SQL 直接查询：
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;
六、优化慢查询的典型方法
-
添加索引
对频繁查询的字段（如WHERE、JOIN、ORDER BY）添加索引。
示例：CREATE INDEX idx_created_at ON users(created_at); -
优化 SQL 语句
- 避免全表扫描（使用
EXPLAIN分析执行计划）。 - 减少子查询，改用
JOIN。 - 避免
SELECT *，仅查询必要字段。
- 避免全表扫描（使用
-
调整配置参数
- 根据业务需求调整
long_query_time（例如设置为 1 秒）。 - 启用
log_queries_not_using_indexes以发现未使用索引的查询。
- 根据业务需求调整
-
分页与缓存
- 对高频查询结果进行缓存（如 Redis）。
- 使用分页限制返回行数（如
LIMIT 100）。
七、常见问题与注意事项
-
日志文件过大
- 定期归档或清理旧日志，避免磁盘空间不足。
- 使用
log_output=TABLE将日志存储到数据库表中，便于管理。
-
生产环境配置建议
- 阈值设置：默认阈值为 10 秒，生产环境中建议调低（如 1-2 秒）以捕获更多潜在问题。
- 权限问题：确保 MySQL 有权限写入日志文件路径。
- 性能影响：慢查询日志本身会带来轻微性能开销，建议在测试环境调试，生产环境谨慎使用。
-
未使用索引的查询
启用log_queries_not_using_indexes后，日志可能快速增长，需结合EXPLAIN分析是否需要添加索引。
八、实战案例
案例 1：索引优化
问题：查询 created_at 范围内的用户列表耗时较长。
SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';
优化：在 created_at 字段上创建索引。
CREATE INDEX idx_created_at ON users(created_at);
效果：查询时间从 5 秒降至 0.1 秒。
案例 2：复杂查询优化
问题：关联查询订单和用户表时响应时间过长。
SELECT o.id, o.total
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31';
优化：
-
在
order_date字段上创建索引：CREATE INDEX idx_order_date ON orders(order_date); -
使用覆盖索引优化查询：
SELECT o.id, o.total FROM orders o JOIN users u ON o.user_id = u.id WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31' USE INDEX (idx_order_date);效果：查询时间从 3 秒降至 0.3 秒。
九、总结
慢查询日志是 MySQL 性能优化的核心工具，通过合理配置和分析，可以显著提升数据库效率。建议结合 EXPLAIN、索引优化和 SQL 重写策略，持续监控和调优数据库性能。