数据库概览
我们已经接触到了数据库中的一些基本组件，现在我们来看看数据库的蓝图。
一个数据库是很容易访问和修改的数据信息的集合。但是一堆简单的文件也能做到这一点，实际上，最简单的数据库如SQLite就是一堆文件而已。但SQLite设计巧妙，可以让你：
> 使用事务来保证数据的安全和一致性
> 快速访问上百万级的数据
通常用以下图表来描述一个数据库：
在写这部分之前，我读的很多书和论文都有各自描述数据库的蓝图方式。这篇文章也是，所以你也不用太在意这里是怎么划分或命名数据库的各部分的，重要的是各个不同的模块；总的思想是数据库被划分成以下交互的模块。
核心模块：
> 进程管理器：很多数据库都有自己需要管理的进程/线程池，有的现代数据库为了达到纳秒级的精度，使用了自己的线程而不是操作系统的线程
> 网络管理器：网络I/O是个重大的问题，特别对于分布式的数据库，这就是为什么有的数据有自己的网络管理器
> 文件管理器：磁盘I/O是数据库的第一个瓶颈，有一个自己的文件系统管理器来处理操作系统的文件系统甚至替代它是很重要的
> 内存管理器：为了避免因磁盘I/O带来的时间损耗，较大的内存是必要的。但是如果你要管理大内存，你也需要一个高效的内存管理器，特别是你有很多数据 库查询同时使用内存
> 安全管理器：管理对用户的鉴权和授权
> 客户端管理器：管理用户连接
> ...
工具模块：
> 备份管理器：用来备份和恢复一个数据库
> 恢复管理器：用来在数据库崩溃后恢复到之前的状态
> 监控管理器：记录数据库活动和提供工具来监控数据库
> 管理管理器 : 存储元数据（比如表名和结构）以及提供管理数据库，模式，表空间，...
> ...
查询管理器：
> 查询解析器：检查数据库查询是否合法
> 查询重写器：预优化一个数据库查询
> 查询优化器：优化一个数据库查询
> 查询执行器：编译和执行一个数据库查询
数据管理器：
> 事务管理器：处理事务。
> 缓存管理器：在从磁盘读数据或写入数据到磁盘时提前将数据放到内存中。
> 数据访问管理器：访问在磁盘上的数据。
在本文中，将主要阐述数据怎么通过以下管理器来管理SQL查询：
> 客户端管理器
> 查询管理器
> 数据管理器 （也会涉及到恢复管理器）
客户端管理器
客户端管理器是处理用户连接的部分，客户端可以是一个web服务器或终端用户应用，客户端管理器通过一系列的API提供了访问数据库的不同方式：JDBC，ODBC，OLE-DB...
当你连接一个数据库的时候：
> 管理器首先检查鉴权（用户名和密码），然后检查你是否有权限使用某个数据库。访问权限由DBA来配置
> 然后，检查是否有进程或线程来管理你的数据库查询
> 也会检查数据库当前是否在高负载下
> 等待一小段时间获取所需资源，如果等待超时，那么它就关闭连接并给出错误消息
> 然后你的查询将发送给查询管理器并被执行
> 因为查询过程不是“一蹴而就”的，所以一旦从查询管理器得到数据库，就会将那部分数据存入缓存区并开始发送给你。
> 如果遇到问题，就关闭连接，给你一个消息进行解释并释放占用的资源。
查询管理器
这部分是数据库强大能力的所在，在这步中，质量差的查询将被优化成效率高的代码，这份代码将被执行并将结果返回到客户端管理器，它经历了一下操作：
> 解析查询，看其是否合法
> 重写查询，去除无用的语句和其他一些预优化操作
> 优化查询，提高性能效率并且转换成一个执行和数据获取计划
> 编译查询
> 执行查询
接下来，我们将不会涉及到以上的最后两步，因为它们相对没那么重要。
读完一下内容，如果你想了解我推荐你阅读一下内容：
> 基于开销的查询优化论文原版：Access Path Selection in a Relational Database Management System.这篇文章只有12页，你只需要有计算机科学的平均水平就可以读懂
> 一篇很好且有深度的关于DB2 9.X如何进行查询优化的文章 DB2 9.X
> 一篇讲解PostgreSQL优化查询的文章。PostgreSQL，这篇文章很容易让人接受比起“让我们看看在PostgreSQL中使用的算法”，它更像是“让我们看看在这些查询场景下PostgreSQL的查询策略”
> 官方的关于SQLite优化的文档，这篇比较“容易”理解因为SQLite使用的规则很简单，这是唯一的一篇官方的文档来解释SQLite内部如何工作的
> 一篇关于SQL Server 2005查询优化的文档
> Oracle 12c查询优化白皮书
> 2堂关于查询优化的理论课程here and there，来源于“数据库系统概念”的作者，着重且很好地阐释了磁盘I/O开销但是需要较高的CS水平
> 另外一个更好理解的理论课here，但是只着重阐释了join操作和磁盘I/O
查询解析器
每一个SQL语句都将被发送给解析器来检查语法，如果语句中有语法错误，那么解析器将拒绝这个查询。例如，如果你写“SLECT ...”而不是“SELECT ...”,那么故事到这就结束了：)
除了检查语法，它还会检查使用的关键字的顺序，例如在SELECT前使用WHERE关键字将会它被拒绝。
接着，在查询中的表和字段将会被分析，解析器使用数据库的解析器来检查：
> 表是否存在
> 表中的字段是否存在
> 针对某个字段的操作是否可行（例如，你不能将一个整数和字符串进行比较，你不能对整数使用substring函数）
接下来会检查你是否有读（或写）表的的权限，这些权限是由DBA来进行设置。
在解析的过程中，一个SQL查询被转换成内部的表达方式（一般是一棵树）
如果一切OK，那么这个内部的表达方式会被发送到查询重写器。
查询重写器
在这一步，我们已经有了查询的内部表达方式，重写器的工作是：
> 预优化查询
> 避免不必要的操作
> 帮助优化器找到可能的最佳优化方案
重写器对查询执行一系列规则，如果查询符合某个规则模式，则规则被应用，查询被重写。一下提到了两个规则：
> 视图结合：如果在查询中使用视图，视图将会和其相关的SQL代码一起进行转换
> 子查询拉平：有子查询时很难进行优化，所以重写器会试着修改查询来移除子查询
例如，
1 SELECT PERSON.* 2 FROM PERSON 3 WHERE PERSON.person_key IN 4 (SELECT MAILS.person_key 5 FROM MAILS 6 WHERE MAILS.mail LIKE 'christophe%');
将会被替代成
1 SELECT PERSON.* 2 FROM PERSON, MAILS 3 WHERE PERSON.person_key = MAILS.person_key 4 and MAILS.mail LIKE 'christophe%';
> 移除不必要的操作符：例如你使用了关键字DISTINCT，而你也使用了UNIQUE限制来防止数据不是唯一的，这时DISTINCT关键字将被移除
> 多余的连接消除：如果由于一个连接隐藏在视图或者由于传递性产生另一个无效连接的两个相同的连接情况，会移除多余的连接。
> 常量化数学运算：如果你写的SQL中有算数运算，那么在重写时将被计算出来。如“WHERE AGE > 10 + 2”会被转换成“WHERE AGE > 12”， TODATE("some date")将会被转换成日期时间格式
> （高级）分区精简：如果你使用分区表，重写器可以找到该使用哪些分区
> （高级）物化视图重写：如果可以判断通过查询物化视图的代价更小且是最新的，那么重写器将使用物化视图而不是原生的表
> （高级）自定义规则：如果对于查询你有一些自定义的规则，那么重写器将执行这些规则
重写完后的SQL查询将发送给查询优化器，现在开始变得有趣起来！
统计分析
在我们开始谈数据库查询优化器之前，让我们谈谈统计分析，因为没有它数据库就有点傻了。如果你不告诉你的数据库要分析数据，它就不会去做并做一些很糟糕的假设。
数据库需要哪些类型的信息呢？
我得先谈谈数据库和操作系统是怎么存储数据的。它们使用了称为“页”的最小单元（默认是4或8kb），这就意味着如果你只需要1kb的数据，你也需要使用1页，如果是每页8kb，那你就浪费了7kb。
让我们继续讲统计分析，当你让数据库收集分析信息时，它会去运算如下信息：
> 表中的行或页数
> 对于表中的每列：不同的数据值，数据的长度，数据值的范围信息（最大，最小，平均）
> 表的索引信息
这些统计信息将会帮助查询优化器预估查询的磁盘I/O，CPU和内存使用。
对于每一列的统计信息是很重要的，例如表PERSON需要通过它的两列LAST_NAME，FIRST_NAME做join操作。有了统计信息，数据库知道FIRST_NAME列一共有1000个不同的值，LAST_NAME共有1000000个不同的值，据此，数据库就用LAST_NAME, FIRST_NAME而不是FIRST_NAME, LAST_NAME做join操作，原因在于LAST_NAME不同的值多，大部分情况比较前2~3个首字符就足够了，因此比较的次数更少。
还有一些基本的统计信息，你也可以让数据库统计一些更高级的直方图信息，直方图描述了在列中值的分布情况。例如，
> 出现最多的值
> 分位点
> ...
这些额外的统计信息可能可以帮助数据库找到最佳的查询方案，特别是判断等于的情况（例如：WHERE AGE = 18）或者范围判断（例如：WHERE AGE > 10 and AGE > 40）,因为数据库会更好的知道进行这些判断要涉及到的数据行数。（专业术语叫做选择度）
这些统计信息被存储在数据库的元数据metadata中，例如你可以在如下表中看到统计信息：
> 在Oracle中，USER/ALL/DBA_TABLES和USER/ALL/DBA_TAB_COLUMNS
> 在DB2中，SYSCAT.TABLES和SYSCAT.COLUMNS
这些统计信息得是最新的，没有比数据库认为某个表只有500行而实际它有1 000 000行更糟糕的事了。唯一的缺陷是这些统计信息需要花时间来计算。这就是为什么大部分的数据库没有自动去计算这些信息。当数据量达到百万级时，计算起来就开始困难起来，在这种情况下，你可以选择只计算一些基本信息或者某个示例数据库的状态。
举例来说，当我在某个项目中需要处理亿级别的表数据时，我只针对其中的10%进行统计，这样就省了很多时间。有时可能通过Oracle 10G选取的10%的数据和总的100%的数据情况有很大出入，这样可能会让数据库作出很糟糕的决策，（尽管对于100M行的数据来说，这种情况不大可能发生），糟糕的统计信息让查询花费近8小时而不是30s搞定，要找到根由也是一场噩梦，这个案例告诉我们统计信息有多重要。
Note：对于各个数据库来说可能有更高级的统计信息，你可以通过阅读数据库文档来了解更多。我曾试着深入理解统计信息怎么使用，找到了一篇很好的官方文档PostgreSQL
查询优化器
所有的现代数据库都使用Cost Based Optimization（CBO）来做查询优化，其基本思想是通过使用最小开销的操作链来减少查询开销得到结果。
要理解查询优化是怎么工作的，我们来看看这个任务的复杂度。我将展示给你三种方式来连接两个表，你会发现即使是优化一个简单的连接查询也是一场噩梦，接着，我们再看查询优化器是来如何做优化的。
对于这些连接，我们只关注它的时间复杂度，而数据库的优化器还会计算它的CPU，磁盘I/O和内存的开销。时间复杂度和CPU开销之间的区别很小（作者很懒，为了省事），对于CPU开销，我需要计算每个操作如加法，if语句，乘法，迭代等等：
> 每个更高级的代码操作对应了一系列的CPU底层操作
> CPU操作的开销对于不同的CPU（Intel Core i7， Intel Pentium4， AMD Opteron）也不一样，与CPU架构有关。
使用时间复杂度描述会更简单，也足够阐述CBO的理念。有的时候也会提到磁盘I/O，很多时候性能瓶颈往往在磁盘I/O而不是CPU。
索引
之前讲B+树的提过索引，这些索引已经被排好序了。
还有一些别的索引类型如位图索引，它们与B+树索引的CPU，磁盘I/O和内存开销也不一样。
甚至，还有很多现代数据库可以动态的创建临时索引以提高执行时的性能。
访问方式
在做join操作之前先得拿到数据，以下阐述了怎么拿到数据。
Note：访问路径涉及到的问题都是磁盘I/O，所以这里不过多的谈时间复杂度。
全扫描
如果你读过查询执行计划，那么你一定见过全扫描（或扫描）这个词，全扫描就是数据库读取整个表或索引。对于磁盘I/O来说，表的全扫描要比索引的全扫描开 销大得多。
范围扫描
有不同类型的扫描如索引范围扫描，当你使用例如“WHERE AGE > 20 AND AGE < 40”时就派上用场了。
你如果要使用索引范围扫描先得对AGE建立索引。
在上一篇中有讲到范围查询的时间复杂度是log(N)+M，N是这个索引的数据量，M是预估的此范围内的数据行数，统计信息里有N和M的值。更重要的是，范围 扫描在磁盘I/O上要比全扫描开销更小。
唯一扫描
如果你要从索引中得到特定值，那么你就需要唯一扫描。
行id
大多时候，如果数据库使用索引，它将会去找与索引相关的行，可以通过行id去找。
例如，如果你要做如下的SQL查询
SELECT LASTNAME, FIRSTNAME from PERSON WHERE AGE = 22
如果PERSON表有在age列上的索引，优化器将会通过索引找到所有年龄为28的人，然后去关联表中相关的行去获取lastname和firstname。
但是，如果你做如下的SQL查询
SELECT TYPE_PERSON.CATEGORY from PERSON ,TYPE_PERSON WHERE PERSON.AGE = TYPE_PERSON.AGE
PERSON表的索引将会用来与TYPE_PERSON做join操作，但由于语句中无需获取PERSON表中的信息，所以不会通过行id去访问PERSON表。
尽管通过行id去访问只需很少的访问次数，但也可能有磁盘I/O的隐患。如果你需要太多次通过行id去访问表中的数据，数据库可能会选择进行一个全扫描。
其他方式
这里并没有将所有的访问方式列出来，如果你想深入了解，可以阅读文档Oracle documentation, 对于其他数据库访问方式的命名可能不一样，但是背后的理念 是一样的。
Join操作符
现在我们知道如何获取数据了，现在让我们开始做连接。
这里讲述三种join操作，合并连接，散列连接，嵌套循环连接。在这之前，我们先谈谈两个概念：内部关联和外部关联。关联可以是：
> 一个表
> 一个索引
> 上一个操作的中间结果（如上一次连接的结果）
当你连接两个关联的时候，join算法对着两种关联的处理也不同，在本文的余下章节，我将假定
> 外部关联是左边的数据集
> 内部关联是右边的数据集
例如，A JOIN B是对A和B做连接操作，其中A是外部关联，B是内部关联。
大多数情况下，A JOIN B的开销与B JOIN A的开销是不同的。
在这部分，假设外部关联有N个元素，内部连接有M个元素。记住在实际中的查询优化器可以通过统计分析数据知道N和M的值。
Note：N和M是这两个关联的基数。
嵌套循环连接
嵌套循环连接是最简单的一个。
其思想是：
> 对于外部关联的每一行
> 去外部关联中寻找与之匹配的行
如下是伪代码：
nested_loop_join(array outer, array inner) for each row a in outer for each row b in inner if (match_join_condition(a,b)) write_result_in_output(a,b) end if end for end for
由于有两次遍历，所以时间复杂度是O(N * M)。
在磁盘I/O方面，对于外部关联的每一行，内部的循环都需要从内部连接中读取M行，整个算法需要从磁盘读取N + N * M数据行。但是，如果内部关联足够小，你可以将这个关联放在内存中，这样就只需要M+N次读取，要这样修改，内部连接必须是最小的那个，因为它更有可能被放入内存中。
对于时间复杂度来说没什么变化，但是只需要对每个关联读取一次对磁盘I/O来说大有裨益。
当然，这个内部关联可以用index来替代，这对磁盘I/O来说更好。
尽管此算法很简单，还有另一种针对内部连接数据过多但磁盘I/O高效的版本，其基本思想是：
> 比起对每个关联一行一行的读
> 一块一块的读取（两个关联）到内存中
> 比较两个块中的行并保存那些匹配的行
> 再从磁盘中读一个块并对他们进行比较
> 直到数据块全部加载到内存中
如下是可能的算法的伪代码：
// improved version to reduce the disk I/O. nested_loop_join_v2(file outer, file inner) for each bunch ba in outer // ba is now in memory for each bunch bb in inner // bb is now in memory for each row a in ba for each row b in bb if (match_join_condition(a,b)) write_result_in_output(a,b) end if end for end for end for end for
这种方式的时间复杂度没变，但是减少了读取磁盘的次数：
> 上个版本的算法需要N + N * M次磁盘读取（每次读取获取关联中的一行）
> 新的版本的算法读取磁盘的次数为外部关联的数据块数 + 外部关联的数据块数 * 内部关联的数据块数
> 如果减少块数的大小就减少了磁盘读取次数
Note：每次磁盘读取比上个版本的算法读取更多数据，但这不是重点因为它们是顺序访问的。（重点在于获取到第一部分的数据）
散列连接
散列连接比嵌套循环连接更复杂但是在大多时候效率更好。
其思想是：
> 拿到内部关联的所有数据元素
> 在内存建一个哈希表
> 逐个拿到外部关联的的数据元素
> 计算每个元素的哈希值（通过哈希表的哈希函数）来找到内部连接中对应的“桶”（bucket）的位置。
> 查看桶中的元素和外部表中的元素是否匹配
对于时间复杂度，为简化问题做如下假设：
> 内部关联被分成了X个桶
> 哈希表在对两个关联映射时哈希值分布均匀，即桶的大小一致。
> 对一个外部关联的的元素和一个桶中所有元素做匹配时开销与桶中元素的个数正相关。
时间复杂度为(M / X) * N + 创建哈希表的开销f（M）+ 哈希函数的开销 * N。
如果哈希函数创建出来的桶足够小，那时间复杂度为O(M + N)。
另一个版本的哈希连接更加内存友好但磁盘I/O开销更大：
> 对于内部关联和外部关联都计算出哈希表
> 将他们放到磁盘中
> 比较两个桶（其中一个加载到内存中，另外一个一行一行去读）
合并连接
合并连接是唯一可以产生有序结果的连接。
Note：在这个简化的合并连接中，没有外部关联和内部关联的概念，两个表的角色是一样的。但实际的实现方式可能有所不同，比如在处理重复值时。
合并连接可以分成如下两步：
> （可选）排序连接运算：根据连接关键字对输入进行排序。
> 合并连接运算：排序好的输入进行合并。
排序
之前有提到归并排序，在这里归并排序是个好的算法（但当内存足够时它并不是最佳选择）
有时数据已经排好序了，例如：
> 如果表本来就是有序的，如基于索引的表
> 如果关联本身就是一个索引
> 如果在查询中连接的中间结果本身就是排序的
合并连接
这部分和归并排序的合并过程很类似，但这次不是去从两个关联表中去选取每个元素，而是从关联中去找相等的元素。其思想是：
> 先比较两个关联中的当前元素（刚开始时当前元素为关联中第一个）
> 如果它们相等，就把他们放入结果中，再继续比较关联中的下一个元素
> 如果不相等，用小的元素所在的关联中的下一元素继续比较（因为小的下一个可能与之前的大元素可能相等）
> 重复前3步，直到某个关联的最后一个元素
这种方法行得通，因为它们已经排好序了，你无需在关联中“回头”去找。
此算法是简化的版本因为它没有处理相同的元素出现多次的情况。实际的版本要更复杂。
如果两个关联已经排好序，那总的时间复杂度为O(N + M)。
如果两个关联需要排序，那总的时间复杂度为O(N * Log(N) + M * Log(M))
对于一些挑剔的技术狂来说，这里有一个可以处理多个重复值的算法。（Note：我对此算法并没有100%的把握）
mergeJoin(relation a, relation b) relation output integer a_key:=0; integer b_key:=0; while (a[a_key]!=null or b[b_key]!=null) if (a[a_key] < b[b_key]) a_key++; else if (a[a_key] > b[b_key]) b_key++; else //Join predicate satisfied //i.e. a[a_key] == b[b_key] //count the number of duplicates in relation a integer nb_dup_in_a = 1: while (a[a_key]==a[a_key+nb_dup_in_a]) nb_dup_in_a++; //count the number of duplicates in relation b integer dup_in_b = 1: while (b[b_key]==b[b_key+nb_dup_in_b]) nb_dup_in_b++; //write the duplicates in output for (int i = 0 ; i< nb_dup_in_a ; i++) for (int j = 0 ; i< nb_dup_in_b ; i++) write_result_in_output(a[a_key+i],b[b_key+j]) a_key=a_key + nb_dup_in_a-1; b_key=b_key + nb_dup_in_b-1; end if end while
哪一种是最佳的连接方式？
如果有的话，那么就没有这么多种连接了。这个问题不好回答因为有很多因素影响：
> 空闲内存的大小：没有足够的内存就要和强大的散列连接说byebye了
> 数据集的大小：如果你有一个超大的表和一个很小的表，嵌套循环连接可能就比散列连接更快，因为创建哈希表时开销很大。如果你有两个超大的表， 那循环嵌套连接的CPU开销就很大了。
> 索引是否存在：有两个B+数的索引那使用归并排序就很合理了。
> 如果结果需要排序：尽管你处理的是未排序的数据，你也可能使用合并连接，因为得到的结果是排好序的，这个中间结果可能在另一个合并排序中用到 （或者查询使用ORDER BY/GROUP BY/DISTINCT操作隐式或显式需要查询的结果为有序。）
> 连接的类型：它是一个等值连接吗？（如tableA.col1 = tableB.col2）它是一个内连接，外连接，笛卡尔积的连接或是自连接？有些连接在某些场合不适 合。
> 数据的分布：如果数据分布很倾斜（使用people的lastname做join操作时，而大部分人的lastname是一样的），使用散列连接将会是灾难因为哈希函数 会创建分布严重不均的桶。
想了解更多，请阅读文档DB2，ORACLE或SQL Server。
简单的例子
我们介绍了3中不同的join操作，假如我们需要连接5张表来获取一个较完整的Person信息，一个PERSON有：
> 多个MOBILES
> 多个MAILS
> 多个ADDRESSES
> 多个BANK_ACCOUNTS
我们需要通过以下查询快速得到结果：
SELECT * from PERSON, MOBILES, MAILS,ADRESSES, BANK_ACCOUNTS WHERE PERSON.PERSON_ID = MOBILES.PERSON_ID AND PERSON.PERSON_ID = MAILS.PERSON_ID AND PERSON.PERSON_ID = ADRESSES.PERSON_ID AND PERSON.PERSON_ID = BANK_ACCOUNTS.PERSON_ID
作为一个查询优化器，需要找到最佳的处理数据的方案。但有两个问题：
> 对于每个join，该使用哪种join方式？
> 使用何种顺序做连接操作？
例如，下图展示了对4张表做3次join操作的不同方案：
以下是可能的方法：
> 使用暴力方式：通过数据库的统计信息，先计算出每一个可行方案的开销，然后保存那个开销最小的。对于每个特定的join顺序，每个join的底层算法有 三种方式：散列连接，合并连接，循环嵌套连接。所以，对于每个特定的join顺序，共有34个不同情况。join顺序是一个对二叉树做全排列的问题，一共有 (2*4)!/(4+1)!个不同的顺序，这样总的情况就有34*(2*4)!/(4+1)!种。共27216中方案，如果在加上合并连接使用的0,1,或2个B+树索引的情况，总方案个数将达 到210000，但之前就讲过这个查询本身很简单。
> 哭着不干了：听着很诱人但我还需要工资来换信用卡呢。
> 随便找几个方案试试，然后选那个开销最小的
> 添加一些规则来减少方案的个数：有两种规则，其一，“逻辑”规则移除一些没用的方案，如“使用嵌套循环的内部关联（表）的数据集最小”，其二， 接受现实不去寻找最佳方案，而是用激进的规则来减少方案个数。如“如果关联的数据集很小，那就是用循环嵌套连接而不是合并连接或散列连接。”
在这样一个简单的查询中，执行方案的情况就有很多种。但现实中查询情况可能还有更多的关系操作符如OUTER JOIN， CROSS JOIN， GROUP BY， PROJECTION， UNION， INTERSECT， DISTINCT...,这意味着可能性可能更多。
那数据库到底是怎么处理的呢？
动态编程，贪心算法和启发式算法！
关系型数据库会使用我说的这些方式，真实的查询优化器会在有限的时间内去找最佳的解决方案。
大多时候，查询优化器找到一个“上佳”的方案，但不一样是“最佳的”。
对于简单的查询，使用暴力遍历的方式也是有可能的。但是有办法在避免很多不必要的计算后，对于一些中等复杂的查询也可以使用暴力方式，这就是动态编程
动态编程
其思想是很多查询执行计划是很类似的，看图：
它们有一样的（A JOIN B）子树，所以没必要在每次执行计划中去计算这个子树的开销，可以将这个开销计算出来后保存下来，当再次见到这棵子树的时候就 可以使用了。通俗来讲，我们处理的是重叠的问题，为了避免额外的计算，我们使用记忆模式。
使用这种方式，时间复杂度从(2*N)!/(N+1)!变为3N，在之前的例子中意味着复杂度从366减少到81，如果有8个join的查询，复杂度从57 657 600减少到6561.
对于技术狂来说，这个course里有介绍这种算法。在这里不做详细解释，如果你之前知道动态编程或者擅长算法不妨读读。
procedure findbestplan(S) if (bestplan[S].cost infinite) return bestplan[S] // else bestplan[S] has not been computed earlier, compute it now if (S contains only 1 relation) set bestplan[S].plan and bestplan[S].cost based on the best way of accessing S /* Using selections on S and indices on S */ else for each non-empty subset S1 of S such that S1 != S P1= findbestplan(S1) P2= findbestplan(S - S1) A = best algorithm for joining results of P1 and P2 cost = P1.cost + P2.cost + cost of A if cost < bestplan[S].cost bestplan[S].cost = cost bestplan[S].plan = “execute P1.plan; execute P2.plan; join results of P1 and P2 using A” return bestplan[S]
对于更复杂的查询还是可以使用动态编程但可以使用额外的规则来减少可能性：
> 这里仅分析一个特定的执行计划（左深树：left-deep tree），这样的复杂度为n*2n而不是3n
> 使用一些逻辑规则来减少方案数（如“如果一个表有针对某个预测的索引，那对合并连接时要对索引而不是表”），这将会减少方案数，不会对最佳方案造成 很大影响。
> 在流程中也添加规则（如“在做其他关系型操作前，先做join操作”），这也会减少很多情况。
> ...
贪心算法
但对于复杂的查询要快速得到结果，另一种算法用到了，这就是贪心算法。
其思想是定制一些规则以递进的方式来构建一个执行计划。通过这种方式，贪心算法每次找到一个问题的最佳方案。这个算法从一个JOIN开始，然后每次将一个新的JOIN加入执行计划。
让我们来看看一个简单的例子，假设我们有一个查询有对5个表的4次join（A, B, C, D和E）。为简化问题，使用嵌套连接作为一个可行的连接。规则为“使用开销最小的连接”。
> 随机从5个表中的某个开始（假设为A）
> 计算每个表和A做连接的开销（A作为内部关联或外部关联）
> 发现A JOIN B开销最小
> 然后计算每个表和（A JOIN B）做连接的开销（A JOIN B作为内部关联或外部关联）
> 发现（A JOIN B）JOIN C开销最小
> 然后计算每个表和（A JOIN B）JOIN C做连接的开销...
> ...
> 最后得出执行计划(((A JOIN B) JOIN C) JOIN D) JOIN E)
因为刚从A开始，我们也可以对B, C应用同样的算法。最后保存那个开销最小的执行计划。
这个算法也有个名字叫Nearest neighbor algorithm。这里不细讲，但是建模后排序的复杂度为N*log(N)。这种算法通过全动态编程的版本的时间复杂度为O(N*log(N)) vs O(3N)，对于一个有20个连接的复杂查询，这意味着26 vs 3 486 784 401，差别还是很大的。
这个算法的缺陷在于我们总假定通过将最佳连接和添加新得连接结合能得到两个表的最佳连接方式并开销最小。但是：
> 尽管A JOIN B可能是在A，B和C中的最小开销的连接
> （A JOIN C）JOIN B也可能比（A JOIN B）JOIN C的开销更小
为了改进这个结果，你可以针对不同的规则使用贪心算法来得到最佳的执行计划。
其他算法等（略...）
查询计划缓存
创建执行计划相对耗时，大多数据库将执行计划缓存下来以避免相同的执行计划被重复计算。这也是一个大的话题因为数据库也需要知道什么时候更新已过时的执行计划，方法是创建一个线程，当这个线程上的表的统计分析改变时，那与这个表相关的查询计划将从缓存中清除出去。
查询执行器
到这步后，你已经有了一个优化过的执行计划了。这个执行计划被编译成可执行的代码。然后，如果有足够的资源（内存，CPU），它将被查询执行器执行，查询计划中的（JOIN，SORT BY ...）可以被顺序或并行执行；这由执行器决定。为了读写数据，查询执行器将要和数据管理器交互，将是下一部分的内容（未完待续~）
扫一扫
7501
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
