I have a few questions relating to UUID's, but before I go on, let me explain our schema a bit, first, we're on MySQL 5.5 (Percona's version to be exact)...
In each applicable table, we use a two column approach, the first column called
ai_col which is an auto-inc int and is set as the primary key (as per the InnoDB best practices), followed (in most cases) by a CHAR(32) column representing the UUID's, and while the UUID col is not the primary key, we do utilize a
UNIQUE index. The combination of these gives us the performance of using an auto-int primary key without causing high IO for the table when new records are inserted, and the benefits of a UUID strategy for multi-source ID generation. I wasn't as as concerned with the additional storage overhead at the time, but I also didn't intend on wasting it either.
Like the poster in this question (Should I invest the time to change the column type from CHAR(36) to UUID?), I was unaware of the option to use a
BIN(16) rather than a
CHAR(32) to store our UUID's. Unfortunately we're a lot further along than they were and out DB is roughly 130GB right now, growing at about 10GB per month at the moment, but that too is accelerating.
Due to the wide use of foreign keys it's not that easy to switch the column types on the fly so I'm trying to come up with a strategy for gradually updating the schema.
Some questions I've got are:
1. Is there a usability issue with
BIN(16) that I'm not considering, i.e. will I need to update queries to pre-convert UUID's being requested to a
BIN format, or does this happen on the fly at the DB?
2. Is it possible to update ALL tables at the same time utilizing a specific key column? i.e. If I know that tables A, B, and C all have a User_ID col, can they all be converted to BIN(16) without needing to remove the foreign key relationships ahead of time? Will one of Percona's tools possibly help in this respect?
3. Are there aspects to this change that I'm not considering that will have a negative impact on us down the road?
Would appreciate any real-world experiences, but theory is good too!