一、起源

1.1 结构变更的成本远高于代码

代码变更可以通过发布回滚,而结构变更涉及数据本身,代价体现在三处:

代价 说明
不可逆 删除列与修改类型可能造成数据丢失,无法通过回滚代码恢复
影响面广 一个字段的语义变化会影响所有读取该字段的代码、报表与下游系统
执行窗口 大表的变更需要额外的在线变更流程与观察期,无法随发布完成

因此建模阶段的约定比事后优化更有价值:约定统一时,新增表与新增字段的成本很低;约定缺失时,同类问题会在每张表上重复出现。

1.2 三类常见返工

返工类型 典型表现 根因
类型不当 金额用浮点、时间用字符串、状态用文本 建表时按直觉选类型,未考虑精度与查询
索引缺失或冗余 上线后大量慢查询,或索引互相重复且写入变慢 索引随业务零散追加,缺少统一评审
语义漂移 同一含义的字段在不同表中名称与取值不同 缺少命名与状态码的统一约定

1.3 取舍

选择 获得 付出
严格约定 新人可快速理解结构,查询与统计口径一致 建模阶段需要评审,短期速度略慢
宽松结构 建表快 类型混用、命名不一致、索引失控,后期治理成本高

二、原理

2.1 命名规范

对象 约定 说明
表名 小写字母与下划线,语义为业务实体名 避免使用保留字与拼音缩写
字段名 小写字母与下划线,避免与表名重复前缀 关联字段与被关联表的主键同名,便于识别
主键 统一为 id 便于工具与代码统一处理
唯一索引 uk_<字段> 或 uk_<字段1>_<字段2> 与主键区分
普通索引 idx_<字段> 或 idx_<字段1>_<字段2> 按最左列命名
相似字段 时间类以 _at 结尾,布尔类以 is_ 或 has_ 前缀,标识类以 _id 结尾 使字段用途自解释

注意标识符长度限制:MySQL 的标识符上限为 64 个字符,索引名与约束名同样受限。复合索引字段过多时按最左列命名,而不是拼接全部字段名。

2.2 主键设计

方案 优点 代价 适用
自增整数 写入顺序性好、索引紧凑、页分裂少 需要回查才能得到主键;分库分表下不唯一 单库单表、无跨库需求
业务键 语义清晰、便于排查 业务键可能变更,变更成本极高 业务键绝对稳定且长度可控
全局唯一标识 分片下唯一、可在写入前生成 随机分布导致索引写入离散与页分裂;长度较大 分库分表、需要提前生成标识
复合主键 表达从属关系 关联与更新复杂,部分框架支持不佳 中间表与严格从属的明细表

三条结论:

  • 主键选择要考虑索引的物理组织:顺序写入的整数主键使页填充率高、分裂少;随机标识会造成离散写入,需要关注页分裂与空间放大。
  • 业务键作为主键的风险是变更:一旦业务规则调整(如单号规则变化),主键变更几乎等同于重建数据。业务键应作为唯一索引存在,而不是主键。
  • 即使使用全局唯一标识,也建议保留一个自增或时间有序的辅助列用于按时间范围扫描与归档。

2.3 字段类型选择

语义 推荐类型 避免 原因
金额 定点类型(如 18 位整数 4 位小数) 浮点类型 浮点存在精度误差,定点可精确表示
比例与利率 定点类型或整数(按最小单位放大) 浮点类型 同上,且需要明确舍入规则
时间点 带精度的时间类型(毫秒级) 字符串、整数时间戳 字符串无法比较与运算,整数时间戳可读性差
布尔 微整数(0 与 1) 字符串 可参与索引与统计,语义由字段名与注释表达
状态 微整数或小整数存代码 ENUM 类型、文本 ENUM 变更需 DDL;文本占用大且易漂移
标识 按取值范围选最小整数类型 一律用最大整数类型 类型过大浪费空间并影响索引密度
短文本 变长字符串,长度按业务上限 定长字符串 定长类型在长度差异大时浪费空间
长文本 大文本类型,或拆分到独立表 放在主表 行长增大导致行溢出与主表扫描成本上升
半结构化数据 JSON 类型,仅用于不适合结构化存储的部分 作为主要查询路径 无法有效索引,查询与统计困难

四项补充说明:

  • 时间的时区语义需要在建模阶段确定。一种时间类型存储为绝对时间并按会话时区转换,另一种原样存储不做转换。选择后必须统一,否则跨时区读取会出现偏移;同时注意其中一种类型存在上限年份的范围限制。
  • 字符集统一为支持四字节字符的 UTF-8 变体。字符集与排序规则不仅影响存储,还影响比较、排序与唯一索引的判定。
  • 金额字段的精度按币种确定,不要统一按两位处理;金额的运算与舍入规范见 数值精度与金额处理。
  • 状态字段存代码而不存文案:文案属于展示层,随语言与产品调整变化,存文案会导致数据随时间漂移。

2.4 范式与反范式

设计 做法 适用
规范化 消除传递依赖,数据只存一份 写多读少、一致性要求高
反范式 冗余常用字段,避免高频关联 读多写少、关联表数据量巨大

反范式的判断依据是"关联查询是否成为主要瓶颈"。引入冗余字段时必须同时确定三件事:冗余字段的更新时机、更新失败时的补偿方式、以及数据不一致时的核对手段。缺少其中任何一项,冗余会长期处于不一致状态且无人发现。

2.5 约束与默认值

约定 好处 代价
字段非空并给默认值 避免空值参与比较与统计带来的歧义;索引统计更准确 需要明确"空"与"默认值"的语义差别
数值字段设默认 0、字符串设默认空串 查询与聚合不需要额外处理空值 需要业务确认默认值的语义
使用外键约束 由数据库保证引用完整性 高并发写入时增加锁竞争;分库分表下无法使用

外键的取舍结论:单库、写入压力可控、数据一致性要求高的场景使用外键;分库分表、写入压力大或需要灵活处理历史数据的场景由应用层保证引用完整性,此时必须配套核对手段(定期一致性检查),否则会积累脏数据。

2.6 审计字段与逻辑删除

推荐的固定字段集合:

字段 用途 说明
创建时间 审计与按时间归档 由应用或数据库默认值填充
更新时间 审计与增量同步 需在更新语句中显式维护,默认值不会自动更新
创建人与更新人 追责与排查 记录操作者标识而非名称
版本号 乐观锁 并发更新时防止覆盖,见 事务与锁
删除标记或删除时间 逻辑删除 保留数据,便于恢复与审计

逻辑删除的三项代价需要在建模时就处理:

  • 唯一索引冲突:逻辑删除后同一业务键再次写入会与已删除记录冲突。处理方式是把删除标记(或删除时间)纳入唯一索引,使已删除记录不再占用业务键。
  • 查询遗漏:所有查询都必须带删除条件,遗漏会读到已删除数据。应由数据访问层统一拦截,而不是依赖开发者记忆。
  • 数据增长:删除只是标记,数据量持续增长,需要配套归档策略。

2.7 索引设计

约定 说明
联合索引按最左前缀使用 列顺序按"等值条件在前、范围条件在后、排序字段随后"排列
区分度高的列靠前 等值条件中优先放区分度高的列,减少扫描行数
覆盖索引 查询所需列都在索引中时可避免回表,对高频查询尤其有效
避免冗余索引 已有 (a, b) 时 (a) 通常冗余;冗余索引增加写入与空间成本
前缀索引 长字符串可只索引前缀,代价是无法用于覆盖与排序
控制单表索引数量 索引越多写入越慢,新增索引需评估写入影响
索引列保持"裸列" 对列做函数运算、类型转换或表达式会使索引失效
更新频繁的列慎建索引 索引维护成本随更新频率上升

2.8 隐式转换与字符集一致性

两类问题会导致"索引存在但不生效":

问题 成因 表现
隐式类型转换 字符串列与数字比较,或数字列与字符串比较 执行计划显示全表扫描或索引失效
字符集不一致 关联两表的字符集或排序规则不同 关联无法使用索引,且结果可能不符合预期

判断方式是查看执行计划中的扫描类型与警告信息,而不是只看是否有索引。字符集在建模阶段就要统一,事后修改属于结构变更且可能触发索引重建。

2.9 大表与大字段的预留

预留项 做法 目的
分片键 预留业务维度上的高频过滤字段并建立索引 为后续水平拆分保留可行路径
时间维度 创建时间建有索引 支持按时间归档与分区
大字段 拆到独立表并对主表保留引用 控制主表行长,减少行溢出
归档条件 明确可归档状态与保留期 避免数据无限增长
主键单调性 避免完全随机的主键 降低页分裂与空间放大

行长存在上限,主表中字段过多或包含大文本会导致行溢出,溢出部分被存到外部页,使读取成本上升。因此"把字段都放一张表"在字段数量增长后会产生明确的性能问题。

2.10 与结构迁移的配合

建模时需要为变更留出空间:新增字段默认允许为空或提供默认值,使结构变更可以先于代码发布;删除字段分阶段进行,先停止写入并观察再删除。具体流程见 数据库 Schema 版本化迁移。

三、实现

3.1 建表模板

CREATE TABLE `order`
(
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    `order_no`        VARCHAR(32)     NOT NULL COMMENT '业务单号',
    `user_id`         BIGINT UNSIGNED NOT NULL COMMENT '用户标识',
    `status`          TINYINT         NOT NULL DEFAULT 0 COMMENT '状态:0 待支付 1 已支付 2 已取消',
    `amount`          DECIMAL(18, 4)  NOT NULL DEFAULT 0 COMMENT '金额,按业务精度保留',
    `currency`        CHAR(3)         NOT NULL DEFAULT 'CNY' COMMENT '币种代码',
    `remark`          VARCHAR(255)    NOT NULL DEFAULT '' COMMENT '备注',
    `is_deleted`      TINYINT         NOT NULL DEFAULT 0 COMMENT '删除标记:0 正常 1 已删除',
    `version`         INT             NOT NULL DEFAULT 0 COMMENT '乐观锁版本',
    `created_at`      DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
    `updated_at`      DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
                                        ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
    `created_by`      BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建人标识',
    `updated_by`      BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新人标识',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_order_no` (`order_no`, `is_deleted`),
    KEY `idx_user_status_created` (`user_id`, `status`, `created_at`),
    KEY `idx_created_at` (`created_at`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT ='订单';

模板中的三处设计意图:

  • 唯一索引包含删除标记,使逻辑删除后同一业务单号可以重新写入。
  • 联合索引按"等值条件加范围与排序"排列,覆盖"某用户某状态按时间排序"的高频查询。
  • 单列时间索引服务于归档与全量统计。

3.2 命名与类型速查

场景 命名 类型
主键 id BIGINT UNSIGNED
关联标识 <被关联表>_id 与被关联主键一致
时间 *_at DATETIME(3)
布尔 is_* 或 has_* TINYINT
状态 status TINYINT
金额 amount、price、fee DECIMAL(18, 4)
币种 currency CHAR(3)
版本 version INT
删除标记 is_deleted TINYINT

3.3 索引评审清单

核对项 要求
是否存在全表扫描 高频查询的执行计划中不应出现全表扫描
联合索引列顺序 等值在前、范围在后、排序随后
是否可覆盖 高频查询尽量只查索引列,避免回表
是否有冗余索引 检查是否存在被其他索引最左前缀包含的索引
是否有未使用索引 统计索引使用情况,长期未使用的索引应评估删除
索引数量 单表索引数量控制在合理范围,写入频繁的表从严
索引列是否裸列 查询条件中不对索引列做函数或类型转换
字符集与排序规则 关联表之间保持一致
大表变更方式 需要重建表的变更走在线变更流程

3.4 建模评审清单

核对项 要求
命名 表、字段、索引符合约定,无保留字与外文夹杂
主键 类型与生成方式明确,分片场景已考虑唯一性
类型 金额为定点、时间为时间类型、状态为整数代码
非空与默认值 除确实需要区分空值的字段外均非空并有默认值
唯一约束 业务唯一键已建立唯一索引,逻辑删除场景已纳入删除标记
审计字段 创建与更新时间、操作人、版本、删除标记齐全
字符集 统一为支持四字节字符的 UTF-8 变体
大字段 大文本与大对象未放在主表
归档 已明确归档条件与保留期
分片预留 分片键已作为联合索引首列出现在高频查询中

3.5 检查命令

# 查看表结构、索引与注释
mysql -e "SHOW CREATE TABLE \`order\`\G"

# 查看索引的区分度(该列越接近总行数,区分度越高)
mysql -e "SELECT COUNT(DISTINCT user_id) AS card, COUNT(*) AS total FROM \`order\`;"

# 查看索引使用情况(统计信息中未使用的索引可作为删除候选)
mysql -e "SELECT * FROM sys.schema_unused_indexes WHERE object_schema = DATABASE();"

# 查看冗余索引
mysql -e "SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = DATABASE();"

# 核对字符集与排序规则是否统一
mysql -e "SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();"

# 查看执行计划中的扫描类型与索引使用
mysql -e "EXPLAIN SELECT * FROM \`order\` WHERE user_id = 1 AND status = 1 ORDER BY created_at DESC LIMIT 20;"

# 查看隐式转换警告(需开启告警级别)
mysql -e "SHOW WARNINGS;"

# 查看表与索引占用空间,评估大表
mysql -e "SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() ORDER BY DATA_LENGTH DESC LIMIT 10;"

3.6 常见问题对照

现象 常见原因 处置
对账出现分位差额 金额字段使用浮点类型 改为定点类型并清洗历史数据
时间读取出现偏移 时间字段类型与应用的时区约定不一致 统一时区约定,修正读取路径
索引存在但查询仍慢 索引列上做了函数运算或发生隐式转换 改为裸列比较,统一类型与字符集
新增索引后写入变慢 索引数量过多或存在冗余索引 清理未使用与冗余索引
唯一索引导致重复数据写入失败 逻辑删除后业务键仍被占用 把删除标记纳入唯一索引
查询到已删除数据 查询未带删除条件 由数据访问层统一追加条件
状态含义变化导致数据无法解释 状态值直接存文案或缺乏统一约定 改为存代码并维护状态字典
关联查询无法使用索引 两表字符集或排序规则不一致 统一字符集与排序规则
单表数据量过大导致查询变慢 缺少归档与分片预留 增加归档任务,评估水平拆分
主表读取成本高 大字段与字段数量过多导致行溢出 大字段拆表,控制主表行长
分片后出现标识冲突 使用自增主键但未预留全局唯一方案 改用全局标识生成方式

四、总结

  1. 结构变更不可逆且影响面广,因此建模约定比事后优化更有价值,约定统一时增量变更成本极低。
  2. 命名约定覆盖表、字段、索引与相似字段的后缀与前缀,同时注意标识符长度上限。
  3. 主键选择要考虑索引的物理组织:顺序主键写入密集、随机标识导致页分裂,业务键应作为唯一索引而非主键。
  4. 字段类型的固定选择是金额用定点、时间用时间类型、状态用整数代码、标识按范围选最小类型,避免用文本承担结构化语义。
  5. 时间的时区语义与字符集必须在建模阶段统一,字符集同时影响比较、排序与唯一索引判定。
  6. 反范式的前提是明确冗余字段的更新时机、失败补偿与核对手段,三者缺一不可。
  7. 非空与默认值使索引统计与聚合行为更可预期,外键的取舍取决于写入压力与是否分库分表,省略外键必须配套核对手段。
  8. 审计字段应作为固定集合存在;逻辑删除需要处理唯一索引冲突、查询遗漏与数据增长三项代价。
  9. 索引设计的核心是列顺序与覆盖:等值在前、范围在后、排序随后,同时避免冗余索引与在索引列上做运算。
  10. 大表与大字段需要在建模阶段预留分片键、归档条件与拆表方案,否则后期治理只能依赖在线变更与迁移。