Using GUID as PK in a large MySQL partitioned table

Question

We have a huge InnoDB table with hundreds of millions of rows and only 3 columns: GUID, enum, smallint. All searches are performed using the GUID.

We plan to make a PK GUID and split it into KEY.

We have heard that using a GUID as a PK is bad due to its random distribution and the fact that PK creates a clustered index. Thus, storing strings in random GUIDs increases fragmentation and page splitting.

An alternative to using a GUID as PK is to create a surrogate auto-increment key and use it as a PC. However, if we want to partition the table by GUID, this GUID must be part of PK. In addition, since all requests are executed using the GUID, we need an additional GUID. This index essentially displays GUID-> PK, and if we use the GUID as PK, does the table itself display GUID-> enum + small int?

So my question is, are we getting anything by adding auto-inc PK and having an extra GUID?

Thanks, Filopator.

Answer 1

The problem with using GUIDs as PCs in InnoDB is not only that the GUID distribution is random. It, which writes to InnoDB, is stored in primary key order. This means that in the table design you are talking about, InnoDB will constantly move data trying to sort your GUIDs. You should use a translation table that maps the GUID to int or bigint and uses it like PK.