MySQL的 索引、聚簇索引、非聚簇索引、回表、索引覆盖、索引下推 都是啥

背景

本文是将网络上搜集到一些相关知识进行汇总，按着自己的理解和方式进行了重新的编辑。

1、MySQL 的索引

针对 MySQL 的索引，主要有以下几条：

• 它是一棵 B Tree
• 每一个 B Tree 的节点都是一个「数据页」
• 每一个「数据页」默认会占用 16KB 的磁盘空间
• 索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样
• 通过索引的查询是在存储引擎中完成的

在一棵 B Tree 上会有很多数据页，上边我们也看到了每一个「数据页」会占用一定的磁盘空间，所以，如果大量的创建索引，势必会导致磁盘空间的消耗。

2、聚簇索引 && 非聚簇索引

首先什么是聚簇索引、非聚簇索引呢？简单的来说，如下：

• 聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（所有字段的值）。
• 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。

看了上边的解释，可能对聚簇索引和非聚簇索引是什么还是有点摸不着通脑。别着急，下面分别以 MySQL 较为常用的两个存储引擎 InnoDB 和 MyISAM 为例，再展开说下。

在展开说明前，我们要先明确下两个概念：

• 主键索引：主键，一棵 B Tree
• 辅助索引（二级索引）：唯一索引、复合索引、前缀索引等等，一棵 B Tree

2.1、InnoDB

在存储引擎为 InnoDB 的表中，主键索引的类型是聚簇索引，辅助索引的类型是都是非聚簇索引。结合上边对聚簇索引、非聚簇索引的定义，我们可以知道，InnoDB 的表中主键索引中的叶子点上存储了行数据（所有字段的值，而辅助索引叶子节点存储了索引列的值和主键值。

下面，用两张图再对 InnoDB 的「主键索引」和「辅助索引」加深下印象：

1. InnoDB-主键索引（聚簇索引）
   
2. InnoDB-辅助索引（非聚簇索引）
   

既然说到了 InnoDB 的「主键索引」，那就顺便再说下根据主键搜索的过程，如下图：

整个查询的过程如下：

1. 查询 id（主键） 为 18 的数据，SELECT id, name, age WHERE id = 18。

2. 首先在「根节点：节点一」上，id = 18 落在了 15 <= id < 56 范围之内，这样我们就知道了下级节点「非叶子节点：节点2-1」的地址。

3. 根据【步骤2】得到的「非叶子节点：节点2-1」的地址，找到对应的「非叶子节点：节点2-1」。然后，id = 18 又落在了 15 <= id < 20 范围之内，这样我们就知道了再下一级节点「叶子节点：节点3-1」的地址。

4. 根据【步骤3】得到的「叶子节点：节点3-1」的地址，找到对应的「叶子节点：节点3-1」。最后，在「叶子节点：节点3-1」这个节点上找到 id = 18 对应的数据 {“id”: 18, “name”: “King”, “age”: 17}

2.2、MyISAM

在存储引擎为 MyISAM 的表中，主键索引和辅助索引的类型都是非聚簇索引。两棵 B Tree 的结构完全一致，只是存储的内容不同，主键索引 B Tree 的节点存储了「主键」 「数据记录的地址」，辅助键索引 B Tree 存储了「索引列的值」 「数据记录的地址」。还有一点不同是主键索引中的 key 必须是唯一的，而辅助索引中的 key 可以重复。

和上边的 InnoDB 讨论一样，下面，用两张图片再对 MyISAM 的「主键索引」和「辅助索引」加深下印象：

1. MyISAM-主键索引（非聚簇索引）
   
2. MyISAM-辅助索引（非聚簇索引）
   

3、回表

主要发生在通过辅助索引查询的时候，通过辅助索引找到 B Tree 中的叶子结点，但是辅助索引的叶子节点内存储的数据不全，只有索引列的值和主键值。我们还需要拿着刚从辅助索引中得到的主键值再去聚簇索引（主键索引）的叶子节点中去拿到完整的数据（全部字段），这个过程就叫「回表」。

下面介绍两个和回表操作相关的概念：索引覆盖 和 索引下推。

3.1、索引覆盖

如果非聚簇索引的叶子节点上有我们想要的返回的数据（字段），那就不需要回表了。例如：name 和 idcard 字段创建了一个联合索引（非聚簇索引），我们只想返回 主键、name、idcard 这 3 个字段（SELECT id, name, idcard WHERE name = “那XX”），因为 name、idcard 作为一个联合索引已经在辅助索引上的叶子节点上存有 name、idcard 的具体值，所以就不需要再回表操作了（除非 SELECT 里再增加一个 address 字段，这样就需要回表了）。

综上所述，这种索引中已经包含了所有需要读取的列数据的查询方式称为覆盖索引（或索引覆盖）。

3.2、索引下推（Index Condition Pushdown，ICP）

索引下推，严格来说应该叫「索引条件下推」。该功能是 MySQL 数据库 5.6 版本添加的，用于优化数据查询，默认情况处于开启状态。我们可以通过如下命令来开启和关闭「索引条件下推」功能：

• 开启

  SET optimizer_switch = 'index_condition_pushdown=on'
  

• 关闭

  SET optimizer_switch = 'index_condition_pushdown=off'
  

下面通过一个例子来说下什么是「索引条件下推」，首先我们假设有这么一张表：

• 表名：t_user
• 字段：id，name，mobile
• 辅助索引：name mobile（索引名：name_mobile_normal）

表结构如下：

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(100) COLLATE utf8_bin NOT NULL COMMENT '姓名',
  `mobile` varchar(200) COLLATE utf8_bin NOT NULL COMMENT '手机',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `name_mobile_normal` (`name`,`mobile`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户表';

下面，让我们来分别看下关闭和开启「索引条件下推」这两种情况，下边这个查询语句的执行计划有怎样的不同。

EXPLAIN SELECT * FROM t_user WHERE name = 'A' AND mobile LIKE '8'

• 关闭「索引条件下推」
  
• 开启「索引条件下推」
  
  从上边，我们可以看到，关闭「索引条件下推」时候 Extra 是 Using where；开启「索引条件下推」时候 Extra 是 Using index condition。

当关闭「索引条件下推」的时候，MySQL 首先通过索引将匹配 name = ‘A’ 的数据都查出来（在存储引擎中完成），然后再从这些数据中找到与 mobile LIKE ‘8’ 条件相匹配的数据（在 Server 层完成）。

当开启「索引条件下推」的时候，因为 name 和 mobile 对应的值已经都存储在索引中了（也就是说我们可以直接拿到这个值，来判断数据是否与查询条件匹配），这样就可以直接在存储引擎中完成数据的查询了。

综上所述，索引条件下推就是 “过滤的动作 尽量由 下层的存储引擎层 通过 使用索引 来完成，而不需要上推到 Server 层进行处理” ，实现的主要思路是充分利用索引中存储的数据，尽量把根据条件过滤数据的操作交给存储引擎来做（这样可以最大限度的减少需要「回表」的数据）。

最后，还需要注意的是由于该技术基于存储引擎，只有特定的存储引擎可以使用。而且，条件判断操作必须可以在存储引擎运行，比如调用存储过程的条件就不可以，因为存储引擎没有调用存储过程的能力。

4、参考

1. 图解｜用好MySQL索引，你需要知道的一些事情
2. 聚簇索引和非聚簇索引
3. MySql 实战—— Innodb 索引、数据结构
4. 什么是索引下推

这篇好文章是转载于：学新通技术网