自增主键最优，因其避免页分裂、节省二级索引空间、提升缓存命中率；但高并发下需关注innodb_autoinc_lock_mode锁模式，分库分表、id暴露、强连续性需求等场景应慎用。
AUTO_INCREMENT 主键在 InnoDB 表中不是“更方便”，而是直接关系到写入性能、索引结构稳定性和二级索引空间开销。如果你的表没有特殊分布式或安全要求，用它就是最省心也最稳的选择。
自增主键如何避免页分裂
InnoDB 的聚簇索引按主键顺序物理存储数据。新记录插入时，如果主键值比现有最大值还大（比如id=1001 插入到 id=1000 之后），就直接追加到 B+ 树叶子节点末尾——不挪动已有数据，不触发页分裂。
但换成 UUID 或身份证号这类随机主键：
- 新值可能落在任意位置（比如插在
id=500和id=501中间） - 对应的数据页若已满，就必须申请新页、搬移部分记录，即“页分裂”
- 分裂后两个页利用率下降，磁盘碎片增多，后续写入和查询 I/O 增加
实操建议：
- 建表时明确声明
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT，别依赖隐式行为 - 避免手动
INSERT INTO ... VALUES (123, ...)覆盖自增值，否则可能破坏递增连续性，诱发意外分裂 - 批量导入时用
INSERT ... SELECT或LOAD DATA INFILE，它们仍走自增逻辑，但要注意innodb_autoinc_lock_mode配置（默认1安全，2更高并发但不保证语句级连续）
为什么主键小能显著节省空间
InnoDB 的每个二级索引（比如INDEX idx_name ON users(name)）的叶子节点里，存的不是行指针，而是**主键值**。这意味着主键长度直接影响所有二级索引体积。
对比一下：
- 用
INT自增主键：每个二级索引叶子节点占 4 字节 - 用
VARCHAR(36)存 UUID：至少 36 字节，实际存储还受字符集影响（UTF8MB4 下可能达 144 字节） - 结果：同样 1000 万行数据，二级索引体积可能差 3–9 倍，缓存命中率直线下降
常见错误：
- 把
CHAR(36)当主键，没转成BINARY(16)存 UUID —— 这会让索引膨胀得更厉害 - 误以为 “主键只用一次，空间无所谓”，忽略了它对所有二级索引的放大效应
自增 ID 在高并发下锁竞争的真实情况
自增 ID 并非天然“无锁”。InnoDB 通过innodb_autoinc_lock_mode 控制锁行为，三种模式差异极大：
关键参数说明：
-
innodb_autoinc_lock_mode = 0（传统模式）：每条INSERT都加表级 AUTO_INC 锁 → 高并发下严重排队 -
innodb_autoinc_lock_mode = 1（默认）：单条 INSERT 加轻量锁；批量 INSERT（如INSERT ... SELECT）加语句级锁 → 平衡安全与性能 -
innodb_autoinc_lock_mode = 2（交错模式）：非事务性 INSERT 不加锁，允许并发分配 → 吞吐最高，但同一语句内 ID 可能不连续
实操建议：
- 生产环境别改默认值（
1），除非你压测确认2真的带来收益且能接受 ID 不连续 - 避免在事务中混合使用
INSERT和REPLACE INTO，后者可能预占 ID 却未真正插入，造成空洞 - 重启 MySQL 8.0+ 实例不会丢自增值（靠 redo log 持久化），但 5.7 及以前版本会扫描表找 MAX(id)，删过大量数据后可能回退
什么情况下不该用自增主键
自增不是银弹。当出现以下任一情况，就得重新评估：典型场景：
- 分库分表：多个 MySQL 实例各自生成
AUTO_INCREMENT，ID 必然重复 → 改用雪花 ID 或 UUID + 映射表 - ID 对外暴露（如 API 返回
/user/123456）：连续 ID 易被爬虫推测总量 → 加一层业务 ID（business_id）作对外标识 - 需要按时间排序且避免跳号：自增 ID 不保证连续（事务回滚、冲突、批量插入都会留空洞）→ 别拿它做计数或分页依据
- 主从架构下强依赖 ID 连续性：主库宕机后部分自增值未同步，从库恢复可能跳号甚至冲突
最容易被忽略的一点是：自增主键的“优势”全部建立在单机 InnoDB 场景下。一旦跨实例、跨集群、跨服务，它的简单性立刻变成扩展性瓶颈。选主键前，先问清楚——这个表未来三年会不会拆？要不要对外提供 ID？有没有多活或迁移计划？
