6.4.3 慢查询日志开启、分析与优化流程

慢查询日志开启、分析与优化流程#

一、慢查询日志作用与适用场景#

慢查询日志记录执行时间超过阈值或未使用索引的SQL，用于定位性能瓶颈、发现慢SQL、评估索引与参数是否合理。适用于线上性能排查、容量规划、基线性能建立与回归验证。

原理草图：慢查询闭环

文章图片

二、开启与关键参数配置#

1）配置文件方式（推荐）
- 文件路径示例：/etc/my.cnf 或 /etc/mysql/my.cnf

[mysqld]
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/slow.log
long_query_time=1
log_queries_not_using_indexes=ON
log_slow_admin_statements=ON
log_slow_slave_statements=ON
min_examined_row_limit=1000

2）命令行动态调整（临时生效）

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
-- 设置阈值为0.5秒
SET GLOBAL long_query_time = 0.5;
-- 记录未使用索引的SQL
SET GLOBAL log_queries_not_using_indexes = ON;
-- 查看当前生效值
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

3）验证与预期效果

# 验证日志文件是否生成
ls -lh /var/lib/mysql/slow.log

# 运行一个慢SQL（示例：对大表无索引扫描）
mysql -uroot -p -e "SELECT COUNT(*) FROM big_table WHERE non_index_col='x';"
# 预期：slow.log中出现该SQL记录

三、日志采集与分析方法#

1）实时观察

tail -f /var/lib/mysql/slow.log
# 预期：可看到# Time、# Query_time、# Rows_examined等字段

2）mysqldumpslow 基础汇总

# 按查询时间排序，展示Top 10
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# 参数说明：
# -s t 按时间排序，-t 10 输出前10条

3）pt-query-digest 深度分析（需要安装）
- 安装（CentOS/RHEL）：

yum install -y percona-toolkit
  • 分析命令与输出：
pt-query-digest /var/lib/mysql/slow.log > /tmp/slow_report.txt
# 预期：报告包含Query_time分布、SQL指纹、排名、样本SQL

4）分析维度检查清单
- 执行时间（Query_time）
- 锁等待（Lock_time）
- 扫描行数（Rows_examined）
- 返回行数（Rows_sent）
- 索引使用（EXPLAIN/EXPLAIN ANALYZE）

四、慢SQL优化流程#

1）确认SQL语义与业务正确性

-- 先确认语义正确，避免冗余条件
SELECT * FROM orders WHERE status='PAID' AND create_time > NOW()-INTERVAL 7 DAY;

2）执行计划分析

EXPLAIN SELECT * FROM orders WHERE status='PAID' AND create_time > NOW()-INTERVAL 7 DAY;
-- 关注type、rows、key、Extra（Using where/Using filesort）

3）索引优化示例

-- 添加复合索引（status+create_time）
ALTER TABLE orders ADD INDEX idx_status_ctime(status, create_time);

4）查询改写示例

-- 优化前：SELECT * 全列返回
SELECT * FROM orders WHERE status='PAID' LIMIT 1000;

-- 优化后：只取必要列，减少IO
SELECT id, user_id, amount FROM orders WHERE status='PAID' LIMIT 1000;

5）参数与资源调整示例

[mysqld]
innodb_buffer_pool_size=4G
tmp_table_size=256M
sort_buffer_size=4M

6）回归验证

# 压测或回归脚本执行后对比
pt-query-digest /var/lib/mysql/slow.log > /tmp/slow_report_after.txt
# 预期：Top SQL耗时下降、慢SQL数量减少

五、运维管控与落地实践#

1）阈值分级策略示例
- 线上：long_query_time=0.5~1
- 测试：long_query_time=0.2~0.5

2）日志轮转（logrotate）

cat > /etc/logrotate.d/mysql-slow <<'EOF'
/var/lib/mysql/slow.log {
  daily
  rotate 7
  missingok
  notifempty
  create 640 mysql mysql
  postrotate
    /usr/bin/mysql -e "FLUSH SLOW LOGS;"
  endscript
}
EOF

# 立即测试轮转配置
logrotate -d /etc/logrotate.d/mysql-slow

3）联动监控告警（示例）
- 指标：慢SQL数量、Top SQL变更、总耗时
- 工具：Prometheus + exporter 或定时脚本解析报告

六、常见问题与排查#

1）慢查询日志未记录

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';
  • 可能原因：slow_query_log=OFF，或日志路径无写权限
  • 排错：检查文件权限
ls -l /var/lib/mysql/slow.log
chown mysql:mysql /var/lib/mysql/slow.log

2）日志爆量
- 解决：调高阈值、设置 min_examined_row_limit、关闭 log_queries_not_using_indexes

SET GLOBAL long_query_time=1.5;
SET GLOBAL min_examined_row_limit=5000;
SET GLOBAL log_queries_not_using_indexes=OFF;

3）优化后仍慢
- 继续排查锁等待、IO瓶颈、网络延迟与并发控制

SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

七、动手练习#

  1. 练习开启慢查询日志
    - 目标：将 long_query_time 设置为 0.5 秒，确认 slow.log 产生记录。
  2. 练习分析
    - 使用 mysqldumpslow 输出Top 5慢SQL，并解释输出字段。
  3. 练习优化
    - 对一条慢SQL添加索引或改写查询，并对比优化前后执行时间。