一条SQL把数据库打挂了——从执行计划到索引设计的完整排查与修复实录

一条SQL把数据库打挂了——从执行计划到索引设计的完整排查与修复实录

这是一份从生产事故中淬炼出来的SQL诊断与索引设计实战指南。读完它，你将获得一套可复用的排查方法论、判断"看一眼执行计划就知道病根"的直觉，以及用optimizer_trace透视优化器决策过程的高级能力。

引言：一个凌晨三点的报警

凌晨3点14分，运维群炸了。

“订单查询接口超时率飙升到45%！”“数据库CPU跑满了！”“应用线程池快被耗尽！”

你睡眼惺忪地打开监控面板——数据库的活跃连接数从平时的20暴涨到300，CPU使用率长时间维持在98%以上。show processlist里塞满了同一个查询的副本：

SELECT*FROMordersWHEREuser_id=123456ORDERBYorder_dateDESCLIMIT10;

这个查询平时不到50毫秒，现在却要跑3到8秒。你的第一反应是什么？“加索引”？但user_id上明明已经有索引了。

接下来我要带你走完从发现 → 诊断 → 修复 → 验证的完整过程。这不是一次"运气好蒙对了"的调优，而是一套可以肌肉记忆的排查流程。

一、前置知识：诊断工具包

在动手之前，先确认你的工具箱里有这三样东西：

1.1 慢查询日志——你的第一道防线

慢查询日志记录所有执行时间超过long_query_time阈值的SQL。先确认它是否开启：

SHOWVARIABLESLIKE'slow_query_log%';SHOWVARIABLESLIKE'long_query_time';

如果没开，在my.cnf中加入：

[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_queries_not_using_indexes = 1 log_slow_admin_statements = 1

环境依赖：MySQL 5.6+，需有SUPER或PROCESS权限查看processlist，需有FILE权限操作慢日志文件。

1.2 EXPLAIN——执行计划的"透视镜"

EXPLAINSELECT*FROMordersWHEREuser_id=123456ORDERBYorder_dateDESCLIMIT10;

1.3 EXPLAIN ANALYZE（MySQL 8.0.18+）——真正的"测谎仪"

传统EXPLAIN的rows是估算值。EXPLAIN ANALYZE会真正执行查询，输出每个步骤的实际耗时、循环次数和真实行数。

EXPLAINANALYZESELECT*FROMordersWHEREuser_id=123456ORDERBYorder_dateDESCLIMIT10;

⚠️警告：EXPLAIN ANALYZE会真实执行SQL，绝对不要在压测中的生产库上直接跑。请在从库或测试环境执行。

二、核心剖析：读懂EXPLAIN这张"天书"

拿到EXPLAIN输出后，不要被那十几列吓到。真正决定生死的关键字段只有5个：

2.1 type——性能的"红绿灯"

铁律：生产环境查询的type至少要达到range级别。看到ALL或index，立刻拉响警报。

2.2 key——到底用没用索引？

• possible_keys：MySQL认为可能用到的索引
• key：MySQL实际使用的索引
• key_len：索引使用的字节数，值越大说明索引利用得越充分（联合索引中实际用了多少列）

关键判断：如果possible_keys有值但key为NULL，说明优化器判断走索引比全表扫描还慢——这通常发生在数据分布极不均匀时。

2.3 rows——扫描行数估算

rows是估算需要扫描的行数，数字越大越慢。

重要：优化器的rows估算依赖于统计信息。统计信息过旧时，rows可能与真实情况差一个数量级。这正是optimizer_trace可以揭示的秘密。

2.4 filtered——回表代价的"放大镜"

表示存储引擎层返回的数据经过WHERE条件过滤后的剩余比例。rows=10000、filtered=1.00意味着最终只返回约100行——存储引擎扫了1万行，在Server层又过滤掉了99%，是巨大的性能浪费。

2.5 Extra——藏着魔鬼的细节

Using filesort是最常见的性能杀手——意味着MySQL无法利用索引完成排序，必须在内存或磁盘中额外排序。

三、手把手实操：从慢日志到根治

现在回到凌晨三点的报警现场。

Step 1：从慢日志中揪出"头号罪犯"

用pt-query-digest分析慢日志：

# 安装Percona Toolkit# Ubuntu/Debian: apt-get install percona-toolkit# CentOS/RHEL: yum install percona-toolkitpt-query-digest /var/log/mysql/slow.log--limit10

输出报告的核心指标：

• Response time：总响应时间占比——占比最高的就是头号罪犯
• Calls：执行次数
• R/Call：平均每次耗时
• Rows examined：平均扫描行数

在我们的案例中，报告显示那个订单查询的Rows examined高达50万，而表总共才100万行。

Step 2：用EXPLAIN看清执行计划

EXPLAINSELECT*FROMordersWHEREuser_id=123456ORDERBYorder_dateDESCLIMIT10\G

输出：

*************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders type: ref possible_keys: idx_user_id key: idx_user_id key_len: 4 ref: const rows: 52341 Extra: Using filesort

诊断结论：

1. type=ref，走了idx_user_id索引——✅ 索引在用
2. rows=52341，该用户有5.2万条订单——⚠️ 扫描行数大
3. Extra=Using filesort——🔴病根在这里！order_date没有进入索引，MySQL要把5.2万条记录全部取出，在内存中排序后再取前10条

Step 3：数据压测——三种索引方案在不同数据量下的真实表现

为了验证不同方案的优劣，我在同等硬件环境下（4C16G、SSD、MySQL 8.0.32）用sysbench构造了一张订单表，分别在100万、500万、1000万三个数据量级下进行压测。每次测试前重启数据库、清空Buffer Pool，确保结果可复现。

测试SQL固定为：

SELECT*FROMordersWHEREuser_id=?ORDERBYorder_dateDESCLIMIT10;

方案A：单列索引（现状）

CREATEINDEXidx_user_idONorders(user_id);

观察：扫描行数≈该用户的订单总数，filesort排序是整个操作的瓶颈，耗时随数据量线性增长。

方案B：联合索引（解决排序）

CREATEINDEXidx_user_dateONorders(user_id,order_date);

为什么EXPLAIN估算rows还是几十万，但实际只扫描了10行？

这是新手最容易困惑的地方。EXPLAIN的rows是优化器在生成执行计划之前的代价估算，它只统计了索引的基数（Cardinality），估算出该用户大约有50万条记录。但优化器忽略了LIMIT 10——它估算的是"该用户总共多少条"，而不是"为了取前10条实际扫描多少行"。

真正的执行过程是：MySQL在(user_id, order_date)联合索引中，用B+Tree定位到user_id=123456的第一条记录（该用户的最新订单，因为索引内已按order_date降序排列），然后连续读取10条索引记录就结束了。Explain Analyze显示的实际扫描行数只有10行。

方案C：覆盖索引（彻底消除回表）

CREATEINDEXidx_user_date_coveringONorders(user_id,order_date,status,amount);

方案C比方案B快了约5-6倍，原因在于：

• 方案B：索引中只有(user_id, order_date)，SELECT*需要的status、amount等字段必须回表（根据主键去聚簇索引读取完整行），额外消耗了随机IO
• 方案C：索引中包含查询所需的所有列（user_id, order_date, status, amount），MySQL直接从索引中返回数据，完全跳过回表步骤。Extra列显示Using index

三方案耗时对比图（1000万行数据）

耗时 (ms) 8500 | ████████████████████████████████████████████████████████ 方案A 52 | ██▌ 方案B 12 | █▌ 方案C +------------------------------------------------------- 方案A 方案B 方案C

结论：方案C（覆盖索引）在千万级数据下依然能稳定在12ms以内，性能提升超过700倍。

Step 4：用optimizer_trace看透优化器的"内心戏"

上面我们看到了方案B和方案C的EXPLAIN输出，但你有没有想过：优化器是怎么决定用哪个索引的？它为什么认为方案C更好？

MySQL的optimizer_trace可以把优化器的完整决策过程以JSON格式输出。这是比EXPLAIN更底层的诊断工具。

-- 开启trace（仅对当前会话生效，安全）SEToptimizer_trace="enabled=on";SEToptimizer_trace_max_mem_size=1000000;-- 执行要分析的查询SELECT*FROMordersWHEREuser_id=123456ORDERBYorder_dateDESCLIMIT10;-- 获取trace结果SELECT*FROMinformation_schema.OPTIMIZER_TRACE\G

输出的JSON非常庞大，但真正有价值的关键节点只有三个：

关键节点1：rows_estimation（行数估算）

"rows_estimation":[{"table":"orders","range_analysis":{"table_scan":{"rows":1000000,"cost":202431},"potential_range_indexes":[{"index":"idx_user_id","usable":true,"chosen":true},{"index":"idx_user_date_covering","usable":true,"chosen":true}],"analyzing_range_alternatives":{"range_scan_alternatives":[{"index":"idx_user_id","ranges":["123456 <= user_id <= 123456"],"index_dives_for_eq_ranges":true,"rowid_ordered":false,"using_mrr":false,"index_only":false,"rows":52341,"cost":62812},{"index":"idx_user_date_covering","ranges":["123456 <= user_id <= 123456"],"rowid_ordered":false,"using_mrr":false,"index_only":true,--✅ 覆盖索引标记！"rows":52341,"cost":10469--✅ 代价远低于idx_user_id}]}}}]

解读：优化器对两个可用索引都做了代价估算——idx_user_id的代价是62,812，而idx_user_date_covering的代价是10,469。关键差异在于index_only: true（覆盖索引无需回表），使得IO代价大幅降低。

关键节点2：considered_execution_plans（执行计划选择）

"considered_execution_plans":[{"plan_prefix":[],"table":"orders","best_access_path":{"considered_access_paths":[{"access_type":"ref","index":"idx_user_date_covering","cost":10469,"chosen":true,"cause":"cost"},{"access_type":"ref","index":"idx_user_id","cost":62812,"chosen":false}]},"cost_for_plan":10469,"rows_for_plan":52341,"chosen":true}]

这里记录了优化器遍历了所有可能的执行路径，最终根据代价（cost）最小原则选择了idx_user_date_covering。

关键节点3：join_optimization（最终优化结果）

"join_optimization":{"select#":1,"steps":[{"join_type":"ref","table":"orders","ref_columns":["user_id"],"used_index":"idx_user_date_covering","output_order":"ORDER BY order_date",--索引保证排序，无需filesort"limit":10,"using_join_buffer":false}]}

这里确认了优化器的最终决策：使用覆盖索引，且ORDER BY order_date由索引直接提供有序性，无需filesort。

optimizer_trace的价值：当你的查询在执行计划中表现异常（如优化器选择了错误的索引）时，optimizer_trace能告诉你为什么——是统计信息偏差导致估算行数不准？还是代价计算中的某个因素被高估/低估了？这比单纯看EXPLAIN深刻得多。

Step 5：验证并上线

测试环境验证方案C：

CREATEINDEXidx_user_date_coveringONorders(user_id,order_date,status,amount);EXPLAINSELECT*FROMordersWHEREuser_id=123456ORDERBYorder_dateDESCLIMIT10\G

预期输出：

type: ref key: idx_user_date_covering rows: 52341 -- 仍为估算值，实际执行只扫描10行（见EXPLAIN ANALYZE） Extra: Using index

上线步骤：

-- 1. 在从库先创建索引，观察复制延迟-- 2. 业务低峰期，在主库创建（使用INPLACE算法避免长时间锁表）ALTERTABLEordersADDINDEXidx_user_date_covering(user_id,order_date,status,amount),ALGORITHM=INPLACE,LOCK=NONE;-- 3. 确认索引生效后，可考虑删除旧索引（先观察几天，确认不影响其他查询）-- DROP INDEX idx_user_id ON orders;

Step 6：常见错误与调试

四、进阶思考：三个"看不见的坑"

坑一：最左前缀原则——联合索引不是万能的

联合索引(a, b, c)遵循最左前缀原则：查询必须从索引的最左列开始，且不能跳过中间的列。

-- ✅ 能用到索引 (a, b, c)WHEREa=1ANDb=2ANDc=3WHEREa=1ANDb=2-- ⚠️ 部分用到（只用a，b跳过，c的排序/过滤失效）WHEREa=1ANDc=3-- ❌ 完全用不到（跳过了a）WHEREb=2ANDc=3

验证索引到底用到了哪几列：

EXPLAINSELECT*FROMordersWHEREuser_id=1ANDorder_date>'2025-01-01'\G-- 看key_len字段：如果索引是(user_id, order_date)，user_id=4字节，order_date=3字节-- key_len=4表示只用了user_id，key_len=7表示两列都用到了

坑二：隐式类型转换——索引失效的"隐形杀手"

当字段类型和查询值类型不匹配时，MySQL会做隐式转换：

-- 假设表结构：id INT-- ❌ 索引失效！id被转换成字符串再比较SELECT*FROMordersWHEREid='10086';-- 假设 phone是VARCHAR-- ❌ 索引失效！phone字段被转换成数字再比较SELECT*FROMusersWHEREphone=13800138000;

验证索引是否真的失效：用EXPLAIN看key列是否为NULL，或用SHOW STATUS LIKE 'Handler_read%'观察读取行为。

坑三：索引下推（ICP）——MySQL 5.6+的福音

没有ICP：存储引擎根据索引找到主键 → 回表 → Server层再过滤其他条件。

有ICP：部分WHERE条件下推到存储引擎，在索引层就完成过滤。

-- 假设有联合索引(name, age)SELECT*FROMtuserWHEREnameLIKE'张%'ANDage=20;

如何确认ICP生效？看Extra列是否有Using index condition。

坑四（新增）：大表创建索引的"时间窗口陷阱"

在1000万行的表上创建覆盖索引，可能耗时数十分钟甚至数小时。如果直接在生产库执行，可能导致：

• 业务写入被阻塞（即使使用ALGORITHM=INPLACE，DDL过程中仍需要短暂的元数据锁（MDL），会阻塞所有DML）
• 主从复制延迟飙升（DDL在从库重放时同样耗时）

解决方案：

# 使用pt-online-schema-change，在业务不中断的情况下在线变更pt-online-schema-change\--alter"ADD INDEX idx_user_date_covering (user_id, order_date, status, amount)"\--execute\--alter-foreign-keys-method=auto\--no-drop-old-table\--max-lag=1\--check-interval=1\h=localhost,D=myapp,t=orders,u=root,p='password'

该工具通过创建影子表、触发器同步增量数据的方式实现在线变更，对业务影响最小。

五、总结：从"会用"到"会诊断"

回到凌晨三点的报警。现在你知道完整的排查路径了：

慢查询报警 ↓ pt-query-digest 分析慢日志 → 定位问题SQL ↓ EXPLAIN 查看执行计划 → 发现 type、rows、Extra 的异常 ↓ （疑难杂症时）optimizer_trace 查看优化器决策全过程 ↓ EXPLAIN ANALYZE 验证实际执行中的真实扫描行数 ↓ 诊断病根（filesort / 回表过多 / 索引失效） ↓ 设计索引方案（单列 → 联合 → 覆盖，逐级优化，附压测验证） ↓ 使用 pt-osc 在大表上安全上线 ↓ 监控对比（关注 QPS、P99 延迟、CPU 使用率） ↓ 复盘沉淀：更新索引设计规范 + 慢查询监控阈值

这次你真正带走的东西：

1. 一套完整的排查工具链：pt-query-digest→EXPLAIN→EXPLAIN ANALYZE→optimizer_trace，从粗筛到精确定位，每层工具有明确的适用场景
2. 读懂执行计划的关键判断力：看到type=ALL知道要全表扫描，看到Extra=Using filesort知道排序是瓶颈，看到key_len判断联合索引用了多少列
3. 优化器决策的"读心术"：通过optimizer_trace理解优化器为什么选择/放弃某个索引，而不是只能被动接受
4. 索引设计的科学方法：覆盖索引为什么能把5.2万行降到10行，如何用压测数据验证方案而不是凭感觉，以及大表索引上线的安全姿势

最后送你一句话：

“写SQL是本能，读执行计划是基本功，看optimizer_trace是进阶，设计索引是手艺，而压测验证才是真正的底气。”

现在，去跑一遍你生产环境中最慢的那条SQL的EXPLAIN吧。再看看optimizer_trace，理解优化器为什么做出了那个选择——答案往往就在那里。