数据库设计原则

简介

良好的数据库设计是系统性能和可维护性的基础。本文总结数据库设计的核心原则：范式与反范式、字段选择、索引设计、命名规范和常见反模式。适用于关系型数据库（MySQL、SQL Server、PostgreSQL）。

特点

• 1.范式设计 — 减少数据冗余
• 2.反范式 — 适度冗余提升查询性能
• 3.字段选择 — 合适的类型减少存储和提升性能
• 4.命名规范 — 统一风格降低沟通成本

范式与反范式

三大范式

第一范式（1NF）— 字段不可再分
  每列都是原子值，不存在多值字段

  反例：联系方式（包含手机、邮箱、微信）
  正例：手机、邮箱、微信 分别独立成列

第二范式（2NF）— 完全依赖主键
  非主键字段必须完全依赖主键，不能只依赖部分

  反例：订单明细表（订单ID, 商品ID, 商品名称, 数量）
        商品名称只依赖商品ID，不依赖订单ID
  正例：拆分为 订单明细（订单ID, 商品ID, 数量）+ 商品（商品ID, 商品名称）

第三范式（3NF）— 消除传递依赖
  非主键字段之间不能有依赖关系

  反例：订单（订单ID, 用户ID, 用户姓名, 用户手机）
        用户姓名和手机依赖用户ID
  正例：订单（订单ID, 用户ID）+ 用户（用户ID, 姓名, 手机）

适度反范式

-- 完全范式化 — 查询需要多表 JOIN
SELECT o.Id, o.OrderNo, u.Name, u.Phone, o.TotalAmount
FROM Orders o
JOIN Users u ON o.UserId = u.Id
WHERE o.Id = 1001;

-- 适度反范式 — 冗余常用查询字段
CREATE TABLE Orders (
    Id            BIGINT PRIMARY KEY,
    OrderNo       NVARCHAR(32) NOT NULL,
    UserId        BIGINT NOT NULL,
    UserName      NVARCHAR(50) NOT NULL,   -- 冗余用户名（避免 JOIN）
    TotalAmount   DECIMAL(18,2) NOT NULL,
    Status        NVARCHAR(20) NOT NULL,
    CreatedAt     DATETIME2 NOT NULL
);

-- 什么时候冗余：
-- 1. 高频查询字段
-- 2. 很少变更的维度数据（如分类名称）
-- 3. 避免 JOIN 超过 3 张表的场景

字段类型选择

常用类型

-- 整数
-- TINYINT    — 0-255（状态、枚举）
-- SMALLINT   — -32768~32767
-- INT        — 通用整数（数量、年龄）
-- BIGINT     — 大整数（ID、金额分）

-- 小数
-- DECIMAL(18,2) — 金额（固定精度）
-- FLOAT        — 科学计算（不推荐金额）

-- 字符串
-- VARCHAR(N)  — 可变长度（名称、邮箱）
-- NVARCHAR(N) — Unicode（支持中文）
-- CHAR(N)     — 定长（手机号、编号）
-- TEXT        — 大文本（不推荐，用 VARCHAR(MAX)）

-- 时间
-- DATE        — 日期
-- TIME        — 时间
-- DATETIME2   — 日期+时间（SQL Server）
-- DATETIME    — 日期+时间（MySQL）
-- TIMESTAMP   — 自动更新时间戳

-- 布尔
-- BIT / TINYINT(1) — 0/1

-- JSON
-- JSON (MySQL 5.7+) / NVARCHAR(MAX) 存储

字段设计原则

-- 1. 金额用 DECIMAL，不用 FLOAT
TotalAmount DECIMAL(18,2) NOT NULL    -- 精确到分

-- 2. 状态用枚举表或 TINYINT
Status TINYINT NOT NULL DEFAULT 0     -- 0=待支付 1=已支付 2=已取消

-- 3. 字符串长度合理
Phone   CHAR(11) NOT NULL             -- 手机号固定11位
Name    NVARCHAR(50) NOT NULL         -- 姓名最长50
Email   VARCHAR(100)                  -- 邮箱

-- 4. 必填和选填
-- 必填字段：NOT NULL + DEFAULT
-- 选填字段：允许 NULL 或设置默认值

-- 5. 每张表必备字段
Id         BIGINT PRIMARY KEY AUTO_INCREMENT,
CreatedAt  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
UpdatedAt  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
IsDeleted  BIT NOT NULL DEFAULT 0     -- 软删除标记

索引设计

索引原则

-- 1. 主键索引 — 自增 ID
ALTER TABLE orders ADD PRIMARY KEY (id);

-- 2. 唯一索引 — 业务唯一字段
CREATE UNIQUE INDEX uk_order_no ON orders(order_no);
CREATE UNIQUE INDEX uk_email ON users(email);

-- 3. 联合索引 — 遵循最左前缀
-- 高选择性字段在前，范围查询在后
CREATE INDEX idx_user_status ON orders(user_id, status);
CREATE INDEX idx_created ON orders(created_at);

-- 4. 覆盖索引 — 避免回表
CREATE INDEX idx_user_order ON orders(user_id, order_no, total_amount);

-- 不建议：
-- 1. 在低选择性字段上建索引（如性别、状态只有几个值）
-- 2. 过多的索引（影响写入性能）
-- 3. 频繁更新的字段做索引

命名规范

表和字段命名

表名：     小写，下划线分隔，复数形式
           users, orders, order_items, product_categories

字段名：   小写，下划线分隔
           user_id, created_at, total_amount, is_active

主键：     id 或 表名_id
           id, user_id

外键：     关联表名_id
           user_id, order_id, product_id

布尔：     is_ 前缀
           is_active, is_deleted, is_paid

时间：     _at 后缀
           created_at, updated_at, deleted_at

日期：     _date 后缀
           birth_date, order_date

索引：     idx_表名_字段名
           idx_orders_user_id

唯一索引： uk_表名_字段名
           uk_users_email

完整建表示例

CREATE TABLE users (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50) NOT NULL,
    email         VARCHAR(100) NOT NULL,
    phone         CHAR(11),
    password_hash VARCHAR(256) NOT NULL,
    avatar_url    VARCHAR(500),
    status        TINYINT NOT NULL DEFAULT 1 COMMENT '1=active, 0=disabled',
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_deleted    BIT NOT NULL DEFAULT 0,

    UNIQUE KEY uk_email (email),
    UNIQUE KEY uk_phone (phone),
    KEY idx_username (username),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='用户表';

常见反模式

避免的设计错误

-- 1. 滥用 EAV（实体-属性-值）
-- 差：一张表存所有属性
CREATE TABLE entity_attributes (
    entity_type VARCHAR(50),
    entity_id BIGINT,
    attribute_name VARCHAR(100),
    attribute_value VARCHAR(500)
);
-- 好：每类实体一张表，字段固定

-- 2. 逗号分隔列表
-- 差：tags = "java,dotnet,python"
-- 好：关联表
CREATE TABLE article_tags (
    article_id BIGINT,
    tag_id BIGINT,
    PRIMARY KEY (article_id, tag_id)
);

-- 3. 不用外键约束
-- 生产环境建议在应用层保证引用完整性
-- 外键约束影响性能和分库分表

-- 4. 不合理的 ENUM
-- 差：ENUM('active','inactive','deleted') — 增加值需要 DDL
-- 好：TINYINT + 注释/字典表

-- 5. 软删除忽略唯一约束
-- 差：删除后重新创建会冲突
-- 好：唯一约束加 IS NULL 条件
CREATE UNIQUE INDEX uk_email ON users(email) WHERE is_deleted = 0;

BCNF 与第四范式

BCNF（Boyce-Codd 范式）

BCNF 是 3NF 的加强版，要求：
  每个决定因素都必须是候选键

  反例：选课表（学生ID, 课程ID, 教师ID）
        一门课由一位教师教授 → 教师ID 决定 课程ID
        但（学生ID, 课程ID）才是主键
  正例：拆分为 选课（学生ID, 课程ID）+ 授课（课程ID, 教师ID）

  适用场景：
  - 表中存在多个候选键且它们有重叠列
  - 一个非主键字段能决定其他非主键字段

第四范式（4NF）

4NF 要求：消除多值依赖

  反例：员工技能表（员工ID, 技能, 语言）
        技能和语言之间无关联，但都依赖员工ID
  正例：拆分为 员工技能（员工ID, 技能）+ 员工语言（员工ID, 语言）

  实际项目中：
  - 大多数业务表到 3NF 就足够
  - BCNF/4NF 更多出现在报表系统和分析场景
  - 过度规范化会增加 JOIN 成本

常见数据模型设计

用户与权限模型（RBAC）

-- 核心表结构
CREATE TABLE users (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50) NOT NULL,
    password_hash VARCHAR(256) NOT NULL,
    status        TINYINT NOT NULL DEFAULT 1,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_deleted    BIT NOT NULL DEFAULT 0,
    UNIQUE KEY uk_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

CREATE TABLE roles (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    role_name     VARCHAR(50) NOT NULL,
    role_code     VARCHAR(50) NOT NULL,
    description   VARCHAR(200),
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_role_code (role_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

CREATE TABLE permissions (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    perm_name     VARCHAR(100) NOT NULL,
    perm_code     VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50) NOT NULL COMMENT 'menu/button/api',
    parent_id     INT DEFAULT 0,
    sort_order    INT DEFAULT 0,
    UNIQUE KEY uk_perm_code (perm_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';

-- 关联表
CREATE TABLE user_roles (
    user_id       BIGINT NOT NULL,
    role_id       INT NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    KEY idx_role_id (role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

CREATE TABLE role_permissions (
    role_id       INT NOT NULL,
    perm_id       INT NOT NULL,
    PRIMARY KEY (role_id, perm_id),
    KEY idx_perm_id (perm_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色权限关联表';

-- 查询用户权限（避免 N+1）
SELECT DISTINCT p.perm_code, p.resource_type
FROM permissions p
INNER JOIN role_permissions rp ON p.id = rp.perm_id
INNER JOIN user_roles ur ON rp.role_id = ur.role_id
WHERE ur.user_id = 1001;

订单与支付模型

-- 订单主表
CREATE TABLE orders (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no        VARCHAR(32) NOT NULL COMMENT '订单编号',
    user_id         BIGINT NOT NULL,
    user_name       VARCHAR(50) NOT NULL COMMENT '冗余用户名',
    total_amount    DECIMAL(18,2) NOT NULL COMMENT '订单总金额',
    discount_amount DECIMAL(18,2) NOT NULL DEFAULT 0 COMMENT '优惠金额',
    pay_amount      DECIMAL(18,2) NOT NULL COMMENT '实付金额',
    status          TINYINT NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已发货 3已完成 4已取消',
    pay_time        DATETIME NULL COMMENT '支付时间',
    shipping_address JSON COMMENT '收货地址（JSON 格式）',
    remark          VARCHAR(500),
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_deleted      BIT NOT NULL DEFAULT 0,
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_id (user_id),
    KEY idx_status_created (status, created_at),
    KEY idx_user_status (user_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 订单明细表
CREATE TABLE order_items (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id        BIGINT NOT NULL,
    product_id      BIGINT NOT NULL,
    product_name    VARCHAR(200) NOT NULL COMMENT '冗余商品名',
    product_image   VARCHAR(500) COMMENT '冗余商品图片',
    price           DECIMAL(18,2) NOT NULL COMMENT '下单时单价',
    quantity        INT NOT NULL DEFAULT 1,
    subtotal        DECIMAL(18,2) NOT NULL COMMENT '小计',
    KEY idx_order_id (order_id),
    KEY idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

-- 支付流水表
CREATE TABLE payments (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    payment_no      VARCHAR(64) NOT NULL COMMENT '支付流水号',
    order_id        BIGINT NOT NULL,
    order_no        VARCHAR(32) NOT NULL,
    pay_channel     TINYINT NOT NULL COMMENT '1微信 2支付宝 3银行卡',
    pay_amount      DECIMAL(18,2) NOT NULL,
    status          TINYINT NOT NULL DEFAULT 0 COMMENT '0待支付 1成功 2失败 3退款',
    third_party_no  VARCHAR(128) COMMENT '第三方交易号',
    paid_at         DATETIME NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_payment_no (payment_no),
    KEY idx_order_id (order_id),
    KEY idx_third_party_no (third_party_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付流水表';

树形结构设计（邻接表 vs 路径枚举）

-- 方案一：邻接表（适合层级不深、频繁移动节点）
CREATE TABLE categories_adj (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    parent_id   INT NULL,
    sort_order  INT DEFAULT 0,
    KEY idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分类表（邻接表）';

-- 递归查询子分类（MySQL 8.0 CTE）
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 AS level
    FROM categories_adj
    WHERE id = 1
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories_adj c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

-- 方案二：路径枚举（适合读多写少、需要快速查询整棵子树）
CREATE TABLE categories_path (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    path        VARCHAR(500) NOT NULL COMMENT '如 /1/5/12/',
    level       INT NOT NULL DEFAULT 1,
    sort_order  INT DEFAULT 0,
    KEY idx_path (path(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分类表（路径枚举）';

-- 查询所有子分类
SELECT * FROM categories_path WHERE path LIKE '/1/%';

-- 查询所有祖先
SELECT * FROM categories_path
WHERE '1/5/12/' LIKE CONCAT(path, '%')
ORDER BY LENGTH(path);

-- 方案三：闭包表（适合任意深度、频繁查询父子关系）
CREATE TABLE categories_closure (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分类表（闭包表）';

CREATE TABLE category_relations (
    ancestor    INT NOT NULL,
    descendant  INT NOT NULL,
    depth       INT NOT NULL DEFAULT 0,
    PRIMARY KEY (ancestor, descendant),
    KEY idx_descendant (descendant)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分类关系表';

-- 查询所有后代
SELECT c.* FROM categories_closure c
INNER JOIN category_relations r ON c.id = r.descendant
WHERE r.ancestor = 1 AND r.depth > 0;

-- 查询所有祖先
SELECT c.* FROM categories_closure c
INNER JOIN category_relations r ON c.id = r.ancestor
WHERE r.descendant = 12 AND r.depth > 0;

索引设计深入

覆盖索引实战

-- 场景：订单列表页只需要几个字段
-- 不使用覆盖索引：需要回表查询所有字段
SELECT id, order_no, status, total_amount
FROM orders
WHERE user_id = 100 AND status = 1
ORDER BY created_at DESC
LIMIT 20;

-- 创建覆盖索引（包含查询所需的所有字段）
CREATE INDEX idx_cover_order_list
ON orders(user_id, status, created_at, order_no, total_amount);

-- 验证覆盖索引生效
EXPLAIN SELECT id, order_no, status, total_amount
FROM orders
WHERE user_id = 100 AND status = 1
ORDER BY created_at DESC
LIMIT 20;
-- Extra 中应出现 Using index

-- 注意事项：
-- 1. 覆盖索引不能包含 SELECT *，必须明确列出字段
-- 2. 索引长度不宜过长（建议 < 800 字节）
-- 3. 频繁更新的字段不建议放入覆盖索引

索引与排序

-- 场景：按时间排序的分页查询
-- 差：无法利用索引排序，产生 filesort
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 10;

-- 好：建立 (user_id, created_at DESC) 索引
CREATE INDEX idx_user_created_desc ON orders(user_id, created_at DESC);

-- 多字段排序
-- 差：排序方向不一致
SELECT * FROM orders
WHERE user_id = 100
ORDER BY status ASC, created_at DESC;

-- 好：分别建索引或调整排序方向
CREATE INDEX idx_user_status_asc_created_desc
ON orders(user_id, status ASC, created_at DESC);

冗余索引识别

-- 冗余索引示例
CREATE INDEX idx_a ON orders(user_id);           -- 冗余
CREATE INDEX idx_ab ON orders(user_id, status);  -- 已包含 idx_a 的功能

-- 查找冗余索引（sys schema）
SELECT
    s.table_name,
    s.index_name,
    GROUP_CONCAT(s.column_name ORDER BY s.seq_in_index) AS columns
FROM information_schema.statistics s
WHERE s.table_schema = 'your_db'
GROUP BY s.table_name, s.index_name
ORDER BY s.table_name, columns;

-- 清理未使用索引
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'your_db';

字段类型进阶

JSON 字段使用

-- MySQL 5.7+ 支持 JSON 类型
CREATE TABLE products (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    attributes  JSON COMMENT '商品动态属性',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入 JSON 数据
INSERT INTO products (name, attributes) VALUES
('iPhone 15', '{"color": "midnight", "storage": "256GB", "weight": "171g"}'),
('MacBook Pro', '{"cpu": "M3 Pro", "memory": "18GB", "disk": "512GB"}');

-- 查询 JSON 字段（生成列 + 索引）
ALTER TABLE products
ADD COLUMN color VARCHAR(50) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.color'))) STORED,
ADD INDEX idx_color (color);

SELECT * FROM products WHERE color = 'midnight';

-- JSON 字段适用场景：
-- 1. 属性不固定的商品/配置
-- 2. 非结构化日志存储
-- 3. 需要频繁变更字段但不想 DDL
-- 注意：JSON 字段不适合高频查询条件

时间字段选择

-- DATETIME vs TIMESTAMP 对比
CREATE TABLE time_test (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    dt_col      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ts_col      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 区别：
-- DATETIME：8 字节，不受时区影响，范围 1000-9999
-- TIMESTAMP：4 字节，受时区影响自动转换，范围 1970-2038

-- 建议：
-- 1. 跨时区系统用 TIMESTAMP
-- 2. 需要存储历史日期（如生日）用 DATETIME
-- 3. MySQL 8.0 推荐用 DATETIME(3) 保留毫秒精度
-- 4. 记录行创建/更新时间用 TIMESTAMP（自动维护）

-- 时间范围查询优化
-- 差：函数导致索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-04-14';
-- 好：范围查询
SELECT * FROM orders
WHERE created_at >= '2026-04-14 00:00:00'
  AND created_at < '2026-04-15 00:00:00';

数据字典与约束管理

CHECK 约束（MySQL 8.0+）

-- 使用 CHECK 约束保证数据质量
CREATE TABLE accounts (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    balance     DECIMAL(18,2) NOT NULL,
    status      TINYINT NOT NULL,
    CONSTRAINT chk_balance_positive CHECK (balance >= 0),
    CONSTRAINT chk_status_range CHECK (status IN (0, 1, 2, 3))
) ENGINE=InnoDB;

-- 金额字段不能为负
INSERT INTO accounts (balance, status) VALUES (-100, 1);
-- ERROR 3819 (HY000): Check constraint 'chk_balance_positive' is violated.

-- 查看表的约束
SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_schema = 'your_db';

触发器实现审计日志

-- 审计日志表
CREATE TABLE audit_log (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    table_name  VARCHAR(64) NOT NULL,
    operation   VARCHAR(10) NOT NULL COMMENT 'INSERT/UPDATE/DELETE',
    row_id      BIGINT NOT NULL,
    old_data    JSON,
    new_data    JSON,
    operator    VARCHAR(50),
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_table_row (table_name, row_id),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='审计日志表';

-- UPDATE 触发器
DELIMITER //
CREATE TRIGGER trg_orders_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO audit_log (table_name, operation, row_id, old_data, new_data)
        VALUES (
            'orders', 'UPDATE', NEW.id,
            JSON_OBJECT('status', OLD.status, 'updated_at', OLD.updated_at),
            JSON_OBJECT('status', NEW.status, 'updated_at', NEW.updated_at)
        );
    END IF;
END //
DELIMITER ;

大表设计策略

冷热数据分离

-- 方案一：按时间分区
CREATE TABLE orders (
    id              BIGINT AUTO_INCREMENT,
    order_no        VARCHAR(32) NOT NULL,
    user_id         BIGINT NOT NULL,
    total_amount    DECIMAL(18,2) NOT NULL,
    status          TINYINT NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at),
    KEY idx_user_id (user_id),
    KEY idx_status_created (status, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p2026_q1 VALUES LESS THAN (TO_DAYS('2026-04-01')),
    PARTITION p2026_q2 VALUES LESS THAN (TO_DAYS('2026-07-01')),
    PARTITION p2026_q3 VALUES LESS THAN (TO_DAYS('2026-10-01')),
    PARTITION p2026_q4 VALUES LESS THAN (TO_DAYS('2027-01-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 方案二：归档表
-- 定期将冷数据迁移到归档表
INSERT INTO orders_archive
SELECT * FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH)
  AND status IN (3, 4);  -- 已完成或已取消

DELETE FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH)
  AND status IN (3, 4);

-- 方案三：分表（水平拆分）
-- orders_2026、orders_2025 按年分表
-- 应用层通过中间件路由（ShardingSphere、MyCat）

宽表拆分

-- 当单表字段超过 30 个，考虑垂直拆分
-- 拆分原则：
-- 1. 核心字段（高频查询）留在主表
-- 2. 扩展字段（低频查询）放入扩展表
-- 3. 大文本/大字段独立成表

-- 主表（核心字段）
CREATE TABLE products (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    category_id INT NOT NULL,
    price       DECIMAL(18,2) NOT NULL,
    status      TINYINT NOT NULL DEFAULT 1,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_category_id (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品主表';

-- 扩展表（详情字段）
CREATE TABLE product_details (
    product_id      BIGINT PRIMARY KEY,
    description     TEXT,
    rich_content    LONGTEXT COMMENT '富文本详情',
    spec_params     JSON COMMENT '规格参数',
    seo_title       VARCHAR(200),
    seo_keywords    VARCHAR(500),
    seo_description VARCHAR(1000),
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品详情表';

-- 图片表（一对多）
CREATE TABLE product_images (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_id  BIGINT NOT NULL,
    image_url   VARCHAR(500) NOT NULL,
    sort_order  INT DEFAULT 0,
    is_cover    BIT DEFAULT 0,
    KEY idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品图片表';

优点

• 1.性能保证 — 合理设计减少查询瓶颈
• 2.可维护 — 清晰的命名和结构
• 3.可扩展 — 良好的设计便于后续演进
• 4.数据一致 — 约束和索引保证数据质量

缺点

• 1.前期投入 — 设计阶段需要更多思考
• 2.过度设计 — 简单场景不需要复杂设计
• 3.重构困难 — 上线后修改表结构成本高
• 4.性能权衡 — 范式与性能需要平衡

总结

数据库设计的核心：遵循三范式减少冗余，适度反范式提升查询性能。字段选择合理类型，命名统一规范。每张表必备 id、created_at、updated_at。索引按查询模式设计，遵循最左前缀。先设计后编码，避免后期重构。

关键知识点

• 数据库主题一定要同时看数据模型、读写模式和执行代价。
• 很多性能问题不是 SQL 语法问题，而是索引、统计信息、事务和数据分布问题。
• 高可用、备份、迁移和治理与查询优化同样重要。
• 先把数据模型、访问模式和执行代价绑定起来理解。

项目落地视角

• 所有优化前后都保留执行计划、样本 SQL 和关键指标对比。
• 上线前准备回滚脚本、备份点和校验方案。
• 把连接池、锁等待、慢查询和容量增长纳入日常巡检。
• 保留执行计划、样本 SQL、索引定义和优化前后指标。

常见误区

• 脱离真实数据分布讨论索引或分片。
• 只看单条 SQL，不看整条业务链路的事务和锁。
• 把测试环境结论直接等同于生产环境结论。
• 脱离真实数据分布设计索引。

进阶路线

• 继续向执行计划、存储引擎、复制机制和数据治理层深入。
• 把主题与 ORM、缓存、消息队列和归档策略联动起来思考。
• 沉淀成数据库设计规范、SQL 审核规则和变更流程。
• 继续深入存储引擎、复制机制、归档与冷热分层治理。

适用场景

• 当你准备把《数据库设计原则》真正落到项目里时，最适合先在一个独立模块或最小样例里验证关键路径。
• 适合数据建模、查询优化、事务控制、高可用和迁移治理。
• 当系统开始遇到慢查询、锁冲突、热点数据或容量增长时，这类主题价值最高。

落地建议

• 先分析真实查询模式、数据量级和写入特征，再决定索引或分片策略。
• 所有优化结论都结合执行计划、样本数据和监控指标验证。
• 高风险操作前准备备份、回滚脚本与校验 SQL。

排错清单

• 先确认瓶颈在 CPU、I/O、锁等待、网络还是 SQL 本身。
• 检查执行计划是否走错索引、是否发生排序或全表扫描。
• 排查长事务、隐式类型转换、统计信息过期和参数嗅探。

复盘问题

• 如果把《数据库设计原则》放进你的当前项目，最先要验证的输入、输出和失败路径分别是什么？
• 《数据库设计原则》最容易在什么规模、什么边界条件下暴露问题？你会用什么指标或日志去确认？
• 相比默认实现或替代方案，采用《数据库设计原则》最大的收益和代价分别是什么？

延伸阅读

• 连接池优化
• 索引原理与设计
• 数据迁移实战
• 数据库迁移策略