把 2 小时的手写 SQL 优化到 20 ms 的一次真实踩坑复盘
原创
把 2 小时的手写 SQL 优化到 20 ms 的一次真实踩坑复盘
原创
一、协作背景
项目：内部工单系统（SpringBoot 3.2 + MyBatis-Plus + PostgreSQL 15）
场景：生产环境工单列表接口 95th 延迟 2.1 s，PO 直接炸群。
目标：把延迟降到 200 ms 以内，不改表结构，不停服。
AI 工具：JetBrains IDE 内嵌 CodeBuddy（2024.1 EAP，本地模型：CodeLlama-13B-Instruct，温度 0.2）。
二、协作流程图（Mermaid）
flowchart TD
A[人工抓慢 SQL] -->|explain| B[Seq Scan 600 w+]\nB --> C{CodeBuddy 会话}\nC --> D[提示缺失索引]\nC --> E[重写窗口函数]\nC --> F[建议覆盖索引]\nD & E & F --> G[本地 Benchmark]\nG -->|20 ms| H[合并 MR]\nH --> I[生产灰度验证]三、关键步骤与 AI 交互日志
时间 | 人工动作 | CodeBuddy 输出 | 采纳/弃用 | 备注 |
|---|---|---|---|---|
10:03 | 贴慢 SQL + explain analyze | “Seq Scan on工单表 cost=12w.. 建议先在(org_id, status, create_time)建联合索引” | 采纳 | 原索引只有(org_id) |
10:07 | 问“还能再快吗？” | “把窗口函数 row_number() over(partition by org_id order by create_time desc) 换成 lateral join 子查询，可完全走索引” | 采纳 | 第一次听说 lateral join |
10:15 | 让 AI 生成 lateral 版本 | 直接给出 18 行重构 SQL，附带 /+ IndexOnlyScan(wo idx_xxx) / 提示 | 采纳 | 语法校验一次过 |
10:18 | 本地 100 k 数据 benchmark | 报告：原 SQL 2 100 ms → 新 SQL 19 ms | — | 用 JMH 跑 5 轮 |
10:25 | 担心回表 | AI 提示“把 select 列全部纳入索引，实现 Index-Only-Scan” | 采纳 | 最终索引 3 + 2 列 |
10:30 | 生成 flyway 脚本 | 一键生成 V20240528__add_covering_index.sql | 采纳 | 文件名自动带时间戳 |
10:35 | 问“会不会写放大？” | 给出 pg_stat_user_indexes 观察 SQL，阈值 < 0.2 可接受 | 采纳 | 后期监控用 |
四、最终 SQL 对比
-- 旧（2 s+）
SELECT *
FROM (
SELECT *, row_number() OVER (PARTITION BY org_id ORDER BY create_time DESC) AS rn
FROM work_order
WHERE status = 'OPEN'
) t
WHERE rn = 1;
-- 新（20 ms）
SELECT wo.*
FROM org o
JOIN LATERAL (
SELECT *
FROM work_order
WHERE org_id = o.id AND status = 'OPEN'
ORDER BY create_time DESC
LIMIT 1
) wo ON true;五、效果量化
指标 | 优化前 | 优化后 | 降幅 |
|---|---|---|---|
95th 延迟 | 2 100 ms | 19 ms | 99.1 % |
平均 CPU | 62 % → 11 % | ||
索引大小 | 0 → 380 MB | ||
磁盘读 | 2.4 GB/小时 → 14 MB/小时 |
六、踩坑反思
- 人眼盲区：看到 row_number 就默认要开窗，没想过 LATERAL 子查询能走“索引+limit”快速短路。
- AI 价值：CodeBuddy 直接给出“覆盖索引 + lateral”组合技，省去翻 3 本 PG 大部头时间。
- 验证纪律：不把 AI 当真理，本地 Benchmark + explain (buffers, timing) 双保险。
- 监控闭环：把 AI 建议的 pgstat* SQL 做成 Grafana 面板，一周后索引利用率 0.17，符合 < 0.2 预期，否则回滚。
七、结论
这次协作让我重新认识到：AI 不是替代 DBA
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
