mysql count 原理_MySQL count(*)原理

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