一、主键设计的核心原则
主键是数据库表的灵魂所在，它就像每个人的身份证号码，必须满足几个基本特性：唯一性、非空性和不可变性。在设计MySQL表结构时，我们通常会考虑以下几种主键设计方案：
- 自增整数：最常用的方案，简单高效
- UUID：全局唯一标识符
- 业务主键：使用有业务意义的字段
- 复合主键：多个字段组合
让我们看一个典型的学生表设计示例（技术栈：MySQL）：
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY, -- 自增主键
student_no VARCHAR(20) UNIQUE, -- 学号业务唯一键
name VARCHAR(50) NOT NULL,
class_id INT,
INDEX idx_class (class_id) -- 班级索引
) ENGINE=InnoDB;
这个设计采用了自增主键+业务唯一键的模式，既保证了插入性能，又满足了业务查询需求。值得注意的是，我们为class_id建立了普通索引，这是为了优化按班级查询的性能。
二、自增ID的优势与隐患
自增ID看似完美，实则暗藏玄机。先说说它的优点：
- 插入性能极佳：因为是顺序写入，不会导致页分裂
- 存储空间小：通常4字节就能满足
- 索引效率高：B+树结构对数字类型特别友好
但问题也随之而来，让我们通过一个电商订单系统的例子来说明（技术栈：MySQL）：
-- 订单表设计
CREATE TABLE orders (
order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
amount DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user (user_id)
);
-- 分库分表时可能出现的问题
INSERT INTO orders_1 (order_id, user_id, amount)
VALUES (10001, 123, 99.9); -- 分片1
INSERT INTO orders_2 (order_id, user_id, amount)
VALUES (10001, 456, 199.9); -- 分片2出现相同ID！
这里暴露了自增ID的最大问题：在分布式系统中可能产生冲突。解决方案包括使用UUID、雪花算法等分布式ID生成方案。
三、业务主键的适用场景
不是所有表都适合用自增ID，有时候业务字段本身就是天然的主键。比如用户表，手机号或邮箱可能就是最佳选择：
CREATE TABLE users (
mobile VARCHAR(20) PRIMARY KEY, -- 使用手机号作为主键
password VARCHAR(100) NOT NULL,
nickname VARCHAR(50),
created_at DATETIME DEFAULT NOW()
);
-- 查询示例
SELECT * FROM users WHERE mobile = '13800138000';
这种设计的好处是：
- 省去了额外的ID字段
- 业务查询直接命中主键索引
- 避免了不必要的JOIN操作
但要注意，如果业务键可能变更（比如手机号可修改），就不适合作为主键，因为主键应当是不可变的。
四、复合主键的艺术
当单个字段无法唯一标识记录时，复合主键就派上用场了。典型的例子是学生选课关系表：
CREATE TABLE student_courses (
student_id INT NOT NULL,
course_id INT NOT NULL,
score TINYINT,
selected_at DATETIME,
PRIMARY KEY (student_id, course_id), -- 复合主键
INDEX idx_course (course_id) -- 反向查询索引
);
-- 查询某个学生的所有课程
SELECT * FROM student_courses WHERE student_id = 1001;
-- 查询某门课程的所有学生
SELECT * FROM student_courses WHERE course_id = 2001;
复合主键的设计要考虑查询模式，将最常用的查询条件放在前面。同时要为反向查询建立适当的索引。
五、特殊场景下的主键选择
在高并发秒杀系统中，传统的主键设计可能成为瓶颈。来看一个优化案例（技术栈：MySQL）：
-- 商品库存表
CREATE TABLE inventory (
item_code VARCHAR(20) PRIMARY KEY, -- 商品编码
stock INT UNSIGNED NOT NULL,
version INT UNSIGNED DEFAULT 0 -- 乐观锁版本号
);
-- 秒杀扣减库存操作
UPDATE inventory
SET stock = stock - 1,
version = version + 1
WHERE item_code = 'SKU123'
AND stock >= 1
AND version = #{oldVersion};
这里我们使用了业务主键+乐观锁的方案，避免了自增ID可能导致的性能瓶颈，同时保证了数据一致性。
六、主键与索引的协同设计
主键本身就是一种索引，但合理的索引设计能极大提升查询性能。看一个电商平台的例子：
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT NOT NULL,
brand_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2),
INDEX idx_category_brand (category_id, brand_id), -- 联合索引
INDEX idx_name (name) -- 名称索引
);
-- 高效查询：命中联合索引
SELECT * FROM products
WHERE category_id = 5 AND brand_id = 10;
-- 低效查询：无法使用联合索引
SELECT * FROM products
WHERE brand_id = 10; -- 只有category_id在前时才能用联合索引
这个例子告诉我们，联合索引的字段顺序至关重要，应该把选择性高的字段放在前面。
七、主键设计的最佳实践
经过前面的讨论，我们总结出以下最佳实践：
- 单机系统优先考虑自增ID，分布式系统考虑雪花算法
- 业务键作为主键的前提是不可变且唯一
- 复合主键要考虑最常用的查询模式
- 主键字段类型尽量小，INT比BIGINT更省空间
- 避免使用字符串作为主键，除非有特殊需求
最后看一个综合案例（技术栈：MySQL）：
-- 消息表设计
CREATE TABLE messages (
id BIGINT PRIMARY KEY, -- 雪花算法生成的ID
sender_id INT NOT NULL,
receiver_id INT NOT NULL,
content TEXT,
is_read BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_sender (sender_id, created_at), -- 发件箱查询
INDEX idx_receiver (receiver_id, created_at) -- 收件箱查询
);
-- 分页查询收件箱
SELECT * FROM messages
WHERE receiver_id = 1001
ORDER BY created_at DESC
LIMIT 0, 20;
这个设计考虑了分布式ID、查询模式优化和分页需求，是一个相对完善的设计方案。
评论