mysql count(*)原理

1
2
3
4
5
6
create table t1(
c1 varchar(30) not null,
c2 varchar(20) not null,
c3 varchar(40) not null,
c4 varchar(10) not null
) engine=innodb;

1. 表无任何索引

不含任何索引,则执行全表扫描(ALL)

1
2
3
4
5
6
7
mysql> explain select count(*) from t1;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

2. 表有主键

使用主键进行扫描

1
2
3
4
5
6
7
8
9
mysql> alter table t1 add primary key (c1);

mysql> explain select count(*) from t1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 32 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

3. 表有二级索引

不管二级索引的key_len是否小于主键,都使用二级索引

1
2
3
4
5
6
7
8
9
mysql> alter table t1 add index i1(c3);

mysql> explain select count(*) from t1;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | i1 | 42 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

4. 表有多个二级索引

使用key_len小的二级索引

1
2
3
4
5
6
7
8
9
mysql> alter table t1 add index i2(c4);

mysql> explain select count(*) from t1;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | i2 | 12 | NULL | 1 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

5. MyISAM与InnoDB

正如在不同的存储引擎中,count()函数的执行是不同的。

  • 在MyISAM存储引擎中,count()函数是直接读取数据表保存的行记录数并返回
  • 在InnoDB存储引擎中,count(*)函数是先从内存中读取表中的数据到内存缓冲区,然后扫描全表获得行记录数的。

在使用count函数中加上where条件时,在两个存储引擎中的效果是一样的,都会扫描全表计算某字段有值项的次数。

6. 聚簇索引

  • 如果您的表上定义有主键,该主键索引是聚集索引。
  • 如果你不定义为您的表的主键时,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚集索引。
  • 如果没有这样的列,InnoDB就自己产生一个这样的ID值,

优先选index key_len小的索引进行count(*),尽量不使用聚簇索引

7. 说明

  • count(column)不会计算column为NULL的列
  • 通过infomation_schema可以快速拿到表的count值,但不是一个准确的值
  • myISAM会存储具体的行数(可能因为myISAM事务要加表锁,才这样设计),innodb则需要进行全表扫描

8. 参考资料

在MySQL的InnoDB存储引擎中count(*)函数的优化)

Comments