MySQL表中存储UUID值作为主键,使用UNHEX()提升性能

微wx笑 2022-11-17【数据库】 1 0关键字： mysql

假设我们有一个用户表，每个用户都有一个UUID。MySQL有一个UUID（）函数，它使MySQL生成一个UUID值，并以VARCHAR（36）类型的可读形式返回。让我们试试MySQL 5.7.8：mysql> select uuid();+

假设我们有一个用户表，每个用户都有一个UUID。MySQL有一个UUID（）函数，它使MySQL生成一个UUID值，并以VARCHAR（36）类型的可读形式返回。让我们试试MySQL 5.7.8：

mysql> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| aab5d5fd-70c1-11e5-a4fb-b026b977eb28 |
+--------------------------------------+

mysql> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| aab5d5fd-70c1-11e5-a4fb-b026b977eb28 |
+--------------------------------------+
所以第一个想法是简单地做到这一点：

create table users(id varchar(36), name varchar(200));
insert into users values(uuid(), 'Andromeda');9YS无知

9YS无知

create table users(id varchar(36), name varchar(200));
insert into users values(uuid(), 'Andromeda');
但是这种UUID的可读形式并不紧凑。让我们观察一下：

四个破折号是多余的
每对字符实际上是一个在00-FF范围内的十六进制数; 总共有16个数字（以上为：0xAA，0xB5等），每个数字可以存储在一个字节中。
所以我们可以使用REPLACE（）去掉破折号，UNHEX（）把每个
双字符对转换成一个字节：

create table users(id_bin binary(16), name varchar(200));
insert into users values(unhex(replace(uuid(),'-','')), 'Andromeda');9YS无知

9YS无知

create table users(id_bin binary(16), name varchar(200));
insert into users values(unhex(replace(uuid(),'-','')), 'Andromeda');
这个二进制形式使用16个字节，比人类可读形式（我现在称之为“文本”形式）使用的VARCHAR（36）小得多。如果UUID必须是主键，则增益更大，如InnoDB中的主键值被复制到所有二级索引值中。

二进制（16）是...好吧...只是二进制！没有字符集，没有排序，只有十六个字节。适合我们的需求。

也许在某些应用程序中，文本形式仍然是必需的，所以让我们把它作为表格中的一个附加列; 但为了尽量减少磁盘占用，让我们将文本形成一个虚拟的生成列（这是MySQL 5.7的一个新特性，在CREATE TABLE的文档中有描述）。该列将通过二进制格式列的公式计算：我们将二进制格式转换回十六进制数字并插入破折号。

create table users(
id_bin binary(16),
id_text varchar(36) generated always as
(insert(
insert(
insert(
insert(hex(id_bin),9,0,'-'),
14,0,'-'),
19,0,'-'),
24,0,'-')
) virtual,
name varchar(200));

insert into users (id_bin,name)
values(unhex(replace(uuid(),'-','')), 'Andromeda');

select id_text, name from users;
+--------------------------------------+-----------+
| id_text | name |
+--------------------------------------+-----------+
| C2770D2E-70E6-11E5-A4FB-B026B977EB28 | Andromeda |
+--------------------------------------+-----------+9YS无知

9YS无知

create table users(
id_bin binary(16),
id_text varchar(36) generated always as
(insert(
insert(
insert(
insert(hex(id_bin),9,0,'-'),
14,0,'-'),
19,0,'-'),
24,0,'-')
) virtual,
name varchar(200));

insert into users (id_bin,name)
values(unhex(replace(uuid(),'-','')), 'Andromeda');

select id_text, name from users;
+--------------------------------------+-----------+
| id_text | name |
+--------------------------------------+-----------+
| C2770D2E-70E6-11E5-A4FB-B026B977EB28 | Andromeda |
+--------------------------------------+-----------+
我没有在SELECT中包含id_bin，因为它会以隐藏字符（ASCII码0xC2，0x77等：通常不在人可读的字符范围内）出现。我们没有理由需要看id_bin的内容; 但是，如果这样做，则可以使用HEX（id_bin）来显示其十六进制代码。

请注意，id_text被声明为VIRTUAL，因此在磁盘上的表中不占用空间。

使id_text成为生成列的另一个好处是消除了两列之间的任何不一致的风险。事实上，如果id_text是一个简单的列，可以做

update users set id_bin = <something>;

而无意更新id_text。但是作为一个生成的列，id_text不能直接更新，而是在更新id_bin时自动更新。换句话说，信息只在一个地方（id_bin），数据库保证了一致性。

那么，那么查询呢？例如，我们可能想通过UUID找到一个用户：

select * from users where <it has UUID XYZ>;9YS无知

9YS无知

WHERE子句应该指定二进制还是文本形式？这取决于：

如果我们创建一个二进制形式的索引：

alter table users add unique(id_bin);

那么，为了使用这个索引，WHERE应该指定二进制形式：

WHERE id_bin = binary_form_of_XYZ

相反，如果我们在文本表单上创建一个索引：

alter table users add unique(id_text);

那么，WHERE应该指定文本形式：

WHERE id_text = text_form_of_XYZ

即使id_text是一个虚拟列，也可以像上面那样在其上添加索引（在这种情况下，索引占用磁盘空间）。这是MySQL 5.7.8中引入的一个新功能。

但是，如果我们有一个选择，由于二进制形式更短，它索引它看起来更合乎逻辑，而不是文本形式 - 索引将更小，从而更快地遍历，更快的备份...

最后，还有如何巧妙地重新排列二进制形式的字节的问题。

要了解这一点，我们需要了解更多关于UUID的信息。它们存在几个版本，不同的来源可以生成不同的版本。MySQL的UUID（）使用版本1，这意味着，正如在RFC 4.1.2中所解释的那样，三个最左边的以破折号分隔的组是8字节的时间戳：最左边的组是时间戳的低四个字节; 第二组是中间两个字节，第三个组是高（最重要）两个字节的时间戳。因此，最左边的组变化最快（每微秒10次）。我们可以验证：

mysql> select uuid(); do sleep(2); select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 3b96402f-70c5-11e5-a4fb-b026b977eb28 |
+--------------------------------------+
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 3cc7f7dc-70c5-11e5-a4fb-b026b977eb28 |
+--------------------------------------+

mysql> select uuid(); do sleep(2); select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 3b96402f-70c5-11e5-a4fb-b026b977eb28 |
+--------------------------------------+
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 3cc7f7dc-70c5-11e5-a4fb-b026b977eb28 |
+--------------------------------------+
你可以看到最左边的8个字符是如何变化的，而其他的则没有。

因此，在由单个机器连续产生的UUID序列中，所有UUID具有不同的第一字节。将该序列插入索引列（二进制或文本形式）将因此每次修改不同的索引页面，从而防止内存中的缓存。因此，在我们存储到id_bin之前，重新安排UUID，使得快速变化的部分走到最后，是有意义的。再一次请注意，这个想法只适用于版本1的UUID。

这个想法不是我的 ; 我在第一次看到它这个博客(http://mysql.rjweb.org/doc.php/uuid)和那一个(https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/)。

以下，通过改变时间低/时间中/时间高到时间高/时间中/时间低来重新排列二进制形式。

create table users(id_bin binary(16), name varchar(200));

set @u = unhex(replace(uuid(),'-',''));

insert into users (id_bin,name)
values
(
concat(substr(@u, 7, 2), substr(@u, 5, 2),
substr(@u, 1, 4), substr(@u, 9, 8)),
'Andromeda'
);

create table users(id_bin binary(16), name varchar(200));

set @u = unhex(replace(uuid(),'-',''));

insert into users (id_bin,name)
values
(
concat(substr(@u, 7, 2), substr(@u, 5, 2),
substr(@u, 1, 4), substr(@u, 9, 8)),
'Andromeda'
);
我在（@u）之上使用了一个用户变量，因为每个SUBSTR（）调用需要引用UUID值，但是我不能写UUID（）四次：每次都会生成一个新的UUID！所以我调用一次UUID（），删除破折号，将其转换为二进制文件，将其存储在一个变量，并做它的四个SUBSTR。

但是，我仍然希望文本格式处于“未重新排列”的顺序，因为...或许这个文本格式将用于一些错误日志记录，调试？如果人类要阅读它，我不想通过重新排列的顺序来混淆它们。
添加id_text可以在CREATE TABLE中完成，或者作为后续的ALTER TABLE：

alter table users add
id_text varchar(36) generated always as
(
insert(
insert(
insert(
insert(
hex(
concat(substr(id_bin,5,4),substr(id_bin,3,2),
substr(id_bin,1,2),substr(id_bin,9,8))
),
9,0,'-'),
14,0,'-'),
19,0,'-'),
24,0,'-')
) virtual;9YS无知

9YS无知

alter table users add
id_text varchar(36) generated always as
(
insert(
insert(
insert(
insert(
hex(
concat(substr(id_bin,5,4),substr(id_bin,3,2),
substr(id_bin,1,2),substr(id_bin,9,8))
),
9,0,'-'),
14,0,'-'),
19,0,'-'),
24,0,'-')
) virtual;
它将二进制形式的部分（带有SUBSTR）放在“正常”位置（CONCAT），将字节转换为十六进制数字（HEX）并插入破折号。对，这是一个复杂的表达式，但是在创建生成的列时只能输入一次。

现在看看数据：

select id_bin, hex(id_bin), name, id_text from users;
+------------------+----------------------------------+-----------+--------------------------------------+
| id_bin | hex(id_bin) | name | id_text |
+------------------+----------------------------------+-----------+--------------------------------------+
| �p�:�ˤ� &�w� | 11E570EA3A059CCBA4FBB026B977EB28 | Andromeda | 3A059CCB-70EA-11E5-A4FB-B026B977EB28 |
+------------------+----------------------------------+-----------+--------------------------------------+

select id_bin, hex(id_bin), name, id_text from users;
+------------------+----------------------------------+-----------+--------------------------------------+
| id_bin | hex(id_bin) | name | id_text |
+------------------+----------------------------------+-----------+--------------------------------------+
| �p�:�ˤ� &�w� | 11E570EA3A059CCBA4FBB026B977EB28 | Andromeda | 3A059CCB-70EA-11E5-A4FB-B026B977EB28 |9YS无知