数据库聚簇索引——not null条件对唯一键索引成为聚簇索引的影响

数据库聚簇索引的规则如下：

• 如果有主键，则主键是聚簇索引（当然主键也不一定是单个列的）；
• 如果没有主键，但是有not null修饰的唯一键索引，则这个索引是聚簇索引；
• 都没有，创建一个叫db_row_id的6字节隐藏列为聚簇索引，这个索引程序员不可见（这个列存在一定冲突和性能问题，详见大佬：https://www.cnblogs.com/frankcui/p/15226301.html）。

所以一般还是要定义主键。如果不定义主键，再想通过创建索引的方式提高性能，那无论创建多少个索引，本质上都是二级索引，都要进行回表（本来一个主键就可以解决的事情，现在仍旧有一个聚簇索引，还额外多了那个原来定义主键就可以创建的索引，反而占用空间）。同样的查询，IO次数变多，这是不值当的。

那有没有not null是否真的影响unique列成为聚簇索引？

看起来我们无法直接从explain中找到是否走聚集索引的信息，官方文档中介绍到，extra字段中大抵只是看是否覆盖，而聚集索引是一个“索引即数据”的概念，它就是完整的表了，即便没有覆盖掉所有查询的列，也是真正覆盖了，所以使用聚集索引的extra字段为null。官方文档：

https://www.cnblogs.com/kerrycode/p/9909093.html

Using index (JSON property: using_index)

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY.

从表中仅使用索引树中的信息就能获取查询语句的列的信息, 而不必进行其他额外查找（seek）去读取实际的行记录。当查询的列是单个索引的部分的列时, 可以使用此策略。（简单的翻译就
是：使用索引来直接获取列的数据，而不需回表）。对于具有用户定义的聚集索引的 InnoDB 表, 即使从Extra列中没有使用索引, 也可以使用该索引。如果type是index并且Key是主键, 则会出现这种情况。

翻译翻译就是你用主键的聚簇索引，可能看起来没有覆盖，但是实际上所有数据都在这里了，默认就是覆盖了。

那怎么判断是否走了聚簇索引呢？就算有唯一键的聚簇索引，用了还是不知道的。我有这样一个思路：

如果唯一键可以为null时，按照理论不应该是聚簇索引，当查询的列有的不在唯一键索引覆盖范围内则应该会有回表（回聚集索引去查），即可以从某些方面佐证发生回表，如索引下推。接下来的目的就是寻找这个证据。

准备两张表，分别加上not null（按理论为聚簇索引）和default null（可以为空）修饰：

CREATE TABLE `test_unique` (
  `id` int NOT NULL,
  `col1` varchar(255),
  `col2` varchar(255) NOT NULL,
  UNIQUE KEY `uni` (`id`) USING BTREE COMMENT '测试没有主键的情况下，唯一键是不是聚簇索引与not null的关系'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `test_unique` (
  `id` int DEFAULT NULL,
  `col1` varchar(255),
  `col2` varchar(255) NOT NULL,
  UNIQUE KEY `uni` (`id`) USING BTREE COMMENT '测试没有主键的情况下，唯一键是不是聚簇索引与not null的关系'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

准备数据：

分别执行这样一个语句：

explain select id, col1 from test_unique where id in (89,98);

结果：

区别就在于Using index condition，找到对这个值的解释：https://www.cnblogs.com/echoppy/p/14247575.html

很幸运得到这个结果，我的mysql的版本支持索引下推，也从侧面佐证了“当唯一键索引为null时，并不能成为聚簇索引，否则就直接查出所有的值，不发生下推”。到此证明结束。

另：

当我在试图寻找佐证时，有一个现象引起了我的好奇：尝试用这样一个语句去判断是否走索引：

explain select id, col1 from test_unique where id > 23;

注意到唯一键索引可以为空时，范围查询会使索引失效：

NOT NULL时，可以发现范围查询走了索引，此时的唯一键索引与主键无二。

小结

实验本身的目的不在于试图推翻现在的结论，而是对现有的结论多一些直观的认识，并通过实践的形式对感兴趣的概念加深认识和理解。本文涉及的知识点有索引以及