1. 云栖社区>
  2. PHP教程>
  3. 正文

mysql 主键选择

作者:用户 来源:互联网 时间:2017-12-01 12:40:13

mysql选择

mysql 主键选择 - 摘要: 本文讲的是mysql 主键选择, 转载:http://www.cnblogs.com/chutianyao/archive/2012/11/04/2753995.html(针对InnoDB引擎)我们实际生产环境可能会使用四类属性作为主键: (1). 自增序列; (2).

转载:http://www.cnblogs.com/chutianyao/archive/2012/11/04/2753995.html(针对InnoDB引擎)我们实际生产环境可能会使用四类属性作为主键:


(1). 自增序列;


(2). UUID()函数生成的随机值;


(3). 用户注册的唯一性帐号名称,字符串类型,一般长度为:40个字符;


(4). 基于一套机制生成类似自增的值,比如序列生成器;


那么我们接下来,再分析下这四类属性各自作为表主键的优缺点:


(1). 自增序列:从小到大 或从大到小的顺序模式增加新值;数据类型也利于进行主键值比较;存储空间占用也相对最小,一般设置为:4个字节的INT类型或 8个字节的BIGINT类型;若是想进行数据水平拆分的话,也可以借助设置mysqld实例的2个参数:auto_increment_increment 和 auto_increment_offset;另外,唯一缺点就是自增序列是一个表级别的全局锁,在5.0系列大规模并发写的时候,因锁释放机制的问题容易出现瓶颈,但是5.1系列做了改进,基本上不存在此问题;


(2). UUID()函数:值为随机性+固定部分,其值产生是无序的,且同一台服务器上产生的值相同部分为77.8%;产生的值字符个数为36,按utf8编码计算,占用的存储空间为36个字节;对于数据水平拆分支持,无需特殊设置;


(3). 使用用户注册的帐号名称,字符串类型,其值的产生依赖用户输入,为此数据基本上为无序增加,字符串的长度也是不定的,只能通过前段技术控制最短最大长度值的限制,对水平拆分支持,无需做特殊设置;


(4). 序列生成器的架构,类似自增序列,不过需要借助额外的开发工作量,以及提供一个第三方的服务,可以规避自增序列的字增全局锁的问题,提高并发,对数据水平拆分可以更好地支持;


(5). 双主复制架构的概率性碰到的场景:主服务器的数据执行成功,而没有复制到在线备用服务器时,出问题的概率确实存在,其他类型的做法,也必须人工干涉解决,都无简单且合理的自动化办法,以上四种办法都无法规避;


通过四种属性值作为主键的优缺点分析,以及对比前面我们阐述的主键需要的优秀素质,若是不考虑水平拆分的问题,带来额外设置上的麻烦,则自增序列是最佳的主键字段选择;用户的注册帐号本身要求唯一性且非空的场景下,则可以作为主键字段的选择;若是考虑水平拆分的问题,则采用自增序列生成器的架构,非常易用和可靠的实现方式,产生的值是最佳主键字段的选择;


MySQL UUID函数的详解


http://www.mysqlops.com/2011/03/01/mysql-uuid.html


MySQL中可以有二类用于生成唯一值性质的工具:UUID()函数和自增序列,那么二者有何区别呢。我们就此对比下各自的特性及异同点:


l 都可以实现生成唯一值的功能;


l UUID是可以生成时间、空间上都独一无二的值;自增序列只能生成基于表内的唯


一值,且需要搭配使其为唯一的主键或唯一索引;


l 实现方式不一样,UUID是随机+规则组合而成的,而自增序列是控制一个值逐步增长的;


l UUID产生的是字符串类型值,固定长度为:36个字符,而自增序列产生的是整数类型值,长度由字段定义属性决定;


接下来,详细讲解下UUID()函数产生的值:


[email protected] : (none) 06:09:40> SELECT UUID(),UUID(),LENGTH(UUID()),CHAR_LENGTH(UUID())/G


***************** 1. row *****************


UUID(): de7ee638-4322-11e0-85ab-842b2b4a7e75


UUID(): de7ee642-4322-11e0-85ab-842b2b4a7e75


LENGTH(UUID()): 36


CHAR_LENGTH(UUID()): 36


1 row in set (0.00 sec)


从上面的执行结果部分的信息看:


l 同一个SQL语句中,多处调用UUID()函数得到的值不相同;


l 得到的随机值由5个部分组成,且分隔符位为:中划线;


l 多次调用或执行得到的后2组值相同,若把mysqld服务器关闭,重新启动之后,会发现第四组的组与未重启前的值发生变化,然后一直不变化,只要重新启动mysqld服务就会发生变化。另外,对于同一台机器,第五组值始终不会发生变化;


l 字符个数为:36,占字节数为:36(注:系统默认字符集编码:utf8);


针对UUID产生的值组成部分,作如下解说:


l 前三组值是时间戳换算过来的;


l 第四组值是暂时性保持时间戳的唯一性。例如,使用夏令时;


l 第五组值是一个IEE 802的节点标识值,它是空间上唯一的。若后者不可用,则用一个随机数字替换。假如主机没有网卡,或者我们不知道如何在某系统下获得机器地址,则空间唯一性就不能得到保证,即使这杨,出现重复值的机率还是非常小的。


UUID函数对复制的支持:


UUID函数属于不确定性函数,为此不支持MySQL 复制的STATEMENT模式,但是支持MIXED、ROW二种模式,大家可以设置2组测试模式,以5.1.系列版本为例。


测试基于命令行模式复制:


tx_isolation = REPEATABLE-READ


binlog_format = STATEMENT


测试基于命令行/混合模式复制:


tx_isolation = REPEATABLE-READ


binlog_format = MIXED OR ROW


在主服务器上执行同一个SQL语句:


INSERT INTO test_uuid(username) VALUES(UUID());


然后再比对主从服务器上表中存储的值,会发现基于命令行模式的:主从不一致,基于行/混合模式的:主从数据时一致;


建议:在复制模式下,需要用到UUID()函数,则一定要使用基于行/混合模式复制方式。


名词解释:


对于输入参数相同,且同一时间执行或一个SQL中多处调用,而得到不同值得函数,我们就称其为:不确定性函数。


备注:


在MySQL 5.1.*及更高版本有一个变种的UUID()函数,名称:UUID_SHORT(),生成一个64位无符号的整数,例如:


[email protected] : (none) 02:46:42> SELECT UUID_SHORT()/G


***************** 1. row *****************


UUID_SHORT(): 6218676250261585921


1 row in set (0.00 sec)


后续加注:


UUID()函数产生的值,并不适合作为InnoDB引擎表的主键,至于详细的原因,请阅读文章InnoDB引擎表的主键选型。


UUID做主键,好还是不好。这是个问题。


http://mlxia.iteye.com/blog/279059


作者:老王


我唯一还算熟悉的数据库就算是MySQL了,大概使用MySQL的人,百分之九九以上的人会使用Autoincrement ID做主键,这是可以理解的,因为MySQL的自增ID效率很高,使用也很方便。那么剩下的百分之一的人使用什么做主键呢。可能是自己做的KeyGenerator,也可能是我们下面要说的UUID。


据说在Oracle的圈子里,如果谁用自增ID做主键是要被鄙视的,主键最自然的选择就是UUID。我不了解Oracle,这些道听途说的结论是否正确不做承诺。


那么我们先看看什么是UUID。简单的说,UUID是指在一台机器上生成的数字,它保证对在同一时空中的所有机器都是唯一的。在UUID的算法中,可能会用到诸如网卡MAC地址,IP,主机名,进程ID等信息以保证其独立性。


如果你的MySQL版本不太老的话,键入 SELECT UUID(); 输出的就是UUID,如下:


mysql> select uuid();+————————————–+| uuid() |+————————————–+| 54b4c01f-dce0-102a-a4e0-462c07a00c5e |+————————————–+


现在大家应该对UUID有一个比较直观的认识了,我们来看看UUID的优缺点分别是什么。


优点:


能够保证独立性,程序可以在不同的数据库间迁移,效果不受影响。保证生成的ID不仅是表独立的,而且是库独立的,这点在你想切分数据库的时候尤为重要。


缺点:


比较占地方,和INT类型相比,存储一个UUID要花费更多的空间。使用UUID后,URL显得冗长,不够友好。


下面针对上述UUID的缺点说说我的看法,比较占地方这个缺点我不是很在乎,现在最不值钱的就是硬盘了,略过此条缺点无妨。至于说使用UUID后,URL显得不友好,我觉得这多少是你的INT情结造成的惯性思维,其实,和INT类型相比,UUID才是最自然的主键选择,注意,我这里用的是自然这个形容词,仔细体会一下你能理解我的意思。另外,很多时候,URL本身就不需要友好,比如,一个电子商务网站,按照INT友好的URL说法,她的订单URL大概是下面这个形式的:/order.php/id/123,我要说明的是,这样是很友好,但是有些太友好了,友好的甚至不安全,比如说,我早晨下一个订单,发现URL是/order.php/id/1000,晚上再下一个订单发现URL是/order.php/id/2000,那么我就可以估计出此网站一天的订单数大致是1000左右,甚至能大体估计出它的销售额,而这些数据往往都是重要的商业秘密。使用UUID就没有这个顾虑。


效率。


如果上面说的UUID的所谓缺点都不成立的话,那么是否使用UUID做主键,唯一的问题就是效率了。据说在PostgreSQL等数据库里,都有专门的UUID类型,在这样的数据库里,使用UUID做主键,效率没有任何问题,可惜在MySQL里没有这样的字段,如果想在MySQL里保存UUID做主键,一般是使用CHAR(36)来模拟,因为不是一个原生的UUID类型,所以主键的效率到底如何有待测试,另外,UUID做主键的效率和UUID本身的算法实现也有很大关系。


我本来想在我自己的电脑上插入1000000条数据测试一下看看来着,可惜一测试,硬盘灯就一直亮,让我很担心它会挂,虽然硬盘不值钱,但是我重要的数据都在上面,一旦坏了,损失就大了,所以,测试只好作罢。


至于在MySQL上使用UUID(用char(36)存储)做主键,效率到底如何,我也不知道,抱歉 -_-!!!

以上是云栖社区小编为您精心准备的的内容,在云栖社区的博客、问答、公众号、人物、课程等栏目也有的相关内容,欢迎继续使用右上角搜索按钮进行搜索mysql 选择 ,以便于您获取更多的相关知识。

稳定可靠、可弹性伸缩的在线数据库服务,全球最受欢迎的开源数据库之一

6款热门基础云产品6个月免费体验;2款产品1年体验;1款产品2年体验

弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率

开发者常用软件,超百款实用软件一站式提供