MySQL索引（一）—— 索引介绍

╰+攻爆jí腚メ 2022-12-17 01:15 175阅读 0赞

# 一、索引 #

索引是什么？打个比方来说，我们要从一本字典中查一个字，我们怎么查？首先我们根据这个字的读音，在目录找到这个字所在的页码，然后再根据页码，找到这个字。目录的好处就是我们能够快速的查到这个字，索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响。要找到一个合适的平衡点，这对应用程序的性能至关重要。

在InnoDB存储引擎中，支持以下几种常见的索引：

*  B+树索引
 *  全文索引
 *  哈希索引

# 二、B+树索引 #

MySQL中B+树索引可以分为聚集索引（clustered inex）和辅助索引（secondary Index），叶子节点存放着所有的数据。聚集索引也称聚簇索引，聚集索引叶子节点存放的是整行数据，而辅助索引叶子节点存放的是主键的值，聚集索引（clustered index）也被称为主键索引，辅助索引也被称为二级索引、非主键索引或非聚集索引。

B+树索引是最为常见，也是在数据库中使用最为频繁的一种索引。B+树索引的构造类似于二叉树，根据键值（Key Value）快速找到数据。B+树中的B代表平衡（balance），因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。

B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

B+树索引在数据库中有一个特点是高扇出性，B+树的高度一般都在2～4层，也就是说查找某一键值的行记录时只需要2到4次IO。当前一般的机械磁盘每秒至少可以做100次IO，2～4次的IO意味着查询时间只需0.02～0.04秒。

# 三、聚集索引 #

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。

同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。

聚集索引能够在B+树索引的叶子节点上直接找到数据，所以查询优化器倾向于采用聚集索引。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

# 四、辅助索引 #

辅助索引（Secondary Index）也称非聚集索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

关于聚簇索引和辅助索引，我们新建一张表来说明

CREATE TABLE `user` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `mobile` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      KEY `idx_name` (`name`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=1;

![20201022102529680.png][]

user表中，InnoDB存储引擎会根据主键id 建立索引，这个索引就是聚集索引，而我们在name列手动建立的索引，就是辅助索引。

InnoDB存储引擎中辅助索引与聚集索引的关系如下图所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhb3lhbmc_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhb3lhbmc_size_16_color_FFFFFF_t_70 1][]

总结：**聚集索引叶子节点存放的是索引和整行数据信息，辅助索引叶子节点存放的是索引和主键的值**

# 五、回表 #

辅助索引叶子节点存放的是主键的值，没有该条数据的其他信息，如果需要其他字段信息，我们需要根据主键的值回到聚集索引上找到该条记录，获取该条记录的所有字段值。

**通过辅助索引，找到主键的值，再回到主键索引树搜索的过程，我们称为回表**。

select * from user where name = 'mike'

我们获取mike的记录信息，因为name字段已经建立索引，InnoDB引擎会用辅助索引进行查询，则会先搜索辅助索引中找到name等于mike的叶子节点，获取主键值，然后再通过聚簇索引中的B+树检索到对应的叶子节点，然后获取整行数据。

我们一起来看看这条 SQL 查询语句的执行流程：

1.  从辅助索引树上找到 name=mike 的叶子节点，获取主键值为9；
2.  再根据id 从聚集索引树查到 id=9 对应的记录信息；

在这个过程中，**回到主键索引树搜索的过程，我们称为回表**。由于查询结果所需要的数据只在主键索引上有，所以不得不回表。我们可以看到，一次查询至少经历一次回表。如果是范围查询，回表次数更多，我们如何避免呢？

# 六、覆盖索引 #

**从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录，这种索引称为覆盖索引（covering index）**。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，大小要远小于聚集索引，因此可以减少大量的IO操作。

假设只需要根据用户的姓名，查询用户的年龄，有什么方式来避免回表呢？我们可以建立一个组合索引，即姓名、年龄作为一个组合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。

**从辅助索引中查询得到记录，而不需要通过聚簇索引查询获得，MySQL中将其称为覆盖索引。使用覆盖索引的好处很明显，不需要查询出包含整行记录的所有信息，因此可以减少大量的I/O操作。**

**由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。**

通常在InnoDB中，除了查询部分字段可以使用覆盖索引来优化查询性能之外，统计数量也会用到。例如， SELECT COUNT(\*)时，如果不存在辅助索引，此时会通过查询聚簇索引来统计行数，如果此时正好存在一个辅助索引，则会通过查询辅助索引来统计行数，减少I/O操作。

# 七、联合索引 #

联合索引是指对表上的多个列进行索引。联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。我们在上面说到：**由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。**联合索引可以减少回表次数，能大大提高查询效率。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhb3lhbmc_size_16_color_FFFFFF_t_70 2][]

# **八、最左前缀原则** #

在上面，我们已经建立了2个索引，一个是name，一个是（name，age），我们现在还有一个需求，根据用户获取手机号，我们怎么做？再新建一个（name，mobile）吗？如果为每一种查询都设计一个索引，索引太多了，如果不建立索引，总不能让它走全表扫描吧？单独为一个不频繁的请求创建一个的索引又感觉有点浪费。应该怎么做呢？

**B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。**

我们使用（name，age）这个联合索引来分析，索引项是按照索引定义里面出现的字段顺序排序的。name和age建立的联合索引如图所示：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhb3lhbmc_size_16_color_FFFFFF_t_70 2][]

我们从图中可以看出，联合索引建立的辅助索引，是先按照name的顺序，name相同，再根据age的顺序进行组织的。下面有三种情况，我们来说明一下索引的查找过程。

1、我们要查询查询name=mike的用户，SQL语句SELECT id FROM user WHERE name='mike'，通过联合索引，可以快速定位到id=9，然后向后遍历得到所有需要的结果（name有重复的，所用会继续向后遍历，如果下一个不是mike，终止查询，返回结果）。

2、如果我们要查询名字“m”的开头的，SQL语句的条件是"where name like ‘m%’"。我们也能够用上这个索引，因为联合先按照name进行组织的，在辅助索引树中，name是有顺序的，在辅助索引中查找到第一个符合条件的记录是id=9，然后向后遍历，直到不满足条件为止。

3、如果我们要查询age=18，SQL语句的条件是"where age = 18"。因为联合索引是按照name进行排序的，从辅助索引的叶子节点中我们可以看出，age是无序的，根据age查询，在我们无法使用这个索引。

从上面的例子可以看出，不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

在建立联合索引的时候，索引内的字段顺序至关重要。因为可以支持最左前缀，所以当已经有了 (name，age) 这个联合索引后，一般就不需要单独在 name 上建立索引了。**如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。**索引的复用性，也是建立索引需要考虑的。

用户表中有name索引和（name，age）联合索引，根据最左匹配原则和索引的复用性，根据name查询也可以使用（name，age）联合索引，我们不需要再根据name单独建立索引了。

如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。这时候，我们要**考虑的原则就是空间**了。比如上面这个用户表的情况，name 字段是比 age 字段大的 ，那我就建议你创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

# **九、索引下推** #

我们在使用联合索引进行查询时，如果满足最左匹配原则，则根据最左前缀匹配的部分进行查询，对于不符合最左前缀的部分，会怎么样呢？

以用户表的联合索引（name, age）为例。如果现在有一个需求：检索出表中姓名以m开头的，而且年龄是 18 岁的用户。

SELECT * FROM user WHERE name like 'm%' and age = 18;

这个语句在搜索索引树的时候，只能用 “m”，找到第一个满足条件的记录id=9。

在 MySQL 5.6 之前，只能从 id=9 开始一个个回表，到主键索引上找出数据行，再对比age字段值是否满足。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

下面是这两个过程的执行流程图，虚线代表回表过程

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhb3lhbmc_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhb3lhbmc_size_16_color_FFFFFF_t_70 4][]

每一个虚线箭头表示回表一次。

无索引下推执行流程中，InnoDB 并不会去看 age 的值，只是按顺序把name 第一个字母是"m"的记录一条条取出来回表，再比较age是否满足。因此，需要回表 3 次。

索引下推执行流程中，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 18，对于不等于 18 的记录，直接判断并跳过。在我们的这个例子中，只需要对 id=9 这两条记录回表取数据判断，就只需要回表 1 次。

参考：

1、[《极客时间——MySQL实战45讲》][MySQL_45]

2、[《MySQL技术内幕：InnoDB存储引擎（第2版）》][MySQL_InnoDB_2]

[20201022102529680.png]: /images/20221123/5e284b29887c4d409d93041cdcdd11bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhb3lhbmc_size_16_color_FFFFFF_t_70]: /images/20221123/f31569e33c8a4567bc574c62650d4b21.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhb3lhbmc_size_16_color_FFFFFF_t_70 1]: /images/20221123/7c7eeacbb4a840e085490ba50c986273.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhb3lhbmc_size_16_color_FFFFFF_t_70 2]: /images/20221123/b508827d1e444482b67a2dd4beb481d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhb3lhbmc_size_16_color_FFFFFF_t_70 3]: /images/20221123/a5e41135f3314ee29981c43fc6d8ee89.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhb3lhbmc_size_16_color_FFFFFF_t_70 4]: /images/20221123/c0e55bd6aa5a4b8b8dae7ac693799d38.png
[MySQL_45]: https://time.geekbang.org/column/intro/139
[MySQL_InnoDB_2]: https://weread.qq.com/web/reader/611329b059346e611427f1c