一条SQL把数据库打挂了——从执行计划到索引设计的完整排查与修复实录这是一份从生产事故中淬炼出来的SQL诊断与索引设计实战指南。读完它你将获得一套可复用的排查方法论、判断看一眼执行计划就知道病根的直觉以及用optimizer_trace透视优化器决策过程的高级能力。引言一个凌晨三点的报警凌晨3点14分运维群炸了。“订单查询接口超时率飙升到45%”“数据库CPU跑满了”“应用线程池快被耗尽”你睡眼惺忪地打开监控面板——数据库的活跃连接数从平时的20暴涨到300CPU使用率长时间维持在98%以上。show processlist里塞满了同一个查询的副本SELECT*FROMordersWHEREuser_id123456ORDERBYorder_dateDESCLIMIT10;这个查询平时不到50毫秒现在却要跑3到8秒。你的第一反应是什么“加索引”但user_id上明明已经有索引了。接下来我要带你走完从发现 → 诊断 → 修复 → 验证的完整过程。这不是一次运气好蒙对了的调优而是一套可以肌肉记忆的排查流程。一、前置知识诊断工具包在动手之前先确认你的工具箱里有这三样东西1.1 慢查询日志——你的第一道防线慢查询日志记录所有执行时间超过long_query_time阈值的SQL。先确认它是否开启SHOWVARIABLESLIKEslow_query_log%;SHOWVARIABLESLIKElong_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_id123456ORDERBYorder_dateDESCLIMIT10;1.3 EXPLAIN ANALYZEMySQL 8.0.18——真正的测谎仪传统EXPLAIN的rows是估算值。EXPLAIN ANALYZE会真正执行查询输出每个步骤的实际耗时、循环次数和真实行数。EXPLAINANALYZESELECT*FROMordersWHEREuser_id123456ORDERBYorder_dateDESCLIMIT10;⚠️警告EXPLAIN ANALYZE会真实执行SQL绝对不要在压测中的生产库上直接跑。请在从库或测试环境执行。二、核心剖析读懂EXPLAIN这张天书拿到EXPLAIN输出后不要被那十几列吓到。真正决定生死的关键字段只有5个2.1 type——性能的红绿灯type含义判断system/const主键或唯一索引等值查询最多一行✅ 最优eq_ref唯一索引关联JOIN连接条件是主键✅ 优秀ref普通索引等值查询✅ 合格range索引范围扫描BETWEEN、、、IN⚠️ 及格线index全索引扫描❌ 较差ALL全表扫描❌灾难铁律生产环境查询的type至少要达到range级别。看到ALL或index立刻拉响警报。2.2 key——到底用没用索引possible_keysMySQL认为可能用到的索引keyMySQL实际使用的索引key_len索引使用的字节数值越大说明索引利用得越充分联合索引中实际用了多少列关键判断如果possible_keys有值但key为NULL说明优化器判断走索引比全表扫描还慢——这通常发生在数据分布极不均匀时。2.3 rows——扫描行数估算rows是估算需要扫描的行数数字越大越慢。重要优化器的rows估算依赖于统计信息。统计信息过旧时rows可能与真实情况差一个数量级。这正是optimizer_trace可以揭示的秘密。2.4 filtered——回表代价的放大镜表示存储引擎层返回的数据经过WHERE条件过滤后的剩余比例。rows10000、filtered1.00意味着最终只返回约100行——存储引擎扫了1万行在Server层又过滤掉了99%是巨大的性能浪费。2.5 Extra——藏着魔鬼的细节Extra信息含义严重程度Using index覆盖索引无需回表 好事Using index condition索引下推(ICP) 较好Using where需要回表过滤 正常Using filesort需要额外排序严重Using temporary使用临时表严重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_id123456ORDERBYorder_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诊断结论typeref走了idx_user_id索引——✅ 索引在用rows52341该用户有5.2万条订单——⚠️ 扫描行数大ExtraUsing 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);数据量平均耗时扫描行数(EXPLAIN)实际扫描行数(EXPLAIN ANALYZE)100万行该用户约5万条~820ms~52,000~52,000500万行该用户约25万条~4.2s~250,000~250,0001000万行该用户约50万条~8.5s~500,000~500,000观察扫描行数≈该用户的订单总数filesort排序是整个操作的瓶颈耗时随数据量线性增长。方案B联合索引解决排序CREATEINDEXidx_user_dateONorders(user_id,order_date);数据量平均耗时扫描行数(EXPLAIN)实际扫描行数(EXPLAIN ANALYZE)100万行~45ms~52,00010500万行~48ms~250,000101000万行~52ms~500,00010为什么EXPLAIN估算rows还是几十万但实际只扫描了10行这是新手最容易困惑的地方。EXPLAIN的rows是优化器在生成执行计划之前的代价估算它只统计了索引的基数Cardinality估算出该用户大约有50万条记录。但优化器忽略了LIMIT 10——它估算的是该用户总共多少条而不是为了取前10条实际扫描多少行。真正的执行过程是MySQL在(user_id, order_date)联合索引中用BTree定位到user_id123456的第一条记录该用户的最新订单因为索引内已按order_date降序排列然后连续读取10条索引记录就结束了。Explain Analyze显示的实际扫描行数只有10行。方案C覆盖索引彻底消除回表CREATEINDEXidx_user_date_coveringONorders(user_id,order_date,status,amount);数据量平均耗时扫描行数(EXPLAIN)实际扫描行数(EXPLAIN ANALYZE)100万行~5ms~52,00010500万行~8ms~250,000101000万行~12ms~500,00010方案C比方案B快了约5-6倍原因在于方案B索引中只有(user_id, order_date)SELECT*需要的status、amount等字段必须回表根据主键去聚簇索引读取完整行额外消耗了随机IO方案C索引中包含查询所需的所有列user_id, order_date, status, amountMySQL直接从索引中返回数据完全跳过回表步骤。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_traceenabledon;SEToptimizer_trace_max_mem_size1000000;-- 执行要分析的查询SELECT*FROMordersWHEREuser_id123456ORDERBYorder_dateDESCLIMIT10;-- 获取trace结果SELECT*FROMinformation_schema.OPTIMIZER_TRACE\G输出的JSON非常庞大但真正有价值的关键节点只有三个关键节点1rows_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代价大幅降低。关键节点2considered_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。关键节点3join_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,--索引保证排序无需filesortlimit:10,using_join_buffer:false}]}这里确认了优化器的最终决策使用覆盖索引且ORDER BY order_date由索引直接提供有序性无需filesort。optimizer_trace的价值当你的查询在执行计划中表现异常如优化器选择了错误的索引时optimizer_trace能告诉你为什么——是统计信息偏差导致估算行数不准还是代价计算中的某个因素被高估/低估了这比单纯看EXPLAIN深刻得多。Step 5验证并上线测试环境验证方案CCREATEINDEXidx_user_date_coveringONorders(user_id,order_date,status,amount);EXPLAINSELECT*FROMordersWHEREuser_id123456ORDERBYorder_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),ALGORITHMINPLACE,LOCKNONE;-- 3. 确认索引生效后可考虑删除旧索引先观察几天确认不影响其他查询-- DROP INDEX idx_user_id ON orders;Step 6常见错误与调试错误现象可能原因排查方法创建索引后key还是旧索引统计信息未更新ANALYZE TABLE orders;rows估算值没下降优化器基于统计信息估算用EXPLAIN ANALYZE看实际扫描行数创建索引时业务阻塞大表DDL默认锁表使用pt-online-schema-change覆盖索引占用空间暴增包含了过多大字段精简索引列只包含SELECT的必要字段四、进阶思考三个看不见的坑坑一最左前缀原则——联合索引不是万能的联合索引(a, b, c)遵循最左前缀原则查询必须从索引的最左列开始且不能跳过中间的列。-- ✅ 能用到索引 (a, b, c)WHEREa1ANDb2ANDc3WHEREa1ANDb2-- ⚠️ 部分用到只用ab跳过c的排序/过滤失效WHEREa1ANDc3-- ❌ 完全用不到跳过了aWHEREb2ANDc3验证索引到底用到了哪几列EXPLAINSELECT*FROMordersWHEREuser_id1ANDorder_date2025-01-01\G-- 看key_len字段如果索引是(user_id, order_date)user_id4字节order_date3字节-- key_len4表示只用了user_idkey_len7表示两列都用到了坑二隐式类型转换——索引失效的隐形杀手当字段类型和查询值类型不匹配时MySQL会做隐式转换-- 假设表结构id INT-- ❌ 索引失效id被转换成字符串再比较SELECT*FROMordersWHEREid10086;-- 假设 phone是VARCHAR-- ❌ 索引失效phone字段被转换成数字再比较SELECT*FROMusersWHEREphone13800138000;验证索引是否真的失效用EXPLAIN看key列是否为NULL或用SHOW STATUS LIKE Handler_read%观察读取行为。坑三索引下推ICP——MySQL 5.6的福音没有ICP存储引擎根据索引找到主键 → 回表 → Server层再过滤其他条件。有ICP部分WHERE条件下推到存储引擎在索引层就完成过滤。-- 假设有联合索引(name, age)SELECT*FROMtuserWHEREnameLIKE张%ANDage20;如何确认ICP生效看Extra列是否有Using index condition。坑四新增大表创建索引的时间窗口陷阱在1000万行的表上创建覆盖索引可能耗时数十分钟甚至数小时。如果直接在生产库执行可能导致业务写入被阻塞即使使用ALGORITHMINPLACEDDL过程中仍需要短暂的元数据锁MDL会阻塞所有DML主从复制延迟飙升DDL在从库重放时同样耗时解决方案# 使用pt-online-schema-change在业务不中断的情况下在线变更pt-online-schema-change\--alterADD INDEX idx_user_date_covering (user_id, order_date, status, amount)\--execute\--alter-foreign-keys-methodauto\--no-drop-old-table\--max-lag1\--check-interval1\hlocalhost,Dmyapp,torders,uroot,ppassword该工具通过创建影子表、触发器同步增量数据的方式实现在线变更对业务影响最小。五、总结从会用到会诊断回到凌晨三点的报警。现在你知道完整的排查路径了慢查询报警 ↓ pt-query-digest 分析慢日志 → 定位问题SQL ↓ EXPLAIN 查看执行计划 → 发现 type、rows、Extra 的异常 ↓ 疑难杂症时optimizer_trace 查看优化器决策全过程 ↓ EXPLAIN ANALYZE 验证实际执行中的真实扫描行数 ↓ 诊断病根filesort / 回表过多 / 索引失效 ↓ 设计索引方案单列 → 联合 → 覆盖逐级优化附压测验证 ↓ 使用 pt-osc 在大表上安全上线 ↓ 监控对比关注 QPS、P99 延迟、CPU 使用率 ↓ 复盘沉淀更新索引设计规范 慢查询监控阈值这次你真正带走的东西一套完整的排查工具链pt-query-digest→EXPLAIN→EXPLAIN ANALYZE→optimizer_trace从粗筛到精确定位每层工具有明确的适用场景读懂执行计划的关键判断力看到typeALL知道要全表扫描看到ExtraUsing filesort知道排序是瓶颈看到key_len判断联合索引用了多少列优化器决策的读心术通过optimizer_trace理解优化器为什么选择/放弃某个索引而不是只能被动接受索引设计的科学方法覆盖索引为什么能把5.2万行降到10行如何用压测数据验证方案而不是凭感觉以及大表索引上线的安全姿势最后送你一句话“写SQL是本能读执行计划是基本功看optimizer_trace是进阶设计索引是手艺而压测验证才是真正的底气。”现在去跑一遍你生产环境中最慢的那条SQL的EXPLAIN吧。再看看optimizer_trace理解优化器为什么做出了那个选择——答案往往就在那里。