Why UUID Primary Keys Slow Down MySQL and How to Optimize Them

In MySQL, using a UUID as the primary key may cause performance problems, especially during insert and update operations, because of its size and randomness.

1. Problems with UUID as Primary Key

(1) UUID Characteristics

UUID is a 128‑bit value usually represented as a 36‑character string, e.g., 550e8400-e29b-41d4-a716-446655440000.

It is globally unique, which is useful for distributed systems.

(2) Disadvantages of Using UUID as Primary Key

1. Low Index Efficiency

Index size : UUID is stored as a string (36 bytes) while an integer key like BIGINT occupies only 8 bytes; larger indexes consume more storage and slow queries.

Index splits : UUIDs are unordered, so inserting new rows can cause frequent B‑tree splits and rebalancing.

2. Poor Insert Performance

Randomness : New rows may be placed anywhere in the index tree, forcing constant adjustments.

Page splits : InnoDB’s B+‑tree suffers page splits on random inserts, increasing disk I/O.

3. Degraded Query Performance

Comparison cost : Comparing strings is slower than comparing integers, especially on large tables.

Large scan range : Bigger indexes lead to larger scan ranges, reducing query speed.

2. Why Updating Data Triggers Index Refresh

(1) Role of Index

Indexes (e.g., B+ trees) accelerate data retrieval.

When data changes, the index must be updated to keep consistency.

(2) Impact of Data Modification

Updating primary key : MySQL deletes the old index entry and inserts a new one, causing tree adjustments and extra I/O.

Updating indexed non‑primary columns : The corresponding index records must be updated, also leading to tree adjustments.

(3) Extra Overhead of UUID Primary Keys

Because UUIDs are unordered, changing a primary‑key value may place the new entry in a different location, incurring more index restructuring than ordered keys.

3. Why Character Primary Keys Reduce Efficiency

(1) Large Storage Space

Character keys (e.g., UUID) occupy more space than integer keys, enlarging the index and increasing I/O.

(2) Slower Comparisons

String comparison is slower than integer comparison; for example, WHERE id = '550e8400-e29b-41d4-a716-446655440000' is slower than WHERE id = 12345.

(3) Index Splits

Unordered character keys cause frequent index‑tree splits and rebalancing during inserts.

4. How to Optimize UUID Primary Key Performance

(1) Use Ordered UUIDs

Generate time‑based UUIDs such as UUIDv7 to reduce index and page splits.

(2) Store UUID as Binary

Save UUID in BINARY(16) instead of CHAR(36) to cut storage size.

CREATE TABLE users (
  id BINARY(16) PRIMARY KEY,
  name VARCHAR(255)
);

(3) Combine Auto‑Increment Key with UUID

Use an auto‑increment BIGINT as the physical primary key and keep a UUID column for logical identification.

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  uuid CHAR(36) UNIQUE,
  name VARCHAR(255)
);

(4) Partition Large Tables

Partitioning reduces the size of each index tree, improving query and maintenance performance.

Summary

UUID primary keys suffer from large index size, low insert and query efficiency, and costly index refreshes on updates.

Character keys increase storage, slow comparisons, and cause frequent index splits.

Optimization strategies include using ordered UUIDs, binary storage, hybrid auto‑increment + UUID keys, and table partitioning.