有一次我在团队内部做 SQL 评审看到一条线上慢查询开发同学脱口而出“这个索引失效了因为查询条件里用了函数。” 我顺口追问了一句“那为什么用了函数索引就失效” 他愣住了。这个场景我遇到过太多次。大多数 SQL 开发者能背出一堆优化规则最左前缀、不要对索引列用函数、LIKE 不要用 % 开头……但如果你追问“为什么”很多人要么沉默要么丢出一句“这是 MySQL 的规则”。而判断一个 SQL 开发者到底是初级还是高级我的标准非常简单看他能不能站在存储引擎B 树的角度把这些零散规则当成理所当然的推演结果。这篇文章不打算再给你一份“优化口诀清单”而是想和你一起从 InnoDB 的 B 树这个最核心的数据结构出发把 SQL 优化里最常见的问题重新推演一遍。你会发现那些看似需要死记硬背的规则其实全部由一棵树的形状、节点、指针决定。搞懂这颗“骨架”以后再遇到慢 SQL你不再是搜肠刮肚回忆规则而是直接看穿引擎到底在做什么、为什么慢、怎么改才治本。1. 高手和普通开发者的分水岭从“背规则”到“推规则”1.1 一个常见却没人答上来的问题先看一个最常见的面试题一张 900 万行的订单表里面有联合索引(status, created_at)现在执行SELECT * FROM orders WHERE status PAID ORDER BY created_at DESC LIMIT 50;很多初级开发看到 EXPLAIN 输出typeref、keyidx_status_created就会给出结论“走了索引没问题。” 但这条 SQL 在真实场景里可能慢到让接口超时。普通开发者看到的是“有没有走索引”高级开发者看到的是“在树上走了多远、回表了多少次、有没有多余的排序”。这两者的差距就是“背规则”和“推规则”的差距。1.2 普通开发者和高手的 SQL 评审差异我在带团队时做过一次统计让大家各自分析同一条慢 SQL结果非常有意思关注点初级/中级开发者资深开发者EXPLAIN 第一眼key 有没有走索引type、rows、Extra 分别代表什么代价索引能用到就结束吗能直接交差不能还要算回表次数和扫描范围对排序的处理看到 filesort 才警惕提前想 ORDER BY 是否与索引顺序一致优化思路加索引、改 SQL围绕“让树上的路径更短”来设计遇到新问题上网搜“为什么索引失效”从 B 树结构反推可能的原因这里没有贬低初级开发的意思但 SQL 优化这个领域规则是有限的场景是无限的。你背得住 30 条规则但背不住线上千变万化的表结构和查询条件。只有从 B 树原理去理解才能在没见过的场景里做出正确判断。庖丁解牛这个故事大家都听过普通厨师一个月换一把刀庖丁的刀用了十九年还像新的一样差别在于普通厨师看到的是整头牛而庖丁看到的是牛骨节之间的缝隙。SQL 优化里的“缝隙”就是 B 树的每一个节点、指针、页的结构。2. B 树的结构与性能算术一次查询到底走了多少路2.1 树长什么样三层结构就能放下千万行数据InnoDB 默认的 B 树分为两类节点非叶子节点和叶子节点。非叶子节点只存放索引键和指向子节点的指针不存放实际数据。叶子节点存放实际数据。对于主键索引叶子节点直接存整行数据对于二级索引叶子节点存主键值。叶子节点之间通过双向链表连接所有叶子节点上的数据按索引键有序排列。为什么这个结构如此关键因为它把“查找”问题变成了“沿着树往下走”的问题。用一个不太严谨但很好懂的类比B 树就像一本按拼音排序的书。非叶子节点是目录页告诉你某个拼音在哪一章叶子节点是正文页而且是按顺序用线串起来的一叠纸你可以从“zhang”这一页直接翻到“zheng”那一页不需要回到目录。再看性能算术。InnoDB 数据页默认 16KB假设索引键是 8 字节的 BIGINT指针约 6 字节那么一个非叶子节点页大约能放下 1170 个键值对。这意味着一棵三层 B 树第三层大约可以有 1170 × 1170 ≈ 137 万个叶子页。如果每行数据平均 1KB每个叶子页大约能放十几行记录那么单表千万级数据的典型场景下B 树通常只需要三层。每次磁盘 IO 读取一个页。查询时根节点常驻内存不产生 IO数据在第三层时只需要两次磁盘 IO 就能定位到目标页。这就是为什么 MySQL 单表千万行也能做到几十毫秒级主键查询——为了找到一行数据它只需要读两个页。2.2 为什么要 B 树而不是 B 树和红黑树很多人问过“B 树是红黑树吗”答案是不是。它们是两套完全不同用途的数据结构。红黑树是内存数据结构常用于 C 的 map、Java 的 TreeMap它解决的是“内存中快速查找并自动排序”的问题。但红黑树每个节点只存一个键树高度随数据量增长明显当数据量大到磁盘时一层树就要一次磁盘 IO几十层就是几十次 IO谁也扛不住。B 树是 B 树的近亲区别在于 B 树的所有节点都存数据而 B 树只在叶子节点存数据。这意味着同样一个 16KB 的页B 树的非叶子节点能存放更多索引键树更矮磁盘 IO 更少。而且 B 树的叶子节点通过链表串联做范围查询时从头到尾顺序遍历即可不需要反复从根节点重新出发。B 树做范围查询就得来回中序遍历效率差太多了。所以数据库选 B 树选得非常有道理为了磁盘 IO 次数更少为了范围查询更快为了数据全部有序排列。2.3 聚集索引与二级索引一个存数据一个存“门牌号”InnoDB 里主键索引是聚集索引叶子节点直接存放完整行数据。而其他索引包括唯一索引都是二级索引叶子节点只存放索引键值和主键值本质上是一张“索引键 → 主键 门牌号”的查询表。这里顺便把热词里的“主键索引和唯一索引的区别”讲透对比项主键索引唯一索引叶子节点直接存整行数据只存索引键 主键值能否有 NULL不允许主键即非空允许 NULL且多个 NULL 不冲突一张表数量只能有一个可以有多个查询方式直接定位到行先定位索引再回表取整行所以你写SELECT * FROM user WHERE id 1时引擎在聚集索引树上走了两次 IO 就直接拿出了整行数据而写SELECT * FROM user WHERE email ab.com时email 有唯一索引引擎先到二级索引树上找到主键 id再回一次聚集索引树才能拿到完整行。这一步“回表”就是后面很多慢 SQL 的万恶之源。3. 索引失效的本质推演在树上这条路走不通是有原因的3.1 对索引列动手脚树的排序基准被破坏了最常见的一句话“不要对索引列使用函数。” 为什么因为 B 树里的索引键是按照原始值的有序性排列的比如created_at按时间大小排好。当你在查询里写WHERE DATE(created_at) 2025-06-01时引擎面对的问题是我不知道 “2025-06-01” 在树里对应哪个区间因为树里存的是精确到秒的值而不是日期。引擎有两个选择一是把根节点每个键都算一遍DATE()但树的高度和节点数量决定了这种“全算一遍”等价于把整棵树扫一遍二是直接全表扫描逐行计算判断。无论哪个索引的有序性都帮不上忙。同样的道理适用于一切对索引列做运算的写法WHERE id 1 100、WHERE amount * 0.8 100。只要改变了索引键的原始形态树的二分定位就失去了基准。再补充一个更容易踩坑的变体——隐式类型转换。比如phone是 VARCHAR 列你写WHERE phone 13800138000MySQL 会把字符串列转成数字再比较等于在phone上偷偷套了一层 CAST。很多人的“明明有索引却不走”问题病根就在这里。3.2 联合索引与最左前缀字典序下的“只给年龄找人”联合索引(a, b, c)到底长什么样你完全可以把它想象成一本先按姓、再按名、最后按年龄排序的通讯录。B 树第一层按a排序a相同时再按b排b也相同时再按c排。那么WHERE a 1 AND c 5能用到索引吗答案是a 1走索引c 5走不了。原因很直接在整棵树上所有a 1的数据虽然聚集在一起但在这组数据内部只保证b有序并不保证c有序。你想从一堆按名排列的人里直接查到某个年龄的人是不可能的只能把这组人全部翻一遍。这就是最左前缀原则的本质联合索引的有序性是逐层累加的跳过了前面的排序键后面的键就是无序的。MySQL 8.0 支持了 Index Skip Scan索引跳跃扫描对于前缀列区分度很低的情况可以自动拆成多个小范围扫描但这只是优化器的“补救”不是常规路径。WHERE b 5这样的查询还是老实加索引或者改造查询吧。范围查询的右侧列失效也是同一个原理。WHERE a 1 AND b 10 AND c 5中b 10可以走索引但c 5失效因为在b的每个不同取值内部c才是有序的一旦用上b 10这个范围跨越了多个b值c的整体有序性就没了。范围条件就像一把剪刀把后面列的有序性都剪断了。3.3 LIKE、OR、范围之后为什么有些条件天然没救LIKE abc%能走索引LIKE %abc不能走索引这也可以用树的有序性解释。B 树从根到叶的定位依赖的是键的“前缀对比”。你给出abc前缀树可以沿着前缀一路定位到第一个abc...的位置然后顺着叶子链表往后扫你只给%abc树根本不知道从哪个节点开始——结尾的abc不影响排序基准。OR也是一个经典的重灾区。WHERE a 1 OR b 2如果a、b各有索引优化器可以走 index_merge把两棵树的查集合并再排序去重但如果两个条件各自命中的行数都不少合并去重的成本和回表次数可能比全表扫描还高。从 B 树角度看一次查询要访问两棵树、回表更多行、最后做集合运算这些开销都是真实存在的。遇到这种情况拆成 UNION 再比较一下执行计划通常是从树的角度更优的做法。到这儿你可以总结出一个判断准则遇到任何“索引失效”的规则先问自己——这条查询在 B 树上能找到一条从根到叶子的有序定位路径吗找不到索引就走不了。4. 回表、覆盖索引与数据页执行计划里看不见的代价4.1 回表二级索引的“二次寻址”前面说过二级索引叶子节点存放的是主键值。所以当你根据二级索引找到某行数据时还差一步拿着主键值去聚集索引树上再查一次才能拿到完整的行数据。这一步就是回表。回表的代价有多大我们做一个简单的算术二级索引树查询大约 2-3 次 IO聚集索引回表又是 2-3 次 IO。单看一次回表不算什么但如果一个二级索引扫描命中了 10 万行就要回表 10 万次。而且二级索引的叶子顺序按索引键排列和主键顺序基本无关所以这些回表 IO 是随机 IO不是顺序 IO。现代 SSD 的随机 IO 能力已经很强但只要数据量和命中行数一上去随机 IO 的累积延迟依然非常可观。这就是为什么一个明明走了索引的 SQL可能比全表顺序扫还慢。4.2 覆盖索引把数据页“搬到”索引页覆盖索引的定义很简单查询的字段全部包含在索引键里引擎只访问二级索引的叶子节点就能拿全数据完全不需要回表。执行计划里会显示Using index。举个最常见的例子SELECT COUNT(*) FROM orders WHERE status PAID如果表上有(status)索引MySQL 会扫描这棵二级索引树而不是庞大的主键树。二级索引的叶子只存 status 和主键一页能装下的记录数是主键树的数倍扫描的页更少IO 更少而且完全不需要回表。很多人会惊讶于 COUNT 查询竟然可以这么快原理就在这里。写 SQL 时一个非常实用的习惯是先看查询要输出的列再想索引要不要带上这些列。如果查询高频且固定覆盖索引是性价比极高的优化手段。当然索引不是越多越好——每多一个索引写操作就要多维护一棵树所以覆盖索引要加在真正的高频查询上。4.3 页分裂与随机主键写慢的根源也在树上B 树的写入为什么有时候会突然变慢看页的工作方式就明白了。InnoDB 以 16KB 的页为单位管理数据。插入数据时如果目标页满了就必须申请新页把一部分数据挪过去保持页内数据有序这就是页分裂。页分裂会引发额外 IO 和索引页碎片。你如果用 UUID 或随机字符串做主键每次插入的键值落在树上的位置都是随机的页分裂会非常频繁。而用自增 BIGINT 主键新数据永远追加在树的最右侧几乎不触发页分裂。同一个 B 树结构就因为插入顺序的差异写入性能可以差出好几倍。我在实际项目里见过把 UUID 主键改成 BIGINT 自增后批量插入耗时直接下降 60% 以上的案例。页碎片多了之后可以周期性用OPTIMIZE TABLE收缩整理但根本解法还是主键设计要贴合树的追加写入特性。4.4 Explain 关键字段与 B 树的对应关系掌握 B 树以后看 EXPLAIN 会有一种“看人下刀”的感觉。几个关键字段对应的树语义我直接列出来EXPLAIN 字段B 树视角的含义要警惕的信号type访问路径的成本级别ALL 表示全树扫描ref/range 通常表示走了索引key实际选中的索引树key 为空时要追问为什么优化器不选rows估算要扫描的索引行数/页面范围rows 巨大说明扫描范围宽Extra: Using index覆盖索引不回表这是最理想的状态之一Extra: Using index condition用上了索引下推ICP部分过滤在索引层完成回表次数已减少但可能还有过滤要回表Extra: Using filesort排序无法复用索引顺序通常说明 ORDER BY 和索引顺序不一致Extra: Using temporary分组/去重要建临时表内存有压力尽量用索引有序性消除优化 SQL 的本质就是让这些字段往“扫描行数更少、回表次数更少、不额外排序、不建临时表”的方向靠拢。5. 三个真实慢 SQL 优化案例的完整链路5.1 深分页回表二十万次的问题某管理后台的订单列表表orders约 900 万行现有索引idx_status_created(status, created_at)。原始 SQLSELECT id, order_no, user_id, amount, status, created_at FROM orders WHERE status PAID ORDER BY created_at DESC LIMIT 200000, 50;测试表现翻到第 50 页时执行时间从几十毫秒涨到 2.1 秒并且页码越深越慢。EXPLAIN 里typeref、keyidx_status_created看着很正常rows却估算到了百万级别。站在 B 树的角度分析二级索引叶子链表虽然能按(status, created_at)顺序扫描但查询要取的是整行数据所以每扫一条符合statusPAID的索引记录都要用主键回一次聚集索引。为了跳过 20 万条记录引擎已经把前 20 万条都回表了一遍——这 20 万次随机 IO才是慢的根源。优化方案用“延迟关联”让子查询只访问二级索引树SELECT t1.id, t1.order_no, t1.user_id, t1.amount, t1.status, t1.created_at FROM orders t1 JOIN ( SELECT id FROM orders WHERE status PAID ORDER BY created_at DESC LIMIT 200000, 50 ) t2 ON t1.id t2.id;子查询SELECT id ...所需的字段全部在idx_status_created里执行时显示Using index引擎只需要在二级索引叶子链表上顺序游标移动跳过 20 万条记录全程不回表。最后只对 50 个 id 回表取整行。优化后实测约 28ms提升了近 70 倍。关键认知LIMIT 的深度本质上是“回表次数的深度”把回表次数降下来深分页就不再可怕。5.2 过滤条件藏在回表列索引下推也救不了同一个订单系统一次运营活动后出现一条新慢查询SELECT id, order_no, user_id, amount, status, created_at FROM orders WHERE status PAID AND amount 1000 ORDER BY created_at DESC LIMIT 50;测试约 700msEXPLAIN 显示Using index condition说明已经用了索引下推ICP在二级索引层面提前过滤了部分条件。但amount不在索引idx_status_created里引擎只能先把statusPAID的记录挑出来再逐行回表取amount判断。PAID 占比大约 60%900 万行里就是 500 多万行。虽然从最新时间倒序回表运气好几十行就能凑够 50 条但活动期间大额订单分布不均遇到断层时段可能要回表几千上万次才能凑够。随机 IO 一多700ms 就很正常。这次优化我没有直接在原索引上叠列而是审视了查询模式这是活动期间的固定查询条件就是statusamount排序是created_at。所以我新建了索引idx_status_amount_dt(status, amount, created_at)ALTER TABLE orders ADD INDEX idx_status_amount_dt (status, amount, created_at);从树视角看这个索引的查找路径第一步statusPAID等值定位第二步amount 1000范围扫描收缩候选集第三步候选集内created_at仍然有序可以直接倒序取 50 条连 filesort 都不需要。amount 过滤性越好收益越明显。优化后实测约 45ms。这里想说一句索引设计不是把 WHERE 后面的列全部塞进去就完事要根据等值条件、范围条件、排序方向来安排列的顺序。等值条件放最前范围条件其次排序字段放在范围条件之后才能保住有序性。5.3 函数让时间范围查询失去有序性再看一个典型的“索引失效”案例。运营需要查某一天的订单明细SELECT id, order_no, amount FROM orders WHERE DATE(created_at) 2025-06-01;900 万行全表扫描耗时约 3 秒。created_at明明有索引但 MySQL 无法从DATE(created_at) 2025-06-01中推导出树上的区间只能在每一行上计算 DATE 再做比较。树视角很简单引擎需要的是“从某个时间点到某个时间点的连续区间”而不是把每个时间值取整后再比较。改成范围条件SELECT id, order_no, amount FROM orders WHERE created_at 2025-06-01 00:00:00 AND created_at 2025-06-02 00:00:00;优化器直接走range扫描按 B 树定位到 6 月 1 日零点再顺着叶子链表一路扫到次日零点。匹配行数只有几千条耗时降到 20ms 以内。很多团队写“按天查”都喜欢用函数改造成范围后不止是这一次查询提速整个索引对时间范围类查询都产生了正的收益。能用边界值解决的问题不要让引擎干“先全算一遍再筛”的体力活。6. 把“B 树直觉”变成日常习惯6.1 写 SQL 前先给索引树“画像”我在写任何一条可能上线的 SQL 之前都会在脑子里快速回答四个问题这条 SQL 会在哪棵索引树上走从哪个节点开始定位扫描到哪里结束要回表多少次这四个问题回答不出来就别急着提交。回答出来之后很多时候你会发现自己已经想改 SQL 了。比如本来要SELECT *发现只查两三个字段就能用覆盖索引比如本来要LIMIT 300000, 20意识到回表量巨大自动改成延迟关联。6.2 把 EXPLAIN 变成肌肉记忆不要等到生产环境告警才去看执行计划。开发阶段的每条慢 SQL、每张新表的重要查询都应该先跑一遍 EXPLAIN。看到Using filesort就条件反射地看 ORDER BY 和索引顺序看到Using temporary就想办法用索引消除分组排序看到ALL就问自己为什么优化器不愿意走索引——是统计信息过期了还是没有合适索引还是过滤性太差导致全表扫反而更划算。6.3 从慢日志里收集“反例”不要只背理论理论任何时候都可以翻书但我更建议你把线上慢日志当成最重要的学习素材。每次抓到一个慢 SQL不要只满足于“A 语句改成 B 语句快了三倍”要顺手分析一下B 语句在树的哪条路径上省了什么开销是省了回表是缩小了扫描范围还是消掉了一次排序我在带人的时候常说一句话一次慢 SQL 的完整复盘胜过背十篇优化文章。因为复盘是把知识和现场绑定的过程下次遇到相似结构你会直接产生“这个 shape 看起来要回表很多次”的直觉。6.4 结合统计信息判断索引的“选择性”另外一个容易被忽略的点优化器选不选索引取决于列的区分度Cardinality。如果一个列的重复值极高比如status只有 3 种取值那么WHERE status PAID即使走了索引也可能因为命中行数太多、回表太贵而比全表扫描慢。这时候加再多的索引也没用不如先做数据分区、归档或者把查询粒度缩小。可以通过SHOW INDEX FROM orders查看 Cardinality 字段粗略估计区分度。区分度太低的列不应该放在联合索引的前缀位置。这些年带团队下来我最大的感受是真正能从“背口诀”阶段跨到“看树说话”阶段的开发者往往不是因为记性好而是因为愿意在遇到慢 SQL 的时候多问一句——引擎到底在做什么付出了什么代价。这个习惯一旦养成你会发现自己写 SQL 时的畏手畏脚消失了因为你不怕新场景了树的结构几千年来没变过所有看似诡异的问题最终都能落到节点的排序、指针的走向和 IO 的代价上。把这个骨架刻进脑子里SQL 优化对你来说就不再是玄学而是一门可以精确计算的工程学。