MySQL part4——MySQL索引详解和实践及从索引结构入手详细分析索引

索引

什么是索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引的作用就相当于书的目录。打个比方：我们在查字典的时候，如果没有目录，那我们就只能一页一页地去找我们需要查的那个字，速度很慢；如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

这样，我们就可以理解下述对索引的描述

索引是帮助MySQL高效获取数据的数据结构，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上采用高级查找算法

索引底层数据结构存在很多种类型，常见的索引结构有：B 树、 B+ 树和 Hash、红黑树。

索引的实现是在存储引擎层实现，不同的存储引擎有不同的结构，主要情况以下

索引	InnoDB	MyISAM	Memory
B+tree索引	√	√	√
Hash索引	x	x	√
R-tree索引	x	√	x
Full-text全文索引	5.6后	√	x

在 MySQL 中，无论是 Innodb 还是 MyISAM，都使用了 B+ 树作为索引结构。

善用索引对 SQL 的性能提升非常明显，是一个性价比较高的 SQL 优化手段。

索引带来了什么

首先，正确理解索引，它的核心价值是用空间换时间

索引的优点

查询速度显著提高：首先，索引肯定让查询速度起飞，无索引时，数据库执行查询会走全表扫描，通过索引，索引会将列值和对应的行地址组织成高效数据结构，据此，数据库只需通过索引结构快速定位到目标行地址，这样数据库可以大幅减少需要扫描的数据量，直接定位到符合条件的记录，从而显著加快数据检索速度，减少磁盘 I/O 次数。
保证数据唯一性：通过创建唯一索引 (Unique Index)，可以确保表中的某一列（或几列组合）的值是独一无二的，比如用户 ID、邮箱等。主键本身就是一种唯一索引。
加速排序和分组：如果查询中的 ORDER BY 或 GROUP BY 子句涉及的列建有索引，数据库往往可以直接利用索引已经排好序的特性，避免额外的排序操作，从而提升性能。

B + 树索引的叶子节点本身是按列值有序排列的，若ORDER BY/GROUP BY的列与索引列一致，数据库无需执行文件排序（Filesort）而是直接遍历有序的索引叶子节点，即可得到排序 / 分组结果。

索引的缺点：

创建和维护耗时：创建索引本身需要时间，特别是对大表操作时。更重要的是，当对表中的数据进行增、删、改 (DML 操作) 时，不仅要操作数据本身，相关的索引也必须动态更新和维护，这会降低这些 DML 操作的执行效率。所以说，查操作多的静态表我们建索引会多一些，增删改操作多的动态表我们建索引会少一些甚至不建索引
占用存储空间：索引本质上也是一种数据结构，需要以物理文件（或内存结构）的形式存储，因此会额外占用一定的磁盘空间。索引越多、越大，占用的空间也就越多。
可能被误用或失效：如果索引设计不当，或者查询语句写得不好，例如模糊查询以 % 开头，OR 连接的列不全有索引，这些情况都会导致数据库优化器可能不会选择使用索引，或者选错索引，反而导致性能下降。而且如果索引列做函数或者运算，所以会直接失效

组合索引不满足最左前缀原则是索引失效的最主要原因，就是组合索引需按最左列开始匹配

例如，CREATE INDEX idx_age_gender ON user(age, gender)，查询WHERE gender='男'

跳过 age 直接查 gender 失效

那么，用了索引就一定能提高查询性能吗？

不一定。 大多数情况下，合理使用索引确实比全表扫描快得多。但也有例外：

数据量太小：如果表里的数据非常少（比如就几百条），全表扫描可能比通过索引查找更快，因为走索引本身也有开销。
查询结果集占比过大：如果要查询的数据占了整张表的大部分（比如超过 20%-30%），优化器可能会认为全表扫描更划算，因为通过索引多次回表（随机 I/O）的成本可能高于一次顺序的全表扫描。
索引维护不当或统计信息过时：导致优化器做出错误判断

正确使用索引

索引设计的原则

要最大化索引优势、最小化缺点，需遵循以下原则

不滥用索引

什么意思，主要是有需要才考虑建索引，而不是建完索引等用到的时候再用

一般情况下，只给查询频率高、选择性高的列建索引

选择性高是什么意思，如果一个列中大部分的内容都不一样，建立索引是值得的，如果列的不同值少，例如班级一共就这几个班，建索引反而会降低性能；

对于查询频率高，上面说了，避免给频繁更新的列建过多索引，给查询多的建立索引

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

不滥用索引也包括在设置索引时要考虑设置合适的列，不要造成过多的索引列。因为每个索引需要额外的磁盘空间，并降低写操作的性能，所以只保持需要的索引有利于查询即可
优先使用联合索引

看个人习惯，但是我更觉得使用组合索引是少而精的，组合索引可覆盖多个查询场景，就好像比如idx_age_gender (age, gender)可支持WHERE age=20、WHERE age=20 AND gender='男'，替代两个单列索引，但是一定要注意最左前缀原则，避免索引失效

组合索引列顺序一般是选择性高的列在前，查询频率高的列在前
正确选择索引类型

不仅是索引的数据结构类型，而且是索引类型也要注意

主键索引是唯一标识行，性能最高，聚簇索引无需回表，这种索引一张表仅一个

唯一索引保证列值唯一，插入 / 更新需校验唯一性，略耗时

普通索引是加速查询，维护成本低，适用范围广

全文索引是文本模糊查询，支持自然语言检索占用空间大，维护成本高，如果很经常使用这种，考虑 MongoDB 或者 ES
定期维护索引

重建碎片索引：频繁删除 / 更新会导致索引产生碎片，执行ALTER TABLE 表名 REBUILD INDEX（MySQL）或REINDEX 索引名（PostgreSQL）重建；

经常分析索引使用情况，删除未使用的索引，优化低效索引；

大表建索引选低峰期，因为大表建索引会阻塞读写，可在凌晨等低峰期执行，或用在线建索引工具（如 MySQL 8.0 的ALTER TABLE ... ADD INDEX ... ALGORITHM=INPLACE）。
最左匹配原则

对于索引中的关键字进行对比的时候，一定是从左往右以此对比，且不可跳过。之前讲解的id都为int型数据，如果id为字符串的时候，如下图：

当进行匹配的时候，会把字符串转换成ascll码，如abc变成97 98 99，然后从左往右一个字符一个字符进行对比。所以在sql查询中使用 like %a 时候索引会失效，因为 % 表示全匹配，如果已经全匹配就不需要索引，还不如直接全表扫描。
最少空间原则

当关键字占用的空间越小，则每个节点保存的关键字个数就越多，每次加载进内存的关键字个数就越多，检索效率就越高。

所以说创建索引的关键字要尽可能占用空间小。

例如，备注列字段VARCHAR(200)，如果该列设置为索引列，查询效率不很高，因为索引字段长度过大，索引节点树高增加，I/O次数也会增加。因此，对于长子字符考虑使用前缀索引。将备注字段值得前10个字符设置为索引，就会节省索引空间，提高效率。
选择合适的字段创建索引

算是对第一条和第六条的总结概括上再单开一个
- 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0、1、true、false 这样语义较为清晰的短值或短字符作为替代。
- 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
- 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引，这样就能有效实践索引下推
- 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
- 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率
限制每张表上的索引数量

索引并不是越多越好，建议单张表索引不超过 5 个！索引可以提高效率，同样可以降低效率。

因为 MySQL 优化器在选择如何优化查询时，会根据统计信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。
注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a)，那么索引(a)就是冗余索引。如(name,city)和(name)这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的。在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引
字符串类型的字段使用前缀索引代替普通索引

前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。
删除长期未使用的索引

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。

MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用

索引应该注意的内容

对于索引，需要注意以下内容

联合索引，遵循最左前缀匹配原则
隐式转换问题：

数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。
发生索引失效的情况例如发生隐式转换，在索引列上进行计算、函数、类型转换等操作
范围条件存在多个索引时，查询可以命中索引

范围条件有：<、<=、>、>=、between等。

范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，并且索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引；

如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引；
利用覆盖索引进行查询，避免回表：被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到row上获取，即被查询列要被所建的索引覆盖，这能够加速查询速度。就是平时我们谈论是否select *
查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到；
以 % 开头的 LIKE 查询比如 LIKE '%abc';；
IN 的取值范围较大时会导致索引失效，走全表扫描（NOT IN 和 IN 的失效场景相同）；

索引类型

按照数据结构维度划分

BTree 索引：MySQL 里默认和最常用的索引类型。只有叶子节点存储 value，非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree，但二者实现方式不一样（前面已经介绍了）。
哈希索引：类似键值对的形式，一次即可定位。
RTree 索引：一般不会使用，仅支持 geometry 数据类型，优势在于范围查找，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR、TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

按照底层存储方式角度划分：

聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引（辅助索引）就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引

按照应用维度划分：

主键索引：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。
普通索引：仅加速查询。
唯一索引：加速查询 + 列值唯一（可以有 NULL）。
覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR、TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。
前缀索引：对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。

MySQL 8.x 中实现的索引新特性：

隐藏索引：也称为不可见索引，不会被优化器使用，但是仍然需要维护，通常会软删除和灰度发布的场景中使用。主键不能设置为隐藏（包括显式设置或隐式设置）。
降序索引：之前的版本就支持通过 desc 来指定索引为降序，但实际上创建的仍然是常规的升序索引。直到 MySQL 8.x 版本才开始真正支持降序索引。另外，在 MySQL 8.x 版本中，不再对 GROUP BY 语句进行隐式排序。
函数索引：从 MySQL 8.0.13 版本开始支持在索引中使用函数或者表达式的值，也就是在索引中可以包含函数或者表达式。

索引结构

Hash表

哈希表是键值对的集合，通过键 key 即可快速取出对应的值 value，因此哈希表可以快速检索数据，接近 O(1)。

因为哈希算法可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value，至于什么是哈希算法，不多说了

但是！哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了提高链表过长时的搜索效率，引入了红黑树。

为了减少 Hash 冲突的发生，一个好的哈希函数应该“均匀地”将数据分布在整个可能的哈希值集合中。

MySQL 的 InnoDB 存储引擎不直接支持常规的哈希索引，但是，InnoDB 存储引擎中存在一种特殊的自适应哈希索引，自适应哈希索引并不是传统意义上的纯哈希索引，而是结合了 B+Tree 和哈希索引的特点，以便更好地适应实际应用中的数据访问模式和性能需求。

自适应哈希索引的每个哈希桶实际上是一个小型的 B+Tree 结构。这个 B+Tree 结构可以存储多个键值对，而不仅仅是一个键。这有助于减少哈希冲突链的长度，提高了索引的效率，它是一个纯内存结构

所以说，一直以来流传着两种关于MySQL InnoDB哈希索引的传言。有一部分人说，InnoDB不支持哈希索引；有一部分人说，InnoDB支持哈希索引

在MySQL运行的过程中，如果InnoDB发现，有很多寻路很长（比如B+树层数太多、回表次数多等情况）的SQL，并且有很多SQL会命中相同的页面（Page）的话，InnoDB会在自己的内存缓冲区（Buffer Pool）里，开辟一块区域，建立自适应哈希索引（Adaptive Hash Index，AHI），以加速查询。

在Adaptive Hash Index，Key就是经常访问到的索引键值，Value就是该索引键值匹配的完整记录所在页面（Page）的位置，而且它只能用于等值比较，例如=、<=>、IN、AND等，无法用于排序；

既然哈希表这么快，为什么 MySQL 没有直接使用哈希作为其索引的数据结构呢？

主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。并且，每次 IO 只能取一个，我们操作数据库表，单取出一个数据的情况有是有，但是确实不多

二叉查找树（BST）

唉数据结构这招太狠了

二叉查找树（Binary Search Tree）是一种基于二叉树的数据结构，它具有以下特点：

左子树所有节点的值均小于根节点的值。
右子树所有节点的值均大于根节点的值。
左右子树也分别为二叉查找树。

当二叉查找树是平衡的时候，也就是树的每个节点的左右子树深度相差不超过 1 的时候，查询的时间复杂度为 O(log2(N))，具有比较高的效率。然而，当二叉查找树不平衡时，例如在最坏情况下（有序插入节点），树会退化成线性链表（也被称为斜树），导致查询效率急剧下降，时间复杂退化为 O(N)。

也就是说，二叉查找树的性能非常依赖于它的平衡程度，这就导致其不适合作为 MySQL 底层索引的数据结构。

所以说，平衡二叉树、B-Tree、B+Tree 等，才是 MySQL 使用的索引数据结构

平衡二叉搜索树（AVL树）

AVL 树是计算机科学中最早被发明的自平衡二叉查找树，它的名称来自于发明者 G.M. Adelson-Velsky 和 E.M. Landis 的名字缩写。

AVL 树的特点是，在平衡二叉树的基础上，保证任何节点的左右子树高度之差不超过 1，因此也被称为高度平衡二叉树

它的查找、插入和删除在平均和最坏情况下的时间复杂度都是 O(logn)。

AVL 树采用了旋转操作来保持平衡。主要有四种旋转操作：LL 旋转、RR 旋转、LR 旋转和 RL 旋转。其中 LL 旋转和 RR 旋转分别用于处理左左和右右失衡，而 LR 旋转和 RL 旋转则用于处理左右和右左失衡。

由于 AVL 树需要频繁地进行旋转操作来保持平衡，因此会有较大的计算开销进而降低了数据库写操作的性能。并且，在使用 AVL 树时，每个树节点仅存储一个数据，而每次进行磁盘 IO 时只能读取一个节点的数据，如果需要查询的数据分布在多个节点上，那么就需要进行多次磁盘 IO。

磁盘 IO 是一项耗时的操作，在设计数据库索引时，我们需要优先考虑如何最大限度地减少磁盘 IO 操作的次数。

所以，实际应用中，AVL 树使用的并不多

红黑树

高度平衡二叉树的核心是严格平衡，任意节点的左右子树高度差，而红黑树是近似平衡的二叉查找树，他不是追求高度平衡的，而是通过颜色规则保证树的最长路径不超过最短路径的 2 倍

红黑树是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态，它具有以下特点：

每个节点非红即黑；
根节点总是黑色的；
每个叶子节点都是黑色的空节点（NIL 节点）；
如果节点是红色的，则它的子节点必须是黑色的（反之不一定），即不能有连续的红色节点
从任意一个节点到其所有后代叶子节点的路径中，包含的黑色节点数量相同

所以说，红黑树近似平衡，插入删除的调整比较少性能非常优秀，查找性能因高度可能略差一点点相比 AVL，所以说，AVL 树是为读而生，红黑树是读写兼顾，更适配写操作。

因为红黑树的平衡性比较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。

红黑树的插入和删除操作效率大大提高了，因为红黑树在插入和删除节点时只需进行 O(1) 次数的旋转和变色操作，即可保持基本平衡状态，而不需要像 AVL 树一样进行 O(logn) 次数的旋转操作。

红黑树的应用还是比较广泛的，TreeMap、TreeSet 以及 JDK1.8 的 HashMap 底层都用到了红黑树。对于数据在内存中的这种情况来说，红黑树的表现是非常优异的

B树

B 树也称 B- 树，全称为 多路平衡查找树，B 树和 B+ 树中的 B 是 Balanced（平衡）的意思。

它的核心设计目标是适配磁盘 IO 特性，很明显，树高一层意味着多一次的磁盘I/O，这是它区别于红黑树、AVL 树的关键，也是数据库索引选择它的核心原因

对于查找，插入，删除的操作，就不在这里细说了

这次再来解释一下B树的全名

多路：B 树的路数是节点能指向的子节点数量，每个节点可以有多个子节点，比如 3 阶 B 树的节点最多有 2 个关键字、3 个子节点；路数 = 关键字数量 + 1。
- 对于阶数定义，m阶B树需满足 3 个条件
  - 根节点至少有 2 个子节点，除非树只有一个根节点
  - 非根非叶子节点中至少有⌈m/2⌉个子节点，最多有m个子节点
  - 每个节点的关键字数量是子节点数 - 1
平衡：所有叶子节点都在同一层，保证任意查询的磁盘 IO 次数一致；、
磁盘友好：每个节点的大小设计为磁盘页大小（如 InnoDB 的 16KB），一次 IO 就能读取整个节点，大幅减少 IO 次数。

和搜索二叉树一样，B 树也满足左子树节点比根节点的值小，这是进行快速查找的基础

B树的特征如下

所有关键字分散在非叶子节点 + 叶子节点中，而下面提到的 B+ 树是非叶子节点仅存索引关键字，数据全在叶子节点，B 树的所有节点既存放键(key)也存放数据(data)
任何关键字只出现一次，保证查询的精准性；数据库中，主键索引的 B 树关键字就是主键值，天然唯一；普通索引的关键字可重复，但在 B 树中仍以独立条目存在。
搜索有可能在非叶子节点结束，因为B树非叶子节点也存关键字，但数据库中几乎用不到，因为数据库索引需要关联行数据，非叶子节点无数据，必须到叶子节点。
搜索性能等价于二分查找，B 树的每个节点内的关键字是有序排列的，查询时先在节点内做二分查找，找到对应子树，再递归查询子节点，整体复杂度为O(log_m n)
自动层次控制，因为它在插入 / 删除关键字时，B树会节点裂开和合并保证所有叶子节点在同一层，避免树高增加

这里顺便说一下：在 B Tree 保证树的平衡的过程中，每次关键字的变化，都会导致结构发生很大的变化，这个过程是特别浪费时间的，所以创建索引一定要创建合适的索引，而不是把所有的字段都创建索引，创建冗余索引只会在对数据进行新增，删除，修改时增加性能消耗。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

而数据库索引用 B + 树，PostgreSQL是 B + 树变种，MySQL 是 B + 树，而非纯 B 树

InnoDB 的 B + 树默认是4 阶，实际实现为 16KB 页大小，非叶子节点至少 2 个子节点，最多 4 个子节点，1-3个关键字

B+树

B+Tree是 B Tree的一个变种，它对 B 树做了专门优化，专门为磁盘、为数据库索引而生的多路平衡查找树。它解决了 B 树的 3 个问题

B 树非叶子节点也存数据，导致一页能存的 key 变少
B 树每次查询路径不固定
B 树范围查询很慢

所以，我们可知 B+ 树是

m 阶多路平衡查找树，所有叶子节点在同一层
非叶子节点只存索引 key，不存数据，所有数据 / 行记录都存在叶子节点
叶子节点之间用双向链表串联，这样就能进行很快速的范围查询
非叶子节点的作用仅仅是索引、导航、分范围
一个节点内的 key 有序排列，内部做二分查找
从根到任意叶子，路径长度完全相同，这样就可以做到拥有稳定 IO 次数
在B+Tree中，B树的路数和关键字的个数的关系不再成立了，路数和关键字个数关系为1比1

从磁盘 IO 的角度，B+ 树为什么能让数据库查询极快，因为，上面提到了 InnoDB 默认页 16KB，假设一个 key 为 8 字节，一页能存16KB / 8B = 2000 个 key，意味着一层可以存储2000个key，两层可以存储2000×2000key，三层可以存储2000×2000×2000key，80 亿的数据，B+ 树高度 3 层就足够，这样一次查询只需要 3次磁盘 IO，相比红黑树下非常快速，相比 B 树也正反应了 B+ 树 IO 次数稳定的特征

最后B 树 & B+ 树两者有何异同呢？

B 树的所有节点既存放键 key 也存放数据 data，而 B+ 树只有叶子节点存放 key 和 data，其他内节点只存放 key，关键字对应的数据只保存在叶子节点中
B 树的叶子节点都是独立的；B+ 树的叶子节点有一条引用链指向与它相邻的叶子节点。
B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+ 树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显
在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+ 树的范围查询，只需要对链表进行遍历即可。

B+Tree 关键字的搜索采用的是左闭合区间，之所以采用左闭合区间是因为他要最好的去支持自增id，这也是mysql的设计初衷。

综上，B+ 树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

此外，B+ 树的保持平衡，插入删除和 B 树类似，都是通过节点分裂与合并完成

在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。

下面的内容整理自《Java 工程师修炼之道》

MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引（非聚集索引）”。

InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（聚集索引）”，而其余的索引都作为 辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂

对于 MySQL，主键索引就是聚簇索引，表本身就是按 B+ 树组织的，，叶子节点直接存整行数据，一张表只有一个聚簇索引，PostgreSQL 的 B+ 树没有聚簇索引，所有索引都是二级索引结构，PG 索引叶子存指针，MySQL 二级索引叶子存主键。

MySQL为什么最终要去选择B+Tree？

扫库，扫表，范围查询能力碾压 B Tree

这是 MySQL 选择 B+Tree 的第一核心原因，数据库中范围查询很明显用的比单值查询更多，而 B Tree 完全适配不了。

B Tree 的关键字分散在非叶子节点 + 叶子节点，且叶子节点之间无关联，需要递归遍历整棵树，相当于遍历无数个孤立的小集合

B+Tree 的所有数据都在叶子节点，且叶子节点通过双向链表串联，扫表 / 范围查询只需两步：① 找到范围的起始叶子节点（如 id=10）；② 顺着链表向后遍历，直到结束节点（如 id=100）；

磁盘读写能力更强

也就是能够 IO 效率最大化，这样让单个磁盘页（节点）存储更多关键字，进一步降低树高、减少 IO 次数

节点类型	B Tree 存储内容	单节点关键字数量	B+Tree 存储内容	单节点关键字数量
非叶子节点	关键字 (4) + 数据指针 (4) + 子节点引用 (4)	16000÷12≈1333 个	仅关键字 (4) + 子节点引用 (4)	16000÷8=2000 个
叶子节点	关键字 (4) + 数据指针 (4) + 子节点引用 (4)	16000÷12≈1333 个	仅关键字 (4) + 数据指针 (4)	16000÷8=2000 个

天然适配数据库排序需求

B+Tree 的天然排序规避了数据库的文件排序（Filesort）操作，因为B+Tree 的叶子节点链表本身就是全局有序的

B Tree 的关键字虽在单个节点内有序，但整体数据分散在整棵树
查询性能稳定

理由同一
数据一致性更易保证

B+Tree 的所有数据都在叶子节点，插入 / 删除操作仅需修改叶子节点和少量非叶子节点；而 B Tree 的插入 / 删除可能涉及整棵树的关键字移动，容易引发数据不一致。
缓存命中率更高

B+Tree 的非叶子节点只存关键字，不存数据，缓存（InnoDB Buffer Pool）能缓存更多的索引节点，减少磁盘 IO；而 B Tree 的非叶子节点存数据，相同大小的缓存空间易被占满，命中率低。

在MYISAM存储引擎中，数据和索引的关系如下：

如何查找数据的呢？

如果要查询id = 40的数据：先根据MyISAM索引文件（如上图左）去找id = 40的节点，通过这个节点的数据区拿到真正保存数据的磁盘地址，再通过这个地址从MYD数据文件（如上图右）中加载对应的记录。

如果有多个索引，表现形式如下：

所以在MYISAM存储引擎中，主键索引和辅助索引是同级别的，没有主次之分。

Innodb主键索引为聚集索引，首先简单理解一下聚集索引的概念：数据库表行中数据的物理顺序和键值的逻辑顺序相同。

Innodb以主键索引来聚集组织数据的存储，下面看看Innodb是如何组织数据的。

如上图，主键索引的叶子节点保存的是真正的数据。而辅助索引叶子节点的数据区保存的是主键索引关键字的值。

假如要查询 name = C 的数据，其搜索过程如下：

先在辅助索引中通过C查询最后找到主键id = 9.
在主键索引中搜索id为9的数据，最终在主键索引的叶子节点中获取到真正的数据。

所以通过辅助索引进行检索，需要检索两次索引。

把 Innodb 和 MYISAM 区别放在一张图中看，就如下所示

主键索引

主键索引就是数据表的主键列所使用的索引

一张数据表有只能有一个主键，并且主键不能为 null，不能重复。（主键必须非空、唯一）

InnoDB 表本质上是按主键索引的 B + 树组织的，整张表的数据都存在主键索引的叶子节点中

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

InnoDB 的所有索引都是B + 树结构，但主键索引和二级索引的 B + 树存储内容、查询逻辑完全不同

主键索引是索引即数据，叶子节点直接的是存整行的数据，而且按主键值有序排列，非叶子节点只存主键值和子节点指针，不存任何行数据
二级索引：叶子节点仅存索引列值 + 主键值，查询需要回表再查出具体内容

找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引，修改聚簇索引其实就是修改主键。

来做个例子，假设用户表user的主键是id（int 类型），字段有id、name、age、phone，主键索引的 B + 树结构如下：

# 非叶子节点（仅存主键值 + 子节点指针）
根节点（3层B+树的顶层）：[100, 200] → 指向2个子节点
子节点（中间层）：
  - [1,50,80] → 指向叶子节点1
  - [100,150,180] → 指向叶子节点2
  - [200,250,300] → 指向叶子节点3

# 叶子节点（双向链表串联，存整行数据）
叶子节点1：(1, "张三", 20, "13800000001") → (50, "李四", 25, "13800000050") → (80, "王五", 30, "13800000080")
叶子节点2：(100, "赵六", 28, "13800000100") → ... → (180, "钱七", 35, "13800000180")
叶子节点3：(200, "孙八", 40, "13800000200") → ... → (300, "周九", 45, "13800000300")

把两者看成计算机寻址的一级地址和二级地址就可以了

为什么 InnoDB 推荐用自增主键？

自增主键是连续的，插入时只会追加到叶子节点末尾，不会触发 B + 树节点分裂（性能高）；若用 UUID 作为主键，UUID 无序，插入时会频繁分裂节点，导致索引碎片、性能下降。

二级索引

二级索引（Secondary Index）的叶子节点存储的数据是主键的值，也就是说，通过二级索引可以定位主键的位置，然后进行回表查询，二级索引又称为辅助索引/非主键索引。

二级索引是除主键索引外的所有索引，其 B + 树的叶子节点不存整行数据

唯一索引、普通索引、前缀索引等索引都属于二级索引。

唯一索引（Unique Key）：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

普通索引（Index）：普通索引的唯一作用就是为了快速查询数据。一张表允许创建多个普通索引，并允许数据重复和 NULL。

前缀索引（Prefix）：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。要注意只有字符类型或者二进制类型的字段可以建立前缀索引。比如：CHAR/ VARCHAR 、 TEXT / BLOB 、BINARY/ VARBINARY。其中，TEXT/BLOB 类型只支持前缀索引

对于前缀索引，https://zhuanlan.zhihu.com/p/345693438

全文索引（Full Text）：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MyISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

一张表可以有多个二级索引
二级索引的索引列可以重复，除了唯一索引
二级索引的 B + 树结构依赖主键索引，无法脱离主键索引存在。

继续看这个例子，基于上面的user表，创建二级索引idx_user_phone (phone)，其 B + 树结构如下

# 非叶子节点（仅存索引列值 + 子节点指针）
根节点：["13800000001", "13800000100"] → 指向2个子节点
子节点：
  - ["13800000001", "13800000050", "13800000080"] → 指向叶子节点A
  - ["13800000100", "13800000180", "13800000200"] → 指向叶子节点B

# 叶子节点（双向链表串联，存索引列值 + 主键值）
叶子节点A：("13800000001", 1) → ("13800000050", 50) → ("13800000080", 80)
叶子节点B：("13800000100", 100) → ("13800000180", 180) → ("13800000200", 200)

非叶子节点只存索引列值（如 phone）和子节点指针，不存主键值、不存行数据；

叶子节点按索引列值有序排列，存储的是该列的主键和索引的列值，拿着主键值，到主键索引的 B + 树中查找，返回最终结果

可以看出全程需 6 次磁盘 IO（二级索引 3 次 + 主键索引 3 次），这就是回表的本质。

聚簇索引与非聚簇索引

聚簇索引

“聚簇” 是指索引结构和数据存储聚在一起

B + 树的非叶子节点仅存主键索引值，负责导航；
叶子节点既存主键索引值，又存整行数据，索引和数据物理上聚在同一位置

聚簇索引（Clustered Index）即索引结构和数据一起存放的索引，并不是一种单独的索引类型，而是 B+ 树索引的一种数据的存储方式，它把 B + 树的索引结构和表数据直接融合在同一个 B + 树中，叶子节点既存索引值，又存整行数据。

InnoDB 中的主键索引就属于聚簇索引。这是它和 MyISAM（非聚簇索引）最核心的区别。

在 MySQL 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引（B+ 树）的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

用一张 InnoDB 表的实例，看看聚簇索引的结构

CREATE TABLE user (
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键（聚簇索引列）
  name VARCHAR(20),
  age INT,
  phone VARCHAR(11)
) ENGINE=InnoDB;

结构就是这样

# 非叶子节点（仅存主键索引值 + 子节点指针）
根节点（3层B+树）：[100, 200]  
→ 指向2个子节点：[1~100]、[101~200]

子节点（中间层）：
- [1, 50, 80] → 指向叶子节点1
- [100, 150, 180] → 指向叶子节点2
- [200, 250, 300] → 指向叶子节点3

# 叶子节点（双向链表串联，存主键值 + 整行数据）
叶子节点1：(1, "张三", 20, "13800000001") → (50, "李四", 25, "13800000050") → (80, "王五", 30, "13800000080")
叶子节点2：(100, "赵六", 28, "13800000100") → ... → (180, "钱七", 35, "13800000180")
叶子节点3：(200, "孙八", 40, "13800000200") → ... → (300, "周九", 45, "13800000300")

优点：

查询速度非常快：聚簇索引的查询速度非常的快，因为整个 B+ 树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引，聚簇索引少了一次读取数据的 IO 操作。

B + 树叶子节点直接存整行数据，定位到叶子节点就等于拿到了数据，仅需 B + 树的树高次数 IO（3 层 = 3 次 IO），无需额外读数据。
对排序查找和范围查找优化：聚簇索引对于主键的排序查找和范围查找速度非常快。因为B + 树的叶子节点本身是按主键有序的双向链表，而聚簇索引的叶子节点就是数据本身

缺点：

依赖于有序的数据：因为 B+ 树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的无序数据，插入或查找的速度肯定比较慢。
更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚簇索引

非聚簇索引（Non-Clustered Index）即索引结构和数据分开存放的索引，和聚簇索引一样，它并不是一种单独的索引类型，也是一种组织数据的方式

也就是说，索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询，非聚簇索引也叫做辅助索引。

普通索引（二级索引，辅助索引）就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚簇索引的优缺点

优点：

更新代价比聚簇索引要小。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的。

缺点：

依赖于有序的数据：跟聚簇索引一样，非聚簇索引也依赖于有序的数据。
可能会二次查询（回表）：这应该是非聚簇索引最大的缺点了。当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

这是 MySQL 的表的文件截图：

聚簇索引和非聚簇索引：

那么，非聚簇索引一定回表查询吗

不一定

试想一种情况，用户准备使用 SQL 查询用户名，而该表对用户名字段正好建立了索引。

1	SELECT name FROM table WHERE name='Ergou';

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

即使是 MyISAM 也是这样，虽然 MyISAM 的主键索引确实需要回表，因为它的主键索引的叶子节点存放的是指针。但是！如果 SQL 查的就是主键呢?

1	SELECT id FROM table WHERE id=1;

主键索引本身的 key 就是主键，查到返回就行了。这种情况就称之为覆盖索引了。

覆盖索引和联合索引

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引（Covering Index）。

在 InnoDB 存储引擎中，非主键索引的叶子节点包含的是主键的值。这意味着，当使用非主键索引进行查询时，数据库会先找到对应的主键值，然后再通过主键索引来定位和检索完整的行数据。这个过程被称为“回表”。

在InnoDB 的设计规则中，无论创建什么二级索引，其叶子节点必然包含主键值

那么，如果覆盖索引所需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

也就是，让查询需要的所有字段，都落在索引列 + 主键值这个范围内。

就和上面说的一样，如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引，那么直接根据这个索引就可以查到数据，也无需回表。

我们进行一个简单的测试

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '名称',
  `age` int(3) unsigned NOT NULL DEFAULT '1' COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `I_name` (`name`)
) ENGINE=InnoDB;

INSERT INTO student (name, age) VALUES("小赵", 10),("小王", 11),("小李", 12),("小陈", 13);

复制代码这里我们设置了主键为自增，那么此时数据库里数据为

此时表里面有两个索引，一个是主键索引一个是唯一约束索引，每一个索引在 InnoDB 里面对应一棵B+树，那么此时就存着两棵B+树

以发现区别在与叶子节点中，主键索引存储了整行数据，而非主键索引中存储的值为主键ID，在我们执行如下sql后

1	SELECT age FROM student WHERE name = '小李'；

查找的流程为：

在 name 索引树上找到名称为小李的节点 id 为03
从id索引树上找到 id 为 03 的节点获取所有数据
从数据中获取字段命为 age 的值返回 12

可以发现，上述进行了一次回表，这次我们使用覆盖索引来避免这次回表，从非主键索引中就能查到的记录，就不需要查询主键索引中的记录了

之前我们已经建立了表 student，那么现在出现的业务需求中要求根据名称获取学生的年龄，并且该搜索场景非常频繁，那么先在我们删除掉之前以字段 name 建立的普通索引，以 name 和 age 两个字段建立联合索引

1 2	ALTER TABLE student DROP INDEX I_name; ALTER TABLE student ADD INDEX I_name_age(name, age);

那在我们再次执行如下sql后

1	SELECT age FROM student WHERE name = '小李'；

代码流程为：

在name,age联合索引树上找到名称为小李的节点
此时节点索引里包含信息 age 直接返回 12

如何确定数据库成功使用了覆盖索引呢？

当发起一个索引覆盖查询时，在 explain 的 extra 列可以看到 using index 的信息

关于 EXPLAIN 命令的详细介绍请看：MySQL 执行计划分析这篇文章

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 的输出各个字段的含义如下：

列名含义

id SELECT 查询的序列标识符

select_type SELECT 关键字对应的查询类型

table 用到的表名

partitions 匹配的分区，对于未分区的表，值为 NULL

type 表的访问方法

possible_keys 可能用到的索引

key 实际用到的索引

key_len 所选索引的长度

ref 当使用索引等值查询时，与索引作比较的列或常量

rows 预计要读取的行数

filtered 按表条件过滤后，留存的记录数的百分比

Extra 附加信息

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

而且分页查询是高频，未优化的分页常因回表慢，覆盖索引可优化，所以一般情况下，分页查询需要尤其注意这个问题

-- 需要回表，IO次数多
SELECT * FROM user WHERE age > 20 LIMIT 10000, 20;

-- 步骤1：用覆盖索引找到分页的主键id（无回表）
SELECT id FROM user WHERE age > 20 LIMIT 10000, 20;

-- 步骤2：用主键索引查全量数据（主键查询无回表）
SELECT * FROM user WHERE id IN (步骤1拿到的id列表);

SELECT *会查询所有字段，几乎不可能被单个索引覆盖，必然触发回表，所以说，只查需要的字段，让查询字段落在索引范围内才是正确实践

联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引

就好像上面的覆盖索引演示中，以 name 和 age 两个字段建立联合索引

1	ALTER TABLE student ADD INDEX I_name_age(name, age);

联合索引涉及到一个最左前缀匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配

相关阅读：联合索引的最左匹配原则全网都在说的一个错误结论。

假设有一个联合索引 (column1, column2, column3)，其从左到右的所有前缀为 (column1)、(column1, column2)、(column1, column2, column3)（创建 1 个联合索引相当于创建了 3 个索引），包含这些列的所有查询都会走索引而不会全表扫描。

我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

我们这里简单演示一下最左前缀匹配的效果。

创建一个名为 student 的表，这张表只有 id、name、class 这 3 个字段。

CREATE TABLE `student` (
  `id` int NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `class` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_class_idx` (`name`,`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

下面我们分别测试三条不同的 SQL 语句。

# 可以命中索引
SELECT * FROM student WHERE name = 'Anne Henry';
EXPLAIN SELECT * FROM student WHERE name = 'Anne Henry' AND class = 'lIrm08RYVk';
# 无法命中索引
SELECT * FROM student WHERE class = 'lIrm08RYVk';

再来看一个常见的面试题：如果有索引 联合索引（a，b，c），查询 a=1 AND c=1 会走索引么？c=1 呢？b=1 AND c=1 呢？ b = 1 AND a = 1 AND c = 1 呢？

对于查询 a=1 AND c=1 ，查询可以使用索引的前缀部分，因此会使用a=1的索引，然后对c=1进行过滤
对于查询 c=1，由于查询中不包含最左列 a，根据最左前缀匹配原则，整个索引都无法被使用。
b=1 AND c=1同上述情况
b = 1 AND a = 1 AND c = 1 会用到索引，查询优化器分析 SQL 语句时，对于联合索引，会对查询条件进行重排序，以便用到索引。会将 b=1 和 a=1 的条件进行重排序，变成 a=1 AND b=1 AND c=1。

MySQL 8.0.13 版本引入了索引跳跃扫描，它可以在某些索引查询场景下提高查询效率。在没有 ISS 之前，不满足最左前缀匹配原则的联合索引查询中会执行全表扫描。而 ISS 允许 MySQL 在某些情况下避免全表扫描，即使查询条件不符合最左前缀。

索引下推

什么是索引下推

索引下推（Index Condition Pushdown，简称 ICP） 是 MySQL 5.6 版本中提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分 WHERE 字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高查询效率。

假设我们有一个名为 user 的表，其中包含 id、username、zipcode 和 birthdate 4 个字段，创建了联合索引 (zipcode, birthdate)。

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `zipcode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `birthdate` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_username_birthdate` (`zipcode`,`birthdate`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4;

# 查询 zipcode 为 431200 且生日在 3 月的用户
# birthdate 字段使用函数索引失效
SELECT * FROM user WHERE zipcode = '431200' AND MONTH(birthdate) = 3;

没有索引下推之前，即使 zipcode 字段利用索引可以帮助我们快速定位到 zipcode = '431200' 的用户，但我们仍然需要对每一个找到的用户进行回表操作，获取完整的用户数据，再去判断 MONTH(birthdate) = 3。
有了索引下推之后，存储引擎会在使用 zipcode 字段索引查找 zipcode = '431200' 的用户时，同时判断 MONTH(birthdate) = 3。这样，只有同时满足条件的记录才会被返回，减少了回表次数。

索引下推的原理

MySQL 可以简单分为 Server 层和存储引擎层这两层。Server 层处理查询解析、分析、优化、缓存以及与客户端的交互等操作，而存储引擎层负责数据的存储和读取，MySQL 支持 InnoDB、MyISAM、Memory 等多种存储引擎。

索引下推的下推其实就是指将部分上层（Server 层）负责的事情，交给了下层（存储引擎层）去处理。

我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

存储引擎读取索引记录；
根据索引中的主键值，定位并读取完整的行记录；
存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

存储引擎读取索引记录（不是完整的行记录）；
判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

结合索引下推原理再对上面提到的例子进行解释。

没有索引下推之前：

存储引擎层先根据 zipcode 索引字段找到所有 zipcode = '431200' 的用户的主键 ID，然后二次回表查询，获取完整的用户数据；
存储引擎层把所有 zipcode = '431200' 的用户数据全部交给 Server 层，Server 层根据 MONTH(birthdate) = 3 这一条件再进一步做筛选。

有了索引下推之后：

存储引擎层先根据 zipcode 索引字段找到所有 zipcode = '431200' 的用户，然后直接判断 MONTH(birthdate) = 3，筛选出符合条件的主键 ID；
二次回表查询，根据符合条件的主键 ID 去获取完整的用户数据；
存储引擎层把符合条件的用户数据全部交给 Server 层。

可以看出，除了可以减少回表次数之外，索引下推还可以减少存储引擎层和 Server 层的数据传输量。

最后，总结一下索引下推应用范围：

适用于 InnoDB 引擎和 MyISAM 引擎的查询。
适用于执行计划是 range、ref、eq_ref、ref_or_null 的范围查询。
对于 InnoDB 表，仅用于非聚簇索引。索引下推的目标是减少全行读取次数，从而减少 I/O 操作。对于 InnoDB 聚集索引，完整的记录已经读入 InnoDB 缓冲区。在这种情况下使用索引下推不会减少 I/O。
子查询不能使用索引下推，因为子查询通常会创建临时表来处理结果，而这些临时表是没有索引的。
存储过程不能使用索引下推，因为存储引擎无法调用存储函数