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. 参考资料