【MySQL 系列】MySQL 索引篇

文章目录

1、MySQL 索引的介绍

1.1、索引目的

索引的目的在于提高查询效率，可以类比字典，比如当我们要查 “mysql” 这个单词，我们肯定需要定位到 ‘m’ 字母，然后从下往下找到 ‘y’ 字母，再找到剩下的 “sql”。如果没有索引，那么我们可能需要把所有单词看一遍才能找到想要的。

在 MySQL 中，索引是一种帮助存储引擎快速获取数据的数据结构，形象的说就是索引是数据的目录。它一般是以包含索引键值和一个指向索引键值对应数据记录物理地址的指针的节点的集合的清单的形式存在。通过使用索引， MySQL 可以在不需要扫描整个表的情况下快速找到与查询条件匹配的记录。

1.2、索引原理

在 MySQL 索引设计中，核心目标是有效平衡数据检索的速度与存储效率。就像图书目录帮助我们快速找到特定章节，MySQL索引使数据库能迅速定位数据。但数据库的挑战更为复杂，因为它需要处理各种查询类型，如等值查询、范围查询和模糊查询等。

为了有效平衡数据检索速度与存储效率，MySQL 通过其 InnoDB 存储引擎采取了以下具体措施：

B+ 树索引结构：① 高效范围查询：InnoDB 索引使用 B+树数据结构，其平衡树特性保证了即使在大量数据中也能保持较低的查询深度，特别是对于范围查询，可以快速通过叶节点链表遍历相关数据；② 优化读写性能：B+ 树的结构减少了节点分裂的频率，保持了树的平衡，从而提高了读写操作的效率；
聚簇索引设计：① 直接存储数据：InnoDB 使用聚簇索引，其中表数据直接存储在索引的叶节点上。这意味着数据物理顺序与键值顺序一致，优化了顺序访问的性能；② 减少I/O操作：通过聚簇索引，查询主键时直接定位到数据，无需额外的数据指针跳转，从而减少了磁盘 I/O 操作；
数据页及预读机制：① 数据页单位操作：InnoDB 以数据页为基本的 I/O单位（默认 16 KB），这比单条记录的读写更高效，因为一次 I/O 可以加载多条记录到内存；② 预读优化：利用操作系统的预读特性，InnoDB 预测并提前加载可能访问的数据页到内存，减少了未来的I/O需求，尤其在顺序访问模式下效果显著；
自适应哈希索引：内存级索引加速：当某些数据页被频繁访问时，InnoDB 会在内存中自动构建哈希索引来加速这些数据页的访问，进一步减少了数据查找时间；
写入缓冲与日志：写入合并：使用写入前日志（Write-Ahead Logging, WAL）和更改缓冲区（Change Buffer）技术， InnoDB 能够合并多个写入操作，减少对磁盘的直接写入，优化了写操作的性能。

MySQL 通过上述措施，在保证数据检索速度的同时，优化存储效率和减少磁盘I/O成本，实现了数据管理的高效平衡。这些技术不仅提高了查询性能，也保证了数据的安全性和一致性。

2、MySQL 索引的数据结构

从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。

2.1、B+ 树索引

MySQL 索引的实现采用的是 B+ 树，B+ 树是 B- 树的变体，也是一棵多路搜索树。B+ 树相较于 B- 树最主要的特点是：数据只出现在叶子节点；所有叶子节点增加了一个链指针。

【MySQL 系列】MySQL 索引篇-LMLPHP

在 B+ Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+ 树的高度。

B+ 树的叶子节点上有指针进行相连，因此在做数据遍历的时候，只需要对叶子节点进行遍历即可，这个特性使得 B+ 树非常适合做范围查询。

默认情况下，如果不指定索引类型，MySQL 将创建 B+ 树索引。下面显示了基于表的存储引擎允许的索引类型：

2.2、为什么是 B+ 树？

为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构呢？

MySQL 选择使用 B+树作为索引结构，主要是因为 B+树提供了许多适合数据库索引的优点：

高效的查找和范围查询性能：B+树的结构使得查找操作非常高效。所有的叶节点都按键值的顺序存储，并且相互链接，这使得对于范围查询（如找出所有在某个值范围内的记录）特别高效。
节省磁盘空间：在 B+树中，只有叶节点包含数据指针或实际的数据值，而内部节点只存储键值。这样的设计减少了内部节点所需的空间，使得更多的键值可以存储在一个节点中，从而减少了磁盘I/O次数。
优化磁盘I/O操作：数据库系统常常运行在存储数据的磁盘驱动器上。B+树的结构减少了节点分裂的频率，并且由于叶节点是顺序访问的，所以它们特别适合磁盘的顺序读取特性。
更好的缓存利用性：由于内部节点不包含实际数据，而只包含键值，这意味着更多的键值可以被缓存在内存中，从而减少访问磁盘的需要。
支持顺序和随机访问：B+树通过其叶节点的链表结构支持高效的顺序访问，同时也支持随机数据访问。
写操作的性能：B+树减少了因插入或删除操作而导致的树重新平衡的频率，这在频繁更新的数据库环境中是一个重要的优势。

2.2.1、B+Tree vs Hash

Hash 在做等值查询的时候效率很高，搜索时间复杂度为 O(1)。但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

2.2.2、B+Tree vs 二叉树

对于有 N 个叶子节点的 B+Tree，其搜索复杂度为 O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

在实际的应用当中， d 值是大于 100 的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。

而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

并且如果二叉树受插入顺序影响特殊化为一个链表，相当于全表扫描。

平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。但是平衡二叉树可是每个节点只存储一个键值和数据的，并且每次新增数据，平衡二叉树都会进行大量的平衡判断。

2.2.3、B+Tree vs B Tree

B+Tree 只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

另外，B+Tree 叶子节点采用的是双向链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

3、MySQL 数据页

3.1、MySQL 数据页结构

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

因此，InnoDB 的数据是按「数据页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

数据库的 I/O 操作的最小单位是页，InnoDB 数据页的默认大小是 16KB，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

数据页包括七个部分，结构如下图：

【MySQL 系列】MySQL 索引篇-LMLPHP

这 7 个部分的作用如下：

文件头（File Header）：文件头，表示页的信息；
页头（Page Header）：页头，表示页的状态信息；
最小（Infimum）、最大记录（supermum）：两个虚拟的伪纪录，分别表示页中的最小记录和最大记录；
用户记录（User Records）：存储行记录内容；
空闲空间（Free Space）：页中还没有被使用的空间；
页目录（Free Space）：存储用户记录的相对位置，对记录起到索引作用；
文件尾（File Tailer）：校验页是否完整。

在 File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向的链表，如下图所示：

【MySQL 系列】MySQL 索引篇-LMLPHP

采用链表的结构是让数据页之间不需要是物理上的连续的，而是逻辑上的连续。

3.2、User Records 如何组织数据

数据页中的记录按照「主键」顺序组成单向链表，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。

因此，数据页中有一个页目录，起到记录的索引作用，就像我们书那样，针对书中内容的每个章节设立了一个目录，想看某个章节的时候，可以查看目录，快速找到对应的章节的页数，而数据页中的页目录就是为了能快速找到记录。

那 InnoDB 是如何给记录创建页目录的呢？页目录与记录的关系如下图：

【MySQL 系列】MySQL 索引篇-LMLPHP

页目录创建的过程如下：

将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为 “已删除” 的记录；
每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）
页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

从图可以看到，页目录就是由多个槽组成的，槽相当于分组记录的索引。然后，因为记录是按照「主键值」从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。

4、MySQL 聚簇索引

4.1、什么是聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据；

InnoDB 里的 B+ 树中的每个节点都是一个数据页，结构示意图如下：

【MySQL 系列】MySQL 索引篇-LMLPHP

以上图为例子，实现快速查找主键为 6 的记录，：

从根节点开始，通过二分法快速定位到符合页内范围包含查询值的页，因为查询的主键值为 6，在 [1, 7) 范围之间，所以到页 30 中查找更详细的目录项；
在非叶子节点（页30）中，继续通过二分法快速定位到符合页内范围包含查询值的页，主键值大于 5，所以就到叶子节点（页16）查找记录；
接着，在叶子节点（页16）中，通过槽查找记录时，使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到主键为 6 的记录。

可以看到，在定位记录所在哪一个页时，也是通过二分法快速定位到包含该记录的页。定位到该页后，又会在该页内进行二分法快速定位记录所在的分组（槽号），最后在分组内进行遍历查找。

4.2、聚簇索引列的选择

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

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

如果有主键，默认会使用主键作为聚簇索引的索引键；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键；

4.2、非聚簇索引和二级索引

一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据。

二级索引的 B+ 树如下图，数据部分为主键值：

【MySQL 系列】MySQL 索引篇-LMLPHP

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

5、其他索引的分类

索引的分类，可以根据角度的不同来分，在前面的内容，我们已经了解到了按 “数据结构（Hash 索引、B+ Tree 索引）” 以及按 “物理存储（聚合索引、二级索引）” 两种角度的索引分类，那么MySQL 中还存在着哪些形式的索引呢。

5.1、按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

5.1.1、主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

在创建表时，创建主键索引的方式如下：

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

5.1.2、唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

在创建表时，创建唯一索引的方式如下：

CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

建表后，如果要创建唯一索引，可以使用这面这条命令：

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

5.1.3、普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

在创建表时，创建普通索引的方式如下：

CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

建表后，如果要创建普通索引，可以使用这面这条命令：

CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

5.1.4、前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

在创建表时，创建前缀索引的方式如下：

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);

建表后，如果要创建前缀索引，可以使用这面这条命令：

CREATE INDEX index_name
ON table_name(column_name(length));

5.2、联合索引（按字段个数分类）

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

建立在单列上的索引称为单列索引，比如主键索引；
建立在多列上的索引称为联合索引；

通过将多个字段组合成一个索引，该索引就被称为联合索引。

比如，将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)，创建联合索引的方式如下：

CREATE INDEX index_product_no_name ON product(product_no, name);

联合索引范围查询：

联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的 B+Tree，部分字段没有用到联合索引的 B+Tree 的情况。

这种特殊情况就发生在范围查询。联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

最左前缀匹配原则：在 MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。比如我们配置了一个 A、B、C 三个字段的联合索引，我们用 A、AB、ABC 的方式都是可以走到联合索引的，但如果是 AC、BC、C 的这种情况则不会使用索引。

6、索引的使用

6.1、创建索引

MySQL 允许我们使用 CREATE INDEX 语句在指定的表上为指定的列创建索引

CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

说明：① UNIQUE 关键字表明此索引为唯一索引，它是可选的；② index_name 是索引的名字。一个表中不应该出现两个相同名字的索引；③ table_name 是表的名字；④ column_list 是表中的列名。多个列名使用逗号分隔；⑤ USING 子句指定索引的类型。可选值：BTREE，HASH。它是可选的 ⑥ algorithm_option 指定删除索引的算法；⑦ lock_option 指定删除索引的并发控制策略。

6.1.1、删除索引的算法

其中 algorithm_option 它使用以下的语法：

ALGORITHM [=] {DEFAULT | INPLACE | COPY}

ALGORITHM 子句是可选的。默认为 INSTANT。如果不支持 INSTANT，则使用 INPLACE。

使用 DEFAULT 和省略 ALGORITHM 子句效果相同。

以下是对各个算法的说明：

COPY：对原表的副本进行操作，将原表中的表数据逐行复制到新表中。不允许并发 DML。
INPLACE: 操作避免复制表数据，但可能会就地重建表。在操作的准备和执行阶段，可能会短暂地对表进行独占元数据锁定。通常，支持并发 DML。
INSTANT: 操作只修改数据字典中的元数据。在操作的执行阶段，可能会短暂地对表进行独占元数据锁定。表数据不受影响，使操作瞬间完成。允许并发 DML。（在 MySQL 8.0.12 中引入）

lock_option 指定删除索引的并发控制策略。它使用以下的语法：

LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

6.1.2、删除索引的并发控制策略

LOCK 子句是可选的。以下是对各个并发策略的说明：

DEFAULT

给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的最大并发级别：如果支持，则允许并发读取和写入。如果不是，则允许并发读取（如果支持）。如果不是，则强制执行独占访问。
NONE

如果支持，允许并发读取和写入。否则，会发生错误。
SHARED

如果支持，允许并发读取但阻止写入。即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发写入，写入也会被阻止。如果不支持并发读取，则会发生错误。
EXCLUSIVE

强制执行独占访问。即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发读/写，也会这样做。

在 MySQL 内部，CREATE INDEX 语句被映射为 ALTER TABLE ... ADD INDEX ... 语句。

6.2、删除索引

在 MySQL 数据库中，我们可以使用 DROP INDEX 从表中删除已有的索引。

DROP INDEX index_name
ON table_name
[algorithm_option | lock_option];

6.3、显示索引

在 MySQL 中，我们可以使用 SHOW INDEXES 命令从表中查询索引信息

SHOW INDEXES FROM db_name.table_name;

或者

SHOW INDEXES FROM table_name IN db_name;

6.4、指定索引

MySQL 查询优化器是 MySQL 数据库服务器的一个组件，它为 SQL 语句制定最佳执行计划。 MySQL 优化器通常根据索引基数进行决策。有时候，虽然你创建了索引，但是你的 SQL 语句却不一定使用索引。这是因为 MySQL 查询优化器的做出了它认为的更优的选择。

MySQL 允许我们使用 USE INDEX 语句建议查询优化器去使用指定的命名索引。

SELECT column_list
FROM table_name
# 如果 MySQL 查询优化器要使用索引，则必须使用索引列表 index_list 中的一个索引
USE INDEX (index_list)
WHERE condition;

同样的有时候，虽然我们创建了索引，但是我们的 SQL 语句却不一定使用索引。这是因为 MySQL 查询优化器的做出了它认为的更优的选择。

这里我们可以是使用 FORCE INDEX 子句告诉 MySQL 查询优化器必须使用指定的索引

SELECT *
FROM table_name
FORCE INDEX (index_list)
WHERE condition;

7、索引的失效

在有些时候因为使用上的一些瑕疵就会导致索引的失效，无法达到我们使用索引的预期效果，下面介绍几种索引失效的场景：

列于列的对比：例如：某个表中，有两列 id 和 c_id 都建了单独索引，Where 条件后为 id=c_id，这种情况会被认为还不如走全表扫描；
存在 Null 值条件：如果索引列是可空的，是不会给其建索引的；
存在 Not 条件：当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描;
Like 通配符：前匹配的情况下，执行计划会更倾向于选择全表扫描。后匹配可以走 INDEX RANGE SCAN。所以业务设计的时候，尽量考虑到模糊搜索的问题，要更多的使用后置通配符；
条件上包括函数：查询条件上尽量不要对索引列使用函数，因为索引在建立时会和计算后可能不同，无法定位到索引。但如果查询条件不是对索引列进行计算，那么依然可以走索引；
复合索引前导列区分大：当复合索引前导列区分小的时候，我们有 INDEX SKIP SCAN，当前导列区分度大，且查后导列的时候，前导列的分裂会非常耗资源，执行计划想，还不如全表扫描来的快，然后就索引失效了；
数据类型的转换：当查询条件存在隐式转换时，索引会失效。比如在数据库里 id 存的 number 类型，但是在查询时，却用了下面的形式：select * from sunyang where id='123';
使用非最左前缀查询：若索引是多列的（比如(a, b, c)），查询条件不是以索引的最左列开始，则索引可能不会被使用。例如，若只有b或c作为查询条件，而没有a，则索引可能不生效。

8、索引的设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能差生障碍，设计高效的索引需要遵循一些原则和最佳实践，以确保查询性能的同时避免不必要的资源消耗。以下是索引设计的一些核心原则：

最左前缀原则：对于复合索引，确保查询条件能够利用索引的“最左前缀”。这意味着，查询条件应该从复合索引的第一个字段开始匹配，并且按照索引字段的顺序进行；
选择性原则：优先为具有高选择性的列创建索引。选择性是指列中唯一值的比例，唯一值越多的列（接近列的总行数），选择性越高，作为索引时效果越好；
避免冗余和重复索引：检查并避免创建冗余（完全相同的索引）或重复（一个索引是另一个索引前缀的）索引，因为这会增加额外的维护成本和空间消耗，而不会带来任何查询性能的提升；
考虑查询模式：根据应用的查询模式（如等值查询、范围查询、排序、分组等）设计索引。例如，如果经常根据某个字段排序，可以考虑在该字段上创建索引；
控制索引数量：虽然索引可以提高查询性能，但每个额外的索引都会增加写操作（INSERT、UPDATE、DELETE）的成本。因此，应该避免在低选择性的列上创建索引，同时根据实际需要合理控制索引的总数量；
考虑索引覆盖：如果一个查询可以通过访问索引就能获取所需的全部数据，那么这个索引被称为“覆盖索引”。设计时尽可能让索引覆盖更多查询，可以显著减少对磁盘的访问次数，提高查询效率；
使用前缀索引以节约空间：对于长文本字段，可以考虑使用字段的前缀作为索引。前缀索引可以节省索引空间，降低维护成本，但需要权衡前缀长度和查询效率；
索引维护：定期维护索引，包括重建或优化索引，以确保索引结构的效率。随着数据的增加和变化，索引可能会变得碎片化。

遵循这些设计原则可以帮助开发者和数据库管理员创建出既能满足查询需求又能优化性能的索引策略。正确的索引策略能够在提升查询性能和控制资源消耗之间找到一个平衡点。

栗筝i