文章目录每日一句正能量1. 背景与问题每个条件单独都走索引为什么用OR连起来反而全表扫2. 环境与数据先把OR分成四类不能所有OR都改UNION ALL2.1 第一类同一列多值 OR2.2 第二类不同列精确检索2.3 第三类可选参数拼成“大OR”2.4 第四类权限、多路径业务 OR3. 复现过程为什么OR会从多个索引退化成Seq Scan3.1 基线SQL3.2 先看每个分支单独计划3.3 OR不是一定不能组合索引3.4 为什么会放弃BitmapOr3.5 ANALYZE以后再次比较4. 方案实施UNION ALL真正的价值是把一个复杂选择性问题拆成多个简单访问路径4.1 原OR4.2 示例计划4.3 UNION ALL最大风险重复4.4 怎样证明互斥4.5 如果多个入口可以同时传入4.6 方案AUNION去重4.7 方案B人工让分支互斥4.8 动态SQL往往比固定大OR更适合“单入口搜索”4.9 动态SQL必须使用Bind变量4.10 同列OR优先改IN4.11 ORDER BY LIMIT是拆分OR时最容易出错的地方4.12 正确做法是全局排序4.13 分支内Limit能不能做4.14 OR里混合不可索引表达式时拆分价值更明显4.15 OR里的权限条件也可能阻碍子查询去相关4.16 统计信息和多列相关性仍然是基础5. 结果对比从16.8秒到120ms最快方案其实不是UNION ALLE0原始 ORE1ANALYZE / 统计修复E2UNION ALLE3UNIONE4动态SQLE5错误的分支Limit5.1 实验汇总5.2 为什么Buffers比单次耗时更值得保存5.3 Temp也要观察5.4 参数组必须分开测5.5 并发验收6. 风险与复盘OR改写最容易出现的事故不是SQL变慢而是集合语义变化6.1 风险一UNION ALL重复6.2 风险二UNION去重成本被忽略6.3 风险三分支内LIMIT破坏全局Top-N6.4 风险四NULL语义6.5 风险五动态SQL SQL注入6.6 风险六计划缓存和参数敏感6.7 风险七过度拆分造成应用复杂度推荐判定流程回退方案最终复盘附录 A基础OR计划附录 BUNION ALL改写附录 C同列OR附录 D最低验收门禁每日一句正能量最奢侈的拥有是能幸福地看一次月升月落。无价的奢侈——内心的安宁与共赏的温情。它能拥有时间、美与爱是物质无法衡量的丰盈。主题条件拆分 / OR优化 / 多入口检索重点OR、IN、UNION ALL、UNION、BitmapOr、选择性估算、动态SQL、全局排序分页、执行计划与结果等价适用场景KingbaseES 中订单多入口查询、客户检索、权限查询、风控规则、搜索后台等“多个字段任一命中即可”的 SQL。1. 背景与问题每个条件单独都走索引为什么用OR连起来反而全表扫多入口检索非常常见。例如客服后台允许通过订单号 手机号 证件号 外部流水号任意一个入口查订单。开发通常写成SELECTorder_id,order_no,mobile,ext_trade_no,created_atFROMsearch_orderWHEREtenant_id:tenant_idAND(order_no:order_noORmobile:mobileORid_card:id_cardORext_trade_no:ext_trade_no)ORDERBYcreated_atDESCLIMIT50;单独执行WHEREtenant_id:tenant_idANDorder_no:order_no走唯一索引。单独WHEREtenant_id:tenant_idANDmobile:mobile也能走索引。于是很多人理所当然认为四个索引条件 OR 在一起 数据库就分别走四次索引但真实计划可能是Seq Scan search_order Filter: order_no? OR mobile? OR id_card? OR ext_trade_no?甚至表有8000万行最终只命中42行P95 却达到十几秒。为什么因为优化器不是按“每个条件单独都快”来选择计划。它需要估算整个P(A OR B OR C OR D)的选择性、重复关系、相关性和成本。如果列之间存在相关性 热点值 NULL很多 数据倾斜或者某一个分支本身不可索引整个 OR 组合的成本估算就可能发生明显变化。KingbaseES 官方 SQL 调优文档强调SQL 性能优化依赖准确的统计信息统计信息过旧、缺少多列统计或数据倾斜时优化器可能选择非最优计划。官方也建议通过实际执行计划定位问题而不是仅从 SQL 文本判断。所以OR 退化的本质不是“数据库不支持 OR”而是多个分支被绑定成一个选择性与访问路径决策任何一个分支的统计、索引或语义问题都可能影响整体计划。2. 环境与数据先把OR分成四类不能所有OR都改UNION ALL示例系统数据库 KingbaseES V9 订单表 search_order 数据量 8000万 租户 3000 查询入口 订单号 手机号 证件号 外部流水号 状态索引(tenant_id,order_no)(tenant_id,mobile)(tenant_id,id_card)(tenant_id,ext_trade_no)2.1 第一类同一列多值 OR例如status1ORstatus2ORstatus3这类一般不需要拆成三条 SQL。可以首先改成statusIN(1,2,3)语义更清晰。优化器也更容易把它视为同一列上的集合条件所以同列等值 OR 的第一候选通常是 IN而不是 UNION ALL。2.2 第二类不同列精确检索例如order_no:xORmobile:yORext_trade_no:z这些字段拥有不同索引 不同选择性这是最值得测试BitmapOr vs UNION ALL的场景。2.3 第三类可选参数拼成“大OR”很多框架会写WHERE(:order_noISNOTNULLANDorder_no:order_no)OR(:mobileISNOTNULLANDmobile:mobile)OR(:id_cardISNOTNULLANDid_card:id_card)OR(:ext_noISNOTNULLANDext_trade_no:ext_no)实际每次可能只传一个参数但 SQL 永远保留四个分支这种场景最优解有时不是UNION ALL而是动态 SQL 只拼真正有值的入口。这样优化器看到的是一条非常简单、选择性非常明确的查询2.4 第四类权限、多路径业务 OR例如owner_id:uidORdept_idIN(...)OREXISTS(...)多个分支可能命中同一业务行如果直接拆UNION ALL会产生重复。所以这类必须先做集合等价验证再考虑拆分。3. 复现过程为什么OR会从多个索引退化成Seq Scan3.1 基线SQLEXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...FROMsearch_orderWHEREtenant_id:tenant_idAND(order_no:order_noORmobile:mobileORext_trade_no:ext_trade_no);示例actual rows: 42 Buffers Read: 1200万 P95: 16.8s Plan: Seq Scan结果极少。扫描却极大。3.2 先看每个分支单独计划订单号Index Scan 0.4ms手机号Index Scan 12ms外部流水号Index Scan 0.8ms说明索引本身并不是不存在问题发生在组合条件3.3 OR不是一定不能组合索引优化器可能产生Bitmap Index Scan \ Bitmap Index Scan \ BitmapOr ↓ Bitmap Heap Scan也就是说OR完全可能利用多个索引。如果计划已经BitmapOr而性能符合 SLA不需要为了“规范”强行UNION ALL这是本文第二个重要原则不要把 UNION ALL 当成 OR 的固定替换模板。先看优化器有没有已经生成优秀的 BitmapOr。3.4 为什么会放弃BitmapOr可能原因包括某个分支选择性很差 某个字段大量NULL 热点手机号命中几十万行 统计信息不准确 类型隐式转换 函数包裹索引列例如mobile::BIGINT:mobile可能导致原有字符索引无法正常利用。KingbaseES 官方 SQL 优化建议也专门把隐式类型转换导致索引失效列为改写建议之一。所以看到OR慢第一步不是拆。而是逐分支检查可索引性3.5 ANALYZE以后再次比较执行ANALYZEsearch_order;假设计划变成BitmapOrP9516.8s →7.2s说明统计问题确实存在。但业务 SLA500ms仍然没有达到。这时才进入 SQL 拆分实验。4. 方案实施UNION ALL真正的价值是把一个复杂选择性问题拆成多个简单访问路径4.1 原ORWHEREorder_no:xORmobile:yORext_trade_no:z优化器需要综合估算。拆开SELECT...WHEREorder_no:xUNIONALLSELECT...WHEREmobile:yUNIONALLSELECT...WHEREext_trade_no:z每个分支可以拥有独立索引 独立选择性 独立计划最终Append组合结果。4.2 示例计划原 ORSeq Scan拆分后Append - Index Scan idx_orderno - Index Scan idx_mobile - Index Scan idx_extno这种改写最大的收益不是UNION ALL本身很快而是让每个入口都能按自己的最佳访问路径执行。4.3 UNION ALL最大风险重复假设一条订单order_no:x同时mobile:y两个分支都会返回同一个order_id原 OR只返回一行UNION ALL返回两行所以只有分支互斥时才能直接使用。4.4 怎样证明互斥例如业务 API 规定一次只能按一个入口查询那么order_no/mobile/ext_no实际上不会同时有值。这种场景根本不应该写 OR。直接动态 SQL哪个参数有值 就生成哪个条件更合理。4.5 如果多个入口可以同时传入先定义业务语义。是任意一个命中还是所有条件同时满足如果是任意一个命中UNION ALL 就要处理重复。4.6 方案AUNION去重SELECT...WHEREorder_no:xUNIONSELECT...WHEREmobile:yKingbaseES 官方调优建议明确说明UNION需要去重。而UNION ALL不去重因此在业务可以确认集合不重叠的情况下把 UNION 替换为 UNION ALL 能避免去重成本。反过来也说明如果集合确实可能重叠UNION 的去重成本是正确性成本不能为了性能直接删掉。4.7 方案B人工让分支互斥例如定义优先级订单号 手机号 外部流水号第一分支order_no:x第二分支排除已被第一分支覆盖的结果。第三分支再排除前两条。这样仍然可以UNION ALL但条件复杂度增加。必须特别检查NULL 空字符串 Collation语义。4.8 动态SQL往往比固定大OR更适合“单入口搜索”如果前端 UI 明确订单号检索 手机号检索 证件号检索一次只选一个搜索类型。最佳 SQL 就应该直接生成WHEREtenant_id:tANDorder_no:x而不是维护四个永远存在的OR分支这样计划更稳定 SQL更简单 索引路径更明确4.9 动态SQL必须使用Bind变量不要WHERE order_no userInput 为了优化制造 SQL 注入风险。应该动态拼条件结构 Bind参数而不是动态拼值4.10 同列OR优先改IN例如status1ORstatus2ORstatus3改statusIN(1,2,3)通常比拆3个UNION ALL更简洁。而且不会产生重复语义问题4.11 ORDER BY LIMIT是拆分OR时最容易出错的地方原AORBORCORDERBYcreated_atDESCLIMIT20语义先得到全部匹配集合 再从整个集合里取最新20条错误改写SELECT...WHEREAORDERBYcreated_atDESCLIMIT20UNIONALLSELECT...WHEREBORDERBYcreated_atDESCLIMIT20...然后直接返回。这会得到每个分支自己的Top20而不是全局Top20结果可能完全不同。4.12 正确做法是全局排序SELECT*FROM(SELECT...WHEREAUNIONALLSELECT...WHEREBUNIONALLSELECT...WHEREC)uORDERBYcreated_atDESCLIMIT20;这样Top-N仍然在所有分支合并以后执行。4.13 分支内Limit能不能做只有能够证明分支局部Top-K不会丢失全局Top-N候选时才可以。例如有数学上安全的每分支取N条 再全局取N如果所有分支内部都按同一排序键且每分支只需要提供其前 N 才可能进入全局前 N那么可以作为进一步优化实验。但必须单独证明。不要凭直觉做。4.14 OR里混合不可索引表达式时拆分价值更明显例如order_no:xORlower(customer_name)lower(:name)第一个高选择性索引。第二个可能无法使用普通索引整体 OR 计划可能偏向Seq Scan拆分后订单号分支走索引 姓名分支单独处理至少不会让一个慢分支拖累全部入口。但更正确的长期方案函数索引 规范化列 全文检索可能更适合。4.15 OR里的权限条件也可能阻碍子查询去相关例如WHEREo.owner_id:uidOREXISTS(...)这种结构同时包含直接条件 相关子查询更容易出现复杂计划。可以评估拆成本人数据 UNION 授权数据但必须处理本人同时也有授权的重复问题。这和上一篇 EXISTS/JOIN 文章的原则一致先证明集合语义 再优化4.16 统计信息和多列相关性仍然是基础例如mobile id_card并非独立。同一个自然人手机号和证件号高度相关简单的独立选择性估算可能失真。所以 OR 计划退化时仍应检查ANALYZE 统计目标 扩展统计 热点值不能所有问题都靠 UNION ALL 绕过。5. 结果对比从16.8秒到120ms最快方案其实不是UNION ALLE0原始 ORPlan: Seq Scan actual result: 42 Buffers: 1200万 P95: 16.8sE1ANALYZE / 统计修复Plan: BitmapOr Buffers: 420万 P95: 7.2s说明优化器本身能够利用多个索引只是成本仍然较高。E2UNION ALLAppend Index Scan(order_no) Index Scan(mobile) Index Scan(ext_no)Buffers8.6万P95480ms性能提升很明显。但是正确性取决于分支是否重叠E3UNIONAppend →HashAggregate/Sort UniqueP95920ms比 UNION ALL 慢。但如果分支可能重复它才是正确方案之一E4动态SQL真实业务一次只会传一个搜索入口所以只生成WHEREtenant_id:tANDorder_no:x计划单 Index ScanP95120ms这是全场最快。这个实验说明真正最好的 OR 优化有时不是“如何把 OR 拆快”而是先问业务为什么需要把根本不会同时出现的分支写成 OR。E5错误的分支Limit每个分支LIMIT 20然后直接拼接。P95200ms非常快。但全局Top20错误所以不可上线5.1 实验汇总实验方案计划P95正确性E0原ORSeq Scan16.8sPASSE1统计修复BitmapOr7.2sPASSE2UNION ALLIndex Scan×3 Append0.48s依赖互斥E3UNIONAppend 去重0.92sPASSE4动态SQL单分支Index Scan0.12sPASSE5错误分支LIMIT局部Top-N0.20sFAIL以上为方法演示数据不是生产实测。5.2 为什么Buffers比单次耗时更值得保存原 SQLBuffers1200万拆分8.6万动态单分支1200这说明优化确实减少了数据库实际读取工作量而不是只因为测试时缓存变热5.3 Temp也要观察UNION需要去重如果结果集合很大可能出现HashAggregate Sort 临时文件所以UNION ALL 0 Temp不能简单拿来证明UNION应该换掉如果重复必须去除这个成本就是语义成本5.4 参数组必须分开测至少订单号唯一命中 手机号普通命中 热点手机号大量命中 外部流水号唯一命中 多个入口同时有值 全部参数为空因为 OR 的选择性高度参数敏感单测一个参数不能代表生产。5.5 并发验收拆 UNION ALL 后单SQL可能同时启动多个索引分支高并发下需要观察CPU Buffer Hit 随机IO P95/P99动态单分支通常资源更低但会增加SQL形态数量要通过参数化、query mapping 和应用治理控制。6. 风险与复盘OR改写最容易出现的事故不是SQL变慢而是集合语义变化6.1 风险一UNION ALL重复原 OR集合语义同一行不因为满足两个谓词就出现两次。UNION ALL分支结果直接拼接会重复。这是第一检查项。6.2 风险二UNION去重成本被忽略为了正确性改UNION可能增加HashAggregate Sort Temp所以要比较正确但稍慢和快但重复只有前者能上线。6.3 风险三分支内LIMIT破坏全局Top-N这是多入口检索最隐蔽的错误之一。所有排序分页都要明确局部 还是 全局6.4 风险四NULL语义人工把分支改成互斥时column:value遇 NULL不是TRUE而是 UNKNOWN。因此排重条件更适合基于明确NOT NULL约束或者使用经过验证的 NULL-safe 逻辑。6.5 风险五动态SQL SQL注入动态条件可以。动态拼接未转义值不可以只动态生成SQL结构所有值仍然Bind6.6 风险六计划缓存和参数敏感手机号普通用户命中3行客服热线号码命中50万行同一个 SQL 计划未必同时适合。需要用热点参数 普通参数分组测试。6.7 风险七过度拆分造成应用复杂度一个 OR 拆成12条UNION ALLSQL 可能比原来更难维护。如果根因是缺索引 错误类型转换 统计陈旧应该先解决根因。而不是机械拆到不可读。推荐判定流程遇到 OR 慢查询1. 每个分支单独EXPLAIN 2. 确认是否可索引 3. ANALYZE 4. 看OR整体是否BitmapOr 5. 检查estimated/actual 6. 判断分支是否互斥 7. OR / UNION ALL / UNION三组对照 8. 若一次只有一个入口改动态SQL 9. 验证全局ORDER BY/LIMIT 10. 做主键集合双向差分回退方案如果 OR 拆分上线以后出现重复 漏数 分页错误 热点参数回归 P99升高执行1. 停止扩大新SQL 2. Feature Flag / Query Mapping切回原OR 3. 保存新旧参数与计划 4. 执行主键集合双向差分 5. 检查重复行 6. 恢复测试时使用的会话参数 7. 新索引暂不删除先做依赖和写成本复核最终复盘OR 条件优化并不是OR一定慢 UNION ALL一定快更准确的理解是OR把多个谓词绑定成一次整体计划决策而UNION ALL把不同入口拆成多个独立计划如果各分支选择性差异大 索引不同 参数大部分为空拆分往往能带来很大收益。但如果优化器已经BitmapOr而且性能满足 SLA没必要强改如果分支重叠UNION ALL还会改变结果如果一次只有一个搜索入口动态SQL通常比“大OR”更直接如果只记住一句话OR 条件真正的优化原则不是“拆掉 OR”而是让不同检索入口拥有与自身选择性和索引匹配的访问路径同时确保拆分以后仍然保持原查询的集合、排序和分页语义。最终裁决依然来自Execution Plan actual rows Buffers P95/P99 结果集合差分而不是语法偏好。附录 A基础OR计划EXPLAIN(ANALYZE,BUFFERS,VERBOSE)SELECT...FROMsearch_orderWHEREtenant_id:tenant_idAND(order_no:order_noORmobile:mobileORext_trade_no:ext_trade_no);重点Seq Scan BitmapOr Bitmap Index Scan Index Scan Rows Removed by Filter actual rows Buffers附录 BUNION ALL改写SELECT...FROMsearch_orderWHEREtenant_id:tANDorder_no:order_noUNIONALLSELECT...FROMsearch_orderWHEREtenant_id:tANDmobile:mobile;必须先验证分支重复附录 C同列ORstatusIN(1,2,3)优先于status1ORstatus2ORstatus3附录 D最低验收门禁[ ] 每个OR分支单独计划已保存 [ ] 各分支可索引性已确认 [ ] estimated/actual无重大未解释偏差 [ ] OR整体BitmapOr/SeqScan原因已确认 [ ] UNION ALL分支互斥已证明 [ ] 主键集合双向差异0 [ ] 重复行0 [ ] NULL语义已验证 [ ] 全局ORDER BY/LIMIT语义一致 [ ] 热点/普通参数已测试 [ ] P95/P99达到SLA [ ] Buffer Read明显改善或有合理解释 [ ] 回退SQL已准备转载自https://blog.csdn.net/u014727709/article/details/163949843欢迎 点赞✍评论⭐收藏欢迎指正