If you have a UUID field, should you still use an INT-based Primary Key, and set the UUID as a Unique Key, or just make the UUID Primary Key?

user_2664719 0
Viewed 1094

This is a best practice question for a potentially large database.

I have a Table exposed to a public API which will store a UUID associated with each row. This UUID is the only way the public will be able to search the data within the table.

A UUID is used externally, as internally there is no need for an incremental ID, and it provides an additional layer of security for the data. This row is not referenced anywhere else in the database.

Initially, I created the table without an auto-increment (int) ID column, and instead, made the BINARY(16) UUID column the Primary Key.

However, I have been doing some more reading, and saw an opinion that in a large dataset, the storage and IOps of a non-sequential Primary Key increase exponentially over just using a sequential Primary Key, as the insert method needs more operations to find the correct row on the B+ Table.

I'm not savvy in the internal workings of MySQL/InnoDB, so my question is:

Would it be "better", to have an auto-increment INT(4)/BIGINT(8) Primary Key -- AS WELL AS -- a BINARY(16) Unique Key == or == Just use the non-incremental BINARY(16) as the Primary Key, when facing potentially large dataset?

Thanks, JP

devops database-design innodb mysql
1 Answers

Will your data be bigger than RAM? If so, the random nature of UUIDs becomes a performance issue.

Are most accesses via "recently issued" ids? If so, something that is chronological has the benefit of clumping the interesting ids near each other, thereby improving caching.

Do you have separate, independent, clients generating ids? If so, you need either UUIDs or a common server issuing ids.

Do you have lots of secondary keys? Note that each secondary index has a copy of the PRIMARY KEY column(s), hence are bulked up by a big PK. (In some situations, this is an argument for an artificial auto_inc PK.)

http://mysql.rjweb.org/doc.php/uuid

user_17668310
Related