1. 一场真实的层次查询事故3万行树为何卡出分钟级延迟1.1 表象SQL看起来没毛病先说一个我实际处理过的案例。某个内部系统的组织架构页突然开始卡顿DBA在群里丢了一条SQL出来说“就这个查询跑了快四十秒你们看一下”。我看完第一反应也是不至于吧。表结构很简单是一张典型的自引用组织表大概长这样CREATE TABLE ORG_INFO ( EMP_ID NUMBER PRIMARY KEY, EMP_NAME VARCHAR2(100), MGR_ID NUMBER, REGION VARCHAR2(20), STATUS VARCHAR2(10) );业务上的查询也很常规从根节点出发把整棵组织树打平带出层级号和路径用于前端组织架构的树形展示。原始SQL长这样SELECT EMP_ID, EMP_NAME, LEVEL AS LV, RPAD( , (LEVEL - 1) * 2, ) || EMP_NAME AS TREE_NAME, SYS_CONNECT_BY_PATH(EMP_NAME, /) AS FULL_PATH FROM ORG_INFO START WITH MGR_ID IS NULL CONNECT BY PRIOR EMP_ID MGR_ID ORDER BY LEVEL, EMP_ID;表的行数只有27000多最深层级9层。按这种数据量和树的规模正常应该在几百毫秒内返回。但这套SQL在线上实测跑了37秒而且随着数据增长这个时间还在非线性上涨。业务方的描述是“组织架构页面打不开导出通讯录直接超时”。1.2 卡点定位索引缺失只是一个开始我先做了两件事看执行计划看执行统计。因为表不大我没急着改SQL而是先搞清楚到底慢在哪。当时执行计划里最显眼的地方是CONNECT BY对应的递归步进对ORG_INFO做的是全表扫描。什么意思呢CONNECT BY PRIOR EMP_ID MGR_ID这个条件的含义是拿当前行的EMP_ID去匹配子行的MGR_ID。“当前行”是上一层结果集里的一行所以从执行引擎的角度看这个递归条件其实是一个相关子查询每处理一层、每处理一个父节点都要根据父节点的EMP_ID去找对应的所有子节点。如果MGR_ID上没有索引引擎每次找子节点都得把整张表过一遍。这个案例里根节点数量、每层的分支数都不小过程大致是第1层全表扫描1次得到几百个根节点 第2层为每个根节点全表扫描1次 第3层为每个第二层节点全表扫描1次 ...总逻辑读大概在40万次以上而一个正常的索引点查方案逻辑读应该在几千次以内。这就是为什么“数据只翻了一倍查询时间却翻了十倍”——因为每一层都在重复做相同体积的全表扫描层数越多倍数效应越明显。当时的修复方式也很直接在MGR_ID上补了一个普通B树索引CREATE INDEX IDX_ORG_INFO_MGR ON ORG_INFO(MGR_ID);加上索引后这条SQL从37秒降到了1.2秒左右。后续我又把ORDER BY LEVEL, EMP_ID改成了ORDER SIBLINGS BY EMP_ID让它走层次序排序而不是把整棵树摊平后再排序耗时又降了一截。这个案例并不新奇但它特别能说明问题层次查询的性能瓶颈绝大多数不在SQL写法多高级多复杂而是递归步进的基础访问路径没走对。这也是我想展开写这篇内容的原因很多优化手段并不玄乎关键是你有没有理解Oracle引擎在递归求值时到底在做什么。2. CONNECT BY和递归WITH在CBO眼里是完全不同的两件事2.1 CONNECT BY的迭代模型凡是写过一点Oracle的人对CONNECT BY都不会陌生。它是数据库内置的层次查询算子语法上非常简洁SELECT ... FROM 表名 START WITH 根节点条件 CONNECT BY PRIOR 子节点关联列 父节点关联列;很多人在使用它时把它理解成一个“类似循环”的功能但实际上在CBO的执行计划里CONNECT BY是一个独立的运算步骤。Oracle对它的处理逻辑大致是先根据START WITH条件确定初始结果集。对这个结果集逐行执行CONNECT BY中的关联条件找到下一层节点。递归重复第2步直到没有新节点出现为止。关键在于第2步的执行方式。Oracle会把当前层结果集的每一行作为驱动行用CONNECT BY条件去访问表。这个访问路径是否高效只看一件事PRIOR关键字后面那个列能不能通过索引快速定位到对应的子行。按执行计划的语言来说这就是一个NESTED LOOPS类型的相关访问。父行有多少子行访问就有多少。父行的数量一多子访问的成本就会线性放大所以一个全表扫描的递归步进在层次深、分支多的情况下会迅速失控。另外要区分一个容易被混淆的点WHERE条件和CONNECT BY条件是不同阶段生效的。WHERE是在层次遍历完成之后做过滤而CONNECT BY条件是在遍历过程中控制分支走向。想剪掉某棵子树应该把它放在CONNECT BY里而不是WHERE里。这个点的性能差异在后面会单独展开。2.2 递归WITH的迭代模型Oracle 11gR2之后引入了递归公共表表达式也就是我们常说的递归WITHWITH RECUR_ORG (EMP_ID, EMP_NAME, MGR_ID, LV, PATH) AS ( SELECT EMP_ID, EMP_NAME, MGR_ID, 1, EMP_NAME FROM ORG_INFO WHERE MGR_ID IS NULL UNION ALL SELECT C.EMP_ID, C.EMP_NAME, C.MGR_ID, P.LV 1, P.PATH || / || C.EMP_NAME FROM ORG_INFO C JOIN RECUR_ORG P ON P.EMP_ID C.MGR_ID ) SELECT * FROM RECUR_ORG;递归WITH和CONNECT BY的底层逻辑是不太一样的。它在执行计划里往往体现为一个临时表变换先把递归的中间结果集物化到内存或临时段然后每一轮迭代把临时表和基础表做连接生成新的行并继续循环。如果你打开执行计划会看到类似这样的操作TEMP TABLE TRANSFORMATION - LOAD AS RESULT (整个递归CTE的中间结果) - 递归部分: HASH JOIN / NESTED LOOPS 连接 临时表和基础表这种模型的优势在于每一轮迭代是标准的表连接CBO的连接方式、驱动顺序、连接顺序都可以被优化。它不是像CONNECT BY那样“逐行相关访问”而是“按层集合连接”。所以当基础表上缺少合适的递归条件索引时递归WITH反而可能通过哈希连接避免全表扫描的重复出现。但递归WITH也有自己的问题每一轮迭代都要把上一轮的结果集物化到临时表如果树的层数很多中间结果集很大临时表可能会被反复读取带来不小的内存和临时段开销。2.3 如何选择不是越新越好也不是越旧越稳这里给一个我实测后总结出来的选择思路不绝对但很有参考价值树的层数浅比如5层以内、每层节点多、递归条件列上有索引 →CONNECT BY通常更快。因为它的相关访问非常轻量几乎就是一次索引扫描拿一行。树的层数深10层以上、每层节点分布不均匀、递归条件列上索引命中率低 → 递归WITH往往更稳至少它不会每一层都去裸扫一张大表。如果业务查询还要求对层次结果做复杂聚合、窗口函数递归WITH更容易改写和优化因为它本质上还是标准的SQL集合操作。所以不要一上来就把CONNECT BY改写成递归WITH。先看执行计划里的访问路径和递归层级再决定要不要做整个语句的重写。我在生产环境里见过把CONNECT BY改成递归WITH后反而变慢的情况原因就是临时表迭代开销大于索引点查。3. 从执行计划里读取层次查询的“真实速度指标”3.1 只看Cost会骗人Starts和A-Rows才有意义很多人调SQL拿到执行计划第一眼看Cost。但在层次查询里Cost的参考意义非常有限因为递归过程的实际开销往往藏在每一层的操作步数里。正确做法是用GATHER_PLAN_STATISTICS提示去拿实际执行统计。例如SELECT /* GATHER_PLAN_STATISTICS */ EMP_ID, EMP_NAME, LEVEL FROM ORG_INFO START WITH MGR_ID IS NULL CONNECT BY PRIOR EMP_ID MGR_ID;执行之后通过DBMS_XPLAN.DISPLAY_CURSOR看带A-Rows、Starts、Buffers的详细计划SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT ALLSTATS LAST));拿到结果后建议重点看两列Starts该步骤被启动了多少次。层级查询如果某一步Starts高达几万说明它被递归调用了上万次每次哪怕只做一次全表扫描总消耗也极其恐怖。Buffers该步骤累计消耗的逻辑读。这是比CPU时间更稳定的性能指标因为逻辑读基本对应着内存访问次数和SQL的物理执行路径强相关。举一个真实计划片段做说明| Id | Operation | Name | Starts | A-Rows | Buffers | | 0 | SELECT STATEMENT | | 1 | 10922 | 423817 | | 1 | SORT ORDER BY | | 1 | 10922 | 423817 | | 2 | CONNECT BY WITHOUT FILTERING| | 1 | 10922 | 423817 | | 3 | TABLE ACCESS FULL | ORG_INFO | 974 | 10922 | 423817 |这个计划里第3步的Starts是974说明表被完整扫描了974次。原因就是上面说的每个父节点找子节点时都触发一次全表扫描。这不是慢在排序或路径拼接而是慢在基础访问路径再看A-Rows是10922最终结果集才一万多行逻辑读却高达42万明显虚高。给MGR_ID建索引后同样的SQL执行计划变成| Id | Operation | Name | Starts | A-Rows | Buffers | | 0 | SELECT STATEMENT | | 1 | 10922 | 18453 | | 1 | SORT ORDER BY | | 1 | 10922 | 18453 | | 2 | CONNECT BY WITHOUT FILTERING | | 1 | 10922 | 18453 | | 3 | INDEX RANGE SCAN | IDX_ORG_INFO_MGR| 974 | 10922 | 18453 |Buffers从42万降到1.8万这才是良性的执行方式。3.2 树的形态会直接反映在执行计划上层次查询还有一个特殊性同一条SQL在不同数据分布下的执行计划可能长得很不一样。这跟普通查询“一次扫描固定表”的思维很不一样。我在实际优化中会把树的形态粗略分成三类第一类窄深树。比如线性的审批链每一层只有一个子节点但层次有30层。这种树的Starts数量等于层数执行计划上每次迭代访问到的行数很少瓶颈反而在迭代次数和函数调用的累积开销上。第二类宽浅树。比如组织架构根节点少但每个节点下面挂了几百上千人。这种树的Starts不会特别高关键是单个父节点下的子节点查找效率以及SYS_CONNECT_BY_PATH这种函数对每一行的路径字符串拼接成本。第三类不规则树。既深且宽还伴随大量的孤儿节点、循环节点。这类树最容易出现“局部慢导致整体崩掉”的现象。比如某一层的某个节点下挂了全表一半的数据执行计划里某一行操作的A-Rows就会异常放大连带后续步骤全部被拖慢。我的建议是在做优化前先明确你这个场景属于哪一类优化方向的优先级会完全不同。窄深树优先考虑用递归WITH控制迭代开销宽浅树优先保证索引和分支剪枝不规则树则要特别关注异常分支的过滤条件。4. 优化动作分级索引、剪枝、改写和结构重设计4.1 第一级让底层扫描成为索引点查这是性价比最高、也是最容易被忽略的一步。很多人调层次查询时总盯着SQL结构却忘记了最基本的递归条件的驱序列有没有索引。CONNECT BY PRIOR A B这种写法含义是用上层结果的A去匹配下层行的B。所以索引应该建在B列上而不是A列上。例如CONNECT BY PRIOR EMP_ID MGR_ID这里应该给MGR_ID建索引而不是EMP_ID。EMP_ID是主键本身有索引但它在这里只是作为父行的驱动值没法成为子行查找的入口真正要做范围扫描的是MGR_ID。另外如果查询里还带其他过滤条件比如状态状态、区域等优先考虑复合索引CREATE INDEX IDX_ORG_MGR_STATUS ON ORG_INFO(MGR_ID, STATUS);在递归过程中Oracle既能通过MGR_ID定位子节点又能在索引层面直接过滤掉不需要的分支回表次数更少。4.2 第二级把WHERE条件变成CONNECT BY剪枝这是很多开发者没有意识到的优化点。举一个典型例子我只想看华东区域的组织树。不少人会写成SELECT EMP_ID, EMP_NAME, LEVEL FROM ORG_INFO WHERE REGION EAST START WITH MGR_ID IS NULL CONNECT BY PRIOR EMP_ID MGR_ID;这写法语法上没问题但WHERE REGION EAST是在层次遍历完成之后才做的最终过滤。也就是说即使REGION不属于目标区域Oracle仍然会把它当成结构的一部分去展开其整棵子树白白消耗大量递归访问。正确做法是把它写进CONNECT BY里作为剪枝条件SELECT EMP_ID, EMP_NAME, LEVEL FROM ORG_INFO START WITH MGR_ID IS NULL CONNECT BY PRIOR EMP_ID MGR_ID AND REGION EAST;这样Oracle在递归过程中发现某个子节点REGION不符合条件时会直接放弃这条路不再往下展开。对于区域隔离、无效节点过滤这类需求这个改动往往比换索引更立竿见影。4.3 第三级递归WITH重写和临时表换空间当一个CONNECT BY查询即便加了索引、做了剪枝仍然因为迭代次数过多而变慢时就该考虑用递归WITH做结构级重写了。递归WITH可以更灵活地控制每一轮的连接方式比如把递归条件和过滤条件拆开让优化器选择哈希连接而不是嵌套循环。我这里有一个实际经验曾经处理过一个物料清单BOM场景部件关系表有280万行最深层级14层每个部件可能有几十个子件。用CONNECT BY怎么写都压不住逻辑读因为分支太多逐行相关访问的累计成本太高。后来改成递归WITH同时在递归成员里把“有效件”这个过滤条件下推到基础表连接之前让每一轮迭代只处理有效的物料节点逻辑读直接降了一个数量级。关键写法是递归里先对基础表做筛选再JOINWITH RECUR_BOM (PART_ID, PARENT_ID, LV) AS ( SELECT PART_ID, PARENT_ID, 1 FROM BOM_ITEMS WHERE PARENT_ID 0 AND IS_VALID 1 UNION ALL SELECT B.PART_ID, B.PARENT_ID, R.LV 1 FROM BOM_ITEMS B JOIN RECUR_BOM R ON B.PARENT_ID R.PART_ID WHERE B.IS_VALID 1 ) SELECT * FROM RECUR_BOM;因为IS_VALID 1在递归成员中是每次迭代都生效的Oracle在执行递归连接时会以更紧凑的中间结果集作为驱动避免无效物料被反复展开。4.4 第四级闭包表/冗余层与结构重设计当业务查询已经不是简单的树展开而是频繁要求“两个节点之间的路径”“某节点的所有祖先”“某节点的所有后代”时单靠SQL递归怎么优化都有上限。这种情况下我会建议业务方考虑引入闭包表或者层级冗余表。闭包表的核心思想很简单用一张关系表把每个节点和其所有祖先、后代的关系直接存下来。例如CREATE TABLE ORG_CLOSURE ( ANCESTOR_ID NUMBER, DESCENDANT_ID NUMBER, DEPTH NUMBER );查询“某个节点的所有后代”就变成一条普通索引查询不再涉及任何递归。代价是写入时需要维护闭包关系通常通过触发器或准实时任务来做。还有另一类更轻量的方案对树的层级路径做冗余存储即每一条记录维护一个从根到自己的FULL_PATH字段例如/100/101/2001然后用LIKE或区间查询快速截取子树。这个方案不用额外建表但要求路径字段长期稳定并且数据量不能太大。这层优化已经不是SQL技巧的范畴了但却是真正能解决“层次数据越来越大、递归查询越来越不可行”的根本手段。要不要走到这一层判断标准很简单当递归查询的逻辑读不再随数据量线性增长而是接近指数级时就该停下来说架构问题了。5. 验证一组可重用的测试方法避免“调完发现更糟”5.1 建立基线与真实数据采样很多人调优有一个坏习惯拿生产环境里的SQL直接跑一遍看一眼耗时觉得“快了”就匆匆上线。层次查询特别不适合这种验证方式因为它的耗时高度依赖数据分布树是深是浅、每层分支多少、有无循环都会显著影响执行时间。我的做法是先做一个可复现的基线从生产库导出真实执行计划、逻辑读、耗时保存为一个基线文件。构造至少三份不同形态的测试数据分别代表窄深树、宽浅树、不规则树。在测试库中把三份数据都跑一遍记录每条执行计划的A-Rows、Starts、Buffers。只有在三种形态下都有明确改善时才把优化后的SQL提交到灰度。5.2 用统计信息判断而不是凭感觉层次查询的优化前后对比我建议用这三项指标指标含义判断标准逻辑读总量访问内存块次数下降至少一个数量级才算明显改善递归访问次数执行计划里关键步骤的Starts数应当显著下降CPU时间实际计算耗时与逻辑读下降趋势一致这里特别提醒一句在多版本并发控制MVCC模型下SQL返回的行数没变但逻辑读大幅下降通常意味着原来每返回一行付出几十次块访问现在降到了个位数这种优化是实打实的。5.3 回归验证时不要只看执行计划层次查询优化后还有一个常被忽略的坑执行计划在测试库里正常不代表上了生产环境还正常。生产库的统计信息、数据分布、并发负载都和测试库不一样CBO很可能在优化器模式下选择不同的连接顺序。所以我的回归清单固定会有这几项检查优化后SQL在基线环境里的执行计划是否稳定是否存在递归WITH临时表落盘的情况。在生产环境抽取真实数据的小样本加HINT或统计信息更新后进行二次验证。如果涉及索引新增确认写入侧的成本层次表的更新通常比查询复杂索引不是越多越好。准备一条回滚路径如果优化后线上表现更差能快速切回原SQL而不是在故障现场研究SQL。6. 五个最典型的性能反模式我几乎每个都踩过6.1 反模式一在CONNECT BY条件里套函数我早期写过一条SQL为了过滤掉名称以“测试”开头的节点直接在递归条件里用了SUBSTR(EMP_NAME,1,2) 测试。逻辑上没错但MGR_ID上的索引彻底用不上了因为Oracle必须对每行先算函数再去匹配。修改思路很朴素把函数条件拆出去或者使用虚拟列 函数索引。数据库里这类“为了让逻辑更简洁而在连接列上套函数”的写法在普通查询里可能只是慢一点在层次查询里就是灾难性放大。6.2 反模式二滥用WHERE做子树的末端过滤前面已经说过WHERE是在递归完成后过滤的。如果你只想展示“在职员工”的组织树正确写法是把STATUS ACTIVE放进CONNECT BY剪枝条件。放在WHERE里的后果是被过滤掉的节点依然会参与整棵子树的展开递归访问没有减少白白浪费几万次逻辑读。不过要注意一个边界如果你需要统计“整棵树有多少人”哪怕部分节点不展示也要完整遍历。那就不能把过滤条件盲目下推否则统计口径会错。优化之前先明确业务语义这是底线。6.3 反模式三无脑拼接SYS_CONNECT_BY_PATHSYS_CONNECT_BY_PATH确实很好用一行SQL就能把完整路径带出来但它的代价是每一层都要拼接字符串并传递到下一层。如果树的平均深度是8层叶子节点的路径就是反复拼接8次的结果这个成本会随层数线性增长而且没法用索引优化。对于只展示节点名称、所在层级不关心完整路径的场景完全不需要这个函数。如果确实需要路径可以考虑只在最底层或特定层级才生成路径减少中间层的拼接次数。我在一个展会后台里这么优化过一条SQL逻辑读直接降了一半。6.4 反模式四无视数据循环自引用表在维护时很容易产生循环关系比如A的上级是BB的上级又是A。如果不加处理CONNECT BY会进入死循环直到达到最大层级或直接报错。Oracle的解决办法是NOCYCLE它能在检测到循环时自动截断代价是多一层循环检测开销SELECT EMP_ID, EMP_NAME, LEVEL FROM ORG_INFO START WITH MGR_ID IS NULL CONNECT BY NOCYCLE PRIOR EMP_ID MGR_ID;但NOCYCLE不是免费的。它会为每一行做一次循环判断数据量大时开销不小。更稳妥的办法是从数据源头治理定时清理循环关系或者在写入时就禁止形成环。如果表里有历史脏数据先清理再上线查询而不是长期依赖NOCYCLE兜底。6.5 反模式五统计信息过期带来的“假慢”最后这个问题几乎和SQL写法无关但踩的人极多。层次查询的表通常不是查询专用而是业务表写入频繁。如果MGR_ID的数据分布已经发生巨大变化而统计信息没有更新Oracle执行计划就会基于错误的基数估算选错访问路径。处理方式很简单优化前先执行一次表级别的统计信息收集EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ORG_INFO, CASCADE TRUE);很多“SQL没变、突然变慢”的线上问题本质就是统计信息和大数据分布错位。这不是技巧问题而是习惯问题在优化层次查询之前先确认统计信息的鲜度再动手修改SQL能省掉很多冤枉路。我做了这么多年数据库优化最深的体会是层次查询本身并不复杂真正复杂的是它把递归逻辑隐藏在了简洁的语法背后。你越理解它在执行计划层面的行为越能预判它是快是慢。每次遇到层次查询性能问题我都会先问自己三件事驱动列的索引在不在不该展开的子树剪了没有数据分布是不是让CBO选错了路把这三个问题查清楚大部分时候问题已经解决了一半。