MySQL 和 UUIDs

原创 eternity 2022-09-16

1320

越来越多的人使用UUID来识别数据库中的记录。

正如您已经知道的，对于MySQL的存储引擎（InnoDB），主键非常重要！（用于性能、内存和磁盘空间）。

请参阅以下链接：

https://lefred.be/content/mysql-invisible-column-part-ii/
https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html (GIPK mode in MySQL 8.0.30 !)

问题

InnoDB中UUID作为主键存在两个主要问题：

1.通常，它们是随机的，会导致聚集索引重新平衡

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 |
+--------------------------------------+---------+-------+
复制

现在，让我们插入两条新记录：

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.使用较小的数据类型存储UUID：二进制（16）

2.按顺序存储UUID：使用UUID_TO_BIN（…，swap_flag）

交换时间低和时间高部分（分别为第一组和第三组十六进制数字）。

让我们通过下面的示例来了解这一点：

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 |
+--------------------------------------+--------+-------+
复制

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

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 |  <--
+------------------------------------+---------+-------+
复制

我们当然可以解码UUID，并看到如果没有交换标志，InnoDB将不得不重新平衡聚集索引：

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字节。只有这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
复制

UUID v1

MySQL生成如RFC4122中所述的UUID v1。

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

使用UUID v4m，不可能生成任何顺序输出，这就是为什么这些随机UUID不应该用作InnoDB的主键。

UUID v4

一些开发人员不断询问UUIDv4以及如何为MySQL生成它们。浏览互联网，你可以找到几个商店程序试图实现这一点。

在StackOverflow上找到的这个可能是我最喜欢的：

CREATE FUNCTION uuid_v4s()
    RETURNS CHAR(36)
BEGIN
    -- 1th and 2nd block are made of 6 random bytes
    SET @h1 = HEX(RANDOM_BYTES(4));
    SET @h2 = HEX(RANDOM_BYTES(2));

    -- 3th block will start with a 4 indicating the version, remaining is random
    SET @h3 = SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3);

    -- 4th block first nibble can only be 8, 9 A or B, remaining is random
    SET @h4 = CONCAT(HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64)+8),
                SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3));

    -- 5th block is made of 6 random bytes
    SET @h5 = HEX(RANDOM_BYTES(6));

    -- Build the complete UUID
    RETURN LOWER(CONCAT(
        @h1, '-', @h2, '-4', @h3, '-', @h4, '-', @h5
    ));
END
复制

很遗憾，此函数不能用作列的默认表达式。

我还使用boost的uuid库编写了一个组件：https://github.com/lefred/mysql-component-uuid_v4

但是这个新函数也不能用作默认值表达式。

MySQL error code MY-003770 (ER_DEFAULT_VAL_GENERATED_NAMED_FUNCTION_IS_NOT_ALLOWED): Default value expression of column ‘%s’ contains a disallowed function: %s.

这意味着，每个新记录都需要提供uuid列……无论如何，这并不太复杂。

让我们看一个例子：

MySQL > install component "file://component_uuid_v4";

MySQL > select uuid_v4() ;
+--------------------------------------+
| uuid_v4()                            |
+--------------------------------------+
| 9944272b-e3f9-4778-9c54-818f0baa87da |
+--------------------------------------+
1 row in set (0.0002 sec)
复制

现在我们将创建一个新表，但按照建议，我们不会使用uuid作为主键！我们将使用MySQL 8.0.30的一个新特性：GIPK模式！

GIPK代表生成的不可见主键，请查看手册了解更多信息。

MySQL > SET sql_generate_invisible_primary_key=1;
 
MySQL > CREATE TABLE my_table3 (   
            uuid BINARY(16) NOT NULL UNIQUE,
            name VARCHAR(20), beers INT UNSIGNED);

MySQL > SHOW CREATE TABLE my_table3\G
*************************** 1. row ***************************
       Table: my_table3
Create Table: CREATE TABLE `my_table3` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `uuid` binary(16) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `beers` int unsigned DEFAULT NULL,
  PRIMARY KEY (`my_row_id`),
  UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
复制

现在，让我们插入一些记录，看看它们是否按顺序插入，以及UUID的值是否完全随机：

MySQL > INSERT INTO my_table3 (uuid, name, beers) 
        VALUES (UUID_TO_BIN(uuid_v4()),'Kenny', 3),
               (UUID_TO_BIN(uuid_v4()), 'lefred', 1);

MySQL > SELECT * FROM my_table3;
+------------------------------------+--------+-------+
| uuid                               | name   | beers |
+------------------------------------+--------+-------+
| 0x5A28E5482CDF4B3D89A298ECA3F3703B | Kenny  |     3 |
| 0x94662BF4DC2F469489D868820B7B31E5 | lefred |     1 |
+------------------------------------+--------+-------+

MySQL > SELECT BIN_TO_UUID(uuid), name, beers FROM my_table3;
+--------------------------------------+--------+-------+
| bin_to_uuid(uuid)                    | name   | beers |
+--------------------------------------+--------+-------+
| 5a28e548-2cdf-4b3d-89a2-98eca3f3703b | Kenny  |     3 |
| 94662bf4-dc2f-4694-89d8-68820b7b31e5 | lefred |     1 |
+--------------------------------------+--------+-------+
复制

到目前为止，一切都很好。让我们再添加一些记录：

MySQL > INSERT INTO my_table3 (uuid, name, beers) 
        VALUES (UUID_TO_BIN(uuid_v4()),'Scott', 10), 
               (UUID_TO_BIN(uuid_v4()), 'Lenka', 0);

MySQL > SELECT BIN_TO_UUID(uuid), name, beers FROM my_table3;
+--------------------------------------+--------+-------+
| bin_to_uuid(uuid)                    | name   | beers |
+--------------------------------------+--------+-------+
| 5a28e548-2cdf-4b3d-89a2-98eca3f3703b | Kenny  |     3 |
| 94662bf4-dc2f-4694-89d8-68820b7b31e5 | lefred |     1 |
| 615fae32-d6c8-439c-9520-5d3c8bfa934b | Scott  |    10 |
| 80a01a29-489b-419d-bca1-05a756ad9d9d | Lenka  |     0 |
+--------------------------------------+--------+-------+
复制

我们可以看到，实际上，UUID是完全随机的，并顺序添加到表中。最佳顺序插入的原因是不可见主键是自动增量。

也可以按需显示：

MySQL > SELECT my_row_id, BIN_TO_UUID(uuid), name, beers FROM my_table3;
+-----------+--------------------------------------+--------+-------+
| my_row_id | bin_to_uuid(uuid)                    | name   | beers |
+-----------+--------------------------------------+--------+-------+
|         1 | 5a28e548-2cdf-4b3d-89a2-98eca3f3703b | Kenny  |     3 |
|         2 | 94662bf4-dc2f-4694-89d8-68820b7b31e5 | lefred |     1 |
|         3 | 615fae32-d6c8-439c-9520-5d3c8bfa934b | Scott  |    10 |
|         4 | 80a01a29-489b-419d-bca1-05a756ad9d9d | Lenka  |     0 |
+-----------+--------------------------------------+--------+-------+
复制