📚 课程目录第2章慢查询根因分析基础

2. 慢查询根因分析基础：数据库执行计划解读、慢查询日志配置与采集、常见慢SQL模式

慢查询根因分析是数据库性能优化的起点。本章聚焦三个核心基础：如何读懂数据库执行计划、如何配置与采集慢查询日志、以及识别最常见的慢SQL模式。掌握这些基础，才能在后继章节中精准定位延迟根因。

2.1 数据库执行计划解读

执行计划是数据库优化器为SQL语句生成的执行步骤树。通过解读执行计划，可以判断查询是否走了正确的索引、扫描了多少行数据、是否存在排序或临时表等低效操作。

2.1.1 执行计划核心概念

概念	说明	性能影响
全表扫描	顺序读取表中所有数据页	极高IO，大表不可接受
索引扫描	按B+树索引顺序读取	中等，取决于索引选择性
索引查找	通过索引键值精确定位行	低延迟，最优路径
回表	通过二级索引找到主键后，再回主表取数据	额外IO，覆盖索引可避免
临时表	排序或分组时在内存/磁盘创建临时结构	高延迟，应尽量避免
嵌套循环连接	驱动表逐行匹配被驱动表	小表驱动大表时高效
哈希连接	建立哈希表进行匹配	大表等值连接高效

2.1.2 执行计划解读流程

获取执行计划：MySQL使用 EXPLAIN 或 EXPLAIN ANALYZE；PostgreSQL使用 EXPLAIN (ANALYZE, BUFFERS)。
关注type列：从 system > const > eq_ref > ref > range > index > ALL，越靠左性能越好。
检查rows列：优化器预估扫描行数，实际扫描行数可通过 EXPLAIN ANALYZE 获得。
查看Extra列：出现 Using filesort、Using temporary、Using index condition 等关键词时需警惕。
对比实际耗时：使用 EXPLAIN ANALYZE 获取每个步骤的实际执行时间和循环次数。

  关键指标速查： 执行计划中 rows 与 filtered 的乘积表示最终返回行数预估。若 rows 远大于实际返回行数，说明索引选择性差，需要优化索引或改写SQL。

2.1.3 执行计划示例解读

-- MySQL EXPLAIN 输出示例
EXPLAIN SELECT o.order_id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PAID' AND o.created_at > '2024-01-01'
ORDER BY o.amount DESC
LIMIT 100;

-- 输出关键列解读：
-- id: 1
-- select_type: SIMPLE
-- table: o (orders)
-- type: ref
-- possible_keys: idx_status_created, idx_customer_id
-- key: idx_status_created
-- rows: 50000
-- filtered: 33.33
-- Extra: Using where; Using index condition; Using filesort

上述计划中 Using filesort 表示MySQL在内存或磁盘中进行了排序，若数据量大则延迟显著。优化方向：创建 (status, created_at, amount) 的覆盖索引，消除排序和回表。

2.2 慢查询日志配置与采集

慢查询日志是数据库自动记录的、执行时间超过阈值的SQL语句。正确配置和采集慢查询日志，是发现性能瓶颈的第一道防线。

2.2.1 MySQL慢查询日志配置

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

-- 动态配置（重启失效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 1;          -- 单位：秒，建议生产环境设为0.5~1
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询

-- 持久化配置（my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1            -- 记录慢管理语句
log_slow_slave_statements = 1            -- 记录从库慢语句

2.2.2 慢查询日志采集与分析工具

工具	功能	适用场景
mysqldumpslow	MySQL自带，汇总慢查询日志	快速查看TOP N慢查询
pt-query-digest	Percona Toolkit，深度分析查询指纹	生产环境慢查询根因分析
Performance Schema	MySQL内置性能监控，实时采集	实时监控与历史趋势分析
sys schema	MySQL 5.7+，提供预定义视图	快速定位IO/锁等待

采集最佳实践： 生产环境建议将 long_query_time 设置为0.5秒，并开启 log_queries_not_using_indexes。使用 pt-query-digest 定期分析慢查询日志，按查询指纹（Query Fingerprint）聚合，关注总耗时和平均耗时两个维度。

2.2.3 慢查询日志示例

# Time: 2024-06-15T10:23:45.123456Z
# User@Host: app_user[app_user] @  [10.0.1.100]  Id: 12345
# Query_time: 2.345678  Lock_time: 0.000123  Rows_sent: 100  Rows_examined: 500000
SET timestamp=1718447025;
SELECT o.order_id, o.amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PAID'
ORDER BY o.created_at DESC
LIMIT 100;

上述日志中 Query_time: 2.34s 远超阈值，Rows_examined: 500000 但只返回100行，说明扫描了大量无效数据，索引缺失或查询条件不够精确。

2.3 常见慢SQL模式

根据生产环境经验，90%的慢查询可归为以下五种模式。识别这些模式，能快速缩小根因范围。

2.3.1 五种常见慢SQL模式

模式	典型特征	根因	优化方向
全表扫描	type=ALL，rows=全表行数	无索引或索引失效	添加合适索引，避免函数包裹索引列
深度分页	LIMIT offset, size，offset极大	扫描大量无效行	使用游标分页或子查询优化
大表JOIN无索引	Using join buffer (Block Nested Loop)	被驱动表连接列无索引	确保被驱动表连接列有索引
排序无索引	Using filesort，rows大	ORDER BY列无索引	创建覆盖索引包含排序列
临时表聚合	Using temporary，GROUP BY/ DISTINCT	分组列无索引或数据量大	索引优化或使用汇总表

2.3.2 深度分页优化示例

-- 慢SQL：深度分页
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 优化方案1：游标分页（推荐）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 优化方案2：子查询延迟关联
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) tmp
ON o.id = tmp.id;

注意： 游标分页要求排序列唯一且递增，适用于主键或时间戳场景。子查询延迟关联适用于任何排序列，但需要确保子查询本身高效。

2.4 本章小结

执行计划是慢查询分析的显微镜，重点看 type、rows、Extra 三列。
慢查询日志配置要合理，生产环境建议 long_query_time=0.5~1秒，并开启 log_queries_not_using_indexes。
常见慢SQL模式包括全表扫描、深度分页、大表JOIN无索引、排序无索引、临时表聚合，每种模式都有对应的优化策略。
掌握这些基础，后续章节将深入 Kafka消费者尾延迟 场景，结合数据库慢查询进行联合根因定位。