开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第06天，点击查看活动详情

SELECT COUNT(*) FROM T_NAME 是我们开发过程中常用的一种统计表中记录行数的语句。它的工作原理是怎样的呢？为什么当表记录数持续增长的时候，它变得越来越慢了呢？今天我们就来一起学习下 COUNT(*)、COUNT(1)、COUNT(COLUMN) 的实现方式及它们之间的区别。

01-COUNT(*) 的实现方式

首先，需要明确的是 COUNT() 方法的语义是：它是一个聚合函数，对于返回的结果集，一行行的判断，如果函数入参不是 NULL，累计值就加1，否则不加，最后返回累计值。

其次，不同的存储引擎实现 COUNT() 方式是不同的。例如，MyISAM 就是将表中记录总行数存储在磁盘上，当遇到 COUNT() 方法时，直接将此值返回。 InnoDB 中由于对事务的支持及 MVCC，每个事务看到的 COUNT() 值可能是不同的，所以不能采用与 MyISAM 一样的方式。

01.1-InnoDB 中 `COUNT()` 的实现方式

由于对事务的支持及 MVCC 机制存在，不同会话中的事务执行 COUNT() 统计行数时，可能返回不一样的结果。下面我们将举例说明。

假设我们有一个 person_info 表，它的 DDL 如下：

CREATE TABLE `person_info` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `sex` char(1) NOT NULL,
  `phone_number` char(11) NOT NULL,
  `country` varchar(100) NOT NULL
) ENGINE=InnoDB;

初始情况下，person_info 表中有 10000 条数据。

此时我们开启三个会话，session_a\session_b\session_c，它们的内容及启动时机如下：

-- session_a
BEGIN ; -- 01
SELECT count(*) FROM person_info; -- 02   
-- 10000

SELECT count(*) FROM person_info; -- 07
-- 10000

-- session_b
BEGIN ; -- 04
INSERT INTO `person_info` values(4, 'zhangsan', now(), '13162110002', 'Japan');  -- 05
SELECT count(*) FROM person_info; -- 06
-- 10002

-- session_c
INSERT INTO `person_info` values('lisi', now(), '13162110001', 'China');  -- 03

SELECT count(*) FROM person_info; -- 08
-- 10001

首先 session_a 先开启一个事务。由于 MySQL 中默认的事务隔离级别为可重复读，所以不管其他事务怎么修改数量，它读取到的记录数量永远是10000（02、06）。
在 session_a 执行过一次 count(*) 查询后，session_c 插入了一条数据（03）。其没有显式地开启事务，且默认情况下 autocommit = 1，所以一条语句就是一个事务。
之后，session_b 开启一个事务，此时03中事务已提交，所以在 session_b 开启的一致性读视图中，数据条数有10001条。然后，它插入一条数据（05），再查询数据（06）得到结果10002，事务尚未提交。
session_a 和 session_c 中进行查询使用得是各自的一致性读视图，所以结果不相同。

01.2-COUNT(*)、COUNT(1)、COUNT(COLUMN) 性能比较

先说结论：COUNT(*) =~= COUNT(1) > COUNT(COLUMN)

COUNT(*) 在查询时被替换成了 COUNT(0)，所以与 COUNT(1) 近似的性能。可以通过以下命令验证：

explain select count(*) from person_info;
show warnings;

+-------+------+------------------------------------------------------------------------+
| Level | Code | Message                                                                |
+-------+------+------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select count(0) AS `count(*)` from `mydb`.`person_info` |
+-------+------+------------------------------------------------------------------------+

COUNT(0) 或 COUNT(1) 的实现原理是：

在所有的二级索引中，找空间最小的索引，遍历叶子节点，但不会取值。Server 层对返回的每一行都将累计值加1。
找空间最小的索引的原因是，空间越小，每页存储的索引就越多，加载页到内存的操作次数就越小，遍历效率就越高。

COUNT(COLUMN) 的实现原理是：

在二级索引中（如何可以），或聚簇索引中，遍历所有的记录，取对应的 column 值返回给 Server 层。 Server 层判断值是否为 NULL，不是则累计值加1，否则不加。
如果 column 为主键的化，可以选择一个最小的索引数去遍历，所以 COUNT(KEY_COLUMN) > COUNT(NON_KEY_COLUMN)。

02-保存表记录总数的设计方案

使用 InnoDB 情况下，既然使用 COUNT(*) 获得记录行数随着记录数的逐渐增多，遍历代价会越来越大。一个头痛医头的解决方案就是，将表记录的总数量记录在缓存中，每次插入、删除记录时，同步更新行数。接下来我们将分析下这个方案。

02.1-方案一：MySQL + Redis

使用 Redis 缓存记录总行数，首先面临的第一个问题是缓存丢失问题。不过这个问题可以通过持久化解决，或者在 Redis 奔溃重启后，再次执行 COUNT(*) 去统计下记录数并缓存在 Redis 中。

这里仍然存在着数据不一致的风险。 MySQL 和 Redis 是两个独立的组件，要保证数据一致性面临分布式事务问题，即更新数据库、更新 Redis 这两个操作如何保证原子性。为解决这个问题，可以将行数存储在 MySQL 中额外的表中。接下来，我们将介绍这个方案。

02.2-方案二：MySQL + 额外的表

将表记录中行数存储在 MySQL 表中，可以解决存储在 Redis 中遇到的分布式事务问题。将插入数据、更新行数放在一个 MySQL 事务中，可以解决数据不一致问题。

如果不需要特别精确的记录行数，可以使用近似值。例如，通过百度搜索时，结果数就是个近似值。

获取近似值的方式：

explain，结果中的 rows 就是一个近似值。

-- explain select * from person_info;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | person_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+

show table status like 'person_info'; 结果中的 Rows 就是一个近似值。

-- show table status like 'person%';
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name        | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| person_info | InnoDB |      10 | Dynamic    |    4 |           4096 |       16384 |               0 |        16384 |         0 |              5 | 2022-12-01 10:54:42 | 2022-12-01 10:54:42 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+-------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+

MySQL「06」COUNT() 实现方式及区别