SQL主键索引是数据库表设计中最基础也最重要的约束之一。它不仅保证了每一行数据的唯一性，还直接决定了数据在磁盘上的物理存储顺序。很多性能问题的根源，其实都能追溯到建表时主键选得不好。这篇教程会系统讲解PRIMARY KEY的语法、底层原理和生产实践中的选型技巧。
一、主键的基本概念与创建语法
主键（PRIMARY KEY）是表中用于唯一标识每一行记录的一个或多个字段的组合。它同时具备两个特性：唯一性和非空性。也就是说，主键列的值不能重复，也不能出现NULL。一张表最多只能有一个主键，但这个主键可以由多个列共同组成，也就是所谓的复合主键。
创建主键最常见的方式是在建表语句中直接声明。不同的数据库管理系统语法略有差异，但整体结构相似。下面分别给出MySQL和SQL Server的写法。
-- MySQL 创建带自增主键的表
CREATE TABLE users (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB;
-- SQL Server 使用 IDENTITY 自增
CREATE TABLE users (
id INT IDENTITY(1,1) NOT NULL,
username NVARCHAR(50) NOT NULL,
email NVARCHAR(100) NOT NULL,
created_at DATETIME2 DEFAULT SYSDATETIME(),
CONSTRAINT PK_users PRIMARY KEY (id)
);
如果表已经存在，需要后期补加主键，可以使用ALTER TABLE语句。需要注意的是，被指定为主键的列必须已经满足唯一且非空的条件，否则语句会执行失败。
-- 为现有表添加主键 ALTER TABLE users ADD CONSTRAINT PK_users PRIMARY KEY (id); -- 删除主键约束 ALTER TABLE users DROP PRIMARY KEY; -- MySQL 写法 ALTER TABLE users DROP CONSTRAINT PK_users; -- SQL Server 写法
还有一种复合主键的写法，常用于多对多关系的中间表。比如用户与角色的关联表，用user_id加role_id的组合做主键，天然防止重复授权。
CREATE TABLE user_role (
user_id INT UNSIGNED NOT NULL,
role_id INT UNSIGNED NOT NULL,
assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, role_id)
) ENGINE=InnoDB;
二、主键索引的底层原理：聚簇索引
在InnoDB引擎中，主键不仅仅是约束，更是整张表数据的组织方式。InnoDB采用聚簇索引结构，表数据本身就是按照主键值的大小顺序存放在B+树的叶子节点上。这意味着主键的值直接决定了行的物理存储位置，这也是为什么主键又被称为聚簇索引。
这个结构带来一个重要推论：如果表没有显式定义主键，InnoDB会先尝试找一个非空的唯一索引来充当聚簇索引，如果连这个也没有，就会生成一个隐藏的内部行ID列。这个隐藏列对用户完全不可见，也无法被利用，白白浪费了主键索引的性能优势。所以在InnoDB下建表，显式声明主键几乎是强制性的最佳实践。
聚簇索引对范围查询非常友好。比如主键为自增id，执行WHERE id BETWEEN 100 AND 200这样的查询时，这些行在物理上是连续存放的，一次顺序读取就能全部拿到，磁盘IO极少。相反，如果主键值是随机的，比如UUID，每次插入都要定位到B+树的中间某个位置，可能导致页分裂，写入性能明显下降。
二级索引（普通索引）的叶子节点存储的不是行的物理地址，而是主键值。查询时如果走二级索引，拿到主键后还要回到聚簇索引中再查一次完整行数据，这个动作叫做回表。主键越短，二级索引就越小，整体内存利用率越高。这就是为什么推荐主键用INT或BIGINT而不是长字符串的核心原因之一。
三、自增主键、UUID主键与业务主键的对比选型
自增主键是最常用的方案。它的优点是值连续递增，新行永远追加到B+树的最右侧，不会引发页分裂，写入吞吐量高且稳定。缺点是在分布式环境下生成困难，并且暴露在URL中容易被猜测出业务规模。
UUID主键天生支持分布式生成，全局唯一，不依赖数据库。但标准的UUID是36个字符的字符串，占用空间大，且值完全无序，会导致频繁的页分裂和碎片，插入性能可能比自增主键低数倍。如果确实需要分布式ID，可以考虑雪花算法（Snowflake），它生成的64位整数既全局唯一又趋势递增，兼顾了两者的优点。
| 方案 | 空间占用 | 写入性能 | 分布式支持 | 安全性 |
|---|---|---|---|---|
| 自增INT/BIGINT | 小 | 高 | 差 | 易被猜测 |
| UUID字符串 | 大 | 低 | 好 | 较好 |
| 雪花算法ID | 中 | 较高 | 好 | 较好 |
至于业务主键，比如手机号、身份证号、订单号，直接拿来做主键要非常谨慎。业务字段往往会变化，而主键一旦被二级索引引用，修改的代价极高。更稳妥的做法是保留一个无业务含义的技术主键，同时给业务字段建唯一索引。这样既保证了查询效率，也保留了业务上的唯一性校验。
四、常见报错与注意事项
实际使用中，主键相关的报错主要有三类。第一类是重复值冲突，比如Duplicate entry '1' for key 'PRIMARY'，说明插入的值已经存在，需要检查业务逻辑或改成INSERT IGNORE、ON DUPLICATE KEY UPDATE等写法。第二类是主键列不允许NULL，插入时报Column 'id' cannot be null。第三类是修改主键时报外键依赖错误，因为其他表的外键引用了这个主键，必须先处理子表。
还有几个细节容易被忽略。第一，复合主键的顺序很重要，查询条件如果只包含第二个字段是无法直接利用主键索引的，需要遵循最左前缀原则。第二，在MySQL中，AUTO_INCREMENT列必须是索引的一部分，且通常就是主键本身。第三，删除大量数据后主键值存在空洞，这是正常现象，不要试图手动重排主键值，尤其是已经被外部系统引用的情况下，风险极大。
-- 插入冲突时自动更新的写法（MySQL） INSERT INTO users (id, username, email) VALUES (1, 'tom', 'tom@ipipp.com') ON DUPLICATE KEY UPDATE username = VALUES(username), email = VALUES(email); -- 查看表的主键定义 SHOW INDEX FROM users WHERE Key_name = 'PRIMARY';
总结一下，主键设计要在建表阶段就想清楚：优先使用无业务含义的短整型自增主键，分布式场景选择趋势递增的分布式ID，避免用长随机字符串或易变的业务字段做主键。好的主键设计能让表在数据量增长后依然保持稳定的写入和查询性能，而糟糕的主键往往要到几千万行数据时才暴露问题，那时改造成本已经很高了。
SQL主键索引PRIMARY KEY数据库索引优化修改时间：2026-09-14 03:48:42