Mysql primary key主键冲突的可能性与解决方案
来源:互联网 发布:毕业分布图 知乎 编辑:程序博客网 时间:2024/05/18 00:01
在mysql中,数据的存储其实相当于Oracle的IOT表。主键和其他列的值以b+tree的形式组织在一起,在其叶子节点上不仅包含了主键,还包含了其他列的值。所以,我们在访问以主键作为where条件的查询时,极大的提高了效率。当然,这也存在一个缺点,主键和其他列存在一起,导致主键作为index比其他二级index需要的空间开销更大,所以当我们做recover index scan时,就会需要scan更多的block。除此之外,我们还需要数据按照主键升序排列,所以我们的主键就可能要设置为自增的,以免随机的数字作为主键,导致数据块频繁分裂,而减缓dml的速度。所以,一般情况下,我们会默认给每张表都加上一个自增的id作为主键,这个id的类型依据实际存放的数据量的范围来定。
从第一段,我们了解到了主键产生的必要性,那么主键的冲突又是如何产生的呢?
在mysql中,一般存在两种replication,一种是ms(master-slave),一种是mm(master-master),在ms中,主键冲突的可能性一般为0,除非应用程序自己插的主键值,而没有使用主键的自增序列。
在master-slave replication中,每次都是在master端写,然后通过binlog传到slave端,slave的IO_therad将数据写到relaylog,然后SQL_therad读取relaylog的信息,来进行主库执行sql的重演,这个过程基本上称为逻辑复制。每次,slave端根据master.info中的信息向主库发送请求,主库接收到请求后启用binlog dump进程将binlog的信息传送到slave端,slave端的io线程得到信息后,将信息写入relay log,sql线程读取relay log信息进行重演,所以备库的信息和主库是一致的,不会存在主键冲突的问题,因为写数据总是在一端进行的。
然而,在master-master replication中,主键冲突却是个实实在在的问题。因为这种架构中,主库既作为主库,也作为另一个库的备库。这种架构主要是在写比较严重的情况下,缓解单个库的写压力。因此,在这种设计中,primary key collision的问题是个需要解决的问题。假如存在两台master相同表的primary key有相同的increment and offset,就会导致在A库上插入的纪录传到B库后,在往B库中的表插入数据时会存在主键冲突的问题 。下面是在electrictoolbox摘抄的一句话:
If you have auto incremental primary keys then the seed for the increment value needs to be different for each server, otherwise you could potentially have multiple servers attempt to insert records at the same time resulting in primary key conflicts.
You will notice in the configuration there is a setting for auto_increment_increment. This should be set to the number of servers there are. In the example in this post, there are 2 servers so it should be 2. If you intend to add other masters in the future then set it to a higher value.
The second setting is auto_increment_offset which should be set to the same as the server-id value. As an example, if server 1 inserted 5 records and then server 2 inserted 2 records the PKs inserted would be 1, 3, 5, 7, 9, 10, 12
一般,在我们在有n(n>1)个Mysql库作为MM的情况下,每个库的incement都设置为n,我们一般设置第1个库的
auto_increment_offset = 1
auto_increment_increment = n
所以在第1个mysql库上的第k(k为大于0的正整数)次插入记录的主键的值为1+n*(k-1)。
同理,第m(n=>m>1)个mysql库的设置为
auto_increment_offset = m
auto_increment_increment = n
所以第m个mysql库上的第h(h为大于0的正整数)次插入的记录的主键值为m+n*(h-1)
假设1+n*(k-1)=m+n*(h-1)进一步得到 1=m+n(h-k),由于1<m<=n,所以在h=k的情况下,1=m与m>1矛盾,不成立。同样,如果h-k<0,因为h和k都是正整数,所以h-k<=-1,所以m+n(h-k)<=m-n,而m<=n,所以m-n<=0,所以m+n(h-k)<=0即m+n(h-k)!=1,所以也不成立,同样,在h-k>0时,由于m>1,n>1,所以m+n(h-k)>1与m+n(h-k)=1矛盾,综上所述,不会存在主键冲突的情况。,所以这种设置不存在主键冲突的问题,有效地解决了mm复制的过程中主键冲突的问题。所以,我们在一个有n个Mysql库作为MM复制的环境中,设置第k(1<=k<=n)个库的auto_increment_increment =n,auto_increment_offset =k,如此,无论在哪个master进行insert,复制到其slave后,都不会出现主键冲突的问题。
- Mysql primary key主键冲突的可能性与解决方案
- MySql 的unique key 与 primary key
- mysql 数据表中主键(primary key)的操作
- 9、MySQL-主键约束(PRIMARY KEY)
- oracle的primary key主键约束
- 主键(Primary Key)
- 主键约束--PRIMARY KEY
- MySQL如何创建主键?Alter table add PRIMARY KEY的用法和例子
- MySQL如何创建主键?Alter table add PRIMARY KEY的用法和例子
- MySQL如何创建主键?Alter table add PRIMARY KEY的用法和例子
- mysql为已经建立的表设置其主键(primary key)
- 【技巧】怎么在MySQL中去掉一个表格的主键(primary key);
- mysql primary key key
- mysql 中key , primary key ,unique key 与index的区别
- MySQL里Create Index 能否创建主键 Primary Key
- mysql 主键重复问题解决 Duplicate entry '787192513' for key 'PRIMARY'
- Unique Key 与 Primary Key 的比较
- Unique Key 与 Primary Key 的比较
- .net获取服务器网卡mac地址实现
- 2-23 Android Service (六)
- Android中自定义ListView无法响应OnItemClickListener中的onItemClick方法问题解决方案
- txt文件导入数据库
- android之View和SurfaceView
- Mysql primary key主键冲突的可能性与解决方案
- sortUsingSelector
- 从一段小程序看vector的[]下标运算符与size()
- Java第六课 数组的复制与排序,Runtime类与Process类,Class类与反射API,Runtime类与单例设计模式。
- 简单句、并列句、复合句、got his wish、 owe you
- Vmware clone 功能的陷阱
- Source Insight 自定义宏函数使用说明
- rand函数使用注意事项
- NSArray中的数据进行排序 ---- sortedArrayUsingDescriptors