例子参考《高性能MySql》第三版4.4.2 计数器表(page 135)
由于mysql 没有执行成功,现在用sqlserver 模拟样本。计数器表可用于缓存一个用户的朋友数,文件下载次数等。
创建表 hit_counter 用于计数,先插入一行数据,每次更新增加1,总计则求和!
CREATE TABLE hit_counter( cnt int not null )
GO
insert into hit_counter values(0);
GO
UPDATE hit_counter SET cnt = cnt + 1;
GO 10
SELECT sum(cnt) FROM hit_counter;
GO
对于高并发的时候,这可能需要加锁等待,事务只能串行执行。要获得更高的并发更新性能,有可以将计数器保存在多行记录中,每次随机选择一行进行更新。这样做需要对计数器表进行如下修改:
DROP TABLE hit_counter;
GO
CREATE TABLE hit_counter(
slot tinyint not null primary key,
cnt int not null
) ;
GO
先添加100行数据,编号为1~100,计数初始值都为0。
declare @num int;
set @num=1;
while @num <= 100
begin
insert into hit_counter(slot,cnt) values(@num,0);
set @num=@num+1;
end
GO
若用户有操作,每次更新加1,100个编号中随机选择一行加1,使用聚集索引查找更新,事务可并发执行。
假如现在更新50次。
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = CEILING(RAND()*100);
GO 50
总计数时则求和,可以不加锁。
SELECT SUM(cnt) FROM hit_counter(nolock);
SELECT * FROM hit_counter(nolock);
以下为 MySql 的示例!
MySql 中测试没有成功,在使用以下条件更新或查询时,有时却返回多个值!原本更新1次,结果更新了多次。
WHERE slot = CEIL(RAND()*100)
WHERE slot = FLOOR(1+RAND()*100)
CREATE TABLE hit_counter(
cnt int unsigned not null
) ENGINE=InnoDB;
insert into hit_counter values(0);
UPDATE hit_counter SET cnt = cnt + 1;
DROP TABLE IF EXISTS hit_counter;
CREATE TABLE hit_counter(
slot tinyint unsigned not null primary key,
cnt int unsigned not null
) ENGINE=InnoDB;
DELIMITER //
CREATE PROCEDURE TempProc()
BEGIN
declare num int;
set num=1;
while num <= 100 do
insert into hit_counter(slot,cnt)
values(num,0);
set num=num+1;
end while;
END;
//
DELIMITER ;
CALL TempProc();
DROP PROCEDURE IF EXISTS TempProc;
SELECT * FROM hit_counter;
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = CEIL(RAND()*100);
SELECT SUM(cnt) FROM hit_counter;
表 hit_counter 有100行,字段 slot 编号为1~100,但是查询为什么有可能出现几行呢?
SELECT * FROM hit_counter WHERE slot = CEIL(RAND()*100);
SELECT * FROM hit_counter WHERE slot = FLOOR(1+RAND()*100);