MySQL Schema 设计与管理

数据库 Schema 是应用系统数据模型的基础。一个设计合理的 Schema，可以让业务开发更加简单，也能降低查询性能问题、数据不一致以及后期数据库迁移的成本。

Schema 设计并不只是“建几张表、加几个字段”，而是需要综合考虑 业务模型、数据类型、约束、索引、数据增长、查询方式以及后续变更。

本文从实际项目出发，介绍 MySQL Schema 的设计方法、管理方法，并通过一个简单的业务案例说明如何从业务模型逐步设计数据库。

1. 什么是 MySQL Schema

在 MySQL 中，Schema 通常可以理解为数据库本身，例如：

CREATE DATABASE user
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

然后在 Schema 中创建表：

user
├── users
├── roles
├── permissions
├── user_roles
└── audit_logs

在实际项目中，我们通常会同时讨论两个层面的设计：

Schema 级设计：字符集、排序规则、数据库命名等
Table 级设计：表结构、字段、约束、索引、关联关系等

因此，Schema 设计本质上是在设计整个数据模型。

一个好的数据库设计通常需要解决几个问题：

业务数据如何存储？
        ↓
表与表之间是什么关系？
        ↓
字段应该使用什么类型？
        ↓
哪些数据必须保证唯一？
        ↓
哪些查询需要索引？
        ↓
数据量增长后怎么办？
        ↓
数据库结构如何持续演进？

2. Schema 设计的基本方法

数据库设计不要从“我要建哪些表”开始，而应该从业务模型开始。

例如一个简单的项目管理系统：

用户
 │
 ├── 工作空间
 │      │
 │      └── 项目
 │             │
 │             └── 任务
 │
 └── 操作日志

可以进一步转换成：

users
  │
  ├──< workspaces
  │        │
  │        └──< projects
  │                │
  │                └──< tasks
  │
  └──< audit_logs

其中：

一个用户可以创建多个工作空间
一个工作空间可以拥有多个项目
一个项目可以拥有多个任务
一个用户可以产生多条操作日志

这一步实际上是在确定数据库的实体和关系。

2.1 先确定实体

通常可以从业务中的名词寻找实体，例如：

用户
订单
商品
项目
任务
评论
文章
日志
角色
权限

每个独立的业务实体，通常对应一张表。

例如：

users
orders
products
articles
comments

2.2 再确定实体之间的关系

常见关系主要有：

一对一
一对多
多对多

例如：

用户 1 ---- N 订单
订单 1 ---- N 订单商品
商品 1 ---- N 订单商品

订单和商品实际上是多对多关系，因此通常需要中间表：

orders
   │
   └── order_items ── products

而不是直接把商品 ID 存成：

product_ids = "1,2,3,4"

这种设计会给查询、索引和数据完整性带来很多问题。

3. 字段设计

确定表之后，再设计字段。

一个典型的用户表：

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(64) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status TINYINT UNSIGNED NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

这里有几个比较重要的设计原则。

3.1 类型要符合数据语义

不要为了方便，把所有字段都设计成：

VARCHAR(255)

例如：

年龄       → SMALLINT
数量       → INT
状态       → TINYINT
金额       → DECIMAL
时间       → DATETIME
内容       → TEXT
结构化扩展 → JSON

例如金额：

amount DECIMAL(18, 2) NOT NULL DEFAULT 0.00

不要使用：

amount FLOAT

存储需要精确计算的金额。

3.2 主键

常见设计：

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT

对于大型业务系统，BIGINT 通常比 INT 更稳妥，可以避免后期数据量增长导致主键空间不足。

如果系统采用 UUID、ULID 等方案，则需要结合索引长度、存储空间以及写入顺序综合考虑，而不是简单认为“UUID 一定更好”。

3.3 状态字段

状态字段通常使用：

status TINYINT UNSIGNED NOT NULL DEFAULT 1

例如：

0 = 禁用
1 = 正常

如果状态非常复杂，可以进一步使用枚举常量或者状态机管理，而不是在代码中大量出现：

if ($status === 3) {
    // ...
}

更推荐：

UserStatus::ACTIVE
UserStatus::DISABLED

让数据库中的值和代码中的业务含义保持清晰。

4. NULL、默认值和字段命名

NULL 并不是不能使用，关键是要根据业务语义决定。

例如：

deleted_at DATETIME NULL

是比较合理的设计：

NULL       → 未删除
2026-09-09 → 已删除

而对于必填字段：

username VARCHAR(64) NOT NULL

通常应该明确禁止 NULL。

设计字段时可以问自己一个问题：

这个字段是否存在“未知/不存在/未设置”的业务状态？

如果不存在，就优先考虑：

NOT NULL

如果确实存在，就使用：

NULL

字段命名

建议整个项目保持统一：

id
user_id
project_id
created_at
updated_at
deleted_at
status
sort
name
description

不要在不同表中出现：

create_time
created_at
createdDate
createDate

数据库设计中最重要的并不是某一个命名规则，而是统一。

5. 主键、唯一约束与索引

这三个概念经常被混淆。

PRIMARY KEY

用于标识一条记录：

PRIMARY KEY (id)

UNIQUE KEY

用于保证业务数据唯一：

UNIQUE KEY uk_email (email)

例如用户邮箱不能重复。

普通索引

主要用于提高查询效率：

KEY idx_status (status)

例如：

SELECT *
FROM users
WHERE status = 1;

但是不要因为某个字段出现在 WHERE 中，就机械地给它建立索引。

索引设计应该从真实 SQL出发。

例如业务经常执行：

SELECT *
FROM orders
WHERE user_id = ?
  AND status = ?
ORDER BY created_at DESC
LIMIT 20;

那么可以考虑：

KEY idx_user_status_created (
    user_id,
    status,
    created_at
)

而不是分别建立：

idx_user_id
idx_status
idx_created_at

具体是否最优，仍然需要通过 EXPLAIN 和真实数据验证。

6. 数据库设计案例

假设我们设计一个简单的文章系统。

业务需求：

用户可以创建文章
文章属于某个用户
文章具有状态
文章支持分类
用户可以对文章发表评论

可以得到以下表：

users
  │
  ├──< articles
  │       │
  │       ├──< article_categories >── categories
  │       │
  │       └──< comments

users

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(64) NOT NULL,
    email VARCHAR(255) NOT NULL,
    status TINYINT UNSIGNED NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

articles

CREATE TABLE articles (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    content LONGTEXT NOT NULL,
    status TINYINT UNSIGNED NOT NULL DEFAULT 0,
    published_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    KEY idx_user_id (user_id),
    KEY idx_status_created (status, created_at)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

article_categories

CREATE TABLE article_categories (
    article_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,

    PRIMARY KEY (article_id, category_id),
    KEY idx_category_id (category_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

这里通过中间表实现：

文章 N ---- N 分类

同时：

PRIMARY KEY (article_id, category_id)

可以防止同一篇文章重复关联同一个分类。

7. Schema 的管理方法

数据库设计完成并不意味着工作结束。

真正的项目中，Schema 会不断发生变化：

新增字段
新增索引
修改字段类型
新增表
删除字段
拆分表
数据迁移

因此必须建立 Schema 的版本管理机制。

7.1 使用 Migration

不要直接在生产数据库执行：

ALTER TABLE users ADD COLUMN xxx;

然后靠人工记录。

应该通过 Migration 管理：

database/
└── migrations/
    ├── 202609090001_create_users.php
    ├── 202609090002_create_articles.php
    ├── 202609090003_create_categories.php
    └── 202609090004_add_user_status.php

这样数据库结构就能够随着代码一起进行版本控制。

基本流程：

修改数据库结构
      ↓
创建 Migration
      ↓
本地测试
      ↓
提交 Git
      ↓
测试环境执行
      ↓
生产环境执行

这也是数据库工程化管理的重要组成部分。

7.2 生产环境修改大表要谨慎

例如：

ALTER TABLE orders
ADD COLUMN remark VARCHAR(255);

如果 orders 已经有几千万甚至上亿条数据，就不能简单认为：

“只是增加一个字段，很快就执行完。”

需要考虑：

表大小
MySQL 版本
DDL 算法
锁
IO
执行时间
业务并发
主从复制
回滚方案

对于大型表，还需要评估 Online DDL 或 Online Schema Change 等方案。

8. Schema 与数据增长

数据库设计还要考虑未来的数据规模。

例如：

用户表       100 万
文章表       5000 万
订单表       2 亿
操作日志     10 亿

这些表的处理方式不能完全一样。

小表

正常设计即可：

users
categories
permissions

大业务表

需要关注：

索引数量
索引长度
分页方式
SQL 执行效率
数据归档
冷热数据

例如订单表长期增长，可以考虑：

orders
├── 热数据
└── 历史数据

日志类数据则可以进一步考虑：

按时间归档
按月/季度拆分
分区表
独立日志存储

但不要在项目初期为了“未来可能达到几亿数据”就直接进行复杂的分库分表。

更合理的原则是：

根据实际数据规模和访问模式演进数据库架构。

9. 常见 Schema 设计问题

9.1 一个表塞所有字段

例如：

users
├── user
├── address
├── company
├── order
├── login
├── permission
└── ...

这种设计会导致表越来越宽，职责混乱。

应该根据实体和业务关系拆分。

9.2 滥用 VARCHAR(255)

例如：

age VARCHAR(255)
status VARCHAR(255)
price VARCHAR(255)

这些字段明显没有体现真实数据类型。

数据库字段类型应该尽可能表达数据本身的语义。

9.3 把多个 ID 放进一个字段

例如：

category_ids VARCHAR(255)

保存：

1,2,5,8

这种方式虽然开发初期方便，但会严重影响查询和数据完整性。

应该使用：

article_categories

这样的关联表。

9.4 并不是索引越多越好

索引会：

占用磁盘空间
增加写入成本
增加 UPDATE/DELETE 成本
增加优化器选择成本

因此应该根据实际查询建立索引，并定期分析无效、重复或低价值索引。

9.5 只考虑当前业务

例如：

name VARCHAR(20)

当前可能够用，但如果业务未来允许名称达到 100 个字符，就会产生 Schema 变更。

设计时应该考虑合理的业务边界，但也不要无限扩大字段长度。

10. 一套实用的 Schema 设计流程

实际项目可以按照下面的流程进行：

① 梳理业务实体
        ↓
② 确定实体关系
        ↓
③ 设计表结构
        ↓
④ 选择字段类型
        ↓
⑤ 定义主键和唯一约束
        ↓
⑥ 根据真实 SQL 设计索引
        ↓
⑦ 使用测试数据验证
        ↓
⑧ EXPLAIN 分析关键 SQL
        ↓
⑨ Migration 管理 Schema
        ↓
⑩ 根据数据增长持续优化

可以把它总结成四个阶段：

设计

业务模型 → 数据模型 → 表结构

验证

SQL → EXPLAIN → 性能验证

发布

Migration → 测试环境 → 生产环境

演进

数据增长 → 性能问题 → Schema 优化

数据库 Schema 不是一次性设计完成的静态结构，而是随着业务不断演进的基础设施。

一个好的 Schema 不一定是最复杂的，而应该做到：

结构清晰
数据类型合理
约束明确
索引匹配实际查询
能够支撑当前业务规模
Schema 变更可追踪、可管理
能够随着业务增长持续演进

对于 MySQL 项目来说，真正成熟的数据库设计，最终应该形成：

业务模型
    ↓
Schema 设计
    ↓
SQL 与索引
    ↓
EXPLAIN 性能验证
    ↓
Migration 版本管理
    ↓
生产变更管理
    ↓
持续优化与演进

这比单纯记住“字段怎么定义、索引怎么建”更加重要。

MySQL Schema 设计与管理#

1. 什么是 MySQL Schema#

2. Schema 设计的基本方法#

2.1 先确定实体#

2.2 再确定实体之间的关系#

3. 字段设计#

3.1 类型要符合数据语义#

3.2 主键#

3.3 状态字段#

4. NULL、默认值和字段命名#

字段命名#

5. 主键、唯一约束与索引#

PRIMARY KEY#

UNIQUE KEY#

普通索引#

6. 数据库设计案例#

users#

articles#

categories#

article_categories#

7. Schema 的管理方法#

7.1 使用 Migration#

7.2 生产环境修改大表要谨慎#

8. Schema 与数据增长#

小表#

大业务表#

9. 常见 Schema 设计问题#

9.1 一个表塞所有字段#

9.2 滥用 VARCHAR(255)#

9.3 把多个 ID 放进一个字段#

9.4 并不是索引越多越好#

9.5 只考虑当前业务#

10. 一套实用的 Schema 设计流程#