MySql索引选择问题

今天测试一条sql遇到了比较奇怪的问题,描述如下:
一个user表中含有以下字段:

  id bigint(20) not null,
  mobile varchar(255),
  create_at datetime(6),
  enabled bit,
  ...其他不重要的字段,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_mobile` (`mobile`) USING BTREE,
  KEY `idx_create_at` (`created_at`) USING BTREE

sql语句如下:

select id from user;

使用explain结果如下:

id select_type table partitions type possible_keys key key_len ref rows filtered extra
1 SIMPLE user index idx_create_at 4 972757 100.00 Using index
由上面信息得知,innodb选择了idx_create_at非聚簇索引,并没有选择id的主键索引,
删除idx_create_at索引,再次explain,结果如下
id select_type table partitions type possible_keys key key_len ref rows filtered extra
----- ----------- ----- ------------ ----- -------------- -------------- -------- ---- --------- --------- -----------------
1 SIMPLE user index idx_mobile 767 972757 100.00 Using index
使用的是idx_mobile非聚簇索引,依然没有选择id的主键索引
删除idx_mobile索引,再次explain,结果如下
id select_type table partitions type possible_keys key key_len ref rows filtered extra
----- ----------- ----- ------------ ----- -------------- -------------- -------- ---- --------- --------- -----------------
1 SIMPLE user index PRIMARY 8 972757 100.00 Using index
这次只剩下主键(聚簇)索引时,选择了Primary,也就是在迫不得已的情况下才会选择。

因此有了如下猜测:
1.由于select id from user;这句sql中只查了主键索引字段,且没有任何排序分组等操作,众所周知,在innodb中非聚簇索引的叶子节点中存放的是主键内容,也就是根据非聚簇索引,innodb先查找到主键,然后根据主键再查询数据,也就是使用非聚簇索引很容易查找主键信息,可能比根据主键直接查询更快,因为聚簇索引叶子节点中记录的是整行的数据,所以多个主键更容易分布在不同的块中,增加了磁盘io,而在机械硬盘为主体的今天,一次磁盘io耗时还是非常可观的,非聚簇索引一块中可以存放更多的主键信息,从而减少磁盘io的次数。
2.猜测1解释了为什么innodb会选择idx_create_at和idx_mobile,而不是主键,那么又为什么存在两个非聚簇索引(idx_create_at和idx_mobile)时,选择了idx_create_at而不是idx_mobile呢,其实这个问题可以从前两次的explain的结果中窥得,重要的key_len标识了一切,使用idx_create_at索引时,key_len仅仅是4,而使用idx_mobile时,key_len为767,由猜测1抑制,更小的ken_len,可以降低磁盘io的次数,因此innodb选择了idx_create_at而不是idx_mobile。

为了验证上述猜测2,对表user添加一个type的索引idx_enabled,由于enabled是个bit类型,占用1个字节,比create_at更少,innodb会优先选择idx_enabled。使用explain结果如下:

id select_type table partitions type possible_keys key key_len ref rows filtered extra
1 SIMPLE user index idx_enabled 8 972757 100.00 Using index
由结果可知,猜想2是正确的。

思考
如果有两个key_len的长度一样的非聚簇索引,innodb又是如何选择的呢?此问题还没有一个清醒的认识。。。
select id from user order by id; 这句sql的explain会优先使用primary索引,思考是为什么。

注:以上只是基于结果做的一些猜测,innodb内部实际的执行过程并没有一个确定的结论,如果有朋友有确切的证据,欢迎在评论区中说明,还有一点,上述毕竟是一些合理的猜测,正确性也不保证是100%,如有错误,请不吝赐教,谢谢~

posted @ 2021-05-20 18:51  AutumnLight  阅读(87)  评论(0编辑  收藏  举报