简介
本指南面向 DouPHP 项目的数据库表结构优化，聚焦以下目标：
- 字段类型选择原则：INT、VARCHAR、TEXT、DECIMAL、DATETIME/DATE/TIME、ENUM 等类型的性能差异与适用场景。
- 表分区策略：范围分区、列表分区、哈希分区的应用场景与落地方法。
- 数据归档方案：历史数据处理、冷热分离、归档策略制定与执行路径。
- 表结构设计最佳实践：范式化与反范式化的权衡、冗余字段设计、枚举值存储优化。
- 结合项目现有表结构给出具体优化建议与可操作案例。
项目结构与数据层概览
DouPHP 的数据库定义集中在各模块的 SQL 备份文件中，并通过 ORM 模型进行读写封装；同时提供工具脚本用于历史数据迁移与一致性治理。
graph TB
A["业务模块<br/>订单/用户/商品"] --> B["SQL 建表脚本<br/>order.sql / user.sql / product.sql"]
B --> C["MySQL 引擎 InnoDB<br/>utf8mb4_unicode_ci"]
A --> D["ORM 模型<br/>Order.php / User.php"]
D --> E["ORM 基础能力<br/>Builder.php / Model.php"]
A --> F["运维与迁移工具<br/>generate_sql.php / upgrade.php"]核心组件与数据流
- 数据写入：控制器/服务通过 ORM 或 DB 门面写入，最终落到 InnoDB 表。
- 数据读取：ORM Builder 链式构建查询，支持 with 预加载、聚合统计与分页。
- 数据演进：通过 generate_sql.php 与 upgrade.php 完成字段类型统一、索引重建与兼容处理。
sequenceDiagram
participant App as "应用代码"
participant ORM as "ORM(Builder/Model)"
participant DB as "数据库连接"
participant TBL as "业务表"
App->>ORM : 构建查询/写入
ORM->>DB : 生成并执行SQL
DB->>TBL : 落盘/检索
TBL-->>DB : 结果集/影响行数
DB-->>ORM : 返回结果
ORM-->>App : 模型/集合/标量架构总览
从“表结构—索引—查询—归档”的全链路视角，优化应围绕以下关键点展开：
- 字段类型最小化且足够表达业务语义。
- 索引覆盖高频查询与排序分组。
- 大文本与日志表采用独立存储与归档策略。
- 状态与分类使用 ENUM 或字典表，避免散落的字符串。
graph LR
Q["高频查询/统计"] --> I["索引设计"]
I --> T["表结构优化<br/>字段类型/冗余/枚举"]
T --> S["存储与分区"]
S --> A["归档与冷热分离"]
A --> M["监控与回归测试"]详细组件分析
字段类型选择原则与实践
- 主键与外键
- 自增主键优先使用 INT/SMALLINT/MEDIUMINT/BIGINT 中满足范围的最小类型，减少索引体积与内存占用。
- 关联 ID 使用无符号整型，避免负数误用。
- 金额
- 所有金额字段使用 DECIMAL(p,s)，禁止 FLOAT/DOUBLE，保证精度一致性与计算正确性。
- 时间
- 统一使用 DATETIME/DATE/TIME，避免混合使用时间戳与日期类型；必要时通过迁移工具统一转换。
- 短文本
- 固定长度或上限明确的短文本使用 VARCHAR(n)，n 尽量贴近实际最大长度，避免过度分配。
- 长文本
- 内容、备注、JSON 等使用 TEXT/LONGTEXT；对不常检索的大字段考虑拆分到扩展表或对象存储。
- 布尔与状态
- 二元状态使用 TINYINT(1) 或 ENUM；多态状态使用 ENUM 或字典表，便于约束与展示映射。
- 唯一标识
- 对外唯一编码（如订单号、会员编号）使用 VARCHAR，并建立 UNIQUE 索引。
索引设计与查询优化
- 单列索引
- 高频等值/范围查询列：如 status、created_at、paid_at、user_id。
- 复合索引
- 联合过滤与排序：如 (status, created_at)、(pay_id, status, paid_at)。
- 前缀索引
- 超长字符串仅使用前缀参与索引，降低索引大小。
- 覆盖索引
- 将常用 SELECT 列纳入索引，减少回表。
- 去重与唯一性
- 对外唯一码（订单号、支付流水号、会员编号）加 UNIQUE 索引。
表分区策略
- 范围分区（RANGE）
- 适用：按时间分区的日志、消息、统计表。例如按月份/季度对 dou_chat_message、dou_ai_usage_log 进行 RANGE 分区，便于按时间窗口清理与归档。
- 列表分区（LIST）
- 适用：有限枚举值的分区，如按 provider_id、chat_id 等维度做 LIST 分区，利于热点隔离与并行维护。
- 哈希分区（HASH）
- 适用：均匀打散写入压力，如按 user_id HASH 分区，提升高并发写入吞吐。
- 注意事项
- 分区键需与查询条件匹配，否则无法有效分区裁剪。
- 分区后索引限制与跨分区聚合成本需评估。
- 建议在 MySQL 5.7+ 环境验证后再上线。
数据归档方案设计
- 归档目标
- 日志类（AI 调用日志、聊天消息）、统计类（每日用量）、历史订单明细。
- 归档策略
- 冷数据按月/季度迁移至归档库或归档表，保留热数据在在线库。
- 使用定时任务（cron/队列）批量导出/导入，配合事务与校验。
- 实施步骤
- 确定归档键（如 created_at、stat_date）。
- 创建归档表结构（与源表一致或精简）。
- 分批迁移（LIMIT/OFFSET 或基于游标），每批提交事务。
- 校验行数与关键指标，确认无误后删除源表旧数据。
- 与项目工具的衔接
- 参考 generate_sql.php 的时间字段统一与索引重建流程，确保归档前后索引一致。
- 参考 upgrade.php 中的字段迁移模式，保证兼容性。
表结构设计最佳实践
- 范式化与反范式化
- 范式化：保持数据一致性，适合低频更新、强一致场景。
- 反范式化：为读多写少的报表/统计增加冗余字段，减少 JOIN 与聚合成本。
- 冗余字段设计
- 在订单/订单条目中冗余必要展示字段（如商品名称、类目ID），避免频繁 JOIN 商品主表。
- 注意冗余字段的一致性维护（触发器/事务/事件）。
- 枚举值存储优化
- 小范围稳定枚举使用 ENUM，便于约束与展示映射。
- 复杂或易变枚举使用字典表，便于扩展与维护。
- 软删除与审计
- 引入 is_deleted、deleted_at 等字段实现软删除。
- 对关键变更记录审计日志表，便于追溯。
依赖关系分析
- 模型与表
- Order.php 对应 order 及相关子表；User.php 对应 user 及相关子表。
- ORM 与底层
- Builder.php 负责链式查询与预加载；Model.php 提供水合、关系与批量写入白名单。
- 配置与环境
- config.php 指定数据库连接、字符集与表前缀，影响全库行为。
classDiagram
class Order {
+table = "order"
+primary = "id"
+casts
+fillable
+items()
+scopeFilterByXxx()
}
class User {
+table = "user"
+primary = "id"
+casts
+fillable
+level()
+scopeFilterByXxx()
}
class Builder {
+where()
+with()
+get()/first()/paginate()
}
class Model {
+fill()->save()
+create()
+destroy()
}
Order --> Model : "继承"
User --> Model : "继承"
Order --> Builder : "使用"
User --> Builder : "使用"性能考量
- 字段尺寸
- 合理缩小字段尺寸，减少行宽，提高缓冲池命中率。
- 索引命中
- 通过 EXPLAIN 分析慢查询，补充或调整复合索引。
- 大字段
- 将大文本/图片路径拆分到扩展表或对象存储，主表只保留引用。
- 统计与报表
- 使用物化视图或汇总表（如 dou_chat_daily_stats）替代实时聚合。
- 分区与归档
- 对超大表启用分区，结合归档策略控制在线库规模。
故障排查指南
- 时间字段不一致
- 使用 generate_sql.php 生成的迁移脚本统一时间字段类型，并重建索引。
- 字段迁移失败
- 参考 upgrade.php 的增量迁移模式，先新增临时列，回填数据，再替换原列。
- 查询缓慢
- 检查是否存在缺失索引、索引失效（函数包裹列）、回表过多等问题。
- 归档异常
- 分批迁移并校验行数与关键指标，出现异常立即回滚并定位问题批次。
结论
通过对字段类型、索引、分区与归档的系统性优化，结合项目现有的 ORM 与迁移工具，可在不破坏既有业务逻辑的前提下显著提升数据库性能与可维护性。建议以“订单/用户/商品”为核心先行试点，逐步推广至日志与统计类表。
附录：表结构示例与优化案例
订单表优化要点
- 字段类型
- 金额使用 DECIMAL(10,2)；时间使用 DATETIME；唯一订单号使用 VARCHAR(20) 并 UNIQUE。
- 索引
- 复合索引 (status, created_at) 支撑列表与统计；(pay_id, status, paid_at) 支撑支付相关查询。
- 冗余
- 订单条目表冗余商品名称、类目ID等展示字段，减少 JOIN。
- 归档
- 历史订单明细可按月份归档至独立库或表，保留最近 N 个月在线。
用户表优化要点
- 字段类型
- 手机号/邮箱使用 VARCHAR(80/180) 并 UNIQUE；密码使用 VARCHAR(255)。
- 索引
- 对 mobile、email 建立唯一索引；登录失败次数与锁定时间用于安全控制。
- 冗余
- 等级信息可通过关联表获取，避免在主表冗余过多等级属性。
- 归档
- 用户日志表可按时间分区或归档，保留近 N 月在线。
商品表优化要点
- 字段类型
- 价格使用 DECIMAL(10,2)；标题/描述使用 VARCHAR/TEXT；图片路径使用 VARCHAR(255)。
- 索引
- slug 建立唯一索引；operator_type/operator_id 建立复合索引便于管理端筛选。
- 冗余
- 促销价、促销时间等冗余字段便于前端快速展示。
- 归档
- 商品详情大字段可考虑拆分到扩展表或对象存储。
日志与统计表的分区与归档
- 日志表（如 AI 调用日志、聊天消息）
- 按 created_at 范围分区，定期归档旧数据。
- 对 admin_id、app_id、created_at 建立合适索引以提升查询效率。
- 统计表（如每日用量）
- 按 stat_date 范围分区，便于按日聚合与清理。
- 对 (stat_date, user_id, chat_id, model_id) 建立唯一索引以避免重复统计。