注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

@fc_lamp

关注Web应用解决方案MySql/PHP/Python一盏名为"飞川"的灯~

 
 
 

日志

 
 

数据库数字参考表的妙用(自增ID的唯一性)  

2012-06-29 11:27:00|  分类: Web技术-Mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

数据库数字参考表的妙用(自增ID的唯一性)

今天的主角就是数字参考表,什么是数字参考表?一个表中,存放了从1开始连续到很大值的数字的表,我们称为数字参考表。

DROP TABLE IF EXISTS ref_number;

CREATE TABLE `ref_number` (

 `id` INT(11) NOT NULL AUTO_INCREMENT, 

 `status` TINYINT(3) DEFAULT 0 NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

#填充数据的存储过程

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `sp_pCreateNums`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_pCreateNums`( cnt INT UNSIGNED)

BEGIN

DECLARE s INT UNSIGNED DEFAULT 1;TRUNCATE TABLE ref_number;WHILE s <= cnt DOBEGININSERT INTO ref_number(id) SELECT s;

SET s= s+1;

END;

END WHILE; 

 END$$

DELIMITER ;

主题就是数据库中参考表的妙用,我们分三种需求来分别说明数据库中参考表的使用,主要针对解决数据库的“历史问题”。

一、两个事务同时锁定一个不存在id导致的死锁

两个事务同时向数据库插入记录,插入的id是一样的。插入前查询表中是否存在这个id,如果不存在,那么会给这个id上锁。mysql允许多个事务给同一条不存在的记录上锁,然后在插入记录,这个时候就产生冲突和死锁。
解决这个问题的方法还是挺多。
方案一:有可能的话,尽量使用auto_increment。
方案二:如果不能使用auto_increment,程序尽量维护这个id的唯一性,不要重复使用。
方案三:引入参考表。
如果业务表不能建立auto_increment,也不方便做一个算法来维护这个唯一性的列,那么我们建立一个数字参考表,如开始建立的ref_number表。

id列对应业务的id列,status对应这个列是否被使用,0为未使用,1为使用,修改业务表之前先从数字参考表中获取一个未使用的id,
然后修改参考表的状态值,在使用这个值插入到表中,这样就不会因为两个事务锁住同一个不存在的id而产生死锁。

二、两张同时有写入的表之间数据同步,如何处理自增长主键的冲突问题。

这个问题在特殊的情况下才会出现,主要是前期规划和后期需求的冲突导致的。
首先描述一下需求和环境:
一个数据库实例里有两个数据库(schema),两个数据库内分别有两个表。php.test和java.test。
test表大概如下:

CREATE TABLE `test` ( 

`id` int(11) NOT NULL AUTO_INCREMENT, 

 `status` tinyint(3) DEFAULT NULL, 

 PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这个schema中表结构大致一样,记录也是一样的,只是字段多少而已。现在的问题是多个团队和功能会往这两个表插入数据,还有程序需要对这两个表做双向同步,保证他们数据一致性。

现在我们模拟插入两条数据。

insert into php.test values ('','mysqlops');结果 1,mysqlops

insert into java.test values ('','mysqlops');结果 1,mysqlops

这样在做数据表的双向同步就产生了冲突。
我能想到的解决方案有以下几个:
1、值留一个表,取消另外一个表,这个是最简单最理想的方案了。不过理想和现实往往有很大差距。
由于两个表存在有一定差异性,不同团队的代码都对两个表有依赖关系,所以暂时还没法实施,但最终会这样(我希望最终会这样)。
2、取消auto_increment,程序能够产生唯一有意义的主键,现在还没有找到可用和可控制的唯一标识代替auto_increment。
由于是多个团队操作这两个表,程序生成自增长id也是不现实的。
3、使用主主复制,php和java分开在不通的master上写入,调整auto_increment的offset,来保证每个服务器的auto_increment和对方是不冲突的。
这也是我们现在使用的方法,不过问题在于启用了master-master的双主写入,在架构维护上有一些问题,比如没法做到HA,数据库迁移或者操作的时候限制更多。
4、也就是我们今天说的参考表。
建立一个参考表,php和java先去参考表获取一个没有使用的id,然后把参考表上这个id的状态置为已使用,然后使用获取的id插入到自己操作的表中。
这个方案相对修改代码较少,对数据库架构也没有影响。

三、在某些查询时候需要使用的参考数据

下面的方法主要吸收与姜老的 《mysql技术内幕 sql编程》
1、查询一个字段连续的值有哪些,这个需要数字参考表。
这个查询和数字参考表没太大关系,主要使用的是临时生成的行号,不过特别有趣,分享给大家。

create table t(a int unsigned not null primary key);

insert into t select 1;

insert into t select 2;

insert into t select 3;

insert into t select 100;

insert into t select 101;

insert into t select 103;

insert into t select 104;

insert into t select 105;

求出哪些是连续的,并给出连续的范围。

SELECT MIN(a) start_range, MAX(a) end_range

FROM (SELECT a, rn, a-rn AS diff FROM (SELECT a, @a := @a + 1 rn FROM t , (SELECT @a :=0) AS a) AS b) 

AS cGROUP BY diff ;

2、使用数字参考表可以更快的预建立时间维度表

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `sp_createTimeDim`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_createTimeDim`(START DATE,END DATE)

BEGIN

SELECT DATE_ADD(START,INTERVAL id-1 DAY) FROM ref_number WHERE id<=DATEDIFF(END,START)+1; 

 END$$

DELIMITER ;

来源:http://www.mysqlops.com/2012/06/15/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%95%B0%E5%AD%97%E5%8F%82%E8%80%83%E8%A1%A8%E7%9A%84%E5%A6%99%E7%94%A8.html

  评论这张
 
阅读(464)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017