DBbrain诊断日 | 深入揭秘DBbrain智能优化引擎

本期诊断日主要分享内容：DBbrain的SQL优化原理和实现。

前言

在之前的几期诊断日的分享中，分别介绍了如何使用DBbrain自助处理数据库主从复制延迟、CPU使用率过高、字符集不匹配的场景。本期的将为大家分享DBbrain的SQL优化原理和实现。主要看点如下：

1.深入揭秘DBbrain智能优化引擎架构及原理

2.DBbrain推出业内首个SQL优化效果对比功能

为了便于大家理解DBbrain的SQL优化功能的使用场景和设计背景，先简单聊一聊SQL性能较差与数据库性能联系——我们通常把性能较差的SQL称之为慢SQL，一般我们可通过设置slow_query_log参数设置为ON，来捕获执行时间超过一定数值（由long_query_time参数控制）的SQL语句。表现上来理解就是执行时间过长的SQL，但广义上消耗资源过多、执行计划不够优秀的SQL同样具有影响数据库性能的潜在隐患，可能只是因为资源足够空闲（紧急升配往往能够临时掩盖性能问题）或者数据量不够大，所以这几类SQL的执行时间并没有太长，但在特定场景下却会放大其对数据库性能的影响。而一般80%的数据库性能问题都是由于SQL性能所导致的，所以如何进行SQL的优化、SQL优化的效果就成为了数据库性能提升的关键因素。那么接下来就为大家揭秘，DBbrain的智能优化引擎是如何进行SQL优化的。

基于规则和代价估算的SQL优化建议

DBbrain的SQL优化引擎独立于数据库，避免对原生数据库引擎进行侵入。它的主要组件包括SQL解析和校验、基于规则的SQL重写、查询条件选择度/代价估算、SQL子句检查以及建议生成器。除此之外，Connector组件负责与目标数据库交互，同步SQL优化所需配置和表结构定义相关信息以及SQL代价估算。
 
1.SQL解析和校验

负责解析输入SQL语句，将提取涉及到的库表交给Connector组件获取表定义，并校验相关字段名、类型以及字符集（出于性能考虑，DBbrain不支持MyISAM表以及视图）。
 
2.SQL重写

数据库优化器都具有重写组件。它一般在选择索引，生成执行计划之前，通过对原SQL语句进行无语义差别的变换，使得SQL语句更加简洁，方便后续组件更好的选择执行计划。执行计划选择是在当前给定条件下尽力选择最佳执行路径，而SQL重写、增加合适的索引则是为执行计划选择创造更好物理条件。
数据库自身具有一定重写功能，因此SQL优化建议也需要识别这些规则，并通过变换将查询条件和实际库表进行关联。下面举个例子来说明一下SQL 重写的原理：
SQL在数据库中的执行路径往往和开发人员在写的结构不太相同，比如开发人员看到的如下SQL语句：

在数据库中的视图却是如下的执行流程：

DBbrain的SQL优化功目的就是帮助数据库寻找最佳执行路径，将其执行路径优化成更为简洁和高效的视图。从SQL解析上看，查询条件字段"value"是和a关联，但a仅仅是子查询的别名。从无语义差别的角度，该查询条件是可以下推到子查询，和库表dbbrain_1直接关联。
 
但是数据库自身重写功能通常具有片面性，实现并不完善。在某些特定场景下，显示的更改SQL语句，可以大幅度提高执行性能。比如：条件下推聚合子查询，exists变换为join，条件合并等。实现SQL变化的最大前提条件是无语义差别的，保证查询结果正确。这些变化是基于预先设定好的规则。
 
3.选择度计算

条件选择度计算是索引建议核心，它决定了索引字段顺序以及驱动表的选择。条件字段的选择度计算依赖于表的统计信息，并需要对库表进行数据抽样。DBbrain会默认随机抽取200~1000条数据。这些数据是应用了crc32函数的校验码，避免获取用户原始数据，用户不必担心数据安全问题。
 
4.条件/子句检查

根据MySQL引擎规则，识别出order by/ aggregate条件、project(输出字段），并合理利用索引；与此同时，识别出不合理条件或使用方式，提示用户。比如非前置like匹配，数字作用于字符条件字段等。
 
5.SQL优化建议生成

SQL优化建议包括索引优化建议和SQL重新优化建议。
 

优化前后的执行计划对比及效果评估

传统的手动优化SQL，极度考验DBA的知识储备和实战经验积累，优化后一般只能通过explain的改变来预估SQL优化效果，而大多数研发和运维目前使用的市面上的SQL优化工具更是只能根据理论分析得出优化结果。这样一来我们如何精确验证SQL优化的效果好坏？大多数情况下都只能通过执行后观察业务延迟的表象来判断（也可以采用较为复杂的测试环境变更，旁路流量的方式验证），但这样的验证方式不仅效率低下，而且在变更前、变更中、变更后均对数据库性能存在极高的风险。

但这些已经是过去式了，现在不用担心啦！腾讯云数据库DBbrain团队历经多年的技术探索和研发后，终于推出了业内第一款基于执行代价分析的SQL性能优化效果对比功能，能够在未执行变更前对变更效果进行预估，让用户能预知变更的优化效果，更加放心的根据优化建议进行变更，同时也通过此类技术的结果反馈不断优化自身SQL优化引擎的精准性。

 
在不更改用户数据库的前提下，DBbrain智能优化引擎能够对给出的SQL优化建议进行效果评估。SQL代价估算引擎在该功能中起到主要作用。通过分析SQL相关库表的统计信息、OPTIMIZER_SWITCH配置、以及索引字段区分度估算，对优化后的SQL语句待机进行整体代价估计。下面我们通过一个现网真实案例进行展示：
 
1、优化效果提前预知

DBbrain智能优化引擎通过代价对比，直观呈现出SQL优化后降低99.19%的效果，也可通过优化前后的执行计划比对进一步验证优化的效果

2、智能建议省时省力

DBbrain智能优化引擎给出的SQL重写+增加索引相结合的建议对SQL进行性能优化

3、辅助用户理解优化

为了辅助用户更好的理解优化，DBbrain还提供表结构信息的展示，让信息更加直观明了。

基于全量审计日志负载评估和优化

一般对实际系统的优化需要全局的把握，而不是仅仅针对有一个SQL语句，虽然增加索引可以提高查询性能，但同时也会增加磁盘开销，降低写入性能。针对不同SQL语句，可能给出针对某一个表的重复索引；同时我们还需要考虑SQL的执行频度，对系统的整体负载影响，有时候单个SQL的扫描行数不高，但是因频率过高也会成为主要问题点。
 
而DBbrain的评估优化是基于全量审计日志的负载，实时计算SQL扫描行数，定位主要问题SQL语句并给出优化建议，因此优化是整体的，全方位的。

新功能速递

在本文的最后，也为大家进行一下DBbrain的新功能速递。DBbrain正式发布新版，新功能包括：

1.全面支持只读实例、灾备实例，涵盖所有性能诊断和优化功能；

2.新增多种主从复制故障、异常、隐患的排查和优化；

3.全面优化用户体验，提供“用户级”和“实例级”功能；

• 用户级功能：实例概览、实例管理（实例列表、告警汇总）、全实例监控；
• 实例级功能：异常诊断、实时会话、慢SQL分析、SQL优化、空间分析、健康报告、审计日志分析。

4.SQL优化能力持续升级。

文章转自公众号：腾讯云数据库