实现一个计数器的数据库设计

已有 6752人阅读此文 - - 数据库

       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,
->n tint unsigned not null,
->primary key(day,slot)
->)engine=InnoDB;

在这个场景中,可以不用像前面的例子那样预先生成指定数据的行,而用ON Duplicate key updat来代替:

mysql> insert into daily_hit_counter(day,slot,cn) 
     ->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;

 

      

 

PS: ON Duplicate key update用法解释:

  如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE

内部的流程是这样的:

准备一条记录---》是否存在唯一一条记录---》存在更新计数字段,不存在插入一条记录。

使用ON Duplicate key update不但是减少了判断,更重要的你不需要多次的查询,不需要预先知道这条记录是否存在。


  • 太合衬 - 2015-04-05 19:55

    on duplicate key updat确实很方便。

期待你一针见血的评论,Come on!