UUID Primary Key Performance in Databases

B-Tree Index and Random Insert Performance Issues

MySQL InnoDB and PostgreSQL primary key indexes both use B-Tree (balanced binary tree) structure, with data stored in order of the primary key in the clustered index (InnoDB) or index on heap table (PostgreSQL). Auto-increment integer primary key inserts always happen at the rightmost side of the index (maximum value), with new pages only added at the end, causing virtually no page splits. UUID v4 (random) inserts at random positions: new UUIDs may fall anywhere in the index tree, requiring insertion in the middle of existing data, causing frequent Page Splits. Cost of page splits: requires reading old pages, allocating new pages, copying partial data, updating parent node pointers — much higher overhead than normal inserts, producing significant fragmentation and reducing page utilization (from near 100% to about 50%).

Performance Benchmark Reference

Based on typical results from multiple database performance tests: in MySQL InnoDB, random UUID primary keys are approximately 30%-50% slower in write performance than auto-increment integer primary keys (at 10 million records scale); under high write pressure (thousands of inserts per second), the performance gap may reach 2-5x; index fragmentation rate: UUID v4 primary key tables after large insert volumes typically have 30%-60% fragmentation, while auto-increment primary keys have virtually none; storage overhead: UUID primary keys (varchar(36) or char(36)) are 4-4.5x larger than integers (bigint, 8 bytes) — in tables with multiple secondary indexes, each secondary index includes the primary key, amplifying the storage effect further. These numbers vary by database version, hardware configuration, and workload type; specific scenarios should be benchmarked.

Solution 1: Using BINARY(16) Storage

Storing UUID as 16-byte BINARY(16) rather than varchar(36) reduces storage overhead by approximately 56% (16 vs 36 bytes), reduces index size, indirectly improves B-Tree fan-out (more keys can fit in each node), reduces tree height, and improves read performance. In MySQL, use UUID_TO_BIN() and BIN_TO_UUID() functions for conversion. PostgreSQL's native UUID type already stores as 16 bytes, requiring no additional handling.

-- MySQL：BINARY(16) 方案
CREATE TABLE users (
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID())),
    name VARCHAR(100),
    email VARCHAR(200) UNIQUE
);

-- 插入
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');

-- 查询
SELECT BIN_TO_UUID(id) as id, name
FROM users
WHERE id = UUID_TO_BIN('550e8400-e29b-41d4-a716-446655440000');

-- PostgreSQL：直接使用 UUID 类型（16字节）
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT,
    email TEXT UNIQUE
);

Solution 2: Ordered UUID (UUID v7 or Rearranged UUID v1)

-- MySQL 8.0+：重排 UUID v1 使其有序
CREATE TABLE orders (
    -- UUID_TO_BIN(UUID(), 1) 的第二个参数 1 表示重排时间字段
    -- 使时间戳部分排在高位，保证插入顺序性
    id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1)),
    customer_id INT NOT NULL,
    total DECIMAL(10,2)
);

-- 验证有序性（连续生成的 UUID 应该递增）
SELECT BIN_TO_UUID(id, 1) FROM orders ORDER BY id LIMIT 10;

-- 应用层使用 UUID v7（推荐新项目）
-- Python
# pip install uuid7
import uuid7
id = uuid7.uuid7()
print(id)  # 时序有序的 UUID v7

-- JavaScript
// npm install uuid
import { v7 as uuidv7 } from 'uuid';
console.log(uuidv7()); // 有序 UUID v7

Solution 3: Dual Primary Key Scheme

For scenarios requiring both performance and security, a dual primary key scheme can be used: internally use auto-increment integer primary key (database-managed, optimal index performance), externally use UUID as a public_id column (with unique index), only exposing UUID in external APIs. The cost of this scheme is additional storage (20-28 extra bytes per row) and an additional index. In exchange, it provides optimal index performance (auto-increment primary key) and security (no sequence exposure). Suitable for scenarios already using auto-increment primary keys that need gradual migration.

CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 内部 ID，性能最优
    public_id CHAR(36) NOT NULL UNIQUE,     -- 对外 UUID
    name VARCHAR(200),
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入时在应用层生成 public_id
INSERT INTO products (public_id, name, price)
VALUES (UUID(), 'Product A', 99.99);

-- 对外 API 使用 public_id 查询
SELECT * FROM products WHERE public_id = ?;

-- 内部联表使用 id（性能更好）
SELECT p.*, c.name as category_name
FROM products p JOIN categories c ON p.category_id = c.id
WHERE p.id = 12345;

Regular Maintenance: Rebuilding Fragmented Indexes

-- MySQL：优化（重建）碎片化的 UUID 主键表
OPTIMIZE TABLE users;  -- 重建表和所有索引

-- 或者使用 ALTER TABLE 重建（在线操作，不锁表）
ALTER TABLE users ENGINE=InnoDB;

-- PostgreSQL：重建索引
REINDEX INDEX users_pkey;  -- 重建特定索引
REINDEX TABLE users;       -- 重建表上所有索引
VACUUM FULL users;         -- 回收碎片空间（会锁表，谨慎使用）
VACUUM ANALYZE users;      -- 不锁表的版本

-- 查看索引碎片率（MySQL）
SELECT
  table_name,
  data_free / (data_length + index_length) * 100 as fragmentation_pct
FROM information_schema.tables
WHERE table_schema = 'your_db' AND table_name = 'users';