UUID Best Practices

Overview of UUIDs

When used as a primary key, instead of an AUTO_INCREMENT integer value, a universally unique identifier (UUID) delivers the following benefits:

• UUIDs can be generated on multiple systems without risking conflicts. In some cases, this means that the number of network trips to TiDB can be reduced, leading to improved performance.
• UUIDs are supported by most programming languages and database systems.
• When used as a part of a URL, a UUID is not vulnerable to enumeration attacks. In comparison, with an auto_increment number, it is possible to guess the invoice IDs or user IDs.

Best practices

Store as binary

The textual UUID format looks like this: ab06f63e-8fe7-11ec-a514-5405db7aad56, which is a string of 36 characters. By using UUID_TO_BIN(), the textual format can be converted into a binary format of 16 bytes. This allows you to store the text in a BINARY(16) column. When retrieving the UUID, you can use the BIN_TO_UUID() function to get back to the textual format.

UUID format binary order and a clustered PK

The UUID_TO_BIN() function can be used with one argument, the UUID or with two arguments where the second argument is a swap_flag. It is recommended to not set the swap_flag with TiDB to avoid hotspots.

You can also explicitly set the CLUSTERED option for UUID based primary keys to avoid hotspots.

To demonstrate the effect of the swap_flag, here are two tables with an identical structure. The difference is that the data inserted into uuid_demo_1 uses UUID_TO_BIN(?, 0) and uuid_demo_2 uses UUID_TO_BIN(?, 1).

In the screenshot of the Key Visualizer below, you can see that writes are concentrated in a single region of the uuid_demo_2 table that has the order of the fields swapped in the binary format.

CREATE TABLE `uuid_demo_1` (
  `uuid` varbinary(16) NOT NULL,
  `c1` varchar(255) NOT NULL,
  PRIMARY KEY (`uuid`) CLUSTERED
)

CREATE TABLE `uuid_demo_2` (
  `uuid` varbinary(16) NOT NULL,
  `c1` varchar(255) NOT NULL,
  PRIMARY KEY (`uuid`) CLUSTERED
)

MySQL compatibility

UUIDs can be used in MySQL as well. The BIN_TO_UUID() and UUID_TO_BIN() functions were introduced in MySQL 8.0. The UUID() function is available in earlier MySQL versions as well.