mysql - MySql UUID Byte Order

Question

I have a MySql table in which the primary key is an auto-increment column and then I also have a binary(16) column with a unique index for storing UUIDs.

The MySqlConnector has a connection option for GuidFormat in which, I'm confused between TimeSwapBinary16 and LittleEndianBinary16 in terms of which one of these would be a better choice and why.

Does the arrangement of UUID bytes in a certain order provide benefit when it comes to efficiency and performance such as during insertions, indexing, selecting with the binary(16) column in a WHERE clause etc?

Can someone please explain these?

标签： mysqlmysql-connector

Answer 1

有一个一般原则，即按升序将新值插入索引会导致索引效率适中。

虽然 MySQL 的默认 UUID 格式包括从时间戳生成的字节，但它们不在 UUID 中的字节位置，这使其符合“按升序插入”目标。在 MySQL 8.0 中，他们引入了一个函数，该函数UUID_TO_BIN()具有重新排列字节的选项，以便在生成 UUID 值时，它们按时间戳排序。

连接器选项采用该TimeSwapBinary16选项，因此它对索引插入性能更好。

如果您有兴趣，请在这篇博文中以详细的基准显示无序和有序 UUID 之间的性能差异：https ://www.percona.com/blog/2014/12/19/store-uuid-optimized-方法/