【MySQL】计数器表

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

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

create table hit_counter(
    cnt int unsigned not null
) ENGINE=InnoDB;

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

update hist_counter set cnt = cnt + 1;

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

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

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

update hit_count set cnt = cnt + 1 where slot = rand() * 100;

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

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 key(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 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);

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

MySQL “ON DUPLICATE KEY UPDATE” 语法

如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的问题，则插入新行。

---

参考：高性能MySQL（第3版）. Baron Schwartz，Peter Zaitsev，Vadim Tkachenko 著；宁海元，周振兴，彭立勋 等 译