CN112347120A - 一种基于复杂sql的自动优化方法和装置 - Google Patents

Abstract

本发明公开一种基于复杂SQL的自动优化方法和装置，方法包括：接收并从中筛选具有优化特征的SQL语句；根据基本规则，检验SQL语句的正确性；对SQL语句遍历所有优化规则，优化SQL语句；输出优化后的SQL语句并记录耗时；生成优化报告，并记录到数据库；其中，优化特征包括：执行出错、影响执行效率、执行顺序有问题、执行耗时长、索引问题导致全表查询、丢失索引、嵌套多级子查询；优化报告包括如下字段：应用程序、数据库类型、SQL语句、执行耗时、问题描述。本发明解决了现有技术中对于复杂SQL语句的开发与维护的复杂度较高、难度较大的问题，达到自动优化与效率监控的效果，提高SQL语句的执行效率。

Description

一种基于复杂SQL的自动优化方法和装置

技术领域

本发明实施例涉及数据库技术领域，尤其涉及一种基于复杂SQL的自动化方法和装置。

背景技术

在当前信息时代，效率对各种行业都非常重要，效率对一个企业的发展来说有着决定性的作用。同理，一个管理系统也不例外，作为系统平台不能单靠代码的好坏来判断系统的效率快慢，往往数据库的脚本SQL起到决定性的影响。因而，脚本优化显得极其重要，甚至影响一个系统的好与坏。

现有系统大部分的数据库脚本都交给ORM(对象/关系映射，object-relationa lmapping)框架去管理，典型的ORM框架包括Hibernate，iBATIS，JDO(Java Data Objects)和JPA(Java Persistence API)。确实，框架帮我们简化了很多问题，使得我们可以将精力集中于业务代码的开发。与此同时，使我们忽略了数据库脚本的执行效率问题。一条效率好的SQL语句，无疑在系统中极其重要。

由于目前大部分的系统，有拥有一套ORM框架，正是因为框架使得我们开发起来，简化很多管理JDBC操作。如果直接使用原生的JDBC操作数据库的A PI，需要处理与数据库访问相关的所有事情，包括数据库资源的管理和异常的处理。使用原生JDBC进行一次插入操作的代码的80％都是样板代码，包括获取和关闭数据库链接，SQL语句，异常处理等等。框架帮我们自动生成这部分代码，但是样板代码是在牺牲执行效率的前提下实现样板化，应用程序中出现SQ L语句不规范问题的原因在于，主要是人为因素关系比较大，开发人员都是根据功能决定SQL语句的，并不会深入理解执行原理，如需查询数据库中某个用户的某个信息，一般程序员都直接使用select*from user,目的是达到了，但是并没有考虑执行SQL语句的性能与效率。往往是目的到达了，就忽略内部执行逻辑，从而使开发人员养成一种“依赖”的惰性。

发明内容

鉴于以上所述现有技术的缺点，本发明的目的在于解决了现有技术中对于复杂SQL语句的开发与维护的复杂度较高、难度较大的问题，达到自动优化与效率监控的效果，提高SQL语句的执行效率。

本专利方案提供一种基于复杂SQL的自动优化方法，包括：

接收并从中筛选具有优化特征的SQL语句；

根据基本规则，检验所述SQL语句的正确性；

对所述SQL语句遍历所有优化规则，优化所述SQL语句；

输出优化后的所述SQL语句并记录耗时；

生成优化报告，并记录到数据库；

其中，所述优化特征包括以下至少一项：执行出错、影响执行效率、执行顺序有问题、执行耗时长、索引问题导致全表查询、丢失索引、嵌套多级子查询；所述优化报告包括如下字段：应用程序、数据库类型、SQL语句、执行耗时、问题描述。

进一步地，所述筛选出具有优化特征的SQL语句，具体包括：

将所述SQL语句发送给SQL Server执行并记录执行耗时，若触发优化特征阀值，则所述SQL语句符合所述优化特征。

进一步地，所述优化规则包括普通优化规则和自定义优化规则。

进一步地，所述普通优化规则包括：

若所述SQL语句中包含select*语句，则将*替换为对应的表字段；

若所述SQL语句进行全表扫描操作，则在where子句中使用！＝或<>操作符；

若where子句中对字段进行操作，则在where子句中对字段进行函数操作；

若where子句中使用索引，则在where子句中的“＝”左边进行函数、算术运算或其他表达式运算；

若where子句中含有操作符in，则用操作符exists代替操作符in。

进一步地，所述根据基本规则，检验所述SQL语句的正确性，具体包括：

将所述SQL语句发送给SQL Server，

若所述SQL语句在SQL Server中运行不报错，则所述SQL语句正确；

若所述SQL语句在SQL Server中运行有报错消息，则输出所述报错信息。

还提供一种基于复杂SQL的自动优化装置，包括：

接收筛选模块，接收并从中筛选具有优化特征的SQL语句；

验证模块，用于根据基本规则，检验所述SQL语句的正确性；

优化模块，用于对所述SQL语句遍历所有优化规则，优化所述SQL语句；输出模块，用于输出优化后的所述SQL语句；

监控模块，用于监控优化后的所述SQL语句的执行耗时；

报告生成模块，用于生成SQL优化报告；

其中，所述优化特征包括以下至少一项：执行出错、影响执行效率、执行顺序有问题、执行耗时长、索引问题导致全表查询、丢失索引、嵌套多级子查询；所述优化报告包括如下字段：应用程序、数据库类型、SQL语句、执行耗时、问题描述。

进一步地，所述筛选出具有优化特征的SQL语句，具体包括：

将所述SQL语句发送给SQL Server执行并记录执行耗时，若触发优化特征阀值，则所述SQL语句符合所述优化特征。

进一步地，所述优化规则包括普通优化规则和自定义优化规则。

进一步地，所述普通优化规则包括：

若所述SQL语句中包含select*语句，则将*替换为对应的表字段；

若所述SQL语句进行全表扫描操作，则在where子句中使用！＝或<>操作符；

若where子句中对字段进行操作，则在where子句中对字段进行函数操作；

若where子句中使用索引，则在where子句中的“＝”左边进行函数、算术运算或其他表达式运算；

若where子句中含有操作符in，则用操作符exists代替操作符in。

进一步地，所述根据基本规则，检验所述SQL语句的正确性，具体包括：

将所述SQL语句发送给SQL Server，

若所述SQL语句在SQL Server中运行不报错，则所述SQL语句正确；

若所述SQL语句在SQL Server中运行有报错消息，则输出所述报错信息。

本专利的改进带来如下优点：

本专利通过优化规则遍历的方法，自动对SQL语句进行优化，提高SQL语句的执行效率，缩短执行耗时；同时还对优化前后的执行效果进行记录，与优化记录一起形成优化报告，展示给开发人员，方便开发人员对优化前后的脚本进行监控，在提高效率的同时，开发人员也可以对脚本的执行计划有所了解，养成良好的SQL脚本编写习惯。

附图说明

图1为本发明实施例一种基于复杂SQL的自动优化方法的步骤示意图；

图2为本发明实施例一种基于复杂SQL的自动优化方法的流程示意图。

具体实施方式

以下通过特定的具体实例说明本发明的实施方式，本领域技术人员可由本说明书所揭露的内容轻易地了解本发明的其他优点与功效。本发明还可以通过另外不同的具体实施方式加以实施或应用，本说明书中的各项细节也可以基于不同观点与应用，在没有背离本发明的精神下进行各种修饰或改变。

如图1，2所示，本申请实施例1提供一种基于复杂SQL的自动优化方法，包括：

S101：接收并从中筛选具有优化特征的SQL语句；

S102：根据基本规则，检验SQL语句的正确性；

S103：对SQL语句遍历所有优化规则，优化SQL语句；

S104：输出优化后的SQL语句并记录耗时；

S105：生成优化报告，并记录到数据库。

其中，优化特征包括以下至少一项：执行出错、影响执行效率、执行顺序有问题、执行耗时长、索引问题导致全表查询、丢失索引、嵌套多级子查询；优化报告包括如下字段：应用程序、数据库类型、SQL语句、执行耗时、问题描述。

优化规则由普通优化规则和自定义优化规则组成；自定义优化规则主要是对普通优化规则的补充，针对不同的使用群体，个性化定义优化的强度，如限定SQL的表的关联数量，返回字段不能超过10个等。

步骤S101具体包括：将SQL语句发送给SQL Server执行并记录执行耗时，若触发优化特征阀值，则SQL语句符合优化特征。

步骤S102具体包括：将SQL语句发送给SQL Server，

若SQL语句在SQL Server中运行不报错，则SQL语句正确；

若SQL语句在SQL Server中运行有报错消息，则输出报错信息。

普通优化规则包括：

若SQL语句中包含select*语句，则将*替换为对应的表字段；优化搜索字段，避免全表查询；

若SQL语句进行全表扫描操作，则在where子句中使用！＝或<>操作符；使用不等于操作符，排除多余字段；

若where子句中对字段进行操作，则在where子句中对字段进行函数操作；如wheresubstring(name,1,3)＝'abc'--name以abc开头的id，优化后则为：where name like'abc％'；

若where子句中使用索引，则在where子句中的“＝”左边进行函数、算术运算或其他表达式运算；

若where子句中含有操作符in，则用操作符exists代替操作符in。

优化例子如下：

检查SQL中是否带*，如SELECT*FROM，优化规则：细致到字段级别。

查询的模糊匹配，如LIKE'％parm1％'——百分号会导致相关列的索引无法使用。

在WHERE语句中，尽量避免对索引字段进行计算操作，如where to_date(create_date)。

例1：优化前的SQL语句：SELECT*FROM emp；优化后的SQL语句：SELECT emp.ename,emp.job FROM emp。

例2：优化前的SQL语句：SELECT ename FROM emp where ename LIKE'％小％'；优化后的SQL语句：SELECT ename FROM emp where ename LIKE'小％'。

例3：优化前的SQL语句：SELECT ename FROM emp where eage-10>20；优化后的SQL语句：SELECT ename FROM emp where ename eage>10+20'。

本申请实施例2提供一种基于复杂SQL的自动优化装置，采用上述方法进行优化数据，包括：

接收筛选模块，接收并从中筛选具有优化特征的SQL语句；

验证模块，用于检验所述SQL语句的正确性；

优化模块，用于对所述SQL语句遍历所有优化规则，优化所述SQL语句；输出模块，用于输出优化后的所述SQL语句；

监控模块，用于监控优化后的所述SQL语句的执行耗时；

报告生成模块，用于生成SQL优化报告。

以上所述，仅为本发明较佳的具体实施方式，但本发明的保护范围并不局限于此，任何熟悉本技术领域的技术人员在本发明揭露的技术范围内，可轻易想到的变化或替换，都应涵盖在本发明的保护范围之内。因此，本发明的保护范围应该以权利要求的保护范围为准。

Claims (10)

1.一种基于复杂SQL的自动优化方法，其特征在于，包括：

接收并从中筛选具有优化特征的SQL语句；

根据基本规则，检验所述SQL语句的正确性；

对所述SQL语句遍历所有优化规则，优化所述SQL语句；

输出优化后的所述SQL语句并记录耗时；

生成优化报告，并记录到数据库；

其中，所述优化特征包括以下至少一项：执行出错、影响执行效率、执行顺序有问题、执行耗时长、索引问题导致全表查询、丢失索引、嵌套多级子查询；所述优化报告包括如下字段：应用程序、数据库类型、SQL语句、执行耗时、问题描述。

2.根据权利要求1所述一种基于复杂SQL的自动优化方法，其特征在于，所述筛选出具有优化特征的SQL语句，具体包括：

将所述SQL语句发送给SQL Server执行并记录执行耗时，若触发优化特征阀值，则所述SQL语句符合所述优化特征。

3.根据权利要求1所述一种基于复杂SQL的自动优化方法，其特征在于，所述优化规则包括普通优化规则和自定义优化规则。

4.根据权利要求3所述的一种基于复杂SQL的自动优化方法，其特征在于，所述普通优化规则包括：

若所述SQL语句中包含select*语句，则将*替换为对应的表字段；

若所述SQL语句进行全表扫描操作，则在where子句中使用！＝或<>操作符；

若where子句中对字段进行操作，则在where子句中对字段进行函数操作；

若where子句中使用索引，则在where子句中的“＝”左边进行函数、算术运算或其他表达式运算；

若where子句中含有操作符in，则用操作符exists代替操作符in。

5.根据权利要求1-4任一项所述的一种基于复杂SQL的自动优化方法，其特征在于，所述根据基本规则，检验所述SQL语句的正确性，具体包括：

将所述SQL语句发送给SQL Server，

若所述SQL语句在SQL Server中运行不报错，则所述SQL语句正确；

若所述SQL语句在SQL Server中运行有报错消息，则输出所述报错信息。

6.一种基于复杂SQL的自动优化装置，其特征在于，包括：

接收筛选模块，接收并从中筛选具有优化特征的SQL语句；

验证模块，用于根据基本规则，检验所述SQL语句的正确性；

优化模块，用于对所述SQL语句遍历所有优化规则，优化所述SQL语句；

输出模块，用于输出优化后的所述SQL语句；

监控模块，用于监控优化后的所述SQL语句的执行耗时；

报告生成模块，用于生成SQL优化报告；

其中，所述优化特征包括以下至少一项：执行出错、影响执行效率、执行顺序有问题、执行耗时长、索引问题导致全表查询、丢失索引、嵌套多级子查询；所述优化报告包括如下字段：应用程序、数据库类型、SQL语句、执行耗时、问题描述。

7.根据权利要求6所述的一种基于复杂SQL的自动优化装置，其特征在于，所述筛选出具有优化特征的SQL语句，具体包括：

将所述SQL语句发送给SQL Server执行并记录执行耗时，若触发优化特征阀值，则所述SQL语句符合所述优化特征。

8.根据权利要求6所述的一种基于复杂SQL的自动优化装置，其特征在于，所述优化规则包括普通优化规则和自定义优化规则。

9.根据权利要求8所述的一种基于复杂SQL的自动优化装置，其特征在于，所述普通优化规则包括：

若所述SQL语句中包含select*语句，则将*替换为对应的表字段；

若所述SQL语句进行全表扫描操作，则在where子句中使用！＝或<>操作符；

若where子句中对字段进行操作，则在where子句中对字段进行函数操作；

若where子句中使用索引，则在where子句中的“＝”左边进行函数、算术运算或其他表达式运算；

若where子句中含有操作符in，则用操作符exists代替操作符in。

10.根据权利要求6-9任一项所述的一种基于复杂SQL的自动优化装置，其特征在于，检验所述SQL语句的正确性，具体包括：

将所述SQL语句发送给SQL Server，

若所述SQL语句在SQL Server中运行不报错，则所述SQL语句正确；

若所述SQL语句在SQL Server中运行有报错消息，则输出所述报错信息。

Images