背了那么久的慢 SQL 八股不如动手跑一遍 EXPLAIN我之前带过几个新人聊起慢 SQL 优化个个能把“最左前缀、回表、覆盖索引、ICP”背得滚瓜烂熟可是一到现场拿到一条真实慢 SQL看着 EXPLAIN 的输出却不知道怎么下手。说实话这太正常了。八股是别人嚼过的结论EXPLAIN 才是你自己亲手剖开 SQL 执行过程的第一现场。这篇文章我打算用一条线上真实出现过的慢 SQL 做例子从准备数据到看执行计划再到优化前后对比完整走一遍。同时会把 MySQL 和 Oracle 两种数据库的执行计划读法都讲清楚毕竟很多团队手里同时管着两套库。如果你正准备系统学慢 SQL 优化或者被一条查了几秒甚至几十秒的 SQL 折磨过这篇内容应该能帮你少走不少弯路。1. 为什么纠结执行计划而不是继续背索引口诀1.1 慢 SQL 排查的第一现场不在慢日志在执行计划慢查询日志能告诉你哪条 SQL 慢、慢了多少秒但它不会告诉你为什么会慢。索引口诀能告诉你要建索引但建在哪一列、为什么这么建它说不清楚。EXPLAIN 是数据库优化器在执行 SQL 前生成的一份“行驶路线图”它展示了优化器打算用哪种方式访问表、用哪个索引、预计扫描多少行、是否需要回表、是否需要排序。你只有学会读这张图才能真正判断一条 SQL 是索引没走对、统计信息有误、还是 SQL 写法本身有问题。我面试时经常问一个问题你优化过最复杂的慢 SQL 是什么很多人的回答都是“加了索引就快了”但这其实是结果不是思路。真正的排查应该是发现慢 SQL 后先抓执行计划看访问路径是否合理再看实际耗时卡在哪个环节最后才决定是加索引、改 SQL、还是调整参数。顺序一旦反了很容易“病急乱投医”。1.2 执行计划到底在回答什么问题如果只能从 EXPLAIN 里读一件事我建议读访问类型也就是 MySQL 里的 type 列、Oracle 执行计划里的 TABLE ACCESS 方式。它直接告诉你数据库是用什么姿势在找数据是全表扫一遍还是通过索引精确定位或者是通过索引范围扫描。这就好比你要在一本字典里找一个字你是从头翻到尾还是先通过拼音索引定位到具体的页再翻那一页两者效率差了几十上百倍。还有一个容易被忽略的问题执行计划是基于当前统计信息做的估算它不等于 SQL 的真实执行轨迹。MySQL 的 EXPLAIN 不会真的跑 SQL它只是根据统计信息“猜”一个方案而 MySQL 8.0 的 EXPLAIN ANALYZE 才是真正执行并返回实测数据。Oracle 的 DBMS_XPLAN 也有类似区别默认显示计划加上 FORMAT 参数可以看到运行时的实际行数和耗时。这一点很多人会踩坑拿 EXPAIN 的预估 rows 当成实际扫描行数结果和真实情况差了一个数量级排查方向完全跑偏。2. 动手前的必要准备先把慢 SQL 稳定复现出来2.1 慢查询日志配置与抓取优化一条慢 SQL第一步不是优化而是能稳定复现它。如果这条 SQL 只是偶发抖动大概率不是执行计划本身的问题而是锁等待、资源争用或者统计信息突然过期。只有稳定复现的慢 SQL才值得你一条条去抠执行计划。在 MySQL 里我习惯先打开慢查询日志并合理设置阈值-- 查看当前慢查询配置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 临时开启重启失效适合在测试库使用 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time 建议线上从 1 秒开始如果慢 SQL 太多可以逐步调到 2 秒或 5 秒。log_queries_not_using_indexes 也建议打开它能把没走索引的查询记下来这类 SQL 即使耗时没超过阈值也可能在数据量增长后成为定时炸弹。日志抓出来之后千万别急着去 EXPLAIN先看 SQL 里的 WHERE 条件、JOIN 条件和 ORDER BY 字段把表结构、数据量大概摸一遍。很多慢 SQL 看一眼表结构就知道问题在哪EXPLAIN 只是帮你确认判断。2.2 造一份能说明问题的测试数据如果线上不能随便跑 EXPLAIN有些公司对生产库执行计划抓取有限制你需要在测试环境造一份“能说明问题”的数据。注意不是随便塞几万行就行而是要让数据的分布、表的数据量级和线上尽量接近。我见过太多人拿 10 万行的表测出来索引没问题结果线上 1 亿行的数据慢成狗。索引失效的场景、优化器选择全表扫描的阈值、JOIN 的行为都和表的规模直接相关。我的建议是测试环境的数据量至少做到线上的 1/10关键大表尽量做到 1/3 以上并且要在测试表上执行 ANALYZE TABLE 刷新统计信息否则你 EXPLAIN 出来的计划可能和线上完全不同。造数据时有个省事技巧用递归 CTE 批量生成序列数据然后用随机函数填充业务字段比一条条插入快得多-- 示例批量生成 100 万行测试数据 INSERT INTO orders (id, user_id, order_no, amount, status, created_at) SELECT seq, FLOOR(RAND() * 500000) 1, CONCAT(NO, LPAD(seq, 10, 0)), ROUND(RAND() * 1000, 2), FLOOR(RAND() * 5), DATE_ADD(2023-01-01, INTERVAL FLOOR(RAND() * 600) DAY) FROM ( WITH RECURSIVE seq_cte AS ( SELECT 1 AS seq UNION ALL SELECT seq 1 FROM seq_cte WHERE seq 1000000 ) SELECT seq FROM seq_cte ) t;这类数据虽然随机性比较强但用来验证执行计划已经足够。如果你想模拟更真实的字段分布比如某个状态值只占 1%可以再加一层 CASE WHEN 控制比例。2.3 搭建最小复现环境的注意事项我踩过不少次坑总结下来有三点值得提醒一是不要在小数据量上直接下结论。10 万行数据里优化器可能觉得全表扫描比用索引更快因为要扫描的块不多回表的开销几乎可以忽略等数据到了千万级执行计划瞬间就变了。你必须在接近真实体量的环境里测试。二是统计信息一定记得更新。MySQL 里执行 ANALYZE TABLE 表名Oracle 里用 DBMS_STATS.GATHER_TABLE_STATS让优化器拿到最新的数据分布信息否则执行计划是“过期的地图”按图索骥找不到问题。三是每次改完 SQL 或索引重新 EXPLAIN 之前先清缓存。MySQL 里可以执行 RESET QUERY CACHE如果还在用或者用 FLUSH TABLES 让表重新打开Oracle 里可以 ALTER SYSTEM FLUSH SHARED_POOL测试库专属操作线上千万别做。不清缓存的情况下你看到的时间可能来自 Buffer Pool 的命中数据根本没走磁盘。3. 从一条真实慢 SQL 开始手把手读懂 MySQL EXPLAIN3.1 完整示例建表、造数、慢 SQL 复现下面这条 SQL 是我去年在处理一个订单报表需求时真实遇到的。需求很简单查某个用户最近 30 天的订单总额并且按订单号排序。先看表结构CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(12,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, KEY idx_user_created (user_id, created_at), KEY idx_created (created_at) ) ENGINEInnoDB;业务慢 SQL 长这样SELECT user_id, order_no, amount, created_at FROM orders WHERE user_id 10001 AND created_at 2023-06-01 00:00:00 AND created_at 2023-07-01 00:00:00 ORDER BY order_no LIMIT 100;这条 SQL 看起来简单但线上执行时间一度超过 3 秒。问题出在排序字段 order_no 并不在索引里优化器要么先按索引查出来 1 万行再用 filesort 排序要么干脆直接全表扫。我们来 EXPLAIN 看一下。3.2 逐列拆解type、key、rows、Extra 怎么配合看执行下面这条命令EXPLAIN SELECT user_id, order_no, amount, created_at FROM orders WHERE user_id 10001 AND created_at 2023-06-01 00:00:00 AND created_at 2023-07-01 00:00:00 ORDER BY order_no LIMIT 100\G输出如下我用的是 MySQL 8.0列名比 5.7 稍有变化id: 1 select_type: SIMPLE table: orders partitions: NULL type: ref possible_keys: idx_user_created, idx_created key: idx_user_created key_len: 8 ref: const rows: 12840 filtered: 20.50 Extra: Using where; Using index condition; Using filesort逐列看下来。type 是 ref说明通过非唯一索引等值匹配找到了候选行这已经不算最差的情况因为没有走 ALL 全表扫描。possible_keys 里列出了两个可能用的索引优化器选了 idx_user_created也就是 (user_id, created_at) 这个联合索引。key_len 是 8说明只用了联合索引的第一列 user_idBIGINT 8 字节作为等值条件created_at 的部分没有进入索引匹配。rows 估算 12840 行表示预计扫描这么多行。filtered 是 20.50表示满足 created_at 条件的行大约占扫描行数的 20.5%。也就是说优化器认为符合条件的有 2632 行。Extra 里出现了三个关键信息Using where 表示需要回表后过滤其余条件Using index condition 说明用上了索引下推ICPcreated_at 的范围条件会在存储引擎层先过滤一部分Using filesort 是这里最扎眼的说明你要的 ORDER BY order_no 没法通过索引顺序直接返回需要额外排序。3.3 定位这条 SQL 慢在哪从执行计划来看这条 SQL 的问题可以拆成两块第一块是回表量不小。user_id 的数据分布如果比较均匀单用户订单可能就数千行再按状态过滤后剩下的仍要回表读取整行数据。第二块是排序。order_no 这个字段在联合索引里并不存在拿到的数据只能是按索引顺序排列的也就是先按 user_id 排再按 created_at 排但业务要求按 order_no 排于是优化器必须把扫描得到的行丢进排序缓冲区做一次 filesort。单看这里filesort 在小结果集上可能并不致命但当数据行数上万时排序内存和临时表的开销会直接拖垮查询。顺手说一下有些人对 filesort 有误解觉得只要出现 filesort 就是慢查询的元凶。其实 filesort 分为内存排序和磁盘排序两类如果排序数据量小于 sort_buffer_size它完全发生在内存里速度很快。真正要警惕的是排序数据量超过 sort_buffer_size导致优化器不得不使用临时文件这时才会出现大量的磁盘 I/O。这条 SQL 之所以变成典型慢 SQL就是因为满足条件的行数有 2000 多排序又叠加了回表访问才把耗时推到了 3 秒以上。3.4 优化效果验证改动后重新跑 EXPLAIN针对上面的问题最直接的优化思路是建一个覆盖索引把排序字段也放进索引里ALTER TABLE orders ADD INDEX idx_user_created_order (user_id, created_at, order_no);这个联合索引的列顺序有讲究user_id 作为等值条件放最左边created_at 作为范围条件放第二位order_no 作为排序字段放最后。因为 MySQL 的索引结构天然是有序的同一 user_id 下 created_at 升序同一时间点下 order_no 也能保持有序这就有机会省掉 filesort。加完索引后重新执行 EXPLAINEXPLAIN SELECT user_id, order_no, amount, created_at FROM orders WHERE user_id 10001 AND created_at 2023-06-01 00:00:00 AND created_at 2023-07-01 00:00:00 ORDER BY order_no LIMIT 100\G输出变成了id: 1 select_type: SIMPLE table: orders partitions: NULL type: range possible_keys: idx_user_created, idx_created, idx_user_created_order key: idx_user_created_order key_len: 16 ref: NULL rows: 2632 filtered: 100.00 Extra: Using where; Using index注意两个变化。type 从 ref 变成了 range说明 created_at 的范围条件也参与到了索引访问中而不是只靠 user_id 等值匹配。key_len 从 8 变成了 16联合索引真正用到了两列。Extra 里 Using filesort 消失了变成了 Using index意思是查询所需的列都在索引里可以直接取得连回表都省了。优化前后对比rows 从 12840 降到 2632少了回表少了排序。线上优化后的实际耗时从 3.2 秒降到了 30 毫秒左右。这个优化案例里真正起关键作用的不是某个口诀而是理解了“索引既组织数据也组织顺序”这个底层逻辑。4. Oracle 执行计划怎么读以及“长时间锁定表”怎么排查4.1 从 EXPLAIN PLAN 到 DBMS_XPLANOracle 和 MySQL 的执行计划查看方式差异很大网上关于 MySQL 的资料铺天盖地但 Oracle 的反而少。如果你工作环境里同时有两种数据库建议都掌握基本用法。Oracle 里想为一个 SQL 生成执行计划常规做法是先 EXPLAIN PLAN FOR再用 DBMS_XPLAN 展示。以一条很常见的慢 SQL 为例EXPLAIN PLAN FOR SELECT o.order_id, o.amount, c.customer_name FROM orders o JOIN customers c ON o.customer_id c.customer_id WHERE o.created_at DATE 2023-01-01 AND o.status PAID; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);输出里有一列叫 OPERATION它负责描述每一步在干什么。你会看到诸如 TABLE ACCESS FULL、INDEX RANGE SCAN、NESTED LOOPS、HASH JOIN、SORT ORDER BY 这样的字样这些才是判断性能的关键。不过我更喜欢用 DBMS_XPLAN.DISPLAY_CURSOR 直接查看 SQL 已经执行过的真实计划因为 EXPLAIN PLAN 只是优化器的估算而实际执行计划能看到真实的行数和执行次数。格式如下SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id 你的SQL_ID, format ALLSTATS LAST));想要拿到 SQL_ID最简单的方式是 v$sql 视图SELECT sql_id, sql_text, elapsed_time, executions FROM v$sql WHERE sql_text LIKE %你要找的关键词% AND sql_text NOT LIKE %v$sql%;v$sql 里还有 elapsed_time、executions、buffer_gets 这些运行指标对排查线上真实慢 SQL 很有参考意义。4.2 执行计划里最值得先看的几个指标很多 Oracle DBA 看执行计划喜欢盯 Cost但我不建议新手一上来就看 Cost。Cost 是相对值不同版本、不同统计信息下可能完全不同。我更建议先看三样东西。一是看有没有全表扫描。全表扫描不一定慢小表扫描比走索引还快但如果扫描发生在 1 亿行的订单表上而且你在 WHERE 条件里明明写了筛选能力很强的字段那基本说明索引没建对或者统计信息过期了。二是看每一步的返回行数。Oracle 执行计划里用 Rows 表示估算返回行数用 A-Rows 表示实际返回行数。如果两者差距太大说明优化器对数据分布的判断有偏差常见原因是统计信息陈旧或者是绑定变量导致优化器无法准确估算。我的经验是 A-Rows 比 Rows 更可信排查问题时要优先看 A-Rows。三是看排序和 hash 操作。SORT ORDER BY、SORT GROUP BY、HASH JOIN 是最容易消耗内存和临时表空间的操作。一个查询如果在执行计划里出现多个 SORT 操作同时又因为数据量大导致内存不足性能基本不会好。这个时候优先考虑在 SQL 层面消除排序比如利用索引顺序或者调整 PGA 相关的内存参数。4.3 锁等待导致的“慢 SQL”为什么执行计划看不出端倪回到热词里那个 Oracle 查询慢 SQL 和长时间锁定表的问题。这类场景我遇到很多次开发同学给我发消息说“这条 SQL 上线后突然变慢平时几十毫秒现在卡了五分钟都不出来是不是执行计划变差了”结果我抓了执行计划一看访问路径非常完美索引也走了预估行数也对按说没理由慢。这时候我一般会顺手查一下阻塞和锁等待的情况。如果是锁等待导致的慢你再怎么优化 SQL 也没用因为 SQL 根本没在跑它被卡住了。判断锁等待Oracle 里最简单的方式是查 v$session 里的事件名称。如果大量会话的 EVENT 是 enq: TX - row lock contention、enq: TX - allocate ITL entry、或者 library cache lock 之类问题根本不在 SQL 执行计划上而在并发控制上。你可以用下面的查询定位当前所有会话在等什么SELECT sid, event, wait_class, blocking_session, seconds_in_wait, state FROM v$session WHERE wait_class ! Idle AND state WAITING ORDER BY seconds_in_wait DESC;blocking_session 字段如果非空说明当前会话被另一个 SID 阻塞了。最常见的锁问题是事务长时间不提交导致其他会话要更新的行被锁住。你查到了阻塞源头 SID再去 v$session 里看那个会话的 SQL_ID基本就能锁定是哪条 DML 没提交。MySQL 里对应的是 sys.innodb_lock_waits 表或 performance_schema 的锁等待视图同样可以查到谁阻塞了谁。这类问题的共性在于它不是 SQL 优化能解决的而是业务逻辑、事务长度、并发提交频率的问题。必须从代码层面减少长事务、优化提交频率或者调整数据库的事务隔离级别、锁等待超时参数。4.4 排查锁问题的常规手段和预防思路排查锁问题我习惯按三层来走。第一层看“等锁的会话”。锁定正在等待资源的会话以及等待了多久这能帮你判断问题有多严重是偶发还是已经积累了几百个会话。第二层看“持锁的会话”。找到等待会话的 blocking_session 对应的会话它在执行哪条 SQL、开启事务多久了、连接来自哪台应用服务器。如果是应用侧开启事务后忘记提交这就是代码问题要通报开发修复。如果是某个批量更新脚本跑了太久就要评估是否分批次提交。第三层看历史。Oracle 的 DBA_HIST_ACTIVE_SESS_HISTORY 里记录了历史活跃会话的等待事件MySQL 8.0 的 performance_schema.events_statements_history 也有类似能力。查看历史可以帮助你确认这个锁等待是上线新功能后才出现的还是一直都存在的长尾问题。再补充一个预防层面的思路长时间锁定表的根因大多是事务过大。比如一个夜里跑的定时任务按月份循环更新数据每个循环都不提交跑了一小时就把一整张表的行锁都快占满了。这种情况即使每条更新语句本身都很快整个任务的锁持有时间也会让业务侧完全没法做 DML。建议分批提交每处理 1000 或者 5000 行就 COMMIT同时监控事务持续时间。另外Oracle 的 undo_retention 和 MySQL 的 innodb_lock_wait_timeout 也要根据业务容忍度合理设置前者决定读一致性快照能回看多长后者决定锁等待最多坚持多久别一味调大。5. 常见问题与排查技巧实录5.1 几个容易误判的场景第一个场景EXPLAIN 里走了索引但 SQL 还是慢。这种我见太多了。走索引不代表性能一定好如果索引的区分度不够高比如在 status 列上建索引一共只有 0、1 两个值优化器扫出来的行数可能接近全表的一半再加上大量回表和随机 I/O不见得比全表扫描快。碰到这种情况你应该去看 key_len 和 rows 列判断到底用到了索引的哪些列、预估扫描了多少行。第二个场景MySQL 5.7 和 8.0 的 EXPLAIN 输出列不同。5.7 有 possible_keys、key、key_len、ref、rows、Extra8.0 在此基础上新增了 indexed_columns 等列同时把部分信息合并进 EXPLAIN ANALYZE。如果你习惯了 5.7 的列名在 8.0 里会稍微不适应但核心概念一致。第三个场景用 EXPLAIN 去验证生产环境的慢 SQL却故意在 WHERE 条件里写死一个线上不可能出现的值比如 user_id 0。这种操作会严重误导优化器的估算因为不同 user_id 的订单量差异可能很大优化器基于某个特例生成的执行计划完全不适用于真实业务。优化器是根据具体条件值来估算的换一个 user_idrows 和访问路径可能天差地别。5.2 排查技巧整理我给几条实测下来很受用的经验供大家参考。一是使用 EXPLAIN ANALYZE 看真实耗时MySQL 8.0.18。EXPLAIN ANALYZE 会真正执行 SQL并返回每个步骤实际的时间和行数比普通 EXPLAIN 的估算值可靠得多。用法和普通 EXPLAIN 类似EXPLAIN ANALYZE SELECT user_id, order_no, amount, created_at FROM orders WHERE user_id 10001 AND created_at 2023-06-01 00:00:00 AND created_at 2023-07-01 00:00:00 ORDER BY order_no LIMIT 100;输出里会有一行行类似 actual time0.123..0.456 rows100 的数据actual time 的第一段是取第一行耗时第二段是取全部行耗时rows 是实际行数。对比估算 rows 和实际 rows一眼就能看出优化器猜得准不准。二是结合 optimizer_trace 看优化器为什么选这个计划MySQL。如果 EXPLAIN 结果让你很困惑比如明明有更好的索引优化器偏不用可以用 OPTIMIZER_TRACE 查看优化器内部决策过程。设置方式SET SESSION optimizer_trace enabledon; -- 执行你的 SQL SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE;优化器会记录它分别考虑了哪些索引、每种方案的估算成本是多少最后为什么选了这个。这类信息对理解优化器行为特别有帮助尤其是排查“为什么没走索引”这种问题时基本能找到确切的成本计算依据。三是分析前先排除环境因素。拿线上执行计划之前先看数据库当前负载、锁等待、临时表空间、磁盘 I/O 这些指标。如果当时 CPU 已经 100% 或者磁盘 I/O 延迟很高任何 SQL 都可能慢这时候抓到的执行计划没有太大参考价值。我一般会等一个相对空闲的时间窗口再做计划分析或者用历史快照Oracle AWR、MySQL performance_schema来做。5.3 一些关于心态和习惯的体会多看执行计划这个习惯确实需要一段时间养。我自己带人时有一个要求凡是手上处理过的慢 SQL必须把优化前后的执行计划、耗时数据、改动内容保存下来按周汇总一次。慢慢你就会积累出一套属于自己团队的“慢 SQL 案例库”以后再碰到类似的 SQL不用看执行计划都能猜到大概问题。这不是靠背八股背出来的是靠一条一条实践堆出来的。另外我特别想提醒一点优化 SQL 前先确认业务需求本身是否合理。你以为用户真的需要一次查一万条订单然后全量渲染吗很多时候需求方只是懒得分页你优化半天索引不如在应用层加个 limit效果立竿见影。索引不是万能的让数据库少干活往往比让数据库干得更快更有效。最后说个很多人忽略的小技巧一条慢 SQL 优化完不要只复测一次。把表里数据量再涨 30% 左右重新跑一遍看执行计划是否依然稳定走预期索引。因为优化器在数据量变化后可能重新评估成本换一条访问路径。如果数据量增长后执行计划回到了全表扫描说明你的 SQL 写法或索引结构对数据分布过于敏感这种优化不具备长期稳定性。我见过不少人优化完当时挺快一个月后数据涨了慢 SQL 又原样回来了就是没做这一步。 慢SQL优化实战：用EXPLAIN读懂执行计划，告别背八股
背了那么久的慢 SQL 八股不如动手跑一遍 EXPLAIN我之前带过几个新人聊起慢 SQL 优化个个能把“最左前缀、回表、覆盖索引、ICP”背得滚瓜烂熟可是一到现场拿到一条真实慢 SQL看着 EXPLAIN 的输出却不知道怎么下手。说实话这太正常了。八股是别人嚼过的结论EXPLAIN 才是你自己亲手剖开 SQL 执行过程的第一现场。这篇文章我打算用一条线上真实出现过的慢 SQL 做例子从准备数据到看执行计划再到优化前后对比完整走一遍。同时会把 MySQL 和 Oracle 两种数据库的执行计划读法都讲清楚毕竟很多团队手里同时管着两套库。如果你正准备系统学慢 SQL 优化或者被一条查了几秒甚至几十秒的 SQL 折磨过这篇内容应该能帮你少走不少弯路。1. 为什么纠结执行计划而不是继续背索引口诀1.1 慢 SQL 排查的第一现场不在慢日志在执行计划慢查询日志能告诉你哪条 SQL 慢、慢了多少秒但它不会告诉你为什么会慢。索引口诀能告诉你要建索引但建在哪一列、为什么这么建它说不清楚。EXPLAIN 是数据库优化器在执行 SQL 前生成的一份“行驶路线图”它展示了优化器打算用哪种方式访问表、用哪个索引、预计扫描多少行、是否需要回表、是否需要排序。你只有学会读这张图才能真正判断一条 SQL 是索引没走对、统计信息有误、还是 SQL 写法本身有问题。我面试时经常问一个问题你优化过最复杂的慢 SQL 是什么很多人的回答都是“加了索引就快了”但这其实是结果不是思路。真正的排查应该是发现慢 SQL 后先抓执行计划看访问路径是否合理再看实际耗时卡在哪个环节最后才决定是加索引、改 SQL、还是调整参数。顺序一旦反了很容易“病急乱投医”。1.2 执行计划到底在回答什么问题如果只能从 EXPLAIN 里读一件事我建议读访问类型也就是 MySQL 里的 type 列、Oracle 执行计划里的 TABLE ACCESS 方式。它直接告诉你数据库是用什么姿势在找数据是全表扫一遍还是通过索引精确定位或者是通过索引范围扫描。这就好比你要在一本字典里找一个字你是从头翻到尾还是先通过拼音索引定位到具体的页再翻那一页两者效率差了几十上百倍。还有一个容易被忽略的问题执行计划是基于当前统计信息做的估算它不等于 SQL 的真实执行轨迹。MySQL 的 EXPLAIN 不会真的跑 SQL它只是根据统计信息“猜”一个方案而 MySQL 8.0 的 EXPLAIN ANALYZE 才是真正执行并返回实测数据。Oracle 的 DBMS_XPLAN 也有类似区别默认显示计划加上 FORMAT 参数可以看到运行时的实际行数和耗时。这一点很多人会踩坑拿 EXPAIN 的预估 rows 当成实际扫描行数结果和真实情况差了一个数量级排查方向完全跑偏。2. 动手前的必要准备先把慢 SQL 稳定复现出来2.1 慢查询日志配置与抓取优化一条慢 SQL第一步不是优化而是能稳定复现它。如果这条 SQL 只是偶发抖动大概率不是执行计划本身的问题而是锁等待、资源争用或者统计信息突然过期。只有稳定复现的慢 SQL才值得你一条条去抠执行计划。在 MySQL 里我习惯先打开慢查询日志并合理设置阈值-- 查看当前慢查询配置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; -- 临时开启重启失效适合在测试库使用 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time 建议线上从 1 秒开始如果慢 SQL 太多可以逐步调到 2 秒或 5 秒。log_queries_not_using_indexes 也建议打开它能把没走索引的查询记下来这类 SQL 即使耗时没超过阈值也可能在数据量增长后成为定时炸弹。日志抓出来之后千万别急着去 EXPLAIN先看 SQL 里的 WHERE 条件、JOIN 条件和 ORDER BY 字段把表结构、数据量大概摸一遍。很多慢 SQL 看一眼表结构就知道问题在哪EXPLAIN 只是帮你确认判断。2.2 造一份能说明问题的测试数据如果线上不能随便跑 EXPLAIN有些公司对生产库执行计划抓取有限制你需要在测试环境造一份“能说明问题”的数据。注意不是随便塞几万行就行而是要让数据的分布、表的数据量级和线上尽量接近。我见过太多人拿 10 万行的表测出来索引没问题结果线上 1 亿行的数据慢成狗。索引失效的场景、优化器选择全表扫描的阈值、JOIN 的行为都和表的规模直接相关。我的建议是测试环境的数据量至少做到线上的 1/10关键大表尽量做到 1/3 以上并且要在测试表上执行 ANALYZE TABLE 刷新统计信息否则你 EXPLAIN 出来的计划可能和线上完全不同。造数据时有个省事技巧用递归 CTE 批量生成序列数据然后用随机函数填充业务字段比一条条插入快得多-- 示例批量生成 100 万行测试数据 INSERT INTO orders (id, user_id, order_no, amount, status, created_at) SELECT seq, FLOOR(RAND() * 500000) 1, CONCAT(NO, LPAD(seq, 10, 0)), ROUND(RAND() * 1000, 2), FLOOR(RAND() * 5), DATE_ADD(2023-01-01, INTERVAL FLOOR(RAND() * 600) DAY) FROM ( WITH RECURSIVE seq_cte AS ( SELECT 1 AS seq UNION ALL SELECT seq 1 FROM seq_cte WHERE seq 1000000 ) SELECT seq FROM seq_cte ) t;这类数据虽然随机性比较强但用来验证执行计划已经足够。如果你想模拟更真实的字段分布比如某个状态值只占 1%可以再加一层 CASE WHEN 控制比例。2.3 搭建最小复现环境的注意事项我踩过不少次坑总结下来有三点值得提醒一是不要在小数据量上直接下结论。10 万行数据里优化器可能觉得全表扫描比用索引更快因为要扫描的块不多回表的开销几乎可以忽略等数据到了千万级执行计划瞬间就变了。你必须在接近真实体量的环境里测试。二是统计信息一定记得更新。MySQL 里执行 ANALYZE TABLE 表名Oracle 里用 DBMS_STATS.GATHER_TABLE_STATS让优化器拿到最新的数据分布信息否则执行计划是“过期的地图”按图索骥找不到问题。三是每次改完 SQL 或索引重新 EXPLAIN 之前先清缓存。MySQL 里可以执行 RESET QUERY CACHE如果还在用或者用 FLUSH TABLES 让表重新打开Oracle 里可以 ALTER SYSTEM FLUSH SHARED_POOL测试库专属操作线上千万别做。不清缓存的情况下你看到的时间可能来自 Buffer Pool 的命中数据根本没走磁盘。3. 从一条真实慢 SQL 开始手把手读懂 MySQL EXPLAIN3.1 完整示例建表、造数、慢 SQL 复现下面这条 SQL 是我去年在处理一个订单报表需求时真实遇到的。需求很简单查某个用户最近 30 天的订单总额并且按订单号排序。先看表结构CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(12,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, KEY idx_user_created (user_id, created_at), KEY idx_created (created_at) ) ENGINEInnoDB;业务慢 SQL 长这样SELECT user_id, order_no, amount, created_at FROM orders WHERE user_id 10001 AND created_at 2023-06-01 00:00:00 AND created_at 2023-07-01 00:00:00 ORDER BY order_no LIMIT 100;这条 SQL 看起来简单但线上执行时间一度超过 3 秒。问题出在排序字段 order_no 并不在索引里优化器要么先按索引查出来 1 万行再用 filesort 排序要么干脆直接全表扫。我们来 EXPLAIN 看一下。3.2 逐列拆解type、key、rows、Extra 怎么配合看执行下面这条命令EXPLAIN SELECT user_id, order_no, amount, created_at FROM orders WHERE user_id 10001 AND created_at 2023-06-01 00:00:00 AND created_at 2023-07-01 00:00:00 ORDER BY order_no LIMIT 100\G输出如下我用的是 MySQL 8.0列名比 5.7 稍有变化id: 1 select_type: SIMPLE table: orders partitions: NULL type: ref possible_keys: idx_user_created, idx_created key: idx_user_created key_len: 8 ref: const rows: 12840 filtered: 20.50 Extra: Using where; Using index condition; Using filesort逐列看下来。type 是 ref说明通过非唯一索引等值匹配找到了候选行这已经不算最差的情况因为没有走 ALL 全表扫描。possible_keys 里列出了两个可能用的索引优化器选了 idx_user_created也就是 (user_id, created_at) 这个联合索引。key_len 是 8说明只用了联合索引的第一列 user_idBIGINT 8 字节作为等值条件created_at 的部分没有进入索引匹配。rows 估算 12840 行表示预计扫描这么多行。filtered 是 20.50表示满足 created_at 条件的行大约占扫描行数的 20.5%。也就是说优化器认为符合条件的有 2632 行。Extra 里出现了三个关键信息Using where 表示需要回表后过滤其余条件Using index condition 说明用上了索引下推ICPcreated_at 的范围条件会在存储引擎层先过滤一部分Using filesort 是这里最扎眼的说明你要的 ORDER BY order_no 没法通过索引顺序直接返回需要额外排序。3.3 定位这条 SQL 慢在哪从执行计划来看这条 SQL 的问题可以拆成两块第一块是回表量不小。user_id 的数据分布如果比较均匀单用户订单可能就数千行再按状态过滤后剩下的仍要回表读取整行数据。第二块是排序。order_no 这个字段在联合索引里并不存在拿到的数据只能是按索引顺序排列的也就是先按 user_id 排再按 created_at 排但业务要求按 order_no 排于是优化器必须把扫描得到的行丢进排序缓冲区做一次 filesort。单看这里filesort 在小结果集上可能并不致命但当数据行数上万时排序内存和临时表的开销会直接拖垮查询。顺手说一下有些人对 filesort 有误解觉得只要出现 filesort 就是慢查询的元凶。其实 filesort 分为内存排序和磁盘排序两类如果排序数据量小于 sort_buffer_size它完全发生在内存里速度很快。真正要警惕的是排序数据量超过 sort_buffer_size导致优化器不得不使用临时文件这时才会出现大量的磁盘 I/O。这条 SQL 之所以变成典型慢 SQL就是因为满足条件的行数有 2000 多排序又叠加了回表访问才把耗时推到了 3 秒以上。3.4 优化效果验证改动后重新跑 EXPLAIN针对上面的问题最直接的优化思路是建一个覆盖索引把排序字段也放进索引里ALTER TABLE orders ADD INDEX idx_user_created_order (user_id, created_at, order_no);这个联合索引的列顺序有讲究user_id 作为等值条件放最左边created_at 作为范围条件放第二位order_no 作为排序字段放最后。因为 MySQL 的索引结构天然是有序的同一 user_id 下 created_at 升序同一时间点下 order_no 也能保持有序这就有机会省掉 filesort。加完索引后重新执行 EXPLAINEXPLAIN SELECT user_id, order_no, amount, created_at FROM orders WHERE user_id 10001 AND created_at 2023-06-01 00:00:00 AND created_at 2023-07-01 00:00:00 ORDER BY order_no LIMIT 100\G输出变成了id: 1 select_type: SIMPLE table: orders partitions: NULL type: range possible_keys: idx_user_created, idx_created, idx_user_created_order key: idx_user_created_order key_len: 16 ref: NULL rows: 2632 filtered: 100.00 Extra: Using where; Using index注意两个变化。type 从 ref 变成了 range说明 created_at 的范围条件也参与到了索引访问中而不是只靠 user_id 等值匹配。key_len 从 8 变成了 16联合索引真正用到了两列。Extra 里 Using filesort 消失了变成了 Using index意思是查询所需的列都在索引里可以直接取得连回表都省了。优化前后对比rows 从 12840 降到 2632少了回表少了排序。线上优化后的实际耗时从 3.2 秒降到了 30 毫秒左右。这个优化案例里真正起关键作用的不是某个口诀而是理解了“索引既组织数据也组织顺序”这个底层逻辑。4. Oracle 执行计划怎么读以及“长时间锁定表”怎么排查4.1 从 EXPLAIN PLAN 到 DBMS_XPLANOracle 和 MySQL 的执行计划查看方式差异很大网上关于 MySQL 的资料铺天盖地但 Oracle 的反而少。如果你工作环境里同时有两种数据库建议都掌握基本用法。Oracle 里想为一个 SQL 生成执行计划常规做法是先 EXPLAIN PLAN FOR再用 DBMS_XPLAN 展示。以一条很常见的慢 SQL 为例EXPLAIN PLAN FOR SELECT o.order_id, o.amount, c.customer_name FROM orders o JOIN customers c ON o.customer_id c.customer_id WHERE o.created_at DATE 2023-01-01 AND o.status PAID; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);输出里有一列叫 OPERATION它负责描述每一步在干什么。你会看到诸如 TABLE ACCESS FULL、INDEX RANGE SCAN、NESTED LOOPS、HASH JOIN、SORT ORDER BY 这样的字样这些才是判断性能的关键。不过我更喜欢用 DBMS_XPLAN.DISPLAY_CURSOR 直接查看 SQL 已经执行过的真实计划因为 EXPLAIN PLAN 只是优化器的估算而实际执行计划能看到真实的行数和执行次数。格式如下SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id 你的SQL_ID, format ALLSTATS LAST));想要拿到 SQL_ID最简单的方式是 v$sql 视图SELECT sql_id, sql_text, elapsed_time, executions FROM v$sql WHERE sql_text LIKE %你要找的关键词% AND sql_text NOT LIKE %v$sql%;v$sql 里还有 elapsed_time、executions、buffer_gets 这些运行指标对排查线上真实慢 SQL 很有参考意义。4.2 执行计划里最值得先看的几个指标很多 Oracle DBA 看执行计划喜欢盯 Cost但我不建议新手一上来就看 Cost。Cost 是相对值不同版本、不同统计信息下可能完全不同。我更建议先看三样东西。一是看有没有全表扫描。全表扫描不一定慢小表扫描比走索引还快但如果扫描发生在 1 亿行的订单表上而且你在 WHERE 条件里明明写了筛选能力很强的字段那基本说明索引没建对或者统计信息过期了。二是看每一步的返回行数。Oracle 执行计划里用 Rows 表示估算返回行数用 A-Rows 表示实际返回行数。如果两者差距太大说明优化器对数据分布的判断有偏差常见原因是统计信息陈旧或者是绑定变量导致优化器无法准确估算。我的经验是 A-Rows 比 Rows 更可信排查问题时要优先看 A-Rows。三是看排序和 hash 操作。SORT ORDER BY、SORT GROUP BY、HASH JOIN 是最容易消耗内存和临时表空间的操作。一个查询如果在执行计划里出现多个 SORT 操作同时又因为数据量大导致内存不足性能基本不会好。这个时候优先考虑在 SQL 层面消除排序比如利用索引顺序或者调整 PGA 相关的内存参数。4.3 锁等待导致的“慢 SQL”为什么执行计划看不出端倪回到热词里那个 Oracle 查询慢 SQL 和长时间锁定表的问题。这类场景我遇到很多次开发同学给我发消息说“这条 SQL 上线后突然变慢平时几十毫秒现在卡了五分钟都不出来是不是执行计划变差了”结果我抓了执行计划一看访问路径非常完美索引也走了预估行数也对按说没理由慢。这时候我一般会顺手查一下阻塞和锁等待的情况。如果是锁等待导致的慢你再怎么优化 SQL 也没用因为 SQL 根本没在跑它被卡住了。判断锁等待Oracle 里最简单的方式是查 v$session 里的事件名称。如果大量会话的 EVENT 是 enq: TX - row lock contention、enq: TX - allocate ITL entry、或者 library cache lock 之类问题根本不在 SQL 执行计划上而在并发控制上。你可以用下面的查询定位当前所有会话在等什么SELECT sid, event, wait_class, blocking_session, seconds_in_wait, state FROM v$session WHERE wait_class ! Idle AND state WAITING ORDER BY seconds_in_wait DESC;blocking_session 字段如果非空说明当前会话被另一个 SID 阻塞了。最常见的锁问题是事务长时间不提交导致其他会话要更新的行被锁住。你查到了阻塞源头 SID再去 v$session 里看那个会话的 SQL_ID基本就能锁定是哪条 DML 没提交。MySQL 里对应的是 sys.innodb_lock_waits 表或 performance_schema 的锁等待视图同样可以查到谁阻塞了谁。这类问题的共性在于它不是 SQL 优化能解决的而是业务逻辑、事务长度、并发提交频率的问题。必须从代码层面减少长事务、优化提交频率或者调整数据库的事务隔离级别、锁等待超时参数。4.4 排查锁问题的常规手段和预防思路排查锁问题我习惯按三层来走。第一层看“等锁的会话”。锁定正在等待资源的会话以及等待了多久这能帮你判断问题有多严重是偶发还是已经积累了几百个会话。第二层看“持锁的会话”。找到等待会话的 blocking_session 对应的会话它在执行哪条 SQL、开启事务多久了、连接来自哪台应用服务器。如果是应用侧开启事务后忘记提交这就是代码问题要通报开发修复。如果是某个批量更新脚本跑了太久就要评估是否分批次提交。第三层看历史。Oracle 的 DBA_HIST_ACTIVE_SESS_HISTORY 里记录了历史活跃会话的等待事件MySQL 8.0 的 performance_schema.events_statements_history 也有类似能力。查看历史可以帮助你确认这个锁等待是上线新功能后才出现的还是一直都存在的长尾问题。再补充一个预防层面的思路长时间锁定表的根因大多是事务过大。比如一个夜里跑的定时任务按月份循环更新数据每个循环都不提交跑了一小时就把一整张表的行锁都快占满了。这种情况即使每条更新语句本身都很快整个任务的锁持有时间也会让业务侧完全没法做 DML。建议分批提交每处理 1000 或者 5000 行就 COMMIT同时监控事务持续时间。另外Oracle 的 undo_retention 和 MySQL 的 innodb_lock_wait_timeout 也要根据业务容忍度合理设置前者决定读一致性快照能回看多长后者决定锁等待最多坚持多久别一味调大。5. 常见问题与排查技巧实录5.1 几个容易误判的场景第一个场景EXPLAIN 里走了索引但 SQL 还是慢。这种我见太多了。走索引不代表性能一定好如果索引的区分度不够高比如在 status 列上建索引一共只有 0、1 两个值优化器扫出来的行数可能接近全表的一半再加上大量回表和随机 I/O不见得比全表扫描快。碰到这种情况你应该去看 key_len 和 rows 列判断到底用到了索引的哪些列、预估扫描了多少行。第二个场景MySQL 5.7 和 8.0 的 EXPLAIN 输出列不同。5.7 有 possible_keys、key、key_len、ref、rows、Extra8.0 在此基础上新增了 indexed_columns 等列同时把部分信息合并进 EXPLAIN ANALYZE。如果你习惯了 5.7 的列名在 8.0 里会稍微不适应但核心概念一致。第三个场景用 EXPLAIN 去验证生产环境的慢 SQL却故意在 WHERE 条件里写死一个线上不可能出现的值比如 user_id 0。这种操作会严重误导优化器的估算因为不同 user_id 的订单量差异可能很大优化器基于某个特例生成的执行计划完全不适用于真实业务。优化器是根据具体条件值来估算的换一个 user_idrows 和访问路径可能天差地别。5.2 排查技巧整理我给几条实测下来很受用的经验供大家参考。一是使用 EXPLAIN ANALYZE 看真实耗时MySQL 8.0.18。EXPLAIN ANALYZE 会真正执行 SQL并返回每个步骤实际的时间和行数比普通 EXPLAIN 的估算值可靠得多。用法和普通 EXPLAIN 类似EXPLAIN ANALYZE SELECT user_id, order_no, amount, created_at FROM orders WHERE user_id 10001 AND created_at 2023-06-01 00:00:00 AND created_at 2023-07-01 00:00:00 ORDER BY order_no LIMIT 100;输出里会有一行行类似 actual time0.123..0.456 rows100 的数据actual time 的第一段是取第一行耗时第二段是取全部行耗时rows 是实际行数。对比估算 rows 和实际 rows一眼就能看出优化器猜得准不准。二是结合 optimizer_trace 看优化器为什么选这个计划MySQL。如果 EXPLAIN 结果让你很困惑比如明明有更好的索引优化器偏不用可以用 OPTIMIZER_TRACE 查看优化器内部决策过程。设置方式SET SESSION optimizer_trace enabledon; -- 执行你的 SQL SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE;优化器会记录它分别考虑了哪些索引、每种方案的估算成本是多少最后为什么选了这个。这类信息对理解优化器行为特别有帮助尤其是排查“为什么没走索引”这种问题时基本能找到确切的成本计算依据。三是分析前先排除环境因素。拿线上执行计划之前先看数据库当前负载、锁等待、临时表空间、磁盘 I/O 这些指标。如果当时 CPU 已经 100% 或者磁盘 I/O 延迟很高任何 SQL 都可能慢这时候抓到的执行计划没有太大参考价值。我一般会等一个相对空闲的时间窗口再做计划分析或者用历史快照Oracle AWR、MySQL performance_schema来做。5.3 一些关于心态和习惯的体会多看执行计划这个习惯确实需要一段时间养。我自己带人时有一个要求凡是手上处理过的慢 SQL必须把优化前后的执行计划、耗时数据、改动内容保存下来按周汇总一次。慢慢你就会积累出一套属于自己团队的“慢 SQL 案例库”以后再碰到类似的 SQL不用看执行计划都能猜到大概问题。这不是靠背八股背出来的是靠一条一条实践堆出来的。另外我特别想提醒一点优化 SQL 前先确认业务需求本身是否合理。你以为用户真的需要一次查一万条订单然后全量渲染吗很多时候需求方只是懒得分页你优化半天索引不如在应用层加个 limit效果立竿见影。索引不是万能的让数据库少干活往往比让数据库干得更快更有效。最后说个很多人忽略的小技巧一条慢 SQL 优化完不要只复测一次。把表里数据量再涨 30% 左右重新跑一遍看执行计划是否依然稳定走预期索引。因为优化器在数据量变化后可能重新评估成本换一条访问路径。如果数据量增长后执行计划回到了全表扫描说明你的 SQL 写法或索引结构对数据分布过于敏感这种优化不具备长期稳定性。我见过不少人优化完当时挺快一个月后数据涨了慢 SQL 又原样回来了就是没做这一步。