MySQL中存储UUID的最佳实践

内容简介：在MySQL中有一个UUID () 函数，图1

在 MySQL 中有一个UUID () 函数，通常用UUID做唯一标识，需要在数据库中进行存储。使用此函数可以让MySQL生成一个UUID值，并以VARCHAR(36)类型的可读形式返回。如图1：

MySQL中存储UUID的最佳实践

图1

UUID值是非常随机的，因此常常被用来当做主键值(PRIMARY KEY)，而且这些以UUID作为主键的数据可以很容易的从不同的数据库中汇聚到一起。但是对于像MySQL的InnoDB存储引擎来说，使用UUID作为主键(PRIMARY KEY)会带来一些问题。

1、问题阐述

问题一：UUID的长度问题

UUID的长度为36个字符。假设数据库的字符集为UTF8，那么UUID的最大长度为2+3*26=110字节。如果这样的UUID作为主键的话，不仅会是主键的尺寸很大，而且会使二级索引的尺寸变大，原因是MySQL中的二级索引的value存的是PRIMARY KEY。由于主键和二级索引的尺寸很大，所以不利于在内存中操作

问题二：UUID的格式问题

MySQL的UUID ()使用的是version 1的UUID，该类型的UUID的特点是基于时间，它是一个128位的数字，由5个十六进制数字组成的utf8字符串表示，我们以图1中的UUID值为例：

432a4ec8-3642-11e9-805a-0050568238b5，每对字符实际上是一个在00-FF范围内的十六进制数; 总共有16个数字，前三个数字432a4ec8-3642-11e9是由时间戳生成。但是，最左边的组变化最快（每微秒10次）。我们可以验证，如图2

MySQL中存储UUID的最佳实践

图2

因为UUID是不连续的随机数，所以insert操作是随机的，数据被离散存储，造成innodb频繁的页分裂，使得insert的操作十分低效。

2、 结合问题定制方案

既然UUID作为主键带有这样那样的问题，难道说让我们在设计表结构时要放弃使用UUID吗？答案是否定的。我们可以通过采用binary(16)数据类型和重新安排UUID的顺序来解决之前提到的两个问题。

首先，BINARY(16) 这个二进制形式数据类型使用16个字节，比人类可读形式（“文本”形式）使用的VARCHAR（36）小的多。注意：只是二进制！没有字符集，没有排序，只有十六个字节。也许在某些应用程序中，文本形式仍然是必需的。那么我们可以使用虚拟列（MySQL5.7的新特性，虚拟列不占用存储空间）来存放文本形式的UUID。

然后，还有如何巧妙地重新排列二进制形式的字节的问题。我们在之前的问题二中已经了解到，MySQL的UUID()使用version1，最左边三个以破折号分隔的组是8字节的时间戳，最左边的第一组是时间戳的低四个字节; 第二组是中间两个字节时间戳，第三组是两个字节的高位时间戳，最左边的第一组变化最快。

因此，在我们存储UUID之前，重新安排UUID，使得快速变化的部分放到最后，例如：

把432a4ec8-3642-11e9-805a-0050568238b5重组为11e9-3642-432a4ec8-805a-0050568238b5

这种结构比起之前的结构更容易被cache缓存，同时存储上会更加连续。

3、方案验证

1)创建两张表

-- 使用原生的uuid作为主键

create table test_uuid (id_binvarchar(36) PRIMARY KEY, name varchar(200)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 使用重组后的uuid做为主键

create table test_uuid_ordered (id_bin binary(16) PRIMARY KEY, name varchar(200)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2)在mysql中创建转换函数uuidtobin

DELIMITER //