用 AI 写 SQL 与迁移：DDL 审查、索引用意与回滚预案

让 AI 写 SQL 很快，风险也集中在这里：它能写出语法正确、在测试库跑得通、到生产上锁表十分钟的语句。SQL 与迁移（migration）的产出必须当成一次线上变更来验收——语句要审查，索引要有用意，回滚要提前写好。

第一步：把上下文喂够，把约束说死

不给表结构、数据量、MySQL 版本就问「帮我加个索引」，得到的建议无法评估。可套用的提示词：

【环境】MySQL 8.0.36，orders 表约 4200 万行，日均新增 30 万
【现状】表结构与现有索引见下（SHOW CREATE TABLE 原文）
【目标】查询「某用户最近 20 笔已支付订单」要走上索引，接口 P99 从 800ms 降到 100ms 内
【约束】必须在业务低峰执行，允许写窗口 ≤ 2 秒，不接受长时间锁表
【要求】输出 4 部分：1) DDL 语句（含字符集与注释）
        2) 为什么这个索引能命中该查询，附 EXPLAIN 预期输出
        3) 回滚脚本  4) 上线步骤与验证命令
【禁止】不要编造不存在的参数，涉及算法与锁行为请注明「以官方文档为准」

三件必须交代清楚的事：当前表结构（SHOW CREATE TABLE 原文）、数据量级、可接受的锁表与耗时。少任何一件，AI 只能给通用答案。

DDL 审查清单

检查项	关注点	反例
主键	是否自增整型主键；避免随机字符串主键导致页分裂	`id VARCHAR(36)` 存 UUID
字段类型	容量与语义匹配；金额不用浮点	金额用 `FLOAT`，用户量增长后 `INT` 溢出
字符集	统一 `utf8mb4`，避免 emoji 与四字节字符插入失败	建表用 `utf8`，插入 emoji 报 `Incorrect string value`
排序规则（collation）	关联字段的排序规则必须一致，否则索引失效	一表 `utf8mb4_general_ci`、另一表 `utf8mb4_0900_ai_ci`
默认值与 NULL	新列必须有默认值或允许 NULL，否则加列会阻塞	`ADD COLUMN status INT NOT NULL` 无默认值
索引数量	单表索引不宜过多，写放大明显；注意冗余索引	已有 `(a,b)` 又加 `(a)`
联合索引顺序	等值在前、范围在后，遵循最左前缀	`(created_at, user_id)` 却按 user_id 等值查
时间字段	用 `DATETIME`/`TIMESTAMP`，明确时区约定；`created_at`/`updated_at` 必备	用字符串存时间
大表变更	大表加列/加索引优先在线方式，算法与锁语义以官方文档为准	4000 万行表直接 `ALTER TABLE ... ADD INDEX`

加列与加索引的写法（ALGORITHM/LOCK 的支持范围随版本与操作类型不同，以官方文档为准）：

-- 加列：给默认值，避免 NOT NULL 无默认值造成的重建
ALTER TABLE `orders`
  ADD COLUMN `channel` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '下单渠道',
  ALGORITHM=INPLACE, LOCK=NONE;

-- 加联合索引：以查询的等值列在前、排序列在后
ALTER TABLE `orders`
  ADD INDEX `idx_user_status_created` (`user_id`, `status`, `created_at`),
  ALGORITHM=INPLACE, LOCK=NONE;

如果目标版本不支持在线方式，或表太大无法承担风险，就改用在线 DDL 工具（如 pt-online-schema-change、gh-ost）配合主从切换；工具的具体参数与限制同样以官方文档为准。

索引用意与 EXPLAIN 验证

每条索引都要能回答「为哪条查询服务」。索引加上去不是结束，要用执行计划验证它真的被用上：

EXPLAIN SELECT id, order_no, amount
FROM orders
WHERE user_id = 1024 AND status = 2
ORDER BY created_at DESC
LIMIT 20;

验收标准：type 不是 ALL，key 命中 idx_user_status_created，rows 估算在几十量级，Extra 里不出现 Using filesort、Using temporary。同时在目标索引存在与不存在两种情况下各跑一次 EXPLAIN，对比 rows 与 Extra——AI 说的「这个索引能命中」只有执行计划能证实。

ANALYZE TABLE orders;                  -- 统计信息过期会让优化器选错索引
SHOW INDEX FROM orders;                -- 确认索引真的建上、列序符合预期

迁移要能回滚，上线要能分批

迁移脚本必须成对交付：升级脚本 + 回滚脚本 + 验证命令。要清楚哪些操作可逆、哪些不可逆：

操作	可逆性	回滚方式
加索引	可逆	`DROP INDEX`
加列	可逆（前提是没有业务依赖）	`DROP COLUMN`，须确认无写入
改字段类型/字符集	高风险	备份恢复或反向 `MODIFY`，可能重建表
删列、删表、删数据	不可逆	只能靠备份或向前修复
数据回填	部分可逆	用旧值再回填回去，需保留原始值

删列的回滚脚本要写成显式提醒，而不是一行可执行语句：

-- 回滚 1（安全）：仅去掉索引
ALTER TABLE `orders` DROP INDEX `idx_user_status_created`, ALGORITHM=INPLACE, LOCK=NONE;

-- 回滚 2（危险）：删列会永久丢数据，执行前必须确认
-- 1) 双写已停止 2) 全量备份已完成 3) 回滚窗口内无新增依赖
-- ALTER TABLE `orders` DROP COLUMN `channel`;

数据回填不要一次 UPDATE 全表：

-- 分批回填，避免大事务、长时间持锁与主从延迟
UPDATE `orders` SET `channel` = 'web'
WHERE `channel` = '' AND `id` > 0 AND `id` <= 200000
LIMIT 2000;   -- 循环推进 id 上界，每批之间留间隔

多版本共存期的上线顺序固定成六步，任一步出问题都能停住：

先加列（可空或有默认值），旧代码不受影响；
双写：新代码同时写新旧字段，读仍走旧字段；
回填：分批把历史数据补齐，并校验条数一致；
切读：读切换到新字段，观察日志与指标；
停双写：确认新字段写入稳定后，移除写旧字段的逻辑；
删旧列：间隔一个版本周期后，用单独一次迁移删除。

常见坑

在测试库跑通就上线：几十行的测试表和生产 4000 万行表，锁表时间差几个数量级。
只看语法不看锁语义：ALGORITHM、LOCK 的可用范围随版本和操作而变，必须查官方文档确认。
字符集不统一：库里混着 utf8 与 utf8mb4、排序规则不一致，联表时索引失效或直接报错。
索引越加越多：每条索引都要维护，写性能和磁盘都吃紧；上线前检查冗余索引。
ORDER BY 字段拼接：占位符不能用于标识符，排序字段只能走白名单映射。
用 SELECT COUNT(*) 判断存在性：大表上代价极高，判断存在用 SELECT 1 ... LIMIT 1。
回滚脚本事后补写：等出事才想怎么回滚，通常已经没有回滚窗口了。
迁移不可重复执行：脚本要能判断幂等（IF NOT EXISTS 或迁移版本表），否则重跑即报错。

小结：让 AI 写 SQL 与迁移，价值在于快速产出「语句 + 索引用意 + EXPLAIN 预期 + 回滚脚本」这一整套，而不是一段能跑的 DDL。DDL 逐项过审查清单（类型、字符集、排序规则、默认值、索引顺序），每条索引都要落到具体查询并用执行计划验证；迁移按「先加列、双写、回填、切读、停双写、删旧列」推进，可逆操作写回滚语句，不可逆操作写明前提并依赖备份。

笔记加载中…

标签：#AI #辅助开发 #工程实践

返回知识库