mysql - SELECT COUNT 查询匹配 10 亿行表中的 1 亿行，即使有索引也需要 30 秒

Question

我试图了解 MySQL 中的索引以及它们如何工作以及它们在多大程度上提高了速度。我创建了一个包含 10 亿行的表，其中有一列名为“index_this”的列包含 10 个可能的字符串值，大致均匀分布(因此每个值大约有 1 亿行)。这是我正在运行的查询。

SELECT COUNT(*) FROM mydatabase.mytable WHERE mytable.index_this = ‘A1ndxstring’;

运行此查询需要 30 秒，在 ‘index_this’ 列上有一个 BTREE 索引。我检查了查询优化器是否正在使用索引。

EXPLAIN SELECT COUNT(*) FROM mydatabase.mytable WHERE mytable.index_this = ‘A1ndxstring’;

返回

id              1
select_type     SIMPLE
table           mytable
type            ref
possible_keys   index_this
key             index_this
key_len         43
ref             const
rows            188130384
Extra           Using where; Using index

这是我创建索引的方式。

CREATE INDEX myindex ON mydatabase.mytable (index_this);

我没有指定前缀长度，但之前我确实尝试过将前缀长度设为 2，这只会使查询花费几乎 100 倍的时间。 30 秒对于这个表大小和这个查询是否合理，即使有索引？我正在寻找有关如何提高速度的任何建议。提前致谢。

Answer 1

索引前缀很少有帮助；经常会痛。
Using index说该索引是“覆盖的”并被使用。也就是说，不需要接触数据。而且，考虑到查询，没有什么比这更好的了。

那么，为什么需要这么长时间？简单的。它必须遍历索引中约 188M 的“行”。那可能是一百万个索引块。每个块为 16KB。您的磁盘需要多长时间才能通过几 GB？可能你经历的 30 秒是现实的。即使所有这些块都缓存在 buffer_pool 中，也需要一些时间来遍历它们。

如果有 1000 个不同的值，那么优化器仍然会选择相同的查询计划(“使用索引”)并且运行速度提高 100 倍。时间与接触的索引行数成正比。

基本上有两种运行查询的方法——使用索引与表扫描。它选择了“正确”的那个。没有统计数据、分析等可以提供帮助。

如果您想描述真正的目标，也许有一种不同的技术可以提供帮助——例如“汇总表”。有多种方法可以更新统计信息。触发器、单独的更新、SP、夜间汇总等——选择取决于性能、限制、对最新值的需求等。(如果你想深入挖掘，请开始一个新问题。)

或者...你说大约有 10 个不同的值？然后更改为 ENUM会将列缩小到一个字节，并将索引缩小可能是两倍。如果它必须从磁盘读取索引，那么它的运行速度将提高两倍；如果它被完全缓存，那么加速就会很小。

“向其扔硬件”不太可能有帮助。

CPU 速度——大约在 2000 年之前，CPU 变得越来越快。但从那以后，几乎没有什么变化。

核心数——MySQL 不会为一个查询使用多个核心。 (8.0.17(?) 很少有并行执行的情况。我认为您的查询不在其中。)

RAM 速度——此外，速度也不会很快。

RAM 大小——如果索引缓存在缓冲池中而不是从磁盘读取，则加速很明显。 (我怀疑您现在有足够的 RAM。)

磁盘速度——过去几十年来唯一一次大幅提升磁盘读取速度是从 HDD 到 SSD。但我的回答是假设您使用的是 SSD。