背了那么久的慢 SQL 八股，不如动手跑一遍 EXPLAIN

写在前面之前写了一篇关于慢请求排查的实战记录传送门本来以为把 Arthas 追调用链、排查 O(n²) 循环什么的讲清楚就差不多了。但写完之后总觉得少了点什么。回想自己背面试题的时候”深分页为什么慢”、”子查询为什么不如 JOIN”、”索引失效的场景有哪些” 这些问题能说得头头是道。可真要我解释清楚到底慢在哪里、具体慢多少我发现自己其实也就知道个大概。所以趁周末我搭了个测试环境自己动手跑了一遍 EXPLAIN把那些背烂了的八股一个个验证了一遍。纸上得来终觉浅绝知此事要躬行。——古人诚不我欺。⚠️说明文中所有截图均为本地测试环境数据。因为不同的数据量、不同的机器配置跑出来的结果差异很大案例一深分页到底”深”在哪里被问烂了的问题面试的时候十有八九会问到”MySQL 分页为什么越往后越慢怎么优化”标准回答人人会背LIMIT 90000, 10虽然只要 10 条但 MySQL 得先捞 90000 行再丢掉偏移量越大越慢。优化用游标分页。但说实话我之前从来没亲眼见过这个过程到底有多慢。于是动手试了一下。先看看正常的查询是什么样作为对比基准先跑一条最普通的全表查询SELECT * FROM orders;此时表里数据量不大查询很快。下面我们模拟深分页的场景。写一个”教科书式”的慢 SQLSELECT * FROM orders ORDER BY id LIMIT 90000, 10;执行耗时0.425秒扫描行数约 90000 行只取 10 条数据却翻了 9 万行。这个反差确实很直观。看看 EXPLAIN 怎么说跑一下EXPLAIN看看 MySQL 自己是怎么说的EXPLAIN SELECT * FROM orders ORDER BY id LIMIT 90000, 10;字段值typeindexrows90010ExtranullMySQL 老老实实告诉你它打算扫多少行——跟你想的差不多是吧换成游标分页SELECT * FROM orders WHERE id 90000 ORDER BY id LIMIT 10;执行耗时0.238扫描行数仅 10 行再跑一遍 EXPLAINEXPLAIN SELECT * FROM orders WHERE id 90000 ORDER BY id LIMIT 10;字段值typerangerows99629ExtraUsing where一点感想实际跑完一圈下来最直观的感受就是纸上说的”扫描行数”和”执行时间”不是抽象概念是实打实能看得到的差距。以前背八股的时候”LIMIT 90000, 10 需要扫描 90010 行” 这句话对我来说就是个知识点。当我在终端看到 rows 那栏的数字时才发现——哦原来这就是”多扫了 9 万行”的感觉。优化建议能用游标分页就用游标分页这也是现在主流业务的做法实在要传统分页考虑在前端限制最大页数比如只让翻 100 页覆盖索引 延迟关联也是路不过不如游标分页直接案例二关联子查询 vs JOIN——面试题的”照妖镜”又一个经典场景SELECT o.*, (SELECT u.phone FROM users u WHERE u.id o.user_id) AS phone FROM orders o WHERE o.status 1;这条 SQL 看起来挺”优雅”的——一条语句就搞定了订单和用户的关联还不用 JOIN。但实际上呢执行耗时0.608问题在哪关联子查询Correlated Subquery的特点是外层查出来多少行内层子查询就跑多少遍。假设 orders 表有 1 万条status 1的记录那这个子查询就被执行 1 万次。就算子查询走了索引1 万次也是 1 万次开销。EXPLAIN 看一下EXPLAIN SELECT o.*, (SELECT u.phone FROM users u WHERE u.id o.user_id) AS phone FROM orders o WHERE o.status 1;字段值typeALLorders/ eq_refusersrows199258orders/ 1usersExtraUsing whereorders/ nullusers换成 JOIN 试试SELECT o.*, u.phone FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.status 1;执行耗时0.546EXPLAINEXPLAIN SELECT o.*, u.phone FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.status 1;字段值typeALLorders/ eq_refusersrows199258orders/ 1usersExtraUsing whereorders/ nullusers一点感想这个案例其实是我最”震撼”的。背了无数遍”子查询不如 JOIN”但自己动手验证之前我一直以为这差别也就一点点。跑完之后发现一个 N 次子查询和一个 JOIN在数据量上去之后完全不是一个量级。但是这也不是绝对的——我就见过有人把大表 JOIN 连到七八个结果比子查询还慢。工具是死的人是活的具体情况具体分析才是正解。案例三IN (1,2,3,…,2000) vs BETWEEN——一个被忽视的细节场景SELECT * FROM orders WHERE user_id IN (1,2,3,...,2000);这个写法在某些 ORM 或者代码生成器里很常见——查一批用户的订单顺手就把 ID 列表拼成 IN 了。执行耗时0.626秒EXPLAIN 看看EXPLAIN SELECT * FROM orders WHERE user_id IN (1,2,3,...,2000);字段值typeALLrows199258ExtraUsing where换成 BETWEENSELECT * FROM orders WHERE user_id BETWEEN 1 AND 2000;执行耗时0.573秒EXPLAINEXPLAIN SELECT * FROM orders WHERE user_id BETWEEN 1 AND 2000;字段值typeALLrows199258ExtraUsing where什么场景有效INvsBETWEEN这个对比有一个前提你的值得是连续的范围。如果你要查的是IN (3, 7, 15, 22, 99)这种离散值那 BETWEEN 当然没法用。这时候可以考虑创建临时表把目标值塞进去用 JOIN 取代 IN分批查询控制每批 IN 列表个数MySQL 默认eq_range_index_dive_limit是 200案例四LIKE 模糊匹配——前% vs 后%场景SELECT * FROM orders WHERE remark LIKE %keyword%;很多时候业务需求确实需要”前模糊”查询但代价是什么呢执行耗时0.352秒EXPLAIN 看看EXPLAIN SELECT * FROM orders WHERE remark LIKE %keyword%;字段值typeALLrows199258ExtraUsing where改用后缀匹配SELECT * FROM orders WHERE remark LIKE keyword%;如果业务上能接受只查”以 keyword 开头”的记录性能差距可不是一星半点执行耗时0.326秒EXPLAIN 对比EXPLAIN SELECT * FROM orders WHERE remark LIKE keyword%;字段值typeALLrows199258ExtraUsing where一点感想这个知识点背八股的时候记得可牢了——”LIKE 前面加 % 会导致索引失效”。但是真的跑完对比一看差距比我想象中大得多。但是说实话很多业务场景就是需要”包含”而非”以什么开头”这时候想用索引只能上全文索引FULLTEXT或者ES 搜索引擎了。建议能用后缀匹配就用后缀匹配keyword%直接走索引非要前模糊且数据量大考虑引入 ES数据量小的表百万以内MySQL 做全表扫描也能接受看实际场景也可以考虑用覆盖索引减少回表开销案例五OR 条件——索引的”隐形杀手”场景SELECT * FROM orders WHERE user_id 100 OR amount 5000;OR 条件在八股里也是高频考点——OR 两边必须都有索引才可能走索引否则全表扫描。执行耗时0.490秒EXPLAIN 看看EXPLAIN SELECT * FROM orders WHERE user_id 100 OR amount 5000;字段值typeALLrows199258ExtraUsing where优化思路OR 的最佳替代方案是UNION ALL——把两条各自能走索引的查询拆开再合并结果SELECT * FROM orders WHERE user_id 100 UNION ALL SELECT * FROM orders WHERE amount 5000 AND user_id ! 100;这条 SQL 的截图就不放了大家感兴趣可以在本地试一下一点感想OR 这个 case 说实话在日常开发中见得不算太多但一旦遇到如果数据量大就非常头疼。面试也高频算是八股里的标配题了。优化建议优先考虑用UNION ALL替代 OR确保 UNION 中每个子查询都能独立走索引如果 OR 两边的条件能转化为 AND优先用 AND案例六IS NOT NULL——NULL 值的麻烦场景SELECT * FROM orders WHERE remark IS NOT NULL;IS NULL 和 IS NOT NULL 能不能走索引答案是取决于数据分布和 MySQL 版本。但 IS NOT NULL 在数据比率高的情况下大概率全表扫描。执行耗时0.464秒EXPLAINEXPLAIN SELECT * FROM orders WHERE remark IS NOT NULL;字段值typeALLrows199258ExtraUsing where一点感想NULL 值在 MySQL 里本身就是个”不太干脆”的存在。IS NOT NULL 不走索引的核心原因是如果大部分行的 remark 都不为 NULL那优化器认为走索引还不如全表扫描快。反过来如果IS NOT NULL筛选后只剩很小一部分数据是有可能走索引的。优化建议表设计时尽量避免 NULL用 NOT NULL 默认值空字符串、0 等如果业务必须用 NULL考虑缓存其他字段的索引值实际查询中IS NOT NULL是最后的选择——能加其他条件就尽量加案例七多条件查询——无联合索引 vs 有联合索引场景当我们在WHERE中写了多个条件时有没有联合索引差别有多大我创建了一个(user_id, status)的联合索引来做对比。先看看只用 status 条件查询——无联合索引发挥作用SELECT * FROM orders WHERE status 2;只用 status 条件时联合索引user_id, status用不上违背最左前缀原则效果很差执行耗时0.434秒EXPLAIN SELECT * FROM orders WHERE status 2;字段值typeALLrows199258ExtraUsing where再看看带上 user_id 条件——联合索引生效SELECT * FROM orders WHERE user_id 100 AND status 2;带上 user_id 后联合索引user_id, status就能完美利用差距非常明显执行耗时0.232秒EXPLAINEXPLAIN SELECT * FROM orders WHERE user_id 100 AND status 2;字段值typerefrows80Extranull一点感想这个 case 和前面的 OR 形成有趣对比——AND 在一个合适的联合索引下可以高效检索OR 却很难用好索引。同样是对多个字段做条件筛选一个 AND 一个 OR性能天差地别。而这里更想强调的是最左前缀原则——(user_id, status)联合索引只对包含user_id的查询有效光查status是走不上的。建了索引不等于能用上还得看怎么写的。联合索引优化建议(user_id, status)的联合索引能完美覆盖WHERE user_id ? AND status ?这类查询联合索引的顺序很重要高频查询、选择性高的字段放左边不一定要给每个 AND 条件都建联合索引单列索引 索引合并也能解决问题但索引合并有额外开销数据量大时联合索引更优案例八类型转换导致的索引失效场景有时候表里明明有索引查询还是慢——别急着怀疑索引坏了先看看类型匹不匹配。假设order_no字段是 VARCHAR 类型也建了索引。跑两个看起来差不多的查询结果天差地别。正确写法带上引号SELECT * FROM orders WHERE order_no 4;字符串查字符串类型匹配索引正常生效执行耗时0.216秒EXPLAINEXPLAIN SELECT * FROM orders WHERE order_no 4;字段值typerefrows1Extranull错误写法没加引号隐式类型转换SELECT * FROM orders WHERE order_no 4;整数比较 VARCHAR——MySQL 会对字段做隐式类型转换索引直接失效执行耗时0.399秒EXPLAINEXPLAIN SELECT * FROM orders WHERE order_no 4;字段值typeALLrows199258ExtraUsing where同样有索引只是一个引号的区别执行计划就完全不同了。一点感想这个 case 其实比”缺少索引”更隐蔽——少了索引你一眼就能看出来 EXPLAIN 的 type 是 ALL但类型不匹配的时候你以为索引已经建了应该没问题结果 MySQL 默默地做了类型转换索引形同虚设。优化建议写 SQL 时注意字段类型VARCHAR 字段一定要加引号代码中用 ORM 的话参数类型要和数据库字段类型对齐排查慢 SQL 时如果发现索引建了但没走先看有没有类型不匹配的问题八个案例放在一起看场景问题优化方向关键点LIMIT 深分页LIMIT 90000, 10 扫了再丢游标分页 WHERE id xxx利用主键索引精准定位关联子查询外层每行执行一次子查询改为 JOINJOIN 一次搞定IN 大量值2000 个值逐个索引查找BETWEEN 或临时表 JOIN连续值用范围查询LIKE 模糊%keyword% 前模糊不能走索引keyword% 后缀匹配 / 全文索引前 % 无解只能换方案OR 条件OR 两边都有索引才可能走UNION ALL 拆开拆成多条独立走索引的查询IS NOT NULL数据占比高时优化器选全表扫避免 NULL 设计NOT NULL 默认值多条件查询无联合索引时用不上最左前缀建联合索引最左前缀原则隐式类型转换索引建了但类型不匹配导致失效VARCHAR 带引号类型匹配比加索引更隐蔽通(ren)用(sheng)建议背了要动手八股可以在面试的时候帮你过关但只有亲手跑一遍 EXPLAIN那些结论才会变成你自己的经验EXPLAIN 是基本功排查慢 SQL 的时候第一件事就是 EXPLAIN。先看 type至少要 range、rows估算扫描行数和 Extra出现 filesort、temporary 要警惕索引不是银弹索引能解决很多问题但不是所有问题都能靠加索引解决。深分页、LIKE 前模糊这些问题加再多索引也没用先检查有没有索引很多慢 SQL 其实没有高深的原理——看看 WHERE 条件的列有没有对应索引就行具体情况具体分析没有放之四海皆准的优化方案写 SQL 的时候要多想想数据量级和业务场景写在最后这篇文章更像是我自己的一个学习笔记——把那些背过的、似懂非懂的八股亲手在 MySQL 里验证了一遍然后记录下自己的感受和发现。如果你也是那种”背了好多但总觉得不踏实”的人强烈建议你花一个周末搭个测试环境把常见的问题 SQL 都跑一遍 Explain。真的会有种”原来如此”的感觉。文章里的耗时和 Explain 字段我留了空因为不同的数据量级和机器配置跑出来差异很大。后续我会补上几组不同量级10 万、100 万、1000 万行的对比数据让它更有参考价值。另外关于慢 SQL你还有什么亲身经历或者踩过的坑吗欢迎在评论区分享说不定能帮到更多人 有问题或者觉得我哪里写错了也欢迎指正。我只是个在学习路上挣扎的后端开发大家互相交流一起进步。