Mysql索引篇：InnoDB索引（B+树结构、聚簇索引）

type

Post

status

Published

date

Jul 7, 2024

slug

summary

Mysql索引篇：InnoDB索引（B+树结构、聚簇索引）

1、概述

1.1 索引是什么

索引是一个单独的、存储在磁盘上的数据库结构，它类似于书籍的目录，能够帮助数据库系统快速定位和访问表中的特定数据，大大提高 MySQL 的检索速度。

1.2 索引分类

1.2.1 按数据结构分类

B+ tree索引：通过 B+ 树实现的，是有序排列存储，所以在排序和范围查找方面都比较有优势，是最常使用的索引类型。

Hash 索引：Hash 索引适合 key-value 键值对查询，无论表数据多大，查询数据的复杂度都是O(1)，且直接通过 Hash 索引查询的性能比其它索引都要高。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度很慢。所以，哈希表结构适用于只有等值查询的场景。目前只有 Memory 引擎支持。

Full-text 索引（全文索引）：基于倒排索引（invertd index）技术，在 CHAR、VARCHAR、TEXT 类型上建立全文索引，加快字符搜索的速度。

R-Tree索引（空间索引）：存储地理空间的位置并进行一定计算，仅支持 geometry 数据类型，比较少用。

各存储引擎对索引的支持情况如下：

ㅤ	InnoDB	MyISAM	Memory
B+ tree索引	√	√	√
Hash索引	×	×	√
Full-text索引	√（MySQL5.6+）	√	×
R-Tree索引	√（MySQL5.7+）	√	×

1.2.2 按物理存储分类

聚簇索引：叶子节点存放的是实际数据。

非聚簇索引：也称为二级索引，叶子节点存储主键值，需要通过主键值去聚簇索引查找实际数据（这个操作称为回表）。

1.2.3 按逻辑功能分类

主键索引：特殊的唯一索引，不允许NULL值

唯一索引：索引列值必须唯一，允许NULL值

普通索引：基本的索引类型，无特殊限制

复合索引：也称为组合索引或联合索引，多列组合的索引，遵循"最左前缀原则”。

前缀索引：对列的前面部分建立索引，适用于长文本字段（如 TEXT、VARCHAR 等）

InnoDB 是 MySQL 最常用的存储引擎，下面将从以下几方面分析 InnoDB 的索引：

从数据结构的角度：InnoDB 使用 B+ 树作为索引数据结构：非叶子节点只存储索引，所有数据都存储在叶子节点，叶子节点通过指针连接形成链表，适合范围查询。

从物理存储的角度：InnoDB 索引的物理数据结构分成聚簇索引和非聚簇索引（二级索引），都采用 B+ 树的数据结构，它们区别就在于叶子节点存放的是什么数据：

聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点。
二级索引的叶子节点存放的是主键值，而不是实际数据。

2、B+ 树索引结构

我们先了解一下常见的数据结构，就明白为什么 InnoDB 选择 B+ 树作为索引结构。

二分查找

二分查找树

自平衡二叉树（AVL树）

B 树

B+ 树

2.1 二分查找

索引数据最好能按顺序排列，这样可以使用「二分查找法」高效定位数据。

假设我们现在用数组来存储索引，比如下面有一个排序的数组，如果要从中找出数字 3，最简单办法就是从头依次遍历查询，这种方法的时间复杂度是 O(n)，查询效率并不高。因为该数组是有序的，所以我们可以采用二分查找法，比如下面这张采用二分法的查询过程图：

可以看到，二分查找法每次都把查询的范围减半，这样时间复杂度就降到了 O(logn)，但是每次查找都需要不断计算中间位置。

2.2 二分查找树

用数组来实现线性排序的数据虽然简单好用，但是插入新元素的时候性能太低。

因为插入一个元素，需要将这个元素之后的所有元素后移一位，如果这个操作发生在磁盘中呢？这必然是灾难性的。因为磁盘的速度比内存慢几十万倍，所以我们不能用一种线性结构将磁盘排序。

其次，有序的数组在使用二分查找的时候，每次查找都要不断计算中间的位置。

那我们能不能设计一个非线形且天然适合二分查找的数据结构呢？

有的，请看下图这个神奇的操作，找到所有二分查找中用到的所有中间节点，把他们用指针连起来，并将最中间的节点作为根节点。

怎么样？是不是变成了二叉树，不过它不是普通的二叉树，它是一个二叉查找树。

二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点，这样我们在查询数据时，不需要计算中间节点的位置了，只需将查找的数据与节点的数据进行比较。

假设，我们查找索引值为 key 的节点：

如果 key 大于根节点，则在右子树中进行查找；

如果 key 小于根节点，则在左子树中进行查找；

如果 key 等于根节点，也就是找到了这个节点，返回根节点即可。

二叉查找树查找某个节点的动图演示如下，比如要查找节点 3 ：

另外，二叉查找树解决了插入新节点的问题，因为二叉查找树是一个跳跃结构，不必连续排列。这样在插入的时候，新节点可以放在任何位置，不会像线性结构那样插入一个元素，所有元素都需要向后排列。

下面是二叉查找树插入某个节点的动图演示：

因此，二叉查找树解决了连续结构插入新元素开销很大的问题，同时又保持着天然的二分结构。

那是不是二叉查找树就可以作为索引的数据结构了呢？

不行不行，二叉查找树存在一个极端情况，会导致它变成一个瘸子！

当每次插入的元素都是二叉查找树中最大的元素，二叉查找树就会退化成了一条链表，查找数据的时间复杂度变成了 O(n)，如下动图演示：

由于树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作（假设一个节点的大小「小于」操作系统的最小读写单位块的大小），也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。

二叉查找树由于存在退化成链表的可能性，会使得查询操作的时间复杂度从 O(logn) 升为 O(n)。

而且会随着插入的元素越多，树的高度也变高，意味着需要磁盘 IO 操作的次数就越多，这样导致查询性能严重下降，再加上不能范围查询，所以不适合作为数据库的索引结构。

2.3 自平衡二叉树（AVL树）

为了解决二叉查找树会在极端情况下退化成链表的问题，后面就有人提出平衡二叉查找树（AVL 树）。

主要是在二叉查找树的基础上增加了一些条件约束：每个节点的左子树和右子树的高度差不能超过 1。也就是说节点的左子树和右子树仍然为平衡二叉树，这样查询操作的时间复杂度就会一直维持在 O(logn) 。

下图是每次插入的元素都是平衡二叉查找树中最大的元素，可以看到，它会维持自平衡：

除了平衡二叉查找树，还有很多自平衡的二叉树，比如红黑树，它也是通过一些约束条件来达到自平衡，不过红黑树的约束条件比较复杂，不是本篇的重点重点，大家可以看《数据结构》相关的书籍来了解红黑树的约束条件。

下面是红黑树插入节点的过程，这左旋右旋的操作，就是为了自平衡。

不管平衡二叉查找树还是红黑树，都会随着插入的元素增多，而导致树的高度变高，这就意味着磁盘 I/O 操作次数多，会影响整体数据查询的效率。

比如，下面这个平衡二叉查找树的高度为 5，那么在访问最底部的节点时，就需要磁盘 5 次 I/O 操作。

根本原因是因为它们都是二叉树，也就是每个节点只能保存 2 个子节点，如果我们把二叉树改成 M 叉树（M>2）呢？

比如，当 M=3 时，在同样的节点个数情况下，三叉树比二叉树的树高要矮。

因此，当树的节点越多的时候，并且树的分叉数 M 越大的时候，M 叉树的高度会远小于二叉树的高度。

2.4 B 树

自平衡二叉树虽然能保持查询操作的时间复杂度在 O(logn)，但是因为它本质上是一个二叉树，每个节点只能有 2 个子节点，那么当节点个数越多的时候，树的高度也会相应变高，这样就会增加磁盘的 I/O 次数，从而影响数据查询的效率。

为了解决降低树的高度的问题，后面就出来了 B 树，它不再限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度。

B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。

假设 M = 3，那么就是一棵 3 阶的 B 树，特点就是每个节点最多有 2 个（M-1个）数据和最多有 3 个（M个）子节点，超过这些要求的话，就会分裂节点，比如下面的的动图：

我们来看看一棵 3 阶的 B 树的查询过程是怎样的？

假设我们在上图一棵 3 阶的 B 树中要查找的索引值是 9 的记录那么步骤可以分为以下几步：

与根节点的索引(4，8）进行比较，9 大于 8，那么往右边的子节点走；

然后该子节点的索引为（10，12），因为 9 小于 10，所以会往该节点的左边子节点走；

走到索引为9的节点，然后我们找到了索引值 9 的节点。

可以看到，一棵 3 阶的 B 树在查询叶子节点中的数据时，由于树的高度是 3 ，所以在查询过程中会发生 3 次磁盘 I/O 操作。

而如果同样的节点数量在平衡二叉树的场景下，树的高度就会很高，意味着磁盘 I/O 操作会更多。所以，B 树在数据查询中比平衡二叉树效率要高。

但是 B 树的每个节点都包含数据（索引+记录），而用户的记录数据的大小很有可能远远超过了索引数据，这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」。

而且，在我们查询位于底层的某个节点（比如 A 记录）过程中，「非 A 记录节点」里的记录数据会从磁盘加载到内存，但是这些记录数据是没用的，我们只是想读取这些节点的索引数据来做比较查询，而「非 A 记录节点」里的记录数据对我们是没用的，这样不仅增多磁盘 I/O 操作次数，也占用内存资源。

另外，如果使用 B 树来做范围查询的话，需要使用中序遍历，这会涉及多个节点的磁盘 I/O 问题，从而导致整体速度下降。

2.5 B+ 树

B+ 树就是对 B 树做了一个升级，MySQL 中索引的数据结构就是采用了 B+ 树，B+ 树结构如下图：

B+ 树与 B 树差异的点，主要是以下这几点：

叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引。

所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表。

非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。

非叶子节点中有多少个子节点，就有多少个索引。

下面通过三个方面，比较下 B+ 树和 B 树的性能区别。

2.6.1 单点查询效率

B 树进行单个索引查询时，最快可以在 O(1) 的时间代价内就查到，而从平均时间代价来看，会比 B+ 树稍快一些。

但是 B 树的查询波动会比较大，因为每个节点即存索引又存记录，所以有时候访问到了非叶子节点就可以找到索引，而有时需要访问到叶子节点才能找到索引。

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

2.6.2 插入和删除效率

B+ 树有大量的冗余节点，这样使得删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点，这样删除非常快，

比如下面这个动图是删除 B+ 树 0004 节点的过程，因为非叶子节点有 0004 的冗余节点，所以在删除的时候，树形结构变化很小：

注意，：B+ 树对于非叶子节点的子节点和索引的个数，定义方式可能会有不同，有的是说非叶子节点的子节点的个数为 M 阶，而索引的个数为 M-1（这个是维基百科里的定义），因此我本文关于 B+ 树的动图都是基于这个。但是我在前面介绍 B+ 树与 B+ 树的差异时，说的是「非叶子节点中有多少个子节点，就有多少个索引」，主要是 MySQL 用到的 B+ 树就是这个特性。

下面这个动图是删除 B 树 0008 节点的过程，可能会导致树的复杂变化：

甚至，B+ 树在删除根节点的时候，由于存在冗余的节点，所以不会发生复杂的树的变形，比如下面这个动图是删除 B+ 树根节点的过程：

B 树则不同，B 树没有冗余节点，删除节点的时候非常复杂，比如删除根节点中的数据，可能涉及复杂的树的变形，比如下面这个动图是删除 B 树根节点的过程：

B+ 树的插入也是一样，有冗余节点，插入可能存在节点的分裂（如果节点饱和），但是最多只涉及树的一条路径。而且 B+ 树会自动平衡，不需要像更多复杂的算法，类似红黑树的旋转操作等。

因此，B+ 树的插入和删除效率更高。

2.6.3 范围查询效率

B+ 树的叶子节点形成双向链表连接，即，即每个叶子节点都包含指向前一个叶子节点和后一个叶子节点的指针。这种设计可以实现高效的范围查找，无论向前遍历还是向后遍历。

比如说我们想知道 12 月 1 日和 12 月 12 日之间的订单，这个时候可以先查找到 12 月 1 日所在的叶子节点，然后利用链表向右遍历，直到找到 12 月12 日的节点，这样就不需要从根节点查询了，进一步节省查询需要的时间。

而 B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

因此，存在大量范围检索的场景，适合使用 B+树，比如数据库。而对于大量的单个索引查询的场景，可以考虑 B 树，比如 nosql 的 MongoDB。

2.7 Innodb 的 B+ 树结构

Innodb 索引采用 B+ 树结构，并进行部分优化：

B+ 树的叶子节点之间是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历。

B+ 树的叶子节点存放的是数据页而不是单独的用户记录（非叶子节点存的不是数据页），数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB。

Innodb 根据索引类型不同，分为聚集和二级索引。他们区别在于

聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点

二级索引的叶子节点存放的是主键值，而不是实际数据。

下图就是 InnoDB 的二级索引。

因为表的数据都是存放在聚集索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚集索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个，而二级索引可以创建多个。

2.8 总结

二分查找树虽然是一个天然的二分结构，能很好的利用二分查找快速定位数据，但是它存在一种极端的情况，每当插入的元素都是树内最大的元素，就会导致二分查找树退化成一个链表，此时查询复杂度就会从 O(logn)降低为 O(n)。

为了解决二分查找树退化成链表的问题，就出现了自平衡二叉树，保证了查询操作的时间复杂度就会一直维持在 O(logn) 。但是它本质上还是一个二叉树，每个节点只能有 2 个子节点，随着元素的增多，树的高度会越来越高。而树的高度决定于磁盘 I/O 操作的次数，因为树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作，也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。

B 树和 B+ 都是通过多叉树的方式，会将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。

B +树相比起其他数据结构的优点：

哈希索引：不支持范围查询，不适合机械硬盘特性
B树：非叶子节点也存储数据，导致树高更高，I/O更多
红黑树：树高更高，且不适合磁盘存储

存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因是：

高效的磁盘 I/O：B+ 树的叶子字节存放实际的记录数据，非叶子节点仅存放索引，因此非叶子节点占用的空间很小，一个数据页可以存放更多的非叶子结点，意味着一次 I/0 （读取一个数据页）可以读取更多的索引。B+树通常 3-4 层就能存储大量数据，意味着最多 3-4 次磁盘 I/O 就能查询到所需的数据，可以减少磁盘 I/O 次数。
稳定的查询效率：B+树所有查询都要走到叶子节点，查询时间复杂度稳定为 O(log n)。
高效的插入删除效率：B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化。
高效的范围查询：B+ 树叶子节点之间连接起来形成双向链表，有利于范围查询。

2、聚簇索引和非聚簇索引

InnoDB 的索引从物理结构上可分为聚簇索引和非聚簇索引，两者的非叶子节点都存储索引，但叶子节点存放的数据不同：

聚簇索引：叶子节点存放真实的用户数据，InnoDB 存储引擎一定会为表创建一个聚簇索引（一般情况下使用主键创建聚簇索引），且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。

非聚簇索引：也称为二级索引，叶子节点存放数据行的主键值，需要使用主键值去聚簇索引再查一遍，才能获取到完整的用户数据，这个过程也称为回表操作。一个表可以有多个非聚簇索引。

2.1 聚簇索引

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键。

如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键。

在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键。

例如下面是一个聚簇索引的数据结构：

聚簇索引的特点：

B+ 树的叶子节点保存了用户的完整记录。

非叶子结点（索引页）和叶子结点（数据页）都使用 InnoDB 中的页结构。

按照主键的大小对用户记录和数据页进行排序，记录用单向链表连接，数据页使用双向链表连接。

聚簇索引的查找过程：例如要查找主键 ID 为 14 的记录：

从页 99 中，快速检索到对应的目录项数据页 124；

在页 124 中，快速检索到对应的数据页 27；

在页 27 中，快速检索到主键为 14 的记录。

2.2 非聚簇索引（二级索引）

InnoDB 可以创建多个二级索引（InnoDB 默认最多允许 64 个），二级索引一般适用于以下场景：

经常用于 WHERE 子句中的列

用于 JOIN 操作的列

用于 ORDER BY 或 GROUP BY 的列

需要覆盖查询时（索引包含所有查询字段）

例如下面是对name 字段单个列建立的二级索引。

二级索引的特点：

叶子节点存放的不再是完整的用户记录，而是只记录name列和主键值。

数据页中存放的用户记录和目录项记录由原本的按照主键排序变为按照name列排序。

目录项记录除了存储索引列（name）和页号之外，同时还存储了主键值。

2.3 MyISAM的索引方案

MyISAM 不支持聚簇索引，这是 MyISAM 和 InnoDB 存储引擎之间的一个重要区别。

MyISAM 使用 B-tree 索引，但数据文件和索引文件是分开存储的

.MYD文件：MyISAM的数据文件，存放用户记录，也就是我们插入的表数据。

.MYI文件：MyISAM的索引文件，B+ 树结构，但叶子节点存储的是指向数据行的指针（物理位置），而不是数据本身。

即使定义了主键，MyISAM 的主键索引也不是聚簇索引，它与其他索引在结构上没有本质区别。

也就是说，不同于 InnoDB 的“索引即数据”的思想，MyISAM 存储引擎中的索引和数据是分开存储的。所以 MyISAM 天生不支持聚簇索引。

MyISAM 的数据查找过程：从索引文件.MYI中找到对应的索引键（建立索引的列的值）后，会到.MYD中找到对应的用户记录。以主键为例：

3、索引优化

索引优化的相关知识点：

联合索引：联合索引是指由多个列组成的索引，遵循最左匹配原则。

回表：从二级索引获取主键，然后再根据主键值从聚簇索引获得完整的数据，这个过程就叫作「回表」，也就是说要查两个 B+ 树才能查到数据。

覆盖索引：当一个索引包含了查询所需的所有字段时，这个索引就被称为「覆盖索引」。因为在二级索引就能查询到所有字段，不用再去聚簇索引查，即只需要查一个 B+ 树就能找到数据。

索引下推：使用索引查找数据时，将查询条件中索引相关的部分"下推"到存储引擎层进行过滤，将在 Server 层的过滤操作下推到存储引擎层，这就是「索引下推」。索引下推可以减少回表次数。

注意回表、覆盖索引、索引下推这些概念只适用于二级索引。聚簇索引本身就存储真实的数据，所以也不存在所谓的回表操作。

3.1 联合索引

联合索引是指由多个列组成的索引，也称为复合索引或多列索引。联合索引的作用：

减少索引数量：一个联合索引可以替代多个单列索引

覆盖索引优化：如果查询只涉及索引列，就可以发挥覆盖索引的特性，避免回表操作。

优化排序操作：对于 ORDER BY 多列的情况特别有效

假设我们为name列和phone列建立联合索引（注意字段顺序），自然也是创建一棵B+树，这棵B+树和之前又稍微有点不同：

叶子节点存放的是name列、phone列和主键值；

目录项记录除了存储索引列（name、phone）和页号之外，同时还存储了主键值；

数据页中存放的用户记录和目录项记录由原本的按照主键排序变为按照name列排序，如果name列相同，那就按照phone列排序；（如果phone列再一样呢？你现在明白为什么要存储主键值了吗？）

还是和二级索引一样，利用 B+ 树快速定位到数据页，然后页内快速定位到记录，找到记录中的主键id，再回表，如果找到多条符合条件的记录，就多回几次表。

最左匹配原则

联合索引遵循最左前缀原则（Leftmost Prefix Principle）。最左匹配原则是指使用联合索引时，查询条件的字段必须从索引的最左列开始依次向右匹配，不能跳过中间字段，才能使索引生效。

根本原因是联合索引在底层通过 B+ 树实现，索引中的字段按从左到右的顺序存储和排序。

例如有一张用户表：

创建一个联合索引：

这个索引的字段顺序是：

MySQL 可以使用以下索引组合：

也就是说，查询条件必须从 name 开始匹配。

什么情况下联合索引会失效：

跳过最左字段：例如以下语句不能使用这个联合索引，因为没有使用最左边的字段 name。

Like 查询不满足最左匹配原则：模糊查询中必须要满足最左匹配原则。比如我们要查询姓张的用户，我们的条件查询可以为 "where name like ‘张%’"，但是不能是 where name like '%张%'或者是 where name like '%张'，否则会导致索引失效。

范围查询：范围查询会导致联合索引的匹配中断。例如 WHERE name = '张三' AND age > 20 AND city = '北京'; 。这种情况下，city 列无法使用索引，因为 age 列使用了范围查询(>)，会中断索引的连续匹配。

注意以下情况联合索引仍然会生效：

查询条件顺序不等于索引顺序，但包含全部字段：例如 (name,phone) 两个字段的联合索引，我们使用 where name = '张三' and phone = '2'索引可以生效的，当我们是颠倒了他们的顺序使用where phone = '1' and name = '王五'，索引同样也是可以生效的。Mysql 查询优化器会判断纠正这条 sql 语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划，我们能尽量的利用到索引时的查询顺序效率最高，所以 Mysql 查询优化器会最终以这种顺序（where name = '张三' and phone = '2'）进行查询执行。

联合索引的设计原则

注意字段顺序：联合索引的列顺序非常重要。

区分度高的字段放前面：假设区分度由大到小为 b,a,c。那么我们就对 (b,a,c) 建立索引。在执行 sql 的时候，优化器会帮我们调整 where 后 a,b,c 的顺序，让我们用上索引。
等值查询的字段放前面：最左匹配原则遇到范围查询就停止匹配，所以建索引的时候把等值查询的字段放在前面，范围查询的字段放后面。

考虑排序和分组：如果查询中有排序或分组，相关列应该包含在索引中。

控制字段数量：不是列越多越好，通常不超过 5 列。

避免频繁更新：更新频繁的列不适合放在联合索引。

索引复用：当已经有了 (name,phone) 这个联合索引后，查询条件里面只有 name 的语句，因为可以支持最左前缀，一般就不需要单独在 name 上建立索引了。因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。但查询条件里面只有 password 的语句，是无法使用 (name,phone) 这个联合索引的，这时候你需要同时维护 (name,phone)、 (password) 这两个索引。

3.2 索引下推

索引下推（Index Condition Pushdown, ICP）是指使用联合索引时，将查询条件中索引相关的部分"下推"到存储引擎层进行过滤，目的是减少回表次数。

简单来说，就是能在存储引擎层判断的条件，就尽量在存储引擎层判断，不要把数据都拿回 Server 层再判断。

传统方式：

存储引擎通过索引查找定位符合条件的记录
将这些记录返回给Server层
服务器层再根据 WHERE 条件进行过滤

使用 ICP 后：

优化器分析 WHERE 条件，将可以下推的部分条件发送给存储引擎
存储引擎在使用索引查找时，同时应用这些下推的条件进行过滤
只有满足所有条件的记录才会被返回给 Server 层

假设有一张用户表。

有一个联合索引：

执行下面 SQL 语句。

上面我们说到满足最左前缀原则的时候，联合索引的最左匹配原则会一直向右匹配直到遇到范围查询( >、 <、 between、 like) 就会停止匹配。没有索引下推时，执行过程大概是：

使用联合索引 idx_name_age 找到所有 name LIKE '张%' 的记录；

对每一条满足 name LIKE '张%' 的索引记录进行回表；

回表拿到完整数据后，再判断 age = 10；

返回最终结果。

也就是说：

如果姓张的人很多，那么回表次数会很多。而且把我们联合索引的另一个字段 age 浪费了。

如果开启了索引下推，执行过程变成：

使用联合索引 idx_name_age 找到满足 name LIKE '张%' 的索引记录；

在索引中直接判断 age = 10；

只有满足 age = 10 的记录才回表；

返回最终结果。

索引下推的核心作用是：减少回表次数。它并不是让查询完全不回表，而是尽可能在索引层提前过滤。

索引下推的适用场景

复合索引查询：当查询 WHERE 条件包含多个列，且这些列构成复合索引时，例如索引是(a,b,c)，查询条件是 WHERE a=1 AND b>5 AND c LIKE 'x%' 。

部分索引列匹配的查询：当查询 WHERE 条件只使用了复合索引的前几列时，例如：索引是(a,b,c)，查询条件是 WHERE a=1 AND b>5。

覆盖索引查询：当查询 SELECT 的所有列都包含在复合索引中，这种索引也称为覆盖索引。覆盖索引默认使用索引下推。

注意索引下推的使用局限：

只能用于 range、 ref、 eq_ref、ref_or_null 访问方法。

只能用于 InnoDB 和 MyISAM存储引擎及其分区表。

对于 InnoDB 的表，索引下推只能用于二级索引，因为在聚簇索引中最下方的叶子节点保存了整行数据，所以使用索引下推并不会起到减少查询全行数据的效果。

引用了子查询的条件不能下推。

引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

Mysql 默认启用索引下推，我们也可以通过修改系统变量 optimizer_switch 的 index_condition_pushdown 标志来控制

如果判断是否使用了索引下推

可以通过 EXPLAIN 查看执行计划。例如

如果在 Extra 列中看到 Using index condition，说明使用了索引下推。

3.3 覆盖索引

覆盖索引（Covering Index）是指如果二级索引里面包含了查询所需的所有字段，不需要回表查询数据行，那么这个索引就称为覆盖索引。

换句话说：索引中已经包含了查询需要的全部数据。

还是这张表：

为了使用覆盖索引，需要创建一个包含所有查询列的联合索引。例如

执行下面语句：

这条 SQL 查询的字段是： name, age，查询条件字段是： name ，这些字段都在联合索引 idx_name_age(name, age) 中，因此 MySQL 可以直接从索引中返回结果，不需要回表，这就是覆盖索引。

覆盖索引的适用场景

覆盖索引适用于查找少量列的场景。

注意覆盖索引的使用局限：

索引列越多，维护成本越高（影响写入性能），需要权衡查询性能和存储空间。

如何判断是否使用了覆盖索引

可以使用 EXPLAIN。

如果 Extra 字段中出现：

通常表示使用了覆盖索引。

注意：

和：

不是一个意思。

覆盖索引和索引下推的区别

覆盖索引和索引下推都是数据库优化查询性能的技术，目的都是通过减少回表次数来提高查询性能。两者最大的区别是

索引下推：先在索引里过滤，减少回表。

覆盖索引：查询结果只从索引中就能拿到，完全不用回表。

假设索引为

使用索引下推

原因：

name 和 age 都在索引中；

name LIKE '张%' 可以走索引范围扫描；

age = 18 可以在索引层提前过滤；

但 SELECT * 需要查询完整数据，所以仍然需要回表。

可能出现：

使用覆盖索引

原因：

查询字段只有 name 和 age；

条件字段也是 name 和 age；

这些字段全部在索引 idx_name_age 中；

不需要回表。

可能出现：

可以这样记：

索引下推：减少回表。 覆盖索引：不需要回表。

两者做一个综合对比：

对比项	索引下推	覆盖索引
英文	Index Condition Pushdown	Covering Index
简称	ICP	无固定简称
主要作用	减少回表次数	避免回表
是否一定回表	可能回表	不需要回表
依赖条件	条件字段部分在索引中	查询条件 + 查询列全部在索引中
`EXPLAIN Extra`	`Using index condition`	`Using index`
优化位置	存储引擎层提前过滤	直接从索引返回结果
常见场景	联合索引中部分条件可用于过滤	查询字段被索引完全覆盖
MySQL版本	所有版本支持	5.6及以上版本