[译]数据库是如何工作（五）查询管理器

这部分是数据库的强大之处。 在这部分中，一个写的不太好的查询被转换成一个快速的可执行代码。然后执行代码，并将结果返回给客户端管理器。这是一个多步骤操作：

  • 首先对查询语句进行解析(parser)，看看它是否有效的
  • 然后重写(rewritten)去移除无用的操作并添加一些预优化
  • 然后对其进行优化(optimized)，以此来提高性能，并生成一个执行和数据访问的计划
  • 然后计划被编译(compiled)
  • 最后，执行

在这部分，我不会讨论最后两点因为他们不那么重要。 在阅读这部分之后，如果你想了解更多我推荐你阅读：

  • 最初的基于成本(cost)优化研究论文(1979):关系数据库管理系统访问途径的选择。这篇文章只有12页，计算机科学的平均水平就能理解了
  • 一个又好又有深度的演讲，介绍 DB2 9.X 是如何优化查询这里
  • 非常好地介绍了PostgreSQL如何优化查询这里。这是一份最友好的文档，因为它更多是介绍“让我们看看 PostgreSQL 在这些情况下的查询计划” ，而不是“让我们看看 PostgreSQL 使用的算法”
  • SQLite讲优化器的官方文档。这是很容易阅读的，因为 SQLite 使用的是很简单的规则。而且，只是唯一的官方文件解释它是怎样工作的
  • 一个很好的演讲关于 SQL Server 2005 的优化器这里
  • 关于Oracle 12C优化的白皮书。这里
  • 从《数据库系统概念》一书的作者那里，查询优化的2个理论课程。这里和这里 这一个很好的，重点是磁盘I/O成本，但必须有计算机科学的良好水平。
  • 另外一个理论课程，这更容易读，但只关系 join 操作符和 I/O 读写。

查询解析（parser）

每条SQL语言都会被送到解析器，在那里检查语法是否正确。如果你的查询语句有错，解析器会拒绝这条语句。例如：如果你写“SLECT ... ” 而不是 “SELECT ... ” ，就会在马上结束。

解析不仅仅是检查单词拼写，还会更深入。它还检查关键字是否按正确的顺序使用。例如，WHERE 在 SELECT 之前也会拒绝。

然后，对查询中的表和字段进行分析。解析器会使用数据库的元数据(metadata)来检查：

  • 表是否存在
  • 表中字段是否存在
  • 那些操作是否可以对那些类型字段使用(比如:你不可以用整数与字符串进行比较，就不能对整数使用substring()函数)

接着，它会检查你是否用读表(或写表)的授权。同样的，这些表的访问权限由你的DBA设置的。 在解析的时候，SQL查询语句会被转换成内部的表示(通常是树)

如果一切正常，就会把内部表示发送给查询重写器

查询重写器

在这步，我有一个查询语句的内部表示。重写的目标是

  • 对查询预优化
  • 避免不必要的操作
  • 帮助优化器寻找最佳的解决方案

这重写器执行 这重写器会对查询(query,这里应该是说内部表示)执行一系列已知的规则。如果这查询匹配到规则，就会用这个规则重写。一下是一些（可选）的非详尽的规则：

  • 视图合并： 如果你在查询中用到视图，这视图会转成视图的SQL代码
  • 子查询的展开：子查询是非常难优化的，所以重写器会尝试用修改查询的方式来删除子查询

举个例子

    SELECT PERSON.*
    FROM PERSON
    WHERE PERSON.person_key IN
    (SELECT MAILS.person_key
    FROM MAILS
    WHERE MAILS.mail LIKE 'christophe%');

或被替换成

    SELECT PERSON.*
    FROM PERSON
    WHERE PERSON.person_key IN
    (SELECT MAILS.person_key
    FROM MAILS
    WHERE MAILS.mail LIKE 'christophe%');
  • 去除不必要的操作：举个例子，如果你用了 DISTINCT 而你又有一个 UNIQUE 的唯一性约束来阻止数据的不唯一，那就就会删除 DISTINCT 的关键字
  • 消除冗余关联：如果因为有一个 JOIN 被视图隐藏，而存在两个JOIN条件或者通过关联的传递性就可以得到等价，有连接是没用的，就会将其删除。
  • 常量的计算： 你如果你写了一些东西是需要运算的，那么他会在重写阶段就算出来的。比如， WHERE AGE > 10 + 2 会被转换成 WHERE AGE > 12 ，还是 TODATE("some date") 会被转成日期时间(datetime)的格式
  • (高级) 分区裁决：如果使用分区表，重写器可以找到要使用的分区。
  • （高级）物化视图重写：如果你有个物化视图在查询中匹配到谓词子集，则重写器会检查视图是否是最新的，并修改查询以使用物化视图而不是原始表。
  • (高级) 多维分析转化：分析/窗口函数，星形连接，汇总...也被转换（但我不确定它是由重写器还是优化器完成的，因为两个进程都非常接近，它必须依赖于数据库)

然后会将这个重写的查询发送到查询优化器，开始有趣起来了！

统计

在我们看到数据库是如果优化查询之前，我们需要讨论一下统计因为没有了他，数据库是一个愚蠢的 。如果你没有告诉数据库要分析它自己的数据，它是不用去做的，而且它会做出非常糟糕的假设 但是什么样的信息是数据库需要的呢？ 我不得不(简要)地谈论数据库和操作系统是如何存储数据的。他们都使用一个最小的单元叫页(page) 或者块(block)(默认是4或者8KB。这意味着如果你需要 1KB无论如何起码都会用掉你一页了。如果你一页是 8KB，就会浪费掉 7KB 回到统计！当你要求数据库要收集统计信息时，他会计算这些值：

  • 表有多少行/页
  • 表中的每一列(column)
    • 所有数据的唯一值(distinct data value)
    • 数据值的长度(最大，最小，平均)
    • 数据值的范围(最大，最小，平均)
  • 有关表索引的信息

这些统计会帮助优化器预估查询的磁盘 I/O、CPU 和内存使用情况 每列的统计信息都很重要。举个栗子，如果有一个 PERSON 的表格就要关联(JOIN)两个列: LAST_NAME , FIRST_NAME 。通过统计，数据库知道只有 FIRST_NAME 只有 1,000 不同值，而 LAST_NAME 有 1,000,000 个不同值(每个人都有FIRST_NAME和LAST_NAME，量是一样)。所以数据库会按 LAST_NAME,FIRST_NAME 的顺序连到数据上，而不是按 FIRST_NAME,LAST_NAME 的顺序。这能减少很多对比因为 LAST_NAME 大多不相同，因为很多是时候只要对比前面 2(或者3) 个 LAST_NAME 的字符就够了。 但这些是基础统计。你可以要求数据库计算很高级的数据叫柱状图(histograms)。柱状图可以统计列中的(column)值的分布的信息。例如：

  • 最频繁使用的值
  • 位数
  • ...

这额外的统计会帮助数据库讯号一个很好的查询计划。特别是对于等式谓词（如：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%是非常不同的（对于一百万个行以内的表不太可能发生）。这个错误的统计导致有时一个查询要用8个小时而不是30秒，而且寻找根源也是个噩梦。这个例子让我们看到统计是多么的重要。

注意：当然，每个数据库都有有很多高级的统计特性。如果你想知道更多，要看下数据可的文档。正如我所言，我会尝试明白如何使用统计而我发现一个最官方的文档是 PostgreSQL。

查询优化器

所有现代数据库都用基于成本的优化(CBO)去优化查询。这思想是每个操作都放一个成本值，通过用最少成本的操作获取结果的方式，来寻找降低查询成本最好的方式。

为了明白一个基于成本的优化器是如何工作的，用一个去例子“感受”下这个任务背后的复杂度，我想应该是不错的。在这部分，我将为你介绍连接2个表的3种普通方法，并且我们将能很看到，一个简单的连接查询优化也是个噩梦。在此之后，我们将看到真正的优化器上是如何工作的。

关于这些关联，我将会把重点放在他们的时间复杂度，但数据库的优化器会计算它们的CPU成本、磁盘 I/O 和内存需求。时间复杂度和CPU的成本是非常接近的(对于像我一样懒的人来讲)。对于 CPU 的成本，我应该计算每个操作箱加法，“if语句”，一个乘法，迭代。。。 更多：

  • 每个高级代码操作有特定数量的低级CPU操作符
  • 每种CPU的操作符的成本是不同的（在 CPU周期），无论你使用 Inter Core I7，Inter 奔腾4，还是 AMD 的 opton , 换句话说，（CPU操作符的成本）取决于CPU的架构

使用时间复杂度更容易（至少对于我来讲），而使用它我们仍然能得到 CBO 的概念。我有时会讲磁盘 I/O，因为这也是个重要的概念。值得注意的是，大多数的瓶颈是磁盘I/O而不是CPU的使用率

索引

当看到 B+ 树的时候，我们会谈及 B+ 树。要记得，索引都是已排序的。

仅供参考，还有很多其他类型的索引，比如位图索引(bitmap indexs)。与B +树索引相比，它们在CPU，磁盘I / O和内存方面的成本不同。

此外，很多现代的数据库中 ，如果动态创建临时索引能改善执行计划的成本，就会对当前的查询使用。

访问方式

在使用你的关联操作符（JOIN）之前，你首先要获取你的数据。下面是如何获取数据的方式

注意：因为访问途径的实际的问题是磁盘 I/O，所以我不会讨论太多时间复杂度

全局扫描

如果你读过执行计划，你会肯定曾经看过这个单词全局扫描（full scan 或只是扫描）。全局扫描是数据库读表或者完整的索引的简单方式。对磁盘I/O来讲，一个表的全局扫描的成本明显是比一个索引扫描贵得多。

范围扫描

有很多其他类型的扫描，如索引范围扫描。例如：当你使用谓词如“WHERE AGE > 20 AND AGE < 40” 当然如果你需要有一条 AGE 字段的索引你才能使用。 我们已经在第一部分中看到，范围查询的时候复杂度大概是 log(N)+M, 其中 N 是索引中数据的数量，而 M 大概是这个范围内的行数。感谢统计信息，M 和 N 都是已知的(注意：对于谓词 AGE > 20 AND AGE < 40 来讲，M是可选择性的)。此外，对于范围扫描来讲，你无需读全局索引 ，它在磁盘I/O上比全局扫描的成本更低

唯一扫描

如果你只需要索引中的一个值，则可以使用唯一扫描。

通过行ID访问

大多数情况下，如果数据库使用索引，则必须查找与索引关联的行。为此，它将使用行ID访问。 例如，如果你做像这样的事

    SELECT LASTNAME, FIRSTNAME from PERSON WHERE AGE = 28

如果你 PERSON 表中有字段 AGE 的索引，优化器将使用索引来查找年龄是 28 的所有人，并询问表中的关联行，因为索引只有关于年龄的信息，而你想知道姓氏和名字。 但如果你现在做事是

    SELECT TYPE_PERSON.CATEGORY from PERSON ,TYPE_PERSON
    WHERE PERSON.AGE = TYPE_PERSON.AGE

PERSON上的索引将用于与TYPE_PERSON关联，但由于你没有询问表 PERSON 的信息，因此不会通过行ID访问表PERSON。 虽然它在少量访问的时候非常好用，但此操作的真正问题是磁盘I/O。如果你按行ID进行过多访问，则数据库可能会选择完整扫描。

其他访问方式

我没有介绍所有访问路径。如果您想了解更多信息，可以阅读 Oracle文档。其他数据库的 名称可能不同，但背后的概念是相同的。

JOIN 操作符

所以，我们知道如何获取我们的数据，让我们来 JOIN 下他们吧！ 我将介绍3个常见的关联运算符：合并关联(Merge Join)，哈希关联(Hash Join) 和 嵌套循环关联(Nested Loop Join) 但在此之前，我需要引入新的词汇：内部联系(inner relation)和外部联系(outer relation)。联系可以是：

  • 一个表
  • 一个索引
  • 来自上次操作的中间结果（如：前一个关联操作的结果）

当你正在关联两个联系时，关联算法会有两种不同方式管理这两种联系。在本文的剩余部分，我会假设：

  • 外部联系是左侧的数据集
  • 内在联系是右侧的数据集

举个栗子，A JOIN B 就是 A 和 B 之间的关联 ，而 A 就是外部联系，B就是内部联系。 大多数时候，A JOIN B 的成本和 B JOIN A 的成本是不一样的 在这部分，我也会假设外部联系有 N 个元素，内部关系有 M 个元素。请记得，真正的优化器会通过信息统计知道 N 和 M 的值。 注意：N 和 M 的关系是基数(cardinalities)

嵌套循环关联

嵌套循环关联是最简单的

这是是它的思想：

  • 首先遍历外部联系的每一行
  • 然后查看在内部关系中的所有看，看一下是否存在能匹配的行

下面是伪代码：

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 而言, 外部关系中(N行)要找每行的(对应关系)，都需要内部关系中循环 M 遍。所以这算法需要从磁盘中读取 N + N*M 次。但是，如果内部关系是足够小的，你可以把内部关系放到内存中，那么你只需要读取磁盘 N+M 次了(M的数据写到内存)。这种修改，内部联系一定是最小的，因为这才能更好放进内存

对于时间复杂度而言，没有区别，但对磁盘I/O来讲，上面那种方式更好。两条数据建立联系都只需读一次磁盘。

当然，内部关联可以被索引代替，这会对磁盘 I/O 更好

由于这个算法是很简单的， 如果内部关联太大以致于不易放进内存，这是另一个对磁盘更友好的版本。构思如下：

  • 不要逐行读取两个联系
  • 一块一块地读数据，并保持有两块数据（来自每个联系）在内存中
  • 比较两块数据，保留匹配到的数据，
  • 然后再从磁盘中创建新的块，并对比
  • 直到没有块可以加载

下载是可能的算法

//改进版本以减少磁盘I / O.
nested_loop_join_v2(file outer, file inner)
    for each bunch ba in outer
    // ba 现在在内存中了
        for each bunch bb in inner 
        // bb 现在在内存中了
           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个访问（每个访问获得一行）。
  • 使用此新版本，磁盘访问次数变为 外部块的数量 + 外部块的数量 * 内部块的数量
  • 如果增加每个块的大小，则减少磁盘访问次数。

注意：每个磁盘访问都会采集比以前算法更多的数据，但这并不重要，因为它们是顺序访问（机械磁盘的真正问题是获取第一个数据的时间）。

哈希关联

哈希关联的思想是：

  1. 从内部关联中获取所有元素
  2. 创建内存哈希表
  3. 逐一获取外部关系中的所有元素
  4. 计算哈希表的每个元素的哈希码(通过哈希表的哈希函数)，用于寻找对应的内部联系桶(bucket)
  5. 查看桶中的元素是否和外部表的元素匹配 对于时间复杂度来讲，我需要 假设 一些东西去简化问题：
  • 内部联系被分成 X 个桶
  • 哈希函数几乎均匀地分布哈希值，换句话来讲，每个桶的大小是相同的
  • 外部联系一个元素和桶中所有元素的匹配的时间复杂度是桶中元素数量（M/X）

所有总共时间复杂度：(M/X) * N + 创建哈希表的成本(M) + 哈希函数的成本 * N 。 如果哈希函数创建了哈希桶的大小足够小(size小，桶数更多，X越大)，那么复杂度就是 O(M+N)? 这是哈希连接的另一个版本，更节省内存但对磁盘 I/O不友好，这次：

  1. 内部和外部联系都计算哈希表
  2. 然后将他们放进磁盘
  3. 然后逐个桶比较两者的关系(一个用加载到内存，另一个逐行读文件) [原文的意思是外部联系的所有元素哈希值存在一个文件中，逐行读取。通过哈希值可以知道是几号桶，就把桶加载到内存进行对比]

合并关联

合并连接是唯一一种关联可以生成排序结果

注意：在这个简化的合并关联中，不区分内部或外部表;两者都扮演了一样的角色。但实际的实现起来是有不同的，例如，在处理重复项时。

排序

我们已经讲过了合并排序，在这种情况下合并排序是个好的算法（但如果内存足够，就不是最好的） 就是数据集是已排序的，举个栗子：

  • 如果表内部本来就有序的，比如关联条件中的索引组织表(oracle的，表中的数据按主键存储和排序)
  • 如果关联条件的联系用的是索引
  • 如果关联的数据是在查询过程中已排序的中间结果

合并关联

这部分非常类似于我们看到的合并排序的合并操作。
但这一次，我们只选择两个关系中相等的元素，而不是从两个关系中挑选每个元素。

这是它的构思：

  1. 对比两个关系中当前项(初始的时候两个当前项都是第一个)
  2. 如果他们是相等的，就把两个元素放到结果，再比较两个关系的下一个元素
  3. 如果不相等，就去对比值最小的那个关系的下个元素(因为下个元素可能能匹配)
  4. 重复 1,2,3 直到有个关系到达最后一个元素

这是有效的，因为两个关系都是已排序的，所以你不需要在这些关系中返回。

这算法一个简化版，因为它没有处理数组中有多个相同值的情况（换句话说，多重匹配）。针对这种情况，真实的版本太重复了。这就是我选择简化版的原因。

如果两个关系是已排序的，那么事件负责会是 O(N+M)

如果两个关系都需要排序，那么会加上排序的成本，时间复杂度会是 O(N*Log(N) + M*Log(M))

对计算机科学的Geeks 来讲，这里有一个可能的算法来处理多个匹配（注意：我不是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]

            //计算与a相关的重复数量
            integer nb_dup_in_a = 1:
            while (a[a_key]==a[a_key+nb_dup_in_a])
                nb_dup_in_a++;

            //计算与b相关的重复数量
            integer dup_in_b = 1:
            while (b[b_key]==b[b_key+nb_dup_in_b])
                nb_dup_in_b++;

           //在输出中写下重复项
           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

哪一个是最好的？

如果存在最佳类型的关联，就不会有那么多种类型。这个问题非常困难，因为有很多因素发挥作用：

  1. 有大量的空闲内存：没有足够的内存，你可以和强大的哈希关联说再见了（至少和全在内存中进行散列连接的方式说再见）
  2. 2个数据集的大小：举个栗子，如果你有一个非常大的表要关联个小表，嵌套循环关联会比哈希关联快，那是因为哈希关联的创建成本较高。如果你有两个很大的表，那么嵌套查询就比较耗CPU了
  3. 索引的存在：如果是两个B+树的索引，最机智的选择当然是合并关联了
  4. 如果结果需要排序：即使你正在处理的数据集是没排序的，你可能会想使用成本昂贵的合并关联（用来排序），因为合并关联后结果是有序的，你也可以把它和其他的合并关联连起来用(或者因为使用 ORDER BY/GROUP BY/DISTINCT 等操作符隐式或显式地要求一个排序结果)
  5. 如果关系已经排序：在这种情况下，合并连接是最佳候选
  6. 关联的类型: 它是等值连接（即：tableA.col1 = tableB.col2）？它是内关联，外关联，笛卡尔积还是自关联？某些关联在某些情况下无法工作。
  7. 数据的分布: 如果数据在关联条件下是有偏向的（比如根据姓氏来关联人，但是很多人同姓），使用哈希关联将是一场灾难，因为哈希函数将创建分布不均匀的桶。
  8. 如果希望连接由 多个线程/进程 执行 有关更多信息，您可以阅读DB2，ORACLE 或 SQL SERVER文档。

简单的例子

我们刚刚看到了3种类型的关联操作。 现在，我们需要关联5个表来表示一个人的信息。一个人要可能有：

  • 多个电话
  • 多个电子邮箱
  • 多个地址（可能是个土豪）
  • 多个银行账号

换个话说，我们需要用下面的查询快速得到答案

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

作为查询优化器，我必须找到处理数据的最佳方法。但是有两个问题：

每次关联应该使用什么样类型？

我有3个可能的关联（哈希关联，合并关联，嵌套关联），有可能使用 0，1 或者 2个索引(更不用说有不同类型的索引)

我应该选择什么顺序来计算关联？

例如，下图显示了4个表上3个关联的可能不同情况

所以这是我觉得的可能性：

  1. 我用暴力遍历的方式 使用数据库的统计信息，我计算每个方案的成本并得到最好的方案，但这也太多中方案了吧。对于给定的关联顺序，每个关联有3个可能性: 哈希关联、合并关联、嵌套关联。所以会有 3^4 中可能性。确定关联的顺序是个二叉树排列问题，有会有 (2*4)!/(4+1)! 种可能的顺序。而本例这这个相当简单的问题，我最后会得到 3^4*(2*4)!/(4+1)! 种可能。 抛开专业术语，那相当于 27,216 种可能性。如果给合并联接加上使用 0,1 或 2 个 B+树索引，可能性就变成了 210,000种。我忘了提到这个查询非常简单吗？
  2. 我哭了，并退出这个任务 这很诱人，但你也不得到你想要结果，毕竟我需要钱来支付账单。
  3. 我只尝试几种计划并采取成本最低的计划。 由于我不是超人，我无法计算每个计划的成本。 相反，我可以任意选择所有可能计划的子集，计算其成本并为你提供该子集的最佳计划。
    4）我使用智能规则来减少可能的计划数量 下面有两种的规则： 1. 我可以使用“逻辑”规则来消除无用的可能性，但它们不会过滤很多方案。比如：内部关系要用循环嵌套关联一定要是最好的数据集 2. 我可以接受不寻找最好的方案并用更积极的规则减少大量的可能性。比如：如何关系很少，使用循环嵌套关联并永远不使用合并关联或者哈希关联 在这个简单的例子中，我最终得到很多的可能性。但  现实中的查询还会有其他关系运算符，像 OUTER JOIN, CROSS JOIN, GROUP BY, ORDER BY, PROJECTION, UNION, INTERSECT, DISTINCT … 这意味着更多的可能性。 那么，数据库是如何做到的呢？

动态规划、贪心算法和启发式算法

关系数据库尝试过我刚才说过的多种方法。 大多数情况下，优化器找不到最佳解决方案，而是“好”解决方案 对于小型查询，可以采用暴力遍历的方法。但是有一种方法可以避免不必要的计算，因此即使是中等查询也可以使用暴力方法。这叫为动态规划编程。

动态规划

它们用了相同的（A JOIN B）子树。所以，我们可以只计算这棵树一次，保存这树的城下，当看到这棵树的时候再次使用，而不是每次看到这棵树都重新计算一次。更正规地说，我们面对的是重复计算的问题。为了避免额外计算结果的部分，我们使用了记忆术。

使用了这个技术，不再是 (2*N)!/(N+1)! 的时间复杂度了，我们只有 3^N 。在我们之前的例子中有4个连接，这意味着 336个关联顺序会降到 81 个。如果你有一个大的查询有 8 个关联（也不是很大），这意味着会从 57,657,600 降到 6561

对于计算机科学的 GEEKS。这里有个算法，是在我曾经介绍给你正式课程找到的。我不会去解释这个算法，所以只有你已经明白动态规划编程或者你算法很好（你已经被警告过了）时才去读它：

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*2^n 而不是 3^n

  • 如何我们在添加逻辑规则时避免一些模式（如：一个表有给定谓词的索引，就不是尝试用合并关联表而要只关联索引) 这会减少很多可能性，对最佳方案也不会造成很大的伤害。
  • 如果给流程添加规制（像是所有其他关系操作前执行关联操作） 这会减少很多可能性。

贪婪算法

但对一个大型的查询或者要快速得到答案(但查询速度不太快)，就会有使用另一种类型的算法，叫贪婪算法。

想法是通过一个规则（或者启发）以增量的方式构建查询计划。使用这个规则。贪婪算法能每次每步地找到问题的最好解决方案。算法从一个关联开始查询计划，然后每一步，算法会使用通过的规制把新的关联添加到新的查询计划

我们举个简单的例子吧。假设我们有个 5张表(A,B,C,D和E) 和 4次关联。为了简化问题，我们用可能会用到嵌套关联。现在使用规则是 “用最小成本关联”

  • 我们首先在5张表中任意选择一个（选A吧）
  • 我们计算每种与A关联的成本（A可能是内部联系或外部联系）
  • 我们发现 A 和 B 关联的成本最低
  • 然后对比每种与 A JOIN B的结果 的成本（A JOIN B可能是内部联系或者外部联系）
  • 我们发现(A JOIN B)再关联C有最低的成本
  • 再每种与 (A JOIN B) JOIN C 的成本
  • 。。。
  • 最后，我们会得到一个查询计划 (((A JOIN B) JOIN C) JOIN D) JOIN E)

我们是从A开始的，我们用同样的算法应用到 B 上 ，然后 C ,然后 D，然后 E。我们可以保持这个计算是最少成本的。

顺便说一下，这个算法的名字叫最近邻居法

我不会详细介绍，但上面的问题(可能性很多的问题) 通过良好的建模和在 复杂度是 N*log(N) 的排序就能很容易地解决。 而这个算法的成本在 O(N*log(N))。而完全动态规划的版本要用 O(3^N) 。如果是有20个连接的大查询，则意味着26 VS 3,486,784,401，这是一个巨大的差异！

这算法的问题是，我们假设了：找到2个表的最佳关联，保存这个关联，下个新关联加进来，也会是最佳的成本，但：

  • 即使在 A, B, C 之间，A JOIN B 可得最低成本
  • (A JOIN C) JOIN B 也许比 (A JOIN B) JOIN C 更好。

为了改善这情况，您可以基于不同的规则运行多个贪婪算法并保持最佳计划。、

其他算法

[如果你已经厌倦了算法，请跳到下一部分，这里说的对于文章的其余部分并不重要]

寻找最好的可能性计划对于计算机科学的研究者来讲是一个活跃的话题。他们经常为特定的问题/模式尝试寻找更好的的解决方案。例如：

  • 如果查询是星型关联（这是某种多次关联查询），某些数据库使用一种特定的算法。
  • 如果查询是并行查询，则某些数据库将使用特定算法

还研究了其他算法来替换大型查询的动态规划。贪婪算法属于称为启发式算法的大家族。贪心算法遵循规则（或启发式），保留在上一步找到的解决方案并将它追加到当前步骤的解决方案中。一些算法遵循规则并逐步应用(apply),蛋不用总是保留上一步的最佳解决方案。他们统称启发式算法。

比如，遗传算法遵循规则，但最后一步的最佳解决方案通常不会保留：

  • 解决方案表示可能的完整查询计划
  • 每一步保留了P个方案（即计划），而不是一个
    1. P个计划随机创建
    1. 成本最低的计划才会保留
    1. 这些最佳计划合起来产生P个新计划
    1. 一些新的计划被随机改写
    1. 1，2，3步重复 T 次
    1. 然后在最后一次循环，从P个计划里得到最佳计划。

循环次数越多，计划就越好。 这是魔术？

不，这是自然法则：适者生存！

实现了遗传算法，但我并没有知道它会不会默认使用这种算法的。

数据库中还使用了其它启发式算法，像『模拟退火算法（Simulated Annealing）』、『交互式改良算法（Iterative Improvement）』、『双阶段优化算法（Two-Phase Optimization）』…..不过，我不知道这些算法现在是否在企业级数据库应用了，还是只是用于研究型数据库。

如果想了解更多，你可以读这篇文章，它还介绍更多可能用到的算法《数据库查询优化中关联排序问题的算法综述》

现实中的优化器

[ 可以到下一部分，这里不太重要 ]

但，所有的叽里呱啦都是非常理论化的。因为我是开发者而不是研究员，我喜欢具体的例子。

我们来看看SQLite 优化器是如何工作的。这是一个很轻型的数据库，所以他使用优化器基于贪心算法+额外规则来限制可能性数量

  • SQLite 在有交叉关联（CROSS JOIN 如： SELECT * FROM A,B）操作符的时候从不会给表重新排序
  • 关联都用嵌套连接实现
  • 外联（outer JOIN）始终按照顺序评估
  • ...
  • 3.8.0版本之前，SQLite 使用“最近邻居”算法来搜索最佳查询计划

等一下，我们已经看过这个算法了。多么地巧合 ，从3.8.0版本（发布于2015年）开始，SQLite使用『N最近邻居』贪婪算法来搜寻最佳查询计划

接下来，让我看看其他的优化器是如何工作的。IBM 的 DB2 看起来和其他企业级别的数据库一样，我会关注 DB2 是因为我切换到大数据之前，最后使用的数据库。

如果我们看它的官方文档，我们了解到 DB2 的优化器允许你使用7中不同级别的优化：

  • 对关联使用贪婪算法
    • 0 最小优化，使用索引扫描和嵌套循环关联并避免一些查询的重写
    • 1 低级优化
    • 2 全局优化
  • 对关联使用动态规划
    • 3 适度优化并粗略的近似估计
    • 5 全局优化并使用启发式的所有技术
    • 7 全局优化和5相似，但不用启发式
    • 9 最大程度的优化，不节省计算力/成本，考虑关联的所有顺序的可能性，包括笛卡尔乘积

我们可能看到 DB2 使用贪心算法和动态规划 。当然，由于查询优化是数据库的主要功能，它们不会分享他们用的启发算法。

仅供参考， 默认的级别是 5 。默认的优化器使用下面的特性：

  • 使用所有可用的统计信息 ,包括频率的值和分数位的统计
  • 使用所有查询的重写规则 （包括 物化查询表路由materialized query table routing），除了再非常罕见的情况要用计算密集的规则外。
  • 使用 动态规划枚举关联 ，用于
    • 有限使用组合的内在联系
    • 涉及查找表的星型模式，有限使用笛卡尔乘积
  • 考虑各种的访问方式，含列表预取（list prefetch，注：我们将会看这是什么），index ANDing（注：一种对索引的特殊操作），和物化查询表路由。

默认情况下， DB2 对关联顺序使用受限制启发的动态规划算法

查询计划缓存

由于创建查询计划会花很多时间，所以绝大部分数据库会存储 *查询计划的缓存* 避免没有必要的重复计算。这是个大话题啊，因为数据需要知道什么时间要更新过时的计划。解决这问题的想法是设置阈值，当统计表的改动超过某个阈值，就会从将这个表从缓存中清除

查询执行器

在这阶段，我们有优化器的执行计划了。这计划会被编译成一个可执行代码。然后，如果有足够的资源（内存，CPU）就会通过查询执行器执行。在这计划中的操作(JOIN,SORT BY...)可能会串行或者并行执行；这取决于执行器。为了获取和写入数据，查询执行器会和数据管理器互相配合，这就是这篇文章下一部分要讲的

posted @ 2019-05-05 21:19  JOJO_2046  阅读(687)  评论(0)  编辑  收藏  举报