分享到: 分享到QQ  分享到Twitter

作者: BigLoser    访问次数: 1243 创建时间: 2020-03-18 19:39:17 更新时间: 2024-03-29 21:52:41

问题
记得刚接手项目时,看了下项目里的库表结构,发现表主键都是varchar(36),就问研发,为啥搞个这么长的主键?研发说这个主键是通过java 程序产生的UUID,考虑到以后数据分片不会产生重复。考虑的还是挺周到!但这么长字段做主键也有许多问题:

 

首先这个主键很长,而主键会自动添加在每一二级索引的后面,这样会导致数据冗余严重,特别是二级索引建多了的情况下,有时会导致索引的数据比表的数据还大!
其次,这样产生UUID是无序的,导致插入性能很差。因为当插入一个数据到B+tree时,系统将这个数据插入到一个合适的位置,这会导致多次IO,而如果数据本身是自增的,那么系统只须将它添加的B+Tree的末端即可,效率要高的多。
因此我还是建议使用整型+auto_increment, 虽然MySQLauto_increment有个众所周知的、历史悠久的bug(这个bug终于在MySQL8的到修复,MySQL8中auto_increment将会被写入redo log 中啦,爽歪歪),但大多数情况下还是可靠的,并且MySQL官方也建议使用auto_incremnet做主键。但它也有问题,就是无法适应数据分片的扩容(分库分表),除非你之前规划的非常好。

 

MySQL8中的解决方案


如今MySQL8 中对UUID提供了增强性支持,不仅使其长度大大缩小,而且解决了顺序的问题!(我之所以称之为增强性支持,是因为他们提供了两个内置函数做了转换,感觉这种方式有点儿low,这样的函数我们也可以写啊)

大家都知道,UUID是由32位16进制字符串组成(不算分隔符’-‘)如:


62ab1547-710f-11e8-9a58-5254007205d6


如果直接保存,则需要32个字符,utf8编码下占用96个字节,对于主键来说还是太长。幸运的是UUID中的每个字符都是16进制字符,两个16进制字符占用一个字节,这样可以轻松将UUID转换为binary(16),占用16个字节,所需空间大大减少,而且二进制字符串检索对比效率很高。

但还有一个至关重要的问题是UUID的组成中将timestamp 部分的低位时间段(如毫秒)放在了前面,高位时间段(如年月日)放在了后面,这会导致前面的字符变化很快,后面的变化很慢,从而使产生的UUID不能顺序自增。这会导致索引插入效率大大降低。

 

为解决这一问题,mysql8提供了两个函数:UID_TO_BIN(arg1) / BIN_TO_UUID(arg1,arg2)

 

UID_TO_BIN(arg1) 将UUID转化为16位二进制字符串,如果参数arg1为true则将UUID中的timestamp部分中的time-low(第一段字符)和time-high(第三段)调换,这样产生的UUID是顺序递增。


BIN_TO_UUID(arg1,arg2)将16位进制字符串转化为可读的UUID,arg1为16位二进制字符串,如果arg2省略或为false,即将二进制字符串原位转换;如果arg2为true,则将原来调换的time-low和time-high再调换回去,返回原本的uuid.


测试
创建一张表如下:


mysql8[test] > create table t (id varbinary(16) primary key,create_time timestamp default current_timestamp());


Query OK, 0 rows affected (0.34 sec)


插入几条数据,注意使用了函数uuid_to_bin:


mysql8[test] > insert into t (id)values(uuid_to_bin(uuid(),true));
Query OK, 1 row affected (0.08 sec)

mysql8[test] > insert into t (id)values(uuid_to_bin(uuid(),true));
Query OK, 1 row affected (0.08 sec)

mysql8[test] > insert into t (id)values(uuid_to_bin(uuid(),true));
Query OK, 1 row affected (0.07 sec)


查看结果:


mysql8[test]>select bin_to_uuid(id) id1,bin_to_uuid(id,true) id2, create_time from t;


+--------------------------------------+--------------------------------------+---------------------+
| id1                                  | id2                                  | create_time         |
+--------------------------------------+--------------------------------------+---------------------+
| 11e87113-f079-024e-8405-5254004332fa | f079024e-7113-11e8-8405-5254004332fa | 2018-06-16 11:18:28 |
| 11e87113-f826-4134-8405-5254004332fa | f8264134-7113-11e8-8405-5254004332fa | 2018-06-16 11:18:41 |
| 11e87113-f88c-c8a6-8405-5254004332fa | f88cc8a6-7113-11e8-8405-5254004332fa | 2018-06-16 11:18:42 |
+--------------------------------------+--------------------------------------+---------------------+


3 rows in set (0.00 sec)


注意字段id1使用了函数bin_to_uuid(id), 而id2使用了bin_to_uuid(id,true),注意他们在结果集中的区别。

如果需要按主键查询,还是需要使用对应的uuid函数:


mysql8[test]>select * from t where id=uuid_to_bin('f079024e-7113-11e8-8405-5254004332fa',true);


+------------------+---------------------+
| id               | create_time         |
+------------------+---------------------+
| 篓xf           | 2018-06-16 11:18:28 |
+------------------+---------------------+


1 row in set (0.00 sec)


总结


MySQL8通过提供两个内置函数解决了传统UUID作为主键的缺陷,也使UUID成为了设计主键的首选,特别是在数据分片的架构中,其优势十分突出。我当时一看到这个,还很兴奋,但UUID终究还不是一种数据类型,这多少给应用上增加了些麻烦。相信在MySQL的未来版本会出现一种数据类型叫UUID , 总不能一直弄两个函数糊弄人吧?!(其实目前官方也提到了实现一种新的数据类型,但增加一种数据类型属于重量级实现,代码变动太大,所以当前选择了增加两个函数)


————————————————
版权声明:本文为CSDN博主「Mount565」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/mount565/article/details/80738523

月度最有价值文章

投票统计

是否原创: 0 %

0 % Complete (success)

是否有价值: 0 %

0% Complete

是否有素质: 0 %

0% Complete (warning)

是否合法: 0 %

0% Complete

   群组工具

   外部链接