UUIDs in MySQL: B-tree Performance and Page Splits

1. Problem: write latency and throughput degradation from random UUID primary keys

MySQL InnoDB uses clustered indexes to organize physical table data, storing rows in B+Tree leaf nodes ordered by primary key. Inserting unordered, random UUID v4 values (f47ac10b-...) distributes inserts randomly across B+Tree pages, causing frequent Page Splits and heavy random disk I/O.

2. Minimal reproduction: unordered UUID inserts vs. sequential B+Tree page splits

This comparison illustrates the physical difference between sequential primary key appends and random UUID inserts in InnoDB:

/* 1. Auto-increment / Sequential Primary Keys (AUTO_INCREMENT / UUID v7): Sequential Append */
// Rows append to the rightmost leaf pages, maintaining ~93% page fill factors with zero page splits!

/* 2. Unordered UUID v4 Primary Keys: Random Insertion */
// Inserts hit full interior pages randomly -> triggers 50/50 page splits!
// Creates high page fragmentation (fill factors drop to ~50%), inflating disk size and trashing Buffer Pool caches.

3. Root cause: B+Tree page fill factors, Buffer Pool LRU pollution, and storage bloat

1. Frequent 50/50 Page Splits: InnoDB data pages default to 16KB. Random inserts into full pages force InnoDB to allocate new pages and move 50% of the rows, creating random disk writes and index fragmentation. 2. Buffer Pool Cache Thrashing: random inserts force unreferenced data pages into memory, evicting hot pages from the Buffer Pool LRU cache. 3. Storage Bloat: VARCHAR(36) consumes 36–144 bytes compared to 8-byte BIGINT; secondary indexes store the full primary key, amplifying storage bloat.

4. Recommendation: MySQL 8.0 UUID_TO_BIN timestamp swapping and UUID v7 sequential optimization

1. Optimize Storage Type: use BINARY(16) instead of VARCHAR(36), shrinking primary key footprint from 36 bytes to 16 bytes. 2. Timestamp Byte Reordering: use MySQL 8.0 UUID_TO_BIN(uuid, 1) to swap UUID v1 timestamp bytes into monotonic order. 3. Modern Best Practice: adopt RFC 9562 UUID v7 or ULID, which feature 48-bit millisecond timestamps for sequential B+Tree appends.

5. Complete code: MySQL 8.0 timestamp-swapped UUID v1 to BINARY(16) helper function

This TypeScript code demonstrates application-level byte reordering similar to MySQL 8.0 UUID_TO_BIN(uuid, 1), converting unordered v1 UUIDs into B+Tree friendly sequential byte arrays.

function uuidV1ToOrderedBinary16(uuidV1Str: string): Uint8Array {
  const clean = uuidV1Str.replace(/-/g, "");
  if (clean.length !== 32) {
    throw new Error("Invalid UUID format");
  }

  const timeLow = clean.substring(0, 8);
  const timeMid = clean.substring(8, 12);
  const timeHi = clean.substring(12, 16);
  const rest = clean.substring(16, 32);

  const orderedHex = timeHi + timeMid + timeLow + rest;

  const bytes = new Uint8Array(16);
  for (let i = 0; i < 16; i++) {
    bytes[i] = parseInt(orderedHex.substr(i * 2, 2), 16);
  }
  return bytes;
}

const v1Uuid = "6c84fb90-12c4-11ee-be56-0242ac120002";
const orderedBytes = uuidV1ToOrderedBinary16(v1Uuid);
console.log("Reordered B+Tree friendly 16-byte Binary length:", orderedBytes.length);

6. Incorrect approaches

Using VARCHAR(36) UUID v4 as primary keys in high-throughput production tables. Blindly using long string keys on tables with numerous secondary indexes. Assuming UUID performance implications in InnoDB apply identically to all database engines.

7. Boundaries: read-heavy workloads, sharding, and bulk insertion trade-offs

In read-heavy, low-volume tables or distributed sharding environments where global uniqueness outweighs write performance, UUID primary keys may be acceptable. Evaluate performance based on workload, table size, and storage engine.

8. Verification

Check Data_free page fragmentation ratios using SHOW TABLE STATUS. Analyze secondary index memory footprints via EXPLAIN. Benchmark TPS and latency differences across auto-increment, UUID v4, and UUID v7 keys.

9. FAQ

Why is PostgreSQL less affected by UUID primary key performance issues than MySQL? PostgreSQL defaults to heap tables where data rows and primary keys are decoupled; rows append directly to heap file ends without triggering InnoDB-style physical row reordering.

What does the 1 parameter in MySQL 8.0 UUID_TO_BIN(uuid, 1) signify? It enables the swap_flag, moving UUID v1's low-order rapid timestamp bytes to high-order positions for monotonic ordering.

Can UUID v7 replace auto-increment primary keys completely? Yes. UUID v7 prevents business metrics leakage and sharding conflicts while delivering sequential B+Tree append performance.

10. Summary

Primary key design must adapt to storage engine data structures. Avoid unordered UUID v4 clustered keys in InnoDB; leverage BINARY(16) storage, byte reordering, or UUID v7 for high throughput and low fragmentation.