MySQL：COUNT语句的索引使用

男娘i 2022-12-17 05:24 213阅读 0赞

我们先准备好测试数据

create table user_test_count
    (
        id       int primary key not null auto_increment,
        name     varchar(45),
        age      int,
        email    varchar(60),
        birthday date
    ) engine 'innodb';
    
    insert into user_test_count (id, name, age, email, birthday)
    values (1, '张三', 20, 'zhangsan@aaa.com', '2000-01-01');
    insert into user_test_count (id, name, age, email, birthday)
    values (2, '李四', 30, 'lisi@bbb.com', '1990-01-01');
    insert into user_test_count (id, name, age, email, birthday)
    values (3, '王五', 40, null, null);
    insert into user_test_count (id, name, age, email, birthday)
    values (4, '赵六', 50, null, null);

我们先直接使用count(\*)

explain select count(*) from user_test_count;

可以看到key使用了主键  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0MzI2MDMwNDM0_size_16_color_FFFFFF_t_70_pic_center]

然后我们给`email`字段添加一个索引  
![在这里插入图片描述][20201022084906860.png_pic_center]  
我们再次执行上述explain语句，可以看到这次使用了email的索引,key\_len长度为243  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0MzI2MDMwNDM0_size_16_color_FFFFFF_t_70_pic_center 1]  
然后我们再添加一个`birthday`字段的索引  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0MzI2MDMwNDM0_size_16_color_FFFFFF_t_70_pic_center 2]

执行上述explain语句，这次使用了birthday的索引,key\_len长度为4  
![在这里插入图片描述][20201022085331676.png_pic_center]  
由此我们可以得出以下结论：

1.  当没有非主键索引时，会使用主键索引
2.  如果存在非主键索引的话，会使用非主键索引
3.  如果存在多个非主键索引，会使用一个最小的非主键索引

其原因是：在innodb中，非主键索引叶子节点存储的结构是：索引+主键；主键索引叶子节点是：主键+表数据。在1个page里面，非主键索引可以存储更多的条目，例如：  
对于一张表，如果有1000000数据，使用非主键索引扫描的page数可能是100 ，而使用主键索引page数可能是500，此时使用非主键索引的性能会更好。同理如果存在多个非主键索引，会使用一个最小的非主键索引，也是为了在一个page里存储更多的数据，从而减少扫描次数，提高性能。

我们可以再试一下单字段的count

explain select count(email) from user_test_count;

可以看到还是撞在`email`的索引上  
![在这里插入图片描述][20201022090231987.png_pic_center]  
说明：count(字段)只会针对该字段统计，使用这个字段上面的索引

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0MzI2MDMwNDM0_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/16f8a3e8170047c1b991996c1c3aff37.png
[20201022084906860.png_pic_center]: /images/20221123/98d6377462e14489a893d9bc4d028a79.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0MzI2MDMwNDM0_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/287324b9a5ca4dd78966972921fb6b63.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0MzI2MDMwNDM0_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/9cb394f0a8404f439037ea0863c28cd5.png
[20201022085331676.png_pic_center]: /images/20221123/6341fd63cd784e89ac741b9a1ae7b9f4.png
[20201022090231987.png_pic_center]: /images/20221123/597d466e7f8a4dbc97a2d233e7f622aa.png