为什么 MySQL 选择 B 树聚簇索引和二级索引到底有什么区别回表、覆盖索引、索引下推又是怎么回事本文将从底层数据结构出发彻底讲透 MySQL 索引的每一个关键概念。一、前言数据库查询是后端开发中最频繁的操作之一。当表中只有几十条数据时全表扫描毫无压力但当数据量达到千万级一条没走索引的 SQL 就可能导致整个系统瘫痪。索引的本质就是排好序的快速查找数据结构。它的核心目标是——减少磁盘 I/O避免全表扫描。MySQL 的 InnoDB 引擎使用B 树作为索引的底层数据结构。理解索引结构是理解一切查询优化、慢查询排查、索引设计的基础。二、从二叉树到 B 树一场问题驱动的进化要理解 MySQL 为什么选择 B 树需要先看其他数据结构为什么不行。2.1 二叉搜索树BST最直观的索引结构左子树所有节点值小于父节点右子树所有节点值大于父节点。致命缺陷极端场景下退化为链表如果插入的数据是有序的如自增 ID树会退化成单链表查询时间从 O(log N) 变成 O(N)树高过高即使平衡状态下100 万条数据的树高约 20 层意味着每次查询需要 20 次磁盘 I/O2.2 平衡二叉树AVL/ 红黑树AVL 通过旋转保证左右子树高度差不超过 1红黑树通过弱平衡规则限制最长路径。它们解决了退化问题。核心缺陷树高依然过高100 万条数据树高接近 20每次查询约 20 次磁盘 I/O无法满足高并发场景每个节点只存一个键值完全浪费了磁盘预读特性每次 I/O 读取一个 16KB 的数据页2.3 B 树平衡多路查找树B 树让每个节点存储多个键值树高大幅降低。仍然存在的问题非叶子节点也存储数据导致每个节点能存储的索引键数量减少树高依然高于 B 树范围查询效率低需要多次中序遍历跨节点 I/O 次数多查询性能不稳定部分查询在非叶子节点就能返回部分需要到叶子节点优化器难以稳定预估成本2.4 B 树——数据库的终极选择B 树在 B 树基础上做了三个关键改进完美适配数据库的磁盘存储和查询场景改进点B 树B 树数据存储位置所有节点都存数据只有叶子节点存数据叶子节点连接无连接双向链表串联查询路径可能在非叶子节点命中所有查询都到叶子节点路径固定图1B 树索引结构示意图三、B 树的核心特性3.1 非叶子节点只存键不存数据所有完整数据都存储在叶子节点非叶子节点只存索引键和指针。这意味着同样大小的页16KB非叶子节点能存储更多的键树高极低。算一笔账假设主键是BIGINT8 字节指针占 6 字节一个页不存数据只存键和指针(16 × 1024) / (8 6) ≈ 1170 个键也就是说一个非叶子节点最多有1170 个子节点。假设叶子节点一行数据的大小是 1KB一个叶子节点可以存储16条数据那么层级节点数存储能力第 1 层根节点1 个页—第 2 层最多 1170 个页—第 3 层叶子节点最多 1170 × 1170 ≈ 137 万个页每页存约 16 行 →约 2000 万条数据结论一棵高度仅 3 层的 B 树就能支撑约 2000 万条数据。每次查询只需 3 次磁盘 I/O。3.2 叶子节点用双向链表串联所有叶子节点按索引键升序排列通过双向链表连接。这使得范围查询极其高效找到起始位置后沿链表顺序扫描即可排序操作不需要额外开销数据本身已经有序分页查询直接跳过沿链表移动即可3.3 所有查询都落到叶子节点无论查询的键值是在非叶子节点就能匹配还是必须到叶子节点最终都会走到叶子节点。这意味着查询性能稳定每次查询的 I/O 次数固定优化器可以精准预估成本利于生成稳定的执行计划四、InnoDB 的数据页结构B 树的每个节点在 InnoDB 中对应一个或多个数据页Page。4.1 页的基本概念属性值默认大小16 KB管理方式InnoDB 以页为最小单位读写磁盘预读机制每次读取一个页加载到 Buffer Pool 缓存中行存储方式行式存储一行数据在页内连续存放4.2 页内结构一个数据页的核心组成部分┌─────────────────────────────────────────┐ │ File Header16 字节 │ ← 页的元信息 ├─────────────────────────────────────────┤ │ Page Header56 字节 │ ← 页的状态信息 ├─────────────────────────────────────────┤ │ Infimum Record │ ← 虚拟最小记录 ├─────────────────────────────────────────┤ │ User Records行记录区 │ ← 实际数据行 │ ┌────────┐ ┌────────┐ ┌────────┐ │ │ │ Row 1 │ │ Row 2 │ │ Row 3 │ ... │ │ └────────┘ └────────┘ └────────┘ │ ├─────────────────────────────────────────┤ │ Supremum Record │ ← 虚拟最大记录 ├─────────────────────────────────────────┤ │ Free Space空闲空间 │ ← 新数据插入位置 ├─────────────────────────────────────────┤ │ Page Directory页目录 │ ← 槽Slot数组 │ [Slot1] [Slot2] [Slot3] ... │ 快速定位行记录 └─────────────────────────────────────────┘4.3 行记录格式InnoDB 的每一行数据除了用户定义的列之外还包含一些隐藏列隐藏列大小作用DB_TRX_ID6 字节最近一次插入或修改该行的事务 IDDB_ROLL_PTR7 字节回滚指针指向 undo log 中该行的旧版本DB_ROW_ID6 字节隐式自增行 ID无主键时自动生成五、聚簇索引Clustered Index5.1 什么是聚簇索引聚簇索引是 InnoDB 最核心的概念索引和数据存储在一起叶子节点直接包含完整的行数据。换句话说表数据的物理存储顺序就是聚簇索引的顺序。5.2 聚簇索引的选择规则InnoDB 按以下优先级确定聚簇索引如果表定义了PRIMARY KEY→ 主键就是聚簇索引如果没有主键 → 选择第一个UNIQUE NOT NULL的索引如果以上都没有 → InnoDB 自动生成一个6 字节的隐藏ROW_ID作为聚簇索引5.3 聚簇索引的 B 树结构[根节点] / | \ [非叶子] [非叶子] [非叶子] / | \ / | \ / | \ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ │L1 │→│L2 │→│L3 │→│L4 │→│L5 │ ← 叶子节点 │id1 │ │id5 │ │id9 │ │id13│ │id17│ 存完整行数据 │全行 │ │全行 │ │全行 │ │全行 │ │全行 │ └─────┘ └─────┘ └─────┘ └─────┘ └─────┘ ←──── 双向链表连接 ────→特点叶子节点存储完整的行数据所有列的值叶子节点之间通过双向链表连接数据按主键顺序物理有序存储5.4 聚簇索引的优劣势优势劣势主键查询只需 1 次 B 树查找每表只能有 1 个聚簇索引范围查询高效沿叶子链表扫描数据只能按一种顺序物理存储相邻数据物理上也相邻缓存友好随机主键插入会导致频繁页分裂减少磁盘 I/O二级索引需要回表六、二级索引Secondary Index6.1 什么是二级索引二级索引也叫辅助索引、非聚簇索引是基于非主键字段构建的 B 树。核心区别二级索引的叶子节点不存储完整行数据只存储索引列的值 对应的主键值。6.2 二级索引的 B 树结构以CREATE INDEX idx_name ON users(name)为例[根节点] / | \ [非叶子] [非叶子] [非叶子] / | \ / | \ / | \ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ │name │→│name │→│name │→│name │ ← 叶子节点 │Alice │ │Bob │ │Carol │ │David │ 存索引列主键值 │id5 │ │id3 │ │id8 │ │id12 │ └──────┘ └──────┘ └──────┘ └──────┘ ←──── 双向链表 ────→6.3 为什么存主键值不存物理地址这是一个非常重要的设计决策聚簇索引会发生页分裂数据行的物理地址会变化。如果二级索引存储物理地址每次页分裂都需要更新所有二级索引维护成本极高。而存储主键值页分裂不影响二级索引只需通过主键到聚簇索引中查找即可。6.4 聚簇索引 vs 二级索引特性聚簇索引二级索引数量每表1 个每表可建多个叶子节点存储完整数据行索引列值 主键值查询方式一次 B 树查找可能需要回表构建依据主键非主键字段物理存储数据按聚簇索引顺序存放不影响数据物理顺序七、回表查询7.1 什么是回表当通过二级索引查找数据时如果查询所需的列不在二级索引中就需要第一步在二级索引的 B 树中查找获取主键值第二步用主键值到聚簇索引中查找完整行数据第二步就是回表——从二级索引回到聚簇索引再查一次。7.2 完整示例CREATETABLEusers(idINTPRIMARYKEY,nameVARCHAR(20),ageINT,cityVARCHAR(20),INDEXidx_name(name))ENGINEInnoDB;-- 这条 SQL 需要回表SELECT*FROMusersWHEREnameAlice;执行过程① 在 idx_name 的 B 树中查找 nameAlice → 找到叶子节点nameAlice, id5 ② 用 id5 到聚簇索引的 B 树中查找 → 找到完整行id5, nameAlice, age30, cityBeijing ③ 返回结果7.3 回表的性能影响每次回表 1 次额外的 B 树查找2~3 次磁盘 I/O如果查询返回 1000 行最坏情况需要1000 次回表当优化器预估回表行数过多时会认为回表的随机 I/O 成本 全表扫描的顺序 I/O 成本直接放弃索引走全表扫描八、覆盖索引——消除回表8.1 原理如果查询所需的所有列都包含在索引中就不需要回表了——这就是覆盖索引Covering Index。-- 建立联合索引CREATEINDEXidx_name_ageONusers(name,age);-- 查询列都在索引中 → 覆盖索引无需回表SELECTid,name,ageFROMusersWHEREnameAlice;idx_name_age的叶子节点存储(name, age, id)而查询只需要id, name, age——所有数据都在索引里一次查找搞定。8.2 如何判断用EXPLAIN查看执行计划如果Extra列显示Using index表示使用了覆盖索引。EXPLAINSELECTid,name,ageFROMusersWHEREnameAlice;-- Extra: Using index ✅8.3 为什么常说避免 SELECT *SELECT *会查询所有列而二级索引通常不包含所有列因此几乎必然触发回表。更严重的是当优化器预估回表成本过高时会直接放弃索引走全表扫描——索引白建了。九、联合索引与最左前缀原则9.1 联合索引的结构联合索引复合索引是基于多个列建立的索引。以INDEX idx_a_b_c (a, b, c)为例B 树的排序规则先按a排序 →a相同时按b排序 →a, b都相同时按c排序。叶子节点排列顺序 (a1, b1, c1) → (a1, b1, c3) → (a1, b2, c1) → (a2, b1, c1) → ...9.2 最左前缀原则查询条件必须从联合索引的最左列开始且中间不能跳过列CREATEINDEXidx_a_b_cONusers(a,b,c);-- ✅ 使用 aWHEREa1-- ✅ 使用 a, bWHEREa1ANDb2-- ✅ 使用 a, b, cWHEREa1ANDb2ANDc3-- ❌ 不使用索引跳过了 aWHEREb2-- ❌ 不使用索引跳过了 aWHEREb2ANDc39.3 范围查询的影响范围查询、、BETWEEN、LIKE后面的列无法使用索引-- ✅ a 用等值b 用范围c 无法使用索引WHEREa1ANDb2ANDc3-- 实际用到索引的列a, bc 失效-- ✅ a 用范围b 和 c 都无法使用索引WHEREa1ANDb2ANDc3-- 实际用到索引的列ab、c 失效原因联合索引先按a排序a相同时按b排序。当b是范围查询时b 2的结果中b值不唯一c就失去了排序意义因此c的索引无法使用。9.4 和 IN 的区别在 MySQL 8.0 中IN被视为等值查询不会中断最左前缀-- ✅ a 用 INb 仍可使用索引WHEREaIN(1,2,3)ANDb29.5 索引设计建议等值查询的列放前面范围查询的列放后面区分度高的列放前面如user_id优于gender尽量用联合索引代替多个单列索引十、索引下推Index Condition Pushdown, ICP10.1 背景在没有索引下推之前对于联合索引(a, b)查询WHERE a 1 AND b 2的过程① 在索引中找到 a1 的所有记录通过最左前缀 ② 逐条回表取出完整行 ③ 在 Server 层判断 b 是否等于 2步骤 ②③ 中大量回表取出的数据最终被过滤掉造成不必要的 I/O。10.2 索引下推的优化MySQL 5.6 引入了索引下推ICP在遍历索引的过程中直接在存储引擎层过滤掉不满足条件的记录减少回表次数。① 在索引中找到 a1 的所有记录 ② 在索引层直接判断 b 是否等于 2不满足的直接跳过 ③ 只对满足 a1 AND b2 的记录回表10.3 如何判断EXPLAIN的Extra列显示Using index condition表示使用了索引下推。十一、Hash 索引11.1 基本结构Hash 索引基于哈希表实现对索引键计算 Hash 值Hash 值指向数据行的存储位置。特性Hash 索引B 树索引等值查询O(1)极快O(log N)范围查询❌ 不支持✅ 支持排序❌ 不支持✅ 支持最左前缀❌ 不支持✅ 支持哈希冲突存在性能退化无此问题11.2 Memory 引擎的 Hash 索引MySQL 的 Memory 存储引擎默认使用 Hash 索引。适合场景内存中的临时表只需要等值查询WHERE id ?数据量不大11.3 InnoDB 的自适应哈希索引AHIInnoDB 在 B 树的基础上内部维护了一个自适应哈希索引当 InnoDB 观察到某些索引值被频繁等值查询时自动为这些值建立 Hash 索引将 B 树的 O(log N) 查询优化为 O(1)完全自动无需手动配置当数据访问模式变化时自动释放不常用的 Hash 条目十二、MySQL 索引分类总结12.1 按数据结构分类类型说明引擎支持B 树索引默认索引类型支持范围查询和排序InnoDB、MyISAMHash 索引仅支持等值查询速度极快MemoryInnoDB 有自适应 Hash全文索引用于文本全文搜索InnoDB5.6、MyISAM12.2 按应用方式分类类型说明语法主键索引唯一 非空InnoDB 中即聚簇索引PRIMARY KEY (id)唯一索引索引值唯一允许 NULLUNIQUE INDEX idx (col)普通索引最基本的索引无约束INDEX idx (col)联合索引多列组合的索引INDEX idx (col1, col2, col3)前缀索引只索引字符串的前 N 个字符INDEX idx (col(10))全文索引用于FULLTEXT全文搜索FULLTEXT INDEX idx (col)12.3 按物理存储分类类型说明聚簇索引叶子节点存储完整行数据InnoDB 主键索引二级索引叶子节点存储索引列值 主键值十三、索引失效的常见场景图2MySQL 索引失效常见场景总结建了索引却不走索引是最常见的性能陷阱。以下是高频失效场景13.1 对索引列使用函数或表达式-- ❌ 索引失效WHEREDATE_FORMAT(create_time,%Y-%m-%d)2026-01-01WHEREid11000-- ✅ 改写为等值范围查询WHEREcreate_time2026-01-01ANDcreate_time2026-01-02WHEREid999原因B 树中存的是原始值函数运算后无法匹配索引的有序性。13.2 隐式类型转换-- phone 是 VARCHAR 类型-- ❌ 传入数字触发隐式转换索引失效WHEREphone13800138000-- ✅ 传入字符串WHEREphone13800138000原因MySQL 将字符串列转为数字比较等价于对索引列使用了函数。13.3 不满足最左前缀原则-- 联合索引 (a, b, c)-- ❌ 跳过最左列 aWHEREb2ANDc313.4 LIKE 以通配符开头-- ❌ 索引失效WHEREnameLIKE%张WHEREnameLIKE%张%-- ✅ 索引生效WHEREnameLIKE张%原因B 树按前缀排序通配符在前无法确定扫描范围。13.5 OR 条件中有无索引列-- ❌ 如果 b 没有索引整个 OR 条件导致全表扫描WHEREa1ORb2原因OR 要求两边的条件都能走索引否则优化器选择全表扫描。13.6 索引列参与运算-- ❌ 索引失效WHEREage*240-- ✅ 改写WHEREage2013.7 NOT IN / NOT EXISTS某些场景下NOT IN和NOT EXISTS会导致优化器放弃索引选择全表扫描。可以用LEFT JOIN ... WHERE ... IS NULL改写。13.8 优化器判断全表扫描更快当表数据量很小或者使用索引需要回表的行数占比过大时优化器会认为全表扫描的顺序 I/O 比索引的随机 I/O 更快主动放弃索引。十四、用 EXPLAIN 验证索引使用情况判断索引是否生效不要靠猜——用EXPLAINEXPLAINSELECT*FROMusersWHEREnameAlice;重点关注字段含义好的值差的值type访问类型const/ref/rangeALL全表扫描key实际使用的索引具体索引名NULL没走索引rows预估扫描行数越小越好接近全表行数Extra额外信息Using index覆盖索引Using filesort额外排序十五、总结核心知识脉络B 树底层数据结构 ├── 聚簇索引主键索引叶子存完整数据行 │ └── 每表 1 个数据按主键物理有序 ├── 二级索引辅助索引叶子存索引列主键值 │ └── 每表可建多个 │ └── 可能需要回表 │ ├── 覆盖索引 → 消除回表 ✅ │ └── 索引下推 → 减少回表 ✅ ├── 联合索引多列组合 │ └── 最左前缀原则 ├── Hash 索引等值查询 O(1) │ └── InnoDB 自适应哈希索引 └── 索引失效场景 └── 函数、隐式转换、不满足最左前缀、LIKE %、OR 等一句话记忆概念一句话B 树只有叶子存数据叶子双向链表串联3 层可存 2000 万条聚簇索引叶子存完整行数据每表 1 个数据物理有序二级索引叶子存索引列主键值每表可建多个回表从二级索引拿到主键再去聚簇索引查完整数据覆盖索引查询的列全在索引里不用回表联合索引多列组合遵循最左前缀原则索引下推在索引层就过滤数据减少回表次数Hash 索引等值查询 O(1)不支持范围查询参考资源MySQL 官方文档 — InnoDB Index Types
--- Rollout)
