数据库您现在的位置是:首页 > 博客日志 > 数据库

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

<a href='mailto:'>微wx笑</a>的头像微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无知

 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无知

 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无知

 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无知

 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无知

+------------------+----------------------------------+-----------+--------------------------------------+9YS无知

 9YS无知

专栏是:
1.隐含字符(重新排列的二进制UUID)
2.第一列的相应的十六进制代码
3.名字
4.未重新排列的文本UUID。看看这个文本形式最左边的3A059CCB是如何快速变化的部分,处于二进制形式(第2列)的中间,所以二进制形式将会提供更有效的索引。9YS无知

 9YS无知

java中使用UUID.randomUUID().toString().replace("-", "").toLowerCase()来生成UUID。9YS无知


9YS无知

本文为转载文章,版权归原作者所有,不代表本站立场和观点。

很赞哦! () 有话说 ()