我应该在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?

例如，记录。不使用 WHERE 子句执行 COUNT 将需要使用索引查找100万条记录。如果添加 WHERE 子句将符合条件的行数从1百万减少到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上的徽章页面，在徽章表中添加一个名为 count 每当用户获得该特定徽章的速度超过执行 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表。因为它只需要读取索引中的最大值。

• 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上的索引）。

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屋！