✏️ Explanatory Question

UUID vs auto-increment INT as primary key — the InnoDB performance impact and how ordered UUIDs fix it

👁 6 Views
📘 Detailed Answer
🟢 Easy
← Previous Soft delete vs hard delete — correct implementation, UNIQUE constraint pitfalls, and the forgotten-filter bug
💡

Answer with Explanation

113

UUID vs auto-increment INT as primary key — the InnoDB performance trap

Level: Hard — A deep InnoDB internals question; random UUIDs as a clustered PK can silently cripple write performance.

Scenario: Your team switches primary keys from AUTO_INCREMENT BIGINT to random UUIDs (for distributed uniqueness and to hide row counts). Soon, INSERT performance tanks on large tables, disk usage balloons, and the buffer pool thrashes. Why does a random UUID PK hurt InnoDB so badly, and how do you keep UUIDs without the pain?

Why It Matters — InnoDB's Clustered Index

In InnoDB, the primary key IS the physical storage order (the clustered index). Rows are stored on disk pages sorted by PK. This makes the nature of the PK critical to write performance.

  • AUTO_INCREMENT: Each new row has the next sequential id, so it's always appended to the last page — tight, sequential, cache-friendly writes.
  • Random UUID (v4): Each new id lands at a random position, forcing InnoDB to insert into the middle of pages — causing page splits, fragmentation, and random I/O.
The root cause — page splits: When a random UUID must be inserted into an already-full page, InnoDB splits the page in two and shuffles rows. This wastes space (pages ~50% full), thrashes the buffer pool (random pages loaded/evicted), and multiplies disk writes.

Sequential vs Random Insert Behaviour

AspectAUTO_INCREMENTRandom UUID (v4)
Insert positionAlways at the endRandom (middle of pages)
Page splitsRareFrequent
Page fill~93% (tight)~50% (fragmented)
Buffer poolHot pages cachedThrashing (random I/O)
PK size8 bytes (BIGINT)16 bytes binary / 36 char
Secondary index bloatSmall (PK stored in each)Large (bigger PK copied everywhere)
The hidden secondary-index cost: InnoDB stores the primary key inside every secondary index. A 36-char UUID PK makes every secondary index far larger than with an 8-byte BIGINT — multiplying storage and slowing all index lookups.

The Fix — Ordered UUIDs (UUIDv7 / time-sortable)

You can keep global uniqueness without the random-insert penalty by using a time-ordered UUID (UUIDv7, or a rearranged UUIDv1). Because the leading bits are a timestamp, new ids are mostly sequential — restoring append-friendly inserts.

Storing UUIDs Efficiently

-- BAD: UUID as CHAR(36) -> 36 bytes, huge, random
CREATE TABLE orders_bad (
    id   CHAR(36) PRIMARY KEY,     -- random UUIDv4 string
    data VARCHAR(255)
);

-- BETTER: store as BINARY(16) -> half the size, still random (v4)
CREATE TABLE orders_ok (
    id   BINARY(16) PRIMARY KEY,
    data VARCHAR(255)
);
-- Insert: UUID_TO_BIN(UUID())

-- BEST: time-ordered UUID stored as BINARY(16) -> sequential inserts
CREATE TABLE orders_best (
    id   BINARY(16) PRIMARY KEY,   -- UUIDv7 / ordered UUID
    data VARCHAR(255)
);

-- MySQL 8.0 trick: UUID_TO_BIN(uuid, 1) swaps time fields to make v1
-- UUIDs time-sortable, giving near-sequential insert order
INSERT INTO orders_best (id, data)
VALUES (UUID_TO_BIN(UUID(), 1), 'payload');

When to Use Each

ChoiceBest For
AUTO_INCREMENT BIGINTSingle DB, best performance, internal ids
UUIDv7 / ordered UUID (BINARY 16)Distributed systems needing global uniqueness + good writes
Random UUIDv4Only when unpredictability is essential and write volume is low
The pragmatic pattern: Many teams keep a BIGINT AUTO_INCREMENT as the internal PK (fast joins/storage) and add a separate UUID column (unique, indexed) as the public/external id. You get sequential-insert performance internally and unguessable ids externally — the best of both.

Interviewer follow-up: "Why not just use AUTO_INCREMENT everywhere then?" → In distributed/sharded systems, a single auto-increment counter becomes a bottleneck and can collide across shards. UUIDs (ideally ordered) or coordinated schemes like Snowflake IDs give shard-safe uniqueness without a central sequence — that's the real reason to move away from plain AUTO_INCREMENT.
← Previous Soft delete vs hard delete — correct implementation, UNIQUE constraint pitfalls, and the forgotten-filter bug