Performance Issues of Using UUID as Primary Key in MySQL and Optimization Strategies

In MySQL, using UUID as a primary key can cause performance problems, especially on large tables, due to larger index size, random insertion order, and expensive index maintenance.

1. UUID as Primary Key Issues

(1) Characteristics of UUID

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

It is globally unique, suitable for distributed systems.

(2) Drawbacks of UUID Primary Keys

1. Low Index Efficiency

Index size : UUID stored as a string occupies 36 bytes, while an integer primary key (e.g., BIGINT ) uses only 8 bytes, making the index larger and slower.

Index splits : UUIDs are unordered, causing frequent B‑tree splits and rebalancing during inserts.

2. Poor Insert Performance

Randomness : New rows are inserted at random positions in the index, leading to constant adjustments.

Page splits : InnoDB’s B‑tree experiences page splits, increasing disk I/O.

3. Degraded Query Performance

Comparison cost : String comparison is slower than integer comparison, especially on large tables.

Index scan range : Larger UUID indexes increase the scan range, reducing query speed.

2. Why Updating Data Triggers Index Refresh

(1) Role of Indexes

Indexes (e.g., B+ trees) accelerate queries.

When data changes, the index must be updated to stay consistent.

(2) Impact of Data Modification

Primary key updates : Changing the primary key forces MySQL to delete the old index entry and insert a new one, causing tree adjustments and extra I/O.

Non‑primary indexed columns : Updating indexed columns also requires index record updates, leading to similar overhead.

(3) Extra Cost of UUID Primary Keys

Because UUIDs are unordered, updating them often moves the entry to a different location in the index, incurring higher adjustment costs than ordered keys.

3. Reasons Character‑Based Primary Keys Are Inefficient

(1) Large Storage Space

Character keys like UUID consume more bytes than integer keys, enlarging indexes and increasing I/O.

(2) Slower Comparisons

String comparisons are slower; for example, WHERE id = '550e8400-e29b-41d4-a716-446655440000' is less efficient than WHERE id = 12345 .

(3) Index Splits

Unordered character keys cause frequent index tree splits during inserts.

4. Optimizing UUID Primary Key Performance

(1) Use Ordered UUIDs

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

(2) Store UUID as Binary

Store UUID in BINARY(16) instead of CHAR(36) to save space.

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

(3) Combine Auto‑Increment Primary Key with UUID

Use an auto‑increment BIGINT as the physical primary key and a unique 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 performance on massive datasets.

Summary

UUID primary keys suffer from low index efficiency, poor insert/query performance, and costly index refreshes on updates.

Root causes are large storage size, unordered nature, and expensive string comparisons.

Optimization recommendations: use ordered UUIDs, store them as binary, combine with auto‑increment keys, and apply table partitioning.