Storing UUID Values in MySQL

Definitely an interesting method Karthik.

But, I think there’s a more efficient, performant and simple way of accomplishing the solution you’re trying to achieve. InnoDB tables, as you know use clustered indexes in the background, meaning the PRIMARY index is part of the actual row (even when no Primary Key is defined). It also supports a special column called ‘ai_col’ which when created as the first column in any table, set to an auto-incrementing unsigned integer, and flagged as the primary key will enable certain built in performance tweaks relating to the clustered index. All secondary indexes use the clustered index key (which is the important part)

The benefit of the clustered index is that it saves disk io because the complete page/row is returned rather than just the key which requires a second read operation on the specified page to retrieve the actual data.

In your solution the primary key would be a 16byte binary UUID, and all secondary indexes would be forced to use this meaning much larger index footprints, and I don’t know about you but most of our tables have multiple indexes. You’ve eliminated the IO overhead of inserting UUID’s and re-indexing, but you’ve increased the size of every index in the system as a side effect meaning both more physical and memory footprint.

By using the ‘ai_col’ first column and using it as the primary key, it solves both of those problems. You would then just create a second column with the true UUID’s, add a UNIQUE index on it. Your rows will have a slightly larger size, but your indexes will be MUCH smaller, resulting in less IO, and a smaller footprint overall.

You gain the benefits of an ordered insert key and thus low IO, you also gain the benefit of distributed UUID generation across all your app servers without any worry of collision in a cluster / multi-master environment, and you get to stick with standard UUID formatting which keeps things simple.

I completely agree on the hyphens though, useless 🙂

Kevin.

UUIDs come from many sources. Many of them do not use “UUID version 1”, like MySQL.

Type 1 is recognizable by the “1” at the beginning of the third clump.

Do not jump to the conclusion that you can rearrange any UUID to benefit from temporal clustering.

Here is my blog on the topic:
http://mysql.rjweb.org/doc.php/uuid

@Kevin… Once the data, or index as you proposed, becomes too big to be cached, UUID lookups become I/O bound. Rearranging Type-1 UUIDs then becomes beneficial _if_ your accesses tend to be clustered in some time range, such as “recent events”.

If you are worried about the index size of the UUID field you can always switch to using a BINARY field and use UNHEX() and HEX() to convert your UUID on its way into and out of the table. So a 16 byte UUID would only take 8 bytes in BINARY:

mysql> select HEX(UNHEX(‘1d8eebc58e0a7d7’));
+——————————-+
| HEX(UNHEX(‘1d8eebc58e0a7d7’)) |
+——————————-+
| 01D8EEBC58E0A7D7 |
+——————————-+

mysql> select LENGTH(UNHEX(‘1d8eebc58e0a7d7’));
+———————————-+
| LENGTH(UNHEX(‘1d8eebc58e0a7d7’)) |
+———————————-+
| 8 |
+———————————-+

Tokudb, with its “fractal” indexing strategy builds the indexes in stages. In contrast, InnoDB inserts index entries “immediately” — actually that indexing is buffered by most of the size of the buffer_pool. To elaborate…

When adding a record to an InnoDB table, here are (roughly) the steps performed to write the data (and PK) and secondary indexes to disk. (I leave out logging, provision for rollback, etc.) First the PRIMARY KEY and data:
* Check for UNIQUEness constraints
* Fetch the BTree block (normally 16KB) that should contain the row (based on the PRIMARY KEY).
* Insert the row (overflow typically occurs 1% of the time; this leads to a block split).
* Leave the page “dirty” in the buffer_pool, hoping that more rows are added before it is bumped out of cache (buffer_pool).. Note that for AUTO_INCREMENT and TIMESTAMP-based PKs, the “last” block in the data will be updated repeatedly before splitting; hence, this delayed write adds greatly to the efficiency. OTOH, a UUID will be very random; when the table is big enough, the block will almost always be flushed before a second insert occurs in that block. <– This is the inefficiency in UUIDs.
Now for any secondary keys:
* All the steps are the same, since an index is essentially a "table" except that the "data" is a copy of the PRIMARY KEY.
* UNIQUEness must be checked immediately — cannot delay the read.
* There are (I think) some other "delays" that avoid some I/O.

Tokudb, on the other hand, does something like
* Write data/index partially sorted records to disk before finding out exactly where it belongs.
* In the background, combine these partially digested blocks. Repeat as needed.
* Eventually move the info into the real table/indexes.

If you are familiar with how sort-merge works, consider the parallels to Tokudb. Each "sort" does some work of ordering things; each "merge" is quite efficient.

To summarize:
* In the extreme (data/index much larger than buffer_pool), InnoDB must read-modify-write one 16KB disk block for each UUID entry.
* Tokudb makes each I/O "count" by merging several UUIDs for each disk block. (Yeah, Toku rereads blocks, but it comes out ahead in the long run.)
* Tokudb excels when the table is really big, which implies high ingestion rate.

As for using a UDF instead of a Stored Function — CPU time is not the problem; it is I/O. Hence, the UDF won't help much.

You can pre-generate these uuids in javascript using this library which was open sourced by my employer, Fisdap: https://github.com/fisdap/innodb-optimized-uuid

@François Schiettecatte — HEX(UNHEX()) is a noop other than leading zeros and case folding.

You can’t shrink a UUID to 8 bytes without losing information, and potentially causing “duplicate key”.

Yes Joe, we can have uuid() inside the function.

James, can you share a repo showing this?

very interesting article.

Do you know any performance difference in storing optimized UUID (11e5e1833a23ad4188733abca40787bc) hex to a decimal value (23790485847991417512368510810209093564)?

e.g.
3a23ad41-e183-11e5-8873-3abca40787bc => 11e5e1833a23ad4188733abca40787bc => 23790485847991417512368510810209093564

Great UUID perf improvement!
I was wondering if there is a shorter version of this optimized UUID.
MySQL has UUID_SHORT which is ordered but it is sequecial
http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid-short

We could remove the Ethernet card number part UUID and use MySQL server number as UUID_SHORT does.
Would the restult save in DB still be binary(16) ?

How COMB GUIDs compare to that?

Seems You haven’t drawn any conclusions? Why do You compare table with 4 indexes to tables with 3 indexes?

Thanks!! Added a simple JS lib for helping with this: https://github.com/odo-network/binary-uuid

Thanks for this, I have a question: are you storing 1d8eebc58e0a7d7 as bytes (meaning you are cutting off the other half of the UUID) orrdo you rearang the hexdecimal value to store them as BINARY(16).