什么是聚簇索引和非聚簇索引，如何理解回表、索引下推

聚簇索引

聚簇索引简单理解就是将数据与索引放在一起，找到索引即找到了数据。换句话说，对于聚簇索引，其非叶子节点上存储的是索引字段的值，而叶子节点上存储的是对应记录的整行数据。

图片

在 InnoDB 中，聚簇索引（Clustered Index）是指按照每张表的主键构建的一种索引方式。它将表数据按照主键的顺序存储在磁盘上，确保了行的物理存储顺序与主键的逻辑顺序相同。这种索引方式使得查找聚簇索引的速度非常快。

非聚簇索引是指将索引与数据分开存储的一种方式。在非聚簇索引中，叶子节点包含索引字段的值以及指向数据页数据行的逻辑指针。

图片

在 InnoDB 中，非聚簇索引（Non-clustered Index）是根据非主键字段创建的索引，通常称为二级索引。它不影响表中数据的物理存储顺序，而是单独创建一张索引表，用于存储索引列和对应行的指针。

在 InnoDB 中，主键索引就是聚簇索引，而非主键索引则是非聚簇索引。因此，在 InnoDB 中：

• 对于聚簇索引，其非叶子节点上存储的是索引值，而叶子节点上存储的是整行记录。
• 对于非聚簇索引，其非叶子节点上存储的是索引值，而叶子节点上存储的是主键的值以及索引值。

因此，通过非聚簇索引进行查询时，需要进行一次回表操作，即先通过索引查找到主键 ID，然后再通过 ID 查询所需字段。

没有创建主键怎么办？

在 InnoDB 中，如果表结构中没有定义主键，数据库会自动为每行记录添加一个隐藏的主键，通常称为 db_row_id 字段。这个隐藏主键会确保每行记录都有一个唯一的标识符。

如果表中没有合适的唯一索引可用作聚簇索引，数据库会使用这个隐藏主键来构建聚簇索引。这样可以确保每行记录都有一个物理上的唯一标识符，并且能够保持索引的唯一性和快速查询的特性。

扩展知识

我们刚刚又提到回表的概念，什么是回表呢？

什么是回表，怎么减少回表的次数？

在 InnoDB 中，索引 B+树的叶子节点存储了整行数据的是主键索引，也被称为聚簇索引。而索引 B+树的叶子节点存储了主键的值的是非主键索引，也被称为非聚簇索引。

在数据存储方面，主键（聚簇）索引的 B+树的叶子节点直接包含了我们要查询的整行数据。而非主键（非聚簇）索引的叶子节点则包含了主键的值。

因此，当我们通过非聚簇索引进行查询时，首先会通过非聚簇索引查找到主键的值，然后需要再通过主键的值进行一次查询才能获取到我们要查询的数据。这个过程称为回表。

因此，在 InnoDB 中，使用主键进行查询效率更高，因为这个过程不需要回表。此外，通过依赖覆盖索引、索引下推等技术，我们可以通过优化索引结构和 SQL 语句来减少回表的次数。

什么是索引覆盖、索引下推？

覆盖索引

覆盖索引是指查询语句的执行只需从索引中获取所需数据，而无需从数据表中读取。也可以称之为实现了索引覆盖。

当一条查询语句符合覆盖索引条件时，MySQL 只需通过索引就能返回查询所需数据，而不需要进行索引查找后再返回表操作，从而减少 I/O，提高效率。

例如，在表 covering_index_sample 中有一个普通索引 idx_key1_key2(key1,key2)。

当我们执行以下 SQL 语句时：

SELECT key2 FROM covering_index_sample WHERE key1 = 'keytest';

此时可以通过覆盖索引查询，无需进行回表操作。

但是对于以下 SQL 语句，虽然是索引覆盖，但由于不符合最左前缀匹配，无法利用索引（会扫描索引树）：

SELECT key1 FROM covering_index_sample WHERE key2 = 'keytest';

另外，如果查询语句中需要的信息不包含在联合索引中，那么就无法使用索引覆盖。例如：

SELECT key2, key3 FROM covering_index_sample WHERE key1 = 'keytest';

索引下推

索引下推是 MySQL 5.6 引入的一种优化技术，默认开启，可通过设置 SET optimizer_switch = 'index_condition_pushdown=off'; 来关闭。

它的工作原理如下：假设 people 表中（zipcode，lastname，firstname）构成一个索引。考虑以下查询：

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果没有使用索引下推技术，MySQL 会通过 zipcode='95054'从存储引擎中查询对应的数据，然后将结果返回到 MySQL 服务端，接着 MySQL 服务端再基于lastname LIKE '%etrunia%' 和 address LIKE '%Main Street%'来判断数据是否符合条件。

而如果使用了索引下推技术，MySQL 首先会返回符合 zipcode='95054'的索引，然后根据lastname LIKE '%etrunia%'来判断索引是否符合条件。如果符合条件，则根据该索引定位对应的数据；如果不符合，则直接拒绝。有了索引下推优化，可以在有 like 条件查询的情况下，减少回表次数。

当一条 SQL 使用到索引下推时，执行计划中的 extra 字段的内容会显示为 "Using index condition"。

索引下推不止 like

上面的例子中，提到了 like，包括 MySQL 官网中也只提到了 like，但是其实不止有 Like。因为我认为索引下推其实是解决索引失效带来的效率低的问题的一种手段。

所以当联合索引中，某个非前导列因为索引失效而要进行扫表并回表时，就可以进行索引下推优化了。

如，有 a,b 联合索引，类型都是 varchar，以下 SQL 也可以用到索引下推：

select d from t2 where a = "ni" and b = 1;

因为 b 字段因为类型不匹配导致索引失效了，但是通过下推优化其实是可以减少回表的次数的。