作者介绍

张树杰，《PostgreSQL技术内幕：查询优化深度探索》作者，目前在Pivotal公司任职Apache HAWQ数据库内核开发工程师，具有多年数据库内核开发经验。

 

 

编者按

本文以逻辑优化角度，对PostgreSQL数据库的查询优化器的实现进行详细分析。为了让大家通过通俗易懂的方式更好地理解消化其中的晦涩概念，作者别出心裁地撰写成趣味故事，虽然篇幅稍长，但细细品读定将收获匪浅。

 

 

查询优化器的基本原理

 

 

小明考上了北清大学的计算机研究生，今年学校开了数据库原理的课程，小明对查询优化的内容不是很理解，虽然已经使出了洪荒之力，仍觉得部分原理有些晦涩难懂，于是打算问一下自己的哥哥大明。

 

大明是一位资深的数据库内核开发老码农，对Greenplum/HAWQ数据库有多年的内核开发经验，眼镜片上的圈圈像年轮一样见证着大明十多年的从业经历。知道小明要来问问题，大明有点紧张，虽然自己做数据库内核好多年了，但是对优化器研究不甚深入，如果被小明这样的小菜鸟问倒就尴尬了，于是大明只好临时抱佛脚，拿出了好多年不看的《数据库系统实现》啃了起来。

 

小明提出的第一个问题是：“为什么数据库要进行查询优化？”

 

大明推了推鼻梁上的眼镜，慢条斯理地说：“不止是数据库要进行优化，基本上所有的编程语言在编译的时候都会优化，比如你在编译C语言的时候，可以通过编译选项-o来指定进行哪个级别的优化，但是查询数据库的查询优化和C语言的优化还有些区别。”

 

“有哪些区别呢？”大明停顿了一下，凝视着小明，仿佛期望小明能给出答案，或是给小明腾挪出足够思考的空间。三五秒之后大明又自问自答道：“C语言是过程化语言，你已经指定好了需要执行的每一个步骤，但是SQL是描述性语言，它只指定了WHAT，而没有指定HOW，这样它的优化空间就大了，你说是不是？”

 

小明点了点头说：“对，也就是说条条大路通罗马，它比过程语言的选择更多，是不是这样？”大明笑道：“孺子可教也。虽然我们知道它的优化空间大，但具体如何优化呢？”

 

说着大明将身子向沙发一靠，翘上二郎腿继续说：“通常来说分成两个层面，一个是基于规则的优化，另一个是基于代价的优化。基于规则的优化也可以叫逻辑优化或者规则优化，基于代价的优化也可以叫物理优化或者代价优化。”

 

“为什么要进行这样的区分呢？优化就优化嘛，何必还分什么规则和代价呢？”，小明问道。

 

“分层不分层不是重点，有些优化器层次分得清楚点，有些优化器层次分得就不那么清楚，都只是优化手段而已。”大明感到有点心虚，再这么问下去恐怕要被问住，于是试图引开话题：“我们继续说说SQL语言吧，我们说它是一种介于关系演算和关系代数之间的语言，关系演算和关系代数你看过吧？”

 

小明想了想，好像上课时老师说过关系代数，但没有说关系演算，于是说：“接触过一点，但不是特别明白。”大明得意地说：“关系演算是纯描述性的语言，而关系代数呢，则包含了一些基本的关系操作，SQL主要借鉴的是关系演算，也包含了关系代数的一部分特点。”

 

大明看小明有点懵，顿了一下继续说道：“上课的时候老师有没有说过关系代数的基本操作？”小明想了一下说：“好像说了，有投影、选择、连接、交集、差集这几个。”大明点点头说：“对的，还可以有一个叫重命名的，一共6个基本操作，另外结合实际应用在这些基本操作之上又扩展出了外连接、半连接、聚集操作、分组操作等等。”

 

大明继续说道：“SQL语句虽然是描述性的，但是我们可以把它转化成一个关系代数表达式，而关系代数中呢，又有一些等价的规则，这样我们就能结合这些等价规则对关系代数表达式进行等价的转换。”

 

“进行等价转换的目的是找到性能更好的代数表达式吧？”小明问。

 

“对，就是这样。”大明投去赞许的目光。

 

“那么如何确定等价变换之后的表达式就能变得比之前性能更好呢？或者说为什么要进行这样的等价变换，而不是使用原来的表达式呢？”

 

大明愣了一下，仿佛没有想到小明会提出这样的问题，但是基于自己多年的忽悠经验，他定了定神，回答道：“这既有经验的成分，也有量化的考虑。例如将选择操作下推，就能优先过滤数据，那么表达式的上层计算结点就能降低计算量，因此很容易可以知道是能降低代价的。再例如我们通常会对相关的子查询进行提升，这是因为如果不提升这种子查询，那么它执行的时候就会产生一个嵌套循环，这种嵌套循环的执行代价是O(N^2)，这种复杂度已经是最坏的情况了，提升上来至少不会比它差，因此提升上来是有价值的。”大明心里对自己的临危不乱暗暗点了个赞。

 

大明看小明没有提问，继续说道：“这些基于关系代数等价规则做等价变换的优化，就是基于规则的优化，当然数据库本身也会结合实际的经验，产生一些优化规则，比如外连接消除，因为外连接优化起来不太方便，如果能把它消除掉，我们就有了更大的优化空间，这些统统都是基于规则的优化。同时这些都是建立在逻辑操作符上的优化，这也是为什么基于规则的优化也叫做逻辑优化。”

 

小明想了想，自己好像对逻辑操作符不太理解，连忙问：“逻辑操作符是啥？既然有物理优化，难道还有物理操作符吗？”

 

大明伸了个懒腰继续说：“比如说吧，你在SQL语句里写上了两个表要做一个左外连接，那么数据库怎么来做这个左外连接呢？”

 

小明一头雾水地摇摇头，向大明投出了期待的眼神。

 

大明继续说道：“数据库说我也不知道啊，你说的左外连接意思我懂，但我也不知道怎么实现啊？你需要告诉我实现方法啊，因此优化器还承担了一个任务，就是告诉执行器，怎么来实现一个左外连接。”

 

“数据库有哪些方法来实现一个左外连接呢？它可以用嵌套循环连接、哈希连接、归并连接等等，注意了，重要的事情说三遍，你看内连接、外连接是连接操作，嵌套循环连接、归并连接等也叫连接，但内连接、外连接这些就是逻辑操作符，而嵌套循环连接、归并连接这些就是物理操作符。所以你说对了，物理优化就是建立在物理操作符上的优化。”

 

大明：“从北京去上海，你说你怎么去？”

小明：“坐高铁啊，又快又方便。”

大明：“坐高铁先去广州再倒车到上海行不？”

小明：“有点扎心了，这不是吃饱了撑的吗？”

大明：“为什么？”

小明：“很明显，我有直达的高铁，既省时间又省钱，先去广州再倒车？我脑子瓦特了？！”

 

大明笑了笑说：“不知不觉之间，你的大脑就建立了一个代价模型，那就是性价比，优化器作为数据库的大脑，也需要建立代价模型，对物理操作符计算代价，然后筛选出最优的物理操作符来，因此基于代价的优化是建立在物理操作符上的优化，所以也叫物理优化。”

 

小明似乎懂了：“公司派我去上海出差就是一个逻辑操作符，它和我们写一个SQL语句要求数据库对两个表做左外连接类似，而去上海的实际路径有很多种，这些就像是物理操作符，我们对这些实际的物理路径计算代价之后，就可以选出来最好的路径了。”

 

大明掏出手机，分别打开了两个不同的地图APP，输入了北京到上海的信息，然后拿给小明看。小明发现两个APP给出的最优路径是不一样的。小明若有所思地说：“看来代价模型很重要，代价模型是不是准确决定了最优路径选择得是否准确？”

 

大明一拍大腿，笑着说：“太对了，所以我作为一个数据库内核的资深开发人员，需要不断地调整优化器的代价模型，以期望获得一个相对稳定的代价模型，不过仍然是任重道远啊。”

 

 

关于语法树

 

 

听了大明对查询优化器基本原理的讲解，小明在学校的数据库原理课堂上顺风顺水，每天吃饭睡觉打豆豆，日子过得非常悠哉。不过眼看就到了数据库原理实践课，老师给出的题目是分析一个数据库的某一模块的实现，小明千挑万选，终于选定了要分析PostgreSQL数据库的查询优化器的实现，因为据说PostgreSQL数据库的查询优化器层（相）次（当）清（复）楚（杂），具有教科书级的示范作用。

 

可是当小明下载了PostgreSQL数据库的源代码，顿时就懵圈了，虽然平时理论说得天花乱坠，但到了实践的时候却发现，理论和实际对应不上。小明深深陷入到代码的细节里不可自拔，查阅了好多资料，结果是读破书万卷，下笔如有锤，一点进展都没有。于是小明又想到了与PostgreSQL有着不解之缘的大明，想必他一定能站得更高，看得更远，于是小明蹬着自己的宝马向大明驶去。

 

大明看着大汗淋漓找上门的小明，意味深长地说：“PostgreSQL的查询优化器功能比较多，恐怕一次说不完，我们分成几次来说清楚吧。”

 

小明说：“的确是的，我在看查询优化器代码的时候觉得无从下手，虽然一些理论学过了，但不知道代码和理论如何对应，而且还有一些优化规则好像我们讲数据库原理的时候没有涉及到，毕竟理论和实践之间还是有一些差距。”

 

大明打开电脑，调出了PostgreSQL的代码说：“我们先来看一下PostgreSQL一个查询执行的基本流程。”，然后调出了一张图。

 

 

 

“这张图是我自己画的，这种图已经成了优化器培训开篇的必备图了，我们有必要借助这张图来看一下PostgreSQL源码的大体结构，了解查询优化器所处的位置。”

 

大明一边指点着电脑屏幕，一边继续说：“我们要执行一条SQL语句，首先会进行词法分析，也就是说把SQL语句做一个分割，分成很多小段段……”小明连忙说：“我们在学编译原理的时候老师说了，分成的小段段可以是关键字、标识符、常量、运算符和边界符，是不是分词之后就会给这些小段段赋予上这些语义？”

 

“对的！看来你对编译原理的第一章很熟悉嘛。”大明笑着说。

 

“当然，我最擅长写Hello World。”

 

“好吧，Let’s继续，PostgreSQL的分词是在scan.l文件中完成的，它可能分得更细致一些，比如常量它就分成了SCONST、FCONST、ICONST等等，不过基本的原理是一样的。进行分词并且给每个单词以语义之后，就可以去匹配gram.y里的语法规则了，在gram.y文件里定义了所有的SQL语言的语法规则，我们的查询经过匹配之后，最终形成了一颗语法树。”

 

“语法树？我还听过什么查询树、计划树，这些树要怎么区分呢？”

 

“一个查询语句在不同的阶段，生成的树是不同的，这些树的顺序应该是先生成语法树，然后得到查询树，最终得到计划树，计划树就是我们说的执行计划。”

 

“那为什么要做这些转换呢？”小明不解地问。

 

“我们通过词法分析、语法分析获得了语法树，但这时的语法树还和SQL语句有很紧密的关系，比如我们在语法树中保存的还是一个表的名字，一个列的名字，但实际上在PostgreSQL数据库数据库中，有很多系统表，比如PG_CLASS用来将表保存成数据库的内部结构，当我们创建一个表的时候，会在PG_CLASS、PG_ATTRIBUTE等等系统表里增加新的元数据，我们要用这些元数据的信息取代语法树中表的名字、列的名字等等。”

 

小明想了想，说：“这个取代的过程就是语义分析？这样就把语法树转换成了查询树，而查询树是使用元数据来描述的，所以我们在数据库内核中使用它就更方便了？”

 

“是的。”大明肯定地说。“不过语义分析还做了一个工作，那就是检查工作，在语法树是通过分析SQL语句获得的，它还不知道一个表是不是存在，一个列是不是存在，这个转换的过程，也是一个检查的过程。”大明停顿了一下，似乎是做了一下思考，然后拿出一张纸，在上边画了起来。

 

 

 

 

“这是SQL语句SELECT st.sname, c.cname, sc.degree FROM STUDENT st , COURSE c INNER JOIN SCORE sc ON c.cno = sc.cno WHERE st.sno = sc.sno对应的简版查询树，看着复杂吗？”大明边画边问。小明心中翻腾出千万只泥马，他似乎感觉到自己选择查询优化作为数据库原理课的实践作业是一个错误的决定，现在自己已经受到了冲动的惩罚，这个图里的大部分内容他都不知道是什么东西。

 

看着小明迷离的眼神，大明有点发慌，说：“我们现在还不用深入到代码层面，你可以忽略这张图，现在可以把查询树认为是一个关系代数表达式。”

 

小明定了定神，问道：“关系代数表达式？上次我问你查询优化原理的时候你是不是说基于规则的优化就是使用关系代数的等价规则对关系代数表达式进行等价的变换，所以查询优化器的工作就是用这个查询树做等价变换？”

 

“恭喜你，答对了。”大明暗暗赞许小明的理解能力和记忆力，继续说：“查询树就是查询优化器的输入，经过逻辑优化和物理优化，最终产生一颗最优的计划树，而我们要做的就是看看查询优化器是如何产生这棵最优的计划树的。”

 

 

关于子连接

 

 

午饭过后，大明惬意地抽起了中华烟，小明看着他好奇地问：“咱爷爷抽的是在农村种的烟叶，自给自足还省钱，你也干脆回农村种烟叶吧，你这中华烟和农村的自己卷的烟叶，能有什么区别？”

 

大明看电视剧正看得起劲，心不在焉地说：“自己种的烟叶直接用报纸卷了抽，没有过滤嘴，会吸入有害颗粒物，而且烟叶的味道也不如现在改进的香烟。”说到这里大明好像想到了什么，继续说：“这就像是查询优化器的逻辑优化，查询树输入之后，需要进行持续的改进。无论是自己用报纸卷的烟，还是在超市买的成品烟，都是香烟，但是通过改进之后，香烟的毒害作用更低、香型更丰富了，逻辑优化也是这个道理，通过改进查询树，能够得到一个更‘好’的查询树。”

 

“哦，那逻辑优化是如何在已有的查询树上增加香型的呢？”

 

大明继续说：“我总结，PostgreSQL在逻辑优化阶段有这么几个重要的优化：子查询&子连接提升、表达式预处理、外连接消除、谓词下推、连接顺序交换、等价类推理。”大明又抽了一口烟，接着说：“从代码逻辑上来看，我们还可以把子查询&子连接提升、表达式预处理、外连接消除叫做逻辑重写优化，因为他们主要是对查询树进行改造，而后面的谓词下推、连接顺序交换、等价类推理则可以称之为逻辑分解优化，他们已经把查询树蹂躏得不成样子了，已经到了看香烟不是香烟的地步了。”

 

“可是我们的数据库原理课上并没有说有逻辑重写优化和逻辑分解优化啊。”

 

“嗯，是的，这是我根据PostgreSQL源代码的特征自己总结的，不过它能比较形象地将现有的逻辑优化区分开来，这样就能更好地对逻辑优化进行提炼、总结、分析。”大明想了一下觉得如果把所有的逻辑优化规则都说完有点多，于是对小明说：“我们就从中挑选一两个详细说明吧，先从子查询&子连接的提升开始说起。”

 

“那……子查询和子连接有什么区别呢？我们在数据库原理课里只讲了子查询，没有子连接的概念，这该怎么解释呢？”小明不解地问。

 

大明拿出了《数据库系统实现》这本书并翻开了对应章节，说道：“通常数据库原理书籍中说的子查询，指的是PostgreSQL中的子连接。你看，书里说的是从条件中去除子查询，但是PostgreSQL把这种情况归类为子连接。”

 

“那在PostgreSQL是如何区分子查询和子连接的呢？”大明自问自答道：“在实际应用中可以通过子句所处的位置来区分子连接和子查询，出现在FROM关键字后的子句是子查询语句，出现在WHERE/ON等约束条件中或投影中的子句是子连接语句。”说着大明快速地在电脑上打开了记事本，敲入了几个SQL语句：

 

1. SELECT * FROM STUDENT, (SELECT * FROM SCORE) as sc;

2. SELECT (SELECT AVG(degree) FROM SCORE), sname FROM STUDENT;

3. SELECT * FROM STUDENT WHERE EXISTS (SELECT A FROM SCORE WHERE SCORE.sno = STUDENT.sno);

 

“这些SQL语句中哪个是子查询？哪个是子连接？”

 

小明看了一下说：“1是子查询，2和3是子连接，语句1里的子句出现在FROM后面，它是以‘表’的形式存在的，是子查询，2和3的子句出现在投影和约束条件中，是以表达式的形式存在的，是子连接。”小明不但答对了问题，而且还对问题的答案做了扩展，大明调侃道：“腰间盘同学，坐下，你太突出了。”

 

大明继续说道“从大的方向上分类，子查询还可以分为相关子连接和非相关子连接，相关子连接是指在子查询语句中引用了外层表的列属性，这就导致外层表每获得一个元组，子查询就需要重新执行一次；而非相关子查询是指在子查询语句是独立的，和外层的表没有直接的关联，子查询可以单独执行一次，外层表可以重复利用子查询的执行结果。”

 

“那么一定是相关子连接才会提升了，因为我记得你在说逻辑优化原理的时候说过，相关子连接会产生‘嵌套循环’，这种情况的复杂度是O(N^2)，提升上来的复杂度肯定不会比O(N^2)差，所以提升是有价值的。”小明说道。

 

听到这些，大明顿时碉堡了，道理虽然是这个道理，但是PostgreSQL偏偏不走寻常路，和自己之前说过的有些许差异，大明羞涩地说：“虽然话是这样说，但PostgreSQL有点不同，PostgreSQL提升了两种类型的子连接，一种是ANY类型的子连接，一种是EXISTS类型的子连接，对于ANY类型的子连接，只提升非相关子连接，而对于EXISTS类型的子连接，则只提升相关子连接。”

 

小明顿时想起了自己曾和同学说过相关子连接理论，当时把宿舍同学忽悠得五迷三道的，今天大明又说这可能是错的，心里不太爽利，于是怒道：“那我和同学吹过的牛岂不是要成为笑柄？感觉受到了一万点伤害……”

 

“小明同学别急”，大明安抚道：“虽然PostgreSQL对于ANY类型只提升非相关的子连接，但它仍然是只提升产生嵌套循环的那种子连接，你看看这个例子。”说着在电脑上又敲了一个SQL语句：

 

SELECT * FROM STUDENT WHERE sno > ANY (SELECT sno from STUDENT);

 

“这是一个ANY类型的非相关子连接，但请注意，在>前面的sno实际上产生了一个天然的相关性，这个天然的相关性就会产生嵌套循环，因此是需要提升的。我们再来看另一个语句。”大明把>前面的sno换成了一个常量：

 

SELECT * FROM STUDENT WHERE 10 > ANY (SELECT sno from STUDENT);

 

“这个SQL语句中的子连接就不会提升了，因为我们把sno换成了常量，父子之间的相关性被打破了，明白了吗？”

 

小明点点头，心里想：子连接是否提升取决于相关性，而这个相关性不只是体现在子句里，也体现在表达式里，也就是说只要能产生嵌套循环，那就有提升的必要啊，但是……小明灵机一动，问道：“那ANY类型的相关子连接也会产生嵌套循环啊，为什么不提升呢？”

 

大明说：“这可能有点历史原因了，PostgreSQL提升ANY类型的子连接的方式和EXISTS类型的子连接的方式不同，他提升EXISTS类型的子连接的时候，是直接把子句中的表提上来做，形成一个SemiJoin，可是提升ANY类型的子连接时，是把整个子句提上来，和父语句中的表做SemiJoin，这时候这个子句就变成了一个子查询，你看这个例子。”说着在电脑上敲了三个语句：

 

1. SELECT * FROM TEST_A WHERE a > ANY (SELECT a FROM TEST_B WHERE TEST_A.b = TEST_B.b)；

2. SELECT * FROM TEST_A, (SELECT a FROM TEST_B WHERE TEST_A.b = TEST_B.b) b WHERE TEST_A.a > b.a;

3. SELECT * FROM TEST_A, LATERAL (SELECT a FROM TEST_B WHERE TEST_A.b = TEST_B.b) b WHERE TEST_A.a > b.a;

 

“如果按照目前ANY类型子连接先提升成子查询的方式，第1个语句提升之后会变成等价于第2个语句，而第2个语句本身是无法执行的，在比较新版本的PostgreSQL上支持了LATERAL之后，只要在第2个语句上加上LATERAL，也就是变成第3个语句就能执行了。”大明在屏幕上比划着说。

 

小明问道：“那岂不是说，有了LATERAL之后，ANY类型的相关子连接也能提升了？”

 

大明说：“只能说有一部分ANY类型的相关子连接能够提升了，比如我们上面的例1本质上就是能提升的，而且一些商业数据库确实也对这种语句做了提升，但是PostgreSQL目前还没有处理这种情况。”

 

小明打了个哈欠说：“实在是太累了，让我们休息一下吧，查询优化器太复杂了。”

 

大明笑着说：“坚持不懈就能成功，子连接提升之后，还有子查询提升、表达式预处理、外连接消除，不过，在这之前还是让我们先吃个鸡再说吧。”

 

 

关于选择下推与等价类

 

 

小明感叹道：“我觉得要深度了解查询优化没希望了……”大明看出小明对查询优化产生了畏难情绪，因为小明本以为通过大明的讲解能够快速理解查询优化的本质，但他听过之后发现，查询优化器远不是几次讲解就能解决的，大明目前给他讲解的还只是在应用层面的讲解，还没有深入到分析源码阶段，仅仅如此，对小明来说理解上就已经有些困难了，看来要想深度了解查询优化器，还需要下更大功夫才行。

 

大明说：“啥叫成功？成功就是在你坚持不下去的时候再坚持一下。来吧，Let’s继续。”说着打开了电脑，“我们继续说点啥呢？刚刚说到了子连接，接下来我们简单说说选择下推和等价类吧。”

 

小明想了想说：“选择下推和等价类是逻辑分解优化中的内容了，可是逻辑重写优化里还有子查询提升、表达式预处理、外连接消除这些大块头你还没有给我讲解过呀。”

 

大明说：“这些先留给你自己去理解，如果理解不了再来找我吧。逻辑优化的规则实际上还是比较多的，但是可以逐个击破的，也就是他们之间通常而言并没有多大的关联，我们不打算在这上面纠缠太多时间，我相信以你自己的能力把他们搞定是没有问题的。”

 

“我记得你说过，选择下推是为了尽早地过滤数据，这样就能在上层结点降低计算量，是吧？”

 

“是的。”大明点了点头，“还是通过一个关系代数的示例来说明一下它吧，顺便我们把等价类推理也说一说。比如说我们想要获得编号为5的老师承担的所有课程名字，我们可以给出它的关系代数表达式。”说着在电脑上敲了一个关系代数表达式：

 

Πcname (σTEACHER.tno=5∧TEACHER.tno=COURSE.tno (TEACHER×COURSE))

 

“小明，你看这个关系代数表达式怎么下推选择操作？”

 

小明看着关系代数表达式思考了一会，说：“我看这个TEACHER.tno = 5比较可疑，你看这个关系代数表达式，先做了TEACHER×COURSE，也就是先做了卡氏积，我要是把TEACHER.tno = 5放到TEACHER上先把一些数据过滤掉，岂不是……完美！”说着小明也在电脑上敲出了把TEACHER.tno = 5下推之后的关系代数表达式。

 

Πcname (σTEACHER.tno=5∧TEACHER.tno=COURSE.tno (TEACHER×COURSE))

 

大明说：“对的，你这样下推下来的确能降低计算量，这应用的是关系代数表达式中的分配率σF(A × B) == σF1(A) × σF2(B)，那你看看，既然下推这么好，是不是投影也能下推？”小明看了一下，关系代数表达式中值需要对cname进行投影，顿时想到了，COURSE表虽然有很多个列，但是我们只需要使用cname就够了嘛，于是小明在电脑上敲了投影下推的关系代数表达式。

 

Πcname (σTEACHER.tno=COURSE.tno (σTEACHER.tno=5(TEACHER)×COURSE))

 

大明拍了小明的头一下说：“笨蛋，你这样下推投影，TEACHER.tno=COURSE.tno还有办法做吗？”小明顿时领悟了，如果只在COURSE上对cname做投影时不行的，上层结点所有的表达式都需要考虑到，于是修改了表达式：

 

Πcname (σTEACHER.tno=COURSE.tno (σTEACHER.tno=5(TEACHER)× Πcname, tno(COURSE)))

 

“这还差不多。”大明笑着说：“这是使用的投影的串接率，也是一个非常重要的关系代数等价规则，目前我们对这个表达式的优化主要是使用了选择下推和投影下推，如果用SQL语句来表示，就像这样。”大明在电脑的记事本上快速打出了两个SQL语句：

 

SELECT sname FROM TEACHER t, COURSE c WHERE t.tno = 5 AND t.tno = c.tno;

SELECT sname FROM (SELECT * FROM TEACHER WHERE tno = 5) tt, (SELECT cname, tno FROM COURSE) cc WHERE tt.tno = cc.tno;

 

“你看这两个语句，就是谓词下推和投影下推前后的对照语句。在做卡氏积之前，先做了过滤，这样笛卡尔积的计算量会变小。”

 

小明仔细观察着代数表达式和这两个SQL语句，发现了一个问题，就是关系代数表达式中有TEACHER.tno = 5和TEACHER.tno = COURSE.tno这样两个约束条件，这是不是意味着COURSE.tno也应该等于5呢？小明试着在电脑上写了一个SQL语句：

 

SELECT sname FROM (SELECT * FROM TEACHER WHERE tno = 5) tt, (SELECT cname, tno FROM COURSE WHERE tno=5) cc WHERE tt.tno = cc.tno;

 

然后小明说：“你看，由于有TEACHER.tno = 5和TEACHER.tno = COURSE.tno这样的两个约束条件，我们是不是可以推理出一个新的COURSE.tno = 5的新约束条件来呢，这样还可以把这个条件下推到COURSE表上，也能降低笛卡尔积的计算量。”

 

大明说：“是的，这就是等价推理，PostgreSQL在查询优化的过程中，会将约束条件中等价的部分都记录到等价类中，这样就能根据等价类生成新的约束条件出来，比如示例的语句中就会产生一个等价类{TEACHER.tno, COURSE.tno, 5}，这是一个含有常量的等价类，是查询优化器比较喜欢的等价类，这种等价类可以得到列属性和常量组合成的约束条件，通常都是能下推的。”

 

小明心里很高兴，自己通过仔细观察，得到了等价类的优化，感觉有了学习的动力，心里美滋滋的，然后问大明：“那上面的SQL语句还有什么可优化的吗？”

 

大明观察了一下这个语句说：“我们已经在TEACHER表上进行了TEACHER.tno = 5的过滤，在COURSE表上也做了COURSE.tno = 5的过滤，这就说明在做笛卡尔积时，实际上已确定了TEACHER.tno = COURSE.tno = 5，也就是说TEACHER.tno = COURSE.tno这个约束条件已经隐含成立了，也就没什么用了，我们可以把它去掉，最终形成一个这样的SQL语句。”大明敲下了最终的语句：

 

SELECT sname FROM (SELECT * FROM TEACHER WHERE tno = 5) tt, (SELECT cname, tno FROM COURSE WHERE tno=5) cc；

 

同时也敲出了这个语句对应的关系代数表达式：

 

Πcname (σTEACHER.tno=5(TEACHER)× Πcname, tno(σCOURSE.tno=5(COURSE)))

 

大明说：“经过选择下推、投影下推和等价类推理，我们对这个SQL语句或者说关系代数表达式进行了优化，最终降低了计算量。”

 

 

 

小明感觉对谓词下推已经理解了：“看上去也不复杂嘛，我发现了可以下推的选择我就下推，完全没有问题啊。”大明笑着说：“甚矣，我从未见过如此厚颜无耻之人，我们现在看的这个例子，只不过是最简单的一种情况啊，你就这样大言不惭，你的人生字典里还有羞耻二字吗？”

 

小明愤愤地说：“我的人生没有字典……”

 

大明问道：“我们这个例子有一个问题，它是内连接，因此我们可以肆意妄为地将选择下推下来，可以没羞没臊地做等价类推理，但如果是外连接，还能这么做吗？”

 

小明顿时陷入了苦苦的沉思。

 

 

关于表达式的严格

 

 

小明被大明将了一军，心里开始合计起来，假如是外连接，可能会对某一方补NULL值，这样的话TEACHER.tno = COURSE.tno的约束条件就无法构成等价类了，然后小明在电脑上默默敲了一个SQL语句：

 

SELECT sname FROM TEACHER t LEFT JOIN COURSE c ON t.tno = 5 AND t.tno = c.tno;

 

然后小明发现不但等价类可能产生不了，选择下推也无法进行了，于是说：“这个语句中的TEACHER.tno = 5不能下推了，因为左连接的语义是外表的所有数据都要输出出来，如果把TEACHER.tno = 5下推到TEACHER表上，就会在做左连接之前先对TEACHER表做过滤，导致查询结果的不等价，而且由于补NULL值，等价类也生成不了了。”

 

大明说：“对的，你理解得很快，由于外连接补NULL值的关系，确实导致无法做谓词下推，不过你可以看一下下面这个语句，看看有什么区别。”然后大明在电脑里输入了另一个类似的SQL语句：

 

SELECT sname FROM TEACHER t LEFT JOIN COURSE c ON TRUE WHERE t.tno = 5 AND t.tno = c.tno;

 

小明仔细观察上面的例句和当前这个例句，发现约束条件一个处在ON后面，另一个处在WHERE后面，小明好像还不是很理解它们的含义，于是向大明投去了询问的目光。大明说：“我们粗略地分一下，ON后面的约束条件是连接条件，WHERE后面的约束条件是过滤条件，连接条件和过滤条件是不同的。”

 

小明好像悟到了什么，抢着说：“我知道了，一个是连接中的，一个是连接后的，可以这么理解吧？你看，连接条件会参与到连接操作的过程中，满足连接条件的会显示出来，不满足连接条件的，根据连接的类型还会决定是否补NULL值，而过滤条件是在连接操作之后对连接的结果进行过滤……”小明又对大明投去了期待的眼神，大明笑着说：“对，可以这么理解。”

 

小明赶紧说：“你先别讲，让我看看这个带有过滤条件的SQL语句是不是能下推。”，然后对着这个SQL语句仔细观察起来，口中念念有词，但观察了半天毫无收获。

 

大明见状继续说：“实际上这里还有一个严格的概念，什么叫严格呢？一个表达式，如果它的输入是NULL并且输出也是NULL，那我们就说这个表达式是严格的，另外我们可以扩展一下严格的定义，从而定义出一个叫做‘宽泛的严格’的概念，就是说如果一个表达式它的输入是NULL，它的输出是NULL或者false，那么我们就说它符合宽泛的严格。”

 

“那么严格有什么用呢？”

 

“如果对一个元组应用约束条件，如果约束条件求值返回的是NULL值或者FALSE，实际上代表的是这一条元组不输出，明白吗？”

 

“那就是说我们补的NULL值如果遇到这种过滤条件，就不会输出出来喽。”小明停了一下，突然想到了些什么，继续问道，“那这种外连接还补NULL值干嘛，岂不是没有什么用了？”

 

“对头，这就是外连接消除的基本原理，遇上这种严格的约束条件，外连接补的NULL值没有什么用，那也就转变成内连接就好了。问题来了，如果变成了内连接，我们又能肆意妄为地选择下推、没羞没臊地做等价推理了，惊不惊喜，意不意外？”

 

“哈哈，那你能给我一个不严格的例子不？让我见识一下不严格的表达式。”

 

“比如说IS NOT NULL，输入是NULL值，输出竟然是TRUE，还有COALESCE函数，输入是NULL值，输出是啥随你定。”

 

小明说：“看来表达式是否严格是很重要的一个概念，通过这个概念我们能做外连接消除，外连接消除又能够导致选择能够下推……这我就明白了为什么要做外连接消除了。”

 

“嗯，外连接消除不只是将外连接转换成内连接，其实还有一种情况，它也和我们要说的表达式的条件是否严格有关，那就是可以将外连接转换成Anti Join，我们来看这样一个例子。”

 

SELECT * FROM STUDENT LEFT JOIN SCORE ON STUDENT.sno = SCORE.sno WHERE SCORE.sno IS NULL;

 

“由于STUDENT.sno = SCORE.sno是严格的，而且是连接条件，这样在连接的过程中他会将在STUDENT表和SCORE表中原有的NULL值去除掉，反而由于SOCRE.sno IS NULL是过滤条件，它起到了过滤作用，会将外连接补充NULL值的数据全部保留下来，这个语句的执行结果实际上就相当于做了一个Anti Join，因此这个语句可以转换成为Anti Join，它转换的结果相当于下面这个语句。”大明在电脑上敲出了等价语句。

 

SELECT * FROM STUDENT ANTI JOIN SCORE ON STUDENT.sno = SCORE.sno

 

“不过需要注意，SQL语法中是没有ANTI JOIN的，这只是一个等价的语句，但是它无法直接执行。另外在外连接消除的阶段还有一个‘很重大’的举措，就是把左外连接全部转换成了右外连接，这样就可以在后续的代码中少处理一种情况，简化了后面的代码逻辑。”

 

“严格果然是太有用了，可是我怎么知道一个表达式是不是严格呢？”

 

“对于函数而言，在PG_PROC系统表中的proisstrict列属性代表了当前函数是否严格，如果是操作符表达式，在PostgreSQL中操作符实际都转成了对应的函数，因此也可以用proisstrict来表示是否严格，而对基于IS [NOT] NULL产生的NullTest表达式需要单独处理，其中IS NOT NULL是严格的，IS NULL是不严格的，大体上我们可以分成这么几类。”

 

注：本故事后续请看DBAplus社群同天发布的第二篇文章《PostgreSQL查询优化器详解（物理优化篇）》。