> ## Content Index
> Fetch the complete content index at: https://blog.vercanti.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# 数据库设计规范
- URL: https://blog.vercanti.com/shu-ju-ku-she-ji-gui-fan/
- Published: 2026-08-28T14:35:39.000Z
- Updated: 2026-08-28T14:59:17.000Z
- Description: 相关文档：MySQL基础完全指南(/mysql-ji-chu-wan-quan-zhi-nan/) | MySQL高级优化(/mysql-gao-ji-you-hua/) | Redis完全指南(/redis-wan-quan-zhi-nan/) 定义：表中每一列都是不可再分的原子值，不允许多值列或嵌套结构。 违反示例： 修正：将多值列拆为单独的行（order_items 表）。 坏处：查询、更新单个值需要字符串解析；无法建立外键约束；无法索引。 定义：在满足 1NF 的基础上，非主键列必须完全依赖于整个主键，不允许部分依赖（仅针对复合主键）。 违反示例
- Author: yellowdog
- Tags: 数据库

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

相关文档：[MySQL基础完全指南](https://blog.vercanti.com/mysql-ji-chu-wan-quan-zhi-nan/) | [MySQL高级优化](https://blog.vercanti.com/mysql-gao-ji-you-hua/) | [Redis完全指南](https://blog.vercanti.com/redis-wan-quan-zhi-nan/)

---

## 一、范式

### 第一范式（1NF）

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

违反示例：

| order\_id | products   |
| --------- | ---------- |
| 1         | 苹果, 香蕉, 橙子 |

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

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

### 第二范式（2NF）

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

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

| order\_id | product\_id | product\_name | quantity |
| --------- | ----------- | ------------- | -------- |
| 1         | 101         | 苹果            | 3        |

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

修正：将 `product_name` 移入 `products` 表，order\_items 表只保留 `quantity`。

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

### 第三范式（3NF）

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

违反示例：

| employee\_id | dept\_id | dept\_name |
| ------------ | -------- | ---------- |
| 1            | 10       | 研发部        |

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

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

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

### BCNF（Boyce-Codd 范式）

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

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

### 反范式化（适当冗余）

| 场景             | 做法                            | 权衡                   |
| -------------- | ----------------------------- | -------------------- |
| 频繁聚合查询（如订单总金额） | 在 orders 表冗余 total\_amount 字段 | 写入时多维护一个字段，避免实时 SUM  |
| 跨表 JOIN 性能瓶颈   | 冗余常用字段（如 orders.user\_name）   | 用户改名时需同步更新冗余列        |
| 统计计数           | 冗余计数列（如 posts.comment\_count） | 计数不精确时用定时任务校准        |
| 归档表            | 完整记录快照（如 order\_snapshots）    | 不依赖其他表的最新状态，保证历史数据准确 |

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

---

## 二、命名规范

### 通用规则

| 规则                | 说明                              | 示例                             |
| ----------------- | ------------------------------- | ------------------------------ |
| 蛇形命名（snake\_case） | 全小写，单词间用下划线                     | user\_profile、created\_at      |
| 不使用 MySQL 保留字     | 避免 order、group、key、desc、table 等 | 用 orders、user\_group           |
| 语义清晰，不缩写          | 除非约定俗成（如 id、url、ip）             | user\_id 而非 uid（可商议）           |
| 表名用复数名词           | 表示一类数据的集合                       | users、orders、products          |
| 字段名不重复表名          | 避免冗余                            | users.name 而非 users.user\_name |
| 布尔字段加 is\_ 前缀     | 明确语义                            | is\_deleted、is\_active         |

### 索引命名约定

| 类型   | 命名格式                   | 示例                            |
| ---- | ---------------------- | ----------------------------- |
| 主键   | pk\_表名 或直接 PRIMARY KEY | pk\_users                     |
| 唯一索引 | uk\_表名\_字段名            | uk\_users\_email              |
| 普通索引 | idx\_表名\_字段名           | idx\_orders\_user\_id         |
| 联合索引 | idx\_表名\_字段1\_字段2      | idx\_orders\_user\_id\_status |
| 全文索引 | ft\_表名\_字段名            | ft\_articles\_content         |

### 外键命名约定

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

```

---

## 三、字段设计规范

### 数据类型选择原则

| 场景        | 推荐类型                           | 不推荐                            | 原因                                                    |
| --------- | ------------------------------ | ------------------------------ | ----------------------------------------------------- |
| 主键 ID     | INT UNSIGNED 或 BIGINT UNSIGNED | VARCHAR                        | 整型比较更快，存储空间小                                          |
| 计数、年龄等小整数 | TINYINT / SMALLINT             | INT                            | 节省存储，适当范围内够用                                          |
| 可变长字符串    | VARCHAR(N)                     | CHAR(N)（长度不固定时）                | VARCHAR 只占实际长度；CHAR 定长，适合固定长度如 MD5、手机号                |
| 长文本内容     | TEXT                           | VARCHAR(65535)                 | TEXT 存储在溢出页，不影响行大小限制                                  |
| 金额、精确小数   | DECIMAL(M, D)                  | FLOAT、DOUBLE                   | 浮点数有精度误差，金融场景绝对不用                                     |
| 状态、枚举     | TINYINT                        | VARCHAR（如 'active'、'inactive'） | 整型存储小、比较快、索引效率高                                       |
| 日期时间      | DATETIME                       | TIMESTAMP（跨时区场景）               | TIMESTAMP 范围到 2038 年，受时区影响；DATETIME 存储本地时间，范围到 9999 年 |
| 仅需日期      | DATE                           | DATETIME                       | 少存储 3 字节                                              |
| IP 地址     | INT UNSIGNED \+ INET\_ATON()   | VARCHAR(15)                    | 整型存储节省空间，范围查询更快                                       |
| 布尔值       | TINYINT(1)                     | CHAR(1) 或 VARCHAR(5)           | MySQL 无原生 BOOLEAN 类型，用 TINYINT(1) 模拟                  |

### DATETIME vs TIMESTAMP 对比

| 维度   | DATETIME                         | TIMESTAMP                |
| ---- | -------------------------------- | ------------------------ |
| 存储大小 | 8 字节                             | 4 字节                     |
| 时区处理 | 不转换，存什么取什么                       | 存储 UTC，取出时转为当前时区         |
| 范围   | 1000-01-01 \~ 9999-12-31         | 1970-01-01 \~ 2038-01-19 |
| 自动更新 | 需手动指定 DEFAULT / ON UPDATE        | 支持自动更新                   |
| 推荐场景 | 业务时间（跨时区部署时用 DATETIME + 统一存 UTC） | 内部时间戳（单时区系统）             |

### 金额字段规范

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

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

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

```

### 必备字段规范

```sql
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 的原因

| 原因       | 说明                                               |
| -------- | ------------------------------------------------ |
| 聚合函数行为变化 | COUNT(col) 会忽略 NULL；SUM、AVG 也忽略 NULL，可能得到意料之外的结果 |
| 索引效率低    | NULL 值不存入普通索引（IS NULL 查询需要特殊处理）                  |
| 比较运算结果异常 | NULL != 1 结果是 NULL（不是 TRUE），导致 NOT IN 等逻辑出错      |
| 存储开销     | 每列 NULL 标记需要额外的 NULL 位图存储                        |
| 应用层处理复杂  | 需要显式判断 null，容易漏判                                 |

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

```

---

## 四、索引设计原则

### 应该加索引的字段

| 场景                     | 原因                      |
| ---------------------- | ----------------------- |
| WHERE 条件频繁出现的列         | 避免全表扫描                  |
| JOIN ON 的关联字段          | 被驱动表无索引会导致嵌套循环全扫        |
| ORDER BY / GROUP BY 的列 | 避免 filesort 和 temporary |
| 区分度高的列（选择性 > 0.1）      | 低区分度（如性别）索引效果差          |
| 频繁范围查询的列               | BETWEEN、\>、< 等          |

### 索引数量控制

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

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

### 复合索引设计原则

```sql
-- 示例：订单表查询场景
-- 查询：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） | 可利用 change buffer 延迟合并，写入更快 |
| 适用场景 | 业务唯一约束（如邮箱、手机号）              | 非唯一的查询优化                    |

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

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

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

```

---

## 五、表设计模式

### 大表拆分

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

```sql
-- 原始宽表（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 万行时考虑水平分表。

| 分区键类型    | 示例                               | 优点       | 缺点          |
| -------- | -------------------------------- | -------- | ----------- |
| 时间（月/年）  | orders\_2026\_01                 | 历史数据易归档  | 跨月查询需 UNION |
| 用户 ID 取模 | orders\_mod\_0 \~ orders\_mod\_7 | 数据均匀分布   | 扩容需要数据迁移    |
| 地区/业务线   | orders\_cn、orders\_us            | 物理隔离     | 全局查询复杂      |
| 一致性哈希    | —                                | 扩容时迁移数据少 | 实现复杂，通常用中间件 |

水平分表要点：

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

### 中间表处理多对多关系

```sql
-- 学生选课：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` 主键，便于关联其他表（如成绩记录关联到选课记录）。

### 树形结构存储方案对比

| 方案   | 结构                                             | 查询子树                      | 查询路径                 | 插入      | 移动节点          | 适用场景                      |
| ---- | ---------------------------------------------- | ------------------------- | -------------------- | ------- | ------------- | ------------------------- |
| 邻接表  | parent\_id                                     | 递归查询（CTE）                 | 递归查询                 | 简单      | 简单            | 层级不深（< 5 层），MySQL 8.0 CTE |
| 路径枚举 | path VARCHAR，如 /1/3/7/                         | LIKE '/1/3/%'             | 字符串截取                | 简单      | 需更新所有子节点 path | 层级固定，读多写少                 |
| 嵌套集合 | lft / rgt 整数                                   | WHERE lft > x AND rgt < y | 简单                   | 需更新大量节点 | 复杂，重建大量节点     | 读极多、几乎不修改的树               |
| 闭包表  | 单独的 tree\_paths(ancestor, descendant, depth) 表 | WHERE ancestor = x        | WHERE descendant = x | 插入多行    | 删除旧路径，插入新路径   | 通用，读写均衡                   |

**邻接表**（最常用）：

```sql
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;

```

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

```sql
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;

```

---

## 六、最佳实践

### 建表模板

```sql
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='表说明';

```

### 字符集统一

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

```

---

## 七、踩坑与注意事项

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

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

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

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

```

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

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

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

```sql
-- 坏做法：用 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);

```

### 自增主键用完的处理

| 类型              | 最大值                        | 用完后的行为                                    |
| --------------- | -------------------------- | ----------------------------------------- |
| INT UNSIGNED    | 4,294,967,295（约 42 亿）      | INSERT 报错：Duplicate entry for key PRIMARY |
| BIGINT UNSIGNED | 18,446,744,073,709,551,615 | 几乎不可能用完                                   |

预防和处理：

```sql
-- 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 不一定成功，需测试

```

### 其他常见坑

```sql
-- 坑 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。

```sql
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

```

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

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

```

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

```sql
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 的字符串列会导致索引效率下降并增加应用层判断复杂度。

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

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

```

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

```sql
-- 方案 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。

```sql
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）的引用行加共享锁进行校验。并发写入同一用户的订单时，多个事务争抢同一行的共享锁，产生排队。

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

```sql
-- 去掉外键，改为应用层保证一致性
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），或将常用查询字段提取为独立列加索引。

```sql
-- 方案 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 减少影响）。

```sql
-- 监控接近上限的表
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基础完全指南](https://blog.vercanti.com/mysql-ji-chu-wan-quan-zhi-nan/)
- [MySQL高级优化](https://blog.vercanti.com/mysql-gao-ji-you-hua/)
- [PostgreSQL完全指南](https://blog.vercanti.com/postgresql-wan-quan-zhi-nan/)
- [Redis完全指南](https://blog.vercanti.com/redis-wan-quan-zhi-nan/)