文档章节

MySQL 自增主键和UUID

learn_more
 learn_more
发布于 2016/12/16 12:12
字数 1795
阅读 582
收藏 2

    关于自增主键和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 技术内幕》

© 著作权归作者所有

上一篇: MySQL Explain
learn_more
粉丝 93
博文 240
码字总数 210196
作品 0
深圳
程序员
私信 提问
加载中

评论(0)

Mybatis插入数据行ID生成策略

Oracle: MySQL: 针对自增主键的表,在插入时不需要主键,而是在插入过程自动获取一个自增的主键,比如MySQL,可以采用如下两种配置方式: 或 UUID: 在Oracle中: 在Mysql中: 注:UUID如不...

boonya
2016/06/15
4.8K
0
mysql InnoDB UUID 主键 性能优化【原理篇】.md

mysql InnoDB uuid 主键 性能优化【原理篇】.mdmysql InnoDB UUID 主键 性能优化【实践篇】.md 有序uuidmysql InnoDB UUID 主键 性能优化【原理篇】.mdmysql InnoDB UUID 主键 性能优化【性能...

北京起航
2015/05/22
1.2K
0
Mysql InnoDB 引擎 主键性能

前些天看到网上有人说:“Mysql InnoDB 引擎 主键不适合用UUID , 若要用UUID的话可考虑用 自增ID做物理主键,UUID做逻辑主键。” 带着以上问题,本人做了如下测试: 先自报测试环境: 测试电...

chi_9
2013/05/23
1.3K
1
mybatis 通用mapper返回插入主键

查看地址:http://git.oschina.net/free/Mapper/blob/master/wiki/mapper3/3.Use.md 3.主键策略(仅用于insert方法) 通用Mapper还提供了序列(支持Oracle)、UUID(任意数据库,字段长度32)、主键...

之渊
2016/11/05
5.9K
0
MySQL UUID函数的详解

MySQL UUID函数的详解 MySQL中可以有二类用于生成唯一值性质的工具:UUID()函数和自增序列,那么二者有何区别呢?我们就此对比下各自的特性及异同点: l 都可以实现生成唯一值的功能; l UU...

风中帆
2016/05/21
592
0

没有更多内容

加载失败,请刷新页面

加载更多

00-Java 面试准备

面试之前 面试前准备简历需要注意的几个方面: 写简历、改简历,这个一定要干的。简历有两个作用,一个是吸引别人,能让别人邀请你去面试,这是前提;另一个是引导面试的人,让面试的人问你所...

源程序
今天
54
0
OSChina 周二乱弹 —— 大王(@罗马的王)颜值制霸Osc社区

Osc乱弹歌单(2020)请戳(这里) 【今日歌曲】 @巴拉迪维 :Lunik的单曲《Seeing You Soar》 I hope you’re smiling,When seeing me soar. #今日歌曲推荐# 《Seeing You Soar》- Lunik 手...

小小编辑
今天
83
0
wordcount代码

1.写出map类 public class WCMapper extends Mapper<LongWritable,Text,Text,LongWritable>{ @Override protected void map(LongWritable key,Text value,Context context)throws IOExcepti......

七宝1
今天
59
0
Spring Batch 小任务(Tasklet)步骤

Chunk-Oriented Processing不是处理 step 的唯一方法。 考虑下面的一个场景,如果你仅仅需要调用一个存储过程,你可以在 ItemReader 中实现这个调用,然后在存储过程完成调用后返回 null。这...

honeymoose
今天
67
0
Linux日志分析

1. Linux日志文件的类型 2. 系统服务日志 2.1 syslogd的简介 2.2 syslogd的配置和使用 2.3 日志的安全性设置 2.4 远程日志记录服务 3. 日志的轮替 3.1 logrotate简介 3.2 logrotate的配置 3....

JiaMing
昨天
67
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部