1.本发明属于数据库
技术领域：
：，尤其涉及一种基于学习的索引推荐与评估方法。
背景技术：
：：2.在不改变表的模式或应用代码的前提下，合理的索引是影响数据库能否进行高效查询和更新的关键因素之一。在有索引的情况下，结构化查询语言(sql)可以通过扫描索引树的方式检索数据，降低查询时间和系统资源消耗。在复杂且动态演化中的业务系统，由于数据库查询优化器无法考虑到所有可能的执行计划，使用某些不得当的索引甚至会减慢查询的速度。专业数据库管理员在创建索引时需要考虑索引建立前后对表上所有查询的影响，但即使是他们在面对一些特定场景问题时，也需要耗费一定分析时间，专业数据库管理员一直是行业稀缺人才，随着数据库规模不断扩张，传统基于人力驱动的优化方式已无法满足现在大规模的慢查询优化。3.为了自动化这一过程，传统基于启发式规则和查询优化器的方法将查询代价和索引存储代价作为优化目标进行索引自动推荐。它将工作负载作为系统输入，对每条查询分析其sql中可做索引的属性列，构建候选索引空间，利用贪心算法和剪枝策略对候选空间进行迭代，选出使查询代价最小的索引集。利用what-if工具假设索引实际存在，避免了迭代过程中若直接创建大量索引导致的巨大开销。然后将所有单条查询的推荐索引集合进行适当的合并，作为整个负载的候选索引空间。最后，再次利用贪心算法从负载候选索引空间中选出整个负载的最优索引集合。它存在以下几个方面的局限性：一是它需要在大的搜索空间中计算出更好的索引配置，有许多候选索引和查询计划需要评估，因此会导致巨大的计算消耗。对索引候选进行剪枝可以减少搜索空间，但也带来了丢失有用索引的风险；二是工作负载中有多种物理运算符类型、多表连接和复杂的子查询。查询优化器对查询代价可能做出不准确的估计，直接添加推荐索引可能会导致性能回归，在生产环境非常危险。技术实现要素：4.为了克服现有索引推荐方法计算资源消耗大、推荐结果置信度低的问题，本发明的目的是提供一种基于学习的索引推荐与评估方法，利用机器学习获取历史数据中业务人员增删索引的专家经验从而推荐索引，并通过抽样方法对推荐索引进行效果评估，实现了高效和准确的索引推荐。5.实现本发明目的的具体技术方案是：6.一种基于学习的索引推荐与评估方法，特点是该方法包括专家经验驱动的索引推荐和基于抽样的索引评估；所述专家经验驱动的索引推荐实现基于历史数据中索引建立经验进行索引推荐；所述基于抽样的索引评估实现在抽样数据库上进行索引评估给出置信度高的推荐结果；具体步骤如下：7.步骤1：专家经验驱动的索引推荐8.a1：根据用户输入的数据库连接信息抽取对应数据库的表信息、索引信息并保存；所述表信息为表名、列名、cardinality及表行数；所述索引信息为索引对应的列名；9.a2：根据用户提供的结构化查询语言即sql语句来调用sql解析程序获取sql信息，sql信息包括抽取单列信息及多列间关系信息；10.a3：将步骤a1中的表信息和步骤a2中的单列信息进行提取并拼接，作为单列特征，将步骤a1中的索引信息作为单列特征的标签，然后训练单列索引模型；具体包括：11.a31：提取列的数据类型；12.a32：提取列在sql中出现位置；13.a33：提取列所在条件表达式；14.a34：提取列的所在函数信息；15.a35：为提取的所有特征编号并拼接，以索引信息作为标签；16.a36：将单列特征用于模型训练，训练单列索引模型；17.a4：根据单列特征构造哈希表，将步骤a2中的多列间关系信息进行提取，并与步骤a3中的单列特征进行拼接，作为多列特征，将步骤a1中的索引信息作为多列特征的标签，然后训练多列索引模型；具体包括：18.a41：根据单列特征构造哈希表，key值为单列特征标识符，value值为该特征向量具备的单列特征；19.a42：组合出现在同一sql的同一table的任意多列，将列对应的单列特征标识符进行拼接，构造初步多列特征，将初步多列特征集合命名为fn_start_train；20.a43：遍历fn_start_train，根据fn_start_train中每个初步多列特征包含的单列特征标识符，提取多列间关系信息，放入多列间关系特征集fn_merge_train；21.a44：将fn_start_train和fn_merge_train根据单列特征标识符进行横向拼接，以对应的索引信息作为标签，得到最终的多列特征fn_end_train；22.a45：将多列特征用于模型训练，训练多列索引模型；23.a5：调用sql解析程序来对用户输入的sql语句进行特征抽取，并与模型训练阶段保存下来数据库表信息进行拼接生成单列和多列特征，输入步骤a3中的单列索引模型和步骤a4中的多列索引模型进行索引推荐；24.a6：将索引推荐结果以json文件输出，文件包括sql语句及推荐索引；25.步骤2：基于抽样的索引评估26.b1：解析索引推荐结果的json文件，获取各查询涉及的所有的数据表schema，包括主键、外键和索引信息，创建测试环境库表；27.b2：从线上数据库根据用户设置的比例抽样并转储数据，导入查询中出现的数据表至测试数据库中；28.b3：在不同推荐索引配置下遍历所有查询，在验证环境数据规模下，获得查询建立推荐的索引前后的评估指标，评估指标包括执行时间、扫描行数及查询代价；29.b4：将索引评估结果以json文件输出，文件包括sql语句、评估索引及评估指标。30.本发明的有益效果包括：31.基于数据库索引的特性和对现有索引推荐算法的研究，本发明提出了专家经验驱动的索引推荐。历史数据包含了正常查询及慢查询的执行记录，以及其相关库表的模式和索引信息，可以理解为专业技术人员建立合理索引的专家经验。将关键sql语义和表列信息提取特征信息，对与单列索引和多列索引，训练多个对应的分类模型，可对一条查询或一组工作负载推荐缺失的索引，显著提高查询的效率。本发明可以有效避免传统启发式规则驱动的索引推荐算法需要数据库what-if功能，以及不同索引配置下的大量查询执行计划对查询优化器轮询导致的大量计算资源消耗；同时本发明通过不同的训练数据，可兼容不同的数据库，适用范围较广。32.基于抽样的索引评估是为了防止在生产环境直接创建索引带来的潜在安全问题，利用高效的抽样方法从生产环境抽取一定量的数据至测试环境，尽量减小对生产数据库的影响。在抽样数据库上对不同的推荐索引配置，验证单个查询或整个工作负载的查询时间、扫描行数、执行代价等信息，筛选出效果较优的索引配置作为最终的推荐索引结果。本发明结合数据库查询优化器处理查询语句的实际场景，利用抽样得到的测试环境与生产环境数据的相似性，提高了推荐索引结果的置信度，降低了建立索引带来的风险。附图说明33.图1是本发明涉及的索引推荐的数据采集信息示意图；34.图2是本发明涉及的索引推荐的模型建模示意图；35.图3是本发明涉及的索引推荐与评估方法架构图。具体实施方式36.结合以下具体实施例和附图，对本发明作进一步的详细说明。实施本发明的过程、条件、实验方法等，除以下专门提及的内容之外，均为本领域的普遍知识和公知常识，本发明没有特别限制内容。37.图1描述了索引推荐的数据采集信息，从在线源数据库实例收集的训练数据包括索引定义、表模式和历史查询。为了减少对在线数据库的性能影响，首先收集所有索引定义，然后收集索引所在表的模式，并查找访问过此索引列的sql查询语句。此外需要收集表中的行数、索引中列的不同值的数量等统计信息。收集一个列的统计信息时，由于该列上可能不存在任何索引，因此查询优化器无法直接收集到相关的统计信息。在这种情况下，需要进行全表扫描或随机抽样以获取数据。38.图2描述了索引推荐的模型建模示例，即将索引推荐转换为传统的分类问题。对于数据库中一张表而言，其中可能存在多个索引，这些索引由一列或多列组成。如果一条sql包含该表，则查询优化器可以选择这些索引来指定查询的执行计划。图2中对于一个给定的查询q1，首先为该查询中的每个表构造单列索引候选，查询q1中出现的每个列是一个单列候选索引。因此customer表有4个单列候选索引，即c_d_id、c_id、c_w_id、c_last；warehouse表有1个单列候选索引w_id。根据单列候选索引的排列组合，可以得到两列和三列候选索引。39.对于每一个单列或多列候选索引，可以生成一个特征向量。特征向量是对候选索引中这些列的sql语义信息和数据分布特征进行编码。由于单列和多列索引候选具有不同的特征维度，需要为每种索引宽度构建一个分类器。对于每个候选索引，需要提取索引所在表的统计特征，包括例如表中的总行数、列的不同值的数量以及range条件中的估计行数等。对于单列候选索引c1，它的特征向量包含在c1上定义的特征，以及它的统计特征。对于两列候选索引《c1,c2》和三列候选索引《c1,c2,c3》，除了在《c1,c2》和《c1,c2,c3》上定义的特征外，还需要添加在每个单列c1、c2和c3上定义的特征。候选索引的特征向量是一种广义的表示，它与具体的数据库无关。因此本发明支持从各种源数据库中学习索引创建知识，并将学习到的分类器应用于目标数据库。在上述索引推荐的建模过程中可能会产生误报样本，原因是即使一个候选索引中的全部列都出现在sql语句中，根据专家经验也创建了此索引，仍然存在该索引没有被查询优化器选择的可能。由于本发明不会通过询问在线数据库实例该查询执行是否使用了该索引，以此来识别这些误报样本。这些误报样本会导致推荐模型推荐了一些额外的索引，因此利用基于抽样的索引评估来过滤推荐的无效索引。40.对于单列候选索引c1、两列候选索引《c1,c2》、三列候选索引《c1,c2,c3》，特征向量分别包含下列多个维度的信息：[0041][0042]历史数据中技术人员创建的索引被视为专家经验，因此使用真实存在的索引记录来标记特征向量。如果候选索引出现在索引记录中，则将特征向量标注为正样本，否则为负样本，即候选索引是真实创建的索引，则其标签为+1，否则其标签为-1。图2中customer表上存在单列索引i(c_w_id)、三列索引i(c_d_id,c_id,c_last)，因此对应的特征向量标签为+1，其他特征向量标签为-1。[0043]本发明采用基于树的轻量级分类模型xgboost，以此获得学习任务中更好的可解释性和与dnn模型相似的准确性。由于实际生产数据库实例中的大多数索引的列不超过三列。本发明训练了三种分类模型，分别用于推荐单列、两列和三列索引。索引创建案例来自大量在线数据库，这些数据在数据库级别被分为训练和测试数据集，来自同一数据库实例的所有索引案例只出现在训练数据或测试数据中。模型精度的度量用于指导训练过程中的模型超参数调优和模型选择。在特征向量层面，通过precision、recall和f1-score来衡量模型的准确率。根据特征向量的预测结果，计算出哪些列被推荐为索引。通过将推荐的索引集与历史数据的真实索引记录进行比较，计算索引级别的精度、召回率和f1-score。[0044]基于抽样的索引评估对专家经验驱动的索引推荐所给出的索引配置，确认如果添加这些索引，是否可以提升慢查询的性能，同时报告添加的索引是否导致其他查询的性能回归。通过对在线数据库进行随机抽样得到测试数据库，测试数据库只需要包含慢查询访问的表。基于抽样的索引评估方法对在线数据库中与查询相关的表进行全扫描，并根据用户自定义的采样率ratio随机判断是否转储该行。通过多个线程将采样数据插入到测试数据库后，将相关库表上的索引同步至测试数据库。在测试数据库上，检查创建推荐索引之前和之后查询计划是否发生变化，即是否使用了推荐的索引。由于查询优化器可能会错误地估计查询成本并且不使用推荐的索引。因此需要使用“forceindex”强制优化器使用推荐的索引，执行查询并观察效果。查询性能改进是通过衡量此查询在添加推荐的索引前后执行时间、扫描行数、查询代价来衡量的。如果执行时间的优化超过用户指定的阈值，即最终推荐此推荐索引，否则丢弃。[0045]图3描述了索引推荐与评估方法架构图。部署的在线服务数据库实例为训练数据库，从中收集索引创建案例，包括表信息、索引信息、查询信息。将索引创建案例转化为特征，训练得单列索引模型和多列索引模型等分类模型。当线上业务数据库出现慢查询时，将其输入训练完成的分类模型，在线预测给出索引推荐结果，并在测试数据库上通过数据抽样进行评估，对推荐结果进行过滤筛选，最后给出创建索引语句。[0046]本发明的保护内容不局限于以上实施例。在不背离发明构思的精神和范围下，本领域技术人员能够想到的变化和优点都被包括在本发明中，并且以所附的权利要求书为保护范围。当前第1页12当前第1页12