Search code examples
mysqlprimary-keyuuidauto-incrementdistributed-system

Combination of UUID and integer value as primary key in one database in MySQL

Could you give me any advice, if will be good to mix UUID as primary key and auto increments integer value for different tables in that same database? We want to rebuild database which will be bigger in time and will works in distributed environment. There will be one main database and many more smaller databases on other machines (subsets of main database). The smallest databases will be in sync with main database.

I know that in such distributed systems UUID will be the best choice for primary key. But for example in database there will be tables like page_status or page_type which will not change so often and will not have to many rows. So for performance and readability will be simpler to have only integer value as primary key in such tables. Please let me know what you think and how your experience in this topic looks like. Thanks in advance!

Solution

  • A UUID is the 'right' way to create a unique id when you require these:

    • The id needs to be constructed independently by different clients.

    UUIDs have these problems:

    • Bulky: 16 bytes per use. Note that "use" includes all secondary keys, and joining tables. It adds up.
    • Randomness: When a table is bigger than RAM, references are slowed to disk speed.

    The alternatives are

    • Have a single source (eg, a database) that delivers the 'next' id when asked. This is limited in how fast the ids can be generated.

    • Devise a mechanism for having clients independently generate unique ids, but not based on UUID -- see the problem above. Example: A 64-bit integer with time in top, then uniqueness number (within the client), then client number.

    • You could map UUIDs to smaller AIs, which are then used various places. But this adds complexity.

    • Juggling the bits of a Type-1 UUID makes the roughly chronological; this avoids the randomness. Discussed in http://mysql.rjweb.org/doc.php/uuid . The functions for that are built into MySQL 8.0.