如何获得大表计数？ [英] How to get Count for large tables?

问题描述

样本表：

+----+-------+-------+-------+-------+-------+---------------+
| id | col1  | col2  | col3  | col4  | col5  | modifiedTime  |
+----+-------+-------+-------+-------+-------+---------------+
|  1 | temp1 | temp2 | temp3 | temp4 | temp5 | 1554459626708 |
+----+-------+-------+-------+-------+-------+---------------+ 

在表格上方拥有5000万条记录

1. （col1，col2，col3，col4，col5这些是VARCHAR列）


2. （id是PK）


3. （modifiedTime）

每个列被索引

例如：我的网站上有两个标签。

FirstTab-我使用以下条件打印上表的计数[col1如 value1％和col2如 value2％]

FirstTab - I print the count of above table with following criteria [col1 like "value1%" and col2 like "value2%"]

SeocndTab-我打印以上的计数符合以下条件的表格[col3 like value3％]

SeocndTab - I print the count of above table with following criteria [col3 like "value3%"]

由于我有5000万条记录，因此符合这些条件的计数

As I have 50 million records, the count with those criteria takes too much time to get the result.

注意：我有时会更改记录数据（表中的行）。插入新行。删除不需要的记录。

我需要一个可行的解决方案，而不是查询整个表。例如：像缓存较早的计数。

I need a feasible solution instead of querying the whole table. Ex: like caching the older count. Is anything like this possible.

推荐答案

虽然我确定MySQL可行，但这是使用触发器的Postgres解决方案。

While I'm sure it's possible for MySQL, here's a solution for Postgres, using triggers.

计数存储在另一个表中，每个插入/更新/删除操作都有一个触发器，用于检查新行是否满足条件，是否满足条件，将1加到计数中。触发器的另一部分检查旧行是否满足条件，如果满足，则减去1。

Count is stored in another table, and there's a trigger on each insert/update/delete that checks if the new row meets the condition(s), and if it does, add 1 to the count. Another part of the trigger checks if the old row meets the condition(s), and if it does, subtracts 1.

以下是触发器的基本代码， temp2 ='5'的行：

Here's the basic code for the trigger that counts the rows with temp2 = '5':

CREATE OR REPLACE FUNCTION updateCount() RETURNS TRIGGER AS 
$func$
BEGIN
   IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
      EXECUTE 'UPDATE someTableCount SET cnt = cnt + 1 WHERE 1 = (SELECT 1 FROM (VALUES($1.*)) x(id, temp1, temp2, temp3) WHERE x.temp2 = ''5'')'
      USING NEW;
   END IF;
   IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
      EXECUTE 'UPDATE someTableCount SET cnt = cnt - 1 WHERE 1 = (SELECT 1 FROM (VALUES($1.*)) x(id, temp1, temp2, temp3) WHERE x.temp2 = ''5'')'
      USING OLD;
   END IF;
   RETURN new;
END
$func$ LANGUAGE plpgsql;

这是dbfiddle上的一个有效示例。

您当然可以修改触发器代码，使其具有动态的表达式和存储计数对于表中的每个表，例如：

You could of course modify the trigger code to have dynamic where expressions and store counts for each in the table like:

CREATE TABLE someTableCount
(
   whereExpr text,
   cnt INT
);

INSERT INTO someTableCount VALUES ('temp2 = ''5''', 0);

在触发器中，您然后遍历条件并进行相应更新。

In the trigger you'd then loop through the conditions and update accordingly.

这篇关于如何获得大表计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！