Migrating existing tables to use INT primary key instead of UUID mysql v8

Question

I'd love to hear everyone's opinion on this issue. I currently use Innodb mysql v8 in RDS. Our db is moderate, with ~130 tables, most are small, with our largest table having ~30 million rows over 6 years. We've currently scaled our RDS instant vertically way larger than our database should need and we're still CPU bound and can't use replicas because the slave gets too far behind. I'm convinced it's because we have UUID version 1 as our primary keys for our tables and have to do lots of string comparisons for our application.

What I'd like to do is prove it by creating a subset of tables that use an auto-incrementing INT as the primary key and foreign keys but I'm struggling to see how this could work. If I just add an auto-incrementing column to the current tables, the queries that our ORM uses won't include it so it wouldn't be that much of a gain. What I am wondering is how I could use a transition table to do essentially swap out the values from UUIDs to INT. For instance, an ORM query now says something like:

select id_primary_key from table_old where id_foreign_key in (UUID1, UUID2, UUID3) with an output of :1d3sed4a-5812-a35c-0204-515f6at42b46

If I create a transition table that has auto_inc INT, and old UUID, what would be the next step so that when the ORM runs that query, it queries against INTs instead of UUIDs?

select id_primary_key from table_new where id_foreign_key in (1, 2, 3) output: 58743

Any thoughts?

UPDATE-- It looks like we're going to be looking into more of a global model in the future, where have multiple dbs and as such will keep the UUIDs. We'll try to go the sharding route as well as trying to store them as VARBINARY(16) and using UUIDtoBIN() w/ the swap flag set to true because we're using UUID v1. Thank you all for your answers and time! I learned a lot from testing it.

Answer 1

Changing from UUID to integer is bound to be a lot of work to implement, and might not help. I recommend first to try to understand more specifically why you have performance problems before you do an invasive change.

For example:

• Are you using ROW based replication and have large tables without a primary key constraint? The replica will do a table-scan for every update on that table, which will slow down replication throughput.

• Is the rate of insert/update/delete simply too high for replication to keep up? Or is the replica provisioned as a less performant server than its source? Or do you need to split the database into "shards" such that each has a fraction of the write traffic?

• Have you identified your slow queries and used EXPLAIN to analyze their optimization? How many are doing table-scans or index-scans instead of using indexes to improve searching or sorting? Do you have the right indexes to help those queries? Look for common mistakes like collation mismatch when comparing strings, or using LIKE or REGEXP, or comparing the results of expressions without creating an expression index.

In any case, you should develop some tests to prove that the solution will solve your problem before implementing it against your production system.

Answer 2

I'm with the other Answers -- it is a lot of work and may not buy you what you need. And it may be possible to get queries and replication sped up even without abandoning UUIDs.

As for conversion, I would do something like the following. [Caveat; this has not been debugged!]

1. Add an AUTO_INCREMENT column on each table that will need it. At the same time, add INDEX(id) so that the id will continue to be updated while you proceed with other steps.
2. If you have FOREIGN KEYS, you may have to at least turn them off.
3. Build a big set of SQL scripts to switch from UUIDs to the new id. Wherever tables are currently JOINed on a UUID, you will need to switch to the id and, at the same time, get the ids set. This is a giant task, and you probably must take an outage for as long as necessary.
4. To avoid changing all the code to reference the new ids, we will need to swap names around. This leads me to step 0:

Step 0: Change PRIMARY KEYs from UUIDs to "natural" PKs wherever possible. In my experience, this gets rid of 2/3 of the UUIDs/ids for PKs. (And in many cases, speeds up queries!) This may lead to less time taken in step 5 for dropping the UUIDs and changing INDEX(id) to PRIMARY KEY(id)

Sorry for giving you a half-baked Answer. Mostly I argue against getting rid of UUIDs. (Yeah, I also frequently argue against ever using UUIDs.)

If you do end up with INTs, you may as well use suitable-sized INTs. This will save a little more space, hence a little more speed.

If the ORM is getting in your way, maybe it is time to convert to writing your own SQL.

Answer 3

I create a transition table that has auto_inc INT, and old UUID, what would be the next step so that when the ORM runs that query, it queries against INTs instead of UUIDs?

Well, you'd want to join to that transition table which will be by the new INT field when you have it, but unfortunately by the UUID still when you don't, such as when you're trying to join a parent and child table together.

I think this approach may end being more work to maintain in the long run and may not help improve performance much if your issue really is due to using a UUID.

Instead, why not just:

1. Add a new INT AUTO_INCREMENT primary key column with the appropriate starting value (and INT foreign key columns when applicable) to every table.
2. Backfill that column using a window function, such as ROW_NUMBER(), based on whatever sorting logic you see fit.
3. Join all the foreign key related child tables to their parents by their current UUID fields to relate them.
4. After the above join, update the new INT foreign key fields with their related parent table's new INT primary key values.
5. Drop the old UUID columns from every table.
6. Rename the new columns to the old column names.

It'll surely be a good amount of work upfront but I think worth it compared to the alternative approach in the long run, and will ensure you're completely dependent on only the INT fields, ensuring utmost performance in that regard.