文章目录每日一句正能量1. 背景与问题把子查询改成JOIN可能快了也可能把权限结果改错了2. 环境与数据用真实的一对多权限模型验证而不是拿唯一键做演示2.1 EXISTS 的本质是 Semi Join 语义2.2 普通 JOIN 是行组合语义2.3 IN 与 EXISTS 也不能只看写法2.4 NOT IN 是更危险的语义边界3. 复现过程权限 EXISTS 为什么会从2秒退化到40秒3.1 原始相关 EXISTS3.2 第一步仍然是 ANALYZE3.3 真正问题权限表查找没有复合索引4. 方案实施五类查询改写必须逐一证明语义4.1 EXISTS → JOIN只有右侧唯一时才可直接等价4.2 JOIN DISTINCT 能恢复结果但不一定更快4.3 EXISTS 和 JOIN 可能产生同类底层计划4.4 NOT EXISTS 通常比手工 LEFT JOIN ... IS NULL 更直接4.5 NOT IN → NOT EXISTS 前先处理 NULL4.6 权限过滤索引应围绕“谁查什么”设计4.7 热点用户必须单独测4.8 权限表的一对多是正常业务不要为了JOIN强行加唯一约束4.9 如果确实要JOIN可以先去重权限键4.10 大权限集合可以先预聚合4.11 相关标量子查询比EXISTS更值得警惕4.12 OR 里的相关子查询更容易阻碍去相关5. 结果对比性能最快的E3为什么反而不能上线E0相关 EXISTS 无合适索引E1ANALYZEE2EXISTS 权限复合索引E3直接改普通 JOINE4JOIN DISTINCTE5反向过滤使用 NOT EXISTS5.1 汇总5.2 结果校验不能只比较COUNT5.3 权限SQL还必须做“泄漏校验”5.4 Buffer Read 是判断是否真的减少工作的关键5.5 新索引也要验写成本6. 风险与复盘子查询改JOIN最危险的是“语法更简单语义更复杂”6.1 风险一一对多重复6.2 风险二NOT IN 的 NULL6.3 风险三优化器本来已经做了Semi Join6.4 风险四相关子查询loops被忽略6.5 风险五JOIN DISTINCT制造隐藏Sort/Hash6.6 风险六权限索引导致写放大6.7 风险七热点用户导致计划参数敏感推荐的等价性验证流程推荐性能诊断顺序回退方案最终复盘附录 A权限 EXISTS附录 B检查JOIN重复附录 C执行计划附录 D最低验收门禁每日一句正能量人生就是一场旅行不在乎目的地在乎的应该是沿途的风景以及看风景的心情。重要的不是你获得了什么头衔而是你如何感受每一天的朝阳与晚风如何与同行者分享那些瞬间。主题查询改写 / 子查询与JOIN / 权限过滤重点EXISTS、IN、NOT EXISTS、NOT IN、Semi Join、Anti Join、重复行、NULL语义、执行计划、索引与并发验证适用场景KingbaseES 上的行级权限过滤、租户隔离、组织权限、订单可见性、白名单/黑名单、数据授权等复杂查询场景。1. 背景与问题把子查询改成JOIN可能快了也可能把权限结果改错了数据库优化里流传很广的一句话是子查询慢改 JOIN 就快。这句话最大的问题不是“有时不快”。而是有些子查询和 JOIN 在业务语义上根本不等价。权限过滤就是最容易踩坑的场景。例如SELECTo.order_id,o.amountFROMbiz_order oWHEREEXISTS(SELECT1FROMuser_order_permission pWHEREp.user_id:user_idANDp.tenant_ido.tenant_idANDp.order_ido.order_id);这段 SQL 的业务含义非常清楚只要当前用户至少拥有一条匹配权限 就返回这条订单。这里的核心不是“把权限表连进来”。而是Existence即“是否存在”。如果开发直接改成SELECTo.order_id,o.amountFROMbiz_order oJOINuser_order_permission pONp.tenant_ido.tenant_idANDp.order_ido.order_idWHEREp.user_id:user_id;假设一个用户对同一订单同时拥有角色权限 组织权限 项目权限 临时授权 区域授权五条记录。那么EXISTS返回1条订单而普通JOIN可能返回5条订单性能可能真的更快。但结果已经错了。这类优化事故最危险的地方就在于SQL执行成功 没有报错 延迟下降 业务还能跑只有报表金额、分页、订单数量或下游接口悄悄出现重复。所以本文第一个结论是查询改写必须先验证等价语义再比较执行计划。性能测试排在第二位。KingbaseES 官方文档对连接执行有 Semi Join、Anti Join 等概念而执行计划分析也强调最终连接方式由优化器根据统计信息和代价进行选择。很多EXISTS、IN形式本身就有机会被优化器解除子查询结构转成半连接执行因此“SQL 文本里看到子查询”并不等于“数据库一定逐行执行子查询”。2. 环境与数据用真实的一对多权限模型验证而不是拿唯一键做演示示例环境数据库 KingbaseES V9 订单 biz_order 订单量 8000万 权限 user_order_permission 权限记录 2.4亿 租户 3000 用户 120万权限表CREATETABLEuser_order_permission(user_idBIGINTNOTNULL,tenant_idBIGINTNOTNULL,order_idBIGINTNOTNULL,role_codeVARCHAR(32),org_idBIGINT,valid_flagINTNOTNULLDEFAULT1);这里故意不设置(user_id, tenant_id, order_id)唯一。因为真实权限系统通常允许同一个人 通过多个授权来源 看到同一数据这恰恰是判断EXISTS与普通JOIN是否等价的关键。2.1 EXISTS 的本质是 Semi Join 语义EXISTS只回答有没有找到第一条满足条件的权限以后逻辑上就不需要把其余匹配权限投影到结果中。因此它对应的是Semi Join思想左侧业务行 只保留一次2.2 普通 JOIN 是行组合语义JOIN 做的是左行 × 所有匹配右行如果1条订单 匹配5条权限结果就是最多5个组合所以两者只有在可以证明右表连接键唯一或者业务允许重复时才能直接说语义等价。2.3 IN 与 EXISTS 也不能只看写法例如WHEREorder_idIN(SELECTorder_idFROMuser_order_permissionWHEREuser_id:uid)在很多等值场景中优化器有机会转换成Semi Join所以IN一定慢 EXISTS一定快同样不是可靠结论。最终还是EXPLAIN ANALYZE说话。2.4 NOT IN 是更危险的语义边界例如WHEREorder_idNOTIN(SELECTorder_idFROMblacklist)如果子查询中存在NULLSQL 三值逻辑会让比较产生UNKNOWN结果可能与开发直觉完全不同。KingbaseES SQL 调优指南也专门给出NOT IN → NOT EXISTS的优化建议但明确附带条件连接列不存在NULL值时两者才可按该规则视为等价并可能利用不同 Anti Join 方式优化。所以NOT IN 改 NOT EXISTS 前必须先证明 NULL 条件而不是只为了拿到 Hash Anti Join。3. 复现过程权限 EXISTS 为什么会从2秒退化到40秒3.1 原始相关 EXISTSSELECTo.order_id,o.amountFROMbiz_order oWHEREo.tenant_id:tenant_idANDo.status1ANDEXISTS(SELECT1FROMuser_order_permission pWHEREp.user_id:user_idANDp.tenant_ido.tenant_idANDp.order_ido.order_idANDp.valid_flag1);如果权限表没有合适索引执行计划可能出现Outer 大量订单 Inner/SubPlan 权限表反复扫描例如Outer actual rows: 800万 Inner loops: 800万这时确实可能非常慢。但慢的根因并不是EXISTS三个字而是相关执行 × 大量Outer × Inner缺乏低成本访问路径3.2 第一步仍然是 ANALYZEKingbaseES 官方执行计划分析流程明确建议先检查估算是否准确 不准确则ANALYZE 再重新查看计划执行ANALYZEbiz_order;ANALYZEuser_order_permission;假设 P9542s →31s说明统计修复有效。但仍然很慢。3.3 真正问题权限表查找没有复合索引每次权限判断条件user_id tenant_id order_id valid_flag如果只有order_id单列索引仍可能访问大量无关权限。建立候选CREATEINDEXidx_perm_user_tenant_order_validONuser_order_permission(user_id,tenant_id,order_id,valid_flag);重新 ANALYZE。计划可能变成Nested Loop Semi Join 或 有效的Semi Join路径 Inner Index ScanP9531s →2.4s这说明保留 EXISTS 语义也完全可能把性能问题解决。没有必要为了速度先改 JOIN。4. 方案实施五类查询改写必须逐一证明语义4.1 EXISTS → JOIN只有右侧唯一时才可直接等价原WHEREEXISTS(SELECT1FROMpermission pWHEREp.order_ido.order_id)改JOINpermission pONp.order_ido.order_id必须先证明SELECTorder_id,COUNT(*)FROMpermissionGROUPBYorder_idHAVINGCOUNT(*)1;结果0如果不是 0不能直接改4.2 JOIN DISTINCT 能恢复结果但不一定更快为了修重复很多人继续SELECTDISTINCTo.*FROMorderoJOINpermission p...语义可能恢复到每订单一行但执行计划可能增加Sort Unique HashAggregate也就是说先把1行放大成5行 再花资源去重成1行从执行模型上并不漂亮。如果业务本质就是是否存在权限直接EXISTS更能表达意图。4.3 EXISTS 和 JOIN 可能产生同类底层计划现代 CBO 并不是逐字翻译 SQL。如果条件可去相关EXISTS可以被转换成Semi Join而不是真的外表每一行执行一次独立SQL所以优化时一定要区分SQL语法形态和物理执行计划KingbaseES 官方查询与子查询文档、连接计划文档都表明查询优化器会根据条件转换和代价选择 Merge、Hash、Nested Loop以及 Semi/Anti 等连接形式。因此如果 EXISTS 已经被优化成优良的 Semi Join再人工改成 JOIN 很可能没有收益。4.4 NOT EXISTS 通常比手工 LEFT JOIN … IS NULL 更直接反权限场景找出用户无权访问的记录可以WHERENOTEXISTS(...)也有人写LEFTJOINpermission p...WHEREp.order_idISNULL在条件满足时两者可能被优化器转换到类似 Anti Join。所以也不能说LEFT JOIN一定更快真正对比Hash Anti Join Nested Loop Anti Merge Anti以及实际扫描量4.5 NOT IN → NOT EXISTS 前先处理 NULL官方 SQL 调优指南明确指出连接列不存在NULL时可以把NOT IN转成NOT EXISTS并可能通过 Hash Anti Join 获得更优执行。因此生产改写流程必须1. 查看字段NOT NULL约束 2. 检查历史真实NULL 3. 确认业务是否应忽略NULL 4. 再进行改写而不是Advisor建议了 就直接批量替换4.6 权限过滤索引应围绕“谁查什么”设计权限 EXISTS 常见条件user_id tenant_id resource_id valid_flag索引(user_id, tenant_id, resource_id, valid_flag)很自然。但如果系统查询模式是先按tenantresource查可见用户索引顺序就可能不同。所以要根据真实查询入口 选择性 热点用户 租户规模设计。4.7 热点用户必须单独测普通用户100条权限管理员3000万条权限同一 SQL 的最佳计划可能不同。热点管理员场景Semi Join构建权限集合可能比逐订单点查权限更合适。所以测试参数必须至少分普通用户 部门管理员 超级管理员4.8 权限表的一对多是正常业务不要为了JOIN强行加唯一约束为了让JOIN不重复而把权限表设计成UNIQUE(user_id,order_id)可能直接破坏授权模型。正确方式应该是SQL匹配业务语义而不是为了SQL方便修改权限语义4.9 如果确实要JOIN可以先去重权限键例如JOIN(SELECTDISTINCTuser_id,tenant_id,order_idFROMuser_order_permissionWHEREuser_id:uidANDvalid_flag1)p这在某些场景有意义先把多来源授权压成资源集合 再与大业务表Join尤其管理员权限很多时。但要看DISTINCT成本 权限集合大小 后续Join方式4.10 大权限集合可以先预聚合例如同一用户500万授权明细但实际资源100万order_id可以先SELECT DISTINCT order_id形成较小集合。如果这个集合会被一个复杂查询多次使用还可以评估CTE 临时表 物化结果这里就与上一篇 CTE 文章衔接起来物化还是内联仍应由结果规模和重复使用成本决定。4.11 相关标量子查询比EXISTS更值得警惕例如SELECTo.order_id,(SELECTMAX(p.role_code)FROMpermission pWHEREp.order_ido.order_id)role_codeFROMbiz_order o;这不是存在性判断。它需要每个外表行得到一个标量结果如果优化器无法很好去相关可能形成大量 SubPlan loops。这类 SQL 改成预聚合 LEFT JOIN往往更有价值LEFTJOIN(SELECTorder_id,MAX(role_code)role_codeFROMpermissionGROUPBYorder_id)pONp.order_ido.order_id但仍然要检查结果等价 聚合范围 过滤能否前推4.12 OR 里的相关子查询更容易阻碍去相关类似WHEREEXISTS(...)OREXISTS(...)复杂 OR 条件可能让优化器难以解除子查询结构。KingbaseAnalyticsDB 官方查询文档也提到某些位于 SELECT 列表或 OR 条件中的相关子查询可能按外层每一行执行并建议通过 JOIN 或拆分查询重写。对于 KingbaseES 生产 SQL仍需以本版本实际计划为准但这类结构值得重点检查SubPlan loops5. 结果对比性能最快的E3为什么反而不能上线示例实验E0相关 EXISTS 无合适索引Outer: 800万 Inner loops: 800万 Buffers Read: 1800万 P95: 42s正确性PASSE1ANALYZEP95: 31s正确性PASSE2EXISTS 权限复合索引Plan: Semi Join / Index Lookup Buffers: 85万 P95: 2.4s正确性PASSE3直接改普通 JOINPlan: Hash Join P95: 1.7s看上去最快但是结果行数倍率: 3.7×因为同一订单有多个权限来源。正确性FAIL所以 E3 不能上线。这就是本文最重要的实验结论性能测试只有在语义等价以后才有意义。错误结果的 1.7 秒没有任何优化价值。E4JOIN DISTINCTHash Join →HashAggregate / Unique结果正确P954.1s反而比EXISTS 索引 2.4s更慢。E5反向过滤使用 NOT EXISTS如果需求找没有权限的订单测试可能得到Hash Anti Join / Index Anti P951.9s并且语义比NOT IN在 NULL 场景下更可控。5.1 汇总实验写法计划P95结果E0相关EXISTS重复SubPlan/Loop42s正确E1EXISTSANALYZE改进计划31s正确E2EXISTS复合索引Semi Join/Index2.4s正确E3普通JOINHash Join1.7s错误重复E4JOINDISTINCTJoin去重4.1s正确E5NOT EXISTSAnti Join1.9s正确以上为方法演示数据不是生产实测。5.2 结果校验不能只比较COUNT如果JOIN重复两条 同时漏两条总 COUNT 可能刚好相等。所以必须比较主键集合例如EXCEPT双向差集。至少Old EXCEPT New New EXCEPT Old都必须是0行5.3 权限SQL还必须做“泄漏校验”普通性能 SQL多一行可能只是业务 Bug。权限 SQL多一行可能是越权泄漏所以验收至少包括无权用户 临界角色 跨租户 失效权限 重复授权 管理员 NULL值5.4 Buffer Read 是判断是否真的减少工作的关键E01800万E285万说明复合索引真正减少了权限表访问。而不是刚好缓存更热这类数据比单次 Execution Time 更有复用价值。5.5 新索引也要验写成本权限系统可能高频授权 撤权 批量同步增加复合索引以后需要测INSERT DELETE UPDATE WAL 索引空间不能为了读 SQL2.4s把权限变更写入拖慢十倍。6. 风险与复盘子查询改JOIN最危险的是“语法更简单语义更复杂”6.1 风险一一对多重复这是权限过滤最常见事故。解决保持EXISTS 或 先去重右侧集合而不是用DISTINCT掩盖一切问题。6.2 风险二NOT IN 的 NULL如果右侧存在 NULLNOT IN可能与NOT EXISTS得到完全不同结果。改写前一定检查约束 真实数据6.3 风险三优化器本来已经做了Semi Join如果计划Hash Semi Join说明 EXISTS 已经被很好去相关。此时人工改 JOIN大概率只是改变语义不一定带来物理执行收益。6.4 风险四相关子查询loops被忽略计划中单次 Inner0.01ms看起来快。但loops800万总体就很贵。和 Nested Loop 文章一样time × loops必须一起看。6.5 风险五JOIN DISTINCT制造隐藏Sort/Hash为了修重复DISTINCT会引入Sort/Unique HashAggregate并可能产生临时文件。所以“JOIN版本看起来简洁”不代表执行更省。6.6 风险六权限索引导致写放大复合索引越多授权写入越慢 WAL越多读写必须一起验收。6.7 风险七热点用户导致计划参数敏感普通员工100个资源超级管理员3000万资源同一 SQLNestLoop Semi未必同时适合。需要参数分组测试普通 高权限 超级管理员推荐的等价性验证流程任何子查询 → JOIN改写都先回答1. 原查询是存在性、反存在性、标量还是集合 2. JOIN右侧是否唯一 3. NULL语义是否一致 4. 重复行是否允许 5. 聚合是否改变 6. 主键集合是否完全一致 7. 权限边界是否完全一致然后才进入EXPLAIN ANALYZE推荐性能诊断顺序原SQL ↓ EXPLAIN ANALYZE ↓ 检查SubPlan / Semi / Anti / Join ↓ estimated vs actual ↓ loops ↓ ANALYZE ↓ 权限键索引 ↓ EXISTS/IN/JOIN对照 ↓ 结果集合差分 ↓ 并发验收回退方案如果 JOIN 改写上线后出现重复 漏数 越权 P95回归 写入下降立即1. 停止扩大新SQL流量 2. Feature Flag切回原EXISTS/IN 3. 保存新旧执行计划 4. 保存新旧结果差集 5. 做越权泄漏复核 6. 新索引先保留确认其他SQL依赖后再决定删除 7. 恢复所有仅用于实验的join参数权限 SQL 的回退优先级应该是正确性 安全性 性能而不是反过来。最终复盘“子查询改 JOIN 是否一定更快”的答案非常明确不一定。原因有三层。第一层优化器可能早就把子查询变成了Semi/Anti Join第二层普通JOIN可能产生更多行语义根本不等价第三层真正的性能瓶颈往往是统计信息、索引、相关循环次数和输入规模所以真正成熟的优化思路不是看到子查询 →机械改JOIN而是确认业务语义 →查看真实计划 →判断是否已去相关 →修统计/索引 →再比较不同SQL形态 →用结果集合证明等价如果只记住一句话子查询是否应该改 JOIN首先是一个“关系语义是否等价”的问题其次才是性能问题如果语义不等价再快的执行计划也是错误计划。附录 A权限 EXISTSSELECTo.order_idFROMbiz_order oWHEREEXISTS(SELECT1FROMuser_order_permission pWHEREp.user_id:uidANDp.tenant_ido.tenant_idANDp.order_ido.order_id);附录 B检查JOIN重复SELECTorder_id,COUNT(*)FROM(SELECTo.order_idFROMbiz_order oJOINuser_order_permission pONp.order_ido.order_idANDp.tenant_ido.tenant_idWHEREp.user_id:uid)xGROUPBYorder_idHAVINGCOUNT(*)1;附录 C执行计划EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;重点Semi Join Anti Join Hash Join Nested Loop SubPlan Loops Rows Removed Buffers Execution Time附录 D最低验收门禁[ ] 原SQL业务语义已分类 [ ] JOIN侧唯一性已证明或明确不存在 [ ] NULL语义已验证 [ ] 主键集合双向差异0 [ ] 越权行数0 [ ] estimated/actual无重大未解释偏差 [ ] SubPlan loops已量化 [ ] 权限索引有效 [ ] P95/P99达到SLA [ ] 新索引写成本通过 [ ] 热点管理员场景通过 [ ] 回退SQL已准备转载自https://blog.csdn.net/u014727709/article/details/163949536欢迎 点赞✍评论⭐收藏欢迎指正 
长河君 · 2026/8/23 19:34:09 · 阅读 1.2k 子查询改JOIN是否一定更快——权限过滤场景的等价语义、执行计划与性能测试实战
文章目录每日一句正能量1. 背景与问题把子查询改成JOIN可能快了也可能把权限结果改错了2. 环境与数据用真实的一对多权限模型验证而不是拿唯一键做演示2.1 EXISTS 的本质是 Semi Join 语义2.2 普通 JOIN 是行组合语义2.3 IN 与 EXISTS 也不能只看写法2.4 NOT IN 是更危险的语义边界3. 复现过程权限 EXISTS 为什么会从2秒退化到40秒3.1 原始相关 EXISTS3.2 第一步仍然是 ANALYZE3.3 真正问题权限表查找没有复合索引4. 方案实施五类查询改写必须逐一证明语义4.1 EXISTS → JOIN只有右侧唯一时才可直接等价4.2 JOIN DISTINCT 能恢复结果但不一定更快4.3 EXISTS 和 JOIN 可能产生同类底层计划4.4 NOT EXISTS 通常比手工 LEFT JOIN ... IS NULL 更直接4.5 NOT IN → NOT EXISTS 前先处理 NULL4.6 权限过滤索引应围绕“谁查什么”设计4.7 热点用户必须单独测4.8 权限表的一对多是正常业务不要为了JOIN强行加唯一约束4.9 如果确实要JOIN可以先去重权限键4.10 大权限集合可以先预聚合4.11 相关标量子查询比EXISTS更值得警惕4.12 OR 里的相关子查询更容易阻碍去相关5. 结果对比性能最快的E3为什么反而不能上线E0相关 EXISTS 无合适索引E1ANALYZEE2EXISTS 权限复合索引E3直接改普通 JOINE4JOIN DISTINCTE5反向过滤使用 NOT EXISTS5.1 汇总5.2 结果校验不能只比较COUNT5.3 权限SQL还必须做“泄漏校验”5.4 Buffer Read 是判断是否真的减少工作的关键5.5 新索引也要验写成本6. 风险与复盘子查询改JOIN最危险的是“语法更简单语义更复杂”6.1 风险一一对多重复6.2 风险二NOT IN 的 NULL6.3 风险三优化器本来已经做了Semi Join6.4 风险四相关子查询loops被忽略6.5 风险五JOIN DISTINCT制造隐藏Sort/Hash6.6 风险六权限索引导致写放大6.7 风险七热点用户导致计划参数敏感推荐的等价性验证流程推荐性能诊断顺序回退方案最终复盘附录 A权限 EXISTS附录 B检查JOIN重复附录 C执行计划附录 D最低验收门禁每日一句正能量人生就是一场旅行不在乎目的地在乎的应该是沿途的风景以及看风景的心情。重要的不是你获得了什么头衔而是你如何感受每一天的朝阳与晚风如何与同行者分享那些瞬间。主题查询改写 / 子查询与JOIN / 权限过滤重点EXISTS、IN、NOT EXISTS、NOT IN、Semi Join、Anti Join、重复行、NULL语义、执行计划、索引与并发验证适用场景KingbaseES 上的行级权限过滤、租户隔离、组织权限、订单可见性、白名单/黑名单、数据授权等复杂查询场景。1. 背景与问题把子查询改成JOIN可能快了也可能把权限结果改错了数据库优化里流传很广的一句话是子查询慢改 JOIN 就快。这句话最大的问题不是“有时不快”。而是有些子查询和 JOIN 在业务语义上根本不等价。权限过滤就是最容易踩坑的场景。例如SELECTo.order_id,o.amountFROMbiz_order oWHEREEXISTS(SELECT1FROMuser_order_permission pWHEREp.user_id:user_idANDp.tenant_ido.tenant_idANDp.order_ido.order_id);这段 SQL 的业务含义非常清楚只要当前用户至少拥有一条匹配权限 就返回这条订单。这里的核心不是“把权限表连进来”。而是Existence即“是否存在”。如果开发直接改成SELECTo.order_id,o.amountFROMbiz_order oJOINuser_order_permission pONp.tenant_ido.tenant_idANDp.order_ido.order_idWHEREp.user_id:user_id;假设一个用户对同一订单同时拥有角色权限 组织权限 项目权限 临时授权 区域授权五条记录。那么EXISTS返回1条订单而普通JOIN可能返回5条订单性能可能真的更快。但结果已经错了。这类优化事故最危险的地方就在于SQL执行成功 没有报错 延迟下降 业务还能跑只有报表金额、分页、订单数量或下游接口悄悄出现重复。所以本文第一个结论是查询改写必须先验证等价语义再比较执行计划。性能测试排在第二位。KingbaseES 官方文档对连接执行有 Semi Join、Anti Join 等概念而执行计划分析也强调最终连接方式由优化器根据统计信息和代价进行选择。很多EXISTS、IN形式本身就有机会被优化器解除子查询结构转成半连接执行因此“SQL 文本里看到子查询”并不等于“数据库一定逐行执行子查询”。2. 环境与数据用真实的一对多权限模型验证而不是拿唯一键做演示示例环境数据库 KingbaseES V9 订单 biz_order 订单量 8000万 权限 user_order_permission 权限记录 2.4亿 租户 3000 用户 120万权限表CREATETABLEuser_order_permission(user_idBIGINTNOTNULL,tenant_idBIGINTNOTNULL,order_idBIGINTNOTNULL,role_codeVARCHAR(32),org_idBIGINT,valid_flagINTNOTNULLDEFAULT1);这里故意不设置(user_id, tenant_id, order_id)唯一。因为真实权限系统通常允许同一个人 通过多个授权来源 看到同一数据这恰恰是判断EXISTS与普通JOIN是否等价的关键。2.1 EXISTS 的本质是 Semi Join 语义EXISTS只回答有没有找到第一条满足条件的权限以后逻辑上就不需要把其余匹配权限投影到结果中。因此它对应的是Semi Join思想左侧业务行 只保留一次2.2 普通 JOIN 是行组合语义JOIN 做的是左行 × 所有匹配右行如果1条订单 匹配5条权限结果就是最多5个组合所以两者只有在可以证明右表连接键唯一或者业务允许重复时才能直接说语义等价。2.3 IN 与 EXISTS 也不能只看写法例如WHEREorder_idIN(SELECTorder_idFROMuser_order_permissionWHEREuser_id:uid)在很多等值场景中优化器有机会转换成Semi Join所以IN一定慢 EXISTS一定快同样不是可靠结论。最终还是EXPLAIN ANALYZE说话。2.4 NOT IN 是更危险的语义边界例如WHEREorder_idNOTIN(SELECTorder_idFROMblacklist)如果子查询中存在NULLSQL 三值逻辑会让比较产生UNKNOWN结果可能与开发直觉完全不同。KingbaseES SQL 调优指南也专门给出NOT IN → NOT EXISTS的优化建议但明确附带条件连接列不存在NULL值时两者才可按该规则视为等价并可能利用不同 Anti Join 方式优化。所以NOT IN 改 NOT EXISTS 前必须先证明 NULL 条件而不是只为了拿到 Hash Anti Join。3. 复现过程权限 EXISTS 为什么会从2秒退化到40秒3.1 原始相关 EXISTSSELECTo.order_id,o.amountFROMbiz_order oWHEREo.tenant_id:tenant_idANDo.status1ANDEXISTS(SELECT1FROMuser_order_permission pWHEREp.user_id:user_idANDp.tenant_ido.tenant_idANDp.order_ido.order_idANDp.valid_flag1);如果权限表没有合适索引执行计划可能出现Outer 大量订单 Inner/SubPlan 权限表反复扫描例如Outer actual rows: 800万 Inner loops: 800万这时确实可能非常慢。但慢的根因并不是EXISTS三个字而是相关执行 × 大量Outer × Inner缺乏低成本访问路径3.2 第一步仍然是 ANALYZEKingbaseES 官方执行计划分析流程明确建议先检查估算是否准确 不准确则ANALYZE 再重新查看计划执行ANALYZEbiz_order;ANALYZEuser_order_permission;假设 P9542s →31s说明统计修复有效。但仍然很慢。3.3 真正问题权限表查找没有复合索引每次权限判断条件user_id tenant_id order_id valid_flag如果只有order_id单列索引仍可能访问大量无关权限。建立候选CREATEINDEXidx_perm_user_tenant_order_validONuser_order_permission(user_id,tenant_id,order_id,valid_flag);重新 ANALYZE。计划可能变成Nested Loop Semi Join 或 有效的Semi Join路径 Inner Index ScanP9531s →2.4s这说明保留 EXISTS 语义也完全可能把性能问题解决。没有必要为了速度先改 JOIN。4. 方案实施五类查询改写必须逐一证明语义4.1 EXISTS → JOIN只有右侧唯一时才可直接等价原WHEREEXISTS(SELECT1FROMpermission pWHEREp.order_ido.order_id)改JOINpermission pONp.order_ido.order_id必须先证明SELECTorder_id,COUNT(*)FROMpermissionGROUPBYorder_idHAVINGCOUNT(*)1;结果0如果不是 0不能直接改4.2 JOIN DISTINCT 能恢复结果但不一定更快为了修重复很多人继续SELECTDISTINCTo.*FROMorderoJOINpermission p...语义可能恢复到每订单一行但执行计划可能增加Sort Unique HashAggregate也就是说先把1行放大成5行 再花资源去重成1行从执行模型上并不漂亮。如果业务本质就是是否存在权限直接EXISTS更能表达意图。4.3 EXISTS 和 JOIN 可能产生同类底层计划现代 CBO 并不是逐字翻译 SQL。如果条件可去相关EXISTS可以被转换成Semi Join而不是真的外表每一行执行一次独立SQL所以优化时一定要区分SQL语法形态和物理执行计划KingbaseES 官方查询与子查询文档、连接计划文档都表明查询优化器会根据条件转换和代价选择 Merge、Hash、Nested Loop以及 Semi/Anti 等连接形式。因此如果 EXISTS 已经被优化成优良的 Semi Join再人工改成 JOIN 很可能没有收益。4.4 NOT EXISTS 通常比手工 LEFT JOIN … IS NULL 更直接反权限场景找出用户无权访问的记录可以WHERENOTEXISTS(...)也有人写LEFTJOINpermission p...WHEREp.order_idISNULL在条件满足时两者可能被优化器转换到类似 Anti Join。所以也不能说LEFT JOIN一定更快真正对比Hash Anti Join Nested Loop Anti Merge Anti以及实际扫描量4.5 NOT IN → NOT EXISTS 前先处理 NULL官方 SQL 调优指南明确指出连接列不存在NULL时可以把NOT IN转成NOT EXISTS并可能通过 Hash Anti Join 获得更优执行。因此生产改写流程必须1. 查看字段NOT NULL约束 2. 检查历史真实NULL 3. 确认业务是否应忽略NULL 4. 再进行改写而不是Advisor建议了 就直接批量替换4.6 权限过滤索引应围绕“谁查什么”设计权限 EXISTS 常见条件user_id tenant_id resource_id valid_flag索引(user_id, tenant_id, resource_id, valid_flag)很自然。但如果系统查询模式是先按tenantresource查可见用户索引顺序就可能不同。所以要根据真实查询入口 选择性 热点用户 租户规模设计。4.7 热点用户必须单独测普通用户100条权限管理员3000万条权限同一 SQL 的最佳计划可能不同。热点管理员场景Semi Join构建权限集合可能比逐订单点查权限更合适。所以测试参数必须至少分普通用户 部门管理员 超级管理员4.8 权限表的一对多是正常业务不要为了JOIN强行加唯一约束为了让JOIN不重复而把权限表设计成UNIQUE(user_id,order_id)可能直接破坏授权模型。正确方式应该是SQL匹配业务语义而不是为了SQL方便修改权限语义4.9 如果确实要JOIN可以先去重权限键例如JOIN(SELECTDISTINCTuser_id,tenant_id,order_idFROMuser_order_permissionWHEREuser_id:uidANDvalid_flag1)p这在某些场景有意义先把多来源授权压成资源集合 再与大业务表Join尤其管理员权限很多时。但要看DISTINCT成本 权限集合大小 后续Join方式4.10 大权限集合可以先预聚合例如同一用户500万授权明细但实际资源100万order_id可以先SELECT DISTINCT order_id形成较小集合。如果这个集合会被一个复杂查询多次使用还可以评估CTE 临时表 物化结果这里就与上一篇 CTE 文章衔接起来物化还是内联仍应由结果规模和重复使用成本决定。4.11 相关标量子查询比EXISTS更值得警惕例如SELECTo.order_id,(SELECTMAX(p.role_code)FROMpermission pWHEREp.order_ido.order_id)role_codeFROMbiz_order o;这不是存在性判断。它需要每个外表行得到一个标量结果如果优化器无法很好去相关可能形成大量 SubPlan loops。这类 SQL 改成预聚合 LEFT JOIN往往更有价值LEFTJOIN(SELECTorder_id,MAX(role_code)role_codeFROMpermissionGROUPBYorder_id)pONp.order_ido.order_id但仍然要检查结果等价 聚合范围 过滤能否前推4.12 OR 里的相关子查询更容易阻碍去相关类似WHEREEXISTS(...)OREXISTS(...)复杂 OR 条件可能让优化器难以解除子查询结构。KingbaseAnalyticsDB 官方查询文档也提到某些位于 SELECT 列表或 OR 条件中的相关子查询可能按外层每一行执行并建议通过 JOIN 或拆分查询重写。对于 KingbaseES 生产 SQL仍需以本版本实际计划为准但这类结构值得重点检查SubPlan loops5. 结果对比性能最快的E3为什么反而不能上线示例实验E0相关 EXISTS 无合适索引Outer: 800万 Inner loops: 800万 Buffers Read: 1800万 P95: 42s正确性PASSE1ANALYZEP95: 31s正确性PASSE2EXISTS 权限复合索引Plan: Semi Join / Index Lookup Buffers: 85万 P95: 2.4s正确性PASSE3直接改普通 JOINPlan: Hash Join P95: 1.7s看上去最快但是结果行数倍率: 3.7×因为同一订单有多个权限来源。正确性FAIL所以 E3 不能上线。这就是本文最重要的实验结论性能测试只有在语义等价以后才有意义。错误结果的 1.7 秒没有任何优化价值。E4JOIN DISTINCTHash Join →HashAggregate / Unique结果正确P954.1s反而比EXISTS 索引 2.4s更慢。E5反向过滤使用 NOT EXISTS如果需求找没有权限的订单测试可能得到Hash Anti Join / Index Anti P951.9s并且语义比NOT IN在 NULL 场景下更可控。5.1 汇总实验写法计划P95结果E0相关EXISTS重复SubPlan/Loop42s正确E1EXISTSANALYZE改进计划31s正确E2EXISTS复合索引Semi Join/Index2.4s正确E3普通JOINHash Join1.7s错误重复E4JOINDISTINCTJoin去重4.1s正确E5NOT EXISTSAnti Join1.9s正确以上为方法演示数据不是生产实测。5.2 结果校验不能只比较COUNT如果JOIN重复两条 同时漏两条总 COUNT 可能刚好相等。所以必须比较主键集合例如EXCEPT双向差集。至少Old EXCEPT New New EXCEPT Old都必须是0行5.3 权限SQL还必须做“泄漏校验”普通性能 SQL多一行可能只是业务 Bug。权限 SQL多一行可能是越权泄漏所以验收至少包括无权用户 临界角色 跨租户 失效权限 重复授权 管理员 NULL值5.4 Buffer Read 是判断是否真的减少工作的关键E01800万E285万说明复合索引真正减少了权限表访问。而不是刚好缓存更热这类数据比单次 Execution Time 更有复用价值。5.5 新索引也要验写成本权限系统可能高频授权 撤权 批量同步增加复合索引以后需要测INSERT DELETE UPDATE WAL 索引空间不能为了读 SQL2.4s把权限变更写入拖慢十倍。6. 风险与复盘子查询改JOIN最危险的是“语法更简单语义更复杂”6.1 风险一一对多重复这是权限过滤最常见事故。解决保持EXISTS 或 先去重右侧集合而不是用DISTINCT掩盖一切问题。6.2 风险二NOT IN 的 NULL如果右侧存在 NULLNOT IN可能与NOT EXISTS得到完全不同结果。改写前一定检查约束 真实数据6.3 风险三优化器本来已经做了Semi Join如果计划Hash Semi Join说明 EXISTS 已经被很好去相关。此时人工改 JOIN大概率只是改变语义不一定带来物理执行收益。6.4 风险四相关子查询loops被忽略计划中单次 Inner0.01ms看起来快。但loops800万总体就很贵。和 Nested Loop 文章一样time × loops必须一起看。6.5 风险五JOIN DISTINCT制造隐藏Sort/Hash为了修重复DISTINCT会引入Sort/Unique HashAggregate并可能产生临时文件。所以“JOIN版本看起来简洁”不代表执行更省。6.6 风险六权限索引导致写放大复合索引越多授权写入越慢 WAL越多读写必须一起验收。6.7 风险七热点用户导致计划参数敏感普通员工100个资源超级管理员3000万资源同一 SQLNestLoop Semi未必同时适合。需要参数分组测试普通 高权限 超级管理员推荐的等价性验证流程任何子查询 → JOIN改写都先回答1. 原查询是存在性、反存在性、标量还是集合 2. JOIN右侧是否唯一 3. NULL语义是否一致 4. 重复行是否允许 5. 聚合是否改变 6. 主键集合是否完全一致 7. 权限边界是否完全一致然后才进入EXPLAIN ANALYZE推荐性能诊断顺序原SQL ↓ EXPLAIN ANALYZE ↓ 检查SubPlan / Semi / Anti / Join ↓ estimated vs actual ↓ loops ↓ ANALYZE ↓ 权限键索引 ↓ EXISTS/IN/JOIN对照 ↓ 结果集合差分 ↓ 并发验收回退方案如果 JOIN 改写上线后出现重复 漏数 越权 P95回归 写入下降立即1. 停止扩大新SQL流量 2. Feature Flag切回原EXISTS/IN 3. 保存新旧执行计划 4. 保存新旧结果差集 5. 做越权泄漏复核 6. 新索引先保留确认其他SQL依赖后再决定删除 7. 恢复所有仅用于实验的join参数权限 SQL 的回退优先级应该是正确性 安全性 性能而不是反过来。最终复盘“子查询改 JOIN 是否一定更快”的答案非常明确不一定。原因有三层。第一层优化器可能早就把子查询变成了Semi/Anti Join第二层普通JOIN可能产生更多行语义根本不等价第三层真正的性能瓶颈往往是统计信息、索引、相关循环次数和输入规模所以真正成熟的优化思路不是看到子查询 →机械改JOIN而是确认业务语义 →查看真实计划 →判断是否已去相关 →修统计/索引 →再比较不同SQL形态 →用结果集合证明等价如果只记住一句话子查询是否应该改 JOIN首先是一个“关系语义是否等价”的问题其次才是性能问题如果语义不等价再快的执行计划也是错误计划。附录 A权限 EXISTSSELECTo.order_idFROMbiz_order oWHEREEXISTS(SELECT1FROMuser_order_permission pWHEREp.user_id:uidANDp.tenant_ido.tenant_idANDp.order_ido.order_id);附录 B检查JOIN重复SELECTorder_id,COUNT(*)FROM(SELECTo.order_idFROMbiz_order oJOINuser_order_permission pONp.order_ido.order_idANDp.tenant_ido.tenant_idWHEREp.user_id:uid)xGROUPBYorder_idHAVINGCOUNT(*)1;附录 C执行计划EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...;重点Semi Join Anti Join Hash Join Nested Loop SubPlan Loops Rows Removed Buffers Execution Time附录 D最低验收门禁[ ] 原SQL业务语义已分类 [ ] JOIN侧唯一性已证明或明确不存在 [ ] NULL语义已验证 [ ] 主键集合双向差异0 [ ] 越权行数0 [ ] estimated/actual无重大未解释偏差 [ ] SubPlan loops已量化 [ ] 权限索引有效 [ ] P95/P99达到SLA [ ] 新索引写成本通过 [ ] 热点管理员场景通过 [ ] 回退SQL已准备转载自https://blog.csdn.net/u014727709/article/details/163949536欢迎 点赞✍评论⭐收藏欢迎指正