Ist die vollständige Abfrage bei einer großen MySQL-InnoDB-Tabelle wirklich so langsam?

Question

Wir haben eine große Tabelle mit Millionen von Einträgen. Die vollständige Zählung ist sehr langsam, siehe Code unten. Ist dies bei MySQL InnoDB-Tabellen üblich? Gibt es keine Möglichkeit, diesen Prozess zu beschleunigen? Selbst mit Abfrage-Caching ist es immer noch „langsam“. Ich würde auch gerne wissen, warum die Zählung der Tabelle „Kommunikation“ mit 2,8 Mio. Einträgen langsamer ist als die Zählung der Tabelle „Transaktion“ mit 4,5 Mio. Einträgen.

Ich weiß, dass es schneller wäre, die Where-Klausel zu verwenden. Ich frage mich nur, ob eine schlechte Leistung normal ist.

Wir verwenden Amazon RDS MySQL 5.7 und m4.xlarge (4 CPUs, 16 GB RAM, 500 GB Speicher). Ich habe auch größere Instanzen mit mehr CPU und RAM ausprobiert, aber keine große Änderung bei den Abfragezeiten.

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 太少会导致第二次运行非常慢） .)