Mysql计数器表设计

应用中很多情况下会用到存储计数，比如存储一个用户的朋友数，文件下载数，点赞数之类的。创建一张独立的表存储计数器通常是个好主意， 这样可以使计数去表小且快。实用独立的表可以帮助避免查询缓存失效，并且可以使用一些高级的技巧来处理并发问题。

假如有一个计数器表，只有一行数据，记录网站的点击次数：

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;

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

select sum(cnt) from hit_counter;

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

mysql> create table daily_hit_counter(
    -> day date not null,
    -> slot tinyint unsgined not null,
    -> 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,0 ,c.slot);

mysql> delete from daily_hit_counter where slot<>0 and cnt=0;