flickr:http://wangxinchun.iteye.com/blog/2373125
目前flickr 方案存在的问题:
性能较差,每次都要连接数据库db,高并发下db吃力
新方案:
数据库每次批量返回多个id记录;双Buffer请求消峰,增加可用性
设计思路:
1、利用数据库的锁和事务
2、每次获取返回本次拿到min_id,max_id,step,batch_size,其中有效的记录是:id = min_id + n*step ,并且 id<=max_id
3、为了削弱波峰请求的影响,双Buffer机制,当id 取到 id > (min_id+max_id)/2 时,加载第二个Buffer
其中 2 和 3 需要服务的代理来实现
db 初始化数据如下:
DROP TABLE idg_1;
DROP TABLE idg_2;
DROP TABLE idg_3;
CREATE TABLE `idg_1` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`key` VARCHAR(100) DEFAULT '',
`min_id` BIGINT(20) NOT NULL ,
`max_id` BIGINT(20) NOT NULL DEFAULT '0',
`step` INT(11) NOT NULL DEFAULT '3',
`batch_size` INT(11) NOT NULL DEFAULT '3000',
UNIQUE KEY `id` (`id`),
UNIQUE KEY `idx_key` (`key`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `idg_2` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`key` VARCHAR(100) DEFAULT '',
`min_id` BIGINT(20) NOT NULL ,
`max_id` BIGINT(20) NOT NULL DEFAULT '0',
`step` INT(11) NOT NULL DEFAULT '3',
`batch_size` INT(11) NOT NULL DEFAULT '3000',
UNIQUE KEY `id` (`id`),
UNIQUE KEY `idx_key` (`key`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `idg_3` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`key` VARCHAR(100) DEFAULT '',
`min_id` BIGINT(20) NOT NULL ,
`max_id` BIGINT(20) NOT NULL DEFAULT '0',
`step` INT(11) NOT NULL DEFAULT '3',
`batch_size` INT(11) NOT NULL DEFAULT '3000',
UNIQUE KEY `id` (`id`),
UNIQUE KEY `idx_key` (`key`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO idg_1(`key`,max_id,min_id,step,batch_size) VALUE ('nts',1,1,3,1000);
INSERT INTO idg_2(`key`,max_id,min_id,step,batch_size) VALUE ('nts',2,2,3,1000);
INSERT INTO idg_3(`key`,max_id,min_id,step,batch_size) VALUE ('nts',3,3,3,1000);
#模拟数据查询
BEGIN
UPDATE idg_1 t SET t.min_id =t.max_id+step, t.max_id = max_id + step*batch_size WHERE `key` = 'nts';
SELECT t.min_id,t.max_id,t.step,t.batch_size FROM idg_1 t WHERE `key` = 'nts';
COMMIT;
END;
BEGIN
UPDATE idg_2 t SET t.min_id =t.max_id+step, t.max_id = max_id + step*batch_size WHERE `key` = 'nts';
SELECT t.min_id,t.max_id,t.step,t.batch_size FROM idg_2 t WHERE `key` = 'nts';
COMMIT;
END;
BEGIN
UPDATE idg_3 t SET t.min_id =t.max_id+step, t.max_id = max_id + step*batch_size WHERE `key` = 'nts';
SELECT t.min_id,t.max_id,t.step,t.batch_size FROM idg_3 t WHERE `key` = 'nts';
COMMIT;
END;
后记:
如果需要扩容,也很简单,只需要修改min_id,max_id,step 即可
1、先改动batch_size 足够大,保证操作期间不会有请求
2、修改min_id,max_id,step
注意:
step:机器数目
min_id = 一个上次返回的最大值+new_step
max_id=min_id 即可
这个思路稍微改动DB,可以升级到redis方案