1. 唯一性约束

• 主键索引：
  • 强制唯一且不允许 NULL 值（隐式 NOT NULL 约束）。
  • 每个表至多有一个主键，若未显式定义，InnoDB会自动生成隐藏的6字节ROW_ID作为聚集索引。
• 唯一索引：
  • 允许存在多个 NULL 值（MySQL特性，与SQL标准不同，其他数据库如Oracle/SQL Server仅允许单个NULL）。
  • 可创建多个唯一索引，适用于多维度业务约束（如用户表的 email + phone + national_id）。

2. 索引类型

• 主键索引：
  • 自动成为聚集索引（Clustered Index），直接决定数据行的物理存储顺序。
  • 若主键为自增整数，插入时数据按顺序追加，避免页分裂；若为随机值（如UUID），可能导致存储碎片化。
• 唯一索引：
  • 默认为非聚集索引（Secondary Index），索引结构中存储索引列值 + 主键值，查询时需回表。
  • 特殊场景：若表中无主键，则选择第一个UNIQUE NOT NULL索引作为聚集索引。

3. 必要性

• 主键索引：
  • 是关系型数据库的核心设计要素，用于实现数据关联（如外键）、分区表、行级锁等高级功能。
  • 无显式主键的表可能引发隐式ROW_ID竞争（如组提交时影响InnoDB事务性能）。
• 唯一索引：
  • 是数据完整性的补充手段，非必需但强烈建议为业务关键字段添加（如防重校验）。

4. 性能与维护

• 主键索引：
  • 插入性能：自增主键最优（顺序写入），随机主键可能导致高达30%的性能下降。
  • 查询性能：作为聚集索引，主键查询无需回表，范围查询效率极高（物理连续存储）。
• 唯一索引：
  • 查询性能：等值查询效率与主键相当，但范围查询需多次回表。
  • 更新代价：若唯一索引列频繁修改（如用户手机号），需同步更新索引树，建议评估业务变更频率。

5. 设计场景

• 主键索引：
  • 技术主键：优先选择BIGINT UNSIGNED AUTO_INCREMENT，无业务意义且紧凑。
  • 自然主键：仅在绝对稳定且不可变的业务字段使用（如身份证号，但需法律允许）。
• 唯一索引：
  • 单列唯一：用户邮箱、手机号、身份证号等。
  • 联合唯一：多字段组合唯一约束（如订单ID + 商品SKU）。

总结对比表

高阶设计建议

1. 主键设计原则

   • 绝对稳定：创建后永不修改，避免触发外键连锁更新。
   • 短小有序：自增整型主键可减少索引层级，提升范围查询性能。
   • 禁用业务字段：如订单号可能因规则变化导致主键失效。

2. 唯一索引陷阱

   • NULL值处理：WHERE email = NULL 无法命中索引，需用IS NULL。
   • 联合唯一索引：字段顺序影响最左前缀匹配，如UNIQUE(a,b)可支持a=?查询，但无法单独用b=?。

3. 性能调优

   • 覆盖索引：通过INCLUDE或联合索引减少回表，如SELECT user_id FROM users WHERE email='xxx'可利用唯一索引直接返回。
   • 索引选择性：唯一索引选择性为1，适合Cardinality高的列，非唯一索引需评估区分度。

实战示例

-- 显式定义自增主键
CREATE TABLE users (
    user_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 聚集索引
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    UNIQUE KEY uk_email (email),      -- 唯一索引
    UNIQUE KEY uk_phone (phone)       -- 允许phone为NULL且存在多条NULL
);

-- 外键引用唯一索引
CREATE TABLE orders (
    order_id VARCHAR(32) PRIMARY KEY,  -- 自然主键（需确保绝对唯一）
    user_id BIGINT UNSIGNED,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

最终结论：主键是数据组织的基石，唯一索引是业务规则的守护者。设计时应严格区分两者用途，主键追求极致稳定和高效，唯一索引则需贴合业务防重需求。