CN112434003B

CN112434003B - 一种sql优化方法、装置、计算机设备及存储介质

Info

Publication number: CN112434003B
Application number: CN202011012194.9A
Authority: CN
Inventors: 高仕进
Original assignee: Suning Cloud Computing Co Ltd
Current assignee: Suning Cloud Computing Co Ltd
Priority date: 2020-09-23
Filing date: 2020-09-23
Publication date: 2022-11-18
Anticipated expiration: 2040-09-23
Also published as: CN112434003A; CA3131725C; CA3131725A1

Abstract

本发明公开了一种SQL优化方法、装置、计算机设备及存储介质，该方法包括：解析SQL语句，识别SQL语句涉及的主表以及辅表；获取主表和辅表在执行SQL语句对应的待优化SQL脚本的目标平台上的记录量；根据记录量以及预设分类规则对主表以及辅表进行分类，获取分类结果；根据预设优化规则以及分类结果对待优化SQL脚本进行优化处理，获取优化后SQL脚本。本发明一方面，通过分析待优化SQL脚本的执行计划，智能化重新组装优化SQL脚本，格式化输出规范的优化后的SQL脚本，提高脚本的可读性，另一方面，可以为开发人员节省出大量的时间和精力，获取高质量专家级的SQL语句，极大的减少人力成本。

Description

一种SQL优化方法、装置、计算机设备及存储介质

技术领域

本发明涉及数据库技术领域，特别涉及一种SQL优化方法、装置、计算机设备及存储介质。

背景技术

目前网上各种HIVE SQL优化的经验分享与总结五花八门，但是行业内暂无HIVESQL自动优化的工具。

由于每个开发人员理解和能力不同，因此不同开发人员写的HIVE SQL执行计划的差异也会很大。一方面，对于同一条件下的SQL语句写法有很多，其中一些写法往往会对性能产生很大影响；另一方面，由于不同开发人员写出的代码风格不同，导致代码的可读性差异很大。

因此，亟需提出一种新的SQL优化方法。

发明内容

为了解决现有技术的问题，本发明实施例提供了一种SQL优化方法、装置、计算机设备以及存储介质，以克服现有技术中存在的不规范写法的SQL语句的性能较差以及SQL脚本的可读性差等问题。

为解决上述一个或多个技术问题，本发明采用的技术方案是：

第一方面，提供了一种SQL优化方法，该方法包括如下步骤：

解析SQL语句，识别所述SQL语句涉及的主表以及辅表；

获取所述主表和所述辅表在执行所述SQL语句对应的待优化SQL脚本的目标平台上的记录量；

根据所述记录量以及预设分类规则对所述主表以及所述辅表进行分类，获取分类结果；

根据预设优化规则以及所述分类结果对所述待优化SQL脚本进行优化处理，获取优化后SQL脚本。

进一步的，所述根据所述记录量以及预设分类规则对所述主表以及所述辅表进行分类，获取分类结果包括：

判断所述主表或所述辅表的所述记录量是否超过预设阈值，若是，则将所述主表或所述辅表确定为大表，否则将所述主表或所述辅表确定为直接小表；

利用子查询将与确定为大表的所述辅表所关联的表根据关联字段进行去重，将去重后的表与确定为大表的所述辅表内关联，重新统计关联后的记录量，判断所述关联后的记录量是否超过预设阈值，若未超过，则将所述辅表确定为间接小表。

进一步的，所述根据预设优化规则以及所述分类结果对所述待优化SQL脚本进行优化处理，获取优化后SQL脚本至少包括：

利用子查询将与所述间接小表所关联的表根据关联字段进行去重，将去重后的表与所述间接小表内关联，生成第一临时表。

识别所述大表中重复扫描的目标大表，从所述SQL语句的解析结果中获取目标字段与过滤条件，根据所述目标字段和所述过滤条件从所述目标大表中获取目标数据，生成第二临时表，以便后续使用所述第二临时表代替所述目标大表进行相关操作。

将与所述主表同粒度且关联字段相同的所述大表以及所述第二临时表进行合并处理，生成第一结果表。

将与所述主表不同粒度且关联字段不同的所述大表、所述第二临时表以及所述第一结果表进行合并处理，生成第二结果表；

将所述第二结果表与所述直接小表以及所述间接小表进行关联，生成优化后SQL脚本的目标表。

进一步的，所述方法还包括：

获取所述目标平台的平台参数信息，根据所述平台参数信息以及所述预设优化规则对所述待优化SQL脚本的参数设置进行调整。

第二方面，提供了一种SQL优化装置，所述装置包括：

解析模块，用于解析SQL语句，识别所述SQL语句涉及的主表以及辅表；

提取模块，用于获取所述主表和所述辅表在执行所述SQL语句对应的待优化SQL脚本的目标平台上的记录量；

分类模块，用于根据所述记录量以及预设分类规则对所述主表以及所述辅表进行分类，获取分类结果；

优化模块，用于根据预设优化规则以及所述分类结果对所述待优化SQL脚本进行优化处理，获取优化后SQL脚本。

第三方面，提供了一种计算机设备，包括存储器、处理器及存储在存储器上并可在处理器上运行的计算机程序，所述处理器执行所述计算机程序时实现如下步骤：

解析SQL语句，识别所述SQL语句涉及的主表以及辅表；

第四方面，提供了一种计算机可读存储介质，其上存储有计算机程序，所述计算机程序被处理器执行时，实现如下步骤：

解析SQL语句，识别所述SQL语句涉及的主表以及辅表；

本发明实施例提供的技术方案带来的有益效果是：

1、本发明实施例提供的SQL优化方法、装置、计算机设备及存储介质，通过解析SQL语句，识别所述SQL语句涉及的主表以及辅表，获取所述主表和所述辅表在执行所述SQL语句对应的待优化SQL脚本的目标平台上的记录量，根据所述记录量以及预设分类规则对所述主表以及所述辅表进行分类，获取分类结果，根据预设优化规则以及所述分类结果对所述待优化SQL脚本进行优化处理，获取优化后SQL脚本，一方面，通过分析待优化SQL脚本的执行计划，智能化重新组装优化SQL脚本，格式化输出规范的优化后的SQL脚本，提高脚本的可读性，另一方面，可以为开发人员节省出大量的时间和精力，获取高质量专家级的SQL语句，极大的减少人力成本；

2、本发明实施例提供的SQL优化方法、装置、计算机设备及存储介质，通过获取所述目标平台的平台参数信息，根据所述平台参数信息以及所述预设优化规则对所述待优化SQL脚本的参数设置进行调整，同样提高了脚本的可读性。

本发明所有产品并不需要具备上述所有效果。

附图说明

为了更清楚地说明本发明实施例中的技术方案，下面将对实施例描述中所需要使用的附图作简单地介绍，显而易见地，下面描述中的附图仅仅是本发明的一些实施例，对于本领域普通技术人员来讲，在不付出创造性劳动的前提下，还可以根据这些附图获得其他的附图。

图1是根据一示例性实施例示出的SQL优化方法的流程图；

图2是根据一示例性实施例示出的SQL优化装置的结构示意图；

图3是根据一示例性实施例示出的计算机设备的内部结构示意图。

具体实施方式

为使本发明的目的、技术方案和优点更加清楚，下面将结合本发明实施例中的附图，对本发明实施例中的技术方案进行清楚、完整地描述，显然，所描述的实施例仅仅是本发明一部分实施例，而不是全部的实施例。基于本发明中的实施例，本领域普通技术人员在没有做出创造性劳动前提下所获得的所有其他实施例，都属于本发明保护的范围。

如背景技术所述，为解决上述问题，本发明实施例中创造性的提出了一种SQL优化方法，该方法通过提取目标平台(如HVIE平台)的平台参数和SQL脚本涉及表(包括主表和辅表)的最近一次执行记录量，分析脚本的执行计划，根据预设优化规则智能化重新组装生成优化后的SQL脚本，并对脚本的SET参数设置进行调整，格式化输出规范的HIVE SQL脚本，提高脚本的可读性，同时可以为开发人员节省出大量的时间和精力，极大的减少人力成本。

图1是根据一示例性实施例示出的SQL优化方法的流程图，参照图1所示，该方法包括如下步骤：

S1：解析SQL语句，识别所述SQL语句涉及的主表以及辅表。

具体的，对原始SQL语句进行解析，根据输出目标表和关键词(如leftjoin、innerjoin、join、rightjoin等)，解析出该SQL语句涉及的主表以及辅表。这里需要说明的是，本发明实施例中，主表指目标表的核心输出表，一般与目标表数据粒度相同，辅表指用来生成维度字段的表，以左联接(leftjoin)为例，一般为leftjoin后面的表。

S2：获取所述主表和所述辅表在执行所述SQL语句对应的待优化SQL脚本的目标平台上的记录量。

具体的，本发明实施例中，待优化SQL脚本是与步骤S1中的SQL语句对应。确定执行待优化SQL脚本的目标平台(如HIVE平台等)，在该目标平台上提取SQL语句涉及的主表以及辅表在目标平台上的记录量。具体实施时，可以根据目标平台的日志信息提取每个表(包括主表和辅表)最近一次执行的记录量，对于从日志信息中提取不到记录量的表(包括主表和辅表)，可以采用count(*)的方式统计该表的记录量。

S3：根据所述记录量以及预设分类规则对所述主表以及所述辅表进行分类，获取分类结果。

具体的，本发明实施例中，结合记录量预先设置一分类规则，然后根据记录量以及预设分类规则对上述主表以及辅表进行分类，获取分类结果。

S4：根据预设优化规则以及所述分类结果对所述待优化SQL脚本进行优化处理，获取优化后SQL脚本。

具体的，本发明实施例中，根据实际需求以及以往优化经验总结等预先设置一优化规则，该预设的优化规则中，对分类结果中不同类型的表进行不同的优化处理，然后根据各个类型表的优化结果重新组织脚本，生成优化后的SQL脚本。

作为一种较优的实施方式，本发明实施例中，所述根据所述记录量以及预设分类规则对所述主表以及所述辅表进行分类，获取分类结果包括：

利用子查询将与确定为大表的所述辅表所关联的表根据关联字段去重，将去重后的表与确定为大表的所述辅表内关联，重新统计关联后的记录量，判断所述关联后的记录量是否超过预设阈值，若未超过，则将所述辅表确定为间接小表。

具体的，本发明实施例中，分类结果包括但不限于大表、直接小表、间接小表等。具体在进行分类时，可以按照如下规则：

将通过前述步骤获取到的主表或辅表的记录量与预设阈值进行比较，判断记录量是否超过该预设阈值，将记录量超过预设阈值的主表或辅表分类为大表(COMMON JOIN)，将记录量不超过预设阈值的主表或辅表分类为直接小表(MAPJOIN)。其中，为了减少后续步骤的数据计算量，本发明实示例中，还需先确定出分类为大表的辅表(buildtable)，利用子查询将与确定为大表的辅表所关联的表根据关联字段进行去重，将去重后的表与确定为大表的辅表用关联字段(即关联KEY)进行内关联，然后再统计关联后的记录量，将关联后的记录量与上述预设阈值进行比较，若关联后的记录量仍然超过预设阈值，则确定该辅表仍然分类为大表，否则，将该辅表重新分类为间接小表。这里需要说明的是，本发明实施例中的预设阈值可以根据实际需求进行设置，这里不做限制。

作为一种较优的实施方式，本发明实施例中，所述根据预设优化规则以及所述分类结果对所述待优化SQL脚本进行优化处理，获取优化后SQL脚本至少包括：

利用子查询将与所述间接小表所关联的表根据关联字段进行去重，将去重后的表与所述间接小表进行内关联，生成第一临时表。

具体的，对于小表关联的治理，这里引入了临时表。具体实施时，首先利用子查询将与间接小表所关联的表用相应的关联字段进行去重，将去重后的表与间接小表进行内关联，生成第一临时表。通过独立一步处理全部小表关联，合理优化参数设置，保障MAPJOIN合并执行，减少MAPJOIN执行次数，提高效率。

具体的，在根据预设优化规则以及分类结果对待优化SQL脚本进行优化处理时，对于上述步骤获取的分类结果中的大表，可以首先识别是否存在重复扫描的表。具体实施时，通过解析待优化SQL脚本，统计每个表(包括主表和辅表)的使用次数，根据使用次数判断该表是否重复扫描。这里需要说明的是，通过解析待优化SQL脚本，统计每个表的使用次数这一步骤，可以在步骤S3之前执行，也可以在步骤S3之后执行，这里不做限制。若是在步骤S3之后执行，则可以只统计分类为大表的主表或辅表的使用次数。

对于识别出为重复扫描的大表，这里可以提前用临时表进行治理。具体实施时，首先根据SQL解析生成相关需要的字段以及过滤条件，即目标字段，然后根据该目标字段以及过滤条件从目标大表中一次性获取目标数据，生成第二临时表，以便后续使用所述第二临时表代替目标大表进行相关操作，即方便后续直接关联引用，减少表的扫描次数与IO压力。

具体的，对于与主表同粒度的大表关联的治理，本发明实施例中，将与主表同粒度且关联字段相同的大表以及第二临时表合并一步关联处理，用子查询处理表的关联，保障核心表相同关联字段关联1个MR完成执行。

具体的，对于与主表不同粒度的大表关联的治理，本发明实施例中，将与主表不同粒度且关联字段不同的大表、第二临时表以及第一结果表关联合并一步处理，相同关联字段的表放在相邻的位置。在处理过程中可能出现数据倾斜，关联字段出现大量的空值或者默认值，导致这些数据集中在某个分区，需要在关联时对关联字段的空值、默认值做随机数处理，将数据集中的空值、默认值分散到多个数据分区中关联处理。最后，将上述步骤获取到的第二结果表、直接小表以及间接小表进行左关联，生成目标表，该目标表即为优化后SQL脚本的目标表。

作为一种较优的实施方式，本发明实施例中，所述方法还包括：

具体的，本发明实施例中，还包括对待优化SQL脚本参数设置的优化过程。具体实施时，首先连接目标平台(如HIVE平台)，提取平台参数信息，其中平台参数信息包括但不限于平台节点数、MAP、REDUCE等相关参数信息。然后根据平台参数信息和HDFS文件大小，对待优化SQL脚本的参数设置中不合理的SET参数设置进行调整，实现对待优化SQL脚本的参数设置的优化。具体调整规则可以根据用户实际需求以及优化经验进行设置这里不一一赘述。

数据倾斜的优化过程。

具体的，对于分类为大表且与主表不同粒度的辅表，统计主表关联字段值的记录量分布情况，选择合适的策略治理数据倾斜处理，例如：

方案1：关联字段为空值或者默认值倾斜，针对空值或者默认值进行随机数打散治理；

方案2：根据统计的关联字段的分布记录量，降序的行裁剪，生成一张临时小表，与前述步骤中的直接小表、间接小表一起关联处理；其他记录生成另外一张临时大表，根据关联字段识别与主表是否同粒度，选择不同步骤的大表合并一步一起进行关联处理。

作为一种较优的实施方式，本发明实施例中，所述根据预设优化规则以及所述分类结果对所述待优化SQL脚本进行优化处理，获取优化后SQL脚本还包括：

根据源表和目标字段元数据信息，完善字段注释；以及根据工具设置的默认排版格式，对HIVE SQL脚本重新统一排版，增加代码的可读性。

图2是根据一示例性实施例示出的SQL优化装置的结构示意图，该装置包括：

作为一种较优的实施方式，本发明实施例中，所述分类模块包括：

第一分类单元，用于判断所述主表或所述辅表的所述记录量是否超过预设阈值，若是，则将所述主表或所述辅表确定为大表，否则将所述主表或所述辅表确定为直接小表；

第二分类单元，用于利用子查询将与确定为大表的所述辅表所关联的表根据关联字段进行去重，将去重后的表与确定为大表的所述辅表内关联，重新统计关联后的记录量，判断所述关联后的记录量是否超过预设阈值，若未超过，则将所述辅表确定为间接小表。

作为一种较优的实施方式，本发明实施例中，所述优化模块具体用于：

将与所述主表同粒度且关联字段相同的所述大表以及所述第二临时表进行合并，处理，生成第一结果表。

作为一种较优的实施方式，本发明实施例中，所述装置还包括：

调整模块，用于获取所述目标平台的平台参数信息，根据所述平台参数信息以及所述预设优化规则对所述待优化SQL脚本的参数设置进行调整。

图3是根据一示例性实施例示出的计算机设备的内部结构示意图，参照图3所示，该计算机设备包括通过系统总线连接的处理器、存储器和网络接口。其中，该计算机设备的处理器用于提供计算和控制能力。该计算机设备的存储器包括非易失性存储介质、内存储器。该非易失性存储介质存储有操作系统、计算机程序和数据库。该内存储器为非易失性存储介质中的操作系统和计算机程序的运行提供环境。该计算机设备的网络接口用于与外部的终端通过网络连接通信。该计算机程序被处理器执行时以实现一种执行计划的优化方法。

本领域技术人员可以理解，图3中示出的结构，仅仅是与本发明方案相关的部分结构的框图，并不构成对本发明方案所应用于其上的计算机设备的限定，具体的计算机设备可以包括比图中所示更多或更少的部件，或者组合某些部件，或者具有不同的部件布置。

作为一种较优的实施方式，本发明实施例中，计算机设备包括存储器、处理器及存储在存储器上并可在处理器上运行的计算机程序，处理器执行计算机程序时实现以下步骤：

解析SQL语句，识别所述SQL语句涉及的主表以及辅表；

作为一种较优的实施方式，本发明实施例中，处理器执行计算机程序时还实现以下步骤：

本发明实施例中，还提供了一种计算机可读存储介质，其上存储有计算机程序，所述计算机程序被处理器执行时，实现如下步骤：

解析SQL语句，识别所述SQL语句涉及的主表以及辅表；

作为一种较优的实施方式，本发明实施例中，所述计算机程序被处理器执行时，还实现如下步骤：

综上所述，本发明实施例提供的技术方案带来的有益效果是：

需要说明的是：上述实施例提供的SQL优化装置在触发优化业务时，仅以上述各功能模块的划分进行举例说明，实际应用中，可以根据需要而将上述功能分配由不同的功能模块完成，即将装置的内部结构划分成不同的功能模块，以完成以上描述的全部或者部分功能。另外，上述实施例提供的SQL优化装置与SQL优化方法实施例属于同一构思，即该装置是基于该SQL优化方法的，其具体实现过程详见方法实施例，这里不再赘述。

本领域普通技术人员可以理解实现上述实施例的全部或部分步骤可以通过硬件来完成，也可以通过程序来指令相关的硬件完成，所述的程序可以存储于一种计算机可读存储介质中，上述提到的存储介质可以是只读存储器，磁盘或光盘等。

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

Claims

1.一种SQL优化方法，其特征在于，所述方法包括如下步骤：

解析SQL语句，识别所述SQL语句涉及的主表以及辅表；

根据预设优化规则以及所述分类结果对所述待优化SQL脚本进行优化处理，获取优化后SQL脚本；

其中，所述根据所述记录量以及预设分类规则对所述主表以及所述辅表进行分类，获取分类结果包括：

利用子查询将与确定为大表的所述辅表所关联的表根据关联字段进行去重，将去重后的表与确定为大表的所述辅表内关联，重新统计关联后的记录量，判断所述关联后的记录量是否超过预设阈值，若未超过，则将所述辅表确定为间接小表；

所述根据预设优化规则以及所述分类结果对所述待优化SQL脚本进行优化处理，获取优化后SQL脚本包括：

利用子查询将与所述间接小表所关联的表根据关联字段进行去重，将去重后的表与所述间接小表内关联，生成第一临时表；

识别所述大表中重复扫描的目标大表，从所述SQL语句的解析结果中获取目标字段与过滤条件，根据所述目标字段和所述过滤条件从所述目标大表中获取目标数据，生成第二临时表，以便后续使用所述第二临时表代替所述目标大表进行相关操作；

将与所述主表同粒度且关联字段相同的所述大表以及所述第二临时表进行合并处理，生成第一结果表；

2.根据权利要求1所述的SQL优化方法，其特征在于，所述方法还包括：

3.一种实现权利要求1所述的SQL优化方法的装置，其特征在于，所述装置包括：

4.一种计算机设备，包括存储器、处理器及存储在存储器上并可在处理器上运行的计算机程序，其特征在于，所述处理器执行所述计算机程序时实现权利要求1至2中任一项所述方法的步骤。

5.一种计算机可读存储介质，其上存储有计算机程序，其特征在于，所述计算机程序被处理器执行时实现权利要求1至2中任一项所述的方法的步骤。