干过几年数据库调优的人多半都听过这样一个场景一张几千万行的业务表用户反馈查询要跑好几秒你在某个字段上加了个普通索引再跑一次变成了几十毫秒。很多同学一直处在“会用索引但说不清它为什么快”的状态——面试被问BTree就背结论实际调优就看网上的口诀。这篇文章想往前推一步索引到底改变了数据读取过程中的哪个环节为什么这种改变能带来如此大的数量级差距以及顺着这套原理我们在真实业务里应该怎么建索引、怎么排查“明明建了索引却不走”的问题。主要适合三类读者被面试官问过索引底层原理却答不透的人自己维护的表越跑越慢、想从根上理解原因的人以及需要设计新表结构想避免以后翻车的人。我尽量用对话的方式讲不堆概念。1. 不加索引时数据库是怎么在磁盘上大海捞针的1.1 数据行并不是“一行一行整齐躺在磁盘上的”很多资料把全表扫描解释成“把整张表读一遍”这句话对但太笼统。如果不理解“读一遍”具体做了什么你就很难理解索引为什么会有用。以InnoDB为例表数据是按B树组织在数据页里的每个数据页默认16KB页内再按行存放记录页与页之间通过双向链表连成整体。这里有个容易被忽略的点数据库真正读盘的单位是页不是行。你想查一条记录底层引擎没有办法只加载那一条它至少要先把这一行所在的数据页从磁盘搬到内存里的Buffer Pool。如果Buffer Pool没命中就产生一次真实磁盘IO。再往前推一层一行数据在页里也不是永远待在同一位置的。插入、更新、页满分裂这些操作都可能让记录的地址发生变化。InnoDB用行主键和页内的槽位来管理记录的位置普通二级索引里存的也不是物理地址而是主键值。这些细节初看和“为什么快”没关系但后面讨论回表、讨论为什么很多索引失效时都会牵涉到。1.2 全表扫描慢在哪儿查询成本与数据量线性挂钩继续推演。假设一张表有N行数据按一个数据页能装M行粗略计算读完整张表至少要做 N/M 次数据页IO。假设每页装1000行一张1亿行的表至少要读10万个页。10万次磁盘IO是什么概念机械硬盘一次随机IO大概要10毫秒左右就算换成顺序读配合预读10万个页的数据量也是几百MB甚至上GB级别全读出来再过滤耗时至少是秒级。更麻烦的是OLTP场景下绝大多数查询是等值或小范围查询比如“查用户ID为10086的订单”“查状态为1且最近一周的订单”它们关心的其实只有几条记录可全表扫描却不得不把整张表所有页都过一遍。这就是不建索引时最核心的浪费读取的数据量不取决于你要查多少行只取决于表总共多大。为了在图书馆里找一本书你把整座图书馆的每一本书都翻了一遍。1.3 顺序读和随机读的差别才是关键认知这时候会有人提出疑问全表扫描是顺序读索引查找是随机读顺序读不是应该比随机读快很多吗那用索引岂不是更慢要分场景看。全表扫描确实是顺序读但顺序读的性能优势会被“巨大的总页数”抵消。索引查询是随机IO但这条路径上的随机IO次数被压缩到了个位数。SSD普及之后随机读和顺序读的差距进一步缩小这个对比就更加明显索引只在B树上做几次定位而全表扫描要读几千几万个页谁快谁慢一目了然。所以真正让索引快的原因不是它用了什么高深莫测的算法而是它把“必须读的页数”从“全表的总数据量”降到了“树的深度级别”。从千万行里找一个点原来要读几十万页现在只需要读三个页左右。这才是数量级差异的来源。2. 索引到底是怎么做到“少读那么多页”的BTree拆开看2.1 从二叉树说起为什么数据库偏偏选了B树要理解B树先看一眼二叉搜索树。二叉树的特点是每个节点最多两个孩子查询时每次比较能排除一半节点听起来很完美。但有个致命问题如果插入顺序恰好是有序的二叉树会退化成一条长链表深度从O(logN)变成O(N)那查询性能和全表扫描也就没什么区别了。所以数据库不会直接用二叉树做索引。B树允许一个节点同时存多个键、多个孩子指针B树在B树基础上进一步做了改造所有键都保存在叶子节点内部节点只存“路由用的键”叶子节点之间用双向链表连接天然支持范围查询和排序每个节点对应一个数据页节点大小和页大小保持一致。这个设计带来的收益有两个单节点能装更多键树更矮叶子节点连续范围扫描友好。对磁盘来说树有多高一次点查大概就要做多少次真实IO。三层B树在InnoDB里就能支撑千万量级的表这正好覆盖了绝大多数业务表。我还经常被问到为什么不用哈希索引。哈希索引确实能在等值查询上做到O(1)但一旦要求范围查询、排序、前缀匹配它就完全派不上用场。B树是等值、范围、排序、插入删除几种操作的一个平衡解也是绝大多数关系型数据库的默认选择。2.2 16KB数据页能装多少键真实算一笔账我们来手算一下为什么三层树就够用。假设主键是bigint占8字节加上一个指向子节点的指针约6字节内部节点每条约14字节。一个16KB的页大概能放 16 * 1024 / 14 ≈ 1170 个键。再假设叶子节点里每行数据平均占1KB很多业务行其实不到一个叶子页能装约16行。那么三层B树的总容量就是第一层是根节点1个页能指向约1170个第二层节点第二层每个节点又能指向约1170个叶子页每个叶子页装约16行数据。总行数约 1170 × 1170 × 16 ≈ 2200万行。也就是说一张两千多万行的表从根节点走到叶子节点一般只需要三次磁盘IO。如果这棵树的上层节点已经在Buffer Pool里命中了实际产生的磁盘IO次数还能更少。这比一次扫描几万个数据页差别不是一点半点。2.3 插入、删除、页分裂为什么索引不是白拿的建索引不是没有代价这个代价主要落在写操作上。当你插入一个新行除了写数据页还要往二级索引对应的B树里插入一条索引记录。如果某个叶子页满了就可能发生页分裂过程中会伴随写放大和更深层的锁竞争。删除记录时虽然通常只是打标记但索引页的占用、整理同样会带来额外开销。所以一个写入非常频繁的表绝不能脑热把所有列都建上索引。我的习惯是优先满足核心查询路径再评估写入模型的容忍度能用一个联合索引覆盖多个高频查询就尽量避免建一堆单列索引。索引多到一定程度读性能的提升会被写性能的下降慢慢稀释这是很多高并发写入场景里容易踩的坑。3. where a and b 到底应该怎么建索引联合索引与最左前缀的实战逻辑3.1 联合索引的存储结构连续前缀排序搜索记录里经常能看到“mysql where条件a and b应该怎么建索引”这类问题。很多人的第一反应是在a上建一个索引在b上再建一个索引。但真实执行时优化器一般只会选择其中一个索引做完过滤另一个索引往往被闲置这等于多花了存储和写入代价却只得到了一个索引的收益。正确的做法通常是把两个条件合并成一个联合索引比如idx_a_b(a, b)。联合索引的存储结构可以理解成先按第一个字段a排序a相同的情况下再按第二个字段b排序。这样在B树上它既能高效过滤a又能在a确定后利用b的有序性做范围或精确过滤。我们常说“多个单列索引不如一个联合索引覆盖更多场景”根源就在这里。3.2 最左前缀到底怎么理解最左前缀不是MySQL随便定的规则而是联合索引的排序方式决定的。因为叶子节点先按最左字段排序要利用索引的有序性查询条件就必须从最左边的字段开始连续匹配。拿(a, b, c)联合索引举例WHERE a 1能走索引WHERE a 1 AND b 2能走索引WHERE a 1 AND b 2 AND c 3能走索引WHERE a 1 AND c 3能走索引但c只在回表后过滤相当于b被跳过WHERE b 2无法利用这个索引的最左前缀只能另想别的办法WHERE c 3同理完全用不上。我见过很多同事在看完执行计划后很疑惑“我明明在b上建了索引为什么查询没走”一看才发现表上是(a, b)联合索引单独拿b做等值条件时最左前缀用不上索引自然就废了。MySQL 8.0引入了一个叫索引跳跃扫描的机制可以在一定程度上处理“跳过最左列”的场景但它本质上靠遍历前缀字段的枚举值来近似数据量大时执行成本并不低不能把它当主力方案依赖。3.3 一个联合索引如何同时覆盖多个查询实际业务里同一张表通常会被不同页面用不同的条件查询。拿订单表举例用户端可能按 user_id 查自己的订单运营端可能按 status 和 created_at 拉取某段时间的待处理订单财务可能按 channel 查某个渠道的支付流水。这时候建索引就不能只看单个SQL而要看一组高频SQL。我通常先拉慢查询日志统计条件字段的出现频率再决定哪个字段放最左边。比较常见的一个设计是ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);这条索引对WHERE status 1 AND created_at 2024-01-01特别友好因为status做等值过滤后created_at还能利用索引自身的排序直接范围扫描不需要额外排序文件。反过来如果查询模式是“按时间段拉全量订单再按状态筛选”那就更适合把时间放最左边建idx_created_status(created_at, status)。这种选择不需要死记口诀只要在纸上把SQL条件和联合索引的排序规则对照着写一遍就能推出最合理的前缀顺序。4. 索引不是万能药我见过的高频失效场景与排查工具4.1 函数包裹、隐式转换、左模糊为什么优化器无力回天讨论“为什么索引会让查询变快”的时候很多人忽略另一半问题什么情况下索引不会变快甚至会被数据库弃用。我真实排查过的失效场景里出现频率最高的三个如下。第一个是对索引列做了函数运算比如SELECT * FROM orders WHERE DATE(created_at) 2024-01-01;B树里存的键是原始created_at的有序排列可DATE()这个操作会打乱原本的顺序关系。优化器没法在树上直接二分定位到目标值只能全表扫描后逐行计算索引自然失效。第二个是隐式类型转换。字符串列和数字列比较时MySQL会把字符串转成数字再比一旦列本身被函数包裹索引顺序也保不住。这类问题特别隐蔽因为SQL看着很正常EXPLAIN之前很难发现。第三个是左侧模糊查询SELECT * FROM audit_log WHERE content LIKE %error%;B树定位需要一个确定的起点而左模糊不知道目标值的前缀从根节点就没有办法二分只能放弃索引。这里补充一个细节函数不一定会让索引失效。MySQL 8.0支持函数索引可以把DATE(created_at)本身作为索引键建进去但这是新增的优化手段不是在旧版本上随便写SQL就能自动生效的。生产环境升级前还是先把索引设计改对更重要。4.2 优化器自己决定不走了rows估算和回表成本还有一种更让人头大的失效SQL没写错字段也没被函数包裹但优化器就是不选索引。这不是它抽风而是它算了一笔账觉得走索引不划算。比如查询条件过滤出来的行数占全表比例很高超过20%~30%时走二级索引的流程是先到索引树定位主键再回聚簇索引取整行每条数据都可能伴随一次随机IO。如果结果集有几百万行回表次数就非常可怕不如直接全表顺序扫描划算。怎么判断看EXPLAIN输出。重点看三样东西type是否达到ref或range代表有没有真正用了索引定位key实际命中的索引名rows优化器估算的需要扫描行数。typeALL并不代表索引一定建错了还要看key是不是空的。如果key为空而rows接近全表行数说明优化器主动放弃了索引。这时候与其怀疑索引失效不如想想如何缩短查询路径比如改成覆盖索引或者调整SQL结构让结果集变小。4.3 慢查询日志与EXPLAIN的配合使用接手一套别人维护过很久的数据库我不会一开始就去翻建表语句而是先打开慢查询日志把执行时间超过阈值的SQL收集出来逐条EXPLAIN。这个过程不需要花哨的监控平台纯SQL就能完成SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;日志文件里会记录所有执行超过1秒的语句拿到SQL后进MySQL跑一遍EXPLAIN重点看有没有出现rows飙升、Using filesort、Using temporary等信号。我习惯在调完索引后再跑一次同样的SQL对比rows和真实耗时变化。很多同学只关心key有没有命中忽略了rows的变化其实rows才直接反映了扫描范围是否真的变小了。索引优化做得好不好最终看的不是索引数量而是每条高频SQL的扫描行数差了多少。5. 主键索引、唯一索引、普通索引之间到底差在哪5.1 聚簇索引与二级索引一个表真正的主心骨主键在InnoDB里的地位被很多人低估了。主键对应的就是聚簇索引叶子节点直接保存整行数据。表数据本身在物理上就是按主键顺序组织的所以一个表只能有一个聚簇索引其他索引统称二级索引。二级索引普通索引、唯一索引都是的叶子节点不存整行数据只存主键值。查询走二级索引时会先通过索引树找到主键再到聚簇索引里取整行记录这个过程叫回表。正因为主键决定了整张表数据的物理组织方式主键设计对写入性能影响极大。自增主键的新记录会追加到已有数据的后面大多数情况下不会打乱既有页结构用UUID这类随机主键时新记录可能插到任意位置页分裂频繁发生写入性能和磁盘碎片都会变差。所以我不太建议在InnoDB表里搞“业务主键就是UUID”的浪漫设计除非你有外部分布式生成ID的成熟方案作为兜底。5.2 唯一索引和普通索引查询差异比想象中小写入差异被很多人忽略从查询角度看唯一索引和普通索引的差别没有想象中大。一个唯一索引在找到一条匹配记录后理论上可以“提前结束”而普通索引还要继续找下一条确认没有重复但因为二级索引记录里的“索引键主键”组合天然唯一普通索引额外扫描的那一点点代价基本可以忽略。真正的差别在写入侧唯一索引插入时要先做唯一性检查这意味着要多读一次索引页确认冲突是否存在在可重复读隔离级别下唯一索引更容易产生间隙锁事务并发稍高时热点唯一字段上可能频繁出现锁等待删除和更新涉及唯一校验时也存在类似开销。所以“唯一”应该由业务约束决定而不是顺手加上。用户名、订单号、身份证号这类必须唯一的字段建唯一索引是合理的因为它能同时承担约束和查询两个职责。但状态、渠道、类型这种枚举型字段业务上本来就能重复硬加唯一索引只会拖慢写入毫无收益。5.3 一个实际设计案例的取舍我之前优化过一个订单查询接口当时的表结构问题很典型每个查询条件都建了单列索引但每次查询都只能用一个索引大量索引在存储和写入上纯属浪费。后来我按下面思路做了调整主键保留自增id聚簇索引负责整行数据存储用户查询列表走idx_user_id(user_id)因为用户查询最频繁运营后台按状态和时间段筛选走idx_status_created(status, created_at)把原来一堆用不上的单列索引全部drop掉。调整之后写入性能反而比原来好了因为每次插入需要维护的索引树从五个降到了三个查询高频SQL也在EXPLAIN里稳定命中索引。这就是“少即是多”的典型例子。还有报表类查询如果只统计某几个固定列我不会让SQL回表而是用覆盖索引把查询列直接塞进索引树里让扫描阶段就得到结果。覆盖索引是比普通索引更“狠”的一种优化适合那种读多写少、读取列相对固定、结果集比较大的统计场景。主键索引、唯一索引和普通索引的取舍其实就像你选房子的承重结构。改变承重墙性质代价极高往房间里加个轻隔断却很容易。索引设计的核心原则是在建表初期把主键定对、把高频查询的联合索引想清楚而不是等到线上慢了再疯狂补索引。真到了ID爆炸、数据量暴涨的后期再想改主键设计就非常痛苦了。说实话这些索引相关的原理我一开始也是背结论背下来的。真正把存储引擎、B树、回表这些概念串起来自己去算了一次三层树能装多少行再用慢查询日志和EXPLAIN去验证了几回之后才觉得建索引这件事从“玄学”变成了“工程判断”。建议你也找一张千万行的表开一下慢查询日志对比一条SQL在建索引前后的rows和耗时中间数据的数量级差会让你印象很深刻。