【MySQL深入详解】第14篇：标识符设计——主键选择的艺术

开篇引入

"用什么做主键？"这个问题，每个DBA都被问过无数遍。

自增ID？UUID？业务ID？还是复合主键？选择错了，数据迁移痛苦、索引效率低下、主从复制出问题。选择对了，数据增长平稳、查询高效、扩展无忧。

《高性能MySQL》第6章专门讨论了标识符设计，这篇文章帮你做出正确的选择。

主键的基本原则

主键必须满足

1. 唯一性：不能有重复值
2. 非空：NOT NULL
3. 不变性：值不应该改变
4. 简洁性：越小越好，越简单越好

主键的选择标准

优先级顺序：
1. 整型 > 字符串 > 复合类型
2. 自增 > 有序UUID > 随机UUID
3. 单列 > 多列
4. 无业务含义 > 有业务含义

整型主键：自增ID

为什么整型最好

-- 整型主键的优点：
-- 1. 比较操作快（CPU原生支持）
-- 2. 占用空间小（INT=4字节，BIGINT=8字节）
-- 3. 索引效率高
-- 4. B+树插入稳定（顺序增长）

CREATE TABLE orders (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  -- 推荐BIGINT
    ...
);

AUTO_INCREMENT的配置

-- 查看自增值
SHOW CREATE TABLE orders;
-- AUTO_INCREMENT=1

-- 设置自增值起始点
ALTER TABLE orders AUTO_INCREMENT = 10000;

-- 配置最大自增值
SHOW VARIABLES LIKE 'auto_increment%';
-- auto_increment_increment: 自增步长
-- auto_increment_offset: 起始偏移量

自增ID的缺点

1. 分布式环境下不唯一

-- 多主复制时，自增ID可能冲突
-- 解决方案：设置不同的步长和偏移量
-- 主库1: auto_increment_offset=1, auto_increment_increment=2
-- 主库2: auto_increment_offset=2, auto_increment_increment=2

2. 无法在插入前获取

-- 如果需要提前知道ID（用于关联表）
-- 方案1：先插入获取ID
INSERT INTO orders (...) VALUES (...);
SET @new_id = 3172850;

-- 方案2：使用应用生成的唯一ID

UUID：通用唯一标识符

UUID的类型

-- UUID v1: 基于时间和MAC地址
-- UUID v4: 随机生成
-- UUID v5: 基于命名空间的SHA-1哈希

-- MySQL存储UUID的方式
CREATE TABLE sessions (
    id CHAR(36) PRIMARY KEY,  -- 标准UUID格式
    data TEXT
);

-- 更高效的存储方式：去除连字符
CREATE TABLE sessions_v2 (
    id VARCHAR(32) PRIMARY KEY,
    data TEXT
);

-- 存储为二进制（节省一半空间）
CREATE TABLE sessions_v3 (
    id BINARY(16) PRIMARY KEY,
    data TEXT
);

随机UUID的问题

-- 随机UUID插入性能差
INSERT INTO orders VALUES (UUID(), ...);

-- 问题：随机UUID插入B+树是随机的
-- 结果：频繁的页分裂
-- 结果：索引碎片化
-- 结果：插入性能下降

有序UUID：UUID v7

MySQL 8.0.17+支持UUID v7（时间有序）：

-- UUID v7：时间戳+随机数
SELECT UUID_TO_BIN(UUID(), 1);

-- 或者在MySQL 8.0.30+
SELECT BIN_TO_UUID(UUID_TO_BIN(UUID(), TRUE), TRUE);

应用层生成有序ID

如果MySQL不支持UUID v7，可以在应用层生成：

// Snowflake算法的Go实现
// ID结构：timestamp(41位) + machine(10位) + sequence(12位)
// 64位有符号整数
func GenerateSnowflake() int64 {
    // 时间戳（毫秒）
    timestamp := time.Now().UnixNano() / 1000000
    // 机器ID
    machineId := getMachineId()
    // 序列号
    sequence := getNextSequence()
    
    return (timestamp - EPOCH) << 22 | 
           machineId << 12 | 
           sequence
}

业务主键：自然键

什么时候用业务主键

-- 适合用业务主键的场景：
-- 1. 业务上已经有唯一标识
-- 2. 需要在关联查询中直接使用
-- 3. 表之间有明确的业务关系

-- 用户表：用用户名或邮箱
CREATE TABLE users (
    username VARCHAR(50) PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    ...
);

-- 国家表：用ISO代码
CREATE TABLE countries (
    country_code CHAR(2) PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL,
    ...
);

-- 银行表：用SWIFT代码
CREATE TABLE banks (
    swift_code CHAR(11) PRIMARY KEY,
    bank_name VARCHAR(100) NOT NULL,
    ...
);

业务主键的优点

• 查询时可以直接使用
• 无需先查询获取ID
• 业务含义清晰

业务主键的缺点

• 可能是字符串（比较慢）
• 可能很长（占用空间）
• 可能变化（违反不变性原则）

复合主键

什么时候用复合主键

-- 适合复合主键的场景：
-- 1. 两列或多列共同唯一
-- 2. 这几列天然是关联关系
-- 3. 避免创建额外的自增ID

-- 订单明细表
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- 用户角色关联表
CREATE TABLE user_roles (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

复合主键的问题

-- 问题1：主键太长
-- 如果有10列做主键，主键索引会非常大

-- 问题2：外键引用复杂
-- 外键必须包含所有主键列
-- 如果以后要改主键，外键也要改

-- 问题3：查询效率
-- 单列主键可以直接比较
-- 复合主键需要比较多个列

推荐方案

场景1：普通业务表

-- 推荐：自增BIGINT主键
CREATE TABLE users (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ...
) ENGINE=InnoDB;

场景2：需要分布式唯一ID

-- 推荐：应用层生成或UUID v7
CREATE TABLE orders (
    id VARCHAR(32) PRIMARY KEY,  -- 雪花ID或UUID v7
    user_id BIGINT UNSIGNED NOT NULL,
    order_no VARCHAR(32) NOT NULL UNIQUE,  -- 业务订单号
    amount DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB;

场景3：关联表（多对多）

-- 推荐：复合主键或单独ID
CREATE TABLE user_roles (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  -- 单独ID更灵活
    user_id BIGINT UNSIGNED NOT NULL,
    role_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_role (user_id, role_id),  -- 唯一约束
    INDEX idx_user_id (user_id),
    INDEX idx_role_id (role_id)
) ENGINE=InnoDB;

场景4：历史数据表/日志表

-- 推荐：时间序ID（避免自增ID用完）
CREATE TABLE access_logs (
    id BIGINT UNSIGNED PRIMARY KEY,  -- 雪花ID
    user_id INT UNSIGNED,
    action VARCHAR(50) NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),  -- 微秒精度
    INDEX idx_created_at (created_at),
    INDEX idx_user_action (user_id, action, created_at)
) ENGINE=InnoDB;

外键设计

外键是否必要

支持外键的理由：

• 数据完整性自动保证
• 级联更新/删除
• 开发时减少数据校验

反对外键的理由：

• 插入、更新变慢（需要检查约束）
• 级联操作难以追踪
• 跨数据库迁移困难
• 分库分表时无法使用外键

建议：

• 小型项目、单机数据库：可以使用外键
• 大型项目、分布式架构：不使用外键，在应用层保证

-- 如果使用外键，注意级联选项
CREATE TABLE orders (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    ...
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT  -- 不允许删除有订单的用户
        ON UPDATE CASCADE   -- 用户ID变更自动更新
);

主键与性能

主键与查询效率

-- 主键查询最快
SELECT * FROM users WHERE id = 12345;

-- 如果主键是UUID，查询会变慢
SELECT * FROM users WHERE id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';

-- 解决方案：使用INT/BIGINT做主键，业务UUID做唯一索引
CREATE TABLE users (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE,
    ...
);

主键与索引

-- InnoDB表必须有主键
-- 如果没有显式主键，InnoDB会生成隐式主键
-- 隐式主键是6字节的整型，不可控

-- 建议：始终显式定义主键
CREATE TABLE logs (
    -- 没有主键，InnoDB会自动创建
);

主键与存储

-- 主键越小，次级索引越小
-- 次级索引存储的是主键值

-- BIGINT(8字节) vs CHAR(36字节)
-- 如果有10个次级索引，每个索引都存主键值
-- CHAR主键比BIGINT多占用 36/8 = 4.5倍 的空间

小结

1. 首选自增BIGINT主键：整型比较快，占用空间小
2. 分布式环境用雪花ID或UUID v7：保证全局唯一且有序
3. 业务唯一标识用UNIQUE约束：不影响主键效率
4. 避免复合主键：除非真的需要多列唯一
5. 永远不要用随机UUID做主键：会导致严重的索引碎片
6. 主键应NOT NULL：InnoDB的隐式主键不支持NULL
7. 主键越小越好：次级索引都存储主键值
8. 外键根据场景决定：小型单体项目可以用，大型分布式项目不用

主键选择是Schema设计的核心决策，选对了后期维护成本大大降低。

---

延伸阅读

• 《高性能MySQL》第6章 schema设计与管理
• UUID v7 Specification: www.ietf.org/archive/id/…
• Snowflake Algorithm: Twitter's ID Generation