做后端这几年我见过太多次慢SQL排查的场景业务方扔过来一条查询说数据才几百万行就卡到几秒开发同学现场加了一个索引结果下次换个查询条件又慢回原形。很多人一听到“MySQL索引原理和B树应用”就觉得这是背诵八股实际上不搞懂B树为什么被选中你根本没法判断一条SQL到底该加什么索引也没法解释为什么明明是同一张表、换个where条件索引就失效。这篇文章我会把从磁盘IO、数据页到联合索引、索引失效的整个链路讲清楚所有结论都能直接用EXPLAIN验证适合正在啃索引原理的开发者也适合天天和慢查询打交道的后端同学。写这篇文章之前我重新翻了InnoDB的存储实现因为很多结论不能只看文档得回到“页”这个最小单位去想。索引不是银弹它是在空间和时间之间做交换如果你不理解交换的底层逻辑很容易加错索引甚至把原本不慢的写入拖垮。先不急着写SQL我们从最底层的问题开始一个查找操作在MySQL里到底要花什么成本1. 从IO成本看索引本质MySQL为什么非要用B树1.1 磁盘IO才是真正的瓶颈很多人对“索引查得快”的理解停留在时间复杂度上红黑树是O(log n)B树也是O(log n)好像差不多。但MySQL是跑在磁盘上的关系型数据库存储引擎最关心的不是CPU计算多少步而是到底要发生多少次磁盘IO。一次随机磁盘IO普遍要5到10毫秒而一次内存访问是几十到几百纳秒两者相差好几个数量级。哪怕内存里算得再快只要多读一个磁盘页整个查询的耗时都会显著上升。索引的真正职责是尽量减少查询过程中需要扫描的磁盘页块数而不是单纯降低算法复杂度。这个观点一旦立住后面很多选择就都说得通了。InnoDB读写数据的最小单位是16KB的数据页默认情况下innodb_page_size16384。B树的一个节点就是一个页。你要查一行数据得从根节点出发一层一层往下走每经过一层都可能发生一次磁盘读取。所以在评估索引结构时关键指标不是“树有多平衡”而是“树有多矮”。1.2 为什么不选哈希、红黑树或B树哈希表在等值查询上确实强一次计算就能定位到桶但它的致命问题是不支持范围查询也不支持排序。你写where user_id 100或者order by create_time哈希索引就完全派不上用场。而且InnoDB中的自适应哈希索引是建立在B树之上的二次加速结构不能替代B树本身。红黑树和AVL树是内存中优秀的数据结构但它们每个节点只保存一个键和两个子节点指针。一千万行数据的AVL树高度大约是24层也就是一次等值查询最坏要读24个页。虽然几十次内存态访问还能接受但放在磁盘上就是灾难。你可以把每个节点想象成图书馆目录柜里的一个卡片传统二叉树只用一张卡片上的字母分叉想看下一个分叉还得再抽一张卡片查一次书要来回翻几十次柜子。B树解决了“一个节点存多个键”的问题但它把索引键和数据都塞在同一个节点里。非叶子节点存入大量行数据后保存的键数量急剧减少同样一个16KB页能延展的分支数变少树还是压不下来。更麻烦的是B树每个节点的子节点之间没有串联做范围查询时中序遍历需要反复从父节点回溯到兄弟节点一次大范围扫描会带来大量随机IO。1.3 B树胜出的三个核心细节B树和B树最明显的区别是非叶子节点只保存索引键和指向子节点的指针不保存行数据。所有数据都放在最底层的叶子节点中叶子节点再通过链表按顺序串起来。这个设计带来三个实打实的好处。第一树更矮。以一个16KB的页为例索引键用BIGINT占8字节子节点指针占6字节一条索引项约14字节一个页大概能放1170条索引项。第二层有1170个页也就是约1170乘1170约137万个叶子节点。假设每个叶子页平均放10行完整数据三层B树就能支撑上千万行。行数据越大支持的行数会少一些但百万级到千万级仍然很稳。对绝大多数业务表来说三层足够。第二范围查询高效。因为叶子节点已经按索引列排好序并且通过链表连接只要定位到范围起点就能沿着链表顺序往后读。这种顺序读非常契合磁盘预读机制扫描大量连续数据时成本远低于B树。第三磁盘IO次数稳定可控。等值查询从根到叶子固定经过三层也就是固定读取3页左右。页是预读的相邻页之间也大概率顺序读。这个稳定的成本模型让优化器能够可靠地估算查询代价也让DBA做容量规划时更有底。B树当然也有代价数据插入位置不连续时会造成页分裂页空间浪费到一定程度还会触发合并。但相对于它带来的查询收益这些写放大是可以接受的而且可以通过正确的主键设计来控制。2. InnoDB里的两棵B树聚簇索引和二级索引怎么协同工作2.1 建一张表看看索引背后长什么样只看理论容易飘我们直接建一张订单表来验证。这张表很典型包含订单号、用户ID、状态、创建时间后面讲联合索引和排查都用它。CREATE TABLE t_order ( id bigint unsigned NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint unsigned NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime NOT NULL, PRIMARY KEY (id), KEY idx_user_create (user_id, create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;InnoDB里最核心的一个概念是表数据本身就是一棵B树。主键构成聚簇索引聚簇索引的叶子节点存储整行数据。也就是说你执行select * from t_order where id 100时走的是主键这棵B树一路定位到叶子页直接拿回完整行。同时InnoDB还会维护第二棵B树也就是二级索引。这里我建了idx_user_create这棵索引树的叶子节点不存整行只存储user_id、create_time以及主键id三个字段。为什么二级索引不直接存行地址因为行数据在聚簇索引里并不是固定的插入、更新、页分裂都可能让行移动。如果二级索引保存行地址每次移动都要回改所有二级索引。存主键值就稳定得多代价只是每次通过二级索引定位后可能还要回聚簇索引再查一次。2.2 数据页内部不是简单链表既然一个叶子节点是一个16KB的数据页那页里面是啥样的InnoDB数据页内部并不是把记录一条条零散放着而是维护了一个“页目录”。你可以把页想象成一栋楼目录是楼层索引牌记录按照键值顺序排好页目录里放槽每个槽指向一组记录的起始位置。当你要在某个叶子页内查找具体记录时InnoDB会先在页目录里做二分查找锁定可能存在的槽再在组内顺序扫描。也就是说即使定位到叶子节点这一层也不是从页头一路扫到页尾。很多人在一个页里数据不多时感觉不到差别但当一个页填了上百条记录后页目录的二分查找能明显减少CPU消耗。每次通过二级索引拿到主键值再去聚簇索引里回查一次就叫回表。一次回表只是多一次索引树的路径查找成本不算高。真正的问题是在一个查询需要回表成千上万次时原本一次范围扫描变成了大量随机IO慢SQL就是从这里来的。后面会说覆盖索引就是用来对付这个问题的。2.3 主键怎么选会直接影响B树的写放大理解了聚簇索引叶子节点存整行就能理解为什么主键设计对性能影响那么大。如果主键是自增整数新插入的行主键值单调递增数据会一直追加到当前索引树最右侧的叶子页页满了就去开新页很少触发其他页的数据搬移。如果主键是UUID或者随机字符串插入位置随机落在B树中间就可能造成已经写满的中间页被分裂数据被搬过来搬过去产生大量磁盘碎片和日志写入。同样一张表自增主键和UUID主键的写入性能差距可以有几倍。而且主键列会出现在每个二级索引的叶子节点里主键越长所有二级索引占用空间就越大缓存命中率也会受影响。所以在建表时我通常遵循三条简单原则能用整数就不用字符串能用自增主键就不要手工生成主键如果没有自然主键老老实实加一个AUTO_INCREMENT的单列主键。树越稳定你的插入TPS越有保障。3. 联合索引的排序逻辑与EXPLAIN实战3.1 最左前缀联合索引的第一个字段是命门很多同学知道联合索引有“最左前缀”原则但不知道为什么。回到B树的排序规则就很好理解了。idx_user_create这棵B树先按user_id排user_id相同的行再按create_time排最后可能按主键id排。也就是说整棵索引树对“创建时间”是有序的但只在user_id确定的前提下才成立。如果查询条件是where create_time 2024-01-01而没有user_id索引第一层的排序对create_time完全没意义优化器没法沿着索引做范围定位。所以并不是where里条件写的顺序决定是否走索引而是查询条件里是否包含了联合索引最左侧的列。如果where user_id 1 and create_time 2024-01-01user_id等值定位后create_time就是局部有序的可以继续在索引上做范围查询。如果交换写where create_time ... and user_id 1优化器通常也能调整顺序关键考察的仍然是列本身而不是书写顺序。这里还要多说一个容易忽略的点联合索引对查询有帮助的条件有两种。等值条件会锁定树上的多个连续区间范围条件则会在某一段有序链路上进行扫描。如果跳过最左列直接查右边列就没法利用索引的有序性常规优化器只好放弃这棵索引。3.2 索引下推和覆盖索引两个低成本高收益的优化MySQL 5.6之后引入了索引下推这是一个经常被忽视的特性。还是看这条查询SELECT * FROM t_order WHERE user_id 123 AND create_time 2024-01-01;走idx_user_create时二级索引里已经包含user_id和create_time。没有ICP时存储引擎需要把所有满足user_id123的索引记录都取出来一条条回表再由Server层过滤create_time。有了ICP存储引擎在读取二级索引时就能直接用create_time条件过滤掉大量不合格记录只有真正符合条件的主键才回表。这样一来回表次数明显减少EXPLAIN里会看到Using index condition。覆盖索引是另一把利器。如果查询只需要用到索引里已经包含的列就不要回聚簇索引拿完整行了。比如这条SQLSELECT id, user_id, create_time FROM t_order WHERE user_id 123 AND create_time 2024-01-01;idx_user_create里已经包含id、user_id、create_time三个字段查询结果全部可以从二级索引的叶子节点拿到EXPLAIN的Extra列会显示Using index这是非常理想的状态。相反如果select所有字段就必须回表同样的条件会出现Using index condition而不是Using index。select *本身没有错但在高频查询、核心链路这种场景下只取需要的字段同时让索引覆盖查询收益会非常可观。这不是为了少传两个字段省带宽而是为了减少随机IO次数。3.3 用EXPLAIN验证索引到底有没有用理论说再多不如看一条真实EXPLAIN。我把上面那条覆盖查询放进去EXPLAIN SELECT id, user_id, create_time FROM t_order WHERE user_id 123 AND create_time 2024-01-01 ORDER BY create_time;执行计划里大概率会出现这些关键信息key是idx_user_create说明优化器真的用了这棵索引。type是range说明在索引上做了范围扫描比全表扫ALL好很多。Extra里有Using index说明没有回表。没有Using filesort说明ORDER BY create_time也能利用索引完成排序。EXPLAIN输出里的type项是判断扫描方式的最好入口。按性能从好到差大致是system、const、eq_ref、ref、range、index、ALL。看到ALL基本就是全表扫描看到index虽然扫了索引但也不一定高效只有ref以上和range才是符合预期的。rows是优化器估算要扫描的行数它不一定精确但如果和实际预期差太多就要看看统计信息是否存在偏差。如果查询里加了ORDER BY create_time且没有先限定user_id那么联合索引就无法保证全局按create_time有序执行计划里大概率出现Using filesort也就是MySQL要把数据取出来另做排序。遇到这种场景要么调整查询让它命中索引前缀列要么单独考虑排序字段的索引。4. 我从慢查询里挖出来的索引失效坑与排查流程4.1 六个典型失效现场索引不是加了就一定生效日常工作里最头疼的是那些“看起来该走索引却没走”的SQL。下面是我实际排查时反复踩过的六类坑。场景常见写法失效原因可行改写索引列套函数WHERE DATE(create_time) 2024-01-01函数破坏了索引列的有序性create_time 2024-01-01 AND create_time 2024-01-02隐式类型转换WHERE phone 13800138000varchar列被转成数值WHERE phone 13800138000左模糊查询WHERE name LIKE %abc%叶子节点有序无法从中间定位尽量避免或改前缀模糊跳过联合索引左列WHERE create_time 2024-01-01全局顺序不按第二列再加单列索引或调整查询OR包含非索引列WHERE user_id1 OR status1优化器难以合并访问路径拆成两条SQL再用UNION ALL范围查询后的列WHERE user_id1 AND create_time2024-01-01 AND status1范围后列的定位失效把等值列放前范围放后第一类最典型。对索引列做函数运算时MySQL无法直接利用B树的有序排列因为它要先把每一行的create_time都算出函数值才能比较。正确的做法是把函数运算从列上挪到常数上写成范围条件。这样不仅能用索引语义也更清晰。隐式转换这个坑也很隐蔽。phone列是varchar但查询传了数字MySQL会把存储的字符串转成数字再比较相当于对索引列做了转换索引自然就废了。反过来如果索引列是int条件传字符串MySQL会尝试把字符串转成int因为转换发生在常数一侧对索引列没有影响索引通常还能用。判断规则很简单看转换发生在哪一侧发生在索引列上就危险。OR条件需要单独说。如果OR两边都是索引列MySQL在某些版本下会尝试Index Merge但成本并不总是比全表扫低。如果OR里有一个列完全没有索引那基本就没有走索引的希望了。业务上能拆就拆成两条查询再UNION ALL也可以用IN重写部分场景效果往往更稳。4.2 索引不是越多越好维护代价与冗余检查B树是读优化的结构但写入时每一棵索引都要跟着更新。插入一行数据如果有5个二级索引就要同时往5棵B树里插入对应记录。页满了还会触发页分裂产生额外磁盘IO和日志。很多高频写入表加了五六个索引后插入吞吐明显下降这种代价在产品初期数据量小的时候根本看不出来一到业务峰值就暴露。所以每次新增索引前我会先问三个问题这条查询是不是真的高频查询里所有条件能不能复用现有索引这个索引会不会和已有索引重复比如已经有idx_user_create(user_id, create_time)再单独建idx_user_id(user_id)就属于冗余因为联合索引的最左列已经能覆盖单列user_id的查询需求。排查冗余索引可以查系统表SELECT * FROM sys.schema_unused_indexes;这个视图能列出启动以来未使用过的索引看到长期没有人命中的索引可以考虑删除。删除索引比新增索引对业务的影响小一些但仍然要选择低峰期操作并且先确认没有别的查询依赖它。4.3 一套可复用的慢查询排查流程光知道理论还不行真遇到线上慢查询要有自己的排查顺序。我一般按下面五步走先开启慢查询日志把超过阈值的SQL抓出来SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SHOW VARIABLES LIKE slow_query_log%;线上环境建议直接在配置文件中固化否则数据库重启后就失效了。对慢SQL执行EXPLAIN先看type和key。如果typeALL且没有可用索引检查是不是缺索引或者索引失效。如果key显示用上了索引但还是很慢重点看rows和Extra。Using filesort说明排序是额外做的Using temporary说明可能产生了临时表这些都要单独优化。想弄明白优化器为什么没选某个索引可以临时打开优化器追踪SET optimizer_trace enabledon; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE; SET optimizer_trace enabledoff;这个操作有额外开销生产环境一定要用完马上关掉。根据结论修改SQL或者索引再重复执行EXPLAIN对比。只改索引不改写法或者反过来很多问题都解决不干净。这套流程最大的价值是让排查有顺序不会一上来就翻代码或者逐条试索引。多数情况下一条慢SQL从问题出现到定位原因五分钟内就能完成。4.4 大表加索引的实操提醒如果你要在一个千万行的大表上新增索引直接执行ALTER TABLE ADD INDEX虽然会被Online DDL尽量优化但大表操作仍然可能造成主从延迟、磁盘IO飙升甚至阻塞写入。更稳妥的方式是使用在线改表工具在低峰期通过后台任务同步数据最终短暂切换完成。这里要注意工具会额外占用磁盘空间操作前先评估磁盘余量并且准备好回滚方案。还有一个小细节新增索引前先看选择性。如果一个列的取值只有两种状态比如status只有0和1那单列索引的选择性太差优化器很可能选择全表扫也不走索引。更好的做法是把低选择性列放在联合索引后面配合高选择性列一起使用。这也是为什么我不建议一看到where status1就立刻加索引的原因。5. 一点个人门道遇到慢SQL我习惯先画B树说了这么多最后还是分享一个我自己养成的小习惯。拿到一条慢SQL我不会急着加索引而是在草稿纸上把涉及的where条件拆成三组等值条件、范围条件、排序字段然后问自己一个问题如果我要为这些条件设计一棵B树索引列应该怎么排列才合理这个习惯是从一次线上事故里逼出来的。当时某张表已经有好几个索引但查询还是慢后来发现是因为一个范围查询夹在联合索引中间导致后面的列全部失去有序性。如果当时先从B树的结构倒推一眼就能看出问题。现在我的经验是把等值条件放在索引最前面范围条件放中间需要排序的字段尽量排在范围条件之前这样才能最大化利用B树的排序能力。B树不是什么高深莫测的概念它就是一棵矮胖树叶子串成链表非叶子只负责路由。搞清楚这一点索引失效的原因、联合索引的设计、回表和覆盖索引的取舍全都能串起来。遇到问题先画树再写SQL最后用EXPLAIN验证这是我目前对抗慢查询最顺手的方法。