mysql5.6.30之count（*）详解

最新推荐文章于 2022-06-30 16:52:21 发布

原创最新推荐文章于 2022-06-30 16:52:21 发布 · 470 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

mysql基础专栏收录该内容

0 篇文章

订阅专栏

本文探讨了MySQL中COUNT函数的使用方式及其与不同索引类型之间的交互效果。通过具体的例子说明了COUNT(*)如何利用主键索引进行高效查询，并对比了在不同条件下使用主键索引与辅助索引时的性能差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一：count

统计目标列不为空的数据行。

例：

    mysql> select * from t6;
    +------+----------+
    | id   | name     |
    +------+----------+
    |    1 | ziOLI    |
    |    2 | xiaoguo  |
    |    3 | xiaowang |
    |    4 |          |
    |    5 | NULL     |
    +------+----------+

如果是count(*)那么就会统计不为空最多的那个列。统计的数据包含为“”不包含为null的数据。
例：

    mysql> select count(name) from t6;
    +-------------+
    | count(name) |
    +-------------+
    |           4 |
    +-------------+

拓展：count(*)与索引

版本：mysql：5.6.30
引擎：innodb

一：何时走索引：

创建表test插入测试数据：

mysql> create table test(id int(10),name varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test values(1,"dfd"),(2,"fdfd");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

无索引查询执行计划：

mysql> explain select count(*) from test\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: NULL
1 row in set (0.00 sec)

添加主键测试count(id)

mysql> alter table test add primary key(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count(id) from test\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using index
1 row in set (0.00 sec)

结论：走了主键索引

测试count(*)

mysql> explain select count(*) from test\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using index
1 row in set (0.00 sec)

结论：即使我没有指定id字段还是走了主键索引

二：5.6环境下count(*)走主键索引和辅助索引的效率的对比

表结构

mysql> desc sbtest;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| aid   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| id    | int(10) unsigned    | NO   | MUL | 0       |                |
| k     | int(10) unsigned    | NO   | MUL | 0       |                |
| c     | char(120)           | NO   |     |         |                |
| pad   | char(60)            | NO   |     |         |                |
+-------+---------------------+------+-----+---------+----------------+

插入1000000行数据

测试：

一：
mysql> explain select count(*) from sbtest;
+----+-------------+--------+-------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+-------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | sbtest | index | NULL          | k    | 4       | NULL | 964496 | Using index |
+----+-------------+--------+-------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
|  1010101 |
+----------+
1 row in set (0.56 sec)


二：

mysql> explain select count(*) from sbtest where id>=0;
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | sbtest | range | id            | id   | 4       | NULL | 482248 | Using where; Using index |
+----+-------------+--------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.04 sec)

mysql> select count(*) from sbtest where id>=0;
+----------+
| count(*) |
+----------+
|  1010101 |
+----------+
1 row in set (0.55 sec)

三：
mysql> explain select count(*) from sbtest where aid>=0;
+----+-------------+--------+-------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | sbtest | range | PRIMARY       | PRIMARY | 8       | NULL | 482248 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql>  select count(*) from sbtest where aid>=0;
+----------+
| count(*) |
+----------+
|  1010101 |
+----------+
1 row in set (2.52 sec)

部分数据：

aid id k c pad
1 1 1 dfgfgf dfdfd
2 2 1 dfgfgf dfdfd
3 3 1 dfgfgf dfdfd
4 4 1 dfgfgf dfdfd
以上测试每次都会重启mysql，因而不存在缓存
结论：在指定条件的情况下主见索引依然没有附属索引快，在不指定的条件的情况下mysql很聪明，选择了走了我数据量较少的索引k。