关闭

MySQL计数器表

标签: 计数器表mysql
1054人阅读 评论(0) 收藏 举报
分类:

在Web开发中经常会遇到需要计数器表的场景,如果将计数器保存在表中,在操作表时可能会遇到并发问题。因此,通常的做法是为计数器单独建立一个表,在写入操作时通过事务在处理业务表的同时处理计数器。

        比如一个典型的场景:记录新闻的浏览量。这个场景的表结构可能如下所示:

create table article(
	article_id int unsigned auto_increment primary key,
	article_author varchar(20) not null,
	article_title  varchar(50) not null,
	article_content text not null
);
create table article_counter(
	article_id int unsigned primary key,
	cnt int unsigned not null
);


在添加或者删除新闻纪录时,需要在事务中操作计数器表。此时有一个问题,当大量的事务访问同一个新闻的计数器时,只能串行执行。

为了更高的并发更新性能,我们为计数器表增加一个池子slot,此时计数器表的结构如下:

create table article_counter(
	article_id int unsigned,
        slot tinyint unsigned,
	cnt int unsigned not null,
    <span style="white-space:pre">	</span>primary key(article_id,slot)
);

池子slot的存在使得每条新闻不再只有一个计数器,而是多个计数器。比如可以设置slot的大小是10。这样不同的事务在访问的时候,随机生成一个数字(1-10)作为slot,如何该slot存在则更新cnt=cnt+1,否则插入cnt=1。

能够表达上面意思的语句如下:

insert into article_counter values(1,RAND()*10,1) on duplicate key update cnt=cnt+1;

  在这里有必要说下onduplicate key update的用法,它不是标准的sql语句,而是mysql特有的语句。它表示的含义是:如果插入行后会导致在一个唯一键索引或主键索引中出现重复值,则执行UPDATE

  通过上面叙述的slot设计方案,可以避免多个事务更新一个新闻的同一行,而是更新一个新闻的多个行。当需要查询某个新闻的浏览量时,可以使用下面的语句:

select sum(cnt) from article_counter where article_id=1;
  对于上面的查询语句,可以建立一个覆盖索引以提高性能,如下:

alter table article_counter add index index_articleid_cnt(`article_id`,`cnt`);

  有些时候,一个常见的需求是每隔一段时间开始一个新的计数器(比如每一天),如果需要这么做,仅仅需要修改下表的设计:

create table article_counter(
	article_id int unsigned,
	day date,
        slot tinyint unsigned,
	cnt int unsigned not null,
        primary key(article_id,day,slot)
);
  对于上面的表,我们先来插入一些数据(多执行几次):

insert into article_counter values(1,LEFT(NOW(),10),RAND()*10,1) on duplicate key update cnt=cnt+1;
insert into article_counter values(1,LEFT(NOW(),10)-interval 10 day,RAND()*10,1) on duplicate key update cnt=cnt+1;

  如果希望减少表的行数,以避免表变的太大,可以写一个周期的任务,合并所有的结果到0slot中去,并且删除其他的slot。

update article_counter as o
inner join
(select article_id,day,min(slot) as mslot,sum(cnt) as scnt from article_counter group by article_id,day) as n
using(article_id,day)
set o.cnt=if(o.slot=n.mslot,n.scnt,0),o.slot=if(o.slot=n.mslot,0,o.slot);
delete from article_counter where  slot<>0 and cnt=0;

        PS:凡事都是双刃剑。为了更快的读我们通常要牺牲一些东西。在读比较多的表要加快读的速度,在写较多的表要加快写的速度。各自权衡。在加快读的速度的时候,我们牺牲的并不仅仅是写的性能,还有开发成本,开发变的更复杂,维护成本等。所以并不是读的速度越快越好,需要找一个平衡点。







0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:133434次
    • 积分:1588
    • 等级:
    • 排名:千里之外
    • 原创:138篇
    • 转载:10篇
    • 译文:0篇
    • 评论:32条
    最新评论