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%,如有错误,请不吝赐教,谢谢~