基于实践检验的执行计划优化的装置及方法

技术领域

本发明涉及关系型数据库的数据处理技术领域，具体涉及一种基于实 践检验的执行计划优化的装置及方法。

背景技术

关系型数据库在执行结构化查询语言(Structured Query Language， SQL)语句时，可通过多种访问路径来获取数据结果，例如可以通过索引 扫描来获取，也可以通过全表扫描来获取，每一种路径被称为该SQL语 句的一个执行计划。不同的执行计划消耗不同的系统资源，如消耗不同的 磁盘I/0、内存和CPU等。关系型数据库在执行SQL语句之前，会生成该 SQL语句的多个执行计划，并选择其中对系统资源消耗最小的执行计划， 称为“最优执行计划”。其中，生成执行计划并选择最优执行计划的模块 称为“优化器”。如图1所示。

传统的优化器有以下两种：基于规则的优化器和基于成本的优化器。 其中，基于规则的优化器(Rule Based Optimizer，RBO)即预先对不同的 访问路径(如索引扫描、全表扫描等)设定优先级，认为优先级别越低的 访问路径的执行代价越大。因此RBO会选择优先级别最高的访问路径作 为最优执行计划。比如当一个SQL语句的查询条件中包含的数据列具有 索引，RBO会始终认为使用索引扫描数据优于全表扫描，如图2所示。

这种RBO执行计划的选择策略由于不考虑数据的分布、数据量大小 等数据特性，也不考虑I/O、CPU及网络消耗等系统环境，往往会选择次 优的执行计划，导致系统性能不高。比如当全表只有少量数据，通过扫描 全表会比通过扫描索引访问表数据消耗更少的I/O，执行时间更短，但RBO 仍会选择扫描索引访问表作为最优的执行计划。

而第二种优化器为基于成本的优化器(Cost Based Optimizer，CBO)， 会根据SQL语句所访问的对象属性、索引特性、数据分布特性等多种数 据库统计信息，并根据这些影响因素的权重来估算这些执行计划可能消耗 的I/O、内存及CPU等资源成本，最终确定一个估算执行成本最小的执行 计划。如图3所示。由于CBO综合考虑了影响SQL语句执行的多种因素， 因此比基于规则的优化方法更容易产生更优的执行计划。

然而，CBO也存在一些不足。由于CBO预先设定的各种影响因素在 成本估算中的权重值并不能真实反应实际中各类资源的消耗比例，因此 CBO估算的执行成本只是假想的成本，同实际执行所消耗的成本很可能不 一致。而且在一些高访问负荷、数据环境异常复杂的业务系统中，数据统 计信息会产生急剧变化、数据环境的变化(如添加或删除索引等)可能导 致CBO收集的统计信息不准确，从而使其产生误判，偏向于选择实际消 耗资源较多的执行计划，即CBO认为的“最优”的执行计划并非现实中 “最优”的执行计划。执行计划的选择失误往往会导致数据库资源使用紧 张，业务系统的性能突然下降，对整个系统的稳定性造成严重的影响。

发明内容

(一)要解决的技术问题

为解决上述问题，本发明的主要目的在于提供一种基于实践检验的执 行计划优化的装置及方法，以降低复杂多变的数据环境中CBO的误判几 率，使得所生成的执行计划确实为现实中最优的执行计划，从而增强系统 的稳定性。

(二)技术方案

为达到上述目的，本发明提供了一种基于实践检验的执行计划优化的 装置，该装置包括相互连接的数据库装置1和基于实践检验的优化装置2， 其中：数据库装置1，用于实现关系型数据库的数据存储、结构化查询语 言SQL语句的优化，以及对执行计划的执行；基于实践检验的优化装置2， 用于接收数据库装置1输出的传统的基于成本的优化执行计划，并返回经 过实践检验的执行计划给数据库装置1。

上述方案中，所述数据库装置1向基于实践检验的优化装置2输出传 统的基于成本的优化执行计划，并执行基于实践检验的优化装置2返回的 经过实践检验的执行计划，包括SQL执行装置101、基于成本的执行计划 优化装置CBO 102和数据库103，其中：SQL执行装置101，用于对用户 提交的SQL语句进行语法解析，将解析后的查询块提交给CBO 102，并 执行基于实践检验的优化装置2返回的经过实践检验的执行计划；CBO 102，用于对解析后的查询块进行重新排序或改变其关联方式以产生更优 的执行计划，通过从数据库103中获取查询块所访问对象的统计信息来计 算查询结果的行数、比率，预估不同访问路径所消耗的CPU、I/O、内存 资源，进而产生多种执行计划，并选取执行成本最小的执行计划作为传统 的基于成本的优化执行计划，返回给基于实践检验的优化装置2；数据库 103，用于实现关系型数据库的数据存储和访问控制，并在SQL语句优化 过程中提供用于估算执行计划成本的各类统计信息。

上述方案中，所述基于实践检验的优化装置2包括执行计划优化装置 201、执行计划基准优化装置202、执行计划管理装置203和数据存储装置 204，其中：执行计划优化装置201，用于二次优化CBO 102生成的执行 计划，以提供给SQL执行装置101执行；执行计划基准优化装置202，用 于定时更新执行计划基准表中的数据，使其中的执行计划基准随系统环境 的变化而变化，保证执行计划基准的确为现实中最优的执行计划；执行计 划管理装置203，用于对数据存储装置204中的记录进行查询、添加、删 除或更新；数据存储装置204，用于执行计划相关数据的存储，包括执行 计划历史表2041和执行计划基准表2042。

为达到上述目的，本发明还提供了一种基于实践检验的执行计划优化 的方法，该方法包括：数据库装置向基于实践检验的优化装置输出传统的 基于成本的优化执行计划；基于实践检验的优化装置检索是否存在与该基 于成本的优化执行计划相关的历史执行计划或基准执行计划BMSP，对基 于成本的优化执行计划LCSP和多个基准执行计划BMSP进行有效性验 证、执行计划匹配以及执行成本比较，确定最终使用的经过实践检验的执 行计划，返回给数据库装置；以及数据库装置执行该经过实践检验的执行 计划。

(三)有益效果

从上述技术方案可以看出，本发明具有以下有益效果：

1、本发明提供的基于实践检验的执行计划优化的装置及方法，通过 将CBO所选择的执行计划的实际执行成本反馈给CBO，使其能够结合实 际执行的统计信息更准确的评估和选择最优执行计划。该方法作为基于成 本的优化方法的一种改进措施，可以在不过多增加系统开销的情况下，有 效降低复杂多变的数据环境中CBO的误判几率，使得所生成的执行计划 确实为现实中最优的执行计划，从而增强系统的稳定性。

2、本发明提供的基于实践检验的执行计划优化的装置及方法，在基 于成本的执行计划器的基础上，将实际检验的结果纳入执行计划选择的考 虑范围，相当于充分全面的考虑了各种外部因素对于执行计划成本的影 响，可以有效降低基于成本的优化方法单纯依赖于几个有限的考虑因素进 行预估和决策所造成的执行计划选择失误的几率。考虑因素的极大丰富却 不过多增加优化器的计算开销，更能保证系统性能的稳定性。

3、本发明提供的基于实践检验的执行计划优化的装置及方法，相当 于通过大量的实际数据给优化器提供了一个训练集，使优化器有一个学习 的过程，变得更加智能，使得它所选择的执行计划最大化的接近现实中最 优的执行计划。

4、本发明提供的基于实践检验的执行计划优化的装置及方法，对于 变化了的执行计划会进行评估，并且对比评估值同执行计划基准的实际执 行成本，只有在验证了新的执行计划优于执行计划基准时，新执行计划才 会被使用。这样可以有效避免数据环境剧变(如添加或删除索引、添加或 删除物化视图、数据库升级等)造成的执行计划巨变从而导致系统性能不 稳定的情况。

附图说明

图1是现有技术中SQL引擎和SQL优化器的示意图；

图2是现有技术中基于规则的优化器的示意图；

图3是现有技术中基于成本的优化器的示意图；

图4是本发明基于实践检验的执行计划优化的示意图；

图5是依照本发明实施例的基于实践检验的执行计划优化的装置的结 构示意图；

图6是依照本发明实施例的基于实践检验的执行计划优化的方法流程 图；

图7是依照本发明实施例的基于实践检验的执行计划优化方法在执行 SQL语句时的工作流程；

图8是依照本发明实施例的基于实践检验的执行计划优化方法在执行 计划基准优化时的工作流程；

图9是本发明基于实践检验的执行计划优化方法的一个具体应用场 景。

具体实施方式

为使本发明的目的、技术方案和优点更加清楚明白，以下结合具体实 施例，并参照附图，对本发明进一步详细说明。

本发明的技术思路是在基于成本的执行计划优化的基础上，引入执行 计划实际执行时消耗资源的统计信息，将实际检验的结果纳入执行计划评 估的考虑范围。当执行计划发生变化时，系统仍会优先选择经过实践检验 的最小实际成本的历史执行计划，但系统会对传统的成本优化生成的新执 行计划进行定期评估，当新计划的评估值小于旧的执行计划的实际消耗成 本，则会使用新计划。这种基于实践检验的执行计划优化方法使得所选择 的执行计划可以最大化的接近现实中最优的执行计划，并在多变的数据环 境中保证了执行计划的稳定性。如图4所示。

本发明为所有执行次数多于一次的SQL语句维护一个执行计划历史 列表，用于存放SQL语句的多版本的执行计划。为所有执行次数多于两 次的SQL语句维护一个执行计划基准列表，用于存放SQL语句性能较优 的执行计划，它是历史执行计划的一个子集。在这两个表中包含了用于重 新生成同样的执行计划的相关信息。当优化器接收到一个SQL优化请求 时，会首先以基于成本的优化方法生成最小成本(预估值)的执行计划， 再将其同基于执行计划基准表生成的执行计划进行匹配，如果匹配成功， 则说明基于最小成本的执行计划选择无误，则使用最小成本的执行计划。 反之则认为不能确认哪个执行计划更优，所以倾向于选择执行计划基准表 中经过实践检验的实际消耗成本最小的执行计划。

然而，执行计划基准表中最小实际成本的执行计划并不一定一直是最 优的执行计划。随着数据分布以及环境的变化，可能新版本的执行计划更 优于基于基准的执行计划。因此本发明会定时调度执行计划基准优化任 务，对执行计划历史列表中的每一个执行计划进行评估，比较历史执行计 划的评估值同基于基准执行计划的实际成本，如果前者更优，则将该条历 史执行计划的相关信息添加到执行计划基准表中。之所以将执行计划基准 的优化作为一个单独的任务定时调度，而非在执行SQL语句产生新执行 计划时，实时评估该执行计划的估算成本同执行计划基准的实际执行成本 的差异从而决定是否采用新执行计划，是因为实时评估及管理执行计划基 准本身会消耗一定的系统资源，导致SQL语句执行缓慢，而定时调度则 可以避免过多增加SQL语句的执行成本。

图5是依照本发明实施例的基于实践检验的执行计划优化的装置的结 构示意图，该装置包括数据库装置1和基于实践检验的优化装置2。数据 库装置1向基于实践检验的优化装置2输出传统的基于成本的优化执行计 划，基于实践检验的优化装置2向数据库装置1返回经过实践检验的执行 计划。

数据库装置1，用于实现关系型数据库的数据存储、SQL语句的优化 以及对执行计划的执行，包括：SQL执行装置101、基于成本的执行计划 优化装置(CBO)102和数据库103。其中，SQL执行装置101即SQL引 擎，用于对用户提交的SQL语句进行语法解析，将解析后的查询块提交 给CBO 102，并执行基于实践检验的优化装置2返回的经过实践检验的执 行计划。CBO 102对解析后的查询块进行重新排序或改变其关联方式以产 生更优的执行计划，并通过从数据库103中获取查询块所访问对象的统计 信息，来计算查询结果的行数、比率，预估不同访问路径(索引、全表扫 描等)所消耗的CPU、I/O、内存等系统资源，进而产生多种执行计划， 并选取执行成本最小的执行计划作为传统的基于成本的优化执行计划，返 回给基于实践检验的优化装置2。数据库103用于实现关系型数据库的数 据存储和访问控制，并在SQL语句优化过程中提供用于估算执行计划成 本的各类统计信息。

基于实践检验的优化装置2，用于接收CBO 102输出的传统的基于成 本的优化执行计划，并返回经过实践检验的执行计划给SQL执行装置101。 基于实践检验的优化装置2包括：执行计划优化装置201、执行计划基准 优化装置202、执行计划管理装置203和数据存储装置204。执行计划管 理装置203接收数据库装置1的CBO 102输出的传统的基于成本的优化执 行计划(LCSP)，从数据存储装置204中检索是否存在该语句的历史执行 计划或基准执行计划(BMSP)，并将该传统的基于成本的优化执行计划 (LCSP)与数据存储装置204中存储的多个基准执行计划(BMSP)输出 至执行计划优化装置201；执行计划优化装置201通过对LCSP和BMSP 进行有效性验证、执行计划匹配以及执行成本比较，确定最终使用的执行 计划，即经过实践检验的执行计划，并提交给数据库装置1中的SQL执 行装置101，同时获取SQL语句的实际执行成本如CPU、执行时间等，并 反馈至执行计划管理装置203，执行计划管理装置203将该SQL语句的实 际执行成本记录或更新至执行计划基准表。执行计划基准优化装置202是 个较为独立的装置，负责定期调度优化程序以进行历史执行计划和基准执 行计划的比较，若产生了优于原基准的新执行计划，则反馈至执行计划管 理装置203，执行计划管理装置203将该优于原基准的新执行计划写入数 据存储装置204的执行计划历史表中。

执行计划优化装置201负责二次优化CBO生成的执行计划以提供给 SQL执行装置101执行，具有实时特性，它包含执行计划比较装置2011、 执行计划匹配装置2012和执行计划验证装置2013。执行计划验证装置 2013接收到执行计划管理装置203发送的基于成本的优化执行计划 (LCSP)和多个基准执行计划(BMSP)之后，验证其对当前系统环境的 有效性，并将有效的基准执行计划输出至执行计划匹配装置2012；执行计 划匹配装置2012将LCSP同多个BMSP进行一一匹配，若匹配成功，则 直接将该LCSP输出至SQL执行装置101，若无法匹配，则将BMSP输出 至执行计划比较装置2011；执行计划比较装置2011根据实际执行成本对 多个BMSP进行比较，选择实际上成本最小的执行计划输出至SQL执行 装置101。

由于系统环境可能发生变化(比如索引被删除)，则已存在的基于执 行计划基准的执行计划(如索引扫描)可能对当前系统无效，所以需要执 行计划验证装置2013将有效的基于基准的执行计划提供给执行计划匹配 装置2012，即执行计划验证装置2013负责验证基于执行计划基准所生成 的执行计划的有效性。执行计划匹配装置2012负责将基于执行计划基准 的执行计划同最小估算成本的执行计划进行匹配。执行计划比较装置2011 负责比较两个执行计划的执行成本，返回性能更优的执行计划。比如对比 基于基准执行计划的实际执行成本，并将其中实际成本最小的执行计划返 回给SQL执行装置101执行。

执行计划基准优化装置202，负责定时更新执行计划基准表中的数据， 使其中的执行计划基准随系统环境的变化而变化，保证执行计划基准的确 为现实中最优的执行计划。它包括任务调度装置2022和执行计划评估装 置2021，具有非实时性。任务调度装置2022中包含定时器，当用户配置 完优化任务的执行起止时间及执行频度之后，任务调度装置2022会定期 启动执行计划评估装置2021；执行计划评估装置2021启动后，从执行计 划管理装置203中获取执行计划历史表和执行计划基准表，预估执行计划 历史表中的执行计划的执行成本，并使之与最小的基准执行计划进行比 较，若小于则将执行计划历史表中的执行计划输出至执行计划管理装置 203，执行计划管理装置203将该执行计划写入执行计划基准表。

执行计划评估装置2021，负责获取执行计划历史表2041的记录并评 估其可能的执行成本，并将评估成本小于执行计划基准的实际成本的执行 计划提供给执行计划管理装置203以更新执行计划基准表。

任务调度装置2022，负责定时调度执行计划基准优化任务。

执行计划管理装置203，负责执行计划历史表2041和执行计划基准表 2042的管理，如对应记录的查询、添加、删除、更新等。

数据存储装置204，负责执行计划相关数据的存储，它包括执行计划 历史表2041和执行计划基准表2042。

执行计划历史表2041，负责存储执行次数多于一次的执行计划的相关 信息，以下为优选的数据结构表：

执行计划基准表2042，负责存储实际执行成本较少的SQL语句执行 计划的相关信息，以下为优选的数据结构表：

基于上述图5所示的依照本发明实施例的基于实践检验的执行计划优 化的装置，图6示出了依照本发明实施例的基于实践检验的执行计划优化 的方法流程图，该方法包括以下步骤：

步骤1：数据库装置向基于实践检验的优化装置输出传统的基于成本 的优化执行计划；

步骤2：基于实践检验的优化装置检索是否存在与该基于成本的优化 执行计划相关的历史执行计划或基准执行计划BMSP，对基于成本的优化 执行计划LCSP和多个基准执行计划BMSP进行有效性验证、执行计划匹 配以及执行成本比较，确定最终使用的经过实践检验的执行计划，返回给 数据库装置；以及

步骤3：数据库装置执行该经过实践检验的执行计划。

其中，步骤1中所述数据库装置向基于实践检验的优化装置输出传统 的基于成本的优化执行计划之前，还包括：数据库装置中的SQL执行装 置接收用户提交的SQL语句，数据库装置中的CBO根据该SQL语句生成 传统的基于成本的优化执行计划。

步骤2中所述基于实践检验的优化装置检索是否存在与该基于成本的 优化执行计划相关的历史执行计划或基准执行计划BMSP，对基于成本的 优化执行计划LCSP和多个基准执行计划BMSP进行有效性验证、执行计 划匹配以及执行成本比较，确定最终使用的经过实践检验的执行计划，包 括：基于实践检验的优化装置中的执行计划管理装置检索数据存储装置中 否存在该SQL语句的基准执行计划，若存在，则验证该基准执行计划的 有效性，然后将基于成本的优化执行计划与有效性的基准执行计划进行匹 配，若匹配成功，则将该基于成本的优化执行计划确定为经过实践检验的 执行计划；若匹配失败，则比较所有基准执行计划的实际执行成本，选择 实际成本最小的基准执行计划为经过实践检验的执行计划。

步骤2中所述确定最终使用的经过实践检验的执行计划之后，还包括： 基于实践检验的优化装置中的执行计划管理装置将确定最终使用的经过 实践检验的执行计划更新到执行计划基准表中。

图7示出了依照本发明实施例的基于实践检验的执行计划优化方法在 执行SQL语句时的工作流程，其具体步骤如下：

步骤101：SQL执行装置101接受用户提交的SQL语句。

步骤102：CBO102生成最小成本执行计划(Lowest Cost SQL PLAN， 简称LCSP)。

步骤103：执行计划管理装置203检索是否存在该语句的执行计划基 准。若存在，则进入步骤104；否则进入步骤111。

步骤104：执行计划验证装置2013验证基于执行计划基准的执行计划 (Benchmark Based SQL PLAN，简称BMSP)的有效性。

步骤105：执行计划匹配装置2012将LCSP同所有有效的BMSP进行 匹配。若匹配失败，则进入步骤106；否则进入步骤109。

步骤106：执行计划比较装置2011比较所有BMSP的实际执行成本。

步骤107：SQL执行装置101执行实际成本最小的BMSP。

步骤108：执行计划管理装置203将所执行的BMSP的实际成本更新 到执行计划基准表2042中。进入步骤113。

步骤109：SQL执行装置101执行LCSP。

步骤110：执行计划管理装置203将所执行的LCSP的相关信息写入 执行计划基准表2042中。进入步骤113。

步骤111：执行计划管理装置203检索该语句的执行计划历史记录。 若存在，则进入步骤109；否则进入步骤112。

步骤112：执行计划管理装置203将LCSP的相关信息写入执行计划 历史表2041，进入步骤109。

步骤113：流程结束。

图8示出了依照本发明实施例的基于实践检验的执行计划优化方法在 执行计划基准优化时的工作流程，其具体步骤如下：

步骤201：任务调度装置2022定时调度执行计划优化任务。

步骤202：执行计划评估装置2021获取执行计划历史表2041中的记 录，并依次评估其中每一个执行计划的成本。

步骤203：执行计划评估装置2021比较历史执行计划的评估值和执行 计划基准的实际成本。若历史执行计划评估值小于最小的执行计划基准的 实际成本，则进入步骤204；否则进入步骤205。

步骤204：执行计划管理装置203将该历史执行计划写入执行计划基 准表2042中。

步骤205：流程结束。

下面以图9所示的一个示例来说明本方法的应用过程。图9是本发明 基于实践检验的执行计划优化方法的一个具体应用场景，在数据库所在的 服务器上部署基于实践检验的优化装置，在数据库中创建该优化装置所需 的数据存储表，即执行计划历史表和执行计划基准表。

数据库装置接收用户终端提交的SQL语句，执行计划管理装置获取 数据库装置输出的SQL语句执行计划，并检索数据存储装置的执行计划 基准，提供给基于实践检验的执行计划优化装置。基于实践检验的执行计 划优化装置实时地对SQL语句进行优化，并最终提交给数据库装置执行。

执行计划基准优化装置独立于基于实践检验的执行计划优化装置运 行，它定时调度执行计划基准优化任务，随数据库系统环境的变化而不断 更新执行计划基准表，从而为实时的执行计划优化任务提供决策支持，使 之选择的执行计划最大化接近现实中最优的执行计划。

以上所述的具体实施例，对本发明的目的、技术方案和有益效果进行 了进一步详细说明，所应理解的是，以上所述仅为本发明的具体实施例而 已，并不用于限制本发明，凡在本发明的精神和原则之内，所做的任何修 改、等同替换、改进等，均应包含在本发明的保护范围之内。