mysql对大表中的行进行计数的性能

这个相当明显的问题很少（找不到任何）可靠的答案。

我从200万行的表中进行简单选择。

select count(id) as total from big_table

我尝试此查询的任何计算机，通常至少需要5秒钟才能完成。这对于实时查询是不可接受的。

我需要获取准确的行值的原因是为了稍后进行精确的统计计算。

不幸的是，使用最后一个自动增量值不是一种选择，因为行也会定期删除。

在InnoDB引擎上运行时，确实确实会很慢。如《 MySQL 5.7参考手册》第14.5.7节“ InnoDB表的限制”第4点所述：

InnoDB不保留表中行的内部计数，因为并发事务可能同时“看到”不同数量的行。为了处理一条SELECT COUNT(*) FROM t语句，InnoDB扫描表的索引，如果索引不完全在缓冲池中，则将花费一些时间。为了快速计数，您必须使用自己创建的计数器表，并让您的应用程序根据插入和删除它来对其进行更新。如果大约有足够的行数，SHOW TABLE STATUS则可以使用。

建议的解决方案是一个计数器表。这是一个具有一行和一列的单独表，具有当前记录数。它可以通过触发器保持更新。像这样：

create table big_table_count (rec_count int default 0);
-- one-shot initialisation:
insert into big_table_count select count(*) from big_table;

create trigger big_insert after insert on big_table
    for each row
    update big_table_count set rec_count = rec_count + 1;

create trigger big_delete after delete on big_table
    for each row
    update big_table_count set rec_count = rec_count - 1;

您可以在此处看到一个小提琴，您应该在build部分中更改insert/ delete语句以查看其效果：

select rec_count from big_table_count;

您可以将其扩展到多个表，方法是为每个表创建一个表，或者在上述计数器表中为每个表保留一行。然后，将使用“ table_name”列作为键。

改善并发

如果您有许多并发会话插入或删除记录，则上述方法的确会产生影响，因为它们需要互相等待才能完成计数器的更新。

一种解决方案是不让触发器更新相同的单个记录，而让它们插入新的记录，如下所示：

create trigger big_insert after insert on big_table
    for each row
    insert into big_table_count (rec_count) values (1);

create trigger big_delete after delete on big_table
    for each row
    insert into big_table_count (rec_count) values (-1);

然后，获得计数的方法变为：

select sum(rec_count) from big_table_count;

然后，偶尔（例如每天）您应该重新初始化计数器表以使其较小：

truncate table big_table_count;
insert into big_table_count select count(*) from big_table;