我应该在 InnoDB 中一起避免 COUNT 吗? [英] Should I avoid COUNT all together in InnoDB?

问题描述

现在，我正在争论是否使用 COUNT(id) 或count"列.我听说 InnoDB COUNT 在没有 WHERE 子句的情况下非常慢，因为它需要锁定表并进行全索引扫描.使用 WHERE 子句时是否有相同的行为?

Right now, I'm debating whether or not to use COUNT(id) or "count" columns. I heard that InnoDB COUNT is very slow without a WHERE clause because it needs to lock the table and do a full index scan. Is that the same behavior when using a WHERE clause?

例如，如果我有一个包含 100 万条记录的表.在没有 WHERE 子句的情况下执行 COUNT 将需要使用索引查找 100 万条记录.如果添加 WHERE 子句将符合条件的行数从 100 万减少到 500,000，查询会变得更快吗?

For example, if I have a table with 1 million records. Doing a COUNT without a WHERE clause will require looking up 1 million records using an index. Will the query become significantly faster if adding a WHERE clause decreases the number of rows that match the criteria from 1 million to 500,000?

考虑 SO 上的徽章"页面，会在 badges 表中添加一个名为 count 的列，并在用户获得该特定徽章时增加它比做更快a SELECT COUNT(id) FROM user_badges WHERE user_id = 111?

Consider the "Badges" page on SO, would adding a column in the badges table called count and incrementing it whenever a user earned that particular badge be faster than doing a SELECT COUNT(id) FROM user_badges WHERE user_id = 111?

使用 MyIASM 不是一种选择，因为我需要 InnoDB 的功能来维护数据完整性.

Using MyIASM is not an option because I need the features of InnoDB to maintain data integrity.

推荐答案

我不会说避免，但这取决于你想要做什么:

I wouldn't say avoid, but it depends on what you are trying to do:

• 如果你只需要提供一个估计，你可以做 SELECT MAX(id) FROM table.这要便宜得多，因为它只需要读取索引中的最大值.

• If you only need to provide an estimate, you could do SELECT MAX(id) FROM table. This is much cheaper, since it just needs to read the max value in the index.

如果我们考虑您给出的徽章示例，InnoDB 只需要计算用户拥有的徽章数量(假设 user_id 上有索引).我会说在大多数情况下不会超过 10-20，而且它根本没有太大的危害.

If we consider the badges example you gave, InnoDB only needs to count up the number of badges that user has (assuming an index on user_id). I'd say in most case that's not going to be more than 10-20, and it's not much harm at all.

这真的要视情况而定.我可能会将某人在主用户表上的徽章数量作为一列 (count_badges_awarded) 保留，因为每次显示头像时，该数字也是如此.这样我就不必进行 2 次查询了.

It really depends on the situation. I probably would keep the count of the number of badges someone has on the main user table as a column (count_badges_awarded) simply because every time an avatar is shown, so is that number. It saves me having to do 2 queries.

这篇关于我应该在 InnoDB 中一起避免 COUNT 吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！