[2016 DTCC 数据库内核专场] 揭开SQL优化的盖头来 解说词

2016 揭开SQL优化的盖头来 解说词

------ 数据库查询优化器实现技术解密

（2016年5月12日）

（PPT第1页）

优化器被比喻为数据库引擎的发动机，这个发动机非常精密非常复杂，今天，我们从原理出发，来揭开优化器的盖头，来看看这个发动机的构造，看看这个发动机多么精密多么复杂?

（PPT第2页）

今天的分享，一共包括4个子话题：

第1，       谁知我心，来了解谁的心呢？这一节，从整体结构上，谈谈优化器的构造，帮助大家了解优化器的核心。

第2，       SQL语句的分析阶段，是一个从拆卸到重新组装的过程。这一节，谈谈SQL语句怎么被拆分为计算机可以理解的形式，又怎么被重新组装为优化器可以优化的形式。

第3，       优化计算，点点滴滴从小事做起。这一节，从逻辑优化 和 物理优化 这2个角度，来揭示优化器怎么从细微、点滴之处来实现优化。

第4，       谁与争锋，主流数据库对比。这一节，我们将对Oracle、Pg、MySQL、Informix数据库的优化器进行对比，来深入地了解这些数据库优化器的优缺点。

 

（PPT第3页）

首先，来看第一个话题，“谁知我心，查询优化器的构造”。

优化器是一个大的话题，在短短的四十分钟，要把优化器的盖头揭开，富有挑战性。为了能够讲明白，我们以MySQL和PG为例，先从整体上认识一下优化器，用优化器的架构图，来直观地感受一下优化器。

这是MySQL优化器的流程图，。。。。。。，也就是说，一条查询语句，从进入到数据库引擎中，到得到结果返回给用户，一共经历三个阶段，分别是分析阶段、优化阶段、执行阶段。

（PPT第4页）

这三个阶段都很复杂，包含了很多技术。比如，MySQL的优化阶段，进行了8层嵌套，使用了子查询上拉、贪婪算法等多种优化技术。

（PPT第5页）

看过了MySQL，我们已经能初步感受到优化器的复杂性。PG的优化器，是不是也非常的复杂？这是PG优化器的流程图，。。。。。。

（PPT第6页）

大家来看这幅图，我们主要看它的嵌套层次，然后感受它的复杂性。大家看，PG的优化器，嵌套了11层，使用多种算法对不同的子句进行了不同的优化。比如说使用动态规划算法和遗传算法求解多表连接。

从上面这几张图我们看出，Pg和MySQL的优化器，都非常复杂。

其实，这还不算是最复杂的优化器，商业数据库的优化器如Informix，相对PG和MySQL的优化器，更为复杂，做得更为精致。期望将来有机会，能够和大家深入地聊一聊Informix优化器的先进技术。

 

（PPT第7页）

把握优化器的构造，要注意2点：

第1，从广义的角度把握优化器。有人认为，优化器只包括逻辑优化和物理优化；甚至，还有人认为优化器仅指基于代价模型的物理优化。从狭义的角度看，优化器只包括逻辑优化和物理优化；但是我们要更好地理解优化器，就要从更广泛的角度认识优化器，不仅要看优化阶段，还需要结合前期的分析阶段以及后期的执行阶段，交互理解，互相印证。这是第一点，认识广义的优化器。

第2 优化器的技术本质，是实现了SQL语义的编译器。首先，优化器就是一个编译器，要用编译技术理解优化器。其次，与其他语言的编译器相比，SQL的优化器，是带有SQL语义的。而SQL的语义基于关系代数，如等值连接、自然连接、外连接、半连接等，这些都有特定的语义，每一种连接方式表达的连接操作是不一样的。

所以优化器技术，基于编译器技术，但需要从SQL语义的角度，从关系代数出发，处理SQL语句内各种子操作的语义、以及他们之间的关系。

 

（PPT第8页）

第一节，我们了解了优化器的构造。接下来，我们来看分析阶段是怎么工作的？

从拆卸到重新组装----就是分析阶段所进行的工作。这个阶段，就是把SQL语句分解，然后，识别出各个子句的含义，最后把各个子句再重新整合在一起，生成一个可以被计算机遍历的树，这样的树，叫做语法树。

（PPT第9页）

下面用一个例子，来看在分析的过程中，优化器是怎么拆卸SQL语句，然后又怎么把一堆零件组装为一颗语法树。

大家看，这是一条SQL语句，下面这幅图，是一颗语法树，优化器分析阶段的工作，就是要把上面这条SQL语句，变为下面这个语法树的样子。

（PPT第10页）

首先，数据库接收到这样的SQL语句，然后，按照文法文件，把SQL语句分解为token，每个token就是一个零部件，这就是词法分析；

在词法分析过程中，还要把SQL语句中的比如目标列、WHERE子句等分解出来，分解的依据，还是文法文件。比如，目标列对应目标对象，目标对象由表达式组成，表达式再逐层分解，直到列对象。

而右侧的这些内容，是文法文件的一部分。文法文件的内容，本质上，就是一个确定有限状态自动机。确定有限状态自动机属于编译原理。

（PPT第11页）

一条SQL语句会被分解为一堆用token表示的零件，但这些零件不是我们想要的结果，我们其实想要的是这样一个东西（ppt展示后）：一辆性能优异的跑车，所以我们需要把零件组装起来，组装的过程，就是语法分析和语义分析的过程。

首先，我们要做语法分析。。。

（PPT第12页）

让我们来总结一下分析阶段的全过程。用户输入的是一条SQL语句，第一步，通过词法分析，把SQL语句分解为一堆token零件；第二，经过语法分析，我们把一堆token零件组装成一颗语法树。第三，经过语义分析，让语法树变得有含义，最后，我们得到了分析阶段的产物，有含义的语法树。

回顾这个过程，我们就可以发现，SQL语句的分析阶段，就是一个编译器的前半段。

（PPT第13页）

SQL语句的分析阶段很重要，对于从事内核开发的人来说，我认为，需要从2个方面来把握分析阶段，要看结构明语义：

 

看结构，就是要深入理解SQL语句的结构。那么，看结构是要看什么呢？看关键字、看对象间的关系。

 

关键字起到统领整个SQL语句的作用。包括3个含义：

1 统领SQL语句的整体语义，如SELECT、UPDATE等关键字能表明SQL语句是查询还是更新。

2 统领SQL语句内的局部语义，如查询语句中，DISTINCT和 GROUP BY分别表明，在内部执行去重和分组的操作，局部的语义是不一样的。

3 统领SQL语句内的隐含语义，如表达式内部涉及到隐含的数据类型转换。

 

看对象间的关系，主要看是否存在嵌套。不存在嵌套，子句与子句之间是扁平的，可以并列，我们使用并列的“列表 List”数据结构就可以表示；而存在嵌套，则子句与子句之间就有了层次，这样的层次，需要我们使用嵌套的层次结构表示，需要在层次中标识出谁是父亲节点、谁是兄弟节点、谁是孩子节点；在每一层中都需要这样标识，就使得分析SQL的过程变得复杂。

这就是看结构。而要更好的看结构，就需要明语义，要“深入理解SQL语句的语义”。

 

明语义，除了理解刚才提到的SQL语句的整体语义、局部语义、隐含语义外，还要理解关系代数所定义的其他语义。比如说：我们要区分不同连接方式的语义差异，笛卡尔集、自然连接、外连接、半连接，每一种连接方式、他们的语义都不同。更为复杂的是，还有一些连接方式具有隐含的语义。比如说：A表外连接B表，意味着不能用B表外连接A表，即：A在前B在后的次序不能更换为B在前A在后。这就是明语义，不仅要明白表面的语义，还要明白隐含的语义。外连接隐含的语义是在说：外连接不符合关系代数的交换律。

 

不管看结构，还是明语义，对于我们实现内核的人来说，都非常重要。这关系到我们实现的数据库内核的功能是否正确。

 

这就是第二节，分析SQL语句得到语法树，把语法树传递给优化器。接下来，我们来看优化器怎么对语法树进行优化。

 

（PPT第14页）

我们来看第三节，优化计算——点点滴滴从小事做起。

（PPT第15页）

为什么说优化器的计算，要点点滴滴从小事做起呢？

第一，优化器技术涉及的算法复杂。比如多表连接的动态规划算法和贪婪算法等，每一种都复杂难懂。

第二，SQL的语义复杂，这一点我们在上一节进行了讲述。

第三，优化的技术多。优化分为逻辑优化和物理优化2个阶段，每个阶段包括的内容都非常多。在逻辑优化阶段，包括嵌套连接消除、外连接消除、子查询消除、视图消除等。而物理优化阶段，包括嵌套循环连接、hash连接、归并排序连接等多种优化技术。

第四，优化的角度多。SQL优化，有的是从形式上进行的优化，如外连接消除；有的是从语义上进行的优化，如MySQL使用完整性约束对SQL进行语义优化。

总之，SQL优化技术中，优化点多而且零散，多个优化技术之间几乎没有关联关系。各种优化方式不一样，致使优化技术没有一个提纲挈领的“纲”存在，我们常说纲举目张，SQL优化不存在一个纲来统领优化技术，所以特别难以掌握。掌握SQL优化技术，就需要极其有耐心的从每一个优化点做起，“从点点滴滴做起”、“从小事做起”。

 

（PPT第16页）

大家来看，对于这条SQL语句，优化器进行了多少个点的优化？（稍微停顿）

（PPT第17页）

我们来看，第一个优化点，---to---第9个优化点.

（PPT第18页）

现在，我们总结一下优化器有哪些技术，刚才的SQL语句中，我们用到了9点优化技术，“1 子查询消除”。。。。。。

（PPT第19页）

我们来总结一下，优化阶段包括逻辑优化和物理优化，逻辑优化主要是消减IO和CPU的消耗，物理优化主要是利用代价模型和索引快速找出代价最小的等价SQL。

逻辑优化基于启发式规则，包含的规则很多。

比如，谓词下推，可以减少元组个数，从而减少CPU消耗。

再比如，视图、派生表、子查询本质相同，都属于子查询优化。而子查询优化的本质，是消除了嵌套层次，这就能减少IO和CPU的消耗

逻辑优化，包含的优化的规则太多，增加了掌握的难度。我写的《数据库查询优化器的艺术》这本书，对各种逻辑优化技术做了较为全面的总结和分析，希望对大家掌握逻辑优化技术有所帮助。

对于物理优化，代价模型是重点，相关的要点和细节，由于时间关系，不能详细说明。上个月，我在MySQL技术嘉年华大会上做了一场《MySQL5.7代价模型》的讲座，对代价模型的要点和细节进行了总结，大家可以参考。

物理优化当中另外一个重点，是索引的利用，挖掘索引的价值，是物理优化的重点。

从整体上看，优化器就像一篇散文，散文的特点，是“形散而神不散”，优化器的技术点多而且没有统一的纲来统领所有的优化技术，这就是形散。

但是，优化器有一个核心思想，就是“代价最小”。物理优化使用代价模型求解最小代价的方式、直接使用了这个核心思想。

而逻辑优化虽然没有直接使用数学公式计算代价、但减少IO和CPU的消耗这个目的、也是“代价最小”的思维方式的应用。这就是神不散。所以，我们说，优化器就是一篇精美的散文，完全做到了“形散而神不散”。

这就是第三节的内容。

（PPT第20页）

接下来看最后一节，“谁与争锋——主流数据库对比”，这一节，我们将横向对比各个主流数据库的优化器的技术。

......