mysql5.6.30之count（*）详解

一：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

一： 何时走索引：

1. 创建表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

2. 无索引查询执行计划：

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

3. 添加主键测试count(id)

   mysql> alter table test add primary key(id);Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> explain select count(id) from test\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: test         type: indexpossible_keys: NULL          key: PRIMARY      key_len: 4          ref: NULL         rows: 2        Extra: Using index1 row in set (0.00 sec)

   结论：走了主键索引

4. 测试count(*)

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

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

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

1. 表结构

   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   |     |         |                |+-------+---------------------+------+-----+---------+----------------+

2. 插入1000000行数据

3. 测试：

   一：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)

4. 部分数据：

   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

5. 以上测试每次都会重启mysql，因而不存在缓存

6. 结论：在指定条件的情况下主见索引依然没有附属索引快，在不指定的条件的情况下mysql很聪明，选择了走了我数据量较少的索引k。