jmaxzh/sdlc-mysql-design

MySQL 表和 SQL 设计

适用范围

• 当任务或项目确定 MySQL 为目标数据库，并且涉及数据建模、表设计、SQL、索引、事务或并发控制时应用；无需显式调用。
• 独立于 SDLC 工作流使用。不要为通用数据库工作选择 MySQL，也不要将其应用于仅涉及其他数据库的任务。

使用方法

• 使用官方 MySQL 8.4 语义作为验证基线，而不限制部署版本。当版本差异重要时，验证实际目标版本中的功能支持、约束执行和弃用情况。
• 确定与任务相关的业务含义、完整性要求、主要访问模式、数据规模和增长、并发性和一致性要求。在可用时使用现有设计和工作负载证据；当关键信息缺失时，陈述假设及其影响。
• 将事务和并发规则应用于本地 InnoDB 事务。明确标识禁止在事务内执行查询、CHECK 约束、外键约束和存储过程是项目约定，而非 MySQL 限制。
• 假设一条记录每毫秒最多被修改一次。此假设不能保证 updated_at 是一个不可重用的版本标识符；在将其用于并发控制之前，请应用 时间戳检查 下的条件。
• 仅应用与任务相关的规则。当某个规则需要澄清时，阅读 MySQL 设计示例 中的相应主题；不要默认加载所有示例。

数据建模

实体、关系和字段所有权

• 行粒度：定义一行所代表的业务对象、关系或事件；保持表内的粒度一致。参见 示例。
• 关系和唯一性：定义双方的最小和最大基数。在多的一方存储一对多引用，使用唯一引用将一对一关系限制为最多一个匹配，并为多对多关系使用连接表。对于同一对象的重复关联，定义每个关联的业务含义、标识和唯一性范围。参见 示例。
• 字段所有权：将字段放置在其值的决定对象、关系或事件中；避免复制当前事实。在事件时间值处保留历史快照，不受当前值更新的影响。参见 示例。
• 引用完整性：定义在创建或更改引用时，被引用的目标是否必须存在以及必须满足哪些状态。定义当目标被删除或失效时，如何处理现有引用。通过 键和约束 和 并发控制 下的业务写入流程强制执行这些要求。参见 示例。

表设计

名称和注释

• 使用小写字母、数字和下划线命名数据库、表和列；不要以数字开头、使用保留字或含义不清的缩写。
• 根据列或其用途命名索引。uniq_ 和 idx_ 可以标识唯一索引和普通索引；主索引名称为 PRIMARY。
• 当含义、状态值或约束发生变化时，更新列注释和相关定义。

数据类型

• 保持用于引用或比较相同值的列在数据类型和符号方面兼容；尽可能使用一致的定义。
• 使用具有显式精度和小数位数的 DECIMAL 或定义最小单位的整数来表示精确的货币金额；定义货币和舍入规则。在存储、参数和计算过程中保持精确表示；不要在转换前使用浮点数进行计算。
• 不要对 FLOAT、DOUBLE 或 DECIMAL 设置已废弃的 UNSIGNED。
• 使用 is_xxx 和 TINYINT UNSIGNED 表示布尔值，并通过应用程序验证将值限制为 0 或 1。
• 使用 CHAR 时，验证其尾部空格处理是否与字段的业务含义匹配。
• TIMESTAMP 在会话时区和 UTC 之间转换；DATETIME 不转换。在需要时单独存储原始时区名称或偏移量。
• 使用 created_at 表示创建时间，updated_at 表示最后修改时间。可变的业务记录需要两者；仅追加的记录只需要创建时间。根据审计需求将其添加到连接表或其他表中。
• 审计时间戳使用 DATETIME(3)、毫秒精度和北京时间，连接时区为 +08:00。
• 将审计时间戳设置为 NOT NULL DEFAULT CURRENT_TIMESTAMP(3)，并为 updated_at 添加 ON UPDATE CURRENT_TIMESTAMP(3)。让数据库维护它们；不要在应用程序中冗余赋值。

键和约束

• 为 InnoDB 表显式定义稳定、简短的主键。二级索引记录包含主键列，因此宽主键会增加二级索引成本。
• 将必填字段标记为 NOT NULL。
• 不要使用 CHECK；在每个应用程序写入入口点验证范围、枚举和字段组合，拒绝无效写入。参见 示例。
• 使用唯一索引强制执行业务唯一性，而不是查询后插入。唯一索引允许多个 NULL 值。
• 在适用的业务操作和租户范围内，强制使用幂等标识符的唯一性，所有参与列必须为 NOT NULL。
• 不要使用外键约束或级联操作；通过业务写入流程维护引用完整性。

字符集和排序规则

• 文本使用 utf8mb4。
• 保持用于引用或比较相同值的文本列在字符集和排序规则方面兼容；尽可能使用一致的定义。
• 在更改排序规则之前，检查相等性变化和新出现的重复值违反唯一性的情况。

SQL 编程

• 不要使用存储过程。应用程序代码控制分支、提交和回滚。

查询

• 在稳定的业务查询中显式选择所需列；避免 SELECT *。
• 当分页或单行选择需要确定性顺序时，使用唯一排序行的排序列组合。
• 在游标中包含所有排序列。并发的排序列更改可能导致跨页遗漏/重复；游标分页不能保证跨页的一致性快照。

数据修改

• 不要使用 INSERT IGNORE 或 UPDATE IGNORE 隐藏应该失败的错误。
• 防止 UPDATE/DELETE 由于空参数或缺少动态谓词而退化为全表操作。LIMIT 不能替代业务范围条件。
• 根据上述审计时间戳定义，updated_at 在行内容更改时自动更改；它不记录每条更新语句的执行时间。
• 不要用 REPLACE INTO 替换普通更新：在主键/唯一键冲突时，其替换语义可能会将未指定的字段重置为默认值，并触发删除/插入行为。

表达式和函数

• 匹配比较类型；不要让隐式转换决定关键的业务比较。
• 在长度检查中区分字节、字符和用户可见的字素。LENGTH 计数字节，CHAR_LENGTH 计数字符，而非可见字素。
• 如果 NOT IN 使用的集合包含 NULL，则匹配不到成员的值可能返回 UNKNOWN；不要将其解释为确认的非成员身份。
• COUNT(DISTINCT col) 排除 NULL；其多列形式仅在所有参与列非 NULL 时才计算不同组合数。
• 对于空输入或全 NULL 输入，SUM 返回 NULL。如果 GROUP BY 未形成分组，则不会返回任何行。仅当业务规则将空聚合定义为零时，才将其替换为零。
• 优先使用左闭右开时间区间：包含开始，排除结束。在与存储值相同的时间基础上，在业务时区中计算边界。

事务和一致性

事务管理

• 事务内的语句：在显式事务外部执行查询、业务验证和写入准备。在事务内部，仅允许必要的数据修改和事务控制。禁止在写入操作（包括子查询）中包含普通查询、锁定查询和查询组件。参见 示例。
• 事务连接：必须在同一连接上的同一事务内执行必须同时成功的写入操作。连接切换、异步执行和传播设置必须保留此边界。参见 示例。
• 隐式提交：不要使用 START TRANSACTION 模拟嵌套事务，也不要在业务写入之间将 autocommit 从 0 切换为 1；两者都会隐式提交。将 DDL（如 ALTER TABLE 和 TRUNCATE TABLE）排除在业务写入事务之外。参见 示例。
• 失败处理：在必需的写入错误或业务条件未满足时，停止后续写入并回滚整个事务。不要假设失败的语句会撤消之前的写入。在死锁或锁超时后，确保完全回滚，并且仅在安全时重试整个事务，并设置有限的重试次数。对于不确定的提交，首先使用幂等标识符进行验证。参见 示例。
• 外部操作：本地事务不会原子地提交/回滚外部调用或消息发送。参见 示例。

并发控制

• 业务条件更新：优先使用写入时的业务字段谓词。当根据事务外部读取的数据计算新值时，包含检查相关原始值或有效版本标识符的谓词，以避免覆盖并发更改。当单个条件语句足够时，不要添加显式事务。参见 示例。
• 时间戳检查：如果没有合适的业务字段，则仅在每次相关修改都会更改 updated_at 且旧值在验证期间无法重用时，才匹配原始的 updated_at。在读取、写入、序列化和绑定中保留毫秒。否则，使用不可重用的版本标识符或其他并发机制。参见 示例。
• 引用完整性：使用条件更新、唯一性和必要的事务来协调引用创建/更改与目标删除/失效。参见 示例。
• 更新结果：确定驱动程序报告的是匹配行还是更改行，然后评估所需的写入是否成功。谓词未满足不是成功；零更改行本身不能证明谓词未满足。参见 示例。
• 写入顺序：竞争事务应使用一致的表/记录顺序。SQL 文本顺序不建立实际的锁顺序。参见 示例。

数据库安全

• 将应用程序数据作为参数绑定，而不是将其拼接到 SQL 中。
• 通过代码白名单将动态表/列名和排序方向映射到 SQL 结构中；值占位符无法替代它们。

性能优化

• 请求往返：减少每个业务操作的数据库请求，同时保持正确性和整体性能。

索引优化

• 索引重用和更改：跨主要查询重用索引。当现有路径满足性能需求时，不要仅为新的 SQL/谓词添加索引。在合并或替换索引之前，检查其他查询和唯一约束。参见 示例。
• 低基数数字段：不要仅仅因为不同值少就拒绝索引。考虑目标分布、组合过滤器、排序、覆盖索引的收益、表查找和写入成本。参见 示例。
• 前缀索引：根据实际数据选择字符串前缀长度。唯一前缀索引可能拒绝共享前缀的不同完整值，并且不能替代完整值的唯一性。参见 示例。
• 复合索引：平衡过滤、左前缀重用、排序和分组。等值列通常位于范围列之前；不要普遍按选择性排序。参见 示例。
• 排序成本：filesort 本身并不意味着索引不好。比较添加索引的排序、扫描和维护成本。参见 示例。
• 索引列和表达式：对于每日过滤，优先使用直接的时间范围谓词。如果需要表达式，评估匹配的函数索引或生成列索引。参见 示例。
• 前置通配符：普通 B 树索引通常不能将前置通配符搜索转换为有效的范围扫描。全文或专用搜索必须保留原始的匹配语义。参见 示例。

查询优化

• 批量查询：使用连接或批处理 ID 列表替换可合并的逐行查询，以降低网络开销。
• 物化视图和缓存：隔离高计算成本的查询或复杂聚合到周期性物化的结果（例如汇总表、Redis 缓存）中。在写入时更新，或在源数据更改时失效并后台重建。
• 大型聚合和分页：当聚合大型数据集时，考虑物化视图或异步计算，以避免阻塞生产读取。对于排序分页，基于游标的分页比基于偏移量的分页更可扩展。

部署和迁移

• 使用数据库迁移工具（如 Flyway 或 Liquibase）管理模式更改。迁移脚本应是确定性的、幂等的，并作为代码检入。
• 在生产迁移之前，在暂存环境中验证 DDL，特别注意 ALTER TABLE 操作（尤其是涉及大型表并创建新列的 ALGORITHM=INPLACE 和 LOCK=NONE 等约束）。
• 将业务逻辑与模式迁移分离；迁移应仅处理结构更改。
• 对于大型数据回填，使用小批次脚本，并包含可恢复性逻辑（例如，检查点、错误日志记录、手动回滚过程）。