数据库设计规范

官方文档：https://dev.mysql.com/doc/refman/8.0/en/database-use.html
适用版本：MySQL 8.0（2026-05-07 核实）

相关文档：MySQL基础完全指南 | MySQL高级优化 | Redis完全指南

一、范式

第一范式（1NF）

定义：表中每一列都是不可再分的原子值，不允许多值列或嵌套结构。

违反示例：

修正：将多值列拆为单独的行（order_items 表）。

坏处：查询、更新单个值需要字符串解析；无法建立外键约束；无法索引。

第二范式（2NF）

定义：在满足 1NF 的基础上，非主键列必须完全依赖于整个主键，不允许部分依赖（仅针对复合主键）。

违反示例（复合主键为 order_id + product_id）：

product_name 只依赖 product_id，不依赖 order_id，违反 2NF。

修正：将 product_name 移入 products 表，order_items 表只保留 quantity。

坏处：商品改名需要批量更新 order_items；数据冗余导致不一致。

第三范式（3NF）

定义：在满足 2NF 的基础上，非主键列不依赖于其他非主键列（无传递依赖）。

违反示例：

dept_name 依赖 dept_id，dept_id 依赖 employee_id，存在传递依赖。

修正：将 dept_name 移入 departments 表，employees 表只存 dept_id。

坏处：部门改名需要扫描 employees 全表更新；数据不一致风险高。

BCNF（Boyce-Codd 范式）

定义：3NF 的加强版。对每一个非平凡函数依赖 X → Y，X 必须是超键（能唯一标识一行的键）。

违反场景较少见，多出现在多个候选键重叠的情况下。实际工程中能达到 3NF 即可。

反范式化（适当冗余）

反范式化的前提：确认存在真实的性能问题，而不是提前优化；写好数据一致性维护的逻辑（触发器、应用层、定时任务均可，选一种并文档化）。

二、命名规范

通用规则

索引命名约定

外键命名约定

-- 格式：fk_当前表名_关联表名_字段名
CONSTRAINT fk_orders_users_user_id
    FOREIGN KEY (user_id) REFERENCES users(id)

三、字段设计规范

数据类型选择原则

DATETIME vs TIMESTAMP 对比

金额字段规范

-- 正确：DECIMAL 精确小数
price DECIMAL(10, 2) NOT NULL DEFAULT '0.00' COMMENT '商品价格，单位元'

-- 或者：用分（整数）存储，避免小数
price_fen INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品价格，单位分'

-- 错误：FLOAT/DOUBLE 有精度误差
price FLOAT  -- 禁止用于金额

必备字段规范

CREATE TABLE orders (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    -- 业务字段 ...

    -- 以下字段每张业务表必须有
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    is_deleted  TINYINT(1)      NOT NULL DEFAULT 0 COMMENT '软删除标记：0正常 1已删除',
    deleted_at  DATETIME                 DEFAULT NULL COMMENT '软删除时间'
);

软删除查询时需要在所有查询中加 WHERE is_deleted = 0，建议在 ORM 层统一处理（如 SQLAlchemy 的 query_class、Tortoise ORM 的 QuerySet 子类）。参考 Tortoise-orm/README。

避免 NULL 的原因

-- 实践：用有意义的默认值代替 NULL
name    VARCHAR(100) NOT NULL DEFAULT '' COMMENT '用户名',
score   INT          NOT NULL DEFAULT 0  COMMENT '得分',
remark  TEXT                             -- 真正可选的长文本，允许 NULL

四、索引设计原则

应该加索引的字段

索引数量控制

每张表的索引不超过 5 个（包含主键）。原因：

• 每个索引占用额外存储空间
• INSERT / UPDATE / DELETE 时需要维护所有索引，写入性能随索引数量线性下降
• 索引过多，优化器选择困难

复合索引设计原则

-- 示例：订单表查询场景
-- 查询：WHERE user_id = ? AND status = ? ORDER BY created_at DESC

-- 设计原则：等值列在前，范围/排序列在后，区分度高的列在前
CREATE INDEX idx_orders_user_status_created
    ON orders (user_id, status, created_at);

-- 验证：
EXPLAIN SELECT id, created_at
FROM orders
WHERE user_id = 100 AND status = 1
ORDER BY created_at DESC
LIMIT 20;
-- 期望：type=ref, key=idx_orders_user_status_created, Extra=Using index

唯一索引 vs 普通索引的写入性能差异

change buffer（写缓冲）：针对不在 buffer pool 中的数据页的写操作，普通索引可以先写 change buffer，之后合并到磁盘，减少随机 IO。唯一索引因需即时校验唯一性，无法利用 change buffer。

-- 写入密集但不需要唯一约束的字段，使用普通索引
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- 有唯一约束要求时，使用唯一索引（并同时起到约束作用）
CREATE UNIQUE INDEX uk_users_email ON users (email);

五、表设计模式

大表拆分

垂直拆分（字段分离）：将宽表按访问频率拆分为主表和扩展表，主表保留高频字段。

-- 原始宽表（50+ 列）
CREATE TABLE users (
    id, name, email, phone,     -- 高频字段
    address, bio, avatar_url,   -- 低频字段
    last_login_ip, device_info  -- 日志类字段
);

-- 拆分后
CREATE TABLE users (            -- 主表，高频查询
    id, name, email, phone, created_at, updated_at
);

CREATE TABLE user_profiles (    -- 扩展表，按需加载
    user_id BIGINT UNSIGNED PRIMARY KEY,  -- 1:1 关系，用 user_id 作主键
    address, bio, avatar_url,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

水平分表（分区键选择）：数据量超过 5000 万行时考虑水平分表。

水平分表要点：

• 分区键一旦确定不能更改，慎重选择
• 避免跨分片的 JOIN 和事务
• 分片键必须出现在所有查询的 WHERE 条件中，否则需要广播查询

中间表处理多对多关系

-- 学生选课：students 和 courses 是多对多
CREATE TABLE student_courses (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id  BIGINT UNSIGNED NOT NULL,
    course_id   BIGINT UNSIGNED NOT NULL,
    enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    grade       DECIMAL(4, 1) DEFAULT NULL,

    UNIQUE KEY uk_student_course (student_id, course_id),  -- 防止重复选课
    INDEX idx_course_id (course_id),  -- 反向查询（某课程的所有学生）

    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id)  REFERENCES courses(id)
);

中间表通常建议有自己的 id 主键，便于关联其他表（如成绩记录关联到选课记录）。

树形结构存储方案对比

邻接表（最常用）：

CREATE TABLE categories (
    id        INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    parent_id INT UNSIGNED DEFAULT NULL,
    INDEX idx_parent_id (parent_id),
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- 递归查询（MySQL 8.0 CTE）
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories WHERE id = 1  -- 根节点
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth;

闭包表（读写均衡时推荐）：

CREATE TABLE category_paths (
    ancestor   INT UNSIGNED NOT NULL,
    descendant INT UNSIGNED NOT NULL,
    depth      TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY (ancestor, descendant),
    INDEX idx_descendant (descendant)
);

-- 查询某节点的所有子孙（含自身）
SELECT c.*
FROM categories c
JOIN category_paths cp ON c.id = cp.descendant
WHERE cp.ancestor = 3;

-- 查询某节点到根的完整路径
SELECT c.*
FROM categories c
JOIN category_paths cp ON c.id = cp.ancestor
WHERE cp.descendant = 7
ORDER BY cp.depth DESC;

六、最佳实践

建表模板

CREATE TABLE `table_name` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    -- 业务字段

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `is_deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '软删除：0正常 1已删除',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '软删除时间',

    PRIMARY KEY (`id`),
    -- 索引

    -- 外键（可选，高并发场景可不加）
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='表说明';

字符集统一

-- 全库统一使用 utf8mb4（支持 emoji 和完整 Unicode）
-- 排序规则统一使用 utf8mb4_unicode_ci（不区分大小写）
-- 或 utf8mb4_bin（区分大小写，适合密码等字段）
CREATE DATABASE mydb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

七、踩坑与注意事项

外键约束在高并发下的锁竞争

外键约束在每次 INSERT / UPDATE / DELETE 时都会对关联表加共享锁进行校验，高并发场景下会产生严重的锁竞争。

-- 问题示例：orders 表有外键指向 users
-- 高并发插入 orders 时，每次都要锁 users 表的对应行
INSERT INTO orders (user_id, ...) VALUES (100, ...);
-- 同时大量并发时，users 行上的共享锁排队严重

-- 解决方案：生产环境常见做法是不使用外键约束
-- 在应用层保证数据一致性，或使用定期校验任务
ALTER TABLE orders DROP FOREIGN KEY fk_orders_users_user_id;

如果必须使用外键，注意：

• 被引用表（父表）的主键/唯一键上必须有索引（否则锁整张父表）
• 尽量减少事务时间，快速释放锁

用字符串列存 JSON 的性能问题

-- 坏做法：用 VARCHAR/TEXT 存 JSON 字符串
meta VARCHAR(1000)  -- '{"color": "red", "size": "L"}'

-- 查询特定字段需要全表扫描 + 字符串解析，无法建索引
SELECT * FROM products WHERE meta LIKE '%"color": "red"%';  -- 极慢

-- 好做法一：MySQL 5.7+ 原生 JSON 类型
meta JSON
-- 支持路径查询，可对虚拟列建索引
SELECT * FROM products WHERE JSON_EXTRACT(meta, '$.color') = 'red';

-- 好做法二：为频繁查询的 JSON 字段提取为单独的列
color VARCHAR(50) NOT NULL DEFAULT '',
size  VARCHAR(20) NOT NULL DEFAULT '',
-- 普通列，可正常建索引

-- 好做法三：虚拟列索引（MySQL 5.7+）
ALTER TABLE products
    ADD COLUMN meta_color VARCHAR(50)
    GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(meta, '$.color'))) VIRTUAL,
    ADD INDEX idx_meta_color (meta_color);

自增主键用完的处理

预防和处理：

-- 1. 新表统一使用 BIGINT UNSIGNED，不用 INT
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT

-- 2. 监控当前自增值（接近上限时告警）
SELECT
    table_name,
    auto_increment,
    (auto_increment / 4294967295 * 100) AS usage_pct
FROM information_schema.tables
WHERE table_schema = 'your_db'
  AND data_type_of_auto_increment = 'int unsigned'  -- 概念示意
ORDER BY usage_pct DESC;

-- 实际查询
SELECT table_name, auto_increment
FROM information_schema.tables
WHERE table_schema = 'your_db'
  AND auto_increment > 3000000000;  -- 超过 30 亿触发告警

-- 3. 已经用完或快用完的 INT 表迁移到 BIGINT
-- 在线迁移（MySQL 5.6+ INPLACE，会短暂加 MDL 锁）
ALTER TABLE orders
    MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ALGORITHM=INPLACE, LOCK=NONE;
-- 注意：从 INT 改为 BIGINT 在某些版本需要重建表，LOCK=NONE 不一定成功，需测试

其他常见坑

-- 坑 1：DATETIME 字段不加索引，大表按时间范围查询极慢
-- 解决：为 created_at 加索引
CREATE INDEX idx_created_at ON orders (created_at);

-- 坑 2：批量插入不使用批量 INSERT，逐行插入极慢
-- 坏做法：循环执行 INSERT INTO orders VALUES (...)
-- 好做法：
INSERT INTO orders (user_id, amount, status)
VALUES (1, 100.00, 1), (2, 200.00, 1), (3, 50.00, 0);
-- 或使用 LOAD DATA INFILE（最快）

-- 坑 3：大量 UPDATE 不分批，锁表时间过长
-- 分批更新：每批 1000 行
UPDATE orders SET status = 2
WHERE status = 1 AND created_at < '2025-01-01'
LIMIT 1000;
-- 业务低峰期循环执行，每批间隔 100ms

-- 坑 4：字符集混用导致隐式类型转换，索引失效
-- 排查：SHOW CREATE TABLE table_name 查看各字段字符集
-- 修复：统一字符集
ALTER TABLE orders
    MODIFY COLUMN user_name VARCHAR(100)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';

最佳实践

所有表必须有主键，优先用 BIGINT UNSIGNED 自增：无主键的表在 MySQL 主从复制时会造成全表锁，且 InnoDB 内部会隐式创建一个 6 字节 RowID 列代替主键。INT UNSIGNED 上限约 42 亿，中等规模系统建议直接用 BIGINT UNSIGNED。

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

每张表必须有 created_at、updated_at：这两个字段几乎在所有业务场景下都会用到（数据同步、问题排查、缓存过期、分页排序）。不加等于每次需要时再 ALTER TABLE，代价极高。

created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

逻辑删除用 is_deleted，保留数据：物理删除（DELETE）数据无法恢复，导致主键间隙，审计困难。逻辑删除配合覆盖索引可保证查询性能不受影响。

is_deleted TINYINT(1) NOT NULL DEFAULT 0,
-- 所有查询默认加 WHERE is_deleted = 0
-- 相关索引包含 is_deleted 列
CREATE INDEX idx_users_email_active ON users(email, is_deleted);

字符串字段长度宁短勿长，NOT NULL + 默认空字符串：VARCHAR(255) 与 VARCHAR(50) 存储空间相同（只占实际字节），但影响排序 buffer 和内存临时表的分配大小。业务含义明确时用精确长度。允许 NULL 的字符串列会导致索引效率下降并增加应用层判断复杂度。

-- 推荐
name VARCHAR(100) NOT NULL DEFAULT '',

-- 避免
name VARCHAR(255) DEFAULT NULL,

金额统一用 DECIMAL(19, 4) 或整数分：浮点数（FLOAT/DOUBLE）不能精确表示十进制小数，金融场景下产生计算误差。推荐两种方案：DECIMAL(19, 4) 直接存元；或 BIGINT 存分（整数运算最快）。

-- 方案 A：DECIMAL
amount DECIMAL(19, 4) NOT NULL DEFAULT '0.0000',

-- 方案 B：整数分（100 = ¥1.00）
amount_cents BIGINT NOT NULL DEFAULT 0,

表评论（COMMENT）描述表和列的业务含义：代码可读，字段名无法替代业务语义描述。DBA 排查问题和新人 onboarding 都依赖 COMMENT。

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '订单 ID',
    user_id BIGINT UNSIGNED NOT NULL COMMENT '下单用户 ID，关联 users.id',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '订单状态：1待支付 2已支付 3已发货 4已完成 5已取消'
) COMMENT='订单主表';

常见陷阱

陷阱：外键约束在高并发下造成锁竞争

现象： 高并发插入子表（如 orders）时，数据库锁等待严重，QPS 下降，甚至死锁。

原因： 外键约束在每次写操作时都对父表（users）的引用行加共享锁进行校验。并发写入同一用户的订单时，多个事务争抢同一行的共享锁，产生排队。

解决： 互联网生产环境通常去掉外键约束，在应用层或通过定期校验任务保证数据一致性。

-- 去掉外键，改为应用层保证一致性
ALTER TABLE orders DROP FOREIGN KEY fk_orders_users_user_id;
-- 保留普通索引（用于 JOIN 查询性能）
CREATE INDEX idx_orders_user_id ON orders(user_id);

陷阱：用 VARCHAR / TEXT 存 JSON 字符串导致查询极慢

现象： 查询某个 JSON 属性值时只能 LIKE '%"color":"red"%'，无法使用索引，全表扫描。

原因： 字符串存储 JSON 让数据库无法理解其结构，无法对内部字段建立索引。

解决： 用 MySQL 8.0+ 的 JSON 类型（或 PostgreSQL 的 JSONB），或将常用查询字段提取为独立列加索引。

-- 方案 A：MySQL JSON 类型 + 虚拟列索引
meta JSON,
ADD COLUMN meta_color VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(meta, '$.color'))) VIRTUAL,
ADD INDEX idx_meta_color (meta_color);

-- 方案 B：提取高频查询字段为普通列
color VARCHAR(50) NOT NULL DEFAULT '',
INDEX idx_color (color)

陷阱：INT 主键接近上限引发插入报错

现象： 突然出现 Duplicate entry '2147483647' for key 'PRIMARY' 或类似错误，服务完全不可写入。

原因： INT UNSIGNED 最大值约 42 亿，高增长表可能在数年内耗尽。一旦耗尽，下一次 INSERT 就会报主键冲突。

解决： 新表统一用 BIGINT UNSIGNED；已有 INT 表在用量超过 30 亿时在线迁移（ALTER TABLE 可用 ALGORITHM=INPLACE 减少影响）。

-- 监控接近上限的表
SELECT table_name, auto_increment
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND auto_increment > 3000000000;

-- 在线扩容
ALTER TABLE orders
    MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ALGORITHM=INPLACE, LOCK=NONE;

参见

• MySQL基础完全指南
• MySQL高级优化
• PostgreSQL完全指南
• Redis完全指南