# MySQL数据库 -- 数据建模、SQL安全与性能优化

面向 PHP/CMS/网站项目中的 MySQL 数据库设计、SQL 编写、索引优化、慢查询诊断、迁移回滚和数据安全场景。目标是在写代码前先明确表结构、访问路径、事务边界和验证方法，避免后期靠补丁修数据库问题。

## 输入要求

### 必填信息

1. **业务场景**：要存储/查询/统计/更新什么数据
2. **操作类型**：建表 / 改表 / 查询 / 写入 / 统计 / 慢查询优化 / 数据迁移
3. **数据规模**：当前行数、预计增长、单次处理量、读写比例
4. **数据库版本**：MySQL 5.7 / 8.0 / MariaDB，未知则标注假设
5. **调用环境**：PHP 原生 / CMS 数据访问层 / Laravel / ThinkPHP / WordPress / EmpireCMS

### 可选信息

- 现有表结构：`SHOW CREATE TABLE`
- 现有 SQL 和参数
- `EXPLAIN` / `EXPLAIN ANALYZE` 结果
- 慢查询日志片段
- 线上约束：是否允许停机、是否大表、是否可加索引
- 事务/一致性要求

## 执行流程

### 第一步：识别数据库任务类型

先判断当前任务属于哪一类：

| 类型 | 触发关键词 | 优先动作 |
| - | - | - |
| 表结构设计 | 建表、字段、模型、Schema | 输出 DDL + 字段说明 + 索引 |
| SQL 编写 | 查询、筛选、分页、统计 | 输出参数化 SQL + 绑定参数 |
| 慢查询优化 | 慢、卡、超时、EXPLAIN | 先读执行计划，再改 SQL/索引 |
| 数据迁移 | ALTER、迁移、导入、修数据 | 输出备份、分批、回滚方案 |
| CMS 数据库 | 表前缀、模型表、副表、options | 调用 CMS二次开发规范 |

### 第二步：确认版本、字符集和会话设置

默认推荐：

```sql
SET NAMES utf8mb4;
SET time_zone = '+08:00';
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
```

建库建表必须优先使用 `utf8mb4`，排序规则优先 `utf8mb4_unicode_ci` 或项目现有规则。

### 第三步：表结构设计

#### 基础规范

| 项 | 规则 |
| - | - |
| 引擎 | 默认 InnoDB；只有明确 CMS 历史约束时保留 MyISAM |
| 主键 | `BIGINT UNSIGNED AUTO_INCREMENT` |
| 字符集 | 表和字段统一 `utf8mb4` |
| 时间字段 | `created_at` / `updated_at` 使用 `DATETIME(3)` |
| 软删除 | 需要恢复/审计时加 `deleted_at` 并建索引 |
| 金额 | 使用 `DECIMAL`，PHP 侧按 string 处理 |
| JSON | MySQL 5.7+ 可用 JSON；旧版本用 TEXT + JSON 校验 |

#### 字段类型选择

| 场景 | 类型 |
| - | - |
| 主键/外键 | `BIGINT UNSIGNED` |
| 状态/枚举 | `TINYINT UNSIGNED` |
| 金额 | `DECIMAL(12,2)` 或按业务精度调整 |
| 短文本 | `VARCHAR(N)` |
| 长文本 | `TEXT` / `MEDIUMTEXT` |
| 布尔 | `TINYINT(1)` |
| 固定散列 | `CHAR(32)` / `CHAR(64)` |

### 第四步：索引设计

必须先根据查询路径设计索引，禁止“看起来可能会用”就加索引。

#### 索引策略决策树

1. **先列查询路径**：WHERE、JOIN、ORDER BY、GROUP BY、分页、唯一约束分别列出。
2. **再看数据分布**：字段基数、重复率、NULL 比例、冷热数据范围。
3. **再定索引类型**：主键、唯一索引、普通索引、联合索引、前缀索引、全文索引。
4. **最后用 EXPLAIN 验证**：确认 `key`、`type`、`rows`、`Extra` 是否符合预期。

#### 基础规则

| 规则 | 要求 |
| - | - |
| 命名 | 普通索引 `idx_表名_字段`，唯一索引 `uk_表名_字段` |
| 联合索引 | 遵循最左前缀，等值字段在前，范围/排序字段靠后 |
| 数量 | 普通业务表不超过 5 个，大表不超过 8 个 |
| VARCHAR 前缀 | utf8mb4 下索引长度注意 191 字符限制 |
| 禁止 | 低区分度字段单独建索引、频繁更新字段滥建索引 |

#### 联合索引字段顺序

推荐顺序：

```text
等值过滤字段 → 高区分度字段 → 范围字段 → 排序字段 → 覆盖返回字段
```

示例：

```sql
WHERE tenant_id = ?
  AND status = ?
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 20
```

推荐索引：

```sql
KEY idx_orders_tenant_status_created (tenant_id, status, created_at)
```

注意：

- 范围字段后面的字段通常不能继续用于高效过滤。
- `ORDER BY` 方向要和索引顺序兼容；MySQL 8.0 支持降序索引。
- 多租户表优先把 `tenant_id` 放在联合索引左侧。

#### 查询场景策略

| 场景 | 索引策略 | 禁忌 |
| - | - | - |
| 精确查询 | 唯一键或高区分度普通索引 | 给低区分度状态字段单独建索引 |
| 列表分页 | 过滤字段 + 排序字段联合索引 | 大 offset 深分页 |
| 深分页 | 使用游标/last_id 翻页 | `LIMIT 100000,20` |
| JOIN | 关联字段两侧类型一致，子表关联字段建索引 | 字段类型/字符集不一致 |
| ORDER BY | 让排序字段进入联合索引 | 依赖 filesort 处理大结果集 |
| GROUP BY | 分组字段建索引或先缩小数据集 | 全表分组统计 |
| LIKE 前缀 | `LIKE 'abc%'` 可用索引 | `LIKE '%abc%'` 期待普通索引命中 |
| 全文搜索 | FULLTEXT 或搜索引擎 | 在大 TEXT 上滥用 LIKE |
| 唯一约束 | 用唯一索引保障业务唯一性 | 只在代码层判断唯一 |

#### 覆盖索引策略

高频列表页可用覆盖索引减少回表：

```sql
KEY idx_article_cat_status_time_id (cat_id, status, publish_time, id)
```

适用：

- 列表页只返回少量字段。
- 查询频率高、返回行数少。
- 回表成本明显。

限制：

- 不要为了覆盖索引把大字段、TEXT、长 VARCHAR 塞进索引。
- 覆盖索引增加写入成本，必须说明收益。

#### 冗余索引治理

必须检查已有索引，避免重复：

| 已有索引 | 新索引 | 判断 |
| - | - | - |
| `(a,b)` | `(a)` | 通常冗余 |
| `(a,b)` | `(a,b,c)` | 可能可合并，需看查询 |
| `(a)` | `(b,a)` | 不等价 |
| `uk_email(email)` | `idx_email(email)` | 冗余 |

删除索引前必须：

1. 列出依赖 SQL。
2. 确认没有唯一约束语义。
3. 在测试环境用 EXPLAIN 对比。
4. 说明回滚 SQL。

#### 写入成本评估

新增索引不是免费优化，必须评估：

- INSERT/UPDATE/DELETE 是否变慢。
- 索引是否会放大磁盘占用。
- 频繁更新字段是否导致页分裂。
- 大表加索引是否需要在线 DDL。

#### CMS 常见索引策略

| CMS 场景 | 推荐策略 |
| - | - |
| 栏目内容列表 | `classid/status/newstime` 或项目等效字段联合索引 |
| 后台搜索 | 关键词字段不做 `%kw%` 普通索引幻想，改全文索引或搜索服务 |
| 订单/表单列表 | `site_id/status/created_at` 联合索引 |
| 软删除 | `deleted_at` 进入常用查询联合索引 |
| 多站点/多租户 | `site_id` / `tenant_id` 放联合索引左侧 |

#### EXPLAIN 验收标准

| 字段 | 目标 |
| - | - |
| `type` | 至少达到 `range`，高频点查应为 `ref` / `const` |
| `key` | 命中预期索引 |
| `rows` | 扫描行数与业务结果数量同量级 |
| `Extra` | 高频查询避免大规模 `Using temporary` / `Using filesort` |

如果不能满足，必须说明原因和替代方案。

### 第五步：SQL安全与参数绑定

所有外部输入必须参数绑定，内部接口也不例外。

| 场景 | 禁止 | 强制 |
| - | - | - |
| WHERE | `"id=$id"` | `WHERE id = ?` |
| LIKE | `LIKE "%$kw%"` | `LIKE CONCAT('%', ?, '%')` |
| IN | 手动拼 `IN(1,2,3)` | 动态占位符 `IN(?,?,?)` |
| ORDER BY | 直接拼用户输入 | 白名单字段映射 |
| LIMIT | 直接拼字符串 | 转 int 后限制最大值 |

### 第六步：事务、锁和批处理

- 多表写入必须显式事务：`begin → write → commit`，异常必须 rollback
- 单事务影响行数超过 10000 时必须分批
- 批量导入/导出/修复数据必须使用 `Init → Step → Poll` 渐进式防卡死架构
- 高并发扣减库存/余额类场景必须说明锁策略：行锁、乐观锁或唯一约束
- 禁止无 WHERE 的 UPDATE / DELETE

### 第七步：慢查询诊断

慢查询必须先拿证据：

1. 原始 SQL
2. 参数样例
3. 表行数
4. `EXPLAIN` 结果
5. 相关索引

诊断重点：

| EXPLAIN 字段 | 风险信号 |
| - | - |
| type | `ALL` / `index` 需警惕 |
| rows | 扫描行数远大于返回行数 |
| key | 未命中预期索引 |
| Extra | `Using filesort` / `Using temporary` |

优化顺序：改查询条件 → 调整索引 → 改分页/统计方式 → 拆表/缓存。不得先上缓存掩盖 SQL 问题。

### 第八步：迁移、备份与回滚

DDL/数据修复必须输出：

- 执行前 SELECT 验证
- 备份方案
- 执行 SQL
- 影响行数预估
- 回滚 SQL 或恢复方案
- 执行窗口和风险提示

大表 ALTER 必须提示使用在线 DDL 或 `pt-online-schema-change`，禁止业务高峰直接执行。

### 第九步：记录到项目记忆

将以下内容沉淀：

- 表结构和字段约定
- 索引设计理由
- 关键 SQL 和参数绑定方式
- 事务边界和回滚策略
- 慢查询诊断结论
- CMS 表前缀和特殊表规则

## 输出格式

```markdown
# MySQL数据库方案

## 1. 场景判断
- 类型: [建表/查询/优化/迁移/修复]
- 数据规模: [当前/预估]
- MySQL版本: [版本或假设]

## 2. 表结构/SQL方案
[DDL 或参数化 SQL]

## 3. 索引设计
| 查询路径 | 推荐索引 | 字段顺序理由 | 覆盖查询 | 写入成本 |
|----------|----------|--------------|----------|----------|

## 3.1 索引治理
- 冗余索引:
- 删除/合并建议:
- 回滚SQL:

## 4. 安全与事务
- 参数绑定:
- 事务边界:
- 锁策略:

## 5. 性能验证
- EXPLAIN:
- 预期扫描行数:
- 风险:

## 6. 迁移/回滚
- 备份:
- 执行:
- 回滚:

## 7. 验证证据
- 测试SQL:
- 预期结果:
- 影响行数:
```

## 质量标准

- 任何 SQL 涉及外部输入时必须参数绑定
- 任何 UPDATE / DELETE 必须带 WHERE，并说明执行前 SELECT
- 建表必须说明字符集、引擎、主键、时间字段、必要索引
- 索引必须对应具体查询路径，不能无理由添加
- 联合索引必须说明字段顺序理由：等值、区分度、范围、排序、覆盖
- 高频查询必须提供 EXPLAIN 验证，说明 `key/type/rows/Extra`
- 新增索引必须评估写入成本、磁盘成本和冗余索引
- 删除/合并索引必须提供依赖 SQL、风险和回滚 SQL
- 金额字段禁止 float
- 慢查询必须基于 `EXPLAIN` 或明确说明缺少执行计划
- DDL/数据修复必须有备份和回滚说明
- 大批量任务必须采用 Init → Step → Poll
- CMS 场景必须说明表前缀和官方数据访问层

## 失败回退机制

| 步骤 | 失败场景 | 回退策略 | 重试次数 | 最终处理 |
| - | - | - | - | - |
| 第一步：识别数据库任务类型 | 需求不清 | 按建表/查询/优化/迁移四类询问用户 | 1 | 输出假设并标注待确认 |
| 第二步：确认版本、字符集和会话设置 | MySQL版本未知 | 默认 MySQL 8.0，兼容 5.7 写法 | 1 | 标注版本假设 |
| 第三步：表结构设计 | 字段含义不足 | 输出最小字段集并列待确认字段 | 1 | 不生成破坏性 DDL |
| 第四步：索引设计 | 缺查询路径 | 只给必要主键/唯一索引 | 1 | 提示补充查询条件 |
| 第五步：SQL安全与参数绑定 | 框架访问层未知 | 使用 PDO 预处理示例 | 1 | 标注需替换为项目 ORM/CMS API |
| 第六步：事务、锁和批处理 | 一致性要求未知 | 按保守事务方案设计 | 1 | 标注并发风险 |
| 第七步：慢查询诊断 | 缺 EXPLAIN | 先输出需采集命令和初步假设 | 1 | 不宣称已完成优化 |
| 第八步：迁移、备份与回滚 | 无备份条件 | 阻止破坏性操作 | 0 | 要求先备份 |
| 第九步：记录到项目记忆 | 项目记忆不可用 | 输出本地记录清单 | 1 | 标注未沉淀 |

## 关联 reference

- **cms-development**（CMS二次开发）- PHP+MySQL CMS 场景必须联动 CMS 表前缀、官方数据访问层和缓存规则
- **code-generation**（代码生成）- 生成 PHP/后端代码时按本技能的 SQL 安全、事务和类型映射执行
- **code-review**（代码审查）- 审查 SQL 注入、慢查询、事务边界、DDL 风险和索引滥用
- **bug-diagnosis**（Bug诊断）- 数据库报错、死锁、慢查询、表不存在、字符集问题优先调用本技能
- **tech-selection**（技术选型）- 数据库版本、存储引擎、缓存和搜索方案选型
- **website-project**（网站项目总控）- 建站项目中的数据模型、内容表、表单提交、统计和上线迁移
- **task-decomposition-and-execution**（任务拆解与执行）- 大型数据库变更按 Wave 拆分并逐步验证
- **project-memory-management**（项目记忆管理）- 沉淀表结构、索引、SQL 约定和迁移记录