常识问题：以下SQL会创建几个索引？

最新推荐文章于 2024-10-08 16:24:39 发布

IT周瑜

最新推荐文章于 2024-10-08 16:24:39 发布

阅读量648

点赞数 23

文章标签：后端 java mysql 数据库架构 sql

本文链接：https://blog.csdn.net/u011919808/article/details/141631934

版权

大家好，我是大都督周瑜，最近一直在研究MySQL源码，有点走火入魔了，今天还是分享一篇跟MySQL有关的。

大家先猜猜以下SQL会创建几个索引？

CREATE TABLE t1 (
  id int,
  a varchar(10),
  b varchar(10),
  index idx_b (b),
  UNIQUE KEY unique_a (a)
);

不要往下滑，先自己思考一个答案，并发到评论区。

正确答案是3个，我先分析原因，再给出三种证明方式。

首先，以上SQL没有定义主键，因此InnoDB会默认创建一个隐藏主键row_id，从而会创建一个主键索引，再加上idx_b、unique_a两个辅助索引，总共3个索引。

是不是和你心里想得一样？

但是有同学应该听说过：如果表里面有唯一索引，那么就会用唯一索引当做主键索引。

那现在不正好定义了一个唯一索引unique_a吗？

对对对，但是以上SQL定义的unique_a真的唯一吗？

比如，我完全可以往表里插入两条一模一样的数据：

大家应该能发现了，重点在于unique_a对应的a字段是可以为null的，这就导致了a字段其实是可以存多个null的，也就导致unique_a并不是真正唯一的。

从而导致unique_a并不能作为主键索引，因为作为主键索引的字段不能为null且得唯一，因此只有a字段是not null的时候unique_a才会作为主键索引，而此时表中就只有两个索引了。

所以，我提的这个问题的重点在于a字段不是not null，所以InnoDB会额外创建一个主键索引，从而有三个索引，而一旦a字段是not null，那么InnoDB就不需要额外创建主键索引了，而是直接用unique_a作为主键索引，从而就只有两个索引了。

接下来证明一下，证明的方式有三种，一种是查INNODB_SYS_INDEXES表、一种是看ibd文件，一种是看源码，不过我们先看show index：

show index from t1;

a字段不是not null

a字段是not null

？？？都是两个索引啊？这是表象， show index是不会把隐藏的主键索引查出来的。

我们查INNODB_SYS_INDEXES：

select * from information_schema.INNODB_SYS_TABLES where name = 'my_db/t1';
select * from information_schema.INNODB_SYS_INDEXES where table_id = 170;

a字段不是not null的结果为，3个索引：

a字段是not null的结果为，2个索引：

再看ibd文件:

a字段不是not null：

圈起来的是页号，3、4、5分别表示对应三个索引B+树的根页页号。

a字段是not null：

会发现确实只有两个索引B+树的根页。

不感兴趣的，可以点赞出门了，谢谢观看，也欢迎大家关注我的公众号：IT周瑜，公众号里有更多面试题解析、源码解析、架构设计的干货文章。

最后一种证明方式就是源码了，不过源码太难，我只贴最关键的源码：

该源码的上下文逻辑为：如果发现表没有明确的定义PK，那么就会遍历定义的每个索引，然后遍历每个索引定义的字段，如果发现字段可以为null，就会直接break出字段循环，接着遍历下一个索引中的各个字段。

因此，如果某个唯一索引的字段都不能null，那么这个索引就能作为primary_key。

最后，如果primary_key仍然为MAX_KEY，那就表示只能由InnoDB自己创建一个主键索引了，比如

如果primary_key_no等于-1表示没有主键，否则表示有主键。

如果没有主键就会调用create_clustered_index_when_no_primary()，会使用row id来创建一个索引

而如果有主键，调用的就是create_index()，会直接对主键字段创建索引。

好啦，一入源码深似海，从此头发是路人。

我是大都督周瑜，感谢你的点赞、分享、关注，谢谢。

欢迎大家关注我的公众号：IT周瑜，公众号里有更多面试题解析、源码解析、架构设计的干货文章。

关注