计数器表--使用MySQL做计数的技巧

墨蓝 2022-02-25 15:24 302阅读 0赞

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数，文件下载次数等。创建一张独立的表存储计数器通常是个好主意，这样可使计数器表小且快。使用独立的表可以帮助避免查询缓存失效，并且可以使用本节展示的一些更高级的技巧。

应该让事情变得尽可能简单，假设有一个计数器表，只有一行数据，记录网站的点击次数：

mysql> CREATE TABLE hit_counter(
    		-> cnt int unsigned not null
    		->) ENGINE=InnoDB;

网站的每次点击都会导致对计数器的更新：

mysql> UPDATE hit_counter SET cnt=cnt+1;

问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁（mutex）。这会使得这些事务只能串行的执行。要获得更高的并发更新性能，也可以**将计数器保存在多行中，每次随机选择一行进行更新**，这样做需要对计数器表进行如下修改：

mysql> CREATE TABLE hit_counter(
    		->slot tinyint unsigned not null primary key,
    		-> cnt int unsigned not null
    		->) ENGINE=InnoDB;

然后预先在这张表增加100行数据。现在选择一个随机的槽（slot）进行更新：

mysql>UPDATE hit_counter SET cnt=cnt+1 where slot= RAND()*100;

要获得统计结果，需要使用下面这样的聚合查询：

mysql>SELECT SUM(cnt) FROM hit_counter;

一个常见的需求是每隔一段时间开始一个新的计数器（例如，每天一个）。如果需要这么做，则可以再简单地修改一下表设计：

mysql> CREATE TABLE daily_hit_counter(
    		->day date not null,
    		->slot tinyint unsigned not null primary key,
    		-> cnt int unsigned not null
    		-> primary key(day,slot)
    		->) ENGINE=InnoDB;

在这个场景中，可以不用像前面的例子那样预先生成行，而用ON DUPLICATE KEY UPDATE代替：

mysql>INSERT INTO daily_hit_counter(day,slot,cnt)
    		-> VALUES(CURRENT_DATE,RAND()*100,1)
    		->ON DUPLICATE KEY UPDATE cnt=cnt+1;

如果希望减少表的行数，以避免表变得太大，可以写一个周期执行的任务，合并所有的结果到0号槽，并且删除所有其他的槽：

mysql>UPDATE daily_hit_counter as c
    		-> INNER JOIN(
    		->			SELECT day,sum(cnt) AS cnt, MIN(slot) AS mslot
    		->			FROM daily_hit_counter
    		->			GROUP BY day
    		->    )AS x USING(day)
    		->SET c.cnt=IF(c.slot=x.mslot,x.cnt,0),
    		->			c.slot=IF(c.slot=x.mslot,o,c.slot);

mysql> DELETE FROM daily_hit_counter where slot <> o AND cnt=0;

*注：ON DUPLICATE KEY UPDATE 是作用在唯一列上。*  
*来源：《高性能MySQL》*