在MySQL中加速行计数

Question

假设仅供演示目的，您正在使用一个简单的MySQL“books”表运行库，该表具有三列：

(id，title，status)

• id是主键
• title是书籍标题
• status可以是枚举，描述书籍的当前状态（例如：AVAILABLE，CHECKEDOUT，PROCESSING，MISSING）

报告每种状态下有多少本书的简单查询如下：

SELECT status, COUNT(*) FROM books GROUP BY status

或者特别查找有多少本书可用：

SELECT COUNT(*) FROM books WHERE status = "AVAILABLE"

然而，一旦表格增长到数百万行，这些查询需要几秒钟才能完成。在我的经验中，在“状态”列上添加索引似乎没有什么作用。除了定期缓存结果或每次书籍更改状态时显式更新摘要信息（通过触发器或其他机制），是否有任何技术可以加速这些查询？似乎COUNT查询最终会查看每一行，并且（不知道更多细节）我有点惊讶于这些信息不能从索引中某种方式确定。

更新：使用具有索引的示例表（“状态”列），含有200万行数据的情况下，我对GROUP BY查询进行了基准测试。使用InnoDB存储引擎，该查询在我的机器上花费3.0-3.2秒。使用MyISAM，该查询需要0.9-1.1秒。在任一情况下，count(*)、count(status)或count(1)没有明显差异。MyISAM确实稍微快一点，但我很好奇是否有一种方法可以使等效的查询运行得更快（例如10-50毫秒——足够快，可以在低流量站点的每个网页请求上调用），而不需要缓存和触发器的心理负担。听起来答案是“没有办法快速运行直接查询”，这就是我预期的答案——我只想确保我没有错过任何简单的替代方案。

- Kevin Ivarsen

---

使用select count(indexed_column) from book会有什么区别吗？ - Mr. Smith

你正在使用InnoDB还是MyISAM？ - Sam Saffron

@Boekwurm：不会的:)。mysql 优化了查询，所以 count(indexed_column)、count(*) 和 count(1) 的效率是相同的。 - Alterlife

Answer 1

所以问题是

有没有一些技巧可以加速这些查询？

实际上并没有。一种基于列的存储引擎可能会在那些SELECT COUNT(*)查询中更快，但对于几乎任何其他查询，它的性能都会较差。

你最好通过触发器来维护摘要表。它没有太多的开销，无论表有多大，SELECT部分都将是瞬间完成的。以下是一些样板代码：

DELIMITER //

CREATE TRIGGER ai_books AFTER INSERT ON books
FOR EACH ROW UPDATE books_cnt SET total = total + 1 WHERE status = NEW.status
//
CREATE TRIGGER ad_books AFTER DELETE ON books
FOR EACH ROW UPDATE books_cnt SET total = total - 1 WHERE status = OLD.status;
//
CREATE TRIGGER au_books AFTER UPDATE ON books
FOR EACH ROW
BEGIN
    IF (OLD.status <> NEW.status)
    THEN
        UPDATE books_cnt SET total = total + IF(status = NEW.status, 1, -1) WHERE status IN (OLD.status, NEW.status);
    END IF;
END
//

- Josh Davis

---

一个问题，那么视图相对于触发器呢？使用视图是否比在原始表上运行查询更快？ - Stewie

不，除非MySQL实现了物化视图，否则它们的性能与相应的SELECT语句大致相同。 - Josh Davis

但这难道不与内部SQL逻辑相悖吗？MySQL不是保留行估计值，因为如果不这样做，就无法保持行的精确计数而导致性能问题吗？例如，在许多情况下，您可以进行单元格或行级锁定。这意味着您可以同时插入/删除两行，但如果您实施此操作，则所有内容都绑定到单个数据片段，每次只能编辑一个。 - Jonathon

是否可以将其用于需要使用WHERE子句获取计数的情况？比如仅针对特定作者获取列计数？ - casraf

Answer 2

来源: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

InnoDB不会在表中保留行的内部计数。 (实际上，由于多版本控制，这可能有些复杂。) 为了处理SELECT COUNT(*) FROM t语句，InnoDB必须扫描表的索引，如果索引不完全在缓冲池中，则需要一些时间。

建议的解决方案是:

要快速计数，您必须使用自己创建的计数器表，并让应用程序根据其进行的插入和删除更新它。如果近似行计数足够，则还可以使用SHOW TABLE STATUS。

简而言之：对于包含大量行的表，count(*)（在innoDB上）将花费很长时间。这是设计如此，并且无法避免。

编写自己的解决方法。

- Alterlife

---

所引用的段落不适用于此案件。MyISAM仅优化不带WHERE子句的COUNT(*)，而这里并非如此。 - Josh Davis

Answer 3

MyISAM非常快，特别是在使用count(*)时，但是它的存储不够可靠，在数据完整性至关重要的情况下最好避免使用。

InnoDB执行count(*)类型查询可能会非常慢，因为它被设计用于允许多个并发视图查看相同的数据。因此，在任何时候，只去索引获取计数是不够的。

来自: http://www.mail-archive.com/[e-mail]/msg120320.html

数据库开始有1000条记录，我开始一个事务。你开始一个事务，我删除了50条记录，你添加了50条记录。我执行COUNT()，看到950条记录。你执行COUNT()，看到1050条记录。我提交了我的事务——除了你之外，数据库现在只有950条记录。你提交了你的事务——数据库再次有1000条记录。 InnoDB如何跟踪哪些记录对于任何事务是“可见”或“可修改”，是通过行级锁定、事务隔离级别和多版本控制。这就是导致每个人可以看到多少记录不那么直截了当的原因。http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-model.html http://dev.mysql.com/doc/refman/4.1/en/innodb-multi-versioning.html 所以底线是，如果您需要频繁快速地获取此信息，则需要考虑以某种方式缓存计数，而不是直接访问表。

- Sam Saffron

---

MyISAM的COUNT仅在没有WHERE子句时快速。 - Rick James

Answer 4

count(*)，count(status)或count(1)之间没有显着的差别。 count(column)返回column不为NULL的行数。由于1和status都不为NULL，数据库将优化掉测试并将它们全部转换为count(*)。具有讽刺意味的是，这并不意味着“计算所有列均不为空的行数”（或任何其他组合），而只是表示“计数行数”...

现在回到你的问题，你不能既要准确的统计数据，又希望写入速度快。你需要进行实时增量和减量，使用触发器，这会减慢写入速度；或者你可以使用count(*)，但这会导致速度变慢；或者你可以接受粗略估计值或过时的值，并使用缓存或其他概率方法。

通常，在“几个”以上的值上，没有人对准确实时计数感兴趣。它本身就是一个干扰因素，因为当你读取时，该值很可能已经改变了。

- bobflux

Answer 5

这里有许多答案表示索引无济于事，但在我的情况下它确实有所帮助...

我的表使用了 MyISAM 引擎，只有大约 10 万行。查询语句如下：

select count(*) from mytable where foreign_key_id=n

执行时间长达7-8秒。

我在foreign_key_id上添加了索引：

create index myindex on mytable (foreign_key_id) using btree;

创建完索引后，上述选择语句的执行时间为0.00秒。

- Witt

---

你的第二个查询可能从查询缓存中取得结果，无论索引如何都会立即返回上次的结果。 - henry700

好观点 - 我刚刚再次尝试了我的查询（几天后，表的内容已经被修改），计数只花费了0.02秒。所以你关于缓存的想法可能是正确的，但似乎索引仍然显著地提高了性能。 - Witt

在查询语句中添加 "sql_no_cache" 可以避免缓存。 - JoTAZUZ

0.02秒可能意味着行数很少。在没有缓存的情况下，COUNT(*)的时间与结果计数成比例。 - Rick James