计数表的技巧

3月 21st, 2010 | Posted by | Filed under 数据库

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/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的槽,这样可以减少表的行数。

  1. h.e.
    4月 1st, 201022:06

    这个设计的改变对传统系统来说不算小,但是用户最终获得的体验又有多少喃?

    爱折腾是好事,但是很多时候还是需要权衡的,不过还是要感谢你(作者)的分享。

    [回复]

    P.Linux 回复:

    这个是不得已的情况下用的设计,系统能承受就没必要这么设计了。主要是系统承受不了count(*),以提高效率为根本目的的时候,也只能这么折腾了。

    [回复]

  2. iamacourser
    9月 6th, 201017:30

    不是dba,问一个可能是菜问题。
    一分钟update一个表的一行1000次和一分钟update一个表的若干行合计1000次,造成的压力是不一样的? 不都是1000次么,分散update也能降低压力的?

    [回复]

    P.Linux 回复:

    如果是表锁,确实差不多。但是如果是行锁,那就不一样了,update分散就可以并发

    [回复]

    iamacourser 回复:

    @P.Linux, 学习了 :)

    [回复]