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

发布于 2022-7-14 15:58
浏览
0收藏

 

本期诊断日主要分享内容: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代价估算。
 DBbrain诊断日 | 深入揭秘DBbrain智能优化引擎-开源基础软件社区
1.SQL解析和校验


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

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

DBbrain诊断日 | 深入揭秘DBbrain智能优化引擎-开源基础软件社区
在数据库中的视图却是如下的执行流程:
DBbrain诊断日 | 深入揭秘DBbrain智能优化引擎-开源基础软件社区
DBbrain的SQL优化功目的就是帮助数据库寻找最佳执行路径,将其执行路径优化成更为简洁和高效的视图。从SQL解析上看,查询条件字段"value"是和a关联,但a仅仅是子查询的别名。从无语义差别的角度,该查询条件是可以下推到子查询,和库表dbbrain_1直接关联。
 DBbrain诊断日 | 深入揭秘DBbrain智能优化引擎-开源基础软件社区
但是数据库自身重写功能通常具有片面性,实现并不完善。在某些特定场景下,显示的更改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%的效果,也可通过优化前后的执行计划比对进一步验证优化的效果

DBbrain诊断日 | 深入揭秘DBbrain智能优化引擎-开源基础软件社区
2、智能建议省时省力


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

DBbrain诊断日 | 深入揭秘DBbrain智能优化引擎-开源基础软件社区
3、辅助用户理解优化


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

 

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

 

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

 

新功能速递


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


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


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


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

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


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

 

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

分类
标签
已于2022-7-14 15:58:29修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐