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

type

status

date

slug

summary

1、概述

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 树和 B+ 树等值查询原理基本一致，先从根节点查找，然后对比目标数据的范围，最后递归的进入子节点查找。

因为 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的索引方案

不同的存储引擎存放数据的方式不一样，产生的文件数量和格式也不一样，InnoDB文件包含2个，MEMORY文件包含1个，MyISAM文件包含3个。我们接下来关注的就是MyISAM中的文件。

.MYD文件，D代表Data，是MyISAM的数据文件，存放用户记录，也就是我们插入的表数据；

.MYI文件，I代表Index，是MyISAM的索引文件。一个索引就会有一棵B+树，所有的B+树都保存在这个文件当中。

也就是说，不同于InnoDB的“索引即数据”的思想，MyISAM存储引擎中的索引和数据是分开存储的。

MyISAM中的B+树长啥样子呢？其实样子和InnoDB差不多，区别就是MyISAM的B+树的叶子节点存储的是用户记录对应的磁盘地址，所以从索引文件.MYI中找到对应的索引键（建立索引的列的值）后，会到.MYD中找到对应的用户记录。以主键为例我再再再画个图：

3、索引优化

索引优化的相关知识点：

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

回表：如果某个查询语句使用了二级索引，但是查询的数据不是主键值，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作「回表」，也就是说要查两个 B+ 树才能查到数据。

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

索引下推：将 WHERE 条件中索引相关的部分"下推"到存储引擎层进行过滤，可以在存储引擎层提前过滤数据，减少回表次数。

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

3.1 联合索引（复合索引）

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

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

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

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

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

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

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

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

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

最左匹配原则

联合索引遵循最左前缀原则（Leftmost Prefix Principle）。最左匹配原则是指的是联合索引中，索引按照定义的列顺序存储和排序，查询时只能从索引的最左列开始使用，不能跳过索引中的列。

例如，索引 (A,B,C)：

可以用于 A, A,B, A,B,C 的查询

不能用于 B, C, B,C 的查询（除非是覆盖索引）

注意：

模糊查询中必须要满足最左匹配原则。比如我们要查询姓张的用户，我们的条件查询可以为 "where name like ‘张%’"，但是不能是 where name like '%张%'或者是 where name like '%张'，因为索引可以用于查询条件字段为索引字段，根据字段值必须是最左若干个字符进行的模糊查询，也就是需要是 张% 这样的添加才可以使用。

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

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

联合索引的设计原则：

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

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

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

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

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

3.2 覆盖索引

覆盖索引是指查询 SELECT 的所有字段都在同一个索引里面，那么这个索引就称为覆盖索引。使用覆盖索引可以从非主键索引中就查到所需的所有字段，不需要再回表查主键索引中的数据，通过减少回表次数显著提升 I/0 性能。

为了使用覆盖索引，一般需要创建一个包含所有需要返回的列的普通索引或者联合索引。例如创建包含 (age, id, name) 三个列的联合索引，执行下面语句：

查询语句使用了 age 列作为过滤条件，同时需要返回 id 和 name 两个列的值。由于联合索引中包含了要返回的两个列，因此可以直接从索引中获取数据，避免了二次查找的开销。

注意：

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

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

3.3 索引下推（ICP）

索引下推（Index Condition Pushdown, ICP）是指数据库引擎在使用索引检索数据时，能够将部分 WHERE 条件"下推"到存储引擎层进行判断，而不是在服务器层对所有检索到的记录进行过滤。

传统方式：

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

使用 ICP 后：

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

因为存储引擎可以提前过滤掉不符合条件的记录，所以索引下推能够减少二级索引在查询时的回表操作，提高查询的效率。

上面我们说到满足最左前缀原则的时候，联合索引的最左匹配原则会一直向右匹配直到遇到范围查询(>、<、between、like) 就会停止匹配。例如表存在 (username,is_del) 联合索引，执行下面 SQL 语句。

在 MySQL 5.6 之前，只能从匹配的位置一个个回表到主键索引上查询出相应的全行数据，然后再对比字段值筛选出未删除的用户数据。整个过程如下图所示。下面图每一个虚线箭头表示回表一次。存储引擎根据通过联合索引找到 name like '张%' 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，Server 层再对数据根据 age=10 进行筛选。需要回表两次，而且把我们联合索引的另一个字段 age 浪费了。

MySQL 5.6 之后使用索引下推技术，存储引擎根据 (name，age) 联合索引，找到 name like '张%'，由于联合索引中包含 age 列，所以存储引擎直接再联合索引里按照 age=10 过滤，按照过滤后的数据再一一进行回表扫描，只回表了一次。查看执行计划，看到 Extra 列里 Using index condition，这就是经典的索引下推。

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

索引下推的使用条件：

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

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

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

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

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

索引下推适用的场景：

复合索引查询：当查询 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 的所有列都包含在复合索引中，这种索引也称为覆盖索引。覆盖索引默认使用索引下推。

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

覆盖索引和索引下推都是数据库优化查询性能的技术，但它们的实现方式和应用场景有所不同。

覆盖索引：覆盖索引是指一个索引包含了查询所需的所有字段。适用于 SELECT 查询的所有字段全都包含在同一个索引里面的场合。

索引下推：索引下推将 WHERE 条件中索引相关的部分"下推"到存储引擎层进行过滤，减少回表次数。适用于 WHERE 条件中部分条件可以使用索引的场合。

特性	覆盖索引	索引下推
目的	避免回表操作	提前在存储引擎层过滤数据
适用场景	查询列全部包含在索引中	WHERE条件部分可用索引
数据来源	完全从索引获取数据	仍需回表获取完整数据行
优化层次	查询执行计划层面	存储引擎执行层面
MySQL版本	所有版本支持	5.6及以上版本