Why Using UUID as a Primary Key in MySQL Causes Performance Issues and How to Optimize It

In MySQL, using UUID as a primary key can cause performance problems in large tables, especially low efficiency for insert and update operations.

1. Problems of UUID as Primary Key

(1) UUID Characteristics

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

UUID is globally unique, suitable for distributed systems.

(2) Drawbacks of UUID as Primary Key

1. Index Efficiency Low

Index size : UUID is a string type occupying 36 bytes, while an integer primary key (e.g., BIGINT ) occupies only 8 bytes. Larger indexes reduce storage and query efficiency.

Index split : UUID is unordered; inserting new rows may cause frequent index‑tree splits and rebalancing, hurting performance.

2. Insert Performance Poor

Randomness : UUID is unordered, so each insert may be placed at any position in the index tree, leading to frequent adjustments.

Page split : InnoDB uses a B+ tree for indexes; random inserts cause page splits, increasing disk I/O.

3. Query Performance Poor

Comparison efficiency low : String comparison is slower than integer comparison, especially in large tables.

Index scan range large : UUID indexes occupy more space, expanding the scan range and reducing query efficiency.

2. Why Modifying Data Triggers Index Refresh

(1) Role of Index

Indexes are data structures (such as B+ trees) created to accelerate queries.

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

(2) Impact of Data Modification on Index

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

Updating non‑primary‑key columns : If the column is indexed (e.g., unique or normal index), MySQL updates the corresponding index records, also causing index‑tree adjustments.

(3) Extra Overhead of UUID Primary Key

Because UUID is unordered, changing its value may insert the new key at a different position in the index tree, leading to frequent adjustments.

Compared with ordered primary keys (e.g., auto‑increment IDs), UUID modifications are more costly.

3. Why Character Primary Keys Reduce Efficiency

(1) Large Storage Space

Character primary keys (like UUID) occupy more storage than integer keys; larger indexes increase disk I/O.

(2) Comparison Efficiency Low

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

(3) Index Split

Character primary keys are usually unordered; inserting new rows can cause frequent index‑tree splits and rebalancing, affecting performance.

4. How to Optimize UUID Primary Key Performance

(1) Use Ordered UUID

Adopt ordered UUIDs (e.g., UUIDv7 ) to reduce index and page splits.

Ordered UUIDs are often time‑based, guaranteeing insertion order.

(2) Store UUID as Binary

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

Example:

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

(3) Use Auto‑Increment Primary Key + UUID

Use an auto‑increment column as the physical primary key and a UUID column as a logical unique key.

Example:

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

(4) Partition Large Tables

Partition a large table to reduce the size of each index tree, improving query performance.

~Summary

Disadvantages of UUID as Primary Key : Low index efficiency, poor insert and query performance, and frequent index refreshes during updates.

Reasons character primary keys are inefficient : Large storage space, slower comparisons, and frequent index splits.

Optimization suggestions : Use ordered UUIDs or binary storage, combine auto‑increment keys with UUIDs, and partition large tables.