数据库慢查询排查实战：从执行计划到索引优化
一、别急着加索引：先看懂执行计划
线上 P99 延迟飙高时，慢查询日志是排查的第一入口。但“发现慢查询”和“解决慢查询”之间，隔着从执行计划分析到索引策略设计的完整链路。很多工程师的第一反应是“加个索引”，但错误的索引不仅无法提速，反而会拖慢写入、浪费空间，甚至因为优化器选错索引导致查询更慢。
更深层的原因往往不是“缺索引”，而是“索引没被用对”——隐式类型转换导致失效、联合索引列序不匹配、统计信息陈旧导致行数估算偏差。这些问题靠简单加索引解决不了，必须深入理解查询优化器的工作机制。
本文将从慢查询定位方法出发，拆解执行计划的关键指标，给出索引设计的量化决策框架，并分析常见索引陷阱的底层成因。
二、一条 SQL 的执行路径：从文本到磁盘 I/O
理解慢查询根因，先厘清一条 SQL 从解析到返回结果的完整路径。
flowchart TD
A[SQL 文本输入] --> B[Parser: 语法解析 → AST]
B --> C[Preprocessor: 语义检查/视图展开]
C --> D[Optimizer: 查询优化]
D --> D1[基于代价的优化 CBO]
D --> D2[基于规则的优化 RBO]
D1 --> E[生成执行计划: 选择索引/连接顺序/访问路径]
E --> F[Executor: 执行引擎]
F --> G{访问路径}
G -->|索引覆盖扫描| H[直接从索引返回数据，无需回表]
G -->|索引范围扫描 + 回表| I[索引定位主键 → 聚簇索引回表取完整行]
G -->|全表扫描| J[顺序读取聚簇索引所有数据页]
H --> K[返回结果集]
I --> K
J --> K
D1 -->|依赖统计信息| L[(统计信息: 行数/基数/数据分布)]
L -->|统计信息过时| M[行数估计偏差 → 索引选择错误]
style D1 fill:#4ecdc4,color:#fff
style I fill:#ff6b6b,color:#fff
style J fill:#ff6b6b,color:#fff
style M fill:#ffe66d,color:#333
执行计划核心指标：
- type（访问类型）：从最优到最差依次为
const > eq_ref > ref > range > index > ALL。ALL表示全表扫描，是优化的首要目标。 - rows（预估扫描行数）：优化器基于统计信息估算的扫描行数，与实际行数的偏差直接反映统计信息的准确性。
- Extra（额外信息）：
Using index表示索引覆盖（最优），Using filesort表示额外排序（需优化），Using temporary表示使用了临时表（严重警告）。
三、慢查询定位与索引优化实战
3.1 定位：从日志到执行计划的三步法
-- Step 1: 开启慢查询日志，设定阈值
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1; -- 100ms 以上记录为慢查询
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未使用索引的查询
-- Step 2: 分析慢查询日志，找到 Top N 高频慢查询
-- 使用 mysqldumpslow 或 pt-query-digest 工具
-- pt-query-digest 输出示例：
-- Rank Query ID Response time Calls R/Call
-- ==== ================ ============== ====== ======
-- 1 0x3F8E1A2B... 1250.1234 62% 3421 0.37 -- 占总响应时间 62%
-- 2 0x7C9D4E5F... 430.5678 21% 1856 0.23
-- Step 3: 对 Top 1 慢查询获取执行计划
EXPLAIN ANALYZE
SELECT o.order_id, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.created_at BETWEEN '2026-01-01' AND '2026-06-30'
AND o.status = 'pending'
AND c.region = 'east'
ORDER BY o.total_amount DESC
LIMIT 100;
-- 典型问题输出：
-- -> Nested loop inner join (cost=125000 rows=850000) (actual time=0.12..3800ms rows=823000)
-- -> Filter: (c.region = 'east') (cost=12000 rows=50000) (actual time=0.08..120ms rows=48500)
-- -> Table scan on customers (cost=12000 rows=500000) (actual time=0.05..80ms rows=500000)
-- -> Filter: (o.status = 'pending') (cost=2.5 rows=17) (actual time=0.02..0.06ms rows=16)
-- -> Index lookup on orders using idx_customer (customer_id=...) (cost=2.5 rows=17)
问题诊断： customers 表缺少 region 索引，导致全表扫描 50 万行后再过滤。orders 表虽有 idx_customer 索引，但 status 过滤条件未被索引覆盖，回表后仍需大量过滤。
3.2 索引设计：量化决策而非经验猜测
-- 优化方案一：为 customers.region 添加索引
ALTER TABLE customers ADD INDEX idx_region (region);
-- 效果：customers 扫描行数从 500000 降至 ~50000（region 基数约 10）
-- 优化方案二：为 orders 创建覆盖索引，消除回表
-- 将 WHERE + ORDER BY 的列全部纳入索引，实现索引覆盖扫描
ALTER TABLE orders ADD INDEX idx_status_created_amount (
status,
created_at,
total_amount,
customer_id,
order_id
);
-- 索引列序设计原则：
-- 1. status（等值过滤，选择性最高，放最左）
-- 2. created_at（范围过滤，等值列之后）
-- 3. total_amount（排序，范围列之后）
-- 4. customer_id, order_id（覆盖列，避免回表）
-- 优化后的执行计划：
-- -> Nested loop inner join (cost=8500 rows=820) (actual time=0.15..45ms rows=823)
-- -> Index scan on customers using idx_region (cost=500 rows=48500) (actual time=0.08..25ms rows=48500)
-- -> Index lookup on orders using idx_status_created_amount (cost=0.17 rows=0.017) (actual time=0.001..0.003ms rows=0.017)
-- 总耗时从 3800ms 降至 45ms，提升 84 倍
索引列序的量化决策框架：
| 规则 | 原因 | 违反后果 |
|---|---|---|
| 等值条件列放最左 | 等值过滤可精确定位 B+Tree 节点 | 范围列在左时，等值列无法利用索引有序性 |
| 范围条件列在等值列之后 | 范围扫描后的列无法保持有序 | 范围列之后的等值过滤退化为回表过滤 |
| 排序列与过滤列同索引 | 利用索引有序性避免 filesort | 排序列不在索引中触发额外排序 |
| 覆盖列放最后 | 仅用于避免回表，不参与过滤/排序 | 覆盖列插入中间会破坏索引有序性 |
3.3 统计信息维护：优化器的眼睛不能近视
-- 检查统计信息的准确性
-- 对比优化器预估行数与实际行数
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';
-- 若预估 rows=1000 但 actual rows=850000，说明统计信息严重过时
-- 手动更新统计信息
ANALYZE TABLE orders;
-- 生产环境建议：设置自动统计信息收集
-- MySQL 8.0+ 默认启用 innodb_stats_auto_recalc
-- 但默认阈值是表数据变化 10% 才触发，对大表可能不够及时
SET GLOBAL innodb_stats_auto_recalc = ON;
-- 对于关键业务表，建议降低触发阈值或手动定期收集
-- 可通过事件调度器实现
CREATE EVENT auto_analyze_orders
ON SCHEDULE EVERY 1 HOUR
DO ANALYZE TABLE orders;
3.4 索引失效的常见陷阱与根因分析
-- 陷阱一：隐式类型转换
-- phone 列为 VARCHAR 类型，但查询传入整数
SELECT * FROM users WHERE phone = 13800138000;
-- 优化器将 phone 列转换为数值比较，导致索引失效
-- 修复：传入字符串常量
SELECT * FROM users WHERE phone = '13800138000';
-- 陷阱二：索引列上使用函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-06-01';
-- DATE() 函数导致 created_at 索引无法使用
-- 修复：使用范围查询替代函数
SELECT * FROM orders
WHERE created_at >= '2026-06-01' AND created_at < '2026-06-02';
-- 陷阱三：LIKE 前缀通配符
SELECT * FROM products WHERE name LIKE '%手机%';
-- 前缀通配符无法利用 B+Tree 的有序性
-- 修复：使用全文索引或 Elasticsearch
ALTER TABLE products ADD FULLTEXT INDEX ft_name (name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机');
-- 陷阱四：OR 条件导致索引合并退化
SELECT * FROM orders WHERE status = 'pending' OR total_amount > 10000;
-- 两个条件分别有索引，但 OR 导致优化器可能放弃索引合并
-- 修复：使用 UNION 替代 OR
SELECT * FROM orders WHERE status = 'pending'
UNION
SELECT * FROM orders WHERE total_amount > 10000;
四、索引不是免费的：写入代价与存储开销的权衡
| 索引类型 | 查询加速效果 | 写入性能影响 | 存储开销 |
|---|---|---|---|
| B+Tree 单列索引 | 等值/范围查询 10-100x 加速 | INSERT 速度降低 5%-10% | 约表大小的 20%-30% |
| B+Tree 联合索引 | 多条件组合查询 50-200x 加速 | INSERT 速度降低 10%-20% | 约表大小的 30%-50% |
| 覆盖索引 | 消除回表，额外 2-5x 加速 | INSERT 速度降低 15%-25% | 索引列越多，存储越大 |
| 全文索引 | 文本搜索 50-500x 加速 | INSERT 速度降低 30%-50% | 约表大小的 50%-80% |
适用边界： 索引优化对读多写少的 OLTP 场景收益最大。在写入密集的场景（如日志表、消息表），每个额外索引都会拖慢 INSERT 速度，此时应严格控制索引数量，优先考虑通过分表、归档等架构手段降低单表数据量。
禁用场景： 当表的写入 QPS 超过 10000 且读 QPS 低于 1000 时，索引的写入代价可能超过查询收益。此时应评估是否将读写分离——从库建索引加速查询，主库保持最少索引保障写入吞吐。此外，区分度低于 5% 的列（如性别、状态）不适合单独建索引，低基数索引的过滤效果极差，优化器可能直接跳过。
五、总结
数据库慢查询优化的核心不是“加索引”，而是“加对索引”。执行计划是诊断慢查询的根本工具，type、rows、Extra 三个指标直接揭示了查询的性能瓶颈。索引列序的设计遵循“等值最左、范围次之、排序随后、覆盖收尾”的量化框架，违反任一规则都可能导致索引退化。统计信息的准确性是优化器正确选择索引的前提，过时的统计信息比没有索引更危险。
落地路线建议：先通过慢查询日志和 pt-query-digest 定位 Top N 慢查询；再用 EXPLAIN ANALYZE 逐条分析执行计划，对比预估行数与实际行数判断统计信息准确性；然后按量化框架设计联合索引，优先实现索引覆盖消除回表；最后定期维护统计信息，监控索引使用率，清理冗余索引。所有优化必须以执行计划的数据为依据——优化前后对比 type、rows、实际耗时，而非凭感觉判断。
转载自 CSDN-专业IT技术社区
原文链接：https://blog.csdn.net/iymei4986533030/article/details/162358045
