Mysql primary key主键冲突的可能性与解决方案

   在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后，都不会出现主键冲突的问题。