db-design

name	db-design
description	数据库工程师技能 - Schema设计、索引优化、并发安全、数据库审计。当你涉及数据库表/字段/索引/SQL/迁移/MCP数据库操作、建表改表、写查询时必须使用此技能。即使用户只是说"加个字段"或"查下数据"，也应触发。

数据库工程师技能 (Database Engineer Skill)

快速规则（日常开发时自动加载，只需读到这里）

[DB核心清单] ① 新增前list_table+desc_table查重，字段必须有注释+默认值 ② 删除前Grep零引用+read_query确认数据可弃 ③ 每表必有id/created_at/updated_at [查询三禁] ❌SELECT * ❌字符串拼接SQL ❌无WHERE的UPDATE/DELETE [并发铁律] 扣减用原子操作(WHERE count > 0)，事务范围最小化，事务内禁调外部HTTP

涉及数据库操作时，强制遵守：

1. 新增前查重：list_table+desc_table确认表/字段不重复，新字段必须有注释+默认值+NOT NULL/NULL约束
2. 删除前验证：Grep全项目确认字段零引用+read_query确认数据可丢弃，先代码停用→确认→再删字段
3. 命名规范：表名snake_case复数(users)，字段snake_case，布尔is_/has_前缀，时间_at后缀
4. 查询安全：❌禁止SELECT *（新增字段意外暴露敏感数据） ❌禁止字符串拼接SQL（SQL注入可导出全库） ❌禁止无WHERE的UPDATE/DELETE（一次执行影响全表无法回滚）
5. 并发安全：扣减用原子操作(WHERE count > 0)，事务范围最小化，事务内禁止调外部HTTP（外部超时=长时间持锁阻塞全库）
6. 索引原则：WHERE/JOIN/ORDER BY频繁字段建索引，复合索引遵守最左前缀，业务唯一字段加唯一索引
7. 必备字段：每张表必须有id/created_at/updated_at，软删除用deleted_at

---

完整审查流程（手动 /db-design 或专项审查时执行）

Phase 1: 数据库现状扫描（必须用MCP工具）

强制使用MCP工具执行以下操作：

1. list_table — 列出所有数据库表
2. desc_table — 逐个检查每张表的结构（字段/类型/约束/注释）
3. read_query — 检查数据量级、字段使用情况、索引使用率

扫描清单：

• 所有表及其字段列表、类型、约束、注释
• 表之间的外键/关联关系图
• 每张表的数据量（行数估算）
• 每个字段是否在代码中被引用（Grep项目代码确认）

Phase 2: 废弃字段与冗余检测

4. 废弃字段检测（最关键——解决"创建了没用的字段"问题）：

   • 用Grep搜索每个字段名在项目代码中的引用
   • 字段在代码中零引用 = 废弃字段，标记待清理
   • 字段只在旧版迁移中出现 = 遗留字段，评估是否可删

5. 冗余检测：

   • 相同含义不同命名的字段（如user_id和uid和userId）
   • 同一数据存储在多张表中（反范式是否有必要？）
   • 可计算字段（如total=price×quantity，是否需要物理存储？）
   • 未使用的表（零引用）

6. 数据一致性检查：

   • read_query检查：是否有NULL值在NOT NULL应为的字段
   • 外键关联的数据是否一致（孤儿记录）
   • 枚举字段是否有非法值

Phase 3: Schema设计审查

7. 命名规范检查：

   • 表名：小写+下划线(snake_case)，复数形式(users不是user)
   • 字段名：小写+下划线，语义明确（created_at不是ct）
   • 主键：统一id或<table>_id
   • 外键：<关联表单数>_id（如user_id）
   • 布尔字段：is_/has_前缀（is_active不是active）
   • 时间字段：_at后缀（created_at/updated_at/deleted_at）
   • ❌ 禁止：中文字段名、拼音缩写、无意义缩写（tp/st/flg）

8. 字段类型审查：

   • 字符串：长度是否合理（名字VARCHAR(50)而非VARCHAR(255)）
   • 数字：金额用DECIMAL不用FLOAT（浮点精度问题）
   • 时间：统一用DATETIME/TIMESTAMP，时区处理是否一致
   • 大文本：TEXT/BLOB是否应该拆到单独表
   • 枚举：ENUM vs TINYINT+注释，哪个更适合当前场景
   • JSON：是否滥用JSON字段（无法索引、无法约束）

9. 表设计原则：

   • 每张表必须有主键（自增ID或UUID，不用复合主键做业务主键）
   • 必须有created_at和updated_at字段
   • 软删除优先（deleted_at）除非有明确理由物理删除
   • 单表字段数不超过30个，超过考虑拆表
   • 注释：每个表和每个字段都必须有中文注释说明用途

Phase 4: 索引优化

10. 索引审查：

    • WHERE条件中频繁出现的字段是否有索引
    • JOIN关联字段是否有索引
    • ORDER BY/GROUP BY字段是否有索引
    • 复合索引的字段顺序是否符合最左前缀原则
    • 是否有冗余索引（A+B的复合索引已包含单独A索引的作用）
    • 是否有从未使用的索引（浪费写入性能）

11. 索引设计原则：

    • 区分度高的字段优先建索引（性别字段建索引无意义）
    • 频繁更新的字段谨慎建索引（索引维护有成本）
    • 覆盖索引：查询字段都在索引中，避免回表
    • 前缀索引：长字符串字段只索引前N个字符
    • 唯一索引：业务上唯一的字段必须加唯一索引（不只靠代码校验）

Phase 5: 查询优化

12. 慢查询识别（Grep项目中的SQL语句）：

    • SELECT * → 只查需要的字段
    • 无WHERE的全表扫描
    • 子查询 → 改为JOIN或EXISTS
    • N+1查询：循环中逐条查询 → 批量查询
    • LIKE '%keyword%' → 考虑全文索引
    • 大偏移分页 OFFSET 10000 → 游标分页(WHERE id > last_id)
    • 未使用索引的JOIN（EXPLAIN检查）

13. 查询安全：

    • 所有查询必须参数化，禁止字符串拼接SQL
    • 用户输入直接进入ORDER BY/LIMIT/表名 = SQL注入
    • 批量操作有数量上限（防止一次DELETE百万行锁表）

Phase 6: 并发与事务安全

14. 并发安全检查：

    • 扣减操作（余额/库存/次数）是否用原子操作（UPDATE SET count = count - 1 WHERE count > 0）
    • 先SELECT再UPDATE的模式 → 是否有并发窗口导致超卖/超扣
    • 分布式锁：是否用了SELECT ... FOR UPDATE或Redis锁
    • 死锁风险：多表更新是否按固定顺序

15. 事务设计：

    • 事务范围是否最小化（不要把不相关操作放同一事务）
    • 长事务检测：事务中是否有外部HTTP调用（会长时间持锁）
    • 事务隔离级别是否合适（READ COMMITTED通常够用，SERIALIZABLE性能差）
    • 是否有事务嵌套导致的意外行为

Phase 7: 分表与扩展性

16. 分表评估（当单表超过500万行时考虑）：

    • 水平分表：按时间/用户ID/地域拆分
    • 垂直分表：大字段（TEXT/BLOB）拆到扩展表
    • 读写分离：主库写、从库读
    • 归档策略：历史数据定期归档到冷存储

17. 扩展性设计：

    • 新增字段是否向后兼容（有默认值、可为NULL）
    • 字段删除是否分步（先代码停用→确认无引用→再删字段）
    • Migration是否可回滚（每个UP都有对应DOWN）

18. 废弃字段/表检测：

    • 对每个表的每个字段，Grep项目代码确认是否有引用（查SQL语句/ORM映射/Model定义）
    • 零引用字段标记为待删除（废弃字段=浪费存储+误导开发者+增加Schema复杂度）
    • 删除前确认：① 不在任何代码/SQL/ORM中引用 ② 不在任何报表/导出中使用 ③ 备份数据后再删
    • 废弃表同理：确认无代码引用→备份→删除或归档

Phase 8: 输出报告

## 数据库审查报告

### 表结构概览
| 表名 | 字段数 | 行数 | 索引数 | 注释 |

### 废弃字段（零引用）
| 表名 | 字段名 | 类型 | 创建时间 | 建议操作 |

### 冗余/问题字段
| 表名 | 字段名 | 问题类型 | 描述 | 建议 |

### 索引优化建议
| 表名 | 当前索引 | 建议操作 | 原因 | 预期效果 |

### 慢查询风险
| 文件:行号 | SQL模式 | 问题 | 优化方案 | 优先级 |

### 并发安全风险
| 文件:行号 | 操作 | 风险 | 修复方案 | 严重度 |

### 命名规范违规
| 表/字段 | 当前名称 | 建议名称 | 原因 |

数据库开发规则（写数据库代码时强制遵守）

新增表/字段前必须执行

1. list_table 确认没有已存在的同功能表
2. desc_table 检查相关表是否已有类似字段
3. 新字段必须有注释、默认值、明确的NOT NULL/NULL约束
4. 新表必须有id/created_at/updated_at

删除字段前必须执行

1. Grep全项目确认字段零引用
2. read_query确认字段数据可丢弃（或已迁移）
3. 先在代码中移除引用 → 部署 → 确认无报错 → 再删字段

写SQL时强制规则

• ❌ 禁止SELECT *（返回冗余字段浪费带宽/内存，且新增字段会意外暴露敏感数据）
• ❌ 禁止字符串拼接SQL（一条恶意输入可导出/删除全库，必须参数化）
• ❌ 禁止无WHERE的UPDATE/DELETE（一次执行影响全表数据，且无法回滚）
• ❌ 禁止在事务中调用外部HTTP（外部超时=长时间持锁，导致全库阻塞）
• ✅ 扣减操作必须原子化（WHERE count > 0）
• ✅ 批量操作有数量上限
• ✅ 每个Migration必须可回滚

约束

• 所有发现必须有 表名:字段名 或 文件:行号 引用
• 废弃字段检测必须用Grep+MCP双重验证
• 索引建议必须基于实际查询模式，不凭经验猜
• 分表建议必须基于实际数据量，不提前过度设计