MYSQL-主键（Primary Key）

作者介绍：简历上没有一个精通的运维工程师，下面的思维导图也是预计更新的内容和当前进度(不定时更新)。

数据库是一个系统（应用）最重要的资产之一，所以我们的数据库将从以下几个数据库来进行介绍。

MySQL（本章节）

PostgreSQL

MongoDB

Redis

Etcd

主键是数据库表设计中最为重要的概念之一，它远不止是“唯一标识符”那么简单，尤其是在 MySQL 默认的 InnoDB 存储引擎中。

一、主键是什么？

主键是数据库表中的一个或多个字段（列），它的值用于唯一地标识表中的某一条记录。

打个比方：身份证号是中国公民在主表“人口数据库”中的主键，每个人的身份证号都是唯一且不为空的，通过它可以精准地定位到一个人。

二、主键的核心特性

1. 唯一性 (Unique)，主键值在整个表中必须是唯一的，任意两行都不能拥有相同的主键值。
2. 非空性 (Not Null)，主键字段不能为空（NULL）。只要定义了主键，数据库就会强制要求插入或更新的记录必须为该字段提供一个值。
3. 唯一标识 (Identifies One Row)，主键的唯一目的是唯一地标识一条记录。一个主键值对应且只对应一行数据。

三、为什么主键如此重要？（作用与好处）

1. 保证数据完整性：唯一性和非空性约束确保了表中每一行数据都可以被唯一、有效地标识，避免了重复数据和脏数据的产生。
2. 作为记录的默认访问路径：
   • 数据行实际上是存储在主键索引的叶子节点上的。
   • 表的数据物理存储顺序与主键顺序大致相同（按主键顺序插入效率最高）。
   • 这种索引被称为聚簇索引（Clustered Index）。
   • 这是 InnoDB 引擎下主键最关键的作用。在 InnoDB 中，表数据本身就是基于主键索引组织的一棵 B+Tree。这意味着：
3. 提高查询性能：
   • 因为数据就存储在主键索引上，所以通过主键进行查询是最快的访问方式，通常只需要 1-3 次磁盘 I/O 就能定位到数据行。
4. 作为外键参照的基础：
   • 其他表可以通过外键（Foreign Key）来引用本表的主键，从而建立表与表之间的关联关系，保证数据的一致性和参照完整性。

四、InnoDB 存储引擎下主键的特殊性

理解 InnoDB 的聚簇索引结构是理解主键的关键。

• 如果表定义了主键：InnoDB 就使用这个主键来作为聚簇索引。
• 如果表没有定义主键：InnoDB 会选择一个第一个所有列都是非空的唯一索引（UNIQUE INDEX） 来作为聚簇索引。
• 如果既没有主键，也没有合适的唯一索引：InnoDB 会在内部自动生成一个隐藏的、名为 GEN_CLUST_INDEX 的 6 字节的行 ID（rowid）作为聚簇索引。这个行 ID 是单调递增的。

重要结论：在 InnoDB 中，每个表都必须有且仅有一个聚簇索引，而主键就是默认的聚簇索引。

五、主键的设计策略与最佳实践

1. 代理主键 (Surrogate Key) vs 自然主键 (Natural Key)

• 自然主键：使用具有业务含义的字段作为主键（如：身份证号、手机号、邮箱）。
  • 优点：避免新增一张表来存储关系。
  • 缺点：如果业务规则发生变化（如身份证号升位），修改主键的代价极高（会导致所有相关外键都要更新）。长度可能较长，影响二级索引性能。
• 代理主键：使用一个与业务无关的、无意义的字段作为主键（如：自增整数、UUID）。
  • 优点：稳定，业务逻辑变化不影响主键。通常是简单的整数，性能极佳。
  • 缺点：需要额外的字段，有时需要多表连接才能表达业务含义。

现代数据库设计实践中，绝大多数推荐使用代理主键，尤其是使用 AUTO_INCREMENT 的自增整数。

2. 主键的选择

• 推荐：自增整数 (BIGINT AUTO_INCREMENT)
  • 性能好：INT/BIGINT 类型占用空间小，比较速度快。自增特性保证了新数据总是顺序追加到当前 B+Tree 的末尾，写入效率高，几乎不会造成页分裂。
  • 简单：对应用层透明，无需关心其生成逻辑。
• 不推荐/谨慎使用：UUID
  • 缺点：
  • 如果必须使用 UUID，可以考虑有序 UUID（如 MySQL 8.0 的 UUID_TO_BIN(... , 1)）或将其作为业务键，另设一个自增 INT 作为主键。
  1. 空间大：CHAR(36) 或 BINARY(16)，比 INT(4字节)/BIGINT(8字节) 大得多。作为主键，每个二级索引的叶子节点都会存储主键值，这会显著增加索引大小。
  2. 随机性：插入新记录时，UUID 是随机的，无法保证顺序追加。这会导致 InnoDB 不得不将新行插入到现有数据页的中间位置，造成页分裂，从而影响写入性能并产生碎片。

3. 复合主键 (Composite Primary Key)

主键可以由多个字段组合而成。

• 使用场景：通常在多对多关系的中间表中使用。
  • 例如：user_role 表，有 user_id 和 role_id 两个字段，其组合可以唯一确定一条记录。PRIMARY KEY (user_id, role_id)。
• 缺点：
  • 每个二级索引都会包含所有主键列，导致索引体积庞大。
  • 在 InnoDB 中，其他表的外键引用此表时会变得复杂。

建议：除非有非常明确的理由（如上述中间表），否则尽量使用单一的代理主键。

六、相关命令

复合主键

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id) -- 指定 (order_id, product_id) 为联合主键
);

为已存在的表添加主键

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

查看主键，实际是查看表结构创建命令，就可以看到。

mysql> SHOW CREATE TABLE test_data;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                           |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_data | CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `random_number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_test_data_value` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=8437687 DEFAULT CHARSET=latin1 |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)