索引是 MySQL 查询性能的第一杠杆：它用磁盘空间和写入维护成本，换取查询时按图索骥的能力。读懂 B+ 树、回表、最左前缀这三件事，索引设计就从背口诀变成了推导题。

它解决什么问题

没有索引时，一条 WHERE status = 1 只能全表扫描：把数据页一页页读进内存逐行比对，百万行就要扫百万行，耗时随数据量线性膨胀。索引的本质是一份额外维护的、按 key 排好序的数据结构，让定位从”翻完整本书”变成”先查目录再翻页”。真正值得想通的问题不是语法，而是：可选的查找结构那么多，InnoDB 为什么偏偏押注 B+ 树？

核心原理：B+ 树为什么适合磁盘

答案藏在磁盘的脾气里。内存随机读以纳秒计，磁盘一次 IO 要贵出几个数量级，而且磁盘按页读写——InnoDB 一页 16KB，读一个字节和读一整页的成本几乎一样。所以磁盘索引的核心目标是：用尽可能少的 IO 次数定位数据，而 IO 次数约等于树的高度。

  • 哈希表：等值查询 O(1)，但哈希值之间没有顺序。WHERE id < 500、排序、LIKE 'abc%' 全部抓瞎，总不能把 1 到 499 逐个哈希一遍。因此 InnoDB 只允许内部的自适应哈希索引（AHI）加速缓冲池里的热点页，用户建不了哈希索引。
  • 跳表：Redis 的有序集合用它，多层链表在内存里实现简单、并发友好。但每个节点只挂一个元素，指针跳转频繁，落到磁盘上一页装不下几个节点，层数和页利用率都不达标。它是内存结构的好答案，不是磁盘结构的好答案。
  • B 树：多路平衡，单节点存多个 key，树高已经压得很低。问题在于内节点同时存 key 和整行数据，一页能塞的 key 变少，树就更高；范围查询还要在树里中序遍历，跨节点来回跳。
  • B+ 树：内节点只存 key 和子树指针，一页 16KB 能容纳上千个路由项，千万级数据树高通常只有 3 层，一次定位约 3 次 IO；所有记录按序躺在叶子层，叶子之间用双向链表串起，范围查询找到起点后顺链扫到底，天然契合磁盘的顺序读偏好。

一句话总结：B+ 树用”矮胖”的树形换最少的 IO，用叶子链表换最顺的范围扫描，这两刀恰好砍在磁盘最贵的两个地方。

深入机制

聚簇索引、二级索引与回表

InnoDB 的表数据本身就是一棵按主键组织的 B+ 树：叶子页存整行记录，这就是聚簇索引。没显式声明主键时，InnoDB 会先找非空唯一索引顶替，再没有就生成隐藏的 6 字节 row_id。

其他列上建的索引叫二级索引，叶子页存的是”索引列值 + 对应主键值”而非整行。经二级索引拿到主键后，还得回聚簇索引再查一次才取得完整行，这一步叫回表。回表是随机 IO，命中几百行就要回表几百次，代价可能反超全表扫描——这也是优化器有时”放弃”索引的原因。

覆盖索引与最左前缀

如果 SELECT 的列全部落在索引里（含叶子自带的主键），就无需回表，EXPLAIN 的 Extra 会出现 Using index：

CREATE INDEX idx_user_status ON orders(user_id, status, created_at);

-- id、user_id、status、created_at 都在索引内，覆盖索引生效
SELECT id, user_id, status, created_at
FROM orders
WHERE user_id = 1001 AND status = 1;

联合索引 (a, b, c) 在磁盘上先按 a、再按 b、再按 c 排序，因此天然支持 a、a+b、a+b+c 三种前缀查询，这就是最左前缀原则。匹配从等值列一路向右推进，遇到第一个范围条件（>、<）后，后续列在索引内不再整体有序，只能用于过滤、不能继续定位；而 >=、<=、BETWEEN、LIKE 'abc%' 不会中断匹配。WHERE 子句的书写顺序无关紧要，优化器会重排等值条件，所以 b=1 AND a=1 AND c=1 照样吃满三段索引。

索引下推（ICP）

MySQL 5.6 引入的优化。没有 ICP 时，存储引擎按索引定位出若干主键就逐条回表，把整行交给 Server 层过滤；有了 ICP，凡能用索引中已有列判断的条件会被下推到引擎层，先过滤再回表。例如 WHERE user_id = 1001 AND status = 1 命中上面的联合索引时，status 的比较直接在引擎层完成，回表次数和引擎层到 Server 层的传输量同时下降，EXPLAIN 的 Extra 显示 Using index condition。

联合索引的列顺序设计

按优先级记住四条：

  1. 等值列在前、范围列殿后，因为范围会截断后续列的定位能力；
  2. 同为等值列时，区分度高的靠前，更早收窄扫描区间；
  3. 兼顾排序：高频 ORDER BY status, created_at 就把它们按相同方向排进索引，消掉 filesort；
  4. 已有 (a, b) 就不必再建单列 (a)，前者完全覆盖后者，避免冗余索引吃空间和拖慢写入。

工程实践：索引失效清单

  • 对索引列套函数或做运算：WHERE DATE(create_time) = '2024-01-01'、WHERE id + 1 = 10；
  • 隐式类型转换：varchar 列拿数字比（phone = 13800001111），相当于整列套了转换函数；
  • 前导通配符：LIKE '%abc' 让起始位置不确定，无法定位区间；
  • 跳过联合索引首列（8.0 的索引跳跃扫描只在特定场景兜底，别当常规手段依赖）；
  • OR 连接的某一边没有索引，整体退化为全表扫；
  • 结果集占比过大（经验阈值两三成）时优化器主动放弃索引，这属于成本决策而非失效；
  • IS NULL、!= 并非必然失效，但配合低区分度列常被放弃。

一切判断以 EXPLAIN 为准：重点看 type（至少到 range/ref）、key（实际选中的索引）、rows 与 Extra。

对比与权衡

结构 等值查询 范围查询 磁盘适配 典型应用
B+ 树 O(logN)，约 3 次 IO 叶子链表顺序扫，最优 单页上千 key，树矮 InnoDB 索引
B 树 O(logN)，IO 略多 树内中序遍历 内节点存数据，树偏高 部分文档库与文件系统
哈希表 O(1) 不支持 无序，页利用率差 Memory 引擎、InnoDB AHI
跳表 O(logN) 支持但跳转多 节点离散，难聚合成页 Redis ZSET、内存 KV

面试视角

Q1：为什么索引用 B+ 树而不用 B 树？ 两个层面：B+ 树内节点不存数据，单页能容纳更多 key，树更矮、IO 更少；叶子层双向链表让范围查询退化为顺序扫描，B 树做范围查询要在树里中序遍历、频繁跨节点。

Q2：什么是回表，如何避免？ 二级索引叶子只存主键，取整行要再查一次聚簇索引，这次是随机 IO。让查询列被索引完全覆盖即可避免，也就是覆盖索引。

Q3：联合索引 (a,b,c)，b=1 AND c=1 走索引吗？a=1 AND c=1 呢？ 前者不走，最左前缀被破坏；后者走 a 段定位，c 配合索引下推在引擎层过滤。书写顺序不影响，优化器会重排等值条件。

Q4：主键为什么建议自增且短？ 自增让插入永远落在叶子链表末尾，避免页中间插入引发的页分裂与碎片；主键短是因为每个二级索引的叶子都要冗余存一份主键值，主键越胖全部索引越胖。

Q5：索引下推到底省了什么？ 把能用索引列判断的 WHERE 条件下推到引擎层、在回表之前过滤，省的是回表次数和引擎层与 Server 层之间的行数据传输。

小结

索引的所有设计都围绕磁盘 IO 展开：B+ 树以高扇出压低树高，以叶子链表顺应范围扫；聚簇索引存整行，二级索引存主键、查全行要回表；覆盖索引、最左前缀、ICP 是日常优化的三把抓手。建联合索引时默念”等值在前、范围殿后、高区分度优先”，剩下的交给 EXPLAIN 验证，而不是交给运气。