MySQL计数器、每日计数器表设计与调优

计数器

如果应用在表中保存计数器，则在更新计数器时可能碰到并发问题。计数器表在Web应用中很常见。可以用这种表缓存一个用户的朋友数、文件下载次数等。创建一张独立的表存储计数器通常是个好主意，这样可使计数器表小且快。使用独立的表可以帮助避免查询缓存失效，并且可以使用本节展示的一些更高级的技巧。
应该让事情变得尽可能简单，假设有一个计数器表，只有行数据，记录网站的点击次数:

CREATE TABLE hit_counter ( 
    cnt INT UNSIGNED NOT NULL 
) ENGINE = INNODB;

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

UPDATE hit_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_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;

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

SELECT SUR(cnt) FROM hit_counter;

 

每日计数器

另外一个常见的需求是每隔一段时间开始一个新的计数器(例如，每天一个)。如果需要这么做，则可以再简单地修改一下表设计：

CREATE TABLE dally_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 `dally_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);

内容参考自《高性能MySQL》 P135