在MySQL中加快行计数

一尘不染

在MySQL中加快行计数

mysql

出于说明目的，假设您正在使用带有三列的简单MySQL“ books”表运行一个库：

（ID，标题，状态）

id 是主键
书名是书的标题
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毫秒-
足够快，可以在低流量站点的每个网页请求上调用）无需担心缓存和触发器的开销。听起来答案似乎是“没有办法快速运行直接查询”，这正是我所期望的-
我只是想确保自己不会错过一个简单的选择。

阅读 286

2020-05-17

共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
//

2020-05-17