P.Linux Laboratory

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/database/count_table_skill.html

对于InnoDB及其他一些不能很快统计行数的存储引擎，对于频繁的count(*)操作，就是一种噩梦了，它可能要遍历一遍索引或者数据行才能知道有多少行。

这种时候我们比较容易想到的方法就是设计一个计数表，每次增删记录，就更新这个表：

CREATE TABLE cnt_tbl (
    tbl VARCHAR(20) NOT NULL PRIMARY KEY COMMENT '表名',
    cnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '计数'
);

先为要计数的表初始化

INSERT INTO cnt_tbl(tbl, cnt) VALUES('A', 0);

每当A表做一次INSERT，就更新一次计数表

UPDATE cnt_tbl SET cnt = cnt+1 WHERE tbl = 'A';

每当A表做一次DELETE，也更新一次计数表

UPDATE cnt_tbl SET cnt = cnt-1 WHERE tbl = 'A';

需要查询计数时只要做一次查表：

SELECT cnt FROM cnt_tbl WHERE tbl = 'A';

这种方法看似很好，但是如果A表更新频繁，计数表本身会成为一个悲剧。

于是我们做一个小改进，把更新的压力分散到多行，计数的时候做个SUM（需要支持行锁的数据库或存储引擎）：

CREATE TABLE cnt_tbl (
    tbl VARCHAR(20) NOT NULL COMMENT '表名',
    slt TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '多个计数槽',
    cnt INT NOT NULL DEFAULT 0 COMMENT '计数',
    PRIMARY KEY (tbl, slt)
);

这里我去掉了cnt列的UNSIGNED属性，为什么呢？因为这里会随机选一个计数槽更新，可能这个槽还没有被+1，但是已经被-1了，于是就有了负数。
可以先初始化256个计数槽

从INSERT INTO cnt_tbl(tbl, slt, cnt) VALUES('A',0,0)
到INSERT INTO cnt_tbl(tbl, slt, cnt) VALUES('A',255,0)

也可以写存储过程一次性搞定。
每当A表做一次INSERT，选择一个槽更新一次计数表

UPDATE cnt_tbl SET cnt = cnt+1 WHERE tbl = 'A' AND slt = RAND()*255;

每当A表做一次DELETE，选择一个槽更新一次计数表

UPDATE cnt_tbl SET cnt = cnt-1 WHERE tbl = 'A' AND slt = RAND()*255;

需要查询计数时需要做一次和：

SELECT SUM(cnt) FROM cnt_tbl WHERE tbl = 'A';

这样已经可以比较分散压力了，但是经常我们count(*)的时候是带有条件的，这又怎么处理呢？

对于带有条件的count(*)，假设我们需要按天计数，例如：

SELECT count(*) FROM A WHERE time BETWEEN '2010-03-21' AND '2010-03-22';

我们可以对计数表再做个改进，以达到这个目的：

CREATE TABLE cnt_tbl (
    tbl VARCHAR(20) NOT NULL COMMENT '表名',
    time DATE NOT NULL DEFAULT '0000-00-00' COMMENT 'WHERE条件',
    slt TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '多个计数槽',
    cnt INT NOT NULL DEFAULT 0 COMMENT '计数',
    PRIMARY KEY (tbl, time, slt),
    KEY (time)
);

这种情况下不可能预先插入行的，所以采用ON DUPLICATE KEY UPDATE
每当A表做一次INSERT，选择一个槽更新一次计数表

INSERT INTO cnt_tbl(tbl, time, slt, cnt) 
VALUES('A', CURRENT_DATE(), RAND()*255, 1)
ON DUPLICATE KEY UPDATE cnt = cnt+1;

每当A表做一次DELETE，选择一个槽更新一次计数表

INSERT INTO cnt_tbl(tbl, time, slt, cnt) 
VALUES('A', CURRENT_DATE(), RAND()*255, 1)
ON DUPLICATE KEY UPDATE cnt = cnt-1;

查询某个时间段的计数就可以这么做

SELECT SUM(cnt) FROM cnt_tbl WHERE time BETWEEN '2010-03-21' AND '2010-03-22';

如果需要其他WHERE条件，也可以利用这个思路相应的修改字段。
如果数据已经过期，不再被UPDATE，也可以考虑把它所有的计数槽合并到一个计数槽中，例如slt=0的槽，这样可以减少表的行数。