CN100403308C - 基于sql负载挖掘的物理数据库自动设计方法 - Google Patents

Abstract

本发明公开了一种基于SQL负载挖掘的物理数据库自动设计方法，由负载信息、数据库模式信息和统计信息挖掘流程和候选物理特性产生和最优物理特性组合搜索流程两个流程共同完成。本发明技术方案中，将框架和算法与物理特性库完全独立，对于不同的关系数据库产品，为其引入自动设计，或为已有的关系数据库产品增加物理特性和优化方案，都不需要改动整体框架和内部算法，只需要对物理特性库进行修改就可以达到目的。

Description

基于SQL负载挖掘的物理数据库自动设计方法

技术领域

本发明涉及一种适用于通用的关系数据库的物理数据库(PDB)自动设计方法，尤其涉及一种基于SQL负载挖掘的通用关系数据库中的物理数据库自动设计方法，属于数据库技术领域。

背景技术

数据库技术中，关系模型数据库具有数据结构简单灵活、易学易懂且具有雄厚的数学基础等特点，现在已成为数据库的标准。目前广泛使用的数据库软件都是基于关系模型的关系数据库管理***。

在数据库设计工作中，逻辑数据库设计和物理数据库设计是核心的环节。逻辑数据库设计是根据用户要求和特定数据库管理***的具体特点，以数据库设计理论为依据，设计数据库的全局逻辑结构和每个用户的局部逻辑结构。物理数据库设计是在逻辑结构确定之后，设计数据库的存储结构及其他实现细节。

实践中，经常需要熟练的数据库管理员对各种关系数据库应用***进行管理和调优。一方面，雇用熟练数据库管理员所耗费的人力成本对***整体拥有成本的影响不可小视；另一方面，即使是一个很熟练的数据库管理员对数据库进行日常管理和调优也需要大量的时间和精力，且无法跟上数据访问的变化脚步。

物理数据库设计是关系数据库性能调优的一个非常重要的方面，它涉及各种关系数据库物理特性的设计，包括索引设计、物化试图(materializedview)设计、表分区设计等等。现有的关系数据库产品中Microsoft SQLServer，IBM DB2和Oracle都提供了一定的物理数据库自动设计功能，然而这些数据库产品中的自动设计功能都紧密地与本数据库提供的特殊功能进行了绑定，因此无法适用到其他的关系数据库中，包括应用非常广泛的MySQL，PostgreSQL，以及国产的Oscar数据库中。

从实际需要上看，若由关系数据库本身代替数据库管理员对物理数据库进行自动设计，可以降低对数据库管理员的要求，减少人力成本；同时，关系数据库本身也可以根据自身的统计信息，提高对数据访问变化的响应能力，减少日常管理和调试所需的时间。但是，目前这方面的技术仍然在探索之中。

发明内容

本发明的目的在于针对上述的现实需求，提供一种基于SQL负载挖掘技术，适用于通用的关系数据库的物理数据库自动设计方法。

为实现上述的发明目的，本发明采用下述的技术方案：

一种基于SQL负载挖掘的物理数据库自动设计方法，其特征在于包括如下步骤：

步骤1)负载信息、数据库模式信息和统计信息挖掘：

10)收集负载的SQL语句及其发生频率；

11)根据收集的所述SQL语句进行分析，并将语法模式树完全一致的SQL语句进行归类；

12)分析所述SQL语句可能用到的关系属性，获取并保存关系属性数据的结构信息和统计信息；

步骤2)候选物理特性产生和最优物理特性组合搜索：

20)针对不同关系数据库管理***特性进行物理特性库构造；

21)根据每一类所述SQL语句以及物理特性库产生候选设计集合；

22)根据单条所述SQL语句的代价估计值以及所述SQL语句出现频率信息计算负载总代价；

23)根据候选设计集合和负载总代价搜索最优设计组合。

其中，所述步骤10)对负载SQL及其发生频率的收集方式为：

对于开放源代码或开放接口的关系数据库***，简单修改源代码或调用其接口实现；

对于其他商用关系数据库***，采用代理方式截获数据库服务器收到的通讯包，并从中提取所需的SQL语句。

所述步骤11)中对收集的所述SQL语句进行分析和归类的方法为：

对各所述SQL语句分别进行语法分析，得到语法分析树；

对语法分析树进行等价修剪、交换和合并，得到语法模式树；

将语法模式树完全相同的SQL语句归类为同一类SQL语句。

所述步骤20)中，构造的物理特性库为语法模式树和物理设计组成的二元组的集合；

所述语法模式树为对SQL语句进行分析提炼得到的、可以表示该所述SQL语句的结构和意义的树型结构，所述物理设计为对关系数据库物理设计方法、方法参数的简单平面表示。

本发明所使用的技术方案将框架和算法与物理特性库完全独立，对于不同的关系数据库产品，为其引入自动设计，或为已有的关系数据库产品增加物理特性和优化方案，都不需要改动整体框架和内部算法，只需要对物理特性库进行修改就可以达到目的。

附图说明

下面结合附图和具体实施方式对本发明作进一步的说明。

图1是本发明所述的关系数据库物理数据库自动设计的总体流程图；

图2是本发明所述的关系数据库***中负载和统计信息挖掘工作的流程图；

图3是本发明所述关系数据库中物理特性产生以及最优物理特性组合搜索工作的流程图。

具体实施方式

如图1所示，本关系数据库物理数据库自动设计方法由负载信息、数据库模式信息和统计信息挖掘流程和候选物理特性产生和最优物理特性组合搜索流程两个流程共同完成，图2和图3分别说明这两个流程的具体细节。

如图2所示，本发明关系数据库***负载和统计信息挖掘方法包括：

步骤10)收集单位时间内各种查询(SELECT)、更新(UPDATE)、***(INSERT)、删除(DELETE)SQL语句的个数及执行时间等相关统计信息；此类收集通过两类方式来完成，对于无法获得源代码的关系数据库***，采用代理的方式，截取并提取客户端与数据库服务器之间的通讯包中的SQL语句信息进行收集；对于可以获得源代码的或者开发相关接口的关系数据库***，直接简单的修改源代码或调用接口进行实现。收集的结果为三元组(id，sql，count)的集合W，其中id为编号，sql为SQL语句，count为出现次数。

步骤11)对于W中的每一个SQL语句，对其进行词法、语法分析，将得到的SQL语法分析树进行提炼获得语法模式树，并将语法模式树完全一致的语句归类为同一类SQL语句，依次完成对W中的SQL语句进行归类。如SQL语句1：select NAME，AGE from FACULTY where NAME＝‘张三’以及SQL语句2：select AGE，NAME from FACULTY where‘李四’＝NAME，他们的语法模式树均可表示为(S(AGE，NAME)，F(FACULTY)，W(NAME＝const))，可被归类为同一类SQL语句；而SQL语句3：SELECT NAME，AGE FROM FACULTY WHERE AGE＜18，其语法模式树可表示为(S(AGE，NAME)，F(FACULTY)，W(AGE＜const))，则不应被视为与SQL语句1、2同类。归类的结果为四元组s(id，sql，patt，freq)的集合S，其中id为编号，sql为范例SQL语句，patt为语法模式树，freq为对应语法模式出现的频率。

步骤12)对于S中的每一类查询，分析其可能用到的所有关系属性(表、列等)，并获取这些数据的一些结构信息和统计信息，包括表的总元组数，表中每个元组的平均长度，表中每个列的类型，每个列的平均长度等。步骤11)得到的负载挖掘三元组集合S和本步骤得到的相关数据统计信息保存下来供步骤2)读取使用。最优物理数据库设计特性的搜索过程可能非常耗费***资源，保存所有需要的统计信息可使最优分析过程在其他机器上读取进行，独立于数据库***实例所在机器，从而减少对正常数据库***运行的影响。

图3所示为本发明所提供的关系数据库物理关系特性产生以及最优物理特性搜索的流程图：

步骤20)由于本发明的目的是提供一个通用的关系数据库物理数据库自动设计方法，而不同的数据库具备不同的物理特性，所以，需要为不同的后端关系数据库定义一系列可行的物理设计特性，这些特性的集合被称为物理特性库。物理特性库F由一系列二元组(p，d)构成，其中p表示语法模式树，而d表示物理设计，二元组f(p，d)的意义为，针对某种语法模式树，为该查询产生物理设计d，可能提高该语法模式对应的查询的性能。

以下举几个例子说明物理特性数据库的构成和使用方式：

√针对关系数据库的通用索引设计特性，得到如下的物理特

性：f1(p(S(*)，F(T)，W(A＝const))，d(BT(T(A))))，意义为：对于类似select*from T where A＝1的查询，产生物理设计在T表A列上建立B+树索引。

√针对关系数据库的通用索引设计特性，得到如下的物理特性：f2(p(S(A)，F(T)，O(A))，d(BT(T(A))))，意义为：对于类似select Afrom T order by A[desc/asc]的查询，产生物理设计在T表A列上建立B+树索引。

√针对OSCAR数据库的垂直分区设计特性，得到如下的物理特性：f3(p(S(A，B)，F(T)，W(A＝const))，d(VP(T(A，B)))，意义为：对于类似select A，B from T where A＝1的查询，产生物理设计将T表A，B列单独垂直分区。

√针对OSCAR数据库的垂直分区设计特性，得到如下的物理特性：f4(p(U(A＝const，B＝C+const，C＝C/const)，F(T)，W(A＝B，B＞C))，d(VP(T(A，B，C)))，意义为：对于类似update T set A＝1，B＝C+2，C＝C/3where A＝B and B＞C的查询，产生物理设计将T表A，B，C列单独垂直分区。

步骤21)读取步骤11)得到的负载挖掘三元组集合S中的每一个语法模式s.patt，并与步骤20)构建的物理特性库中的每一个物理特性二元组中的语法模式f.p进行比较，对于每一个包含物理特性中语法模式的语法模式s.patt，为其产生对应的物理设计f.d。所有得到的物理设计d构成的集合D为所有可能提高性能的物理设计方法的集合，即候选设计集合。

步骤22)获得负载的总代价估计函数。负载总代价即指负载中的每一条查询的代价使用其单位时间内出现的频率进行加权求和得到的总代价：Call＝∑s∈S(Cs.sql*s.freq)，其中Cs.sql代表单条查询语句的代价估计值。单条语句的代价估计可通过两类方法完成，一类直接依赖于数据库本身提供的代价估计工具获得(如MySQL，PostgreSQL，Oscar等数据库提供的EXPLAIN语句)；另一类通过统一的外部性能评价模型完成。

步骤23)根据步骤21)输出的集合D中的所有候选物理设计，搜索最优组合 $\mathrm{Dopt}\⋐D$ ，使得工作负载的总代价Call取得最小值。搜索过程按以下贪婪算法进行：

√步骤23.1)初始化Dopt为空，计算当前总代价Call，并用其初始化Cmin，用于记录当前得最低总代价；

√步骤23.2)从D中取得下一个可能改善性能得物理设计d，若已经取完，到步骤23.4)

√步骤23.3)根据d尝试应用推荐的物理设计，重新计算Call，

若Call＜Cmin，说明该物理设计可以改善性能，Dopt＝Dopt∪d，并使得Cmin＝Call，并保持本步应用的物理设计；否则，该推荐物理设计不能改善性能，删除本步应用的物理设计。转到步骤23.2)；

√步骤23.4)搜索推荐过程结束，输出Dopt作为最优物理设计组合。

下面举例详细说明本发明所述的通用关系数据库物理数据库自动设计方法的技术方案。

假设某应用的关系数据库中存在，以下两张表：

create table TEST_1(COL11 int，COL12 char(500))；1000行

create table TEST_2(COL21 int，COL22 int)；100000行

并假设外部工作负载为：

SQL1：select COL 11，COL 12 from TEST_1 where COL 11＝x1 orderby COL11；

每分钟80条，x1为[1..1000]取值的整数

SQL2：update TEST_1 set COL 11＝COL 11-1 where COL 11＜10 andCOL12＝‘’；

每分钟10条，x2为[1..1000]取值的整数

SQL2：select*from TEST_2 where COL21＝x3and COL22＝COL21；

每分钟10条，x3为[1..100000]取值的整数

下面开始使用本发明，分别对使用MySQL数据库和本申请人所开发的OSCAR数据库中的该应用的物理数据库进行自动设计。

首先，根据步骤10)进行负载信息收集，持续时间1分钟，获得的工作负载集合可能为：

{{1，select COL11，COL12 from TEST_1 where COL11＝1 order byCOL11，1}，{2，select COL11，COL12 from TEST_1 where COL11＝2order by COL11，1}，                …

{80，select COL11，COL12 from TEST_1 where COL11＝80 orderby COL11，1}，

{81，update TEST_1 set COL11＝COL11-1 where COL11＜10 andCOL12＝‘’，10}，

{82，select*from TEST_2 where COL21＝1 and COL22＝COL21，4}，

{83，select*from TEST_2 where COL21＝100 and COL22＝COL21，4}，

{84，select*from TEST_2 where COL21＝10000 and COL22＝COL21，2}

}

然后，根据步骤11)归类后得到的工作负载归类集合为：

{{1，select COL11，COL12 from TEST_1 where COL11＝1 order byCOL11，

    (S(COL11，COL12)，F(TEST_1)，W(COL11＝const)，OB(COL11)，

        READ(COL11，COL12)，WRITE(NULL))，0.80}，

{2，update TEST_1 set COL11＝COL11-1 where COL11＜10 andCOL12＝‘’，

    (U(COL11＝func(COL11))，F(TEST_1)，W(COL11＜const，COL12＝const)，

        READ(COL11，COL12)，WRITE(COL11))，0.10}，

{3，select*from TEST_2 where COL21＝1 and COL22＝COL21，

    (S(*)F(TEST_2)W(COL21＝const，COL22＝func(COL21))，

        READ(COL21，COL22)，WRITE(NULL))，0.10}

}

接着，将步骤11)得到的负载挖掘信息和从数据库中获取的模式信息分析并保存。

在步骤20)，构造物理特性库时，因为MySQL不支持OSCAR所支持的垂直分区物理设计方法，而OSCAR不支持MySQL所支持的直接从索引获取数据，所以构造的物理特性库不尽相同，其中，与该应用相关的公共的物理特性库包括：

{{S(A，B)，F(T)，W(A＝const)，O(A)}，BT(T(A))}

{{U(A＝func(A))，F(T)，W(A＜const，B＝const)，O(A)}，BT(T(A))}

{{U(A＝func(A))，F(T)，W(A＜const，B＝const)，O(A)}，BT(T(A，B))}

{{S(*)，F(T)，W(A＝const，B＝func(A))，BT(T(A))}

{{S(*)，F(T)，W(A＝const，B＝func(A))，BT(T(A，B))}

MySQL附加的物理特性库包括：

{{F(T)，READ(A)}，BT(T(A))}

{{F(T)，READ(A，B)}，BT(T(A，B))}

OSCAR附加的物理特性库包括：

{{F(T)，READ(A)}，VP(T(A))}

{{F(T)，READ(A，B)}，VP(T(A，B))}

{{F(T)，READ(A，B)，WRITE(A)}，VP(T(A))}

在步骤21)，用步骤11)输出的工作负载归类集合中的每一类查询的语法模式与物理特性库中的每一个物理特性的语法模式进行匹配，得到候选特性如下：

对于类别1：公共候选：BT(TEST_1(COL11))；

MySQL附加候选：BT(TEST_1(COL11，COL12))

OSCAR附加候选：VP(TEST_1(COL11，COL12))

对于类别2：公共候选：BT(TEST_1(COL11))，BT(TEST_1(COL11，COL12))

         MySQL附加候选：无

         OSCAR附加候选：VP(TEST_1(COL11，COL12))，VP(TEST_1(COL11))

对于类别3：公共候选：BT(TEST_2(COL21))，BT(TEST_2(COL21，COL22))

         MySQL附加候选：无

         OSCAR附加候选：VP(TEST_1(COL11，COL12))

综合并去除一些没有意义的候选(如VP(TEST_1(COL11，COL12))对于一个表的所有列进行垂直分区是无意义的)，得到候选特性集合D：

对于MySQL，BT(TEST_1(COL11))，BT(TEST_1(COL11，COL12))，BT(TEST_2(COL21))，BT(TEST_2(COL21，COL22))；

对于OSCAR，BT(TEST_1(COL11))，BT(TEST_1(COL11，COL12))，VP(TEST_1(COL11))，BT(TEST_2(COL21))，BT(TEST_2(COL21，COL22)).

在步骤22)和步骤23)，使用OSCAR/MySQL本身自带的代价估计工具EXPLAIN计算代价，使用下面的公式计算总代价：

Call＝Cs1.sql*0.80+Cs2.sql*0.10+Cs3.sql*0.10

并搜索得到总代价最小的解。

本应用最终得到的最优物理设计分别是：

对于MySQL，BT(TEST_1(COL11))，BT(TEST_2(COL21，COL22))

对于OSCAR，BT(TEST_1(COL11))，VP(TEST_1(COL11))，BT(TEST_2(COL21，COL22))

以上对本发明的具体实施方式进行了详细的解说。对于本技术领域的一般技术人员来说，在不背离本发明所述方法的精神和权利要求范围的情况下对它进行的各种显而易见的改变都在本发明的保护范围之内。

Claims (4)

1.一种基于SQL负载挖掘的物理数据库自动设计方法，其特征在于包括如下步骤：

步骤1)负载信息、数据库模式信息和统计信息挖掘：

10)收集负载的SQL语句及其发生频率；

11)根据收集的所述SQL语句进行分析，并将语法模式树完全一致的SQL语句进行归类；

12)分析所述SQL语句可能用到的关系属性，获取并保存关系属性数据的结构信息和统计信息；

步骤2)候选物理特性产生和最优物理特性组合搜索：

20)针对不同关系数据库管理***特性进行物理特性库构造；

21)根据每一类所述SQL语句以及物理特性库产生候选设计集合；

22)根据单条所述SQL语句的代价估计值以及所述SQL语句出现频率信息计算负载总代价；

23)根据候选设计集合和负载总代价搜索最优设计组合。

2.如权利要求1所述的基于SQL负载挖掘的物理数据库自动设计方法，其特征在于：

所述步骤10)对负载SQL及其发生频率的收集方式为：

对于开放源代码或开放接口的关系数据库***，简单修改源代码或调用其接口实现；

对于其他商用关系数据库***，采用代理方式截获数据库服务器收到的通讯包，并从中提取所需的SQL语句。

3.如权利要求1所述的基于SQL负载挖掘的物理数据库自动设计方法，其特征在于：

所述步骤11)中对收集的所述SQL语句进行分析和归类的方法为：

对各所述SQL语句分别进行语法分析，得到语法分析树；

对语法分析树进行等价修剪、交换和合并，得到语法模式树；

将语法模式树完全相同的SQL语句归类为同一类SQL语句。

4.如权利要求1所述的基于SQL负载挖掘的物理数据库自动设计方法，其特征在于：

所述步骤20)中，构造的物理特性库为语法模式树和物理设计组成的二元组的集合；

所述语法模式树为对SQL语句进行分析提炼得到的、可以表示该所述SQL语句的结构和意义的树型结构，所述物理设计为对关系数据库物理设计方法、方法参数的简单平面表示。

Images