MySQL计数器表

标签（空格分隔）： 高性能MYSQL 第四章 schema与数据类型 计数器表

---

1. 计数器表在Web应用中很常见，可以用这种表缓存一个用户的朋友数、文件下载次数等等。
   假设有一个计数器表，只有一行数据，记录网站的点击次数：

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

每次点击更新计数器：

mysql> UPDATE hit_counter SET cnt = cnt + 1;

1. 要获得更高的并发性，可以将计数器保存在多行中，每次随机选择一行进行更新，要获取统计结果时，作聚合查询。
   如下修改：

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;

1. 一个常见的需求是每隔一段时间开始一个新的计算器（例如，每天一个）。再作进一步修改：

mysql> 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（MySQL独有语法）代替：

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;