MYSQL primary key strategy for microservices architecture

Question

We are currently using auto-generated primary keys and we would like to switch to an approach which is more suitable for microservices applications: either we are going to use business defined primary key (a tax code for persons) or global unique identifiers.

In the past we had performance issues when using business-defined primary keys instead of autoincrement, is it still the case? Does MySQL support strong GUID so that we could have unique uuid even across databases? Eventually, is there a better approach we are not considering?

Answer 1

Corrected:

with MySQL You can store GUID/UUID data as char/varchar in case of UUID() and UNSIGNED INTEGER in case of UUID_SHORT()

My personal vision - UUID_SHORT not working case, it is unsigned INT same as Auto-Increment and You can read restrictions in function description.

UUID() will return result as utf8 string - "eabd691c-b552-11e6-942e-ee8ae5dda6a8"

Business logic for primary key - work well, You always mange this process and do not need spend time for call function.

add after question in comment:

performance not depend from business logic or auto increment, it depend mostly from 2 parameters:

• size of column
• function duration time

INT (BIGINT) always will be smaller than CHAR(36) - as result less memory usage, more data in memory for index recreate operations (such as massive insert)

Increment will be faster any other form of random generator in 99% of cases

So in case of TAXNUMBER as primary key:

• advantage - no any function for generate this type of key
• disadvantage - most of them will take more memory than INT (BIGINT)

Add some visual examples: make a proper test it very hard, but this is small example: was created 3 tables, with only 1 column ID - primary key

• UNSIGNED INT AUTOINCREMENT

• BINARY(16)

• VARCHAR(36)

was created stored procedure which insert 11M of records in each table, time results, very close:

• 11m41s - INT
• 12m40s - UUID() -> binary(16)
• 12m40s - UUID() -> varchar(36)

with time difference will need adjust server settings but if we check size on disk (tables include only 1 column, PK)

-rw-r-----  1 _mysql  _mysql   1.1G 29 Nov 01:00 t__binary.ibd
-rw-r-----  1 _mysql  _mysql   268M 29 Nov 01:01 t__uint.ibd
-rw-r-----  1 _mysql  _mysql   1.6G 29 Nov 01:00 t__varchar.ibd

we can see the difference, and as bigger will be data, as much more will be difference

Answer 2

(This answer does not fully answer your question; it is intended to enlighten you about the issues of GUID/UUID performance issues, plus some mitigation.)

In the past we had performance issues when using business-defined primary keys instead of autoincrement, is it still the case?

Yes. But it can be mitigated some. When you have a huge database (more data than can be cached in RAM), UUIDs start to perform poorly, eventually (as the data grows) degenerating to one disk I/O per one reference to a UUID-based record. For huge databases, this is untenable. The mitigation is to use only Type-1 UUIDs (which is what MySQL's UUID() is), and rearrange the bits so that the date&time is at the beginning. This is beneficial if your data references are biased to "recent" rows, thereby making the caching more efficient. Changing from 36 characters into 16 bytes also helps with performance.

I discuss this more in my blog . In MySQL 8.0, Oracle picked up the ball and implemented UUID_TO_BIN() and related functions to both rearrange the bits and pack into VARBINARY(16).

Does MySQL support strong GUID so that we could have unique uuid even across databases?

You can obviously create your own GUID/UUID and use it as a globally-unique key for whatever you want. MySQL has its builtin UUID() function, which is "Type-1". I don't know whether you consider that to be "strong"; it seems fine.

Eventually, is there a better approach we are not considering?

That question is too broad.