一种数据库SQL索引动态优化方法与流程

本发明涉及一种数据库SQL处理方法，尤其涉及一种数据库SQL索引动态优化方法。

背景技术：

信息技术已成为电信行业至关重要的一种生产力，业务系统的质量直接影响到企业的生产情况。目前传统企业架构中数据库层是其中最为关键的一环，而数据库中高效的SQL执行效率将极大地提高系统的吞吐量。

当前环境考虑到不合理的索引设计将产生额外的DML语句产生的索引维护消耗，业务上线前仅安排关键字段的索引设计。在系统运行过程中往往会因为业务需求的改变从而产生大量的变种SQL，如何在系统的运行过程中动态的完成索引优化将显得尤为重要。

在此情况下，一般采取以下几种方案中的一种或者多种结合来或多或少的解决系统运行过程中的SQL索引优化问题。

请参见图1，现有技术主要有如下两种处理流程：

1)、使用Index Analyze类第三方软件

第三方软件(Index Analyze),它提供一些索引使用的监控方案进而判断索引的设计是否合理。具体监控指标与方案根据厂商设计或多或少存在些许差异。

当某类指标运行达到预设监控阀值时,结合配置严重置级别策略进行告警，保证问题响应及时性，需人工干预处理。

2)、人工巡检处理

通过人工定期进行SQL调用量、执行计划等使用情况的巡检,并根据使用情况进行人工干预处理，保证数据库的稳定以及高效。

随着业务系统的不断发展，单位时间内的业务吞吐量有大规模的提升，这样也导致了数据库中SQL的调用量直线上升，也必然导致对SQL执行效率要求的提升。因此对数据库SQL进行动态索引优化尤为重要。现有技术存在以下缺点：

1)、种类繁多，安全性差

当前互联网上各类IndexAnalyze系统大同小异，但各厂商的监控指标严谨性、及时性、准确性仍然存在大量问题，以及对各种数据库平台的支持也不是非常友好。

2)、仅起到监测作用

IndexAnalyze仅起到相关索引使用情况的监测作用，当新上线的业务SQL产生性能问题时，业务已受到影响，不能避免故障的发生。

3)、依赖经验

通过人工核查对于数据库DBA的要求非常高。数据库DBA经验越丰富，则越能保证准确性。对于数据库的变更是非常谨慎的，一旦产生人为误操作将导致故障影响范围进一步扩大。

以上技术，由于其实现的机制，都存在同样的一个问题，发生问题时，需要人工介入分析并操作，这一系列的操作，即需要大量的时间，也需要具有丰富经验的工程师手工完成处理，在一定程度上故障处理的时间极大的依赖于工程师的个人能力以及对环境的熟悉程度。

技术实现要素：

本发明所要解决的技术问题是提供一种数据库SQL索引动态优化方法，能够周期性对数据库内部的SQL语句进行调用量、谓词条件、IO、CPU等相关资源进行采集分析；根据SQL语句消耗资源的分析结果，系统动态采取索引优化变更方案，避免故障的发生。

本发明为解决上述技术问题而采用的技术方案是提供一种数据库SQL索引动态优化方法，包括如下步骤：步骤S1：通过配置定时任务，定时收集SQL资源使用情况，建立资源基线；步骤S2：对采集后的SQL资源使用情况，分析执行计划与谓词条件的索引情况；步骤S3：对分析的数据进行跟踪，如果达到优化阀值则进行相应的索引的优化实施。

上述的数据库SQL索引动态优化方法，其中，所述步骤S1中的配置定时任务包括：任务的创建、取消、更新、删除、查询和后台调度操作；设置任务为一次执行或多次循环执行，并通过过滤核心资源，将关键信息加入列表，记录在信息采集库中。

上述的数据库SQL索引动态优化方法，其中，所述步骤S1收集SQL资源的过程如下：步骤S11：收集SQL基础运行信息，调用、耗时资源，包括：调用量、执行时长、SQL版本、逻辑读和物理读；步骤S12：将SQL中的过滤条件进行拆分，对相关谓词字段进行分析，包括谓词量、谓词选择性、谓词结果集和谓词数据倾斜度；步骤S13：执行路径采集，收集SQL在数据扫描的过程中使用的数据访问方式，所述数据访问方式包括全表扫描、索引唯一扫描或索引范围扫描。

上述的数据库SQL索引动态优化方法，其中，所述步骤S2包括分析SQL的访问路径和谓词字段选择性，所述步骤S3根据谓词字段的选择性自动化建立谓词索引完成对应索引的优化，以及根据谓词字段的数据访问方式以及选择性将无效索引去除。

上述的数据库SQL索引动态优化方法，其中，所述谓词选择性f值计算如下：

其中，c1,c2，cN代表该谓词条件的记录数,再次根据该记录数去重，获得非重复值，并使用非重复值除以总记录数获得该谓词条件的选择性；当f值达到30％时，则判断根据该谓词条件过滤的数据结果集较小，并为该谓词字段建立普通B树索引。

上述的数据库SQL索引动态优化方法，其中，所述谓词数据倾斜度计算如下：

d＝distinct(Col)

f＝[d1{count(col)}，d2{count(col)}，dN{count(col)}]

其中，d为该谓词字段的唯一性数据，通过分类计算d中数据的分布情况并进行排序即可得知数据分布是否均匀，是否存在异常倾斜的情况；当谓词数据倾斜度超过预设阈值时，根据其他谓词字段建立复合索引。

本发明对比现有技术有如下的有益效果：本发明提供的数据库SQL索引动态优化方法，通过对SQL使用情况进行综合监测和分析，并根据谓词条件进行分析实现优化，大大提高SQL执行效率；在SQL发生性能问题初期即被系统干预优化，降低故障发生概率以及故障影响；SQL谓词索引分析全部为自动化完成，无需人为干预，降低人为依赖以及误操作。

附图说明

图1为现有数据库SQL索引动态优化流程图；

图2为本发明数据库SQL索引动态优化流程图；

图3为本发明数据库SQL索引动态优化系统架构示意图。

具体实施方式

下面结合附图和实施例对本发明作进一步的描述。

图2为本发明数据库SQL索引动态优化流程图。

请参见图2，本发明提供的数据库SQL索引动态优化方法，包括如下步骤：

步骤S1：通过配置定时任务，定时收集SQL资源使用情况，建立资源基线；

步骤S2：对采集后的SQL资源使用情况，分析执行计划与谓词条件的索引情况；

步骤S3：对分析的数据进行跟踪，如果达到优化阀值则进行相应的索引的优化实施。

图3为本发明数据库SQL索引动态优化系统架构示意图。

请继续参见图3，本发明主要分为四层来实现，下面详细介绍各层实现及功能。

1.初始化层：

该层主要负责任务的管理，主要有任务的创建、取消、更新、删除、查询和后台调度等操作。定义监控任务，可定义一次执行或多次循环执行，通过过滤核心资源，将关键信息加入列表，记录在信息采集库中。

2.信息采集层：

将SQL使用资源进行收集以便于进行谓词索引分析，数据主要如以下3个方面：

1、SQL采集资源,收集SQL基础运行信息，调用、耗时等相关资源。

●调用量

●执行时长

●SQL版本

●逻辑读

●物理读

2、SQL条件拆分，将SQL中的过滤条件进行拆分，对相关谓词字段进行分析。

●谓词量

●谓词选择性

●谓词结果集

●谓词数据倾斜

3、执行路径采集，收集SQL在数据扫描的过程中使用的数据访问方式，如全表扫描、索引唯一扫描、索引范围扫描等。

●全表扫描

●索引唯一扫描

●索引范围扫描

3.数据分析层：

通过分析SQL的访问路径、谓词字段选择性，以便生成对应的索引优化建议。

3.1谓词选择性分析

其中，c1，c2，cN代表该谓词条件的记录数，再次根据该记录数去重获得非重复值，并使用非重复值除以总记录数获得该谓词条件的选择性。

当f值达到30％，时，即意味着根据该谓词条件过滤的数据结果集较小，该谓词字段适合建立普通B树索引。

3.2数据倾斜度分析

为避免在大部分数据选择性良好的基础上出现少量null值类倾斜异常的情况特单独分析数据倾斜度，公式如下：

d＝distinct(Col)

f＝[d1{coumt(col)}，d2{count(col)}，dN{count(col)}]

其中，d为该谓词字段的唯一性数据，通过分类计算d中数据的分布情况并进行排序即可得知数据分布是否均匀，是否存在异常倾斜的情况。当出现较严重的数据倾斜时可根据其他谓词字段进行建立复合索引。

4.方案实施层：

当SQL调用情况以及谓词字段选择性，数据分布情况分析完毕后，方案实施层负责：

1.根据谓词字段的选择性自动化建立谓词索引，改善执行计划，提高sql响应时间。

2.根据谓词字段的数据访问方式以及选择性将无效索引去除，降低DML语句带来的索引维护问题以及降低磁盘空间消耗。

虽然本发明已以较佳实施例揭示如上，然其并非用以限定本发明，任何本领域技术人员，在不脱离本发明的精神和范围内，当可作些许的修改和完善，因此本发明的保护范围当以权利要求书所界定的为准。