MySQL-count(*)、count(1)、count(主键)、count(非索引列)、count(索引列)性能分析

小白菜S

已于 2022-03-05 16:42:18 修改

阅读量2k

点赞数 2

分类专栏： mysql 文章标签： java 数据库

于 2022-03-05 16:36:48 首次发布

本文链接：https://blog.csdn.net/weixin_38923162/article/details/123293386

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文主要讨论的是count()函数在没有where的情况下统计性能

环境: MySQL 数据库5.7.23 服务器4核8G 带宽5M 1000W数据为防止缓存影响，测试前已经将缓存关闭

一：先说结果

按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)
这里需要说明下count(字段)是该字段没有索引的情况下。如果该列是索引列性性能优于count(主键id)次于count(1)

二：开始验证

1：检查数据库数据量以及索引情况

表结构：
在这里插入图片描述

索引：
在这里插入图片描述

数据量：

mysql> select count(*) from user_operation_log;
+----------+
| count(*) |
+----------+
| 11018376 |
+----------+
1 row in set (7.38 sec)

由上面的图片得知数据1100W。索引包含主键索引和两个普通索引name、time。

2: 总数据量1100W+ 表的速度对比

1）count(*)统计

mysql> select count(*) from user_operation_log;
+----------+
| count(*) |
+----------+
| 11018377 |
+----------+
1 row in set (3.11 sec)

 mysql> EXPLAIN   SELECT COUNT(*) FROM user_operation_log\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_operation_log
   partitions: NULL
         type: index
possible_keys: NULL
          key: job
      key_len: 33
          ref: NULL
         rows: 10971114
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

2）count(1)统计

 mysql> select count(1) from user_operation_log;
+----------+
| count(1) |
+----------+
| 11018377 |
+----------+
1 row in set (3.04 sec)

 mysql> EXPLAIN   SELECT COUNT(1) FROM user_operation_log\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_operation_log
   partitions: NULL
         type: index
possible_keys: NULL
          key: job
      key_len: 33
          ref: NULL
         rows: 10971114
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

3）count(主键) 统计

 mysql> SELECT COUNT(id) FROM  user_operation_log   FORCE INDEX (PRIMARY);
+-----------+
| COUNT(id) |
+-----------+
|  11018376 |
+-----------+
1 row in set (16.07 sec)

 mysql> EXPLAIN  SELECT COUNT(id) FROM  user_operation_log   FORCE INDEX (PRIMARY
)\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_operation_log
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10716132
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

4）count(列)统计非索引

mysql> SELECT COUNT(isdel) FROM user_operation_log  ;
+--------------+
| COUNT(isdel) |
+--------------+
|     11018376 |
+--------------+
1 row in set (16.85 sec)

mysql> EXPLAIN  SELECT COUNT(isdel) FROM user_operation_log\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_operation_log
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10716132
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

3：分析

通过上面的分析count(*) 和count(1)使用的索引一样都是job，count(主键)的索引使用的主键索引，count(非索引列)没有使用索引进行了全表扫扫描性能最差。
已知表除了主键索引还有别的索引，数据库为什么采用的是job索引呢？

1）查看索引的详细信息

SHOW INDEX FROM user_operation_log

在这里插入图片描述

主要字段解释：
Non_unique: 如果索引不能包括重复值则为0，如果可以则为1。也就是平时所说的唯一索引。
Key_name: 索引名称，如果名字相同则表明是同一个索引，而并不是重复。
Seq_in_index: 索引中的列序列号，从1开始。
Column_name: 索引的列名。
Cardinality: 是基数的意思，表示索引中唯一值的数目的估计值。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，那就需要重新评估这个字段是否适合建立索引。

2）根据Cardinality得知索引列应该选择基数小的，但是content和job一样都是最小，为什么没有选择呢？我们强行使用content作为索引列分析下

 mysql>  SELECT COUNT(*) FROM  user_operation_log   FORCE INDEX (content) ;
+----------+
| COUNT(*) |
+----------+
| 11018377 |
+----------+
1 row in set (6.63 sec)

 mysql> EXPLAIN  SELECT COUNT(*) FROM  user_operation_log   FORCE INDEX (content)
\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_operation_log
   partitions: NULL
         type: index
possible_keys: NULL
          key: content
      key_len: 2003
          ref: NULL
         rows: 10971114
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

通过上面的分析可知content作为索引列后key_leng的长度为2003远大于time作为索引列的key_leng(33)。

结果可知：count(*)和count(1)的查询效率差不多

4：此时如果添加一个字段，数据类型同样varchar长度10，默认值20，默认索引会发生什么变化？

在这里插入图片描述

这时查询默认的索引：

mysql> EXPLAIN   SELECT COUNT(1) FROM user_operation_log\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_operation_log
   partitions: NULL
         type: index
possible_keys: NULL
          key: sex
      key_len: 13
          ref: NULL
         rows: 10968893
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

很明显发生了变化，由之前的job(33)变成了sex(13)。

5：有人会问为什么主键索引反而查询的效率低呢？

1.这个问题就需要了解数据库的索引结构

在这里插入图片描述
因为主键索引（聚集索引）存储的是每行的数据，而二级索引（辅助索引）存储的是主键。统计行数所以二级索引查询的更快。

三：总结

就如文章开头说的一样count(字段)<count(主键id)<count(索引列)≈count(1)≈count(*)
多个索引的情况下选择的原则是索引长度最短、基数偏小
如果索引基数一致，选择索引长度最小的
在设计数据库的时候字段长度不能随意创建，需要根据需求决定，会影响sql性能
大表的count()查询优化手段就是新增tinyint类型的标识字段，速度可以得到有效提升

四：意外情况

在第一次测试的时候
环境：MySQL 数据库 8.0.25 服务器4核8G 带宽5M 1000W数据 (标识字段 tinyint类型保证最优索引)
count(*) 17秒
count(标识字段) 5秒
EXPLAIN 显示的索引都是标识字段。
发现count()速度很慢，经过和别人讨论发现，貌似是8.0.25的数据库BUG。因为是远程库自己没有权限，所以找了一个5.7.23版本的数据库。
网友提供了一段解释：

InnoDB: Stalls were caused by concurrent SELECT COUNT(*) queries where the number of parallel read threads exceeded the number of machine cores. A patch for this issue was provided for Windows builds in MySQL 8.0.24. The MySQL 8.0.26 patch addresses the same issue on other affected platforms. (Bug #32678019) References: See also: Bug #32224707.

参考连接
mysql的count()函数如何选择索引，千万级表的count()查询优化实例