MySQL 和 UUIDs

发表于： 2022-09-16 23:02:47
分类：数据库文章分享
浏览：浏览(91)

越来越多的人使用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 |
+-----------+--------------------------------------+--------+-------+

结论

总之，如果您想在MySQL中使用UUID，建议使用UUIDv1，即MySQL生成的UUID v1，并使用交换标志将它们存储为二进制。

如果出于某种原因需要UUID v4，建议让MySQL和InnoDB通过启用GIPK模式来处理主键。

原文标题：MySQL & UUIDs
原文作者：LEFRED
原文链接：https://lefred.be/content/mysql-uuids/

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论