计数器表的设计

研究的问题

如何实现缓存用户的朋友数、文件的下载次数

相关知识点

sum() 求和函数    count() 统计数量函数    avg()平均值函数    RAND() 随机数（0-1之间）

min()最小值函数    CURRENT_DATE 当前系统日期    UNSIGNED 无符号

设计方案

业务场景1

问题：记录网站的点击次数

第一步：首先创建一张计数表（cnt字段为记录点击次数的字段）

CREATE TABLE website_click_count ( cnt INT UNSIGNED NOT NULL DEFAULT 0) ENGINE = INNODB;

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

INSERT into website_click_count (cnt) VALUES (0);

UPDATE website_click_count set cnt=cnt+1;

分析目前表结构存在的问题

1、所有的想更新这一行数据的事务来说，这条记录上都有一个全局的互斥锁（mutex）；

2、所有的事务都会成为串行执行，降低了性能

解决方案

第一步：将计数器保存在多行中，当业务执行时随机给出其中一行进行更新，下面是对表结构的修改

CREATE TABLE `website_click_count_v2` ( `slot` tinyint(3) unsigned NOT NULL AUTO_INCREMENT, `cnt` int(10) unsigned NOT NULL, PRIMARY KEY (`slot`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO website_click_count_v2 (cnt)VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);

第二步：网站的每次点击都会导致对计数器进行更新（此处的随机数建议在代码中生成，此处存在问题）

UPDATE website_click_count_v2 set cnt = cnt+1 where slot = ROUND(RAND()*100,0);

第三步：要获取统计结果，则使用聚合函数进行计算

SELECT sum(cnt) from website_click_count_v2;

业务场景2

如果需要按天进行统计点击数量

第一步：将每一天的计数器保存在多行中，当业务执行时随机给出其中一行进行更新，下面是对表结构

 

第二步：初始化数据，采用ON DUPLICATE KEY UPDATE 代替实现

INSERT into website_click_count_v3(day,slot,cnt) VALUES(CURRENT_DATE(),ROUND(RAND()*100),1)on DUPLICATE key UPDATE cnt = cnt +1;

第三步：统计结果，如果数据量太大，可以定时将结果统计到0槽里面，并删除相关数据

UPDATE website_click_count_v3 AS cINNER JOIN ( SELECT DAY, sum(cnt) AS cnt, min(slot) AS mslot FROM website_click_count_v3 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 website_click_count_v3 where slot<> 0 and cnt = 0;