上一篇介绍了第八章AI技术中“8.1 概述”及“8.2 自调优”的相关内容，本篇我们开启第八章 AI技术中“8.3 慢SQL发现”的相关精彩内容介绍。

8.3 慢SQL发现

基于历史SQL语句信息进行模型训练，并用训练好的模型进行SQL语句的预测，利用预测结果判断该SQL语句是否是潜在的慢SQL。当发现潜在的慢SQL后，开发者便可以进行针对性优化或者风险评估，以防业务上线后发生问题。

8.3.1 慢SQL发现的功能

上线业务预检测：上线一批新业务前，使用SQL诊断功能评估此次上线业务的预估执行时长，便于用户参考是否应该修改上线业务。
workload分析：能够对现有workload进行分析，将现有workload自动分为若干类别，并依次分析此类别SQL语句执行代价，以及各个类别之间的相似程度。

8.3.2 现有技术

首先，明确一下慢SQL发现的几个不同阶段，及其对应解决的问题。
阶段1：对用户输入的一批业务SQL语句进行分析，推断SQL语句执行时间的快慢，进而可以将评估为慢SQL的语句识别出来。
阶段2：对识别出的潜在慢SQL进行根因诊断，判断这些SQL语句是因为什么慢，例如比较常见的原因可能是数据量过大、SQL语句自身过于复杂、容易产生并发的锁冲突、没有创建索引导致全表扫描等等。
阶段3：对于已经识别出来的慢SQL语句的可能问题源，给出针对性的解决方案，譬如可以提示用户进行SQL语句的改写、创建索引等。
目前openGauss已具备阶段1的能力，正在推进阶段2能力，同时发布了部分阶段3的能力，如索引推荐功能。
业内对于上述第一阶段的主要实现方法大部分是通过执行计划进行估计的，第二阶段大多是通过构建故障模式库、通过启发式规则来实现的，有了上述前两个阶段的准备，第三阶段的实现往往是比较独立的。学术界对于第一阶段的研究比较多，第二阶段采用常规的构建故障模式库的方法实现已经能取得比较好的效果了，因此并不是研究的热点，而第三阶段的工作又相对独立，可以单独作为一个领域进行研究。因此，这里仅介绍业内是如何评估SQL语句执行时间的，其他两部分暂不详细展开。

1. 基于执行计划的在线SVM模型

图8-5 基于执行计划SVM模型的系统架构图

如图8-5所示，基于执行计划的在线SVM（support vector machine，支持向量机）模型包含训练模块和测试模块。
训练阶段：Data Collection模块执行作为训练集的语句，Data Extraction模块收集执行的语句特征及执行时间，包括执行计划及算子级别的信息。Model Building模块基于计划级别特征与算子级别信息分别训练SVM模型，再将两模型通过误差分布结合，生成最终的预测模型。这主要是考虑到计划级别信息具有普适性，而算子级别信息具有更高的精确性，结合两者可以在保持具有普适性的前提下，尽可能地精确预测。
测试阶段：Query Planning模块生成待预测语句的执行计划，Feature Extraction抽取这些计划中的特征，整合后投入训练阶段生成的模型中产生预测结果。
整个功能的流程如图8-6所示。

图8-6 基于执行计划SVM模型的流程图

（1） 如果场景不同时，当参数发生变化，系统不能很快感知，预测会有较大误差。
（2） 预测过程依赖待测语句的执行计划，加重了数据库的负荷，对于OLTP场景格外不适用。
（3） 每次重启都要重新训练，不能利用历史训练经验。

2. 基于执行计划的MART模型

图8-7 基于执行计划MART模型的系统架构图

基于执行计划的MART（multiple additive regression trees，多重累加回归树）模型如图8-7所示，主要包含离线训练模块和在线预测模块。他们的功能如下所示。
离线训练阶段：针对数据库每种类型的算子（如Table Scan，Merge Join，Sort…），分别训练其对应的模型，用于估算此算子的开销。此外，使用单独的训练阶段，可为不同的算子选择适当的缩放函数。最后，形成带缩放函数的不同的回归树模型。
在线预测阶段：计算出执行计划中所有算子的特征值。然后，使用特征值为算子选择合适的模型，并使用它来估算执行时间。
整个功能的流程如图8-8所示。

图8-8 基于执行计划MART模型的流程图

基于执行计划MART模型技术调优技术的缺点。
（1） 泛用性较差，强依赖训练好的算子模型，遇到例如用户自定义函数的未知语句时，预测效果会较差。
（2） 缩放函数依赖于先验结果，对于超出范围的特征值效果无法保证。
（3） 预测过程依赖待测语句的执行计划，加重了数据库的负荷，很难推广到OLTP场景中。

3. 基于执行计划的DNN模型

图8-9 基于执行计划的结构化DNN模型的算法架构图

该技术方案的系统架构图与图8-5类似，区别在于与图8-5中的Model Building模块中选择的算法不同。如图8-9所示，是现有技术的算法架构图，算法的概述如下。
该算法依然是将执行计划中的算子信息输入到深度学习网络中，从而对执行时间进行预测的。对于每个算子，收集左右子树的向量化特征、优化器代价及执行时间，输入与之对应的模型中，预测该算子的向量化特征及执行时间等。图8-9中显示了一个join操作的预测流程，其左右子树均为Scan算子，将两个Scan算子通过对应的模型预测出的向量化特征、执行时间，以及该join算子的优化器评估代价作为入参，输出join算子模型得到该操作的向量化特征及预测出的执行时间。上述过程是个自底向上的过程。
整个功能的流程如图8-10所示。

图8-10 基于深度强化学习执行时间预估流程图

上述技术的缺点。
（1） 需要通过已预测算子不断修正模型，预测过程会较慢。
（2） 对环境变化感知差，如数据库参数变化会使得原模型几乎完全失效。
（3） 预测过程依赖待测语句的执行计划，加重了数据库的负荷，对于OLTP场景格外不适用。

8.3.3 慢SQL发现采取的策略

图8-11 慢SQL发现流程图

慢SQL发现工具SQLDiag的执行流程如图8-11所示，该过程可以分为两个部分，分别是基于模板化的方法和基于深度学习的方法，下面分别介绍一下。

1. 基于SQL模板化的流程

（1） 获取SQL流水数据。
（2） 检测本地是否存在对应实例的历史模板信息，如果存在，则加载该模板信息，如果不存在，则对该模板进行初始化。
（3） 基于SQL数据，提取SQL的粗粒度模板信息。粗粒度模板表示将SQL中表名、列名和其他敏感信息去除之后的SQL语句模板，该模板只保留最基本的SQL语句骨架。
（4） 基于SQL数据，提取SQL细粒度的模板信息。细粒度模板表示在粗粒度模板信息的基础上保留表名、列名等关键信息的SQL语句模板。细粒度模板相对粗粒度模板保留了更多SQL语句的信息。
（5） 执行训练过程时，首先构造SQL语句的基于粗粒度模板和细粒度模板信息，例如粗粒度模板ID、执行平均时间、细模板执行时间序列、执行平均时间和基于滑动窗口计算出的平均执行时间等。最后将上述模板信息进行储存。
（6） 执行预测过程时，首先导入对应实例的模板信息，如果不存在该模板信息，则直接报错退出；否则继续检测是否存在该SQL语句的粗粒度模板信息，如果不存在，则基于模板相似度计算方法在所有粗粒度模板里面寻找最相似的N条模板，之后基于KNN（k nearest neighbor，K近邻）算法预测出执行时间；如果存在粗粒度模板，则接着检测是否存在近似的细粒度模板，如果不存在，则基于模板相似度计算方法在所有细粒度模板里面寻找最相似的N条模板，之后基于KNN预测出执行时间；如果存在匹配的细粒度模板，则基于当前模板数据，直接返回对应的执行时间。

2. 基于深度学习的执行流程

（1） 获取SQL流水。
（2） 在训练过程中，首先判断是否存在历史模型，如果存在，则导入模型进行增量训练；如果不存在历史模型，则首先利用word2vector算法对SQL语句进行向量化，即图8-11中的SQL embeding过程。而后创建深度学习模型，将该SQL语句向量化的结果作为输入特征。基于训练数据进行训练，并将模型保存到本地。值得一提的是，该深度学习模型的最后一个全连接层网络的输出结果作为该SQL语句的特征向量。
（3） 在预测过程中，首先判断是否存在模型，如果模型不存在，则直接报错退出；如果存在模型，则导入模型，并利用word2vector算法将待预测的SQL语句进行向量化，并将该向量输入到深度学习网络中，获取该神经网络的最后一个全连接层的输出结果，即为该SQL语句的特征向量。最后，利用余弦相似度在样本数据集中进行寻找，找到相似度最高的SQL语句，将该结果返回即为该待预测SQL语句的预估执行时间。当然，如果是基于最新SQL语句执行时间数据集训练出的深度学习模型，则模型的回归预测结果也可以作为预估执行时间。

8.3.4 关键源码解析

慢SQL发现工具在项目中的源代码路径为：openGauss-server/src/gausskernel/dbmind/tools/sqldiag。

1. 项目结构

慢SQL发现工具文件结构如表8-6所示。

表8-6 慢SQL发现工具结构

preprocessing.py

SQL预处理方法

requirements.txt

依赖第三方库列表，通过pip –r安装

main.py

测试文件集合

algorithm

项目核心代码

algorithm/sql_similarity

相似度计算方法

2. 总体流程解析

算法的总体流程在main.py中给出，根据传来的参数实例化算法模型后，进行训练、增量训练、预测等。main函数的核心代码如下所示。

def main(args):
logging.basicConfig(level=logging.INFO)
# 实例化算法模型，模板化模型或DNN模型
model = SQLDiag(args.model, args.csv_file, get_config(args.config_file))
# 训练模型
if args.mode == 'train':
    # fit训练数据，提取模板或特征
        model.fit()
        # 模型保存
        model.save(args.model_path)
    # 预测
elif args.mode == 'predict':
    # 加载模型
        model.load(args.model_path)
        # 标准化预测数据，获取结果
        pred_result = model.transform()
        # 保存输出结果
        ResultSave().save(pred_result, args.predicted_file)
        logging.info('predict result in saved in {}'.format(args.predicted_file))
    # 更新模型
elif args.mode == 'finetune':
        model.fine_tune(args.model_path)
        model.save(args.model_path)

3. 模板化算法源码解析

通过模板化方法，实现在不获取SQL语句执行计划的前提下，依据语句逻辑相似度与历史执行记录，预测SQL语句的执行时间。主要源码如下：

class TemplateModel(AbstractModel):
    # 初始化算法参数
    def __init__(self, params):
        super().__init__(params)
        self.bias = 1e-5
        self.__hash_table = dict(INSERT=dict(), UPDATE=dict(), DELETE=dict(), SELECT=dict(),
                                 OTHER=dict())
        self.time_list_size = params.time_list_size
        self.knn_number = params.knn_number
        self.similarity_algorithm = calc_sql_distance(params.similarity_algorithm)

def fit(self, data):
    # 对每条sql语句按照粗、细粒度进行标准化，生成模板
        for sql, duration_time in data:
            if not self.check_illegal_sql(sql):
                continue
            fine_template, rough_template = get_sql_template(sql)
            sql_prefix = fine_template.split()[0]
            if sql_prefix not in self.__hash_table:
                sql_prefix = 'OTHER'
            # 更新粗粒度模板框架
            if rough_template not in self.__hash_table[sql_prefix]:
                self.__hash_table[sql_prefix][rough_template] = dict()
                self.__hash_table[sql_prefix][rough_template]['info'] = dict()
            # 更新细粒度模板框架
            if fine_template not in self.__hash_table[sql_prefix][rough_template]['info']:
                self.__hash_table[sql_prefix][rough_template]['info'][fine_template] = \
                    dict(time_list=[], count=0, mean_time=0.0, iter_time=0.0)
            # 更新每个细粒度模板的执行时间、迭代时间、sql语句的计数。
            …

self.__hash_table[sql_prefix][rough_template]['info'][fine_template]['count'] += 1
…        
# 基于细粒度模板更新粗粒度模板信息
        for sql_prefix, sql_prefix_info in self.__hash_table.items():
            …

    def transform(self, data):
        predict_time_list = {}
        for sql in data:
            # sql语句不属于'INSERT', 'SELECT', 'UPDATE', 'DELETE', 'CREATE', 'DROP'任何一个，预测时间默认为-1
            if not self.check_illegal_sql(sql):
                predict_time_list[sql] = -1
                continue
            …
                # 若预测的sql所对应的粗粒度模板不存在，执行模板相似度计算方法获取与所有粗粒度模板的相似度
                if rough_template not in self.__hash_table[sql_prefix]:
                    for local_rough_template, local_rough_template_info in self.__hash_table[
                            sql_prefix].items():
                        similarity_info.append(
                            (self.similarity_algorithm(rough_template, local_rough_template), local_rough_template_info['mean_time']))
                # 若预测的sql所对应的细粒度模板不存在，执行模板相似度计算方法获取与所有细粒度模板的相似度
                else:
                    for local_fine_template, local_fine_template_info in \
                            self.__hash_table[sql_prefix][rough_template][
                                'info'].items():
                        similarity_info.append(
                            (self.similarity_algorithm(fine_template, local_fine_template),
                             local_fine_template_info['iter_time']))
                # 基于KNN思想计算sql执行时间
                topn_similarity_info = heapq.nlargest(self.knn_number, similarity_info)
                …

        return predict_time_list

4. DNN算法源码解析

训练阶段先初始化SQL向量，之后创建深度学习模型，将模型保存到本地。
预测阶段，导入模型，向量化待预测的SQL；基于向量相似度对SQL的执行时间进行预测。主要源码如下：

class KerasRegression:
    # 初始化模型参数
    def __init__(self, encoding_dim=1):
        self.model = None
        self.encoding_dim = encoding_dim
# 模型定义
    @staticmethod
    def build_model(shape, encoding_dim):
        from tensorflow.keras import Input, Model
        from tensorflow.keras.layers import Dense
        inputs = Input(shape=(shape,))
        layer_dense1 = Dense(128, activation='relu', kernel_initializer='he_normal')(inputs)
        …
        model = Model(inputs=inputs, outputs=y_pred)
        # 优化器，损失函数
        model.compile(optimizer='adam', loss='mse', metrics=['mae'])
        return model
    # 模型训练
    def fit(self, features, labels, batch_size=128, epochs=300):
        …
        self.model.fit(features, labels, epochs=epochs, batch_size=batch_size, shuffle=True, verbose=2)
    # 模型预测
    def predict(self, features):
        predict_result = self.model.predict(features)
        return predict_result
    # 模型保存
    def save(self, filepath):
        self.model.save(filepath)
    # 模型读取
    def load(self, filepath):
        from tensorflow.keras.models import load_model
        self.model = load_model(filepath)

class DnnModel(AbstractModel, ABC):
    # 初始化算法参数
    def __init__(self, params):
        …
        self.regression = KerasRegression(encoding_dim=1)
        self.data = None
    # 把sql语句转化为vector，如果模型不存在，则直接训练w2v模型，如果模型存在则进行增量训练
    def build_word2vector(self, data):
        self.data = list(data)
        if self.w2v.model:
            self.w2v.update(self.data)
        else:
            self.w2v.fit(self.data)

    def fit(self, data):
        self.build_word2vector(data)
        …
        # 数据归一化
        self.scaler = MinMaxScaler(feature_range=(0, 1))
        self.scaler.fit(labels)
        labels = self.scaler.transform(labels)
        self.regression.fit(features, labels, epochs=self.epoch)

# 利用回归模型预测执行时间
def transform(self, data):
…

8.3.5 使用示例

SQL流水的采集方法：SQL流水可以通过openGauss自带的采集工具进行采集，采集过程的性能损耗很低，一般不会超过5%，该过程可以通过GUC参数设置。
（1） log_statement = all。
（2） log_statement_stats=on。
开启参数后，会向数据库日志文件中记录具体的执行语句以及其开销。
使用方法示例：使用前，可通过如下指令获取帮助。

python main.py –help

参数说明如表8-7所示。

表8-7 命令行参数说明

参数 参数说明 取值范围 -f, --csv-file 训练、预测数据文件路径 - –predicted-file 预测结果存储文件路径 - –model 模型选择 template、dnn –model-path 模型存储文件路径 - –config-file 配置文件路径 -

使用方法示例，使用提供的训练数据进行训练，代码如下：

python main.py train -f train.csv --model-path test/

使用提供的数据进行预测，代码如下：

python main.py predict –f predict.csv –model-path test/ --predicted-file test/result.csv

使用已有的模型进行增量训练，代码如下：

python main.py finetune –f train_new.csv –model-path test/

输出样例为SQL语句与预测的执行时间。

8.3.6 总结

当前的慢SQL发现功能只是根据历史的workload信息，定性、定量地估计未来的SQL语句的执行时间。由于SQL语句的真实执行结果会受到多种因素影响，这为SQL语句的执行结果带来很大噪声，因此理论上通过本功能实现SQL语句的执行时间预估是存在一些偏差的，这也是本功能侧重定性判断的原因。对于更精确的SQL执行时间预估，可以使用8.6节提到的AI查询时间预测功能。

感谢大家学习第8章 AI技术中“8.3 慢SQL发现”的精彩内容，下一篇我们开启“8.4 智能索引推荐”的相关内容的介绍。
敬请期待。