DBbrain诊断日 | SQL优化老大难？一键召唤鹅厂专家来帮你

 

本期文章将聚焦于数据库智能管家DBbrain的最新功能“SQL优化”服务，为大家详细解读如何高效、高质的完成耗时又繁重的SQL优化工作，帮助业务持续稳定的运行，实现数据库“自治”。

 

多样的数据库优化手段

 

数据库（Database）一直以来都在业务系统中扮演着举足轻重的角色，大部分业务的稳定运行都离不开数据库的性能状态。数据库出现一个性能的抖动都有可能在业务层被放大，导致极为严重的后果。如何让数据库更高效、稳定的运行，并且持续的进行优化一直都是业务最核心的诉求之一。

我们可以简单把数据库环境下的业务优化分为5个层级。随着等级1到5的递增，性能优化涉及的改动量、风险性等都会增加。但在一些场景中，在高等级中的一小点优化对性能产生的直接影响，往往会大大超过低等级的改造。

1.SQL优化

主要手段是通过慢日志、全日志、业务层打印的超时或错误日志等方式，发现执行效率低、存在性能瓶颈或隐患的SQL语句，并根据统计信息和经验规则等方式进行优化。

2.配置优化

大多数用户在搭建数据库服务系统后仅仅停留在使用层面上，使用过程中经常遇到数据库系统性能下降的问题，由于缺少数据库管理系统性能优化的经验，用户通常都只是使用默认的模板或者一些网上流传的所谓“最佳模板”，并不能100%的适配业务逻辑，很难保证数据库的性能维持在一个较优的状态。

3.数据优化

针对数据层面的优化需要结合一定的业务逻辑，常用的解决方案包括数据库表的拆分、冗余数据的清理以及热冷数据的分离，通过这些常规的数据优化手段一方面能够使得请求响应时间缩短、资源消耗情况降低，另一方面也可以降低冲突概率，减少锁竞争、提高并发度。

 

4.架构优化

架构优化是站在更加宏观的角度来看待性能优化的问题，不拘泥于数据库中的细枝末节，而是把优化的焦点集中在了数据库的选型和架构设计上，通过前置的cache（可以是缓存数据库或者本地缓存等内存介质），利用内存的高速存取能力来实现加速请求以及横向和纵向扩展的方式，利用集群模式突破单机瓶颈，利用硬件的加持换取更高的性能。

5.业务优化

业务优化更多的是侧重于业务逻辑和变更，能够在逻辑上规避掉一些复杂查询和不合理的请求方式对底层数据库的冲击和压力。

这些性能优化方式里面，大家在日常工作中用到最多的肯定是“SQL优化”，其原因并不是因为SQL优化简单，而是因为其可覆盖的问题面很广。根据经验，85%以上的数据库性能问题都能通过SQL优化的方式解决，但SQL优化一直以来都是一个非常复杂的过程，主要体现在以下几个方面：

 

• SQL优化需要依赖于大量的系统数据，甚至是长期的历史数据，只有基于完备的信息才能给出准确的解法。一套完善的数据库监控体系以及信息收集的全面深入比较困难。
• 需要多年的数据库优化经验，以及对各种业务场景的深入了解，才能更好的作出准确的判断。
• 有些SQL优化的思路和优化的方式甚至要深入到数据库引擎层代码进行，更增加了技术难度。

“SQL优化”是腾讯云数据库智能管家DBbrain的核心服务之一 ，它以SQL语句作为输入，由DBbrain完成性能分析并提供优化建议，操作界面100%还原数据库终端的“习惯性”操作体验，同时用户不必精通数据库优化领域专家知识，即可轻松获得SQL优化建议，并且能够在线执行和变更，完成SQL优化的最后一公里，帮助业务有效的提升数据库使用性能。

下面我们针对DBbrain的“SQL优化”进行详细的解读。

 

DBbrain的“SQL优化”

 

1.不仅仅只有索引建议

在SQL优化的众多开源组件和产品中，基于规则的推荐索引方式被广泛使用。而DBbrain的“SQL优化”服务不仅仅能够识别出基本的索引缺失场景，还能覆盖多种SQL优化场景：

 

• 在多种索引方案中选取最优解

与传统的规则优化方案不同，DBbrain中的SQL优化服务采用的是基于代价模型方式实现，也就是说其优化的思路更加贴合数据库引擎的处理模式，最终会以执行代价（cost不完全等同于执行时间，详见下文）的方式量化所有可能推荐候选项，最终做出最优解的推荐。

 

• 自动修复异常执行计划

SQL 执行计划选择错误，这类问题的危害是很大的，常常导致业务突然卡顿、数据库过载等不良后果。原因大多数是因为SQL的复杂程度、统计信息更新不及时等，通常应急处理方式往往是利用hint或者force index，而“SQL优化”服务能够自动修复此类问题探测出更好的执行计划。

 

• 多场景SQL重写建议

SQL优化服务会将SQL成在引擎中执行的真实路径，帮助其寻找最佳执行路径，将其执行路径优化成更为简洁和高效的视图。比如：条件下推聚合子查询、exists变换为join、条件合并等。实现SQL变化的最大前提条件是无语义差别的，保证查询结果正确。

2.基于规则和代价估算的SQL优化

上文提到过，DBbrain中的SQL优化服务采用的是基于代价模型方式实现，一方面使得优化建议有量化的标准，能够更为精准；另一方面也能够在未执行变更前对变更效果进行预估，让用户能预知变更的优化效果，更加放心的根据优化建议进行变更，同时也通过此类技术的结果反馈不断优化自身SQL优化引擎的精准性。

而这里所说的“代价”，即cost，可以看做是SQL执行过程中在Server层和engine层中的资源消耗（包含了io、cpu、memory等）。而在Jion计算时不仅要考虑condition,还要考虑condition上的filter。

在Server层中主要的开销由计算符合条件的行的代价（行数越多，此项代价越大）内存临时表的创建代价、内存临时表的行代价、键比较的代价、内部myisam或innodb临时表代价等维度组成。

在engine层中主要的开销由从磁盘读数据的代价（对innodb来说，表示从磁盘读一个page的代价）、从内存读数据的代价（对innodb来说，表示从buffer pool读一个page的代价）等维度组成。

3.100%还原数据库终端“习惯性”体验

SQL优化服务，不仅能够为用户提供SQL优化的专家级别建议，同时也涵盖了数据库管理的众多功能，可以实现在线库表结构查看、SQL执行和变更等，帮助用户完成SQL优化的全链路闭环。DBbrain为用户打造了100%原始数据库终端的交互体验。

 

• 提供表，索引等数据库对象的浏览和定义查询；
• 支持 delimiter，批量执行复杂 SQL 脚本；
• 登录会话保持长连接，提供原生 SQL 交互；
• 跟踪会话，回滚长时间未提交阻塞性事务;
• SQL 执行关系密切属性展示

a.会话ID (连接线程ID)：若需要观察会话状态或通过kill命令结束会话；
b.自动提交：用户登录成功后，会话默认开启自动提交。在编辑器中可以通过 "set autocommit = 0" 命令关闭会话自动提交的方式来开启事务；
c.事务开启（处于事务中）：显示的开启事务 "start transaction"，或者关闭会话自动提交后，后续SQL执行将处于事务中状态，直到显示的 "commit" 或者 "rollback";
d.事务隔离级别：用户登录成功后，会话默认开启设置隔离级别 "READ COMMITTED";
e.锁住行数/锁住表数：会话锁的状态，提示用户当前会话对数据库的影响；
f.自动回滚时间：若会话开启事务后10秒内为提交或回滚，系统尝试检查改事务是否对系统造成阻塞；若存在block其它会话的现象，则自动回滚改会话，防止用户忘记提交事务给系统造成严重影响；

• 事务状态监控

• 支持常用运维SQL快捷执行

支持参数/指标、用户信息、其他类的常用运维SQL快捷执行，例如：
show global variables where variable_name in ('')  ##查看参数值

show slave status   ##查看主从复制状态信息

flush binary logs  ##切割log日志

select * from information_schema.innodb_lock_waits;  ##查看当前innodb引擎锁信息，排查锁和堵塞情况

 

4. 安全加固，避免数据库异常风险

• 用户数据库账号鉴权登录，支持SQL 执行

DBbrain 的“SQL优化”服务将数据库问题分析和定位的思想贯穿于各个环节。在用户账号登录失败时，会直接将原因清晰的提示给用户，比如用户名、密码错误、账号不存在等；

• 监控执行 SQL 语句，提示危险操作

人为导致的数据安全危机占数据安全故障总数的70%。而其中误操作（指本意并不想破坏数据库系统，但是由于技术积累经验不够或疏忽引发了数据安全故障）导致的故障占到了人为故障的80%以上。网上一直以来都个脍炙人口的段子“从删库到跑路”来调侃这一现象。在SQL优化服务中执行SQL语句时，DBbrain会自动识别高危SQL和异常请求，提示用户执行风险，并可做到中断操作，避免用户误操作数据库。
 

真实案例分享

 

最后为大家分享一个业务使用SQL优化服务来解决slow sql的真实案例：

SQL优化建议示例：

SELECT d.id, d.name
FROM sys_task d
WHERE EXISTS (
    SELECT *
    FROM sys_task_item o
    WHERE o.target LIKE '25100016%'
        AND o.parent = d.id

 1. 编辑器中可以直接点击“执行计划”快捷键（或使用 explain 命令）查看该 SQL 的执行计划。从执行计划中可以看出该 SQL 存在 DEPENDENT SUBQUERY, 即对外表每一条记录，都会执行一次子查询。
 

2. 点击 SQL 优化按钮，即可立即看到 DBbrain 对该 SQL 的优化建议。其中包括索引和重新建议， 右上角的优化代价估算数据，可以直观的看到优化效果：

其中索引建议指出了两个表的索引：

• systaskitem 表给出了（target, parent) 联合索引参考：target 字段为 like 全匹配查询字段，parent 为覆盖索引字段。
• 判断子查询为驱动表后，parent 字段关联 sys_task 的 id 字段，因 id 为主键，无需新建索引。

除此之外，重写建议指出将 exists 查询转换为 join 后能避免 DEPENDENT SUBQUERY， 性能更加：

3. 现在我们通过编辑器来验证 DBbrain 给出的优化建议，首先创建索引；

4. 再次查询原 SQL 语句执行计划，无效果后，验证重写建议，从该执行计划中我们看出 DEPENDENT SUBQUERY 变成了 Derived，整体扫描行数也大幅度降低。验证了优化建议中的代价估算。

接下来我们将会持续推出数据库智能管家DBbrain的系列文章，深度解读DBbrain最新版本的功能，包括热点更新、SQL限流、空间分析等，敬请关注。

 

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