count(*)不加条件,居然不走群簇索引?

1,577 阅读1分钟

文章中的表中,大概有几百~几千万的数据。

表中有2个字段:id,name

有3个索引:主键索引,name,id

count(*)不走主键索引而是普通(二级)索引,why?

EXPLAIN select count(id) from Student

明明选的是id,但是走的却是二级索引而非覆盖索引

因为主键索引树的叶子节点是数据,二级索引树的叶子节点是主键值,所以二级索引树比主键索引树小很多。对于 count(*) 操作,优化器会找到最小的那棵树来遍历,所以当你有建立二级索引的时候,速度自然就快了

可以通过以下SQL查看索引长度:

select
database_name,
table_name,
index_name,
round((stat_value*@@innodb_page_size)/1024/1024, 2) SizeMB,
round(((100/(SELECT INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = iis.table_name and t.TABLE_SCHEMA = iis.database_name))*(stat_value*@@innodb_page_size)), 2) `Percentage`
from mysql.innodb_index_stats iis
where stat_name='size'
and table_name = 'your_table'
and database_name = 'your_database_name'

对数据一亿行的简单表进行上述SQL查询,结果如下:

image-20201130140115449

分别强制用对应索引进行查询,结果如下:

select count(*) from Student FORCE INDEX(PRIMARY)

查询时间为174.490s

select count(*) from Student FORCE INDEX(idx_name)

查询时间为171.265s

select count(*) from Student FORCE INDEX(idx_id)

查询时间为37s左右

在看完mySQL小册子之后,我认为原因如下: 非群簇索引一般来说在叶子节点上,一个页面中会包含更多的记录,因此整个B+树的规模会小一些,某些条件的查询中查非群簇索引,MySQL的分析优化过程会认为走非群簇索引会更快,从而选择非群簇索引 这篇博客也提供了一定的思路,我觉得写得很不错: juejin.cn/post/684490…