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(*)函数的优化)
