数据库优化深度科普：外连接消除底层原理，KES优化器完整设计拆解

数据库优化深度科普外连接消除底层原理KES优化器完整设计拆解前言平时做数据库开发、运维或是国产化迁移工作的时候不少同行都踩过一种很迷惑的线上问题。我们SQL里明明写了LEFT JOIN本意是把左表全部数据都保留下来但导出报表、做数据对账的时候统计出来的记录直接少掉一大半。很多人第一反应都会觉得是KES有兼容问题实际完全不是这么一回事。这个现象是KES查询优化器自带的逻辑优化动作名字叫外连接消除属于关系代数里标准等价转换并不是数据库出故障。这篇内容我分三块来讲清楚。第一块从SQL三值逻辑、代数规则讲明白优化器为什么能直接改写LEFT JOIN第二块拆解KES优化器完整处理流程顺带对比MySQL、PG、Oracle的处理差别第三块给大家可直接跑的测试SQL、线上修改写法还有迁移阶段要遵守的规范。一、前置基础KES优化器完整执行链路想要看懂外连接消除的逻辑得先清楚一条SQL在KES内部会经历哪些处理步骤。外连接消除属于逻辑改写这一步是RBO规则里很关键的一块内容。1.1 KES处理SQL五层完整流程按照金仓官方内核文档里的说明SQL输入到最终执行一共分五个阶段词法/语法解析Parser核对我们写的SQL语法有没有错误生成原始的解析树结构。语义分析Analyzer校验表名、字段名、账号访问权限输出标准查询树。查询重写 Rewriter外连接消除就在这一步依托关系代数等价规则改写语句里面包含子查询展开、视图替换、外连接消除、谓词下推这类优化逻辑内核对应处理函数是remove_useless_joins。这里有个关键点系统会先做完整校验只有改写前后查询结果完全一致才会执行转换。代价优化 PlannerCBO物理优化读取表统计信息计算不同关联方式的开销挑选Hash Join、嵌套循环、合并连接其中一种物理执行算子。执行器 Executor按照最终生成的执行计划读取磁盘数据、做关联计算、输出查询结果。1.2 几款主流数据库处理规则横向对比不同数据库对于外连接消除的宽松程度不一样这也是跨库迁移后数据对不上的主要原因数据库优化器对外连接消除策略核心表现MySQL 8.0保守型RBO规则能触发转换的场景很少WHERE里加右表过滤不会删掉LEFT JOIN迁移报表数据不会变少Oracle中等约束只有复杂多条件场景才会转换简单等值筛选基本不会触发消除PostgreSQL/KES偏向激进的等价转换逻辑只要满足空值过滤条件自动转内连接很容易出现数据缺失KES是基于PG内核开发完整继承这套改写逻辑。同时针对政务、金融这类国产化项目额外加了迁移兼容提示、可手动开关Hint参数。二、底层数学逻辑LEFT JOIN会被改写的根本原因2.1 先搞懂可空侧与SQL三值逻辑拿A LEFT JOIN B举例这里A属于不会出现空的一侧B是可空侧。当A没有匹配的B记录时B所有字段都会填充NULL。SQL有一套三值判断规则NULL和任意常量做对比返回的结果都是UNKNOWN。WHERE子句会直接把这类行过滤掉。举个简单例子左表t1有四条记录右表t2只匹配id1、id2两行。执行LEFT JOIN之后t1里id3、id4对应的t2字段全是NULL。这时候如果WHERE写t2.name2 ‘cc’NULL和字符串对比不成立这两行直接被丢弃。从数据结果来看这条语句和内连接最后输出的内容完全相同。优化器就会判定外连接是多余操作直接替换成开销更低的内连接。2.2 KES内核三重判定规则在内核remove_useless_joins这个函数里要同时满足三条条件才会触发外连接消除关联类型是LEFT或者RIGHT全外连接不会做这个转换WHERE条件作用在可空的右侧表使用等值、区间、不等这类过滤被过滤的字段设置了NOT NULL约束NULL行一定会被全部筛掉。三条全部满足的情况下优化器就会抛弃LEFT JOIN算子改用Hash内连接。外连接需要缓存全部左表数据、填充空字段内存和IO消耗会高出不少。2.3 唯一不会触发的豁免场景如果过滤条件是IS NULL、IS NOT NULL优化器不会执行转换。这条语句本身的作用就是找出左表没有匹配的数据如果改成内连接这类空行会全部消失两边结果不一致优化器会跳过这套改写逻辑。三、完整可复现实战测试表执行计划对照3.1 测试表初始化SQL直接复制运行-- 左表 t1非空侧业务主表CREATETABLEt1(id1INTPRIMARYKEY,name1VARCHAR(32)NOTNULL);-- 右表 t2Nullable可空侧name2添加NOT NULL约束CREATETABLEt2(id2INTPRIMARYKEY,name2VARCHAR(32)NOTNULL);INSERTINTOt1VALUES(1,用户A),(2,用户B),(3,用户C),(4,用户D);INSERTINTOt2VALUES(1,cc),(2,bb);表里数据情况t1一共4条id3、id4在t2没有匹配记录标准LEFT JOIN需要全部输出。3.2 场景一触发外连接消除迁移高频踩坑写法1原始SQLSELECT*FROMt1LEFTJOINt2ONt1.id1t2.id2WHEREt2.name2cc;2MySQL里大家预期的输出id1name1id2name21用户A1cc2用户BNULLNULL3用户CNULLNULL4用户DNULLNULL3KES实际返回结果id1name1id2name21用户A1cc4EXPLAIN ANALYZE 执行计划佐证Hash Join Hash Cond: (t1.id1 t2.id2) - Seq Scan on t1 - Hash - Seq Scan on t2 Filter: ((name2)::text cc::text)计划里看不到Left标识直接变成普通Hash内连接也就是外连接消除生效。3.3 场景二WHERE使用IS NULL不会触发消除1测试SQLSELECT*FROMt1LEFTJOINt2ONt1.id1t2.id2WHEREt2.name2ISNULL;2KES执行结果id1name1id2name23用户CNULLNULL4用户DNULLNULL3执行计划Hash Left Join Hash Cond: (t1.id1 t2.id2) - Seq Scan on t1 - Hash - Seq Scan on t2 Filter: (t2.name2 IS NULL)计划明确标注Hash Left Join优化器不会做等价替换。3.4 场景三过滤条件放在左表不会触发消除SELECT*FROMt1LEFTJOINt2ONt1.id1t2.id2WHEREt1.name1用户A;执行计划依旧保留Left Join只会提前过滤左表本身的数据不会丢失无匹配行。四、三套标准化改写方案线上永久规避数据缺失方案1把附表过滤条件挪到ON后线上优先选用先筛选附表数据再执行左关联左表所有记录都会保留MySQL、Oracle、KES全都兼容。SELECT*FROMt1LEFTJOINt2ONt1.id1t2.id2ANDt2.name2cc;这条语句执行计划会保留Left Join同时可以正常使用t2字段索引。方案2子查询先过滤附表复杂报表专用碰到几百行的多层统计SQL不方便修改关联条件时可以用子查询提前筛选附表内容。SELECT*FROMt1LEFTJOIN(SELECT*FROMt2WHEREname2cc)t2ONt1.id1t2.id2;方案3Oracle老式()语法适配Oracle迁移项目KES兼容Oracle的()外连接写法但过滤条件不加()同样会触发消除。危险写法会丢失数据SELECT*FROMt1,t2WHEREt1.id1t2.id2()ANDt2.name2cc;安全标准写法SELECT*FROMt1,t2WHEREt1.id1t2.id2()ANDt2.name2()cc;附表字段带上()代表过滤逻辑属于外关联匹配阶段不会被后置WHERE改写。五、KES优化器配套兼容能力很多人会问既然这个转换容易造成迁移数据对不上为什么KES不直接关掉这个功能这里分开讲两部分考量。5.1 原生业务的性能提升点省去大量NULL填充、内存缓存操作内连接只保留匹配数据磁盘IO、CPU占用都会下降转换成内连接之后优化器能自由调整关联顺序提前下推过滤复杂报表查询速度能提升不少分组、聚合类语句的计算开销同步降低海量流水表效果更明显。5.2 国产化迁移配套的兼容手段针对从MySQL、Oracle迁移过来的项目KES提供三种切换方式平衡数据准确性和查询速度。方式1单条SQL添加Hint临时关闭优化单张报表不想触发外连接消除可以加上指定Hint强制保留左连接算子/* NoOuterJoinElim */SELECT*FROMt1LEFTJOINt2ONt1.id1t2.id2WHEREt2.name2cc;方式2全局参数统一关闭优化整套系统还在迁移过渡阶段可以全局关闭这套改写逻辑SETenable_outer_join_eliminateoff;参数关闭后所有语句都不会做外连接消除和MySQL输出结果保持一致等业务全部稳定再打开参数恢复性能。方式3KD迁移工具批量检测KES配套的KDMS迁移评估工具扫描存量SQL、存储过程的时候会自动标记容易触发消除的LEFT JOIN语句给出对应的改写建议批量改造很省心。六、线上问题排查、代码评审标准流程6.1 用执行计划快速判断执行EXPLAIN ANALYZE查看算子名称计划里出现Hash Left Join / Nested Loop Left Join没有触发消除数据不会少只写Hash Join、Nested LoopWHERE又存在附表等值过滤大概率触发消除需要改SQL。6.2 迁移项目代码评审硬性要求使用LEFT/RIGHT JOIN时附表的等值、区间筛选统一写到ON子句里只有统计无匹配数据的场景才允许在WHERE写IS NULLOracle老式()语法附表过滤字段必须同步带上()上线之前用KDMS工具扫描全部存储过程和业务SQL。6.3 真实线上故障复盘之前接触过一个零售行业迁移项目整套系统从MySQL换到KES。上线门店订单报表之后月度统计数据直接少了四成。出问题的原始SQLSELECTshop_id,order_no,refund_amountFROMshop_order sLEFTJOINorder_refund rONs.order_nor.order_noWHEREr.refund_type1;问题根源MySQL不会触发消除KES自动转成内连接没有退款的订单全部被过滤。修改后标准语句SELECTshop_id,order_no,refund_amountFROMshop_order sLEFTJOINorder_refund rONs.order_noANDr.refund_type1;修改之后统计数值和原系统完全一致查询速度也没有下降。七、全文总结外连接消除是KES优化器基于关系代数的正常改写动作不属于数据库bug。只要WHERE对可空附表做等值、区间过滤优化器就会判定LEFT JOIN和内连接结果相同自动转换降低开销。整套改写流程发生在SQL重写阶段内核会做三层校验只有IS NULL这类查询优化器会跳过转换逻辑。MySQL和KES优化策略差别很大这也是国产化迁移时报表、对账数据对不上的主要诱因。线上最稳妥的处理方式就是把附表过滤条件全部放到JOIN后面的ON子句全数据库通用从根源避免数据丢失。KES提供单语句Hint、全局参数、迁移工具三种兼容方案既能保证迁移期数据准确稳定后也能开启优化提升速度。做国产化迁移上线前一定要核对执行计划不要把风险LEFT JOIN语句直接投产避免线上统计数据出错。