MySQL 聚簇索引一定是主键吗

数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引（又叫二级索引）两种。

use myTest;                 

drop table if exists t1; 

CREATE TABLE `t1` (          
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(20) DEFAULT NULL,
  `b` int(20) DEFAULT NULL,
  `c` int(20) DEFAULT NULL,
  `d` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_a_b` (`a`,`b`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB CHARSET=utf8mb4 ;

- 什么是聚簇索引

先说说聚簇索引，聚簇索引有两个明显特征：

1. 索引必须为唯一索引
2. 叶子节点处储存的是整行数据（这也是为何用主键（聚簇索引）进行查询时，查询速度会较快的原因（减少回表查询））

这也是为何在新建数据表时，会推荐建立自增id作为主键，同时id自增确保业务层面上的无意义。==确保主键ID业务上无意义很重要，因为它确保你再已生成记录的主键ID，是不会被update的==。如果是随机主键或者频繁更新主键的话,就会存在数据页频繁断裂，B+树不饱和的情况（原因：聚簇索引是按顺序进行排序的）。而如果设置主键是自增，那么每一次都是在聚集索引的最后增加，当一页写满，就会自动开辟一个新页，不会有聚集索引树分裂这一步，效率会比随机主键高很多。这也是很多建表规范要求主键自增的原因。

- 什么是非聚簇索引

非聚簇索引有两个明显特征：

1. 索引值必须为可不唯一
2. 叶子节点处储存的索引行跟主键（key+pk cols）

辅助索引的叶子节点，存储主键值，而不是整行数据，这样的好处有：
1.减少存储数据，降低了辅助索引的树所占的存储空间
2.聚簇索引列不变的情况下，非聚簇索引列改变不影响辅助索引的原本结构

- 聚簇索引一定是主键吗

有人会疑虑似乎聚簇索引一定会是主键，那如果数据表不建立主键的话是否就没有聚簇索引了？
在 InnoDB 中，聚集索引不一定是主键，但是主键一定是聚集索引：原因是如果没有定义主键，聚集索引可能是第一个不允许为 null 的唯一索引，如果也没有这样的唯一索引，InnoDB 会选择内置 6 字节长的 ROWID 作为隐含的聚集索引。
InnoDB 的数据是按照主键顺序存放的，而聚集索引就是按照每张表的主键构造一颗 B+ 树，它的叶子节点存放的是整行数据。
每张 InnoDB 表都有一个聚集索引，但是不一定有主键。

- 使用非聚簇索引查询数据时，一定要回表查询吗？

回表查询是由于非聚簇索引的B+Tree叶子节点只记录了，PK cols与key。这就造成当查询字段非主键跟索引字段时，索引树无法直接提供所有数据列，从而要进行一次回表查询，也就是拿着主键ID再进行一次查询操作。
例如t1表

select * from t1 where c = 1; #sql_1
select id,c from t1 where c = 1; #sql_2

当查询被非聚簇索引涵盖列时，则不需要走回表操作。即sql_2不需要走回表查询，sql_1需要。

本作品采用《CC 协议》，转载必须注明作者和本文链接