在大型 MySQL InnoDB 表上进行全计数查询真的那么慢吗？

Question

我们有一个包含数百万个条目的大表。完整计数非常慢，请参见下面的代码。这对于 MySQL InnoDB 表来说很常见吗？难道就没有办法加速这个过程吗？ 即使使用查询缓存，它仍然“慢”。 我还想知道，为什么具有 2.8 mio 条目的“通信”表的计数比具有 4.5 mio 条目的“事务”表的计数慢。

我知道使用 where 子句会更快。我只是想知道糟糕的表现是否正常。

我们使用 Amazon RDS MySQL 5.7 和 m4.xlarge（4 个 CPU、16 GB RAM、500 GB 存储）。我也已经尝试过使用更多 CPU 和 RAM 的更大实例，但查询时间没有大的变化。

mysql> SELECT COUNT(*) FROM transaction;
+----------+
| COUNT(*) |
+----------+
|  4569880 |
+----------+
1 row in set (1 min 37.88 sec)

mysql> SELECT COUNT(*) FROM transaction;
+----------+
| count(*) |
+----------+
|  4569880 |
+----------+
1 row in set (1.44 sec)

mysql> SELECT COUNT(*) FROM communication;
+----------+
| count(*) |
+----------+
|  2821486 |
+----------+
1 row in set (2 min 19.28 sec)

Answer 1

这是使用支持 多版本并发控制 (MVCC)。

InnoDB 允许您的查询在事务中隔离，而不会阻塞正在读取和写入数据行的其他并发客户端。这些并发更新不会影响您的事务的数据视图。

但是，考虑到在进行计数时许多行正在添加或删除，表中的行数是多少？答案是模糊的。

您的事务不应能够“查看”事务开始后创建的行版本。同样，即使其他人请求删除行，您的事务也应该对行进行计数，但他们是在您的事务开始后这样做的。

答案是，当您执行 SELECT COUNT(*) 或任何其他类型的需要检查多行的查询时，InnoDB 必须访问每一行，查看该行的当前版本对数据库的事务视图可见，并在可见时对其进行计数。

在不支持事务或并发更新的表（例如 MyISAM）中，存储引擎将行总数保留为表的元数据。该存储引擎无法支持多个线程同时更新行，因此行总数不太模糊。因此，当您从 MyISAM 表请求 SELECT COUNT(*) 时，它只会返回内存中的行数（但如果您执行 SELECT COUNT(*) 则这没有用） 使用 WHERE 子句按某些条件对行的某些子集进行计数，因此在这种情况下它必须实际对它们进行计数）。

总的来说，大多数人认为 InnoDB 对并发更新的支持非常值得，并且他们愿意牺牲 SELECT COUNT(*) 的优化。

Answer 2

除了比尔所说的之外......

最小索引

InnoDB 选择“最小”索引来执行 COUNT(*)。可能所有communication的索引都大于transaction的最小索引，因此存在时间差。判断索引的大小时，请将 PRIMARY KEY 列与任何辅助索引一起包含在内：

PRIMARY KEY(id),   -- INT (4 bytes)
INDEX(flag),       -- TINYINT (1 byte)
INDEX(name),       -- VARCHAR(255) (? bytes)

为了测量大小，PRIMARY KEY 很大，因为它包含（由于集群）表的所有列。 INDEX(flag) 是“5 个字节”。 INDEX(name) 平均可能有几十个字节。 SELECT COUNT(*) 将明确选择 INDEX(flag)。

显然交易有一个“小”索引，但通信没有。

TEXT/BLOG 列有时会“不记录”存储。因此，它们不计入 PK 指数的大小。

查询缓存

如果“查询缓存”打开，查询的第二次运行可能比第一次快得多。但这只是在表没有发生变化的情况下发生的。由于对表的任何更改都会使该表的所有 QC 条目失效，因此 QC 在生产系统中很少有用。我所说的“更快”是指大约 0.001 秒；不是 1.44 秒。

1m38s 和 1.44s 之间的差异可能是由于 buffer_pool（InnoDB 的通用缓存区域）中缓存的内容所致。第一次运行可能在 RAM 中找不到任何“最小”索引，因此它执行了大量 I/O，花费 98 秒来获取该索引的所有 450 万行。第二次运行发现所有数据都缓存在 buffer_pool 中，因此它以 CPU 速度运行（无 I/O），因此速度要快得多。

足够好

在这种情况下，我根本质疑执行 COUNT(*) 的必要性。请注意您如何说“2.8 mio 条目”，就好像 2 个有效数字“足够好”一样。如果您在 UI 上向用户显示计数，这难道不是“足够好”吗？如果是这样，性能的一种解决方案是每天计数一次并将其存储在某个地方。这将允许立即访问“足够好”的值。

还有其他技术。一是使用活动代码或某种形式的汇总表来保持计数器更新。

向其扔硬件

您已经发现更改硬件没有帮助。

• 98 的运行速度与 RDS 的任何 I/O 产品的运行速度一样快。
• 1.44s 的运行速度与任何 RDS CPU 的运行速度一样快。
• MySQL（及其变体）每次查询不会使用多个 CPU。
• 您有足够的 RAM，因此整个“小”索引都可以放入 buffer_pool 中，直到您的第二次 SELECT COUNT(*)..（RAM 太少会导致第二次运行非常慢） .)