MySQL学习笔记之索引篇

站长

2023年03月24日 22:27 · 阅读数 60

1. MySQL的索引是什么？

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

索引的好处在于：

可以大大加快数据的检索速度（减少检索的数据量），降低数据库的IO成本。
加快分组和排序的速度。
保证行的唯一性，增强数据完整性。

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

创建和维护索引需要耗费一定时间，对数据进行插入、删除、修改时增加了时间开销，降低SQL执行效率。
索引需要使用物理空间存储即需要占用一定的磁盘空间。

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

2. 索引的分类

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

按 数据结构 分类可分为：B+树索引、Hash索引、全文索引等
按 物理存储 分类可分为：聚簇索引（主键索引）、非聚簇索引（二级索引）等
按 逻辑特性 分类可分为：主键索引、普通索引、唯一索引、前缀索引、联合索引（多个字段）等

2.1 为什么使用B+树作为索引？

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+树结构图：

MySQL学习笔记之索引篇

阐述一下B+树的优点：

B+树是一种基于磁盘的 平衡多路查找树，它的高度通常很低（3~4层），这意味着访问效率很高，从千万或上亿数据里查询一条数据，只用 3到4 次 I/O操作。
B+树的 非叶子节点不存储实际的数据，只存储索引，相比于非叶子节点又存储索引又存储数据的B树而言，B+树的非叶子节点可以存放更多的索引，所以查询底层节点的磁盘I/O次数会更少。
B+树的所有叶子节点都存储了数据，并且叶子节点之间用双向链表连接，所以数据是有序的，范围查询通过链表可以快速实现，这样可以方便地进行范围查询和排序操作。
B+树每个节点都有大量的数据或索引（有大量冗余的节点），这些冗余的数据或索引可以保证B+树在插入和删除时效率更高（因为不会因为某个数据导致需要进行复杂的树变化，即B+树的树层结构很稳定不会经常变化）。

相比于其他数据结构：

相对于数组来说，虽然使用数组＋二分查找的方式实现线性排序更为简单，但是相比于B+树，B+树具备更高效的插入或删除元素的能力。
相对于 二叉查找树或平衡二叉树 来说，虽然平衡二叉树具备平衡树高和高效地维护元素地能力，但是相比于B+树，由于二叉树始终只能二分，随着元素地增加，树高会越来越大，B+树具备更矮的树高，即对I/O操作的次数更少，所以具备更高效地查找效率。
相对于 B树来说，因为B树需要使用中序遍历的方式进行范围查询，增加了I/O次数。而由于B+树的叶子节点使用双向链表所以可以更高效地进行范围查询，具备给更高的范围查询效率。

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

2.2 聚簇索引和非聚簇索引的区别？

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

聚簇索引是指将 数据存储与索引放到了一块，叶子节点存储索引和索引对应的数据；聚簇索引的顺序就是数据的物理存储顺序，聚簇索引可以把相关数据保存在一起，提高查询效率；但也会增加插入和更新的开销，以及占用更多的空间。
非聚簇索引是指将 数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置；非聚簇索引的顺序与数据物理排列顺序无关

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

主键索引

主键索引是建立在主键字段上的索引，主键索引的B+树结构中的叶子节点存放的是实际数据（所有完整的记录）。

关于InnoDB引擎确定 聚簇索引（主键索引） 的方式按照如下次序：

如果表中有主键，默认使用主键作为聚簇索引（主键索引）的索引键
如果表中没有主键，则选择 第一个不包含NULL值的唯一列 作为聚簇索引（主键索引）的索引键
如果2的情况也不存在，则存储引擎会 自动生成一个隐式自增id 列作为聚簇索引（主键索引）的索引键

二级索引

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

唯一索引（UNIQUE）：唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表可以允许创建多个唯一索引。
普通索引（INDEX）：建立在普通字段上的索引，一张表可以允许多个普通索引，并允许数据重复和数据为NULL。
前缀索引：前缀索引只能建立在字符串类型（char、varchar、binary、varbinary）等类型上。创建方式可以是CREATE INDEX 索引名 ON 表名(列名(指定前缀长度)); ，前缀索引的目的是为了减少索引占用的存储空间，提高查询效率。

2.3 回表/覆盖索引/索引下推

什么是回表？

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

二级索引查找记录的过程为：先获得二级索引中的B+树的索引值，检索 二级索引的B+树 找到对应的叶子节点后获取到对应的主键值，再通过主键值检索 主键索引的B+树 找到对应的叶子节点即可获得对应的数据（记录）。第一次检索获取主键值，再通过主键值再次检索获得记录的这个过程叫做回表。

举个例子：

select * from products where name = 'iPhone 14';

MySQL学习笔记之索引篇

如上面的代码，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层 之后再进行判断。

后面的索引失效的联合索引问题中会使用具体例子说明。

2.4 联合索引

联合索引：由 多个字段 组合成一个索引。

语法：CREATE INDEX 索引名 ON 表名(列名1, 列名2, ...);

通过例子阐述联合索引

当前数据有：

MySQL学习笔记之索引篇

通过 CREATE INDEX index_stockNo_stock ON products(stockNo, stock); 创建 stockNo 和 stock 的联合索引。

此时联合索引的B+树是这样的：

MySQL学习笔记之索引篇

可以看到B+树是先按照 stockNo 字段进行排序，再 stockNo 字段相同的情况况下再按照 stock 字段进行排序。

所以 stockNo 字段是 全局有序 的，而 stock 字段是 全局无序，但局部有序 的。也就是说在创建联合索引时，创建顺序为(a, b, c)，那么对应的就是 a 全局有序，b、c 局部相对有序。

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

首先需要明确的一点是：利用索引的前提是 索引数据结构中的索引键是有序的，否则就需要遍历查找。

如果通过 select * from products where stockNo = 1003 and stock = 80; 查询数据，是可以利用到索引进行检索。（注意 in 和 = 可以乱序，因为有查询优化器所以stockNo字段在where子句的顺序不重要）

MySQL学习笔记之索引篇

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

MySQL学习笔记之索引篇

这是因为前面提到的 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是可以匹配到的，具体在索引失效中介绍）。

3. 索引的创建时机和优化

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

索引的创建时机，什么场景需要 / 不需要 创建索引。
怎么 优化索引，有什么方式。

3.1 创建索引的时机

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

适合创建索引的场景

不为NULL的字段：对于数据为NULL的字段，数据库较难优化，字段被频繁查询时，避免不了为NULL。
频繁用于where子句查询的字段：经常用于条件查询的字段可以建立索引，这样能提高查询的速度。
经常排序的字段：经常被oreder by 或者 group by 使用的字段可以建立索引，这样在查询时就无需再做一次排序。
被经常用于连接的字段：经常用于连接的字段涉及到表与表的关系，对于频繁被连接查询的字段，可以建立索引，提高多表连接查询的效率。
尽可能地创建联合索引：相对于创建的单一索引来说，如果适合创建联合索引应当选择建立联合索引，因为联合索引多个字段在一个索引上，可以很大程度地减少磁盘占用的空间，提高维护索引效率。
尽可能地创建前缀索引：对于字符串类型的字段，创建前缀索引相对于创建普通索引来说，能节约磁盘占用空间。

不适合创建索引的场景

频繁需要更新的字段：对于需要经常更新的字段不适合建立索引，因为字段频繁被修改，而B+树需要维护索引键的有序性，所以频繁更新的字段会增加维护索引的成本，很影响数据库的性能。
表中数据量过少：表数据太少时无需创建索引，因为全表扫描也不会很慢，无需以空间换时间。
冗余索引：比如已经有联合索引(a, b, c)，则自然无需为字段 a 单独建立索引，此时单独建立字段 a 索引就是冗余索引。
较低区分度（重复数据过多）的字段：较低区分度的字段很容易导致表中出现过多的重复数据，此时建立索引已经无法起到优化作用了，因为查询优化器在对比成本后会直接跳过索引选择全表扫描（索引失效）。
需要参与计算的字段：索引列不能参与计算，因为B+树中存储的都是原始的字段值，如果需要参与计算的话，可能会导致索引失效（具体后面介绍）。虽然可以把 a + 1 = 5 这样的表达式变为 a = 5 - 1使得索引不失效，但针对一些较为复杂的表达式，无疑会增加查询成本，所以不建议在需要计算的字段上建立索引。

3.2 索引优化的方式

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

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

使用覆盖索引优化回表次数

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

主键索引中主键设置为自增

对于主键索引的主键最好可以设置为自增。

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

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

4. 索引失效问题

建立了索引，是否意味着 所有查询都可以使用索引进行检索数据 呢？

当然不是，如果无法命中索引（索引失效）时，将会进行全表扫描，此时建立的索引就派不上用场了。

出现索引失效这种情况将会导致性能大大降低，所以需要了解 如何查看索引是否命中 以及一些 常见的索引失效问题，避免出现索引失效这样才能有效地利用索引提高查询效率。

4.1 如何查看是否命中索引？

MySQL中通过 explain 查看SQL的执行计划，通过执行计划可以查看是否命中索引。

如下图这样：

MySQL学习笔记之索引篇

执行计划中只需要重点关注 type 、key 、rows 、filtered 、Extra 即可。

type 连接类型（可以看成是扫描方式）

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

类型	说明
ALL	全表扫描
index	全索引扫描，与ALL差不多，好处在于无需对数据进行排序
range	索引范围查询，一般用于`where`子句中使用`<`、`>`、`between`、`in`。
index_merge	采用索引合并优化方式，使用了两个以上的索引。
ref	非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。虽然使用了索引，但该索引列的值并不唯一，有重复。
eq_ref	主键或唯一索引，一般用于多表联查中。
const	使用了主键或者唯一索引与常量值进行比较，只通过一次索引就能获得数据。

key 实际使用的索引

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

rows 扫描的行数

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

filtered 返回的行数

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

Extra 额外信息

值	说明
Using filesort	表示按文件排序，一般在指定的排序和索引排序不一致的情况下出现如 `group by` 或 `order by` 。
Using temporary	表示使用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，性能特别差需要优化，常见于排序 `order by` 和分组查询 `group by`。
Using index	表示是否使用了覆盖索引。
Using index condition	MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。
Using Where	使用了`where`条件过滤

4.2 常见的索引失效问题

4.2.1 `WHERE` 子句出现 `OR`

如果在查询语句的 WHERE 子句中出现 OR 并且 OR 的左右两边的字段中有一个字段不是索引列时，会出现索引失效，进行全表扫描。

如 select * from products where name = 'Apple Watch' or price = 999.99;语句中 name字段为唯一索引列，而 price 只是普通字段时，执行计划如下：

MySQL学习笔记之索引篇

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

如何避免这种情况下的索引失效呢？

避免这种情况的方法是 OR 的左右两边的字段都需要是索引列。

如 select * from products where name = 'Apple Watch' or id = 1; 就使用了主键索引和唯一索引，这里的执行计划 type 为 index_merge，表示利用两个索引分别检索后合并得到结果。

MySQL学习笔记之索引篇

4.2.2 索引字段使用函数或进行表达式计算

如果在查询语句中 对索引列使用函数 或者 对索引列进行表达式计算，都会导致索引失效。

如 select * from products where LENGTH(name) = 5; 语句中看似使用了 name 的唯一索引，但是实际上并没有命中索引，而是进行了全表扫描。

MySQL学习笔记之索引篇

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

MySQL学习笔记之索引篇

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

因为 索引的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; 即可避免索引失效问题。

MySQL学习笔记之索引篇

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

4.2.3 `like` 通配符模糊查询

如果在查询语句中使用 like 模糊匹配时，对于 like %XXX 和 like %XXX% 这样的形式模糊匹配时是无法命中索引（索引失效）的。而对于 like% 这样的形式是可以命中索引进行索引检索的。

如 select * from products where name like '%Pro';语句中执行计划的 type 为 ALL，表明这次执行是全表扫描。

MySQL学习笔记之索引篇

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

MySQL学习笔记之索引篇

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

由于索引的B+树的索引键是有序的，根据指定的值和索引键比较，最后找到所需的记录，在模糊查询时会根据指定的值 按照前缀的方式 比较索引键来找到所需的记录。

对于像 Apple% 这样的值因为前面有明确的 A，所以完全可以使用索引快速缩小范围，进行索引范围查询。

而对于像 %Pro 或者 %Pro% 这类的值因为前面不具有明确的值，可以是任意值，这样索引就不知道从哪里开始检索，无法快速缩小范围，只能选择全表扫描，也就会导致索引失效。

4.2.4 索引字段隐式转换问题

如果在查询语句时，对字符串类型的索引列的输入参数类型为整型时，会导致索引失效。

如 select * from products where stockNo = 1001; 中将为 varchar 类型的 stockNo比较时输入参数为整型 1001 时，虽然可以找出记录，但是使用的却是全表扫描，说明索引失效。

MySQL学习笔记之索引篇

出现这种情况的原因

MySQL的隐式转换：当遇到字符串和数字进行比较时，会自动将字符串转为数字进行比较。

知道这个原则后，就比较好解释了，上面的例子中在字符串类型的 stockNo 和数字类型的 1001比较时，会自动把 stockNo转换成数字，但是因为 隐式转换仍然是需要使用函数进行转换的，也就是说需要对 stockNo 索引列使用函数，所以自然无法使用索引检索了，这就导致索引失效。

也就是说如果例子为 select * from products where id = '2'; 时，因为 id 是主键索引并且是整型，此时输入参数是字符串类型，但是通过隐式转换后会变为 id = 2，所以并不影响主键索引的使用，也就不会导致索引失效。

MySQL学习笔记之索引篇

4.2.5 联合索引不匹配问题

如果在查询数据时使用联合索引进行检索数据时，需要注意按照最左优先的方式进行索引的匹配（遵循最左前缀原则）。

现在创建一个联合索引 (a, b, c) ，此时的索引结构是以 a 为全局有序， b、c 为局部相对有序。

如果此时的查询为：where b = 2 and c = 3 或者 where b = 2 或者 where c = 3 会出现索引失效的问题

MySQL学习笔记之索引篇

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

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

答案是不会。

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

在出现 索引下推 后，这条语句的执行过程是先去匹配 a 字段的索引键找到所有符合条件的主键时，因为 c字段也在索引中，所以可以在存储引擎层就对 c 字段进行比较，过滤掉不符合 c字段的值，然后再将剩余符合条件的主键进行回表操作，通过主键索引找到对应的数据返回给Server层。

通过这个例子也可以巩固之前文章中 索引下推 的定义，索引下推减少了回表的数量，提高了查询效率。

MySQL学习笔记之索引篇

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

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

因为在联合索引中，数据是按照索引第一列排序，在第一列相同的情况下才按照第二列进行排序，也就是最左边的列是全局有序的，而其余的列都是局部相对有序的，查询时如果连最左的列都无法匹配到，那么自然无法使用索引。

想要更多的列的使用到 联合索引，必须保证从最左边开始能连续匹配到对应列，像 where a = XXX and c = XXX这种使用索引只能缩小范围到 a 字段，无法快速检索 c 字段，只能通过遍历进行 c 字段的比较。

转载自:https://juejin.cn/post/7213296062084546616

MySQL学习笔记之索引篇

1. MySQL的索引是什么？

2. 索引的分类

2.1 为什么使用B+树作为索引？

2.2 聚簇索引和非聚簇索引的区别？

2.3 回表/覆盖索引/索引下推

2.4 联合索引

3. 索引的创建时机和优化

3.1 创建索引的时机

3.2 索引优化的方式

4. 索引失效问题

4.1 如何查看是否命中索引？

4.2 常见的索引失效问题

4.2.1 WHERE 子句出现 OR

4.2.2 索引字段使用函数或进行表达式计算

4.2.3 like 通配符模糊查询

4.2.4 索引字段隐式转换问题

4.2.5 联合索引不匹配问题

4.2.1 `WHERE` 子句出现 `OR`

4.2.3 `like` 通配符模糊查询