KES SQL高级调优与执行计划深度解析

KES SQL高级调优与执行计划深度解析前面的内容我们把基础SQL写法、索引使用、分区表、普通性能优化这些内容都过了一遍。到这一章我们就往更深一层的调优方向走了。平时大部分慢查询靠基础手段就能处理。但碰到多表关联、超大结果集、高并发报表、多层嵌套子查询这类场景单纯加索引、改语句就不够用了。这个时候就得学会看懂执行计划搞懂数据库底层的运行逻辑再搭配对应的高级优化手段来处理。一、 本章学习导读1.1 学习目标能把KES执行计划里每一个字段都看懂顺着内容快速找到SQL运行卡顿的地方。能分清顺序扫描、索引扫描、位图扫描这些不同的数据读取方式判断每一种方式的好坏。把嵌套循环、哈希连接、合并连接这三种表关联方式吃透结合业务场景选择合适的算法。了解分区裁剪、谓词下推、表达式计算这些优化器自带的行为学会顺着规则写SQL引导数据库做优化。搞定分页、分组聚合、子查询、CTE、模糊查询这些线上经常出现的慢SQL问题。会结合系统视图、执行计划、会话状态综合排查问题形成属于自己的一套调优思路。1.2 本章重点执行计划完整解读包括里面的关键字段、不同节点、成本和耗时怎么看全表扫描、各类索引扫描、位图扫描的区别和对应的优化办法三种表连接算法的原理、适用场景和调优小技巧分区裁剪、谓词下推、常量折叠这些优化器常用能力子查询、CTE、深分页、模糊查询、分组聚合这类复杂SQL的调优方法政务类大型报表SQL的真实调优案例二、 为什么要深入学习执行计划我发现很多做开发和运维的朋友调SQL的时候全靠经验。感觉哪里慢就试着加索引、改SQL语句。运气好了问题能解决运气不好就反复试来试事白白耗费不少时间。其实数据库执行SQL是有一套完整步骤的。执行计划就相当于数据库执行任务的步骤清单。数据库会严格按照这份清单一步步读取数据、做关联、做计算最后返回结果。你看不懂这份清单就没法知道数据库实际走了哪条执行路径。也不清楚具体卡在哪个环节更不知道明明建了索引数据库却不去使用的原因。KES是基于PostgreSQL内核打造同时兼容Oracle的优化逻辑。它的执行计划展示得很直观。只要学会解读的方法再棘手的慢SQL也能找到问题所在。这里提一句很实在的经验。不管调哪条SQL第一步一定是查看执行计划。不要上来就盲目修改代码、新增索引。三、 执行计划基础开启与基础字段解读3.1 执行计划两种查看方式KES里有两条最常用的语句用来查看执行计划。一种只做预估一种会真实运行SQL两者使用场景不一样。3.1.1 EXPLAIN 预估执行计划执行这条语句的时候数据库只会解析SQL逻辑算出大致的执行成本。不会真正去执行SQL。像数据量很大的查询、有可能锁表的危险语句都可以用这个命令查看能避免影响线上服务。EXPLAINSELECT*FROMt_userWHEREusername张三;3.1.2 EXPLAIN ANALYZE 真实执行计划这条命令不光会生成执行计划还会把SQL完整跑一遍。每一个步骤的实际耗时、扫描行数都会展示出来。日常排查慢SQL基本都用它也是我们调优时的首选命令。EXPLAINANALYZESELECTu.username,o.order_time,o.total_priceFROMt_user uJOINt_order oONu.uido.user_idWHEREu.username张三ORDERBYo.order_timeDESC;3.2 核心关键字段逐行解读很多人拿到执行计划的返回内容看着一堆英文参数就犯难。我挑平时最常接触的内容用大白话讲清楚。Seq Scan顺序扫描也就是大家常说的全表扫描。数据库会逐行读取整张数据表。数据量一旦变大速度就会明显下降。千万级别的数据表如果出现这个节点基本就是我们需要优化的目标。Index Scan普通索引扫描。数据库依靠索引定位数据读取效率比全表扫描高很多也是我们希望看到的执行方式。Index Unique Scan唯一索引扫描。一般用在主键、唯一约束的查询场景数据定位速度是最快的。Bitmap Scan位图扫描。一条SQL用到多个索引做筛选的时候就会触发这种扫描方式。多条件查询里很常见。Nested Loop嵌套循环多表关联的其中一种算法后面会详细说明。Hash Join哈希连接两张大表做关联时的主流选择。Merge Join合并连接两张有序数据表做关联时会用到。rows预估或者实际扫描的数据行数。如果这个数值比业务实际数据量大很多就说明过滤条件没有生效。cost数据库估算出来的执行开销。这个数值只做参考优先级不如实际耗时。time对应节点的实际运行时间单位是毫秒。整个计划里耗时最长的节点就是整条SQL的瓶颈。补充一个小知识点。执行计划是树形的结构。阅读的时候要从缩进最深、最内层的节点开始看。看完子节点再往上看父节点。3.3 实战判断一眼识别好坏计划结合实际场景简单区分一下。出现Seq Scan全表扫描、单次扫描几十万行数据、单节点耗时几百毫秒以上这类执行计划就属于需要优化的坏计划。查询条件正常走Index Scan索引扫描、扫描行数和实际业务匹配、整体耗时控制在几十毫秒内这类就是比较理想的执行计划。四、 数据访问方式深度解析扫描类型与调优策略读取数据是SQL执行的第一步。选择哪种读取方式直接决定了整条语句的基础性能。下面把KES里四种主流的扫描方式、适用场景和对应的优化方法逐一说明。4.1 顺序扫描Seq Scan全表扫描4.1.1 出现场景数据表没有创建对应索引或者查询条件没办法匹配索引。还有一种情况数据库判断走全表扫描比走索引速度更快。如果是几百行以内的小表数据库选择全表扫描是正常的不用额外处理。但数据表行数超过10万还在走全表扫描就必须做优化。4.1.2 典型问题与解决方案举个例子一张100万行的用户表根据手机号查询数据没有建索引的情况下就会触发全表扫描。EXPLAINANALYZESELECT*FROMt_userWHEREphone13800138000;执行计划里会看到Seq Scan on t_user。对应的解决办法有两个。第一给查询字段补充索引。第二检查查询条件里是不是用了函数、存在隐式类型转换这类写法都会让索引失效。4.2 索引扫描Index Scan4.2.1 出现场景查询条件可以匹配B树索引数据库通过索引找到数据位置再回表读取完整内容。单表查询里这是比较理想的执行方式。-- 给手机号建立索引后自动走索引扫描CREATEINDEXidx_user_phoneONt_user(phone);EXPLAINANALYZESELECT*FROMt_userWHEREphone13800138000;4.3 唯一索引扫描Index Unique Scan这种方式一般用在主键、唯一索引的查询上。数据库可以直接定位到单条数据整体性能是最优的。根据主键ID查询数据基本都会触发它。EXPLAINANALYZESELECT*FROMt_userWHEREuid10001;4.4 位图扫描Bitmap Scan当一条SQL同时使用多个索引做筛选的时候数据库就会启用位图扫描。比如同时根据用户名、账号状态两个条件查询。这种扫描方式本身性能不错日常不用特意去调整了解原理就可以。4.5 扫描类型避坑总结行数少于1000的小表走全表扫描属于正常现象不用额外建索引。行数超过10万的大表要避免无意义的全表扫描优先补充合适的索引。不要在索引字段上嵌套函数、做运算也尽量避免隐式类型转换这些操作都会让索引失效。五、 三大表连接算法原理、场景与调优多表关联的SQL也是慢查询的重灾区。KES提供嵌套循环、哈希连接、合并连接三种关联算法。优化器会根据表的数据量、数据排序状态自动选择。我们要做的就是弄懂每种算法的特点引导数据库选出最合适的方案。5.1 嵌套循环连接Nested Loop5.1.1 工作原理简单来说先拿小表也就是驱动表里的每一行数据去大表也就是被驱动表里做匹配。一轮一轮循环完成两张表的关联。5.1.2 适用场景小表和大表做关联的场景最适合它。驱动表的数据量越小被驱动表的关联字段建有索引整体速度就越快。平时普通的两表关联、维度表关联业务大表基本都用这种算法。5.1.3 调优要点被驱动表的关联字段一定要建立索引这是保证性能的关键。尽量把数据量最小的表设置成驱动表。能提前过滤的数据尽量在关联之前处理减少循环的次数。示例用户表数据量小关联百万行的订单表EXPLAINANALYZESELECTu.username,o.order_timeFROMt_user uJOINt_order oONu.uido.user_idWHEREu.uidIN(10001,10002);5.2 哈希连接Hash Join5.2.1 工作原理数据库先读取其中一张表根据关联字段生成哈希结构。之后再遍历另一张表通过哈希匹配的方式完成数据关联。5.2.2 适用场景两张都是大数据量的表索引收益不高的情况下优先使用。政务报表、数据统计类SQL里经常能看到。5.2.3 调优要点可以适当调大work_mem参数让哈希计算在内存里完成。一旦落到磁盘上性能会大幅下降。尽量在关联之前用WHERE条件过滤数据减少参与关联的数据总量。5.3 合并连接Merge Join5.3.1 工作原理它要求两张表的关联字段本身是有序的。数据库按照顺序逐行比对数据完成关联。5.3.2 适用场景两张数据表数据量偏大且关联字段已经排序比如主键、索引字段。5.3.3 调优要点如果数据表本身无序数据库就要额外执行排序操作会增加耗时。这种情况下合并连接的表现不如哈希连接。5.4 三大算法选型速记小表关联大表、关联字段有索引 → 选择嵌套循环大表关联大表、索引收益低 → 选择哈希连接两张表数据量大且字段有序 → 选择合并连接六、⚙️ 优化器核心特性读懂数据库的“小聪明”KES的查询优化器会自动做很多逻辑改写来提升执行效率。很多SQL变慢并不是语句写错了而是我们不了解这些特性导致优化能力没法生效。这里讲分区裁剪、谓词下推、常量折叠三个常用能力。6.1 分区裁剪Partition Pruning这个功能是针对分区表设计的也是分区表能提速的核心。6.1.1 原理查询条件里包含分区键的时候优化器会自动跳过不相关的分区只扫描符合条件的分区不用遍历全部分区。6.1.1 正常案例裁剪生效t_order_log是按月份分区的表根据时间筛选数据只会扫描对应分区。EXPLAINANALYZESELECT*FROMt_order_logWHEREcreate_time2025-06-01;6.1.2 失效案例裁剪失败如果在分区键上使用函数优化器就没法识别分区范围分区裁剪会直接失效会扫描所有分区。-- 分区键使用函数分区裁剪失效扫描全部分区EXPLAINANALYZESELECT*FROMt_order_logWHERETO_CHAR(create_time,YYYY-MM)2025-06;小提醒写分区表查询的时候不要在分区键上做函数运算、算术运算保证分区裁剪正常触发。6.2 谓词下推Predicate Pushdown谓词其实就是我们写的过滤条件。谓词下推的意思就是优化器会把外层的WHERE条件推到子查询、视图里面提前过滤数据减少后续关联和计算的数据量。6.2.1 正常场景-- 条件下推到子查询内部提前过滤EXPLAINANALYZESELECT*FROM(SELECT*FROMt_order)tWHEREuser_id10001;6.2.2 失效场景如果子查询里有分组、聚合、集合运算谓词下推就会被阻止。数据库会先全量计算再做过滤性能会变差。对应的解决办法就是改写SQL把过滤条件写到子查询内部。6.3 常量折叠SQL里的常量计算优化器会提前算好。比如WHERE age 10 5优化器会直接转换成WHERE age 15。不用在SQL运行阶段重复计算。这个功能不用我们手动干预了解即可。七、️ 经典疑难SQL专项深度调结合线上经常碰到的问题针对子查询、CTE、深分页、分组聚合、模糊查询这几类慢SQL结合执行计划分析问题给出对应的改写方案。7.1 子查询调优EXISTS、IN 与 JOIN 选型不少人纠结 IN 和 EXISTS 哪个更快结合执行计划就能分清。子查询返回的数据量偏小优先用 IN。子查询返回的数据量很大优先用 EXISTS。碰到多层嵌套子查询建议全部改写成 JOIN 关联优化器处理起来效率更高。低效写法示例SELECT*FROMt_userWHEREuidIN(SELECTuser_idFROMt_orderWHEREorder_amount1000);改写后高效写法SELECTDISTINCTu.*FROMt_user uJOINt_order oONu.uido.user_idWHEREo.order_amount1000;7.2 CTE 公共表达式调优WITH 也就是CTE可以拆分复杂SQL逻辑。不过在部分旧版本内核里CTE的结果会生成临时数据。多次引用CTE就会重复扫描数据。日常建议简单逻辑尽量不用CTE。复杂报表可以合理拆分同时避免多层CTE嵌套。7.3 深分页调优普通的 LIMIT OFFSET 分页写法当OFFSET数值很大的时候数据库要先读取前面所有行再丢弃。分页越靠后速度越慢。低效写法-- 偏移量10000性能极差SELECT*FROMt_orderORDERBYoidLIMIT20OFFSET10000;可以利用主键来改写直接定位分页起始位置不用扫描前置数据。SELECT*FROMt_orderWHEREoid10000ORDERBYoidLIMIT20;7.4 分组聚合GROUP BY调优大表做分组统计很容易出现Sort排序节点耗时会增加。优化方式给GROUP BY、ORDER BY对应的字段建立复合索引利用索引自带的有序特性避免现场排序。在聚合计算之前先过滤数据缩小统计范围。7.5 模糊查询调优like 关键词%右模糊查询可以正常命中索引性能没问题。like %关键词、like %关键词%左模糊、全模糊索引会直接失效一定会走全表扫描。如果业务允许尽量限制模糊查询的写法。如果必须使用全模糊可以搭配全文检索插件处理。八、 真实企业案例政务大数据报表SQL深度调优8.1 业务背景某市级政务平台的综合统计报表一共关联五张大表单表数据量都超过800万行。原始SQL执行耗时达到22秒业务高峰期经常超时影响后台统计工作正常使用。8.2 问题分析通过执行计划定位两张核心大表采用嵌套循环但是关联字段没有索引出现大量全表扫描。多层嵌套子查询过滤条件无法下推数据过滤时机太晚。GROUP BY 字段没有索引数据库需要现场排序耗时增加。分区表没有正确使用分区条件分区裁剪失效遍历了所有历史分区。8.3 分步调优操作第一步补充关联字段索引CREATEINDEXidx_order_useridONt_order(user_id);CREATEINDEXidx_apply_codeONt_gov_apply(dept_code);第二步多层子查询改写为多表JOIN优化谓词下推把三层嵌套的IN子查询全部改写成INNER JOIN。把时间、部门这类过滤条件提前设置。第三步分组字段建立复合索引消除排序节点CREATEINDEXidx_stat_dept_timeONt_gov_apply(dept_code,submit_time);第四步修正分区查询条件启用分区裁剪去掉分区键上的函数运算直接使用时间范围做筛选。8.4 调优结果✅ 报表执行耗时从 22秒 → 0.8秒✅ 执行计划里的全表扫描、大量Sort排序节点全部消失✅ 高峰期持续运行不再出现超时问题可支撑每日上万次统计查询✅ 应用端代码不需要大幅修改改造成本很低九、⚠️ 高级调优高频坑点汇总结合线上大量排错和调优的经验整理解读执行计划、做SQL深度优化时容易踩的问题大家提前留意。只看 EXPLAIN 预估计划不看 EXPLAIN ANALYZE 真实计划。预估结果只能参考真实耗时一定要以后者为准。盲目新建索引。索引太多会拖累写入性能优先改写SQL逻辑再考虑加索引。在分区键上使用函数直接让分区裁剪失效分区表失去优化效果。两张百万行以上的大表依旧使用嵌套循环执行效率远不如哈希连接。深分页场景一直使用OFFSET偏移量超过5000之后性能会明显下滑。忽略隐式类型转换字符串和数字做比对会隐性造成索引失效不容易排查。一次性修改多条问题SQL。建议改一条、验证一条避免引发连锁问题。十、✅ 本章总结与后续学习预告学完这一章你就掌握了KES SQL深度调优的整套能力。从执行计划解读、三种表连接算法再到优化器特性、各类疑难SQL改写还有大型报表的实战调优这些内容基本可以应对企业里绝大多数SQL性能问题。简单梳理下本章学到的内容会使用EXPLAIN/EXPLAIN ANALYZE看懂执行计划快速定位性能瓶颈。分清各类数据扫描方式知道全表扫描和索引扫描各自的使用场景与优化边界。理解三种表连接算法根据数据表规模选择合适的关联方式。了解分区裁剪、谓词下推等优化器规则按照规则编写SQL。掌握子查询、分页、模糊查询、分组等常见慢SQL的改写方法。具备复杂大型报表的综合调优能力。