文章目录
- 每日一句正能量
- 1. 背景与问题
- 2. 环境与数据
- 3. 复现过程
- 3.1 第一步：从活动会话锁定 SQL
- 3.2 第二步：用 SQL 聚合统计确认影响面
- 3.3 第三步：读懂优化前执行计划
- 3.4 第四步：验证参数是否是主因
- 4. 方案实施
- 4.1 SQL 改写：让时间条件可索引
- 4.2 索引设计：匹配等值过滤、范围与排序
- 4.3 刷新统计信息并检查估算偏差
- 4.4 建立可重复的回归脚本
- 5. 结果对比
- 6. 风险与复盘
- 6.1 灰度与回退
- 6.2 需要重点防范的风险
- 6.3 本次诊断的可复用方法
每日一句正能量
“原本的我就很好，我只需要做减法，卸载不必要的负担，成为真实的自己。”
真正的成长不是不断添加技能、标签、成就，而是减去外界强加的期待、无谓的比较、内耗的执念。就像雕刻——去掉多余的石料，里面早已有完整的形象。
1. 背景与问题
某订单中心在月初促销结束后出现间歇性查询超时。客服工作台根据租户、订单状态和时间区间查询最近 50 条订单，平时响应在 100 ms 左右，故障时段平均耗时升至 4～6 s，个别请求超过 10 s。应用日志只记录了“数据库查询超时”，没有说明数据库是在等待锁、等待磁盘，还是单纯执行了低效计划。
最初团队提出三个猜测：一是促销期间写入量大，订单表膨胀导致磁盘读取增加；二是存在长事务阻塞查询；三是连接池参数异常。若直接根据猜测扩容或调整参数，既可能掩盖根因，也可能引入新的抖动。因此本次处理坚持一条原则：先建立“业务现象—活动会话—等待事件—SQL 画像—执行计划—数据对象”的证据链，再实施改动。
故障 SQL 的业务形态如下：
SELECTo.order_id,o.create_time,o.total_amount,i.sku_id,i.quantityFROMorders oJOINorder_item iONi.order_id=o.order_idWHEREo.tenant_id=:tenant_idANDo.status=:statusANDTO_CHAR(o.create_time,'YYYY-MM-DD')BETWEEN:begin_dateAND:end_dateORDERBYo.create_timeDESCFETCHFIRST50ROWSONLY;这段 SQL 在功能上没有错误，但把create_time包在TO_CHAR中，过滤条件难以直接利用以时间列为尾列的普通 B-tree 复合索引；同时，订单明细表在连接前没有受到“前 50 条订单”的有效约束，可能放大扫描和连接代价。
2. 环境与数据
复现实验环境使用 KingbaseES 测试实例，业务模型与生产保持同构：
| 项目 | 示例值 |
|---|---|
orders行数 | 约 1280 万 |
order_item行数 | 约 3560 万 |
| 单租户月订单 | 约 4.2 万 |
| 高峰并发 | 180～260 会话 |
| 原索引 | orders(tenant_id)、orders(create_time)、order_item(order_id) |
| 目标 SLA | 平均 < 100 ms，P95 < 200 ms |
| SQL 超时 | 8 s |
在诊断前先冻结变量：不同时调整内存参数、并行度、索引和 SQL；不在生产直接运行不可控的EXPLAIN ANALYZE；所有采样记录保留时间戳、会话、应用名和 SQL 文本，确保能够回溯。
KingbaseES 文档指出，sys_stat_activity中的等待事件是瞬时状态，不累计等待时长，因此一次查询结果不足以量化问题，应通过连续采样判断等待是否反复出现。查看当前 SQL 与等待事件还依赖track_activities，开启该参数存在一定监控开销。正式实施前应确认版本、权限与参数状态。
3. 复现过程
3.1 第一步：从活动会话锁定 SQL
先查询持续时间超过 3 秒的活动会话：
SELECTpid,usename,application_name,client_addr,wait_event_type,wait_event,now()-query_startASrunning_time,LEFT(query,1000)ASsql_textFROMsys_stat_activityWHEREstate<>'idle'ANDquery_startISNOTNULLANDnow()-query_start>INTERVAL'3 seconds'ORDERBYrunning_timeDESC;故障时连续采样 10 分钟。结果显示，慢会话多数没有长期停留在锁等待；少量会话出现数据文件读取相关等待，但持续时间短、出现频率高。这说明“锁阻塞”不是主要矛盾，I/O 更像是低效扫描带来的结果，而不是根因本身。
这里要避免一个常见误区：看到DataFileRead一类事件就立即增加缓存。等待事件描述的是会话当时在等什么，不等于为什么读了这么多数据。若 SQL 本来只需要 50 行，却扫描数千万行，扩容只能暂时降低延迟，不能消除访问路径问题。
3.2 第二步：用 SQL 聚合统计确认影响面
在已启用sys_stat_statements的环境中，按累计执行时间和平均执行时间定位高消耗 SQL：
SELECTqueryid,calls,total_exec_time,mean_exec_time,rows,shared_blks_hit,shared_blks_read,temp_blks_written,LEFT(query,1200)ASsql_textFROMsys_stat_statementsORDERBYtotal_exec_timeDESCFETCHFIRST20ROWSONLY;样本窗口内，该订单查询调用 1842 次，平均耗时 4.82 s，累计耗时占业务库前台 SQL 时间的 31%。每次只返回几十行，却伴随大量共享块读取和临时文件写入，符合“扫描多、返回少”的典型特征。
3.3 第三步：读懂优化前执行计划
生产先使用不实际执行语句的EXPLAIN；在隔离测试库还原相同统计信息和绑定值后，再使用：
EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...EXPLAIN ANALYZE会实际执行 SQL，并带来额外分析开销，因此不能把它当成完全无害的查看命令。对于更新、删除或不可控查询，应放在事务回滚、只读副本或测试环境中执行。
优化前计划暴露出四个问题：
orders采用顺序扫描，1280 万行中只保留约 4.2 万行。TO_CHAR(create_time, ...)使时间过滤没有形成理想的索引条件。- 排序发生在大结果集之后，并写出约 640 MB 临时文件。
- 估算行数与实际行数相差两个数量级，说明统计信息或数据相关性未被充分反映。
3.4 第四步：验证参数是否是主因
检查work_mem、shared_buffers、随机页成本、有效缓存估算等参数，只做记录，不立即修改。测试中把会话级work_mem提高后，临时文件下降，但总耗时仍在 3 s 以上，说明参数只能缓解排序落盘，不能解决全表扫描与连接放大。
这一步的价值在于排除“只调参数就能解决”的假设。参数调整应有清晰的内存预算：work_mem往往按执行节点和并发会话消耗，简单全局放大可能在高峰触发内存压力。
4. 方案实施
4.1 SQL 改写：让时间条件可索引
把字符串日期比较改成左闭右开的时间范围：
SELECTo.order_id,o.create_time,o.total_amount,i.sku_id,i.quantityFROM(SELECTorder_id,create_time,total_amountFROMordersWHEREtenant_id=:tenant_idANDstatus=:statusANDcreate_time>=:begin_timeANDcreate_time<:end_timeORDERBYcreate_timeDESCFETCHFIRST50ROWSONLY)oJOINorder_item iONi.order_id=o.order_idORDERBYo.create_timeDESC;改写有两个目的：第一，消除分区键或索引列上的函数包装；第二，先在订单主表完成过滤、排序和 Top-N，再访问明细，避免把数万条候选订单全部连接后再截取 50 条。
应用层必须使用时间类型绑定参数，不再传入受格式影响的字符串。结束时间取下一日或下一月零点，以< end_time表达，避免“23:59:59.999999”边界遗漏。
4.2 索引设计：匹配等值过滤、范围与排序
CREATEINDEXCONCURRENTLY orders_idx_qryONorders(tenant_id,status,create_timeDESC)INCLUDE(order_id,total_amount);CREATEINDEXCONCURRENTLY order_item_idxONorder_item(order_id)INCLUDE(sku_id,quantity,sale_amount);索引列顺序遵循本次查询模式：tenant_id、status为等值条件，create_time同时承担范围过滤和倒序输出。包含列用于降低回表概率，但是否支持、语法是否一致以及索引大小，应按实际 KingbaseES 版本验证。
索引不是越宽越好。orders是高频写入表，新增索引会增加插入、更新、WAL 和备份成本。上线前分别测量索引体积、建索引时长、写入 TPS 降幅和锁影响。
4.3 刷新统计信息并检查估算偏差
ANALYZEorders;ANALYZEorder_item;对倾斜明显的租户和状态字段，要重点比较优化器估算行数与实际行数。若某些大租户占据绝大多数数据，单列统计可能无法描述tenant_id + status + create_time的相关性，应结合版本能力评估扩展统计信息，而不是用固定 Hint 掩盖估算问题。
4.4 建立可重复的回归脚本
每组测试至少执行 10 次，区分冷缓存和热缓存，记录：
- 总执行时间、规划时间；
- 实际返回行数；
- 共享块命中与读取；
- 临时块读写；
- 扫描方式和连接方式；
- 估算行数与实际行数偏差；
- CPU、I/O、锁等待采样；
- 同时段订单写入 TPS。
功能校验不能只比较COUNT(*)。使用订单主键集合做双向差集，并校验金额、明细数量和排序稳定性。分页查询还要验证相同create_time下的确定性顺序，建议补充order_id DESC作为次排序键。
5. 结果对比
在相同绑定值、相同数据快照、热缓存条件下，复现实验得到如下结果：
| 指标 | 优化前 | 优化后 | 变化 |
|---|---|---|---|
| 平均耗时 | 4820 ms | 38 ms | 下降约 99.2% |
| P95 | 6110 ms | 62 ms | 下降约 99.0% |
| 返回行数 | 50 | 50 | 一致 |
| 共享块读取 | 约 31 万 | 约 1260 | 大幅下降 |
| 临时文件 | 约 640 MB | 0 | 消除 |
| 主表访问 | 顺序扫描 | 复合索引扫描 | 改善 |
| 明细访问 | 大范围连接 | 按 50 个订单精确访问 | 改善 |
| 估算/实际偏差 | 约 87 倍 | 约 1.3 倍 | 明显收敛 |
结果表明，真正产生收益的不是某个“神奇参数”，而是访问路径重构：把函数化过滤改成可索引范围，把 Top-N 前推，把复合索引顺序与查询条件对齐，并刷新统计信息。等待事件中反复出现的数据读取随之下降，验证了 I/O 等待是低效计划的结果。
上线后观察 24 小时，除了查询延迟，还应检查新增索引对订单写入、自动清理、备份窗口和复制延迟的影响。性能优化只有在系统整体成本可接受时才算完成。
6. 风险与复盘
6.1 灰度与回退
改造采用应用开关保留新旧 SQL 两条路径。先放量 5%，限定内部租户和单个应用节点，连续观察 30 分钟；门禁包括：
- 新旧 SQL 结果主键集合一致；
- 错误率无上升；
- P95 小于 200 ms；
- 锁等待、复制延迟和写入 TPS 无明显恶化；
- 执行计划稳定使用目标索引。
不满足门禁时，立即把路由切回旧 SQL。新索引先保留用于复盘，不在故障窗口匆忙删除；若确认索引引起写入或空间风险，再在低峰期撤销。回退脚本、应用开关和责任人必须在发布前演练。
6.2 需要重点防范的风险
绑定值差异。小租户和超级租户的数据分布不同，同一个计划未必适合全部租户。回归样本必须覆盖高、中、低基数，而不能只测一个“漂亮参数”。
统计信息变化。大批量归档、导入或促销数据写入后，行数分布变化可能触发计划漂移。应保存基线计划特征，并持续监控平均读块和 P95，而不是只盯平均耗时。
索引写放大。覆盖索引减少读取，但增加写入和存储。对高频更新列谨慎使用包含列，定期评估索引使用率和膨胀。
测试误差。EXPLAIN ANALYZE自身有开销；首次执行可能包含物理 I/O；测试库硬件和缓存状态也会影响数字。文章中必须写清采样方法、执行次数和缓存条件，避免把一次结果包装成稳定结论。
6.3 本次诊断的可复用方法
这次慢 SQL 处理最有价值的不是最终那条索引，而是诊断顺序：
- 从业务时间窗口和请求标识定位会话；
- 连续采样等待事件，判断数据库当时在等待什么；
- 用 SQL 聚合统计确认调用频率和资源占比；
- 用执行计划解释“为何读取这么多数据”；
- 分离 SQL、索引、统计信息和参数变量逐项验证；
- 用结果一致性、资源消耗和写入影响共同验收；
- 通过灰度开关和可逆 DDL 保证能够回退。
慢 SQL 诊断不应止于“加索引”。只有证据链能够解释优化前为什么慢、优化后为什么快，并证明业务结果没有变化，方案才具备可复用和可审计的价值。
转载自：https://blog.csdn.net/u014727709/article/details/163194995
欢迎 👍点赞✍评论⭐收藏，欢迎指正
