一种基于有监督机器学习的SQL运行慢原因分析方法与流程

本发明属于数据分析领域，尤其涉及一种基于有监督机器学习的sql运行慢原因分析方法。

背景技术：

1、sql(structured query language，结构化查询语言)执行慢的问题在实际使用环境中非常常见，对sql执行慢问题事后的原因的准确分析对提高数据库性能和提升用户体验至关重要。然而，随着云计算和分布式技术的发展，数据库架构在不断的变化，sql执行慢问题的研究也变得更加复杂。

2、目前公开文献中将sql执行慢的原因分为两类，一类是由于内部原因(如编写sql不合理不完善、缺乏索引、sql复杂性高)导致的；另一类是由于外部的间歇性性能问题(如数据库或机器层面的性能低或出现故障)引起的。其中，外部间歇性执行慢sql多发生于云数据库。

3、在大数据时代常见的分析海量查询慢sql的原因的方法为：通过提取sql语句的外部相关特征(如sql语句的执行时间、sql执行时的cpu、sql执行时的网络等)来表示慢查询sql语句，随后使用聚类方法或基于子群发现的框架将同一类的慢查询sql聚合为一类，最后基于dba(database administrator，数据库管理员)的专家经验来分析每一类慢查询sql的具体原因。这种常见方法应用到不同数据库使用场景时，对应的特征需要根据场景不同而变化。虽然该方法能较为准确地区分慢查询sql的类型，但是(1)用于表征sql的特征为sql语句的环境变量(cpu、内存、io、执行时间等)，对于较为复杂的sql语句，只用此类环境变量特征不足以准确表示sql。如数据库系统中会出现环境变量值相近但sql语句较为不同的使用场景，对于此类使用场景，单纯使用环境变量等特征就无法准确区分出执行慢sql的慢的原因。(2)每类查询慢sql的慢的具体原因仍需要依赖dba的人工分析和标注来得出，但在海量数据库中面对海量sql这一人工分析行为将耗费大量人力和时间。

技术实现思路

1、本发明所要解决的技术问题是针对背景技术的不足提供了一种更全面的用于表示sql语句的特征组合方案、实现了基于专家经验进行sql慢原因的自动标注的思路并基于有监督机器学习方法完成对执行慢sql的分类从而最终实现对执行慢sql的事后的原因分析。

2、本发明为解决上述技术问题采用以下技术方案：

3、一种基于有监督机器学习的sql运行慢原因分析方法，具体包含如下步骤：

4、步骤1，收集过往的执行慢的sql运行数据；

5、步骤2，基于收集的sql运行数据，提取所需的特征向量得到sql对应的特征数据集；

6、步骤3，对特征数据集进行数据预处理，包括数据清洗、数据归一化；

7、步骤4，基于专家经验库编写自动标注sql慢原因的原因标注程序，并对步骤3输出的特征数据集进行原因的标注并打上对应的标号，得到有标签的特征数据集；

8、步骤5，对步骤4输出的已标记的数据，使用有监督的机器学习算法实现分类模型的训练和测试，得到分类模型；

9、步骤6，对于待分析的已执行结束的慢sql，使用步骤5得到的分类模型，对慢sql进行分类，从而得到慢sql的具体慢的原因；

10、步骤7，由于可能会有新的类型的执行慢sql出现，所以每过一个时间周期需要扩展和完善用于自动标注慢的原因的专家经验库，从而更新原因标注程序以保障sql慢原因分析的准确性；相应的，用于训练分类模型的数据集的标记会随之变化，就需要基于新的训练集数据重新训练模型得到新的分类模型。

11、作为本发明一种基于有监督机器学习的sql运行慢原因分析方法的进一步优选方案，所述步骤1包括如下子步骤：

12、步骤1.1，在数据库中设计对应的表结构采集所需的sql运行数据；

13、步骤1.2，收集过往的历史sql运行数据；

14、步骤1.3，筛选出步骤1.2中执行时间超过时间阈值t的慢sql作为研究对象。

15、作为本发明一种基于有监督机器学习的sql运行慢原因分析方法的进一步优选方案，所述步骤2包括如下子步骤：

16、步骤2.1，针对步骤1采集的sql运行数据，调用海量数据库管理系统中的hive udf以提取sql语句中的关键字等传统特征以及sql查询数据对应的时间分区数、正则匹配关键词的出现次数、是否有非等值join和子查询复杂特征作为文本类特征，同时针对步骤1采集。

17、作为本发明一种基于有监督机器学习的sql运行慢原因分析方法的进一步优选方案，所述步骤3包括如下子步骤：

18、步骤3.1，针对步骤2中获取的数据特征，将特征数据中的缺失值和异常值替换为0；

19、步骤3.2，针对步骤3.1得到的正常数据，继续数据的归一化操作，得到可用于构建分类模型的数据集。

20、作为本发明一种基于有监督机器学习的sql运行慢原因分析方法的进一步优选方案，所述步骤4具体包括如下子步骤：

21、步骤4.1，收集dba的数据库运维经验，根据步骤2.1提取的sql特征数据，确定具体的执行慢原因对应的特征的数值；

22、步骤4.2，根据步骤4.1确定的执行慢原因与特征值之间的对应关系，编写用于标注sql慢原因的原因标注程序，需要求出表2中14个执行慢原因的全组合；根据已执行sql是否有表2中14个原因中的一个或多个来具体确定sql的原因组合从而得到对应的标号以完成对慢sql的标记工作；

23、步骤4.2.1，对于14个执行慢原因求全组合得到多个原因组合，其中每个原因组合有对应的标号，执行慢原因的全组合及其对应的标号映射形成原因字典dictr；

24、步骤4.2.2，求出每条sql的表2中对应的共14个执行慢原因的具体值作为基础原因值组合r0；对于每条sql，存在某个慢原因则对应的慢原因特征的值为1、否则为0；

25、步骤4.2.3，根据每条sql的基础原因值组合r0与原因全组合及其对应标号所组成的原因字典dictr，得到每条sql对应的原因标号，完成每条sql的标记工作。

26、作为本发明一种基于有监督机器学习的sql运行慢原因分析方法的进一步优选方案，所述步骤5具体包括如下子步骤：

27、步骤5.1，对步骤4.2.3得到的带有分类标号的特征样本，按照比例将样本划分为训练子集与验证子集，训练子集用于训练模型，验证子集用于验证模型的性能；使用有监督的随机森林分类算法基于训练子集拟合得到分类模型、基于验证子集验证分类模型的分类性能。

28、作为本发明一种基于有监督机器学习的sql运行慢原因分析方法的进一步优选方案，所述步骤6具体包括如下子步骤：

29、步骤6.1，对于待分析的已执行的慢sql，调用海量数据库管理系统中的hive udf函数以提取sql语句中的关键字传统特征以及sql查询数据对应的时间分区数、正则匹配关键词的出现次数、是否有非等值join和子查询等复杂特征作为分类特征；

30、步骤6.2，使用步骤5训练得到的分类模型对步骤6.1得到的慢sql的分类特征进行分类，得到每条慢sql的各个类别的分类概率；

31、如果各个类别中存在任意一个类别的分类概率大于等于阈值p，则需要找出分类概率大于等于p的所有类别中概率值最大的类别号i，随后将该条慢sql标注为第i类；

32、如果各个类别中任意一个类别的分类概率都小于阈值p，就将对应的慢sql的类别号设定为-1；遍历处理每条慢sql最终得到带有分类标签的慢sql数据；

33、步骤6.3，将步骤6.2中分为-1类的慢sql作为增量的新出现的sql类型记录至文件中；

34、步骤6.4，根据步骤4.1中执行慢的原因与特征值之间的对应关系，统计分析得出步骤6.2中慢sql对应的具体执行慢原因。

35、作为本发明一种基于有监督机器学习的sql运行慢原因分析方法的进一步优选方案，所述步骤7具体包括如下子步骤：

36、步骤7.1，每隔一段时间，需要收集数据库中运行的增量的慢sql数据集，即为步骤6.3收集的标注为-1类的慢sql数据；由dba人工分析后，将新出现的执行慢的原因及其对应的特征值补充到专家经验库；

37、步骤7.2，基于步骤7.1更新的专家经验库及收集到的增量的慢sql数据集，重新执行步骤2至步骤4得到更新的分类模型。

38、本发明采用以上技术方案与现有技术相比，具有以下技术效果：

39、1、本发明为了准确全面地表示一条sql，提取了sql语句中的关键字或谓词(如group by、join、distinct等)以及sql查询数据对应的时间分区数、正则匹配关键词出现次数、是否有非等值join和子查询等复杂特征作为sql的特征；

40、2、本发明为自动统计sql执行慢的原因而非由dba人工分析得出，提出了一种基于有监督机器学习方法的分类和分析方法；前期需要基于专家经验库编写自动标注sql慢原因的原因标注程序；随后，本发明使用原因标注程序对海量sql数据进行原因标注，得到带有标号的数据；其次，使用有监督的机器学习算法对已有标签的数据特征进行模拟训练得到分类模型；最后，根据原因标注程序中标号和具体执行慢原因的映射关系，统计得出被分类sql的具体执行慢原因；其中，原因标注程序可以自动地对sql执行慢原因进行标注，省去了人工标注执行慢原因所需消耗的时间和人力，此技术方案在大数据时代更具实用性。