MySQL 自增主键和UUID

原创
2016/12/16 12:12
阅读数 1.1K

    关于自增主键和UUID的比较,可以从数据插入前,插入中,插入三个阶段进行比较,他们有各自的有点,当然也有各自的不足。下面就分三个阶段说说优缺点。

插入前

1)UUID 需要手动维护,要求是保证每次生成的数据都是不一致的,然后我们需要手写sql插入,如果代码逻辑中含有大量这种非业务相关的代码,其实是很不友好的,所以尽量透明。但是在代码中(Java)你可以在未插入之前就知道了主键ID,这个在有关联表需要插入时非常不错。

 

2)自增主键 插入前无需维护,编写的sql也不用管这个字段,数据库自己维护,对写程序来说完全透明。缺点就是插入前代码中(Java)并不知道主键。

 

插入中

1)UUID 长度相对比较大,如果大批量数据插入,在网络传输间,实际上这个字段占用的空间是不容小觑的,当然一般的程序不会担心这个,因为选用 Hibernate 的人,基本不会在乎某个字段的事。另外,既然是主键那么必然要考虑主键的唯一性,如何做这个唯一的校验呢?尤其是并发情况下,如果有多个相同的键同时插入怎么办?所以我觉得这里会有个锁(可能是锁索引,也可能是锁表),因此使用UUID并发插入肯定会发生阻塞的。这个可以去试验一下,开两个事务,使用相同的ID插入,后一个插入肯定会被阻塞,所以我觉得这里肯定有一个判断是否已经存在键的代码,而且是同步的,如果键存在那么阻塞后面的操作,当然初步判断不是锁表,如果是锁表会导致所有的插入阻塞,实际上不会。

 

2)自增主键 在插入时完全没有流量,因为都是数据库本身维护,所以很干净。关于自增主键插入有很

多需要补充的知识,这里先从MySQL以前的版本说起,以前采用的是:对每个含有自增长值的表都有一个自增长计数器(auto-increment counter),当有数据插入时就会执行操作:

select max(auto_inc_col) from t for update;

这个以前自增长字段锁的方式 AUTO-INC Locking,称为传统模式,他还有个很突出的特点是自增主键的值不用等待事务提交才生成,而是立刻生效,所以一定程度上提高了并发效率。但是,对于并发插入或者 insert..select 这种大批量数据插入的方式都是会被阻塞的。

然后就出现了新的自增长锁方式,版本> MySQL 5.1.22开始,InnoDB提供了一种轻量级互斥的自增长实现机制,在内存中会有一个互斥量(mutex),每次分配自增长ID时,就通过估算插入的数量(前提是必须能够估算到插入的数量,否则还是使用传统模式),然后更新mutex,下一个线程过来时从新 mutex 开始继续计算,这样就能避免传统模式非要等待每个都插入之后才能获取下一个。这种实现方式是通过 innodb_autoinc_lock_mode 的来来确定锁的方式。

我现在使用的 MySQL版本是 5.6.16 引擎是 innodb.

首先执行如下语句,检查数据库自增主键锁的模式(> MySQL 5.1.22 )

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode' 默认是 1

innodb_autoinc_lock_mode 有三个值分别是

0(traditional) ,这个就是之前说的 AUTO-INC Locking,并发相对较差

1(default consecutive),对不同的插入操作会有不同的方式,即传统方式、互斥方式

2(interleaved),完全使用互斥方式,并发最好,但是缺点明显,大并发下可能会出现重复。

我们只用考虑 innodb_autoinc_lock_mode = 1 的情况,另外两种是固定的模式,而连续模式到底是使用传统还是使用交叉模式,就是根据插入数据的量是否可估算的,先看看插入的分类:

Simple inserts

通过分析insert语句可以确定插入数量的insert语句,

Bulk inserts

通过分析insert语句不能确定插入数量的insert语句

Mixed-mode inserts

不确定是否需要分配 auto_increment id

名称 插入语法
Insert-like INSERT,INSERT … SELECT, REPLACE, REPLACE … SELECT, LOAD DATA, INSERT VALUES(),VALUES()
Simple inserts INSERT, INSERT … VALUES(),VALUES()
Bulk inserts INSERT … SELECT, REPLACE … SELECT, LOAD DATA
Mixed-mode inserts INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b');                INSERT … ON DUPLICATE KEY UPDATE

总结:如果是连续模式,那么对 Simple inserts 采用的是互斥方式,如果是 Bulk inserts 采用的是传统模式,Mixed-mode inserts 模式其实也是使用互斥方式,但是缺点就是浪费大量的ID。

 

回到问题上,mysql 对自增主键做了很多特殊处理,所以在插入的并发情况下还是可以接受的,当然相对UUID还是差了点,毕竟只要UUID只要维护唯一性,然而自增主键首先要考虑生成规则,然后还要考虑唯一性,然而就是这个生成规则MySQL已经采用互斥方式进行了改进。

 

 

 

插入后

1)UUID 还是长度的问题,这个影响的不仅仅是磁盘空间问题,更多还是索引的创建与查找,MySQL采用的 BTree+ 索引,而且主键都会附带在所有其他的索引的第一列(默认),首先导致索引空间变大,其次还会导致节点的分裂和移动,所以插入的时候因为需要维护索引更新,所以插入相对比较慢。另外UUID有一个很大的好处就是数据库合并,因为任何UUID都是不一致的,所以当做库-表合并时,不会出现主键冲突,这是自增主键做不到的。

 

2)自增主键 的长度相对较小,而且自增主键只能是数字,所以索引更快,磁盘空间更小,在数据插入时虽然会导致节点的分裂但是不会移动。

 

 

经验

使用 insert t values(),() 比使用批处理使用 insert t values();insert t values(); 要快上好几倍,所以建立使用第一种方式,当然如果你可以使用 insert t select ,那是最好不过了。在使用第一种方式也有注意的地方,如果values 里面已经有主键的值了,那么使用并发操作会发生锁超时,原因应该是在数据唯一性检查那里做了同步,如果没有主键的值,那么并发是最快的,因为它采用互斥增长方式生成主键ID。

 

参考

《mysql 技术内幕》

展开阅读全文
打赏
0
2 收藏
分享
加载中
更多评论
打赏
0 评论
2 收藏
0
分享
返回顶部
顶部