MySQL 和 UUID

hudson2022
142 阅读5分钟

MySQL 和 UUID

lefred 【hudson译】

2022年9月9日

越来越多的人使用UUID来识别数据库中的记录。 如您所知，对于MySQL的存储引擎（InnoDB），主键对于用于性能、内存和磁盘空间非常重要！。

参考下面链接

问题

在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大！ 此外，如果我们为uuid保留VARCHCAR数据类型，主键每行可以占用146个字节（一些utf8字符最多可以占用4个字节+ 2个字节VARCHAR结尾标记）：

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：BINARY（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()对其进行解码，不要忘记swap标志：

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版本1

MySQL依照 RFC4122 所述生成UUID v1。

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

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

UUID版本4

一些开发人员不断询问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库 编写了一个组件: github.com/lefred/mysq… 但这个新函数也不能用作默认值表达式。

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，并使用swap标志将其存储为二进制。

avatar
文章
阅读
粉丝