mysql 主键选择

最近研究uuid，收集的一些资料：

 

http://www.mysqlops.com/2011/09/10/innodb-primary.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()函数产生的值：

oot@localhost : (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位无符号的整数，例如：

root@localhost : (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)存储）做主键，效率到底如何，我也不知道，抱歉 -_-!!!