为什么索引不能加速这个查询？

Question

我有两个表格users和posts，每个表格都有50万条记录。

我想要找到发表了100到200篇文章的用户。

我的查询语句是：

SELECT u.accountid, COUNT(*)
FROM users u
JOIN posts p
ON u.accountid = p.owneruserid
GROUP BY u.accountid
HAVING COUNT(*) BETWEEN 100 AND 200;

我大约在一秒钟内得到了答案。

我在users和posts表的accountid和owneruserid字段上添加了索引，但是查询没有加速。为什么？

- user4230877

---

1秒钟似乎相当合理。 - Gordon Linoff

@GordonLinoff 这只是用于测试。我只是不明白为什么索引完全没有起到帮助作用。 - user4230877

索引主要是为了高效地获取数据子集。但是看起来你的查询需要获取并处理所有数据（仅在分组操作发生后进行子集操作）。因此，索引无法提供帮助并不奇怪。 - user1919238

@VaoTsun 我会检查它。 - user4230877

请发布它们 :) 不仅仅是检查 - Vao Tsun

Answer 1

HAVING COUNT(*) BETWEEN 100 AND 200;

这段文字的关键在于解释为什么索引是无用的。

我们需要获取成员数量在100到200之间的群组。这意味着对于每个群组，我们需要精确计算成员数量。第二点是我们没有任何限制（例如WHERE部分），因此要获取所有计数和群组，我们需要遍历表中的所有记录。

索引，例如B-Tree索引，有助于根据索引条件找到正确的元素（行）。如果数据以某种方式排序（索引提供了排序），我们可以使用二进制搜索来查找所需的子集。但在我们的情况下，我们需要扫描所有记录。因此，它们是否排序都无关紧要。

这就是为什么索引不能加速查询的原因。

- StanislavL

---

我需要的答案。非常感谢。 - user4230877

Answer 2

您可以简化查询为：

SELECT p.owneruserid, COUNT(*)
FROM posts p
GROUP BY p.owneruserid
HAVING COUNT(*) BETWEEN 100 AND 200;

posts(owneruserid)上的索引应该适用于此查询。它是查询的覆盖索引，因此查询可能会快一点。

总体而言，查询似乎需要扫描posts中的所有数据进行聚合。 HAVING无法利用索引。但是，查询可以使用覆盖索引来减少I/O。

- Gordon Linoff

---

索引无法起作用。它请求 HAVING COUNT(*) BETWEEN 100 AND 200，这需要进行全面扫描以获取所有计数。 - StanislavL

但是，如果他把计数结果放在临时表中，然后在100和200之间进行计数...是否比使用Having计数条款更快？从未测试过，所以不得不询问。 - Veljko89

@StanislavL 你能多说一些关于它的内容吗？能提供一些参考资料吗？ - user4230877

@user4230877，你能使用这个简化版本并在之后加入用户表吗？ - Malte Hartwig

@MalteHartwig . . . 具有讽刺意味的是，您的示例也解释了索引如何用于分组。 如果您想计算每位作者编写的书籍数量，则可以更简单地查看卡片目录而不是看书架上的书。 当然，有多少人在阅读此论坛时曾经见过图书馆中的实体卡片目录呢？ - Gordon Linoff