MySQL计数器表的设计

  如果应用在表中保存计数器,则在更新计数器时可能碰到并发问题。计数器表在web应用中非常常见。可以用这个表缓存一个用户的朋友书、文件下载次数等。创建一张独立的表存储计数器是一种非常好的做法,这样可以使计数器表小并且快。使用独立的表可以帮助避免查询缓存失效。如下面这个例子:

  假设有一个计数器表,只有一行数据,记录网站的点击次数。

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

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

UPDATE hit_counter SET cnt = cnt + 1;

  那么问题出现了,对于任何想要更新这一行的事务来说,这条记录上都有全局的互斥锁。这会使得这些事务只能串行执行。要活的跟高的冰法更新性能,我们可以这样解决:

  将技术其保存在多行中,每次随机选择一行进行更新,这样需要对计数器表作如下修改:

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

  然后在这张数据表中增加100条数据。现在选择一个随机的槽(slot)进行更新:

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

  要获得统计结果,使用具和函数sum()进行查询:

SELECT SUM( cnt ) FROM hit_counter;

  但是还有一种常见的需求是每隔一段时间开始一个新的计数器(如每天一个)。想要实现这个,我们继续修改计数器表啊:

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

  在这个场景里,可以不用像前面那样,预先生成行,而是用 ON DUPLICATE KEY UPDATE代替:

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

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

UPDATE daily_hit_counter as c
    INNER JION(
        SEKECT 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 );
DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;

 

posted @ 2015-07-21 22:41  web开发者  阅读(2879)  评论(0编辑  收藏  举报