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;