别让报表 Agent 的 SQL 拖慢收银交易表
先定可量化目标、读执行计划,把报表迁到 PostgreSQL 物化视图,最后才考虑索引。
要点速览
- 从可量化的目标和执行历史出发,而不是随手调优。
- EXPLAIN ANALYZE 会真正执行语句:ROLLBACK 只撤销副作用,客户数据库承受的负载照样发生。
- 让 AI 报表读取物化视图,约定数据可以滞后多久,选好刷新时间,并监控视图的数据时效。
设想你部署报表智能体的第一周,客户是一家零售连锁企业。每当有人问“本周各门店营业额”,Agent 就生成 SQL,直接扫描 orders 表,而收银机一整天都在往这张表里写数据。演示时一切顺利。上了生产环境,客户的运维团队开始追问:数据库怎么忙了这么多?
问题不在提示词。Agent 不知道哪些表服务于交易、哪些表用于分析,这一点得由你替它弄清楚。本文介绍一套可以在笔记本上用 PostgreSQL 实操的流程:定目标,读执行计划,把报表迁到物化视图上,最后才考虑索引。
文中的表结构是假设的简化版:一张 orders(id, store_id, created_at, amount, is_paid) 表。你需要在本地运行 PostgreSQL,有建表权限,最好再有一份足够大的数据副本,让执行计划接近生产环境的情况。
第 1 步:要达到什么数字,哪些语句值得改?
Oracle 把 SQL 调优定义为一个迭代过程,目标必须具体、可量化、可实现。所以在敲任何命令之前,先和客户一起把目标写下来。举个假设的例子:“周营业额报表在两秒内返回,且运行报表的 Agent 不拖慢订单写入”。
接着找出值得改的语句。Oracle 的文档建议查看执行历史,找出占系统负载和资源最多的语句,而不是随意调优。
有了 Agent,执行历史通常会暴露一个现象:少数几种聚合查询模式反复出现,只是参数略有不同。它们就是首批候选。
**检查:**你手里有一份负载最重的语句短名单,以及一个客户认可的目标数字。
第 2 步:真正执行之前,先读执行计划
按照 Oracle 的说法,执行计划是手动调优的主要诊断工具。EXPLAIN 只展示计划、不执行语句,因此是在客户数据库上最安全的一步。
EXPLAIN
SELECT store_id, date_trunc('day', created_at) AS day, sum(amount)
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY 1, 2;
要拿到真实数字,就得用 EXPLAIN ANALYZE。PostgreSQL 文档写得很明确:这条命令会真正执行查询,所有副作用照常发生。对于写数据的语句,PostgreSQL 建议包在事务里,执行后回滚。
但别把回滚当成安全保障。回滚只撤销数据变更;一条重量级聚合查询仍会完整跑完,照样压在客户的交易数据库上。
对下面这种只读查询,包一层事务毫无帮助。应当在数据副本上运行,或者在运维团队同意后,于业务低峰期运行。
EXPLAIN (ANALYZE, BUFFERS)
SELECT store_id, date_trunc('day', created_at) AS day, sum(amount)
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY 1, 2;
BUFFERS 选项会报告计划中每个节点的 I/O。PostgreSQL 18 在使用 ANALYZE 时会隐式开启它,但显式写出来,对日后读你脚本的人仍有帮助。
第 3 步:看行数,别看成本
新手常把 cost 列当成毫秒来比较。它不是。PostgreSQL 文档指出,成本单位是任意的,并不对应真实时间;最该检查的是估算行数与实际情况是否接近。
假设扫描 orders 的节点估算 1,000 行,实际返回 900,000 行。规划器按小数据集选了策略,结果要处理的数据量大了 900 倍,该节点之上的每个决策都建立在错误的数字上。这是首先要记下的问题,还轮不到考虑索引。
**检查:**对名单上的每条语句,你都清楚哪个节点读取的缓冲区最多,哪个节点的估算偏差最大。
第 4 步:给 Agent 一份汇总数据来读
Oracle 把缺少访问结构(如索引和物化视图)列为 SQL 变慢的典型原因。对 AI 报表来说,物化视图通常是更好的选择:Agent 读取预先聚合好的表,而不是反复冲击交易表。
Microsoft 在描述物化视图(Materialized View)模式时强调了一个很有价值的特性:视图及其数据完全可丢弃,因为随时可以从源数据重建。它是一种特殊形式的缓存,所以你可以试验、出错、重建,而不会碰到客户的源数据。
CREATE MATERIALIZED VIEW report_daily_store_sales AS
SELECT store_id,
date_trunc('day', created_at) AS day,
sum(amount) AS revenue,
count(*) AS order_count
FROM orders
GROUP BY 1, 2;
这一步最难的问题与 SQL 无关。Microsoft 建议先确定数据可以接受滞后多久,再在事件驱动、定时和手动刷新之间做选择。
如果区域经理只在每天早上看报表,数据滞后几个小时没问题;如果他们白天要靠它调货,就不行。这个问题要在客户需求调研阶段问清楚,而不是靠猜。
第 5 步:刷新时不把读者锁在门外
普通的 REFRESH MATERIALIZED VIEW 在运行期间会阻塞对视图的读取,也就是说 Agent 得等着。CONCURRENTLY 方式刷新视图时不会锁住并发的 SELECT,但要求有一个 UNIQUE 索引,并且同一时间只允许一个刷新在运行。
CREATE UNIQUE INDEX report_daily_store_sales_key
ON report_daily_store_sales (store_id, day);
REFRESH MATERIALIZED VIEW CONCURRENTLY report_daily_store_sales;
一个容易踩的坑:PostgreSQL 文档要求这个索引只能使用普通列,不能是表达式索引,也不能带 WHERE 子句。上面的例子中,date_trunc 已经写在视图定义里,day 是视图的普通列,所以索引有效。如果把 date_trunc 放进索引,并发刷新就无法执行。
别忘了这个视图是从 orders 构建的:每次完整刷新都会针对交易表重新执行视图定义中的聚合,Microsoft 也指出刷新需要消耗计算资源。因此,在第 4 步约定的滞后阈值允许范围内尽量少刷新,并安排在低峰时段,而不是营业高峰。
索引能让 Agent 读得更快,但并非没有代价。Microsoft 指出,维护视图上的索引会给每个刷新周期增加写入成本。只保留报表查询真正用到的索引。
**检查:**把 Agent 的查询改为指向视图,重新运行 EXPLAIN,与第 3 步的缓冲区数量做对比。
第 6 步:悄无声息过期的视图是最危险的故障
Microsoft 警告,一旦触发器或刷新任务漏跑,视图会悄无声息地返回过期结果。对 Agent 而言,这比明显的报错更糟:它会用昨天的数字流畅作答。建议记录最近一次刷新的时间,当视图的数据时效超过约定阈值时发出告警。
一个简单的做法:刷新任务把完成时间写入一张小日志表,再由定期检查把这个时间与第 4 步的阈值做比较。你也可以让 Agent 读取这个时间戳,在回答中注明“数据截至……”,以免误导用户。
什么时候该考虑索引表?
有些查询不是聚合,而是按主键以外的键查找,比如按客户编码查订单。Microsoft 的索引表(Index Table)模式在架构层面解决这类问题,做法是单独构建一张索引表。
把它迁移到 PostgreSQL 索引上只是类比,并非 PostgreSQL 的规则,但该模式中有两条建议值得前线部署工程师(FDE)记住。
第一,不要为应用从不运行、或只偶尔运行的查询预先建索引表。每张索引表都会增加维护成本,因此需要定期审查,没人用了就删掉。放到 Agent 场景里,只有当第 1 步的执行历史显示某种查询模式确实反复出现时,才添加索引。
第二,低选择性的键(例如布尔列 is_paid)是糟糕的候选。索引表照样要付出全部存储和吞吐成本,却几乎缩小不了查询范围。只有两个取值的标志位只能把表一分为二,帮不了你快速找到少数几行。
常见错误
最常见的错误是在生产环境运行 EXPLAIN ANALYZE,以为有 ROLLBACK 就安全,负载却照样落在客户数据库上。第二个错误是比较两个计划的 cost,并以毫秒为单位下结论。
第三个错误是建了物化视图,却没有人约定可接受的滞后程度,于是刷新全凭感觉,出了故障也没有任何告警。
反过来,也别急着给 Agent 筛选过的每一列都建索引。每个索引都是客户交易系统要一直承担的写入成本。
如何把这项技能写进简历
如果 FDE 岗位描述提到直接对接客户数据库或生产环境性能,这项技能值得在面试中重点展示。
简历上别写“精通 SQL”。写一行结构清晰的描述:可量化的目标、负载最重的语句、你做的改动(物化视图、UNIQUE 索引、并发刷新),以及你如何监控数据滞后。
一个小仓库,包含脚本、优化前后的执行计划,以及一份解释为何不给布尔列建索引的 README,比任何证书都更有说服力。
Agent 写 SQL 的能力会越来越强。但弄清客户数据库在什么时间为谁服务、Agent 为什么不该碰它,依然是坐在客户运维团队旁边的 FDE 的工作。
这篇文章对你有帮助吗?
感谢你的反馈!