文章目录每日一句正能量1. 背景与问题2. 环境与数据3. 复现过程3.1 第一步从活动会话锁定 SQL3.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 左右故障时段平均耗时升至 46 s个别请求超过 10 s。应用日志只记录了“数据库查询超时”没有说明数据库是在等待锁、等待磁盘还是单纯执行了低效计划。最初团队提出三个猜测一是促销期间写入量大订单表膨胀导致磁盘读取增加二是存在长事务阻塞查询三是连接池参数异常。若直接根据猜测扩容或调整参数既可能掩盖根因也可能引入新的抖动。因此本次处理坚持一条原则先建立“业务现象—活动会话—等待事件—SQL 画像—执行计划—数据对象”的证据链再实施改动。故障 SQL 的业务形态如下SELECTo.order_id,o.create_time,o.total_amount,i.sku_id,i.quantityFROMorders oJOINorder_item iONi.order_ido.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 万高峰并发180260 会话原索引orders(tenant_id)、orders(create_time)、order_item(order_id)目标 SLA平均 100 msP95 200 msSQL 超时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_activityWHEREstateidleANDquery_startISNOTNULLANDnow()-query_startINTERVAL3 secondsORDERBYrunning_timeDESC;故障时连续采样 10 分钟。结果显示慢会话多数没有长期停留在锁等待少量会话出现数据文件读取相关等待但持续时间短、出现频率高。这说明“锁阻塞”不是主要矛盾I/O 更像是低效扫描带来的结果而不是根因本身。这里要避免一个常见误区看到DataFileRead一类事件就立即增加缓存。等待事件描述的是会话当时在等什么不等于为什么读了这么多数据。若 SQL 本来只需要 50 行却扫描数千万行扩容只能暂时降低延迟不能消除访问路径问题。3.2 第二步用 SQL 聚合统计确认影响面在已启用sys_stat_statements的环境中按累计执行时间和平均执行时间定位高消耗 SQLSELECTqueryid,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_ido.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 ms38 ms下降约 99.2%P956110 ms62 ms下降约 99.0%返回行数5050一致共享块读取约 31 万约 1260大幅下降临时文件约 640 MB0消除主表访问顺序扫描复合索引扫描改善明细访问大范围连接按 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欢迎 点赞✍评论⭐收藏欢迎指正
)
