MySQL学习笔记之索引篇

MySQL的索引是一种帮助MySQL 高效地查询和检索数据 的数据结构，可以看作是 数据的目录。 索引的 好处 在于：

当然并不是创建的索引越多越好，索引也存在 缺点 ：

所以MySQL索引是以 空间换取时间 的设计思想。

MySQL的索引按照不同的类型和特点可以从三个角度进行分类：

MySQL索引从数据结构的角度可以分为B+树索引、Hash索引、全文索引等。 在这里只详细说一下B+树索引，因为InnoDB、MyISAM等常见的MySQL存储引擎都支持B+树索引，并且MySQL5.5后将InnoDB设为默认存储引擎，B+树索引也成为了MySQL存储引擎使用最多的所有类型。

在疑问为什么使用B+树这种数据结构之前，先想想什么样的数据结构才最适合MySQL？

MySQL的数据是 持久化 的，也就是说数据是存储在磁盘中的（即索引和记录是保存在磁盘中的）。 那么在需要使用索引进行高效检索数据时，就必须访问磁盘取得索引读入内存，再根据索引再次访问磁盘检索数据后读入内存，所以每次检索数据都需要进行多次的 I/O操作，I/O操作耗费大量时间。 因此应该选择一种数据结构 使得 I/O操作 次数尽可能的少 。 此外还需要注意的是MySQL是支持范围查询的，也就意味着挑选的数据结构必须能够 支持高效地范围查询。

了解了MySQL对数据结构的需求后，来看看为什么B+树符合？

MySQL中的B+树结构图：

阐述一下B+树的优点：

相比于其他数据结构：

所以B+树更适合作为MySQL索引的数据结构。

聚簇索引和非聚簇索引的区别：

MySQL的InnoDB存储引擎中每张表只能有一个聚簇索引，通常是主键索引；而非聚簇索引都是二级索引，可以有多个。 主键索引

主键索引是建立在主键字段上的索引，主键索引的B+树结构中的叶子节点存放的是实际数据（所有完整的记录）。 关于InnoDB引擎确定 聚簇索引（主键索引） 的方式按照如下次序：

二级索引又称为辅助索引，二级索引的B+树结构中的叶子节点 存放的数据是主键，索引分类中的唯一索引、普通索引、前缀索引等都属于二级索引。

什么是回表？

正如上面所说的二级索引的B+树结构中的叶子节点存放的数据是主键，那么使用二级索引查找记录的过程是怎么样的呢？

二级索引查找记录的过程为：先获得二级索引中的B+树的索引值，检索 二级索引的B+树 找到对应的叶子节点后获取到对应的主键值，再通过主键值检索 主键索引的B+树 找到对应的叶子节点即可获得对应的数据（记录）。第一次检索获取主键值，再通过主键值再次检索获得记录的这个过程叫做 回表。 举个例子： select * from products where name = 'iPhone 14'; 复制代码

如上面的代码，id为主键索引列，name为唯一索引列（二级索引）。执行这条语句时，会先拿'iPhone 14'作为索引值检索二级索引的B+树找到对应的叶子节点，这里的叶子节点存储的是要找的这条记录的主键值1，然后再拿1作为索引值检索主键索引的B+树找到对应的叶子节点，此时这里的叶子节点存储的就是整条记录了。

二级索引查找记录一定要进行回表查询吗？

答案是并不一定。 修改上面的例子： select id from products where name = 'iPhone 14'; 复制代码 当 查询的数据能在二级索引的B+树的叶子节点上直接查询到 时，就不再需要进行回表查询，直接返回数据即可。正如这里例子中，因为需要查询的是 id，二级索引的B+树的叶子节点上可以直接获取，这时就不需要回表操作了。 这种在二级索引的B+树的叶子节点能直接查询到数据的过程就叫做 覆盖索引。

什么是索引下推？

索引下推是在MySQL5.6之后引进的索引优化功能，可以让存储引擎在非聚簇索引检索数据时，对索引中包含的字段先做判断是否符合条件，过滤掉不符合条件的记录，在返回给MySQL数据库（Server层），减少回表次数，减少不必要的数据传输，提高查询效率和性能。

索引下推我的理解就是把 原本应该在MySQL数据库（Server层）进行判断是否符合条件的工作下推到了存储引擎层中进行，这样的好处就是存储引擎返回给Server层的数据少了，减少了不必要的数据传输同时也就减少了回表的次数。

这里需要注意的是 存储引擎层 只是判断 索引中包含的字段 是否符合条件，对于那些 没有索引的字段但又需要判断的 会返回到 Server层 之后再进行判断。 后面的索引失效的联合索引问题中会使用具体例子说明。

联合索引：由 多个字段 组合成一个索引。 语法：CREATE INDEX 索引名 ON 表名(列名1, 列名2, ...); 通过例子阐述联合索引

当前数据有：

通过 CREATE INDEX index_stockNo_stock ON products(stockNo, stock); 创建 stockNo 和 stock 的联合索引。 此时联合索引的B+树是这样的：

可以看到B+树是先按照 stockNo 字段进行排序，再 stockNo 字段相同的情况况下再按照 stock 字段进行排序。 所以 stockNo 字段是 全局有序 的，而 stock 字段是 全局无序，但局部有序 的。也就是说在创建联合索引时，创建顺序为(a, b, c)，那么对应的就是 a 全局有序，b、c 局部相对有序。

联合索引是否存在失效情况呢？

首先需要明确的一点是：利用索引的前提是 索引数据结构中的索引键是有序的，否则就需要遍历查找。 如果通过 select * from products where stockNo = 1003 and stock = 80; 查询数据，是可以利用到索引进行检索。（注意 in 和 = 可以乱序，因为有查询优化器所以stockNo字段在where子句的顺序不重要）

如果通过 select * from products where stock = 80; 查询数据，是不可以利用到索引进行检索。

这是因为前面提到的 stock 并不是全局有序的，而是局部相对有序的，所以此时就会出现索引失效的情况。（对于索引失效问题后面还会详细讲解）。

联合索引的最左前缀原则

联合索引的最左前缀原则指的是 在MySQL建立联合索引时，会遵守最左前缀原则，在检索数据时从联合索引的最左边开始匹配。 通俗一点讲：好比现在创建了一个联合索引 (a, b, c)，相当于建立了三个索引 (a)、(a, b)、(a, b, c)， 也就是说当需要检索的是 where a = XXX; 或者是 where a = XXX and b = XXX; 或者是 where a = XXX and b = XXX and c = XXX都是可以命中索引也就是利用索引进行检索数据的。 但是如果需要检索的是 where b = XXX and c = XXX 那就无法命中索引了也就是无法利用索引检索数据。也就是说必须从最左边开始匹配，只要匹配不到了就无法使用索引（注意这里 where a = XXX and c = XXX是可以匹配到的，具体在索引失效中介绍）。

通过上一章了解了MySQL中都有什么索引后，接下来将继续介绍：

3.1 创建索引的时机 通过前面文章中介绍的索引优缺点可以知道 索引虽好但不能过多使用，接下来将介绍一下什么样的场景才需要创建索引，什么样的场景不适合创建索引。

适合创建索引的场景

不适合创建索引的场景

使用前缀索引替换普通索引

对于字符串类型的字段建立索引时，可以使用前缀索引替换普通索引，这样可以减少索引字段的大小，从而增大数据也存储索引的数量，可以提高索引的查询效率。 使用覆盖索引优化回表次数

对于查询记录不要求全部的，可以考虑建立联合索引，这样使得二级索引的B+树中的叶子节点能找到所有需要查询的数据，这样就不再需要通过主键索引查询整条记录，也就是避免了回表操作，减少I/O次数，从而提高查询效率。 主键索引中主键设置为自增

对于主键索引的主键最好可以设置为自增。 对于使用自增的主键值，在索引的B+树插入新数据时，都是顺序的追加操作，无需移动节点调整树结构，这样的插入效率会变得更高。

而对于使用非自增的主键值，在B+树插入新数据时，可能需要移动其他节点来满足新节点的插入，也可能出现页分裂（当前数据页由于新数据的插入需要将数据页的数据隔开，所以需要新建一张数据页并且把当前数据页的一部分数据复制进新数据页），页分裂可能造成大量的内存碎片从而导致索引结构不稳定，影响查询效率。

建立了索引，是否意味着 所有查询都可以使用索引进行检索数据 呢？ 当然不是，如果无法命中索引（索引失效）时，将会进行全表扫描，此时建立的索引就派不上用场了。 出现索引失效这种情况将会导致性能大大降低，所以需要了解 如何查看索引是否命中 以及一些 常见的索引失效问题，避免出现索引失效这样才能有效地利用索引提高查询效率。

MySQL中通过 explain 查看SQL的执行计划，通过执行计划可以查看是否命中索引。 如下图这样：

执行计划中只需要重点关注 type 、key 、rows 、filtered 、Extra 即可。 type 连接类型（可以看成是扫描方式）

type如下表，扫描方式的 速度（性能）从慢到快。

key 实际使用的索引

表示实际使用到的索引（显示的是索引名，也就是在创建索引时给索引命名的名字）。 rows 扫描的行数

表示MySQL估算找到所需的记录需要扫描的行数，注意这个值只是估算值，并不是一定准确。 filtered 返回的行数

表示符合条件的记录数的百分比（注意这里是百分比值），百分比为 经过过滤后满足条件的记录数 / 存储引擎返回的记录数 。 Extra 额外信息

如果在查询语句的 WHERE 子句中出现 OR 并且 OR 的左右两边的字段中有一个字段不是索引列时，会出现索引失效，进行全表扫描。 如 select * from products where name = 'Apple Watch' or price = 999.99;语句中 name字段为唯一索引列，而 price 只是普通字段时，执行计划如下：

可以看到执行计划中的 type 为 ALL，说明进行了全表扫描，也就说明了对于 name 字段的唯一索引并没有使用，即 索引失效。

如何避免这种情况下的索引失效呢？ 避免这种情况的方法是 OR 的左右两边的字段都需要是索引列。 如 select * from products where name = 'Apple Watch' or id = 1; 就使用了主键索引和唯一索引，这里的执行计划 type 为 index_merge，表示利用两个索引分别检索后合并得到结果。

如果在查询语句中 对索引列使用函数 或者 对索引列进行表达式计算，都会导致索引失效。 如 select * from products where LENGTH(name) = 5; 语句中看似使用了 name 的唯一索引，但是实际上并没有命中索引，而是进行了全表扫描。

如 select * from products where id + 2 = 5; 语句中看似使用了 id 的主键索引，但是实际上并没有命中索引，而是进行了全表扫描。

为什么会出现这种情况呢？

因为 索引的B+树结构中索引键是原始的索引值（没有经过计算或函数的），如果经过函数或者表达式计算之后自然就无法在B+树结构找到对应的索引键了，那么就自然无法通过索引来检索到记录了。

如何避免这种情况呢？

其实只需要对代码做一些转换，像 id + 2 = 5 转换为 id = 5 - 2，from_unixtime(create_time) = '2023-3-22'转换为 create_time = unix_timestamp('2023-3-22') 这样即可实现索引命中，也就是让索引列比较时变得“干净些”。 如 select * from products where id = 5 - 2; 即可避免索引失效问题。

但是还是比较建议如果 需要进行计算的字段就不必再建立索引 了，因为当面对一些较为复杂的函数时无法做到的两边转换。

如果在查询语句中使用 like 模糊匹配时，对于 like %XXX 和 like %XXX% 这样的形式模糊匹配时是无法命中索引（索引失效）的。而对于 like% 这样的形式是可以命中索引进行索引检索的。 如 select * from products where name like '%Pro';语句中执行计划的 type 为 ALL，表明这次执行是全表扫描。

如 select * from products where name like 'Apple%';语句中执行计划的 type 为 range，表明这次执行是索引范围查询。

为什么会出现这样的情况呢？

由于索引的B+树的索引键是有序的，根据指定的值和索引键比较，最后找到所需的记录，在模糊查询时会根据指定的值 按照前缀的方式 比较索引键来找到所需的记录。 对于像 Apple% 这样的值因为前面有明确的 A，所以完全可以使用索引快速缩小范围，进行索引范围查询。 而对于像 %Pro 或者 %Pro% 这类的值因为前面不具有明确的值，可以是任意值，这样索引就不知道从哪里开始检索，无法快速缩小范围，只能选择全表扫描，也就会导致索引失效。

如果在查询语句时，对字符串类型的索引列的输入参数类型为整型时，会导致索引失效。 如 select * from products where stockNo = 1001; 中将为 varchar 类型的 stockNo比较时输入参数为整型 1001 时，虽然可以找出记录，但是使用的却是全表扫描，说明索引失效。

出现这种情况的原因

MySQL的隐式转换：当遇到字符串和数字进行比较时，会自动将字符串转为数字进行比较。 知道这个原则后，就比较好解释了，上面的例子中在 字符串类型的 stockNo 和数字类型的 1001比较时，会自动把 stockNo转换成数字，但是因为 隐式转换仍然是需要使用函数进行转换的，也就是说需要对 stockNo 索引列使用函数，所以自然无法使用索引检索了，这就导致索引失效。 也就是说如果例子为 select * from products where id = '2'; 时，因为 id 是主键索引并且是整型，此时输入参数是字符串类型，但是通过隐式转换后会变为 id = 2，所以并不影响主键索引的使用，也就不会导致索引失效。

如果在查询数据时使用联合索引进行检索数据时，需要注意按照最左优先的方式进行索引的匹配（遵循最左前缀原则）。 现在创建一个联合索引 (a, b, c) ，此时的索引结构是以 a 为全局有序， b、c 为局部相对有序。 如果此时的查询为：where b = 2 and c = 3 或者 where b = 2 或者 where c = 3 会出现索引失效的问题

之所以出现这样的情况是因为查询中并没有遵守最左前缀原则，无法匹配到 a 字段，所以无法命中联合索引，导致索引失效问题。

对于 where a = 1 and c = 3 这种情况会出现索引失效吗？

答案是不会。 在没有出现 索引下推 前，这条语句的执行过程是 先去匹配 a 字段的索引键找到所有符合条件的主键后，通过回表操作将这些主键值经过主键索引找到对应的数据行，并且把这些数据都返回给Server层，然后再通过Server层的 where 比较 c 的值，过滤掉不符合的条件。

在出现 索引下推 后，这条语句的执行过程是 先去匹配 a 字段的索引键找到所有符合条件的主键时，因为 c字段也在索引中，所以可以在存储引擎层就对 c 字段进行比较，过滤掉不符合 c字段的值，然后再将剩余符合条件的主键进行回表操作，通过主键索引找到对应的数据返回给Server层。 通过这个例子也可以巩固之前文章中 索引下推 的定义，索引下推减少了回表的数量，提高了查询效率。

因为当前使用的是MySQL5.7所以这里的执行计划可以的Extra中的值为Using index condition，也就是索引下推使用的标识，同时也可以看到并没有出现索引失效。

为什么不遵守最左前缀原则就无法命中索引呢？

因为在联合索引中，数据是按照索引第一列排序，在第一列相同的情况下才按照第二列进行排序，也就是最左边的列是全局有序的，而其余的列都是局部相对有序的，查询时如果连最左的列都无法匹配到，那么自然无法使用索引。 想要更多的列的使用到 联合索引，必须保证从最左边开始能连续匹配到对应列，像 where a = XXX and c = XXX这种使用索引只能缩小范围到 a 字段，无法快速检索 c 字段，只能通过遍历进行 c 字段的比较。