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

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

输入要求

必填信息

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

可选信息

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

执行流程

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

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

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

默认推荐：

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) |

第四步：索引设计

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

索引策略决策树

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

基础规则

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

联合索引字段顺序

推荐顺序：

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

示例：

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

推荐索引：

KEY idx_orders_tenant_status_created (tenant_id, status, created_at)

注意：

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

查询场景策略

覆盖索引策略

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

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) | 冗余 |

删除索引前必须：

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

写入成本评估

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

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

CMS 常见索引策略

EXPLAIN 验收标准

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

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

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

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

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

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

第七步：慢查询诊断

慢查询必须先拿证据：

原始 SQL
参数样例
表行数
EXPLAIN 结果
相关索引

诊断重点：

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

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

DDL/数据修复必须输出：

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

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

第九步：记录到项目记忆

将以下内容沉淀：

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

输出格式

# 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 场景必须说明表前缀和官方数据访问层

失败回退机制

关联 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 约定和迁移记录

mysql-database.md 13 KB Istoric Crud