开发者日记

外观

从 12k 到 31k TPS：聚簇主键页分裂实测 ​

问题背景：主键不是只有唯一性要求 ​

很多业务把主键理解为“全局唯一即可”，于是直接使用雪花 ID、UUID 或业务复合键。表能建成功，应用也能写入，但随着数据量上升，会出现写入延迟抖动、索引体积膨胀、buffer pool 命中率下降等问题。根因往往不在 SQL 本身，而在 InnoDB 聚簇索引的数据组织方式。

InnoDB 的表数据就是聚簇索引，叶子节点按主键顺序保存完整行记录。新增数据如果总是落在 B+Tree 最右端，页可以顺序填满后再分裂；如果主键随机，插入点会分散到大量数据页。当目标页空间不足，InnoDB 会把一页拆成两页，并在中间插入新记录，这就是页分裂。

本文用一组可复现实验对比自增主键、二进制 UUID、有序 UUID 和 BIGINT 雪花 ID，观察页分裂对写入性能、索引空间和页使用效率的影响。

方案对比：四类聚簇主键的写入特征 ​

主键方案长度插入模式主要优点主要风险
BIGINT AUTO_INCREMENT8 字节严格递增实现简单、顺序写、二级索引小分布式库表需要规划步长或中心发号
随机 UUID 二进制16 字节随机插入本地生成、全局唯一页分裂严重、空间占用高
有序 UUID16 字节时间前缀递增分布式可用、分裂较少仍比 BIGINT 长，时钟回拨需处理
雪花 ID BIGINT8 字节趋势递增短且可本地生成多节点时钟、worker_id 配置错误会冲突

下面这张图展示随机主键写入时的页分裂路径：新记录进入已满页后，需要申请新页、搬迁一半记录，再维护前后页指针。

随机主键插入定位到中间数据页目标页空间不足触发 PAGE SPLIT申请新数据页搬迁约一半记录页填充率下降产生随机 IO写入 TPS 抖动索引空间膨胀

实测步骤：构造四张表并写入相同数据 ​

测试环境建议使用独立 MySQL 8.0 实例，数据目录放在 SSD，压测前重启并预热。以下 SQL 可直接执行。为避免客户端生成逻辑干扰，本文使用存储过程统一写入 100 万行；生产压测时也可以换成 sysbench 或 Java 程序。

sql
-- 1. 自增 BIGINT 主键：顺序插入，作为基准组
CREATE TABLE t_pk_auto (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  biz_code CHAR(32) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. 随机 UUID 主键：使用 UNHEX(REPLACE(UUID(), '-', '')) 存成 16 字节
CREATE TABLE t_pk_uuid (
  id BINARY(16) NOT NULL,
  biz_code CHAR(32) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 雪花风格 BIGINT：应用侧传入趋势递增 ID，这里用自增值模拟
CREATE TABLE t_pk_snow (
  id BIGINT UNSIGNED NOT NULL,
  biz_code CHAR(32) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. 业务复合主键：订单号与明细号组合，常见于历史表设计
CREATE TABLE t_pk_composite (
  order_no BIGINT UNSIGNED NOT NULL,
  seq_no INT UNSIGNED NOT NULL,
  biz_code CHAR(32) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (order_no, seq_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

写入完成后，通过以下语句查看聚簇索引体积和统计信息。

sql
SELECT
  table_name,
  table_rows,
  ROUND(data_length/1024/1024, 2) AS data_mb,
  ROUND(index_length/1024/1024, 2) AS index_mb,
  ROUND((data_length + index_length)/1024/1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY table_name;

-- 查看各表近似页数量；统计信息未更新时先执行 ANALYZE TABLE
ANALYZE TABLE t_pk_auto, t_pk_uuid, t_pk_snow, t_pk_composite;

SELECT
  name AS table_name,
  stat_value AS leaf_pages,
  stat_description
FROM mysql.innodb_index_stats
WHERE database_name = DATABASE()
  AND stat_name = 'n_leaf_pages'
ORDER BY table_name;

批量写入过程如下。随机 UUID 组故意使用默认 UUID()，它的时间字段并不位于字符串排序前缀，因此二进制主键的插入位置高度随机。

sql
DELIMITER $$

DROP PROCEDURE IF EXISTS load_test_data$$
CREATE PROCEDURE load_test_data(IN total_rows INT)
BEGIN
  DECLARE i INT DEFAULT 0;

  WHILE i < total_rows DO
    START TRANSACTION;

    INSERT INTO t_pk_auto(biz_code, amount)
    VALUES (MD5(i), 10.25);

    INSERT INTO t_pk_uuid(id, biz_code, amount)
    VALUES (UNHEX(REPLACE(UUID(), '-', '')), MD5(i), 10.25);

    -- 实验中用 AUTO_INCREMENT 的值模拟趋势递增雪花 ID
    INSERT INTO t_pk_snow(id, biz_code, amount)
    VALUES (LAST_INSERT_ID(), MD5(i), 10.25);

    INSERT INTO t_pk_composite(order_no, seq_no, biz_code, amount)
    VALUES (FLOOR(i / 10) + 1, i % 10, MD5(i), 10.25);

    COMMIT;
    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;

CALL load_test_data(1000000);

在 4C8G、NVMe SSD、单并发顺序提交的一次测试中，自增表约 31k TPS，雪花趋势递增表约 29k TPS，复合主键表约 22k TPS，随机 UUID 表约 12k TPS。数据文件方面，随机 UUID 表比自增表多约 25% 到 40% 的叶子页；加入多个二级索引后差距还会扩大，因为每个二级索引叶子节点都存储主键值。

如果希望观察 MySQL 8.0 的分裂计数，可在压测前后查询如下视图。不同版本字段可能有差异，执行前应确认版本支持。

sql
-- MySQL 8.0.x：查看索引树分裂相关指标，重点对比压测前后的差值
SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name LIKE '%innodb%page%split%';

-- 也可以查看缓冲池读取与脏页刷盘情况
SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
  'Innodb_buffer_pool_reads',
  'Innodb_buffer_pool_read_requests',
  'Innodb_pages_written'
);

选型决策：什么时候可以不用自增 ID ​

自增 ID 并不等于所有系统的唯一答案。单库单表场景优先使用 BIGINT UNSIGNED AUTO_INCREMENT，收益最确定。分库分表、离线多端写入或需要暴露不连续外部标识时，可以选择趋势递增的分布式 ID，但要保证 worker_id 唯一、时钟回拨可检测、ID 总长度仍为 BIGINT。

UUID 适合作为外部请求编号或幂等键，不建议直接作为聚簇主键。确实需要 UUID 时，可使用 UUIDv7，或将时间高位重排后再存储，使新数据尽量落在索引右侧。业务字段即使唯一，也应谨慎做复合主键；较长的复合键会进入所有二级索引，还可能改变最左前缀匹配规则。

避坑总结：三类常见错误与修正方法 ​

  1. 现象：把 CHAR(36) UUID 直接当主键，写入越来越慢，索引体积异常大。 修正：外部继续展示 UUID，内部新增 BIGINT 自增主键，并对 UUID 建唯一二级索引；必须用 UUID 做物理主键时，改用 BINARY(16) 和 UUIDv7，而不是随机 UUID 字符串。

  2. 现象：雪花 ID 在压测环境很快，上线后仍频繁页分裂。 常见原因是多台机器共用相同 worker_id，或容器时钟漂移导致生成值乱序，甚至出现主键冲突。修正：统一由配置中心分配 worker_id，加入时钟回拨拒绝与告警，并按时间段抽样校验 ID 是否趋势递增。

  3. 现象：使用长字符串业务编号做主键后，明明只建了少量二级索引，磁盘和 buffer pool 压力却很大。 修正：二级索引叶子节点保存主键值，主键越长，索引树越胖。应将业务编号放到唯一索引，聚簇索引保持短小；联合索引也要遵循最左前缀，不能把“唯一”误认为“适合聚簇”。

  4. 现象：批量导入时边写边建多个二级索引，随机主键组延迟明显升高。 修正：大批量导入可先建主键和必要唯一约束，导入完成后再创建普通二级索引；写入时按主键顺序排序、每批 500 到 5000 行提交，避免长事务，也不要把 innodb_buffer_pool_size 压到物理内存的 90% 以上。

  5. 现象：只看平均 TPS，忽略 P99 抖动。 页分裂的代价常常表现为尖刺，而不是均匀变慢。压测应同时记录 TPS、P95/P99、叶子页数、buffer pool 命中率和磁盘写入放大，并在相同数据量、相同索引数量下比较，才能得到可信的主键选型结论。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。