CN102541884B

CN102541884B - 数据库优化方法和装置

Info

Publication number: CN102541884B
Application number: CN201010595187.6A
Authority: CN
Inventors: 林昊; 张黔; 刘阳; 宋玉军; 杜鹏; 何昱吾; 陆恩波
Original assignee: China Mobile Group Guizhou Co Ltd
Current assignee: China Mobile Group Guizhou Co Ltd
Priority date: 2010-12-10
Filing date: 2010-12-10
Publication date: 2014-07-02
Anticipated expiration: 2030-12-10
Also published as: CN102541884A

Abstract

本发明提供了数据库优化方法和装置，其中，该方法包括：A，捕获原始SQL；B，针对捕获的每一原始SQL，对该原始SQL进行标准化处理，得到对应该原始SQL的标准SQL，并生成该标准SQL的标识；C，根据各个标准SQL的标识定位出需要优化的关键SQL。采用本发明，能够定位出关键SQL，利于数据库优化。

Description

数据库优化方法和装置

技术领域

本发明涉及移动通信技术，特别涉及数据库优化方法和装置。

背景技术

在数据库应用中，每天都有大量的SQL访问数据库。目前，数据库比如Informix数据库都是通过INFORMIX数据库接口访问INFORMIX数据库的***表来查询当前时间点正在执行的SQL的执行情况(其包含该SQL当前耗用的资源等)。

INFORMIX数据库接口只能提供一个时间点的SQL执行状况，并无数据采集汇总分析的功能，因此，其不能提供一个时间段内SQL的执行情况，缺乏统计及过滤功能。如此，导致数据库管理人员无法定位关键SQL，进而影响数据库的优化。其中，关键SQL为执行频率和执行成本较高的SQL，其对数据库的资源耗用较大，定位出关键SQL，对数据库优化有事半功倍的作用。

发明内容

本发明提供了数据库优化的方法和装置，以定位出关键SQL，利于数据库优化。

本发明提供的技术方案包括：

一种数据库优化方法，包括：

A，捕获原始SQL；

B，针对捕获的每一原始SQL，对该原始SQL进行标准化处理，得到对应该原始SQL的标准SQL，并生成该标准SQL的标识；

C，根据各个标准SQL的标识定位出需要优化的关键SQL。

一种数据库优化装置，包括：

捕获单元，用于捕获原始SQL；

处理单元，用于针对所述捕获单元捕获的每一原始SQL，对该原始SQL进行标准化处理，得到对应该原始SQL的标准SQL，并生成该标准SQL的标识；

定位单元，用于根据所述处理单元生成的各个标准SQL的标识定位出需要优化的关键SQL。

由以上技术方案可以看出，本发明中，通过对捕获的原始SQL进行标准化处理，得到对应该原始SQL的标准SQL，并生成该标准SQL的标识；之后根据各个标准SQL的标识定位出需要优化的关键SQL。即实现了定位出关键SQL，通过分析关键SQL优化数据库的目的。

进一步地，本发明有针对性地对关键SQL进行优化，还能实现对数据库的最少修改，达到优化效果最大的目的，也大大减少风险性。

附图说明

图1为本发明实施例提供的基本流程图；

图2为本发明实施例提供的步骤102的实现流程图；

图3为本发明实施例提供的步骤104的实现流程图；

图4为本发明实施例提供的装置结构图。

具体实施方式

为了使本发明的目的、技术方案和优点更加清楚，下面结合附图和具体实施例对本发明进行详细描述。

关键SQL需要耗用较大的数据库资源，因此，找出关键SQL并对其进行优化，是实现数据库优化的最佳方式。本发明提供的方法能够定位出关键SQL，具体如图1所示。

参见图1，图1为本发明实施例提供的基本流程图。如图1所示，该流程可包括以下步骤：

步骤101，捕获原始SQL

具体实现时，本步骤101以设定捕获粒度为单位从数据库的共享内存池中捕获原始SQL。其中，所述捕获粒度按照对***负荷影响最小、且保证捕获的原始SQL具有代表性的原则设定，本实施例通常设置所述捕获粒度为5至15分钟。

步骤102，针对捕获的每一原始SQL，对该原始SQL进行标准化处理，得到对应该原始SQL的标准SQL。

本步骤102中，对原始SQL进行标准化处理可为：提取原始SQL的执行路线，将该执行路线抽象过滤为标准SQL，其具体可参见图2所示的流程。

步骤103，生成标准SQL的标识。

步骤104，根据各个标准SQL的标识定位出需要优化的关键SQL。

本步骤104具体可参见图3所示的流程。

至此，完成图1所示的流程。从图1所示的流程可以看出，本发明有针对性地对关键SQL进行优化，还能实现对数据库的最少修改，达到优化效果最大的目的，也大大减少风险性。

需要说明的是，以上步骤101至步骤104由预先在数据库所在服务器上安装的SQL采集引擎实现。

在步骤102中，对原始SQL进行标准化处理实质上是：从应用角度而言，其是将该原始SQL还原到业务层面，以实现属于同业务的不同SQL不被分散来定位关键SQL；从数据库角度或者技术角度而言，由于数据库对具有相同执行路线的不同SQL执行的操作是完全一样的，因此，该标准化处理为：将原始SQL抽象到执行路线，以实现属于同一数据库操作的不同SQL不被分散来定位关键SQL。以查询用户基本信息的数据库操作为例，如果从具体的SQL进行分析，因查询带有用户名等参数，几乎每一个查询的SQL语句都不相同，比如，其中一个SQL为select*from taw_user where userid＝“yjy2”，另一个SQL为select*from taw_user where userid＝“zhouli”，但实际上，查询用户基本信息业务本身是一个操作，在数据库查询中的执行路线是相同的，因此，可将这两个SQL抽象到同一执行路线。

在步骤103中，步骤103中的生成操作具体可为：利用设定的哈希(HASH)码生成函数，生成该标准SQL的哈希码，即标准SQL的标识为哈希码。其中，哈希码是一个十进制数字，用于唯一标识标准SQL。

在步骤104中，步骤104的定位操作具体可参见图3所示的流程。根据步骤104的描述可以知道，关键SQL是根据标准SQL的哈希码定位的，而不是将标准SQL本身的字符串进行比较确定，这一方面可以提供定位效率，另一方面减少对数据库***的负荷。

另外，根据步骤104的描述可以知道，定位关键SQL是基于标准SQL，而不是直接采用原始SQL。通过对步骤102的分析可以知道，标准SQL是对应执行路线，基于此，可以看出步骤104是从执行路线的角度定位关键SQL，这能够保证定位出的关键SQL更加准确。

下面通过图2对步骤102进行详细描述：

参见图2，图2为本发明实施例提供的步骤102的实现流程图。如图2所示，针对步骤101捕获的每一原始SQL，执行如下处理：

步骤201，按照顺序遍历原始SQL中的字符，将遍历到的字符作为当前字符。

本步骤201中的字符为单词、符号或者数组。

步骤202，判断所述当前字符是否为最后一个未被遍历的字符，如果否，执行步骤203，如果是，结束当前处理流程。

若当前字符为最后一个未被遍历的字符，则结束当前处理流程，将处理后的SQL作为标准SQL。

步骤203，判断当前字符是否在设定的运算符中，如果是，执行步骤204；否则，执行步骤205。

本步骤203中的运算符至少包含以下字符中的至少一个：等于(＝)、大于(＞)、小于(＜)、尖括号(<>)、大于等于(＞＝)，小于等于(＜＝)、like。

步骤204，确定当前字符的下一个字符为执行参数，并将其替换为标准字符，将替换后的标准字符的下一个字符作为当前字符，返回步骤202。

步骤205，判断当前字符是否为between，如果否，将当前字符的下一个字符作为当前字符，返回步骤202；如果是，执行步骤206。

步骤206，确定当前字符的下一个字符为执行参数，将其替换为标准字符。

步骤207，如果步骤206替换后的标准字符的下一个字符为and，则确定该and的下一个字符为执行参数，将其替换为标准字符，并将该替换后的标准字符的下一个字符确定为当前字符，返回执行步骤202。

至此，完成图2所示的流程。

从图2所示的流程可以看出，图2主要是过滤掉原始SQL中执行参数的影响，以便将属于同一数据库操作的不同SQL处理为同一标准SQL。

在上述图2所示的流程中，标准字符具体实现时可为“？”，或者其他字符，本发明实施例并不具体限定。

下面以标准字符为“？”为例，对图2所示的流程进行具体分析。

假如捕获到的SQL至少包含以下两个SQL，即SQL1和SQL2，在业务层面上，其属于同一个数据库操作，即用于查询并统计某一部门未处理任务工单数量的操作，但对于数据库来说，由于它们包含了不同的参数，因此，是两条不同的SQL：

SQL 1：

SQL2：

对上述两个原始SQL分别基于图2所示的处理流程，可以得到同一标准SQL，具体为：

也就是说，通过图2，能够将属于同一数据库操作的不同SQL处理为同一标准SQL，这能保证步骤104定位的关键SQL更加准确，具体参见图3所示。

以上对图2所示方法的应用进行了描述，下面通过图3对步骤104的具体流程进行描述。

需要说明的是，为便于定位关键SQL，步骤103在针对每一标准SQL生成该标准SQL的哈希码后，进一步包括：存储标准SQL的记录至已创建的采集数据库表中，其中，标准SQL的记录包含：标准SQL、标准SQL的捕获时间、以及该标准SQL的哈希码至已创建的采集数据库表中；所述标准SQL的捕获时间为捕获该标准SQL对应的原始SQL的时间。以标准SQL为select*from taw_user where userid＝？为例，则该标准SQL的记录为：

hashid 2627970744.00000

sql select*from taw_user where userid＝？

intime 20100702174157

其中，hashid是通过HASH码生成函数生成的该标准SQL即select*fromtaw_user where userid＝？的哈希码，intime为该标准SQL的捕获时间。

基于此，步骤104的具体实现流程如图3所示：

参见图3，图3为本发明实施例提供的步骤104的实现流程图。如图3所示，该流程可包括：

步骤301，从采集数据库表中采集捕获时间在设定统计周期内的标准SQL的哈希码。

由于频繁采集SQL会给数据库带来负荷，所以不可能将所有执行过的SQL全部采集，而是以设定统计周期为单位采集，然后汇总定位出关键SQL，这样不会给数据库增加太大负荷。

比如，设定统计周期为2010年11月30日12点10分10秒至2010年11月30日13点10分10秒，基于此，本步骤301可从采集数据库表中将捕获时间在2010年11月30日12点10分10秒至2010年11月30日13点10分10秒的所有标准SQL采集出来。

步骤302，确定采集的每一哈希码标识的标准SQL在所述设定统计周期内的执行频率和执行成本。

其中，标准SQL的执行频率为该标准SQL在所述设定统计周期内被执行的次数，具体实现时，可通过统计该标准SQL的哈希码在所有被采集的哈希码中出现的次数。比如，步骤301采集的哈希码为5个，分别为A1、A2、A1、A1和A4；由于A1在采集的5个哈希码中出现了3次，如此，确定A1标识的SQL的执行频率为3，相应地，也可确定出A2和A4标识的SQL的执行频率为1。

至于标准SQL的执行成本，其是一个SQL执行时消耗***资源的估算值，没有计量单位，值越大，表示消耗的***资源将越多，执行时间也越长，其可由数据库中的优化器确定。其中，优化器可通过分析执行SQL的磁盘I/O、CPU时间消耗、内存开销等因素，构造所有可能的执行计划，并估算出其执行SQL的成本，最后选择具有最低执行成本的执行计划。

优选地，本实施例在确定出采集的所有哈希码标识的标准SQL的执行频率后，且在确定该标准SQL的执行成本之前进一步包括：按照执行频率的大小对各个标准SQL排队；比如，将执行频率最大的标准SQL排在最前面，依次类推，即可形成一个队列；如此，本步骤302可针对队列中的标准SQL按顺序确定对应的执行成本。

步骤303，将执行频率和执行成本均大于对应阈值的标准SQL确定为需要优化的关键SQL。

通常，执行频率高、且执行成本高的标准SQL耗用的数据库资源比较多，其一般为关键SQL。因此，本步骤303可根据实际情况设置执行频率和执行成本分别对应的阈值，以便准确、快速识别出关键SQL。

至此，通过上述步骤301至步骤303实现了关键SQL的识别。从图3可以看出，本流程是基于标准SQL识别关键SQL的，而并非采用原始SQL，由于标准SQL能够统一属于同一数据库操作但不同的原始SQL，因此，图3所示的流程避免了属于同一数据库操作的不同原始SQL分开处理造成关键SQL定位不准确、影响数据库优化的缺陷。

以上对本发明实施例提供的方法进行了详细描述，下面对本发明实施例提供的装置进行描述。

参见图4，图4为本发明实施例提供的装置结构图。如图4所示，该装置可包括：

捕获单元401，用于捕获原始SQL；

处理单元402，用于针对捕获单元401捕获的每一原始SQL，对该原始SQL进行标准化处理，得到对应该原始SQL的标准SQL，并生成该标准SQL的标识；

定位单元403，用于根据处理单元402生成的各个标准SQL的标识定位出需要优化的关键SQL。

优选地，捕获单元401以设定捕获粒度为单位从数据库的共享内存池中捕获原始SQL；其中，所述捕获粒度按照对***负荷影响最小、且保证捕获的原始SQL具有代表性的原则设定。

本实施例中，处理单元402具体实现时可包括：

标准化子单元4021，用于针对捕获单元401捕获的每一原始SQL，对该原始SQL进行标准化处理，得到对应该原始SQL的标准SQL；

标识生成子单元4022，用于生成每一标准SQL的标识。

其中，标准化子单元4021可通过提取原始SQL的执行路线，将该执行路线抽象过滤为标准SQL实现标准化处理，具体包括以下模块：

遍历模块4023，用于按照顺序遍历原始SQL中的字符，所述字符为单词、符号或者数组，将遍历到的字符作为当前字符；

第一判断模块4024，用于判断所述当前字符是否为最后一个未被遍历的字符，如果否，发送判断通知给第二判断模块4025；如果是，结束当前流程；

第二判断模块4025，用于接收到判断通知后，判断当前字符是否在设定的运算符中，如果是，确定当前字符的下一个字符为执行参数，将其替换为标准字符，将替换后的标准字符的下一个字符作为当前字符，并触发第一判断模块4024执行判断操作；如果否，将当前字符的下一个字符作为当前字符，并触发第一判断模块4024执行判断操作。

第三判断模块4026，用于在第二判断模块4025判断结果为否之后，且在第二判断模块4025执行将当前字符的下一个字符作为当前字符之前，判断当前字符是否为between，如果否，触发第二判断模块4025继续执行将当前字符的下一个字符作为当前字符的操作，如果是，发送处理通知给处理模块4027；

处理模块4027，用于接收到所述处理通知后，确定当前字符的下一个字符为执行参数，将其替换为标准字符，并在该替换后的标准字符的下一个字符为and时，确定该and的下一个字符为执行参数，将其替换为标准字符，并将该替换后的标准字符的下一个字符确定为当前字符，并触发第一判断模块4024执行判断操作。

优选地，标识生成子单元4022在生成标准SQL的标识之后进一步包括：记录标准SQL、该标准SQL的捕获时间、以及该标准SQL的标识至已创建的采集数据库表中，所述标准SQL的捕获时间为捕获该标准SQL对应的原始SQL的时间；

基于此，定位单元403可根据采集数据库表的记录执行定位关键SQL的操作。如图4所示，定位单元403可包括：

采集子单元4031，用于从所述采集数据库表中采集捕获时间在设定统计周期内的标准SQL的标识；

确定子单元4032，用于针对采集子单元4031采集的每一标准SQL的标识，确定该标准SQL在所述设定统计周期内的执行频率和执行成本；

其中，确定子单元4032确定标准SQL在设定统计周期内的执行频率包括：确定该标准SQL的标识在采集的所有标准SQL标识中的出现次数，将所述出现次数确定为该标准SQL在设定统计周期内的执行频率。

至于确定子单元4032确定标准SQL在设定统计周期内的执行成本，其可按照现有方式确定，这里不再赘述。

定位子单元4033，用于将执行频率和执行成本均大于对应阈值的标准SQL确定为需要优化的关键SQL。

优选地，标识生成子单元4022利用设定的哈希HASH码生成函数，生成该标准SQL的哈希码。

以上对本发明实施例提供的装置进行了描述。

以上所述仅为本发明的较佳实施例而已，并不用以限制本发明，凡在本发明的精神和原则之内，所做的任何修改、等同替换、改进等，均应包含在本发明保护的范围之内。

Claims

1.一种数据库优化方法，其特征在于，该方法包括：

A，捕获原始SQL；

B,针对捕获的每一原始SQL，提取原始SQL的执行路线，将该执行路线抽象过滤为标准SQL，得到对应该原始SQL的标准SQL，并生成该标准SQL的标识，记录标准SQL、该标准SQL的捕获时间、以及该标准SQL的标识至已创建的采集数据库表中，所述标准SQL的捕获时间为捕获该标准SQL对应的原始SQL的时间；

其中，提取原始SQL的执行路线，将该执行路线抽象过滤为标准SQL包括：B11，按照顺序遍历原始SQL中的字符，所述字符为单词、符号或者数组，将遍历到的字符作为当前字符；B12，判断所述当前字符是否为最后一个未被遍历的字符，如果否，执行步骤B13；如果是，结束当前流程；B13，判断当前字符是否在设定的运算符中，如果是，确定当前字符的下一个字符为执行参数，将该执行参数替换为标准字符，将替换后的标准字符的下一个字符作为当前字符，返回步骤B12；如果否，将当前字符的下一个字符作为当前字符，返回步骤B12；

C1，从所述采集数据库表中采集捕获时间在设定统计周期内的标准SQL的标识；

C2，针对采集的每一标准SQL的标识，确定该标准SQL在所述设定统计周期内的执行频率和执行成本；

C3，将执行频率和执行成本均大于对应阈值的标准SQL确定为需要优化的关键SQL。

2.根据权利要求1所述的方法，其特征在于，步骤A以设定捕获粒度为单位从数据库的共享内存池中捕获原始SQL。

3.根据权利要求2所述的方法，其特征在于，所述捕获粒度按照对***负荷影响最小、且保证捕获的原始SQL具有代表性的原则设定。

4.根据权利要求1所述的方法，其特征在于，所述运算符至少包含以下字符中的至少一个：等于=、大于＞、小于＜、尖括号<>、大于等于＞＝，小于等于＜＝、like。

5.根据权利要求1所述的方法，其特征在于，步骤B13中，在判断出当前字符不在设定的运算符中之后，且在将当前字符的下一个字符作为当前字符之前，进一步包括：

B00，判断当前字符是否为between，如果否，继续执行步骤B13中将当前字符的下一个字符作为当前字符的操作，如果是，执行步骤B01；

B01，确定当前字符的下一个字符为执行参数，将确定为执行参数的当前字符的下一个字符替换为标准字符；之后执行步骤B02；

B02，如果步骤B01替换后的标准字符的下一个字符为and，则确定该and的下一个字符为执行参数，将确定为执行参数的and的下一个字符替换为标准字符，并将该替换后的标准字符的下一个字符确定为当前字符，返回执行步骤B12。

6.根据权利要求1所述的方法，其特征在于，步骤C2中，确定标准SQL在设定统计周期内的执行频率包括：

确定该标准SQL的标识在步骤C1采集的所有标准SQL标识中出现的次数，将该出现次数确定为该标准SQL在设定统计周期内的执行频率。

7.根据权利要求1至6任一所述的方法，其特征在于，步骤B中，生成标准SQL的标识包括：

利用设定的哈希HASH码生成函数，生成该标准SQL的哈希码。

8.一种数据库优化装置，其特征在于，该装置包括：

捕获单元，用于捕获原始SQL；

处理单元，包括：

标准化子单元，用于针对所述捕获单元捕获的每一原始SQL，对该原始SQL进行标准化处理，得到对应该原始SQL的标准SQL，记录标准SQL、该标准SQL的捕获时间、以及该标准SQL的标识至已创建的采集数据库表中，所述标准SQL的捕获时间为捕获该标准SQL对应的原始SQL的时间；

标识生成子单元，用于生成每一标准SQL的标识；

其中，所述标准化子单元通过提取原始SQL的执行路线，将该执行路线抽象过滤为标准SQL实现标准化处理，具体包括以下模块：

遍历模块，用于按照顺序遍历原始SQL中的字符，所述字符为单词、符号或者数组，将遍历到的字符作为当前字符；

第一判断模块，用于判断所述当前字符是否为最后一个未被遍历的字符，如果否，发送判断通知给第二判断模块；如果是，结束当前流程；

第二判断模块，用于接收到判断通知后，判断当前字符是否在设定的运算符中，如果是，确定当前字符的下一个字符为执行参数，将该执行参数替换为标准字符，将替换后的标准字符的下一个字符作为当前字符，并触发所述第一判断模块执行判断操作；如果否，将当前字符的下一个字符作为当前字符，并触发所述第一判断模块执行判断操作；

定位单元，包括：

采集子单元，用于从所述采集数据库表中采集捕获时间在设定统计周期内的标准SQL的标识；

确定子单元，用于针对采集的每一标准SQL的标识，确定该标准SQL在所述设定统计周期内的执行频率和执行成本；

定位子单元，用于将执行频率和执行成本均大于对应阈值的标准SQL确定为需要优化的关键SQL。

9.根据权利要求8所述的装置，其特征在于，所述捕获单元以设定捕获粒度为单位从数据库的共享内存池中捕获原始SQL；

所述捕获粒度按照对***负荷影响最小、且保证捕获的原始SQL具有代表性的原则设定。

10.根据权利要求8所述的装置，其特征在于，所述标准化子单元还包括：

第三判断模块，用于在第二判断模块的判断结果为否之后，且在所述第二判断模块执行将当前字符的下一个字符作为当前字符之前，判断当前字符是否为between，如果否，触发所述第二判断模块继续执行将当前字符的下一个字符作为当前字符的操作，如果是，发送处理通知给处理模块；

处理模块，用于接收到所述处理通知后，确定当前字符的下一个字符为执行参数，将确定为执行参数的当前字符的下一个字符替换为标准字符，并在该替换后的标准字符的下一个字符为and时，确定该and的下一个字符为执行参数，将确定为执行参数的and的下一个字符替换为标准字符，并将该替换后的标准字符的下一个字符确定为当前字符，并触发所述第一判断模块执行判断操作。

11.根据权利要求8至10任一所述的装置，其特征在于，所述标识生成子单元利用设定的哈希HASH码生成函数，生成该标准SQL的哈希码。