摘要
上两篇写了部署和 AI 直连。这篇调优。200 万行,报表慢。我造了 200 万行,揪出慢查询,sys_hypo 试水,再落地索引。region 查询 655.9→181.9 ms。amount 查询 583.4→257.4 ms。复合索引 174.9→42.7 ms。大排序 6.3 秒→2.5 秒。数字全是机器跑的,截图在下边。
一、为什么调优
订单表 1 万行,全表扫眨眼完。半年后 200 万行,扫一次 176 MB,报表卡。走没走索引,EXPLAIN 里全写着。
我定了顺序。采集,诊断,模拟,落地。sys_stat_statements 采集,EXPLAIN ANALYZE 诊断,sys_hypo 模拟,CREATE INDEX 落地。
二、全景图
调优路径就一条,慢查询从哪来,索引怎么选,收益多大,都在图里。下面每个场景都是真实执行。
三、环境检测与版本确认
动库前先摸底。OS,CPU,内存,Docker,金仓版本,参数。2 vCPU,1.9 GiB,AMD EPYC 7K62。金仓 V009R001C010,容器,54321。
参数记下来。shared_buffers 128 MB,work_mem 4 MB,effective_cache_size 4 GB。后两个要动。1.9 GiB 机器,25% 内存是 512 MB,现在给 128 MB,差得远。
四、构造 200 万行样本库
原表 1 万行,没效果。新建 opt schema,建 t_order_big,六字段。status 90/10,region 八城市,create_time 撒一年。generate_series 灌 200 万行。
校验一遍。200 万行,用户 199986,N 1799971,S 200029,金额 0.02-99998.97。分布对得上。
五、诊断：无索引状态下的慢查询
表没索引。跑三条。region 等值,amount 范围,ORDER BY。报表最常见的三种。
region='xian',Seq Scan 扫全表,滤 185 万行,655.928 ms。amount>90000,全表扫,583.397 ms。ORDER BY 取 20 条,810.722 ms。176 MB 的表,扫一次 600-800 ms。报表一天几十次,还并发,就卡在这。
sys_stat_statements track=all 记账。region 1222.65 ms,amount 899.92 ms,GROUP BY 1003.15 ms。
六、假设索引模拟：不建索引先看效果
直接建怕选错。sys_hypo 给假设索引,不落盘,只算计划。region,amount 各建一个,EXPLAIN 看变不变。
假设索引一挂,region 从 Seq Scan 变 Bitmap Heap Scan,cost 42029→3015。amount 也变 Bitmap,cost 4623。没碰真实数据,计划直接变。sys_hypo 只认当前会话,用完 reset。
七、落地真实索引
模拟过了,建。region,amount 各一个 btree,ANALYZE,重跑。
建完数字降下来。region Bitmap Heap Scan 181.874 ms,快 3.6 倍。amount 257.369 ms,快 2.3 倍。idx_region 53 MB,idx_amount 59 MB。索引和表 1:1,读性能换存储。
八、复合索引与覆盖索引
单列建完,再试两件。复合,region+amount 一起查,单列要回表。覆盖,查询列全装进索引,省回表。
复合最惊艳。region='xian' AND amount>90000,之前只有 idx_region,174.882 ms。建 (region,amount),42.704 ms,4.1 倍。收益最大一笔。
覆盖 (region,status) 也建了。SELECT region,status WHERE region='xian' AND status='N',还是 Bitmap Heap Scan,177.688 ms。没触发 Index Only Scan,收益一般。覆盖索引不是啥场景都灵。
九、参数调优：work_mem 与大排序
索引管检索,排序另一回事。ORDER BY amount DESC 大排序,内存不够落磁盘。work_mem 4 MB,调到 64 MB,同一查询对比。
4 MB 时走 Index Scan Backward,6282.626 ms。64 MB 时 2471.416 ms,2.5 倍。work_mem 只对当前会话,SET 完就生效。shared_buffers 128 MB 偏小,建议 512 MB,这次没动容器。
十、日常运维收尾
VACUUM (ANALYZE),回收死元组,刷统计。死元组 0,表 417 MB（含索引）。pg_stat_user_indexes 里,idx_region 扫 2 次,idx_amount 和 idx_region_amount 各 1 次,主键 0 次。
十一、数据汇总
|
维度 |
调优前 |
调优后 |
提升 |
出处 |
|
region 等值查询 |
655.928 ms（Seq Scan） |
181.874 ms（Bitmap） |
3.6× |
图 4 / 图 6 |
|
amount 范围查询 |
583.397 ms（Seq Scan） |
257.369 ms（Bitmap） |
2.3× |
图 4 / 图 6 |
|
region+amount 复合查询 |
174.882 ms |
42.704 ms |
4.1× |
图 7 |
|
大排序 ORDER BY |
6282.626 ms（work_mem=4MB） |
2471.416 ms（64MB） |
2.5× |
图 8 |
|
索引存储开销 |
— |
idx_region 53 MB + idx_amount 59 MB + 复合 73 MB |
≈185 MB |
图 6 |
|
sys_hypo 模拟 |
Seq Scan cost 42029 |
Bitmap cost 3015 起 |
计划级预演 |
图 5 |
十二、写在最后
四步走完,账算清了。慢查询一条条采,瓶颈在 EXPLAIN 现形,sys_hypo 试错没花钱,真索引 2.3× 到 4.1×。没编数字,每条 SQL,每个耗时,机器吐出来啥就是啥,附录命令重跑能复现。
国产库内核一直在追,EXPLAIN,假设索引,统计视图,一样不缺。有台能跑 Docker 的机器,照着附录走,两三小时跑通。200 万行是起点,千万,亿行,才见真章。
转载自 CSDN-专业IT技术社区
原文链接：https://blog.csdn.net/cccxcmm/article/details/163452382
