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

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

小心两个坑

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

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

让我们看看这个例子：

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用户可以遵循一些最佳实践来避免这两个问题：

  1. 使用数据类型BINARY(16)来存储UUID占用的空间比较小。
  2. 使用函数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字节被添加到所有的二级索引中，这样二级索引对长度也变小了。

UUID 的版本

目前有两个流行UUID版本，分别是v1和v4。UUID v1的说明参见：https://www.rfc-editor.org/rfc/rfc4122.html。

  • UUID v1：是一个通用的唯一标识符，使用时间戳和生成它的计算机的MAC地址生成。
  • UUID v4：是一个使用随机数生成的通用唯一标识符。

使用UUID v4，无法生成任何顺序输出，因此不推荐使用UUID v4做为InnoDB表的主键。

总结

总之，如果想在MySQL中使用UUID，请遵循以下建议：

  • 使用UUID v1，而不是UUID v4。
  • 使用BINARY(16)来存储UUID存储UUID。
  • 使用函数UUID_TO_BIN(…, swap_flag)对UUID进行转换，这里将swap_flag设置为“1”。

托业890的Oracle ACE为您翻译国外大佬的雄文

# mysql # 数据库 # java
Logo
开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

avatar 开放原子开发者工作坊

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

avatar 开放原子开发者工作坊

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

avatar 开放原子开发者工作坊