MySQL系列01-索引

MySQL 索引结构：从页组织到查询路径的底层分析

1. 写在前面：这篇文章要解决什么问题

在日常使用 MySQL 的过程中，当我们遇到慢查询时，第一反应往往是先通过 explain 去看执行计划，判断 SQL 是否命中了索引、扫描了多少行、是否出现了回表或者 filesort。但如果只停留在这个层面，很多问题其实并没有真正解决。更进一步的问题是，索引为什么会生效，为什么有些 SQL 明明建了索引却还是很慢，以及在一个真实业务表中，索引到底应该如何设计，才算是合理且可持续的。 这篇文章的目的，就是把这些问题放到 InnoDB 的底层实现里重新梳理一遍。我们不只讨论“索引是什么”，而是重点理解 MySQL 索引的实现机制、查询路径以及背后的设计取舍。尤其在当前 AI 工具越来越普遍的情况下，虽然你可以把一条慢 SQL 交给工具去分析甚至直接生成优化建议，但工程上仍然需要自己判断这样的索引设计是否真正合理，是否符合业务访问模式，以及会不会给写入、存储和维护成本带来新的问题。

因此，这篇文章希望解决的不是一个面试题层面的“索引知识点总结”，而是建立一套更完整的认知框架：从存储结构理解索引，从查询路径理解性能，从业务场景理解索引设计。

• 重点讨论 InnoDB。
• 重点讨论 B+Tree 索引。
• 会提到哈希、全文、自适应哈希，但不作为主体。

2. 从存储成本出发：为什么数据库一定需要索引

索引的出现，本质上不是为了让数据库“看起来更高级”，而是为了降低数据查找成本。对于数据库来说，真正昂贵的操作从来都不是一两次 CPU 计算，而是把数据从磁盘读取到内存的过程。尤其当表中的数据量越来越大时，如果没有索引，数据库在执行查询时往往只能从头到尾扫描整张表，再从大量记录中筛选出满足条件的那一部分数据。这样的查找方式在数据量小的时候还可以接受，但一旦进入百万级、千万级数据规模，代价就会迅速变得不可忽视。

可以把这个过程类比为查字典。如果一本字典没有目录，也没有拼音索引，你想找某个字，就只能从第一页开始一页页往后翻，直到找到目标位置为止。数据库中的全表扫描，本质上就是类似的过程。问题在于，数据库面对的不是几百页纸，而是大量分布在磁盘页中的记录；每多读取一个页，背后就可能多一次磁盘 IO，而磁盘 IO 的成本远高于内存访问。因此，在数据库场景里，查询性能的关键并不只是“比较次数少了多少”，而是“为了找到目标数据，究竟要读取多少个页”。

索引的价值，就在于为数据建立了一套更高效的定位路径。它并不是简单地把字段单独存一份，而是通过特定的数据结构，把原本需要大范围扫描的查找过程，变成一个可以持续缩小范围、快速定位目标页的过程。也正因为如此，理解数据库索引时，不能只从“有没有命中索引”这个结果出发，更应该从底层存储成本去理解它存在的必要性。后面我们讨论 B+Tree、聚簇索引、二级索引，最终都要回到这个核心问题上：如何用更少的页访问次数，完成一次查询。

3. InnoDB 的基本存储单位：页、行、页目录、页间链表

3.1 MySQL 的行存储结构

在理解索引之前，需要先统一一个概念：对于 InnoDB 来说，行是逻辑上的记录单位，而页才是物理存储和磁盘 IO 的基本单位。我们平时看到的是一行一行的数据，但这些行最终并不是零散地落在磁盘上，而是被组织到一个个页中，再由这些页进一步组成整棵索引树。

MySQL 支持多种行格式，常见的有 REDUNDANT、COMPACT、DYNAMIC、COMPRESSED。其中 COMPACT 是理解行结构最经典的切入点，而在 MySQL 8.0 中，更常见的默认行格式通常是 DYNAMIC。因此这里先以 COMPACT 为例说明行记录在页内大致是如何组织的。

一条记录通常可以拆成几部分来理解：

• 可变长度字段列表：用于描述 varchar 这类变长字段实际占用了多少空间。
• NULL 值列表：用于标识哪些允许为 NULL 的列当前确实是 NULL。
• 记录头信息：保存这条记录在页内组织和访问时需要的元数据。
• 列数据区：存储这条记录真正的列值。

记录头信息里有几个字段比较关键：

• deleted_flag：表示该记录是否被标记删除。
• min_rec_flag：用于标识某些 B+Tree 非叶子页中的最小记录，更多是页管理和树结构维护时使用。
• n_owned：表示当前记录作为槽目录“组内代表”时，管理了多少条记录。
• heap_no：表示这条记录在页内堆中的相对位置。
• record_type：表示记录类型。0 表示普通记录，1 表示非叶子节点记录，2 表示 Infimum，3 表示 Supremum。
• next_record：指向页内下一条记录，因此页内记录会按逻辑顺序串成一个单向链表。

这里要特别注意隐藏列。InnoDB 的聚簇索引记录里，通常还会包含几个内部字段：

• trx_id：最近一次修改该行记录的事务 ID。
• roll_pointer：指向回滚段，用于 MVCC。
• row_id：只有在表没有显式主键，且也没有可作为聚簇键的 NOT NULL UNIQUE 索引时，InnoDB 才会生成这个隐藏行 ID，作为内部聚簇键。

InnoDB 必须选择一个聚簇索引。如果用户没有定义合适的主键，它就会退而求其次，使用内部隐藏列来完成这件事。

上面这些内容描述的是 COMPACT 行格式的基本组织方式。COMPACT 和 DYNAMIC 的一个重要差异，在于大字段的页外存储策略，也就是常说的溢出页处理。

• 溢出页：InnoDB 默认页大小通常是 16KB，但这并不意味着“一行数据超过 16KB 才会使用溢出页”。更常见的情况是，某些变长列，比如很长的 varchar、text、blob，无法完全留在当前页中，于是需要把部分或大部分内容放到页外。COMPACT 通常会在当前页保留一部分前缀，再把剩余内容放到溢出页；DYNAMIC 则更倾向于只在当前页保留一个 20 字节的指针，把长列内容放到溢出页中。

这一点非常重要，因为它解释了为什么行格式不仅影响存储空间，也会影响页利用率、缓存命中率以及后续的查询成本。

3.2 页的结构

如果说“行”是我们理解数据的方式，那么“页”才是 InnoDB 真正组织数据的方式。在磁盘中，InnoDB 默认以 16KB 作为一个页的大小，数据页、索引页、本质上都是页，只是内部用途不同。

一个页通常可以拆成下面几个部分：

0. File Header：页的通用头信息，比如页号、上一页和下一页等信息。
1. Page Header：数据页自己的头信息，用来描述页内记录数量、空闲空间等状态。
2. Infimum + Supremum：两个虚拟记录，分别表示页内最小边界和最大边界。
3. User Records：页中真正存储的用户记录。
4. Free Space：页内尚未使用的空间，用于后续插入新记录。
5. Page Directory：页目录，也就是页内槽数组，用于加速页内查找。
6. File Trailer：页尾校验信息，用于检测页是否完整。

这里需要强调两点。

第一，页空间不足并不等于“直接产生碎片”。更常见的结果是插入操作无法在当前页继续完成，从而触发页分裂；而所谓碎片，更多来自删除、更新和页利用率下降之后留下的空洞。

第二，Page Directory 不是“页的相对位置表”，而是页内记录的槽目录。它保存的是若干目录项，每个目录项对应一组记录中的“代表记录”。这样做的目的是避免每次都从页头把所有记录线性扫一遍。

从页内组织方式来看，可以把它理解成两层结构：

• 第一层是页目录，目录项形成一个有序槽数组。
• 第二层是页内记录，记录之间通过 next_record 串成单向链表。

因此，InnoDB 在页内查找一条记录时，并不是直接顺着整条链表从头扫到尾，而是“先通过页目录缩小范围，再在小范围内顺链表查找”。

举个例子，假设我们要在某个主键页中查找 id = 6 这条记录，过程大致如下：

0. 先在 Page Directory 里做二分查找，找到目标值可能落在哪两个槽之间。
1. 假设通过二分之后，发现 id = 6 应该位于“槽 1 对应的最大记录”和“槽 2 对应的最大记录”之间。
2. 这时就不需要扫描整页，只需要从槽 1 对应的那组记录起点开始，顺着 next_record 向后遍历。
3. 如果在这组记录中找到 id = 6，查找结束；如果遍历到更大的值还没有命中，说明该记录在当前页中不存在。

也就是说，页目录解决的是“先快速定位到哪一小段记录”，记录链表解决的是“如何在这一小段记录里顺序找到目标”。后面 B+Tree 的每一个节点，本质上也都是这样的页结构，只不过页与页之间又进一步通过指针和层级关系组织成了一棵多叉树。

3.3 页间链表为什么重要

当一张表的数据越来越多时，显然不可能只放在一个页里。多个页之间也需要被组织起来，否则数据库虽然能找到某一页，但无法高效地在相邻页之间移动。

在 InnoDB 中，叶子页之间会按照键值顺序通过页级别指针串成双向链表。这一点和前面提到的“页内记录通过 next_record 组成单向链表”不是同一层次：

• 页内链表，解决的是单个页内部记录如何组织。
• 页间链表，解决的是多个叶子页之间如何顺序访问。

总结这一节，其实可以得到一个很关键的认识：InnoDB 中的数据访问几乎都不是“直接定位到某一行”，而是先定位到某个页，再在页内定位到某条记录。

4. 索引结构选择：跳表，二叉树、AVL、B-Tree、B+Tree

前面我们已经提到，MySQL（更准确地说是 InnoDB）在索引实现上采用的是 B+Tree。那问题来了：为什么最终会选择 B+Tree，而不是其他数据结构？接下来我们从存储组织和查询路径两个角度继续分析。

先回到页内查找的过程：InnoDB 会通过页内的 Page Directory（槽目录）定位记录大致所在的分组，再通过二分查找快速确定目标记录可能落在哪个槽区间。这个机制在单页内非常高效，但随着数据规模持续增长，瓶颈会从“页内定位”转移到“跨页定位”——即使页内查找足够快，仍然需要在大量页之间继续缩小范围。于是，数据库在槽目录之上继续做抽象：把多个页组织成更高层级的有序节点，并在这些节点之上继续建立索引，最终形成根节点、非叶子节点和叶子节点的层次结构。这样查询就可以从根节点出发，逐层缩小范围，直到命中目标数据所在的叶子页。

那么问题来了，究竟采用哪种数据结构比较好？这里要先明确一个判断标准：数据库索引结构要优化的核心目标，不只是让理论上的比较次数更少，而是要在“以页为单位进行磁盘 IO”的前提下，尽可能降低树高、减少页访问次数，并支持范围查询和顺序扫描。带着这个标准，再来看几种常见的数据结构。

跳表

跳表是一种建立在有序链表之上的概率型数据结构。它通过为部分节点增加多层“快速通道”，让查找、插入、删除操作在期望时间复杂度上达到 O(log n)。从实现思路上看，跳表可以理解为一种用链表结构近似平衡树查找能力的方案。

以上图为例，最底层 Lv 0 保存全部元素，并按键值有序排列；更高层则是对下一层节点的稀疏抽样。查找 31 时，可以先从高层快速缩小范围，再逐层下降到更精确的位置，因此不需要像普通链表那样顺序扫描所有节点。

不过，跳表更适合内存场景，不适合作为数据库磁盘索引的核心结构。原因不在于“每走一步就一定发生一次随机 IO”，而在于它的节点组织方式在页式存储模型下很难获得足够高的分支因子，跨节点定位时也不如 B+Tree 那样有利于减少页访问次数。因此，跳表更常见于以内存访问为主的系统中，例如 Redis 的有序集合 zset。

二叉树

二叉查找树能够根据键值大小关系逐步缩小查找范围，但它有一个明显问题：在极端情况下，整棵树可能退化成一条链表。此时查找效率会从 O(log n) 退化到 O(n)，性能失去保障。

平衡树

为了解决普通二叉查找树容易退化的问题，可以引入 AVL 树、红黑树这类平衡树。它们通过旋转等机制控制树高，使查找复杂度稳定在 O(log n)。

但平衡树仍然不适合作为数据库的主索引结构。核心原因在于它本质上仍是“低分支”的二叉结构，每个节点通常只有两个子节点。对于数据库这种以磁盘页为单位进行 IO 的系统来说，分支数过少会导致树高偏高，从根节点走到目标记录需要访问更多层级，也就意味着更高的页读取成本。

B树

B 树将二叉树扩展为多叉树，一个节点可以拥有多个子节点。这样一来，树的高度可以明显下降，更适合磁盘存储场景。

不过，B 树的非叶子节点和叶子节点都可能保存数据记录或记录指针，这会占用节点空间，导致单个节点能容纳的索引项数量受到限制，从而影响整体分支数。

B+树

B+Tree 可以看作是对 B 树进一步面向数据库场景的优化。它的非叶子节点只保存键值和子节点指针，真正的数据记录统一存放在叶子节点中。

这种设计带来几个关键好处：

• 非叶子节点更“轻”，一个页中可以容纳更多索引项，树的分支因子更大，整体树高更低。
• 所有查询最终都会落到叶子节点，查询路径更加稳定。
• 叶子节点之间按键值顺序链接，非常适合范围查询、排序和顺序扫描。

在 InnoDB 默认页大小为 16KB、键值长度合理的情况下，较低树高的 B+Tree 就可以支撑非常大的数据规模，很多业务表在三到四层树高下就已经能够覆盖大量数据。

也正因为如此，B+Tree 比跳表、二叉树、平衡树和普通 B 树更符合数据库在磁盘页访问模型下的需求，这也是 InnoDB 选择它作为核心索引结构的根本原因。

5. B+Tree 在 InnoDB 中到底长什么样

5.1 B+Tree 的节点分层

在 InnoDB 中，B+Tree 的每一个节点本质上就是一个页。整棵树从上到下可以分为三层来理解：

• 根页：整棵索引树的入口，查询从这里开始。当数据量很小时，根页本身可能既是根也是叶子。
• 非叶子页（内部节点） ：保存的是键值和指向子页的指针，不存放实际的行记录。它的唯一职责是"导航"——帮助查询逐层缩小范围，定位到目标数据所在的叶子页。
• 叶子页：保存实际的数据记录（对于聚簇索引）或索引列值加主键值（对于二级索引）。叶子页之间按键值顺序通过双向链表相互连接。

这种分层设计的核心意义在于：非叶子节点越轻，单个页能容纳的索引项越多，树的分支因子就越大，整体树高就越低。而树高每降低一层，一次查询就少一次页读取。

5.2 一次等值查询是如何走完整棵树的

以一条最简单的主键等值查询为例：

SELECT * FROM user WHERE id = 35;

当存储引擎接收到这条查询后，会从聚簇索引的根页开始，沿着 B+Tree 逐层向下查找：

0. 根页：读取根页中的索引项，通过比较找到 35 应该落入哪个子节点范围。假设根页中的分界键值为 [10, 20, 30]，则 35 > 30，走向最右侧子页指针。
1. 非叶子页：进入下一层页后，继续比较键值，进一步缩小到更精确的子页范围。假设该页内分界键为 [31, 33, 36]，则 33 < 35 < 36，走向对应的子页指针。
2. 叶子页：最终到达叶子页，在页内通过 Page Directory 做二分查找，再顺着记录链表定位到 id = 35 这条记录，返回整行数据。

整个过程中，树有多少层就读取多少个页。对于一棵三层高的 B+Tree，一次等值查询最多只需要三次页读取就能命中目标记录。

如果查询走的是二级索引而不是主键索引，流程会多一步：

0. 先在二级索引树上按上述方式查找，定位到叶子页后拿到的不是完整行数据，而是索引列值 + 对应的主键值。
1. 再拿着这个主键值，回到聚簇索引树中重新执行一次从根到叶的查找，最终取出完整行记录。

这个"从二级索引拿到主键，再回聚簇索引取数据"的过程，就是常说的回表。

5.3 范围查询为什么是 B+Tree 的强项

B+Tree 之所以特别适合范围查询，根本原因在于叶子节点的两个特性：

0. 有序性：聚簇索引的叶子按主键有序排列，二级索引的叶子按索引键有序排列。
1. 链表连接：相邻叶子页之间通过双向链表串联，可以直接顺序访问下一页，无需再回到上层节点重新定位。

因此，当执行一条范围查询时，比如：

SELECT * FROM user WHERE id BETWEEN 20 AND 50;

存储引擎只需要先通过 B+Tree 定位到 id = 20 所在的叶子页（和等值查询一样的路径），然后从该位置开始，沿叶子链表向右顺序扫描，直到遇到 id < 50 的记录为止。中间不需要再走任何非叶子节点。

这也解释了为什么 B+Tree 比哈希索引更适合数据库场景：哈希索引只能处理精确等值匹配，而 B+Tree 天然支持 BETWEEN、>、<、ORDER BY、GROUP BY 这类需要利用数据有序性的操作。

6. 聚簇索引与二级索引：物理差异与主键设计

6.1 InnoDB 如何选择聚簇索引

在 InnoDB 中，每张表有且只有一棵聚簇索引树，表中所有行记录的物理存储就是按照聚簇索引的键值顺序组织的。换句话说，聚簇索引不是"数据之外的一个附加结构"，而是数据本身的存储方式。

InnoDB 选择聚簇索引的规则如下：

0. 如果表定义了显式主键（PRIMARY KEY），则以该主键作为聚簇索引的键。
1. 如果没有显式主键，InnoDB 会选择第一个所有列都为 NOT NULL 的唯一索引（UNIQUE INDEX）作为聚簇键。
2. 如果以上两者都不存在，InnoDB 会自动生成一个 6 字节的隐藏列 row_id，作为内部聚簇键。

在绝大多数业务场景中，我们都会显式定义主键，因此聚簇索引几乎等同于"主键索引"。

6.2 两棵树的物理结构对比

理解聚簇索引和二级索引的核心区别，关键在于看它们的叶子页里分别存了什么。

聚簇索引的叶子页存储的是完整的行记录。也就是说，当你通过主键定位到叶子页后，该页内的每条记录都包含这一行所有列的值（加上隐藏列 trx_id、roll_pointer）。因此，主键查询只需要一次从根到叶的路径就能拿到全部数据。

二级索引的叶子页存储的是索引列的值加上对应的主键值。它不包含行的其他列数据。因此，如果查询需要的列不在该二级索引中，就必须拿着主键值再回到聚簇索引树中查一次，这就是回表。

用一个具体例子来说明。假设有一张 user 表，主键为 id，并且在 name 列上建了二级索引：

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    INDEX idx_name (name)
);

此时 InnoDB 内部会维护两棵 B+Tree：

• 聚簇索引树：非叶子页存储 (id值, 子页指针)，叶子页存储 (id, name, age, email, trx_id, roll_pointer) 的完整行记录，按 id 有序排列。
• 二级索引树 idx_name：非叶子页存储 (name值, 子页指针)，叶子页存储 (name, id)，按 name 有序排列。

当执行 SELECT * FROM user WHERE name = '张三' 时，查询路径是：

0. 在 idx_name 这棵二级索引树上，从根页逐层向下，定位到 name = '张三' 所在的叶子页。
1. 从叶子页中取出对应的主键值，比如 id = 42。
2. 拿着 id = 42 回到聚簇索引树，再执行一次从根到叶的查找，最终取出完整行记录。

这个过程涉及两棵树的访问，总页读取次数是两棵树的树高之和。如果聚簇索引树高为 3，二级索引树高为 3，那么一次带回表的查询最多需要 6 次页读取。

6.3 主键设计为什么会影响整体索引效率

从上面的结构对比可以看出，主键不仅决定了聚簇索引树的组织方式，还会通过"二级索引叶子页存储主键值"这一机制，间接影响所有二级索引的效率。具体来说，主键设计需要关注三个维度：有序性、长度和稳定性。

有序性：自增主键减少页分裂

聚簇索引的叶子页按主键顺序排列，页与页之间通过双向链表连接。当新记录插入时，如果主键是自增的，新记录总是追加到当前最右侧叶子页的末尾。只有当该页写满时，才会申请一个新页继续追加，整个过程是顺序写入，不会影响已有页的结构。

但如果主键是随机生成的（比如 UUID），新记录的主键值可能落在已有叶子页的中间位置。当目标页已经写满时，InnoDB 不得不执行页分裂：把当前页的一部分记录搬到新申请的页中，再把新记录插入到正确的位置。页分裂不仅涉及数据搬迁和页内重新排列，还需要更新父节点的索引项和相邻页的链表指针。在高并发写入场景下，频繁的页分裂会显著拖慢插入性能。

在分布式系统中，如果全局自增 ID 难以实现，可以使用雪花算法（Snowflake）这类方案。雪花算法生成的 ID 虽然不是严格连续的，但高位包含时间戳，整体趋势是递增的，因此在大多数情况下仍然能保证顺序写入，只在极少数并发场景下可能出现局部乱序，页分裂的概率远低于纯随机 ID。

长度：主键越短，索引容量越大

主键长度的影响不仅体现在聚簇索引本身，更体现在所有二级索引上。因为每棵二级索引的叶子页都要存储主键值，主键越长，单个叶子页能容纳的索引条目就越少，树的层级可能更高，查询时需要读取的页也就越多。

举一个简单的对比：假设二级索引叶子页大小为 16KB，索引列本身占 20 字节。如果主键是 4 字节的 INT，每条索引记录约 24 字节，一页大约能存 500 多条；如果主键是 36 字节的 UUID 字符串，每条索引记录约 56 字节，一页只能存不到 300 条。当表的数据量达到千万级时，这种差异会直接反映在树高和查询 IO 次数上。

稳定性：主键更新的代价极高

如果业务中存在更新主键值的操作，比如把某条记录的 id 从 2 改为 4，InnoDB 并不会简单地修改叶子页中的键值。实际执行过程是：

0. 将原记录（id = 2）标记为删除（设置 deleted_flag），并从叶子页的记录链表中摘除。
1. 在聚簇索引中按新的主键值（id = 4）重新定位插入位置，写入一条新记录。
2. 所有引用了该行主键的二级索引，也需要相应地删除旧条目、插入新条目。

这个过程本质上等同于"删除一行再插入一行"，涉及多棵索引树的修改，代价远高于普通列的更新。因此，主键一旦确定，应当视为不可变的。

聚簇索引和二级索引的物理差异，可以浓缩为一句话：聚簇索引的叶子页存的是数据本身，二级索引的叶子页存的是"指向数据的指针"（即主键值）。这种设计决定了两件事：第一，二级索引查询在需要完整行数据时必须回表；第二，主键的设计质量会传导到整张表所有索引的效率上。

基于此，主键设计的基本原则是：自增（保证顺序写入）、短小（降低索引空间占用）、不可变（避免级联更新代价）。

7. 联合索引的底层排序规则：最左匹配为什么成立

7.1 联合索引的本质：多列排序的二级索引

上一节讨论了聚簇索引和二级索引的物理差异。联合索引本质上就是一种二级索引，区别在于它的索引键由多个列组成。以联合索引 (a, b, c) 为例，它构成的 B+Tree 叶子页中，每条记录保存的是 (a, b, c, 主键值)，按照特定的排序规则有序排列。

7.2 联合索引的排序规则

联合索引 (a, b, c) 在 B+Tree 叶子页中的排列遵循多级排序：

0. 首先按 a 的值排序。
1. a 相同的记录，再按 b 的值排序。
2. a 和 b 都相同的记录，再按 c 的值排序。

用一个具体的叶子页数据排列来直观理解：

a=1, b=1, c=3, pk=10
a=1, b=2, c=1, pk=7
a=1, b=2, c=5, pk=3
a=2, b=1, c=2, pk=15
a=2, b=3, c=1, pk=8
a=3, b=1, c=4, pk=12

从这个排列中可以直接观察到一个关键事实：a 在全局范围内是有序的；b 只在相同 a 值的记录范围内是有序的；c 只在 a 和 b 都相同的记录范围内是有序的。而 B+Tree 的查找能力完全依赖有序性——如果目标列在当前查找范围内不是有序的，树结构就无法提供定位能力。

这就是最左匹配原则的本质：它不是一条人为规定的规则，而是多列排序方式的直接推论。

7.3 从排序规则推导查询行为

基于上述排序机制，不同的查询条件能利用联合索引的程度是不同的。

WHERE a = 1 AND b = 2：索引完全可用。先通过 a = 1 在 B+Tree 中定位到 a 为 1 的范围，而在这个范围内 b 是有序的，因此可以继续利用 b = 2 进一步缩小范围，最终精确定位到目标记录。

WHERE b = 2：索引无法使用。从上面的排列可以看到，b = 2 的记录分散在不同的 a 值分组中（a=1 下有 b=2，但 a=2 和 a=3 下没有），在全局视角下 b 并不有序。B+Tree 无法通过 b 的值从根节点开始做有效的范围缩小，只能退化为全索引扫描或全表扫描。

WHERE a = 1 AND c = 3：索引只能利用到 a 列。通过 a = 1 可以定位到对应的记录范围，但由于跳过了 b，在 a = 1 的记录中 c 的值并不是全局有序的（c 的顺序依赖于 b 的分组）。因此 c = 3 无法继续通过索引结构缩小范围。不过，在 MySQL 5.6 及以上版本中，索引下推（Index Condition Pushdown）优化会在存储引擎层直接使用 c = 3 对索引记录做过滤，减少回表次数，这和"索引能定位"是两个层次的事情。

7.4 范围条件对后续列的影响

除了跳过列之外，范围条件也会截断后续列对索引的利用。

WHERE a > 1 AND b = 2：索引可以利用 a > 1 做范围扫描，定位到 a 为 2、3、4……的所有记录。但在这个范围扫描的结果中，不同 a 值各自有独立的 b 排序，b 在跨 a 值的全局范围内并不有序。因此 b = 2 无法继续通过索引结构缩小范围，只能在扫描过程中逐条过滤。

这也是为什么联合索引的字段顺序设计中，通常建议将等值查询的列放在前面、范围查询的列放在后面。等值条件不会破坏后续列的有序性，而范围条件会。

7.5 联合索引对排序的影响

联合索引的有序性不仅服务于 WHERE 过滤，也直接影响 ORDER BY 是否需要额外排序。

ORDER BY a, b：可以直接利用索引的物理排序，存储引擎按叶子页顺序返回记录即可，不需要额外的排序操作（filesort）。

ORDER BY b, a：无法利用索引排序。索引的物理顺序是先 a 后 b，与 ORDER BY b, a 要求的顺序不匹配，存储引擎必须读取数据后再做一次内存或磁盘排序。

ORDER BY b：同样无法利用索引排序。b 在全局范围内并不有序，和 WHERE b = ? 无法命中索引是同一个原因。

WHERE a = 1 ORDER BY b：可以利用索引排序。通过 a = 1 定位到的记录范围内，b 是有序的，因此 ORDER BY b 直接匹配了这个局部有序性，无需额外排序。

这说明联合索引的排序能力同样遵循最左匹配的逻辑：只有当排序列的顺序与索引的物理排序一致，或者前导列已经被等值条件固定时，索引才能为排序提供帮助。

8. 覆盖索引、回表、索引下推：命中索引不代表代价低

前面几节已经解释了 B+Tree 是如何帮助查询缩小范围的，但在真实业务中，"命中了索引" 并不等于 "查询代价一定低"。尤其是在 InnoDB 中，很多查询虽然使用了二级索引，却仍然可能很慢。原因在于：二级索引解决的是"如何找到候选记录"，而不一定解决"如何以最低成本拿到最终结果"。

这一节重点回答三个问题：为什么二级索引查询仍然可能很贵，什么情况下覆盖索引会比单纯命中索引更有价值，以及索引下推到底优化了哪一步。

8.1 回表是什么

回表的前提，是查询走了二级索引，但最终还需要拿到不在该索引中的列。假设 user 表在 name 列上建了二级索引，执行下面这条语句：

SELECT * FROM user WHERE name = 'xqc';

这条 SQL 的执行过程通常是：

0. 先在 name 的二级索引树中查找 name = 'xqc' 对应的索引记录。
1. 在二级索引叶子页中拿到匹配记录的主键值。
2. 再拿着这些主键值回到聚簇索引树中，逐条取出完整行记录。

这个"先查二级索引，再查聚簇索引"的过程，就是回表。

回表之所以可能很慢，不是因为多了一次简单比较，而是因为一次二级索引查询，可能会变成很多次聚簇索引查找。如果条件命中了大量记录，那么就会发生大量从二级索引叶子页跳回聚簇索引叶子页的访问。这类访问往往不是顺序扫描，而是更接近随机页读取，因此代价会迅速上升。

这也解释了为什么有些 SQL 虽然看上去"已经走了索引"，执行起来仍然很慢：索引只是帮助你找到了候选记录，但最终取完整行数据的成本可能依然很高。

8.2 覆盖索引为什么常常比“命中索引”更重要

覆盖索引的核心价值，在于让查询可以在二级索引层直接结束，而不必再回到聚簇索引取整行数据。沿用上面的例子，如果 SQL 改成：

SELECT name FROM user WHERE name = 'xqc';

那么查询需要返回的列只有 name，而 name 已经包含在二级索引叶子页里。此时存储引擎在二级索引中找到目标记录后，就可以直接返回结果，不需要再根据主键去聚簇索引中取完整行。

这就是覆盖索引。它的价值不在于"也走了索引"，而在于"查询路径在索引层就结束了"。相比普通二级索引查询，覆盖索引节省的是整段回表路径，因此在高频读场景下往往收益非常明显。

不过，覆盖索引并不意味着索引越宽越好。如果为了覆盖更多查询而把很多列都塞进索引，会直接带来几个副作用：索引页能容纳的记录变少，树的体积变大，写入时维护索引的成本更高，Buffer Pool 压力也更大。因此，覆盖索引的设计原则不是"尽量多放列"，而是围绕高频、稳定、收益明显的查询模式做针对性优化。

8.3 索引下推优化了什么

索引下推（Index Condition Pushdown, ICP）解决的，不是"让联合索引多利用一列做定位"，而是"把原本更晚发生的过滤，尽量提前到索引扫描阶段完成"。

以联合索引 (a, b, c) 为例，假设执行下面这条 SQL：

SELECT * FROM t WHERE a = 1 AND c = 3;

根据最左匹配原则，这条 SQL 只能利用 a = 1 做索引定位，因为中间跳过了 b，c 无法继续参与 B+Tree 的范围缩小。也就是说，索引真正用来"找位置"的仍然只有 a。

但问题在于，联合索引的叶子页中保存的是 (a, b, c, 主键值)。这意味着当存储引擎扫描 a = 1 对应的索引记录时，它其实已经能看到 c 的值了。如果没有索引下推，存储引擎会把这些候选记录的主键值继续拿去回表，再由 Server 层判断 c = 3 是否成立；而有了索引下推之后，存储引擎可以在扫描索引记录时先判断 c = 3，只让真正满足条件的记录继续回表。

因此，索引下推优化的关键收益是减少不必要的回表次数，而不是改变联合索引的定位能力。它不能绕过最左匹配原则，也不能让 c 变成可直接定位的列，但它可以显著降低"先扫描到很多候选记录，再逐条回表过滤"带来的成本。

在执行计划中，如果 Extra 字段出现 Using index condition，通常就表示优化器使用了索引下推。

8.4 小结

回表、覆盖索引和索引下推，本质上都在回答同一个问题：当查询已经命中二级索引后，如何进一步降低从"找到候选记录"到"拿到最终结果"之间的成本。

• 回表解释了为什么命中二级索引后查询仍然可能很贵。
• 覆盖索引解释了为什么最理想的情况，是让查询直接停在索引层结束。
• 索引下推解释了在无法做到覆盖时，如何尽量减少无效回表。

9. 优化器是如何决定“用不用索引”的

前面几节主要讨论的是：索引为什么能加快查询，以及不同索引结构各自适合什么场景。但在真实执行过程中，"存在索引" 和 "最终使用索引" 并不是一回事。对 MySQL 来说，优化器真正要解决的问题不是"这张表上有没有索引"，而是"针对当前这条 SQL，哪一条执行路径的总代价最低"。

这意味着，优化器并不会看到索引就直接使用。它会在多种候选执行方案之间做比较，例如全表扫描、走某个二级索引、走另一个联合索引、是否需要回表、是否可以利用索引排序，最后选择它估计最便宜的那一条路径。

9.1 优化器比较的不是“有没有索引”，而是总代价

从原理上看，优化器的判断是一个典型的成本模型问题。它关心的不是某个局部动作是否更快，而是整条查询链路的总代价。这个总代价通常可以拆成两大类：

• I/O 成本：需要读取多少个页，访问是顺序的还是随机的，是否需要频繁回表。
• CPU 成本：读取数据后需要做多少次比较、过滤、排序、分组、连接等内存计算。

但在具体 SQL 上，这两个抽象成本会进一步表现为几类更容易理解的代价：

• 全表扫描成本：把整张表顺序读一遍，再在结果中做过滤。
• 索引扫描成本：先沿索引树查找，再扫描满足条件的索引记录。
• 回表成本：通过二级索引拿到主键后，再回聚簇索引取完整行。
• 排序成本：结果集不能直接按索引顺序返回时，需要额外执行 filesort。
• 分组或临时表成本：某些 GROUP BY、DISTINCT、复杂排序可能还会引入临时表。

优化器最终比较的，就是这些成本加总后的结果。因此，判断"该不该走索引"，本质上是在判断：索引带来的定位收益，是否足以覆盖索引扫描、回表、排序等额外成本。

9.2 为什么有索引，优化器也可能不用

很多人会直觉地认为：只要建了索引，查询就应该走索引。但对优化器来说，是否使用索引取决于成本，而不是取决于索引是否存在。

最典型的一个场景，是字段区分度很低。比如某张大表在 gender 或 status 这类字段上建了索引，但某个取值覆盖了表中大部分记录。此时即使走索引，优化器也会发现：虽然可以先通过索引定位，但后续仍然要扫描大量记录，甚至还要做大量回表。和这种代价相比，直接顺序扫描整张表反而可能更便宜。

另一个常见场景，是二级索引命中了很多记录，但查询列又不在索引里。例如：

SELECT * FROM user WHERE name LIKE '张%';

如果命中的行数很多，那么执行过程就可能变成：先扫描大量二级索引记录，再根据每条记录的主键值逐条回表。此时总代价未必优于直接扫描聚簇索引。因此，"命中了索引" 并不自动等于 "成本更低"。

这也是为什么在执行计划中，有时候明明 possible_keys 里列出了某个索引，但 key 最终仍然是 NULL，或者优化器选择了 ALL 这类全表扫描访问方式。不是索引失效了，而是优化器判断不用它更划算。

9.3 多个索引都能用时，优化器如何选择

比"有索引却不用"更进一步的问题是：如果一条 SQL 可以使用多条索引，优化器会怎么选？

假设 user 表上同时存在下面两个索引：

• idx_name(name)
• idx_name_age(name, age)

对于查询：

SELECT * FROM user WHERE name = 'xqc' AND age = 30;

理论上这两条索引都能参与执行。但优化器不会简单地按"谁先创建"或者"谁名字更短"来选，而是会继续比较它们的成本差异。例如：

• 哪个索引能过滤掉更多记录。
• 哪个索引扫描的叶子页更少。
• 哪个索引带来的回表次数更少。
• 哪个索引还能顺带满足排序或分组要求。

如果一条联合索引不仅能完成过滤，还能减少回表，或者直接满足 ORDER BY 的顺序要求，那么它的总代价就可能显著低于单列索引。反过来说，某个索引虽然过滤能力更强，但如果需要额外排序，或者回表代价过高，优化器也可能放弃它，转而选择另一条综合成本更低的路径。

这说明优化器不是在选"过滤能力最强"的索引，而是在选"整体执行成本最低"的执行方案。

9.4 排序、分页和回表量都会影响优化器决策

优化器的成本比较不是只看 WHERE 条件，还会把排序、分页和结果返回方式一起纳入考虑。

例如下面两条 SQL，过滤条件相同，但优化器的偏好可能不同：

SELECT * FROM user WHERE name = 'xqc';

SELECT * FROM user WHERE name = 'xqc' ORDER BY create_time LIMIT 20;

第二条 SQL 中，优化器除了考虑 name 的过滤效果，还要考虑：

• 是否有索引可以同时支持过滤和排序。
• 如果不能直接利用索引顺序，额外排序的代价有多大。
• LIMIT 20 是否意味着某条索引路径可以更早停止扫描。

因此，在某些场景中，优化器甚至会选择一条过滤能力不是最强的索引，只因为它能避免 filesort，或者能更快拿到前 20 条结果。对于数据库来说，"更少扫描" 和 "更少排序" 往往同样重要。

9.5 统计信息为什么会影响优化器判断

优化器并不会在每次执行 SQL 之前都把整张表完整扫描一遍，然后再决定走哪条路。那样做的成本太高了。它依赖的是统计信息，比如表的大致行数、索引列的基数、数据分布情况等，来估算某条执行路径可能扫描多少行、回表多少次、排序多少数据。

这也是为什么统计信息不准确时，优化器可能做出错误决策。比如某个字段的真实选择性已经发生了明显变化，但统计信息还停留在旧状态，此时优化器就可能错误地高估或低估某条索引路径的成本，进而选出不理想的执行计划。

所以，理解优化器时要注意一点：它做的不是精确计算，而是基于统计信息的成本估算。大多数情况下估算足够好，但并不保证永远最优。

9.6 如何观察优化器的选择结果

优化器的判断过程虽然发生在内部，但它最终的选择结果会反映在执行计划中。例如：

• possible_keys 表示理论上可选的索引。
• key 表示优化器最终决定使用的索引。
• rows 表示优化器预估需要扫描的行数。
• Extra 则会透露是否发生了 Using filesort、Using temporary、Using index condition 等额外操作。

也就是说，执行计划并不是单纯告诉你"走没走索引"，而是在展示优化器做完成本比较后的结果。下一节就可以顺着这个问题继续展开：当我们拿到 EXPLAIN 输出时，应该如何解读这些字段，判断优化器的选择是否合理。

10. 索引失效的底层原因

10.1 "索引失效"的本质是什么

在讨论具体场景之前，有必要先厘清一个认知：所谓"索引失效"，大多数时候并不是索引结构本身出了问题，而是以下两类情况之一：

0. B+Tree 的有序性无法被利用：查询条件导致存储引擎无法沿着索引树做范围缩小，只能退化为逐条扫描。
1. 成本估算不划算：索引路径存在，但优化器判断走这条路径的总代价比全表扫描还高，主动放弃。

这两种情况在执行计划上的表现可能相同（都不走索引），但根本原因不同。理解透这两类原因，才能在遇到新场景时自己推断，而不是靠死记具体规则。

前面第7节已经解释了 B+Tree 的排序规则，以及为什么有序性是索引定位能力的根基。这一节的所有场景，都可以回到这个根基来解释。

10.2 破坏有序性：这类场景无法利用索引结构

场景一：联合索引跳过前导列

联合索引 (a, b, c) 的叶子页按先 a、后 b、再 c 的顺序排列。如果查询条件跳过 a 直接使用 b，例如：

SELECT * FROM user WHERE b = 2 AND c = 3;

在整棵索引树的全局视角下，b 的值并不是有序排列的。B+Tree 的查找依赖有序性从根节点出发逐层缩小范围，对于全局无序的列，树结构无法提供定位能力，因此这条 SQL 无法利用该联合索引。

场景二：范围条件截断后续列

范围条件不是不能走索引，但它会中断后续列对索引的利用。例如：

SELECT * FROM user WHERE a < 10 AND b = 2;

a < 10 可以利用索引做范围扫描，但扫描到的记录中 a 的值各不相同，而 b 只在 a 相同的记录内才是有序的。在 a 取值不固定的范围结果中，b 的值是无序的，因此 b = 2 无法继续利用索引定位，只能在扫描过程中逐条过滤（借助索引下推可以减少回表，但不改变索引定位能力的边界）。

这也是为什么联合索引设计通常建议将等值条件列放在前面、范围条件列放在后面——等值条件不会破坏后续列的局部有序性，而范围条件会。

场景三：对索引列施加函数或表达式

B+Tree 中存储的是列的原始值，索引按照原始值排序。如果查询条件对索引列做了函数变换或计算，例如：

SELECT * FROM user WHERE LENGTH(name) = 5;
SELECT * FROM user WHERE create_time + INTERVAL 1 DAY > NOW();

存储引擎无法直接用 LENGTH(name) = 5 这个条件在原始值有序的索引树上做范围缩小，因为函数的结果和原始值的顺序没有直接对应关系。此时只能对每条索引记录逐一计算函数值，索引树的有序性完全失去意义。

场景四：LIKE '%xx' 通配符在前

LIKE 'xqc%' 可以利用索引，因为它等价于查找所有以 xqc 开头的字符串，在按字符顺序排列的索引中可以找到一个连续范围。

但 LIKE '%xqc' 则不同。后缀匹配意味着符合条件的字符串在索引中并不连续，存储引擎无法从根节点出发确定一个有意义的起点，只能退化为全索引扫描或全表扫描。

场景五：隐式类型转换

这个场景比较隐蔽，但本质上和"对索引列施加函数"是同一类问题。如果索引列的数据类型与查询条件中的字面量类型不一致，MySQL 会在执行时做隐式转换。

例如，phone 列定义为 VARCHAR，但查询条件写成了整数：

SELECT * FROM user WHERE phone = 13900001234;

MySQL 处理时，相当于对 phone 列的每个值调用类型转换函数后再做比较，效果等同于 WHERE CAST(phone AS UNSIGNED) = 13900001234。索引列被套上了隐式函数，有序性失效。

反过来，如果整数列用字符串查询，例如 WHERE id = '42'，MySQL 会把字符串 '42' 转成整数 42，不需要对索引列做变换，所以仍然可以走索引。

10.3 成本不划算：这类场景索引存在但优化器放弃

上面的场景是索引结构本身无法被利用。但还有另一类情况：索引结构完全可以用，只是优化器估算后认为代价不划算，主动选择了其他路径。

场景六：字段选择性太差

如果一个字段的取值高度集中，比如 status 字段只有 0 和 1 两个值，而查询 WHERE status = 1 可能命中表中 80% 的记录。此时走二级索引意味着：扫描大量索引记录，再对每条记录回表取完整行。和这种代价相比，直接顺序扫描整张聚簇索引树可能更便宜，因此优化器可能主动放弃该索引。

场景七：OR 两侧条件不对称

SELECT * FROM user WHERE a = 1 OR b = 2;

如果 a 有索引但 b 没有，那么 b = 2 的条件必须全表扫描才能完成。既然 b 已经需要全表扫描，优化器就可能判断：在全表扫描过程中顺带过滤 a = 1，比"走索引取 a 的记录 + 全表扫描取 b 的记录再合并"更便宜。因此整条 SQL 可能最终不走任何索引。

即使 a 和 b 都有索引，也不一定会走索引。MySQL 此时可能尝试索引合并（Index Merge），即分别用两个索引扫描再对结果取并集。但当两个结果集都比较大时，合并操作本身的内存和 CPU 成本可能很高，优化器同样可能放弃，转而选择全表扫描。

10.4 小结：一个判断框架

遇到"索引是否生效"的问题时，可以按两步判断：

第一步，问自己：这个查询条件能利用 B+Tree 的有序性吗？

• 有没有跳过联合索引的前导列？
• 是否对索引列做了函数、计算或隐式转换？
• 范围条件之后还有其他列需要利用吗？
• LIKE 的通配符是否在最前面？

如果以上任意一条成立，索引的定位能力就会受到影响，甚至完全失效。

第二步，问自己：即使能利用，成本是否划算？

• 索引命中的记录比例是否过高？
• 回表的次数是否过多？
• OR 两侧条件是否存在不对称？

如果成本不划算，优化器可能主动选择全表扫描。这不是索引出了问题，而是优化器的合理判断。

11. 从执行计划看索引是否真的工作了

前面几节讨论了索引为什么会失效、优化器是如何做成本判断的。但这些分析最终要落地，还需要一个工具：执行计划（EXPLAIN）。

EXPLAIN 不只是"查一下有没有走索引"的确认工具，它展示的是优化器做完所有成本比较后的最终决策。理解执行计划，本质上是在理解优化器"为什么这样选"，而不只是看"选了什么"。

11.1 如何使用 EXPLAIN

使用方式很简单，在 SQL 前面加上 EXPLAIN 关键字即可：

EXPLAIN SELECT * FROM user WHERE name = 'xqc';

MySQL 会返回一行（或多行，针对多表 JOIN）描述执行路径的记录，其中有几个字段最值得关注。

11.2 关键字段逐个解读

type：访问方式，最能直观反映代价

type 描述的是存储引擎访问数据的方式，可以理解为"这条查询大概要扫多少东西"。常见取值从最优到最差依次是：

type 值	含义
system	表中只有一行记录，常见于系统表
const	通过主键或唯一索引做等值查询，最多匹配一条记录，速度最快
eq_ref	多表 JOIN 时，驱动表的每一行在被驱动表中只匹配一行（主键或唯一索引）
ref	通过非唯一索引做等值查询，可能返回多行
range	通过索引做范围扫描，如 BETWEEN、>、<、IN
index	扫描整棵索引树（不是全表，但代价也不低）
ALL	全表扫描，性能最差

实际排查时，const 和 ref 通常是期望的状态；range 说明走了索引但有范围扫描；ALL 或 index 出现时就需要关注了。

以 const 为例，这条查询会在执行计划中体现为 type = const：

-- id 是主键
EXPLAIN SELECT * FROM user WHERE id = 42;

而这条查询，如果 name 列上只有普通二级索引，则会体现为 type = ref：

EXPLAIN SELECT * FROM user WHERE name = 'xqc';

key 与 possible_keys：优化器选了什么，又排除了什么

possible_keys 列出的是优化器认为理论上可以使用的索引候选集，key 则是最终决定使用的那一个。

注意两者的差别非常重要：possible_keys 不为 NULL 并不代表索引一定会被使用。如第 9 节所述，优化器可能评估后认为某条索引的总代价不如全表扫描，最终在 key 里填 NULL。因此，光看 possible_keys 不够，要结合 key 和 type 一起判断。

key_len：联合索引实际用到了哪一段

这个字段在排查联合索引的问题时非常有用。它表示优化器实际使用了索引的多少字节，通过计算可以反推出联合索引中哪些列被真正利用了。

举个例子，假设联合索引 (a, b, c) 中，a 是 INT（4 字节），b 是 INT（4 字节），c 是 VARCHAR(20)（可变长）。

EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 2;

如果 key_len = 8，说明只用到了 a 和 b；如果 key_len = 4，则说明只用到了 a。这样就能直接判断最左匹配截止到了哪一列，而不是只看条件里写了什么。

rows：优化器预估扫描量，不是精确值

rows 是优化器基于统计信息估算出来的扫描行数，用来比较不同执行路径的成本。要注意两点：

• 它是估算值，不是真实执行时扫描的行数。统计信息不准时，rows 可能和实际相差很大。
• 联合索引路径下的 rows 已经考虑了过滤效果，所以不能用来判断"查了多少条"，而应该用来判断"这个执行路径大概要读多少数据"。

如果 rows 接近表的总行数，但 type 不是 ALL，就需要进一步看 Extra 字段——可能走了索引但回表次数极多，实际成本并不低。

Extra：揭示执行细节的关键字段

Extra 字段里的信息往往比 type 和 key 更能说明执行路径的质量。几个最常见的取值：

• Using index：覆盖索引命中，查询在二级索引层就结束了，没有回表。这是二级索引场景下最理想的状态。
• Using where：存储引擎返回记录后，Server 层还需要再做一次过滤。通常意味着索引只承担了部分过滤工作。
• Using index condition：开启了索引下推（ICP），过滤条件被下推到存储引擎层处理，减少了回表次数。
• Using filesort：排序操作无法利用索引的有序性，需要额外执行内存或磁盘排序。当结果集较大时，这是明显的性能警告。
• Using temporary：使用了临时表，常见于 GROUP BY、DISTINCT、或某些复杂排序场景。代价通常比 filesort 更高。

以第 7 节讨论的排序场景为例，可以用 Extra 直接验证：

-- 联合索引 (a, b)，排序方向与索引一致
EXPLAIN SELECT * FROM t WHERE a = 1 ORDER BY b;
-- Extra 中不出现 Using filesort，说明利用了索引排序
​
EXPLAIN SELECT * FROM t WHERE a = 1 ORDER BY b DESC, c ASC;
-- 若排序方向不一致，Extra 中会出现 Using filesort

11.3 几个典型的执行计划模式

把上面几个字段结合起来看，可以识别出一些常见的执行路径模式。

模式一：主键等值，最优路径

EXPLAIN SELECT * FROM user WHERE id = 42;

预期：type = const，key = PRIMARY，rows = 1，Extra 为空或 NULL。这是最理想的状态，一次页读取就能拿到结果。

模式二：二级索引 + 回表

EXPLAIN SELECT * FROM user WHERE name = 'xqc';

预期：type = ref，key = idx_name，Extra = Using where（如果还有其他 Server 层过滤）。这条路径需要先走二级索引，再回聚簇索引取完整行，如第 8 节所述。

模式三：覆盖索引，无回表

-- 假设联合索引 idx_name_age(name, age)
EXPLAIN SELECT name, age FROM user WHERE name = 'xqc';

预期：type = ref，key = idx_name_age，Extra = Using index。查询列都在索引中，无需回表。

模式四：联合索引部分利用

-- 联合索引 idx_a_b_c(a, b, c)
EXPLAIN SELECT * FROM t WHERE a = 1 AND c = 3;

预期：type = ref，key = idx_a_b_c，但 key_len 只有 a 列的字节数，说明 c 没有参与索引定位。Extra 可能出现 Using index condition（索引下推生效）。

模式五：索引存在但未使用

EXPLAIN SELECT * FROM user WHERE status = 1;
-- status 字段区分度很低，大部分记录都是 status=1

预期：type = ALL，key = NULL，possible_keys 可能列出了索引，但优化器判断全表扫描成本更低，主动放弃。

11.4 EXPLAIN ANALYZE：看估算和实际的差距

从 MySQL 8.0.18 开始，可以使用 EXPLAIN ANALYZE 获取实际执行信息，而不只是估算值：

EXPLAIN ANALYZE SELECT * FROM user WHERE name = 'xqc';

它会真正执行这条 SQL，并在执行计划中附上每一步的实际行数、实际耗时和循环次数。这在排查"执行计划看上去没问题，但实际很慢"的场景下非常有价值——有时候问题正出在统计信息偏差导致 rows 估算严重失准，而 EXPLAIN ANALYZE 会把真实值直接显示出来。

不过要注意：EXPLAIN ANALYZE 会真实执行查询，对于耗时较长或写入操作，使用时需要谨慎。

11.5 小结

执行计划是第 9 节和第 10 节分析的"落地工具"。在解读时，建议按以下顺序来看：

0. 先看 type，判断访问方式是否合理。
1. 再看 key，确认优化器选择了哪个索引（或者为什么没选）。
2. 用 key_len 验证联合索引的实际利用范围。
3. 用 rows 判断预估扫描量是否在合理范围内。
4. 最后看 Extra，确认有没有回表、额外排序或临时表。

如果执行计划和预期不符，不要急着用 FORCE INDEX 强制走某个索引。更值得做的是先回到第 9、10 节的分析框架，想清楚优化器放弃这条索引的原因——是有序性被破坏了，还是成本模型做出了不同的判断。大多数情况下，问题出在 SQL 写法、索引设计或统计信息上，而不是优化器出了故障。

12. 索引不是银弹：它的收益、代价和边界

前面十几节的讨论，核心都在解释索引如何帮助查询降低成本。但索引本身不是免费的。在真实业务中，是否加索引、加几个索引、索引覆盖到什么程度，都需要在收益和代价之间做权衡。这一节把索引的代价和边界做一次完整梳理。

12.1 索引的收益回顾

从前面各节的分析中，可以把索引的核心收益归纳为三点：

• 降低查询的页访问次数：通过 B+Tree 的层级结构，将全表扫描的线性查找变成对数级别的定位，直接减少磁盘 IO。
• 支持范围查询和排序：叶子节点的有序性和双向链表结构，使得 BETWEEN、ORDER BY、GROUP BY 等操作可以直接利用物理顺序，避免额外排序。
• 覆盖索引消除回表：当查询列完全包含在索引中时，查询在索引层即可结束，无需访问聚簇索引。

这些收益在读多写少、查询模式稳定的场景下非常显著。但收益越大，越容易忽略另一面的成本。

12.2 索引的代价

写入放大：每次 DML 都要维护所有相关索引

索引不是静态结构。每一次 INSERT、UPDATE、DELETE，不仅要修改聚簇索引中的行记录，还需要同步维护所有涉及到的二级索引。

具体来说：

• INSERT：需要在聚簇索引中写入新行，同时在每棵相关的二级索引中插入对应的索引记录。如果插入位置的目标页已满，还可能触发页分裂。
• DELETE：需要在聚簇索引中标记删除，同时在所有相关二级索引中标记对应条目删除。
• UPDATE：如果更新的列是某个索引的组成列，效果等同于"在该索引中删除旧条目 + 插入新条目"。

因此，一张表上的索引越多，每次写入操作的实际代价就越高。对于写入密集的业务表，索引数量需要严格控制。

InnoDB 通过 Change Buffer 机制对二级索引的写入做了优化：当目标索引页不在 Buffer Pool 中时，不会立即从磁盘读取该页，而是先把变更缓存起来，等到该页后续被读取时再合并。这在一定程度上缓解了随机写入的 IO 压力，但不改变"索引越多、写入维护成本越高"的基本事实。

存储空间占用

每棵二级索引都是一棵独立的 B+Tree，占用独立的磁盘空间。索引列越宽、索引数量越多，总存储开销越大。同时，索引页也会占用 Buffer Pool 的内存空间，挤压数据页的缓存容量。在内存有限的环境下，过多的索引反而可能导致热点数据页被频繁换出，影响整体查询性能。

DDL 操作的代价

在已有大量数据的表上新建索引，需要扫描全表数据并构建完整的 B+Tree 结构。对于千万级甚至更大的表，这个过程可能持续数分钟到数十分钟。虽然 MySQL 5.6 之后支持 Online DDL，允许在建索引期间继续执行 DML，但仍然会占用大量 IO 和 CPU 资源，在高峰期执行可能影响线上服务的响应延迟。

因此，索引的添加和删除在生产环境中通常需要安排在低峰期，或者通过 pt-online-schema-change、gh-ost 等工具来控制影响范围。

优化器决策复杂度

索引越多，优化器在生成执行计划时需要评估的候选路径就越多。虽然这通常不会成为瓶颈，但在极端情况下（比如一张表有十几个索引、SQL 涉及多表 JOIN），优化器的计划生成阶段可能消耗更多时间，甚至因为统计信息偏差选错索引。

12.3 索引的边界：它解决不了什么

除了代价之外，还需要认识到索引在某些场景下根本无法提供帮助。

数据量极小的表：如果整张表只有几十行或几百行，全表扫描本身只需要一两次页读取，索引带来的定位收益几乎为零，反而增加了维护成本。

无法匹配查询模式的索引：如果业务查询模式频繁变化，或者 SQL 条件高度动态化（比如用户自由组合的多维筛选），很难设计出一组稳定命中的索引。此时堆积大量索引往往适得其反。

写入瓶颈场景：当系统瓶颈在写入而不是读取时，增加索引只会让瓶颈更严重。这类场景的优化方向通常是减少索引、批量写入、异步处理，而不是继续加索引。

数据分析类查询：对于需要扫描大量数据做聚合计算的分析场景（比如报表、全量统计），索引的逐行定位能力帮助有限。这类需求更适合列式存储、物化视图或独立的分析引擎来承接。

12.4 小结

索引的本质是用写入成本和存储成本，换取读取效率的提升。这个交换在读多写少、查询模式稳定的场景下收益显著，但并非没有上限。当索引数量过多、写入压力过大、或者查询模式无法被索引覆盖时，索引带来的代价可能超过收益。

因此，索引设计的最终原则不是"能加就加"，而是围绕真实、稳定、高频的查询路径做针对性设计，同时持续关注写入代价和存储开销的平衡。

13. 索引设计方法论：什么时候该建，建成什么样

• 优先服务高频查询，而不是所有查询。
• 优先服务过滤、排序、分页、关联最重的 SQL。
• 联合索引字段顺序通常考虑：过滤能力、等值优先、排序兼容、回表成本。
• 尽量复用已有联合索引，避免重复索引。
• 控制索引数量，避免写放大和维护成本过高。
• 定期清理低命中、重复、冗余索引。