MySQL中使用UUID做主键时需要注意的两个坑（译文）

姚远Oracle ACE

已于 2023-03-28 10:23:40 修改

阅读量5.4k

点赞数 6

分类专栏： MySQL 翻译文章标签： mysql 数据库 java

于 2023-01-08 13:25:22 首次发布

原文链接：https://blogs.oracle.com/mysql/post/mysql-uuids

版权

MySQL 同时被 2 个专栏收录

92 篇文章

订阅专栏

翻译

12 篇文章

订阅专栏

在这里插入图片描述
摘要：越来越多的人在MySQL数据库使用UUID作为表的主键。大家都知道，对于MySQL的InnoDB存储引擎，主键非常重要！它对性能、内存和磁盘空间的影响巨大。

原文：https://blogs.oracle.com/mysql/post/mysql-uuids
作者： Frédéric Descamps，Oracle公司MySQL社区经理，知名MySQL布道师。
在这里插入图片描述
译者，姚远

小心两个坑

在InnoDB中使用UUID作为主键需要考虑两个问题：

UUID的返回值通常是随机的，而InnoDB的表实质是以主键组织存储的索引，插入新的记录会造成表的再平衡。
主键包含在每个二级索引中，过长的主键会浪费磁盘和内存的空间。

让我们看看这个例子：

MySQL > CREATE TABLE my_table ( 
        uuid VARCHAR(36) DEFAULT (UUID()) PRIMARY KEY, 
        name VARCHAR(20), beers int unsigned);
...

MySQL > SELECT * FROM my_table;
+--------------------------------------+---------+-------+
| uuid                                 | name    | beers |
+--------------------------------------+---------+-------+
| 17cd1188-1fa0-11ed-ba36-c8cb9e32df8e | Kenny   |     0 |
| 17cd12e2-1fa0-11ed-ba36-c8cb9e32df8e | lefred  |     1 |
| 478368a0-1fa0-11ed-ba36-c8cb9e32df8e | Scott   |     1 |
| 47836a77-1fa0-11ed-ba36-c8cb9e32df8e | Lenka   |     0 |
+--------------------------------------+---------+-------+

现在，让我们插入2个新记录：

MySQL > INSERT INTO my_table (name, beers) VALUES ("Luis",1), ("Miguel",5);

我们查看一下这个表的内容：

MySQL > SELECT * FROM my_table;
+--------------------------------------+---------+-------+
| uuid                                 | name    | beers |
+--------------------------------------+---------+-------+
| 17cd1188-1fa0-11ed-ba36-c8cb9e32df8e | Kenny   |     0 |
| 17cd12e2-1fa0-11ed-ba36-c8cb9e32df8e | lefred  |     1 |
| 36f1ce9a-1fa1-11ed-ba36-c8cb9e32df8e | Luis    |     1 |  <--
| 36f1d158-1fa1-11ed-ba36-c8cb9e32df8e | Miguel  |     5 |  <--
| 478368a0-1fa0-11ed-ba36-c8cb9e32df8e | Scott   |     1 |
| 47836a77-1fa0-11ed-ba36-c8cb9e32df8e | Lenka   |     0 |
+--------------------------------------+---------+-------+

我们可以看到，两个新记录不是插入表格的末尾，而是插入到中间。InnoDB必须移动两个旧记录才能插入它们之前的两个新记录。在这个小表上（所有记录都在同一个页面上），不会造成任何问题，但设想一下，如果这个表是1TB大！

此外，如果我们使用VARCHCAR数据类型保存UUID，每个字段可能需要146字节（一些utf8字符最多可以占用4个字节+标记VARCHAR结束的2个字节）：

MySQL > EXPLAIN SELECT * FROM my_table WHERE 
        uuid='36f1d158-1fa1-11ed-ba36-c8cb9e32df8e'\G
 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_table
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 146        <--
          ref: const
         rows: 1
     filtered: 100
        Extra: NULL

解决方案

MySQL用户可以遵循一些最佳实践来避免这两个问题：

使用数据类型BINARY(16)来存储UUID占用的空间比较小。
使用函数UUID_TO_BIN(…, swap_flag)对UUID进行转换，这里将swap_flag设置为“1”可以让生成的UUID单向正增长，这里的时间戳的低部分和高部分（分别为十六进制数字的第一组和第三组）被交换，参见：
https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid-to-bin

不断地重复执行下面的语句，看到得到结果不一定是正向增长的：

mysql> select hex(uuid_to_bin(uuid()));
+----------------------------------+
| hex(uuid_to_bin(uuid()))         |
+----------------------------------+
| 8EC0DA428BF211EDB473001631F55036 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select hex(uuid_to_bin(uuid()));
+----------------------------------+
| hex(uuid_to_bin(uuid()))         |
+----------------------------------+
| 906E8F628BF211EDB473001631F55036 |
+----------------------------------+
1 row in set (0.00 sec)

如果加上swap_flag设置为“1”，看到得到结果一直是正向增长的：

mysql> select hex(uuid_to_bin(uuid(),1));
+----------------------------------+
| hex(uuid_to_bin(uuid(),1))       |
+----------------------------------+
| 11ED8BF2A04370C4B473001631F55036 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select hex(uuid_to_bin(uuid(),1));
+----------------------------------+
| hex(uuid_to_bin(uuid(),1))       |
+----------------------------------+
| 11ED8BF2A1931D65B473001631F55036 |
+----------------------------------+
1 row in set (0.00 sec)

让我们通过下面的例子来了解如何实现：

MySQL > CREATE TABLE my_table2 ( 
           uuid BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY, 
           name VARCHAR(20), beers int unsigned);

MySQL > SELECT * FROM my_table2;
+------------------------------------+--------+-------+
| uuid                               | name   | beers |
+------------------------------------+--------+-------+
| 0x11ED1F9F633ECB6CBA36C8CB9E32DF8E | Kenny  |     0 |
| 0x11ED1F9F633ECD6FBA36C8CB9E32DF8E | lefred |     1 |
+------------------------------------+--------+-------+

表里面存储的UUID是二进制的，我们使用BIN_TO_UUID对其进行解码：

MySQL > SELECT BIN_TO_UUID(uuid,1), name, beers FROM my_table2;
+--------------------------------------+--------+-------+
| BIN_TO_UUID(uuid,1)                  | name   | beers |
+--------------------------------------+--------+-------+
| 633ecb6c-1f9f-11ed-ba36-c8cb9e32df8e | Kenny  |     0 |
| 633ecd6f-1f9f-11ed-ba36-c8cb9e32df8e | lefred |     1 |
+--------------------------------------+--------+-------+

注意BIN_TO_UUID的swap_flag设置为“1”并不是为了让结果看起来单向增长，而是为了把UUID_TO_BIN函数对时间的交换改成正确的。

现在我们可以验证，当我们添加新条目时，它们会添加到表格的末尾：

MySQL > INSERT INTO my_table2 (name, beers) VALUES ("Scott",1), ("Lenka",5); 

MySQL > SELECT * FROM my_table2;
+------------------------------------+---------+-------+
| uuid                               | name    | beers |
+------------------------------------+---------+-------+
| 0x11ED1F9F633ECB6CBA36C8CB9E32DF8E | Kenny   |     0 |
| 0x11ED1F9F633ECD6FBA36C8CB9E32DF8E | lefred  |     1 |
| 0x11ED1FA537C57361BA36C8CB9E32DF8E | Scott   |     1 |  <--
| 0x11ED1FA537C5752DBA36C8CB9E32DF8E | Lenka   |     5 |  <--
+------------------------------------+---------+-------+

我们可以把swap_flag设置为“1”进行解码UUID：

MySQL > SELECT BIN_TO_UUID(uuid,1), name, beers FROM my_table2;
+--------------------------------------+---------+-------+
| BIN_TO_UUID(uuid,1)                  | name    | beers |
+--------------------------------------+---------+-------+
| 633ecb6c-1f9f-11ed-ba36-c8cb9e32df8e | Kenny   |     0 |
| 633ecd6f-1f9f-11ed-ba36-c8cb9e32df8e | lefred  |     1 |
| 37c57361-1fa5-11ed-ba36-c8cb9e32df8e | Scott   |     1 |  <--
| 37c5752d-1fa5-11ed-ba36-c8cb9e32df8e | Lenka   |     5 |  <--
+--------------------------------------+---------+-------+

当然，现在主键的大小更小，固定为16字节：

MySQL > EXPLAIN SELECT * FROM my_table2 
        WHERE uuid=UUID_TO_BIN("37c5752d-1fa5-11ed-ba36-c8cb9e32df8e",1)\G
 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_table2
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 16        <---
          ref: const
         rows: 1
     filtered: 100
        Extra: NULL

只有这16字节被添加到所有的二级索引中，这样二级索引对长度也变小了。