简介：第十一届中国数据库技术大会（DTCC2020），在北京隆重召开。在 12.23 日性能优化与 SQL 审计专场上，邀请了阿里巴巴数据库技术团队高级技术专家梁高中为大家介绍 DAS 之基于 Workload 的全局主动优化实际。SQL 主动优化是阿里云数据库自治服务重要自治场景之一，该服务撑持阿里巴巴团体全网慢 SQL 的主动优化，目前已累计主动优化超 4900 万慢 SQL。阿里在构建这一能力过程中有教训也有教训，冀望从基于 Workload 的全局优化能力构建历程、智能化主动优化闭环实际两个方面和大家分享。

SQL 主动优化是阿里云数据库自治服务重要自治场景之一，该服务撑持阿里巴巴团体全网慢 SQL 的主动优化，目前已累计主动优化超 4900 万慢 SQL。阿里在构建这一能力过程中有教训也有教训，冀望从基于 Workload 的全局优化能力构建历程、智能化主动优化闭环实际两个方面和大家分享。

演讲嘉宾简介：

梁高中，阿里巴巴数据库技术团队高级技术专家，2017 年退出阿里巴巴团体，目前负责阿里巴巴阿里云数据库自治服务研发负责人。退出阿里巴巴前，曾就任于 IBM，华为等，领有 12+ 年的数据库产品、数据库优化教训，曾负责数据库优化专家系统，跨源跨数据中心联邦数据库等开发团队负责人。

以下内容依据演讲视频以及 PPT 整顿而成。

本次分享次要围绕以下三个方面：
一、SQL 优化场景
二、外围诊断能力构建
三、主动优化闭环

一、SQL 优化场景

1. SQL 优化挑战

===========

数据库诊断优化是进步数据库性能和稳定性的关键技术之一，SQL 优化是其中至关重要的一环。目前约 80% 的数据库性能问题可通过 SQL 优化伎俩解决。SQL 优化目前还是面临着很多挑战，首先，SQL 优化须要基于多方面的数据库领域专家常识和教训。而且 SQL 优化耗时沉重，当面临如阿里这样的大规模的业务场景时，SQL 继续优化充斥挑战。下图中有一个基于实在业务数据所画出的，随工夫变动的数据库慢 SQL 趋势图
，T1 代表着发现数据库实例因慢 SQL 造成性能异样的工夫点，而 T2 示意优化过程完结，复原常态工夫点。那么 T1 越短示意发现性能异样的消耗工夫越少。其次 T2-T1 工夫是异样解决时长，如果解决工夫过长，一方面会重大影响业务，另一方面大大增加故障危险。

1. SQL 优化三大场景

=============

如果将 SQL 优化性能提供给用户，次要波及三种场景。首先是单 SQL 工具辅助诊断。用户能够抉择以单 SQL 为输出，辅助诊断工具会依据给定 SQL 及相干环境信息，给出优化倡议（改写、最优索引倡议等），最大化减速查问。还有基于负载全局辅助诊断工具，次要以 Workload 负载为优化单位，综合思考 Workload 中影响整体性能的特色，实现负载整体性能最大化晋升同时最大化升高空间耗费。这两个场景以辅助决策形式，为用户提供 SQL 诊断和优化。还有一种场景是主动 SQL 优化，通过构建欠缺的自动化流程，实现问题 SQL 辨认、优化倡议生成、评估主动上线，后续跟踪、收益计算的全自动化流程。

二、外围诊断能力构建

反对 SQL 优化，就须要对外围诊断能力进行构建。那什么是外围诊断能力？即针对问题 SQL，给出十分精确的倡议。用户通常会遇到上面几种 SQL 优化问题。

1. 单 SQL 优化诊断

============

SQL 优化的实质是创造条件，发现能够晋升的点，如 SQL 改写，创立 SQL 索引等，从而让数据库优化器抉择最优或者次优的 SQL 执行打算。下图两头外围地位的是 SQL 优化引擎，两边是从外围能力衍生出的对外场景，右边是对外提供的 SQL 主动优化的闭环，左边是为用户提供的 SQL 优化倡议。那么单 SQL 优化诊断能力的构建面临几个次要的问题，首先是应该采纳哪种优化举荐算法？是基于规定形式还是基于代价模型形式？针对 WHAT-IF 内核能力缺失的数据库，应该如何抉择？第二点，足够覆盖度的测试集，既如何构建一个宏大的测试案例库用于其外围能力验证？领有足够覆盖度，因为精确的测试案例库往往是外围诊断能力构建过程中至关重要的一环。第三点，如何在大规模业务场景下提供诊断服务能力，阿里须要服务于云上几十万级的数据库实例的 SQL 优化诊断，那么如何实现简单的计算服务服务化拆分，计算服务的横向伸缩，最大化的并行，资源拜访分布式环境下的并发管制，不同优先级的无效调度打消隔离，峰值缓冲等等？第四点，如何让 SQL 诊断能力继续改良。

单 SQL 优化诊断 —— 优化举荐算法抉择·面临挑战

第一类举荐算法是基于规定式的，其显著的特点是基于当时编辑好的规定来优化。第二类是基于代价评估形式。下图左侧是目前传统商业化最优索引举荐引擎架构，SQL 导入之后，对其进行剖析，生成候选索引。而后通过代价评估，这时会通过数据库服务器 WHAT-IF 能力取得这些候选索引的代价。基于 WHAT-IF 接口返回的后果进行代价评估，最初进行最终的索引合并择优。这是传统数据库中基于代价评估的最优索引举荐流程。然而，对于例如 MySQL 这样的数据库引擎，这个过程中还是面临几个挑战：
挑战一：在 MySQL 中 WHAT-IF 性能是缺失的；
挑战二：MySQL 中没有残缺的统计信息可应用；

因而须要对此架构进行优化，既在 SQL 引擎和数据库服务器间加一个内置优化器，通过内置优化器提供 WHAT-IF 性能。但这种架构仍然会面临几个挑战：

挑战三：如何最大限度放大两个优化器的差距；
挑战四：内置优化器中的统计信息与 MySQL 中的统计信息存在差别，那么应该如何放大或者优化它们之间的统计信息的差别？

单 SQL 优化诊断 —— 优化举荐算法抉择·基于代价评估形式

首先在内置优化器局部，阿里会在物理打算根底上进行代价评估，而后从中抉择。这里与传统数据库中的优化器不同点在于退出候选索引、SQL 改写的考量。另外，优化器是基于统计信息进行代价计算，因而在统计信息问题上采纳了自适应采样算法，自适应采样实现在指定误差范畴内自适应决定数据采样量。还须要留神的一点是数据采样的过程不能对指标数据库实例造成太大的压力。

单 SQL 优化诊断 —— 足够覆盖度的测试集·整体思路

为了保障 SQL 优化引擎笼罩足够全面，那么就须要足够的测试集。抉择测试集时会面临三个问题，首先在抉择的测试集中要蕴含什么样的测试案例？第二点，多少测试案例可能证实曾经足够全面？第三点，目前 SQL 优化引擎的能力在什么地位？测试集的抉择之所以艰难是因为影响 SQL 优化的因素太多，如何让这些特色一一映射到测试案例也是较为宏大的工程。还有，测试案例设计须要专业知识且信息量大，对于繁多测试案例设计也须要专业知识且测试案例中携带的信息量大。

测试案例覆盖度剖析报告是通过下图右侧的流程来生成，首先是剖析影响 SQL 优化的因素，将其合成为多维度的测试案例特色集。之后通过特色形式化形容，生成测试案例形式化特色库。之后借助阿里丰盛的业务场景，收集线上全量 SQL 及全量慢 SQL。而后联合形式化的特色，抽取线上测试案例，生成测试案例库。最初联合测试案例运行零碎和测试案例剖析工具，评估测试案例覆盖度，生成剖析报告。整个过程中首先是在对多维度特色进行形式化转化，而后通过线上资源构建通往引擎测试集的桥梁，另外，对引擎测试集构建查漏补缺的一把尺子。

单 SQL 优化诊断 —— 足够覆盖度的测试集·测试用例特色化

下图展现了测试用例特色化的构造。首先从影响索引抉择的因素登程，列出这些因素。而后将 SQL 分为 Single Table 和 Multi Table 两个场景，别离从影响因素往下分 SQL 语句。再通过三种场景，实现特色集到能力级的映射。

这三种场景别离是 L1、L2、L3。L1 反对对外围标签谓词局部、聚合排序局部做全排列，保障非核心标签被笼罩，对谓词聚合排序做粗粒度排列组合。L2 包含对 LIMIT 的反对、NOT 谓词、聚合反对、函数反对、OR 谓词的反对、两表的 INNER JOIN、单表或两表的 UNION、SUBQUERY 反对、隐式转换等。L3 包含三表到五表的 INNER JOIN、UNION、SUBQUERY、LEFT/RIGHT JOIN、NATUAL JOIN 等。

单 SQL 优化诊断 —— 大规模诊断能力与数据驱动

反对大规模的业务场景的诊断服务，SQL 优化策略的实际还须要实现很多的事件。首先对计算服务进行拆分、保障计算服务横向伸缩、还要无效保障并行采样效率、管制资源并发拜访、打消优先级调度隔离、缓冲业务峰值。这样能力满足在线上反对大规模业务场景的 SQL 优化的利用。

1. 基于 Workload 全局优化

==================

下面始终在探讨对单 SQL 的优化策略，那么从反对业务角度而言，还是须要从全局登程，做全局优化。全局优化是以 Workload 负载为优化单位，综合思考 Workload 中影响整体性能的特色，实现负载整体性能最大化晋升，同时最大化升高空间耗费。如下图左侧，从全量 SQL 中提取 Workload 负载状况，通过 SQL 全局优化引擎，在思考存储约束条件 S，以及老本约束条件 C 的状况下，输入须要创立的新索引、须要改写的新索引、须要删除的新索引、并提供 SQL 改写倡议。

下图左侧的表格里是一系列简略的 SQL 语句和 Workload 特色，包含 INSERT 语句，SELECT 语句，在每个时间段内执行次数。如果从单 SQL 优化的角度，会举荐 SQL2-SQL6 的四条优化语句。然而从 Workload 全局优化角度思考会举荐两项 SQL 优化。Workload 全局优化相比与单 SQL 优化整体 RT 降落了 14.45%，索引空间节俭了 50%。

三、SQL 主动优化闭环

1. SQL 主动优化闭环 —— 实际成果

=====================

SQL 主动优化闭环指的是从问题 SQL 辨认到基于 Workload 全局优化倡议主动生成与评估、优化上线再到量化追踪评估的全自动优化闭环。主动优化闭环将人工的被动式优化转变为以智能化为根底的主动式优化。下图左侧展现了整个 SQL 主动优化闭环的几个要害优化节点。首先是继续 24 小时的跟踪，进行指标异样检测和 Workload 异样检测，发现异常点。之后通过 SQL 优化引擎，给出优化倡议。如果用户驳回主动优化倡议，则灰度上线。如果不驳回，则须要通过智能压测验证，再到灰度上线，而后进行优化成果跟踪。
阿里实现了 SQL 优化的全自动化闭环，主动 SQL 优化持续保持数据库实例运行在最佳优化状态，目前阿里外部主动优化了 4900 万慢 SQL，全网慢 SQL 显著降落了 92%，全网慢 SQL 举荐率达到了 75%。主动优化闭环在云上辅助自治了 30 万多的服务实例，全网实例月增长率达到 90%。SQL 主动优化闭环心愿从规模性、精准性、安全性、全面性、联动性等方面继续优化晋升，服务更多用户。

1. SQL 主动优化闭环 —— 生成基于压测的优化收益报告

==============================

下图左侧是基于压测的优化收益报告。依据 SQL 优化引擎生成的 SQL 优化的倡议，选取用户实在的负载数据状况，进行压测。压测实现之后生成在实在的场景下对优化倡议的综合评估，剖析优化收益。

1. SQL 主动优化闭环 —— 演示复盘

=====================

SQL 优化为用户提供了丰盛的测试场景，基于 SQL 主动优化只是其中一个场景。那如何将 SQL 主动优化与其它测试场景混合到一起？这又将产生什么微妙的成果？同时能够解决哪些问题？
下图展现了随工夫变动的数据库性能变动图，以及过程中 SQL 主动优化做的事件。图中黄色线条是沉闷会话数，深蓝色线条示意 CPU 利用率，浅蓝色线条是 IOPS 利用率。第一个阶段是橙黄色局部，既在 2020 年 9 月 3 日 21:06 数据库出现异常，此时能够 1 分钟内发现异常、2 分钟内定位异样，并主动发现 SQL 限流，而后限流失效，黄色沉闷会话数回归原位，深蓝色 CPU 利用率降落，业务恢复正常。到第二阶段绿色局部 SQL 主动优化启动，在 2020 年 9 月 3 日 21:17 发动异样 SQL 优化诊断，紧接着优化索引变更上线，索引变更完结，进行 24 小时跟踪，而后解除限流。随即推出规格升配（Autoscaling）倡议，依据负载的变
化降级数据库规格。

作者：stromal
原文链接
本文为阿里云原创内容，未经容许不得转载