数据库优化深度科普｜外连接消除底层原理与KES优化器设计思路拆解

文章目录先搞清楚优化器在干啥外连接消除的核心空值拒绝条件这事儿其实没那么简单——几种界定方式的分歧从理论到工程外连接消除的研究脉络与争鸣KES优化器的逻辑优化全景从设计哲学的角度看优化器行为几个容易踩的具体场景给迁移团队的建议兼容是对前人努力的尊重是确保业务平稳过渡的基石然而这仅仅是故事的起点我一直在想一个问题——数据库优化器到底应不应该自作主张改写用户写的SQL这个念头最开始冒出来是我在研究KES查询优化器的时候。翻金仓官方产品手册看到查询优化器简介那一节提到一个叫外连接消除的逻辑优化技术。手册里就一句话——“查询优化将满足特定条件的外连接转换为内连接完成外连接的消除转换的条件是看WHERE子句中与内表相关的条件是否满足空值拒绝条件。”就这么一句话看着轻描淡写但背后牵扯出来的东西特别多。空值拒绝条件是什么为什么外连接能转成内连接优化器凭什么替你做这个决定这些问题串起来其实就是在问——优化器的逻辑优化到底怎么设计的边界在哪里。今天这篇就当一次拆解吧我尽量把外连接消除从里到外讲透不光说现象还要说原理说设计思路。先搞清楚优化器在干啥要理解外连接消除得先知道数据库优化器的工作流程。一条SQL写完丢给数据库它不是直接就去执行的中间要经过两个阶段的优化——基于规则的逻辑优化RBO和基于代价的物理优化CBO。逻辑优化阶段干的事儿说白了就是根据关系代数的等价变换规则把你的SQL改写成另一种效果一样但更好执行的形式。这一步不关心执行代价只关心逻辑上是否等价。金仓官方文档里列了一堆逻辑优化技术视图重写、子查询上拉、表达式重写、条件化简、外连接消除、谓词下推、连接消除、公共表达式合并……外连接消除只是其中之一。物理优化阶段才是真正算代价的地方。它会估算不同执行计划的CPU开销、IO开销选一个预估最快的。但请注意物理优化是在逻辑优化改写完SQL之后才做的。也就是说如果逻辑优化阶段把你的外连接改成了内连接物理优化阶段拿到的就已经是内连接了它压根不知道你原来写的是外连接。这个流程设计本身没问题但它意味着——逻辑优化阶段的改写决策非常关键一旦改错了后面的物理优化再精确也救不回来。外连接消除的核心空值拒绝条件好现在说重点。外连接消除的核心判断依据是空值拒绝条件英文叫reject-NULL condition。这个概念听着唬人其实拆开来看没那么复杂。我们用LEFT JOIN来举例。LEFT JOIN的特点是左表的行全部保留右表没有匹配的行就用NULL补齐。这些被NULL补齐的行就是外连接跟内连接唯一的区别——内连接不会有这些NULL行。那么问题来了如果WHERE子句里有一个条件恰好能把所有这些NULL行都过滤掉呢举个例子CREATETABLEorders(order_idSERIALPRIMARYKEY,customer_idINT,amountDECIMAL(10,2));CREATETABLEcustomers(customer_idINTPRIMARYKEY,customer_nameVARCHAR(100),vip_levelVARCHAR(10));INSERTINTOorders(customer_id,amount)VALUES(1,100.00),(2,200.00),(NULL,50.00),(3,300.00);INSERTINTOcustomersVALUES(1,张三,GOLD),(2,李四,SILVER),(3,王五,GOLD);现在写一条查询-- 想查所有订单顺便带出VIP等级是GOLD的客户信息SELECTo.order_id,o.amount,c.customer_name,c.vip_levelFROMorders oLEFTJOINcustomers cONo.customer_idc.customer_idWHEREc.vip_levelGOLD;你脑子里想的是所有订单都返回匹配到GOLD客户的就显示客户信息没匹配到的客户信息显示NULL。但实际上呢LEFT JOIN执行完之后order_id3那条记录customer_id是NULL的那个右表字段全是NULL。然后WHERE c.vip_level GOLD’开始过滤——NULL GOLD’的结果是Unknown在WHERE里被当false处理这行就没了。同样的customer_id2的李四vip_level是SILVER不等于GOLD也没了。最终只剩下customer_id1和3那两条GOLD客户的订单。这跟直接写INNER JOIN的结果一模一样。优化器看到的就是这一点——WHERE条件c.vip_level GOLD’是一个针对右表customers可空侧的过滤条件它会把所有右表为NULL的行都拒绝掉。这就是所谓的空值拒绝条件——这个条件拒绝掉了所有NULL值行。既然外连接产生的NULL行注定会被WHERE干掉那外连接的存在就没有意义了逻辑上等价于内连接。优化器就把LEFT JOIN改写成INNER JOIN然后物理优化阶段可以选更多更高效的执行路径。-- 看看执行计划Left字样消失了EXPLAINSELECTo.order_id,o.amount,c.customer_name,c.vip_levelFROMorders oLEFTJOINcustomers cONo.customer_idc.customer_idWHEREc.vip_levelGOLD;QUERY PLAN -------------------------------------------- Hash Join Hash Cond: (o.customer_id c.customer_id) - Seq Scan on orders o - Hash - Seq Scan on customers c Filter: (vip_level GOLD::text)你看计划里是Hash Join不是Hash Left Join。这就是外连接被消除的铁证。这事儿其实没那么简单——几种界定方式的分歧说到这儿我觉得有必要把外连接消除这个概念本身拿出来聊聊。因为这个词在不同语境下指的东西不完全一样。一种狭义的界定是特指外连接转内连接——也就是我们上面讨论的这种场景优化器通过判断WHERE条件是否满足空值拒绝把OUTER JOIN降级为INNER JOIN。金仓官方文档用的就是这种界定。另一种广义的界定把连接消除也算进来——当存在主外键约束的时候优化器可以直接把某个表的扫描整个跳过。比如你查SELECT * FROM t1 LEFT JOIN t2 ON t1.id t2.fk而t2.fk上有NOT NULL约束并且是t1.id的外键那t2这张表可能整个被优化掉连扫描都不用做。还有第三种理解更宽泛泛指一切减少外连接开销的优化手段包括谓词下推把过滤条件提前到ON子句里执行也算。我在这篇文章里采用的是第一种界定——外连接转内连接。原因有两个一是这跟金仓官方文档的描述一致讨论的时候不会产生歧义二是这种场景在实际迁移和开发中遇到得最多影响面最大。但需要注意的是如果你看其他资料讨论join elimination的时候说的是另一种东西别搞混了。概念界定不清晰是很多技术争论的根源大家在说同一个词但指的不是同一件事儿吵了半天其实没在一个频道上。从理论到工程外连接消除的研究脉络与争鸣外连接消除并不是哪个数据库厂商的发明它的理论基础可以追溯到关系代数的早期研究。但要真正理解它的来龙去脉我觉得得把时间线拉长了看。上世纪七八十年代关系数据库理论刚起步的时候研究焦点在关系代数的等价变换规则上。Codd提出关系模型之后一批学者开始系统性地研究哪些变换是安全的——也就是说变换前后的结果集在所有可能的数据分布下都保持一致。外连接的等价变换就是在这一时期被形式化定义的核心结论是如果存在一个空值拒绝谓词作用在可空侧那么外连接可以被安全地转换为内连接。这个结论在数学上是严谨的至今没有争议。但理论严谨不等于工程上没有分歧。进入九十年代以后各家数据库厂商开始把理论落地到实际产品中这时候分歧就出现了。分歧的焦点不在于能不能消除——理论上当然能——而在于什么时候消除以及消除的判断条件设多严格。一派观点认为既然数学上证明了等价性优化器就应该积极利用这个机会来提升性能能消除就消除。这种思路的代表是一些追求极致性能的数据库系统它们的外连接消除触发条件相对宽松只要检测到WHERE中有针对可空侧的常规谓词就会触发。另一派则更保守认为优化器不应该过度干预用户SQL的语义表达。即使数学上等价用户写LEFT JOIN可能有他的意图优化器擅自改写可能掩盖用户SQL中潜在的问题。这一派的产品往往只在非常明确的场景下才做消除或者干脆提供参数让用户自己决定是否开启。我个人的看法是这两派其实都不完全对。激进派的思路在性能上确实占优但它忽略了一个现实——大量开发人员写SQL的时候对三值逻辑TRUE/FALSE/UNKNOWN根本没有清晰的认识。他们以为LEFT JOIN WHERE右表条件就能保留左表全部数据这本身就是对SQL语义的误解。优化器帮你消除外连接从结果上来说是正确的——因为你的SQL语义确实等价于内连接。但它同时也帮你掩盖了这个本该暴露出来的编码错误。保守派虽然让错误可见了但代价是牺牲了本可以获得的性能优化。这个分歧在今天依然存在而且随着国产数据库的崛起变得更加凸显。KES的优化器在这方面的策略偏向激进——它的等价变换检查比较积极会主动识别并消除满足条件的外连接。这在原生KES环境下不是问题但在异构迁移场景下源库可能恰好属于保守派同样的SQL在两边行为就不一样了。还有一个被学术界长期忽视的角度——优化器的改写决策缺乏可观测性。我看过不少论文讨论外连接消除的算法优化但很少有研究关注用户如何知道优化器做了什么改写。执行计划能展示最终结果但它不会告诉你这里本来是外连接我帮你改成了内连接。对于排查问题的人来说这种透明度缺失是实实在在的痛点。近年来有一些研究开始关注优化器的可解释性但大部分还停留在学术阶段。KES在这块的做法是——你可以通过EXPLAIN看到改写后的执行计划通过计划中Join算子是否带有Left字样来判断是否发生了消除。这算是间接的可观测手段但需要使用者自己具备判断能力。KES优化器的逻辑优化全景把外连接消除放回它所在的大背景里看会更清楚KES优化器的设计思路。KES的逻辑优化阶段支持这么几项关键技术我在金仓官方文档里翻到的挨个说一下我的理解视图重写——你查的是视图优化器把视图引用替换成对基表的子查询再进一步优化。这一步是后续很多优化的前提。子查询优化——把NOT EXISTS、IN、ANY这类子链接上拉跟父查询拉平成多表连接物理优化阶段就能考虑更多连接方式。有些子查询改写后性能能提升好几个量级。条件化简——利用等式和不等式性质把WHERE、HAVING、ON里的复杂表达式化简。比如a 1 AND a 5可以化简成a 5。谓词下推——把过滤条件尽可能推到连接操作之前执行让参与连接的数据量变小对大表关联性能影响很大。外连接消除——我们今天讨论的主角把满足条件的外连接转成内连接。连接消除——注意这跟外连接消除不是一回事。这个是利用主外键约束直接把不需要的表从查询里拿掉。你看这些技术之间是有协同关系的。谓词下推把条件推下去之后可能让某个外连接满足消除条件外连接消除之后又可能让连接顺序的选择空间变大。逻辑优化不是一项一项孤立执行的它是一个不断尝试、层层递进的过程。理解了这个全景你就能明白为什么KES的外连接消除会看起来更激进——不是它单独在这项技术上比别的库激进而是它的整个逻辑优化体系是协同设计的谓词下推、条件化简等前置优化为外连接消除创造了更多触发机会。从设计哲学的角度看优化器行为聊到这儿我想跳出具体技术细节说说KES优化器的设计哲学。数据库优化器的设计本质上是在做一种权衡——性能 vs 可预测性。你让优化器越聪明、越激进性能上限越高但行为也越难预测出问题的时候排查难度也越大。你让优化器越保守、越听话行为越可预测但性能天花板也越低。KES选择的是偏激进的路子。它的逻辑优化体系相当完整从视图重写到谓词下推到外连接消除形成了一套协同的优化流水线。这种设计在原生环境下能提供很好的查询性能但在异构迁移场景下需要特别注意——因为源库的优化器可能没有那么激进同样的SQL在两边的行为可能不一样。这其实不是KES独有的挑战。任何两个优化策略不同的数据库之间做迁移都会碰到类似问题只是KES作为国产数据库主力选手迁移场景多所以被反复提及。我的建议是与其抱怨优化器太激进不如趁着迁移的机会把SQL好好梳理一遍。优化器做的事情在数学上是正确的——如果你的SQL在KES上因为外连接消除而丢数据了那说明你的SQL在源库上也有同样的问题只是源库没帮你消除所以你没发现。换个角度想KES的优化器其实是帮你暴露了一个潜在的编码隐患。金仓社区里这段时间也挺热闹的各种技术帖和实战分享不少。我看到一个智能运维工具开发大赛正在搞感兴趣的可以瞅瞅https://bbs.kingbase.com.cn/forumDetail?articleId2394013b19f3ef84a43edb994692b88e围绕KES运维工具开发如果能搞出个好用的执行计划可视化分析工具说不定能解决上面说的可观测性痛点。几个容易踩的具体场景光说原理不落地也不行我挑几个实际开发中容易碰到的场景说说。场景一聚合查询里的外连接消除-- 统计每个部门的员工数量包括没有员工的部门SELECTd.dept_name,COUNT(e.emp_id)asemp_countFROMdepartments dLEFTJOINemployees eONd.dept_ide.dept_idWHEREe.statusACTIVEGROUPBYd.dept_name;场景二多层外连接的级联消除SELECTa.id,b.info,c.detailFROMtable_a aLEFTJOINtable_b bONa.idb.a_idLEFTJOINtable_c cONb.idc.b_idWHEREb.categoryXANDc.level5;这里有两个LEFT JOINWHERE里同时有针对b和c的过滤条件。优化器会分别判断每个外连接是否满足消除条件。b的categoryX’会让a-b之间的外连接被消除c的level5会让b-c之间的外连接被消除。最终整条SQL退化成三表内连接。如果业务需要保留table_a的全部行两个条件都得挪到ON里SELECTa.id,b.info,c.detailFROMtable_a aLEFTJOINtable_b bONa.idb.a_idANDb.categoryXLEFTJOINtable_c cONb.idc.b_idANDc.level5;给迁移团队的建议最后总结几条实操建议吧。第一理解优化器的行为模式。不要把外连接消除当成Bug它是一个有充分理论基础的优化技术。你要做的不是阻止优化器工作而是确保你的SQL语义跟你的业务意图一致。第二建立SQL审查机制。所有涉及外连接的SQL在迁移后都应该过一遍EXPLAIN检查执行计划中Join算子是否带有Left/Right字样。这是发现外连接消除最直接的方法。-- 快速检查模板EXPLAINSELECT/* 你的LEFT JOIN查询 */;-- 看计划里是Hash Join还是Hash Left Join说到底优化器是数据库的大脑。理解它的思维方式才能写出跟它配合默契的SQL。外连接消除只是优化器众多能力中的一个但它特别能说明问题——它是那种技术上完全正确但实际使用中可能让人措手不及的典型代表。搞懂了它你对优化器的理解就上了一个台阶。