MySQL 主键与外键设计

主键设计

主键的作用

主键是表中用于唯一标识每行数据的列或列组合，它的主要作用包括：

唯一标识表中的每一行数据
作为表与表之间关联的基础
加速数据查询和检索
确保数据完整性

主键的设计原则

1. 唯一性

主键值必须唯一，不能重复
主键列不能包含 NULL 值

2. 稳定性

主键值应保持稳定，不要频繁更改
避免使用业务字段作为主键，因为业务字段可能会发生变化

3. 简单性

主键应尽可能简单，最好使用单个列
避免使用复杂的组合主键

4. 高性能

主键应具有良好的性能，便于快速查询和索引
建议使用整数类型作为主键，因为整数类型的索引效率更高

5. 自增性

建议使用自增主键，便于插入操作
自增主键可以减少页分裂，提高插入性能

主键的类型选择

1. 整数类型

INT：4字节，范围为 -2^31 到 2^31-1
BIGINT：8字节，范围为 -2^63 到 2^63-1
TINYINT：1字节，范围为 -128 到 127
SMALLINT：2字节，范围为 -32768 到 32767
MEDIUMINT：3字节，范围为 -8388608 到 8388607

优势：

存储效率高
索引效率高
插入性能好

建议：

对于中小型表，使用 INT 类型
对于大型表或预计会快速增长的表，使用 BIGINT 类型

2. 字符串类型

VARCHAR：可变长度字符串
CHAR：固定长度字符串
UUID：全局唯一标识符

优势：

可以在分布式系统中保证唯一性
不需要集中式的ID生成器

劣势：

存储效率低
索引效率低
插入性能差
会导致频繁的页分裂

建议：

尽量避免使用字符串类型作为主键
如果必须使用，建议使用 UUID 的二进制形式（BINARY(16)）

3. 复合主键

由多个列组合而成的主键。

优势：

可以唯一标识数据行
适用于多对多关系表

劣势：

索引效率低
插入性能差
维护成本高

建议：

尽量避免使用复合主键
对于多对多关系表，可以使用复合主键

自增主键的优化

1. 设置合适的自增步长

auto_increment_increment：自增步长
auto_increment_offset：自增起始值

配置示例：

ini

# 设置自增步长为 2
auto_increment_increment = 2

# 设置自增起始值为 1
auto_increment_offset = 1

2. 避免自增主键冲突

在主从复制环境中，避免自增主键冲突的方法包括：

为每个主库设置不同的自增步长和起始值
使用 GTID 复制
避免在从库上执行写入操作

3. 处理自增主键溢出

定期监控自增主键的使用情况
对于即将溢出的表，考虑使用 BIGINT 类型替换 INT 类型
对于历史表，可以考虑归档或分区

主键的最佳实践

使用自增整数作为主键
避免使用业务字段作为主键
避免使用复合主键
为大型表使用 BIGINT 类型
定期监控自增主键的使用情况

外键设计

外键的作用

外键是表中用于关联其他表主键的列，它的主要作用包括：

建立表与表之间的关联关系
确保数据完整性
防止无效数据的插入
自动处理级联操作

外键的设计原则

1. 引用完整性

外键必须引用另一个表的主键或唯一键
外键值必须存在于被引用表中

2. 简单性

外键应尽可能简单，最好使用单个列
避免使用复杂的组合外键

3. 性能考虑

外键会增加插入、更新和删除操作的开销
外键会增加死锁的风险
外键会影响查询优化

4. 级联操作

合理设置级联操作，避免意外删除或更新数据
建议明确指定级联操作，而不是使用默认值

外键的级联操作

1. CASCADE

当被引用表的主键值被更新或删除时，自动更新或删除引用表中对应的外键值
ON DELETE CASCADE：删除被引用表中的行时，自动删除引用表中对应的行
ON UPDATE CASCADE：更新被引用表中的主键值时，自动更新引用表中对应的外键值

2. SET NULL

当被引用表的主键值被更新或删除时，将引用表中对应的外键值设置为 NULL
ON DELETE SET NULL：删除被引用表中的行时，将引用表中对应的外键值设置为 NULL
ON UPDATE SET NULL：更新被引用表中的主键值时，将引用表中对应的外键值设置为 NULL

3. SET DEFAULT

当被引用表的主键值被更新或删除时，将引用表中对应的外键值设置为默认值
ON DELETE SET DEFAULT：删除被引用表中的行时，将引用表中对应的外键值设置为默认值
ON UPDATE SET DEFAULT：更新被引用表中的主键值时，将引用表中对应的外键值设置为默认值

4. RESTRICT

当被引用表的主键值被更新或删除时，如果引用表中存在对应的外键值，则拒绝执行操作
ON DELETE RESTRICT：删除被引用表中的行时，如果引用表中存在对应的外键值，则拒绝删除
ON UPDATE RESTRICT：更新被引用表中的主键值时，如果引用表中存在对应的外键值，则拒绝更新

5. NO ACTION

与 RESTRICT 类似，当被引用表的主键值被更新或删除时，如果引用表中存在对应的外键值，则拒绝执行操作
在不同的数据库系统中，NO ACTION 和 RESTRICT 可能有细微差别

外键的性能影响

1. 插入操作

插入引用表中的行时，需要检查外键约束
会增加插入操作的开销

2. 更新操作

更新被引用表中的主键值时，需要检查外键约束
如果设置了级联操作，会触发额外的更新操作

3. 删除操作

删除被引用表中的行时，需要检查外键约束
如果设置了级联操作，会触发额外的删除操作

4. 查询操作

外键可以帮助优化查询，特别是在连接查询中
但外键约束也会增加查询优化的复杂度

外键的最佳实践

只在必要时使用外键
合理设置级联操作
避免在高并发表上使用外键
定期检查外键约束的完整性
考虑使用应用程序级别的外键约束替代数据库级别的外键约束

主键与外键的索引优化

1. 主键索引

主键会自动创建唯一索引
主键索引是聚簇索引，数据行按照主键顺序存储
主键索引的查询效率非常高

2. 外键索引

外键不会自动创建索引
建议为外键列创建索引，以提高查询效率
外键索引可以减少死锁的风险

创建外键索引的示例：

sql

-- 创建表时为外键列创建索引
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id)
);

-- 为已存在的表添加外键索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

3. 联合索引

对于频繁一起查询的列，可以创建联合索引
联合索引的顺序应遵循最左前缀原则

创建联合索引的示例：

sql

-- 创建联合索引
CREATE INDEX idx_user_order_date ON orders (user_id, order_date);

主键与外键的设计案例

1. 电商系统的表设计

sql

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
);

-- 订单商品表
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
);

2. 博客系统的表设计

sql

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 分类表
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 文章表
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    content TEXT NOT NULL,
    user_id INT NOT NULL,
    category_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT,
    INDEX idx_user_id (user_id),
    INDEX idx_category_id (category_id),
    INDEX idx_created_at (created_at)
);

-- 评论表
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT NOT NULL,
    user_id INT NOT NULL,
    article_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_article_id (article_id)
);

不同版本的主键与外键差异

MySQL 5.5 及之前

支持外键约束
外键约束只能在 InnoDB 存储引擎中使用
不支持在线添加外键约束

MySQL 5.6

支持在线添加外键约束
增强了外键约束的性能
支持更多的外键约束选项

MySQL 5.7

进一步增强了外键约束的性能
支持外键约束的延迟检查
支持外键约束的级联操作优化

MySQL 8.0

增强了外键约束的错误信息
支持外键约束的并行检查
支持外键约束的在线修改

主键与外键的常见问题

1. 外键约束失败

原因：

引用的主键值不存在
违反了外键约束的级联操作规则
外键列的数据类型与被引用列的数据类型不匹配

解决方案：

检查引用的主键值是否存在
检查外键约束的级联操作规则
确保外键列的数据类型与被引用列的数据类型匹配

2. 外键导致的死锁

原因：

并发操作中，不同事务以不同的顺序锁定表
外键约束导致的额外锁定

解决方案：

为外键列创建索引
确保所有事务以相同的顺序锁定表
考虑使用应用程序级别的外键约束替代数据库级别的外键约束

3. 自增主键冲突

原因：

在主从复制环境中，多个主库生成相同的自增主键
在从库上执行写入操作

解决方案：

为每个主库设置不同的自增步长和起始值
使用 GTID 复制
避免在从库上执行写入操作

常见问题（FAQ）

Q1: 应该使用自增主键还是UUID作为主键？

A1: 对于大多数应用场景，建议使用自增主键，因为：

自增主键的存储效率高
自增主键的索引效率高
自增主键的插入性能好
自增主键可以减少页分裂

如果需要在分布式系统中保证唯一性，可以考虑使用UUID，但建议使用UUID的二进制形式（BINARY(16)），并将其转换为字符串形式存储和使用。

Q2: 为什么外键不会自动创建索引？

A2: 外键不会自动创建索引的原因包括：

外键约束的主要作用是确保数据完整性，而不是提高查询效率
创建索引会增加存储和维护成本
并不是所有外键都需要频繁查询

建议为频繁查询的外键列创建索引，以提高查询效率和减少死锁的风险。

Q3: 如何处理外键约束导致的性能问题？

A3: 处理外键约束导致的性能问题的方法包括：

只在必要时使用外键
合理设置级联操作
为外键列创建索引
避免在高并发表上使用外键
考虑使用应用程序级别的外键约束替代数据库级别的外键约束

Q4: 如何在线添加外键约束？

A4: 在MySQL 5.6及以上版本中，可以使用以下语句在线添加外键约束：

sql

ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

在添加外键约束时，建议为外键列创建索引，以提高查询效率和减少死锁的风险。

Q5: 如何禁用和启用外键约束？

A5: 可以使用以下语句禁用和启用外键约束：

sql

-- 禁用外键约束
SET FOREIGN_KEY_CHECKS = 0;

-- 启用外键约束
SET FOREIGN_KEY_CHECKS = 1;

注意：禁用外键约束可能会导致数据不一致，建议只在必要时使用，并在操作完成后立即启用外键约束。

Q6: 如何检查外键约束的完整性？

A6: 可以使用以下语句检查外键约束的完整性：

sql

-- 检查所有表的外键约束
CHECK TABLE table_name;

-- 检查数据库中所有表的外键约束
mysqlcheck -u username -p database_name --check

Q7: 如何删除外键约束？

A7: 可以使用以下语句删除外键约束：

sql

-- 查看外键约束的名称
SHOW CREATE TABLE table_name;

-- 删除外键约束
ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name;

在删除外键约束后，建议考虑是否需要保留外键列的索引。

MySQL 主键与外键设计 ​

主键设计 ​

主键的作用 ​

主键的设计原则 ​

1. 唯一性 ​

2. 稳定性 ​

3. 简单性 ​

4. 高性能 ​

5. 自增性 ​

主键的类型选择 ​

1. 整数类型 ​

2. 字符串类型 ​

3. 复合主键 ​

自增主键的优化 ​

1. 设置合适的自增步长 ​

2. 避免自增主键冲突 ​

3. 处理自增主键溢出 ​

主键的最佳实践 ​

外键设计 ​

外键的作用 ​

外键的设计原则 ​

1. 引用完整性 ​

2. 简单性 ​

3. 性能考虑 ​

4. 级联操作 ​

外键的级联操作 ​

1. CASCADE ​

2. SET NULL ​

3. SET DEFAULT ​

4. RESTRICT ​

5. NO ACTION ​

外键的性能影响 ​

1. 插入操作 ​

2. 更新操作 ​

3. 删除操作 ​

4. 查询操作 ​

外键的最佳实践 ​

主键与外键的索引优化 ​

1. 主键索引 ​

2. 外键索引 ​

3. 联合索引 ​

主键与外键的设计案例 ​

1. 电商系统的表设计 ​

2. 博客系统的表设计 ​

不同版本的主键与外键差异 ​

MySQL 5.5 及之前 ​

MySQL 5.6 ​

MySQL 5.7 ​

MySQL 8.0 ​

主键与外键的常见问题 ​

1. 外键约束失败 ​

2. 外键导致的死锁 ​

3. 自增主键冲突 ​

MySQL 主键与外键设计

主键设计

主键的作用

主键的设计原则

1. 唯一性

2. 稳定性

3. 简单性

4. 高性能

5. 自增性

主键的类型选择

1. 整数类型

2. 字符串类型

3. 复合主键

自增主键的优化

1. 设置合适的自增步长

2. 避免自增主键冲突

3. 处理自增主键溢出

主键的最佳实践

外键设计

外键的作用

外键的设计原则

1. 引用完整性

2. 简单性

3. 性能考虑

4. 级联操作

外键的级联操作

1. CASCADE

2. SET NULL

3. SET DEFAULT

4. RESTRICT

5. NO ACTION

外键的性能影响

1. 插入操作

2. 更新操作

3. 删除操作

4. 查询操作

外键的最佳实践

主键与外键的索引优化

1. 主键索引

2. 外键索引

3. 联合索引

主键与外键的设计案例

1. 电商系统的表设计

2. 博客系统的表设计

不同版本的主键与外键差异

MySQL 5.5 及之前

MySQL 5.6

MySQL 5.7

MySQL 8.0

主键与外键的常见问题

1. 外键约束失败

2. 外键导致的死锁

3. 自增主键冲突