本文根据社区第110次在线分享整理而成。 文末还有好书赠送~算法

讲师介绍sql

鼎骏数据库

新聚网络首席性能优化专家性能优化

SQL审计产品经理微信

本次分享的内容是基于SQL优化的。 以一条极慢的SQL为例，快速解读SQL执行计划，如何从执行计划、统计信息和问题中找到SQL执行缓慢的根本原因，探究性能杀手操作。 、如何进行逻辑重写让SQL腾飞，等多个维度去分析，最终优化极慢的SQL语句。 希望能给别人带来启发，和大家一起讨论SQL优化方法。 IDE

另外，还简单介绍了两个解决SQL优化难题的工具：10053和SQLT。 尤其是SQLT，在机器耗尽的过程中往往能取得奇效。 建议大家花时间研究一下SQLT工具。 最后对本次分享进行总结和反思：分享SQL相关内容以及SQL最佳实践功能。

大纲下方：工具

从非常慢的 SQL 开始

这条极其缓慢的 SQL 执行预计需要 12 个小时以上，并返回数百万行数据。 首先，当我们处理SQL优化问题时，我们至少需要做以下两件事：

了解SQL的结构：SQL中使用了哪些语法，以及这些语法是否经常导致性能问题，例如标量子查询的滥用。

获取执行计划：执行计划反映了SQL的执行路径，直接影响SQL的执行效率。 如何发现执行计划中的问题是SQL的关键。

让我们言归正传，首先揭开 SQL 速度极慢的神秘面纱：

这条语句实际上是一个查询表，有各种复杂的子查询。 当我第一次看到这个子查询时，我基本上明白问题出在哪里了。 我们来尝试一下，看看执行计划：

一旦得到这样的执行计划，其实很容易发现问题：

（1）分析指标问题：Rows，即每一步都很小，说明每一步返回的结果行数不多。 这是值得怀疑的。

(2)因为数量不多，所以进行了一系列的Loops操作。 我们知道，对于NL操作，驱动表通常会返回少量的结果行，而驱动表是有索引的，最终返回的结果也比较少（通常最多几千行），效率会很高。

以上两点值得注意：如果准确的话，那么取一系列Loop的执行计划应该不会有太大问题，但是如果不准确怎么办？ 这是个大问题。 这和一些初级开发者的想法是一样的。 他们经常喜欢使用循环来处理数据。 如果循环次数很少，也没关系。 如果循环次数很多，就会很慢。 循环操作完全取决于循环次数。 从SQL执行计划来看，取决于驱动表返回的结果行数。 显然，这不适合大规模数据操作。

（3）ID=1中有一个，这个子操作是ID=15~18的全表扫描。 但执行计划却存在很大问题。 当然，这里的问题一定是2个或者更多子节点的运行。 如果是单节点的话，就只是简单的过滤条件。

对于普通的SQL优化，需要分析SQL的语法结构和语义，解释SQL的执行计划。 基于SQL执行计划，分析执行计划中的问题并执行SQL，基本上可以解决大部分SQL优化问题。

当然，从我个人的理解，SQL优化不仅需要强大的逻辑思维、正确的理论指导、熟练掌握各种SQL语法、熟悉索引的使用、了解CBO相关内容，还需要从全局的角度进行控制：物理模型设计和具体业务分析。

快速解读执行计划

快速解读执行计划要点

SQL执行计划作为SQL优化的关键，必须好好利用。 经常看到开发人员喜欢使用PL/SQL等工具来查看执行计划。 需要提醒大家的是，这个内部调用是Plan For，可能不够准确，尤其是有绑定变量的时候。 最重要的一点是，分析长SQL执行计划几乎是不可能的。 我还是喜欢文本型的执行计划，尤其是真实的执行计划。 能够获取A-ROWS、E-ROWS等指标的执行计划可以让我全景式地看到执行计划中的问题，尤其是对于速度极慢的执行计划。 SQL也可以运行几分钟，中断一下，获取一些信息来辅助判断。

执行计划要点如下：

快速解读执行计划示例

上述执行计划的入口点是ID=6（全表扫描），返回行号1，然后进行ID=7的Loops操作。 详细内容请参见分析部分。

各种操作之间的关系是由各种因素触发的，不正确的操作会导致使用循环连接而不是HASH JOIN。 往往入口处出现问题，导致后续的执行计划全部出错。 因此，理清各个步骤之间的关系，有助于找到影响问题的根本步骤。

明确执行计划顺序有助于了解SQL内部执行路径，通过实际的执行状态判断不合理的步骤操作。

这里的条目是ID=6的全表扫描，返回的行是1行，不准确。 显然，找到入口问题已经可以解决部分问题了。

从执行计划看SQL效率低下的根本原因

找出问题的根本原因后，逐步解决。

第一分析：解决ID=6步估计不准确的问题。

统计和

解决估算不准确的问题

今天的问题是转向表统计信息的准确性，特别是谓词计算的统计信息的准确性。

解决估计不准确的问题——扩大统计范围

发现使用size auto和size对于收集直方图没有影响。 执行计划中的查询条件估计返回的行数仍然是1（实际上返回了2000万行）。 怎么解决？ 卡的计算与谓词密切相关。 查看谓词：

(, 1, 3) 不在 ('147','151', …)

该怎么办？ 万千思绪，突发灵感！

提示：(a,)等即可。

有没有更好的办法？

突然想起11g有一个统计收集的新特性：扩展统计收集。

exec .(=>'xxx',=>'',=>'for ((, 1, 3)) size ',=>10,=>false,=>true,=>10);

一旦收集到扩展统计信息，执行计划如下：

统计信息还存在很多问题。 对一张表统计信息的收集，尤其是自动收集，可能并不一定能让所有相关的SQL找到最佳的执行路径，尤其是SQL条件复杂、数据倾斜、表类型定义不准确等情况。在这种情况下，特别是使用复杂条件时，CBO无法准确计算谓词对应的卡片，或者类型定义不准确。 它最初用于日期，但内部所有内容都需要转换为数字以计算选择性。 显然，列类型是随机定义的。 也存在问题。 因此，有必要对收集到的统计信息进行有针对性的修正。

解决估计不准确问题 - 有关统计的问题

统计采集比例高并不意味着对应谓词的特征可以转化，而且统计方面还有很多算法限制和不完善的地方，比如11g的扩展统计有待继续完善，12c也有很多统计完善的特征。 所以，并不是比率低就不好，而是比率高就好！ 统计信息的收集必须满足核心SQL的执行效率。 在一定程度上不需要过多关注非核心SQL，因为统计信息很难满足所有相关SQL的最优执行。

直方图有很多局限性。 12c之前，只有频率直方图和轮廓直方图两种，无法准确表示很多值的分布，因此存在很多局限性。 因此，12c 又增加了两种类型的直方图：最高频率直方图和混合直方图。 另外，直方图有只存储前32个字符的限制。

显然这是错误的。 直方图仅反映数据的分布情况。 如果数据的分布正确，就可以更准确地查询对应的谓词，从而影响执行计划，因此对于整个表来说也是有用的。

例如，删除原始直方图很可能会导致更糟糕的执行计划。 因此，通常使用size来进行更新，除非确认某些直方图需要修改。 此外，谓词与统计信息密切相关。 一旦针对某些谓词条件收集了统计信息，计算可能不准确。

CBO的内部算法非常复杂，并且存在很多bug。 如果遇到问题，要大胆并多疑。

具体问题具体分析，无论是自动采集还是自己写脚本，都需要长期的实践总结。 对于复杂的系统，需要采样率和许多自定义设置。

选择性内部计算需要转换为数字：CBO内部计算选择性会先将字符串转换为RAW，然后将RAW转换为数字，从左边开始。 如果字符串的唯一性很好，但是计算成数字后的唯一性很差，就会导致执行计划错误。 这时候还需要收集直方图。

不，除非你非常喜欢研究，否则只会弊大于利。 只需了解各种 JOIN 算法、查询转换特性、索引和其他效率相关的内容即可。 COST并不是最重要的指标。 我们应该关注高效的SQL操作所需的执行路径和执行方式。 是否可以及早过滤大量数据。 JOIN方法和顺序是否正确，是否可以创建高效的访问对象等。

发现性能杀手

性能杀手机制

造成查询转换时，如果不能转换子查询消除子查询，就会走。 Go表明子查询是由表结果驱动的，类似于循环操作！ 显然，驱动次数越多，效率就越低！

查询转换是生成高效 SQL 执行计划的重要步骤。 如果查询转换做得不好，很多后续的执行路径都会被阻塞。 掌握查询转换机制对于如何编写高效的SQL和调优SQL非常重要。 你理解得越深入，你就越了解CBO。

以下是CBO组件图。 熟悉相应的组件对于 SQL 优化是必要的：

我将建立一个HASH表来保存输入/输出对，以减少以后子查询的执行次数。 这是与纯 Nesed 循环操作的典型区别，例如来自 a where a。 In( b.staus from b...). 如果之前已经检查过，那么以后就不需要再次执行子查询了。 相反，结果是直接从保存的哈希表中获取的。 这减少了子查询执行的次数并提高了效率。 也就是说，如果子查询关联条件有很多重复值，还是有一定优势的，否则就是一场灾难！

如果走的话，子查询是由子查询外部的结果集驱动的，即子查询最后执行。 但在实际应用中，有时为了提高效率，应该先执行子查询。 在这种情况下，可以使用提示。

性能杀手机制示例

执行计划如下：

从执行计划可以看出，多个子节点通常具有以下特点：

自动生成绑定变量：B1，因为需要进行循环操作

转换成

因此，当我稍后看到带有自动生成的绑定变量的执行计划时，它们都是类似的操作，例如标量子查询和相关子查询。 如果要优化，就需要消除（类）来进行优化。

这里的例子实际上是一个 CBO 限制：

换句话说，查询需要被重构和消除！ 慢的根本原因是这里有7万多行，但是打印的执行计划只执行了116行！ ID=3~6的执行次数取决于ID=2的结果行数。 ID=3~6的全表扫描次数太多了。

逻辑重写让SQL腾飞

逻辑重写——构建高效的HASH JOIN而不是低效

完全重写——HASH JOIN重写思想，消除OR扩展

追根溯源，从SQL的含义出发，上面的含义是ERR表的前8,9,10,11位用.进行匹配，对应的匹配长度正好是8,9,10， 11. 显然，语义可以这样重写：

ERR表与该表相关联。 ERR 的前 8 位。 与 8-11 之间的前 8 位长度完全匹配。 在这个前提下，就像||'%'。

现在让我们彻底改变多个OR子查询，让SQL更加精简、更加高效。

完全重写——消除OR扩展HASH JOIN，让SQL腾飞

按照上一节的思路，重写SQL如下：

执行计划如下：

两个工具提高困难 SQL 优化的效率

两个工具提高困难SQL优化效率——10053分析生成执行计划的原因

让我们使用 10053 来探索优化器行为来研究这个问题。

******************************

基于成本

******************************

SU：查询块 SEL$1 (#1) 中的查询对 有效。

对于查询块 SEL$3 (#3)

RSW：对于 SEL$3 无效 (#3)

。

在查询块 SEL$2 (#2)SU 上：那不是。

SU：在查询块 SEL$2 (#2) 上。

SU：3 SEL$3 (#3)

SU: SU : 在带有 rowid 的视图中。

苏：。

两个工具提高SQL优化效率——SQLT找出正确的执行计划需要设置的参数

执行计划如下所示：

显然，这两者有问题，按理说应该使用ANTI JOIN。

我们来看看使用SQLT来查找问题。 我们先看一下SQLT的介绍：

运行时只需要调用该方法即可提高效率，无需实际执行SQL：

可以看到与对应的隐式参数mup设置有关，这是一个与子查询的查询转换相关的隐式参数。

修订后的未来执行计划：

走回ANTI JOIN，对。 终于从无法跑到几秒就跑完了。 其实还是可以优化的，但这已经不是最重要的了！

SQLT 的一些限制：

SQL 思考

通过AWR、ASH、SQL CHECK S等主动发现有问题的SQL、用户报告性能问题时DBA介入等，通过分析SQL的执行状态制定SQL优化目标。

工具，sql*plus，，1004六，1005三，.sql等

使用包来管理统计信息，涉及系统统计信息、表、列、索引、分区等对象。 统计信息是SQL能够遵循正确执行计划的保证。

索引、分区等重要访问结构可以快速提高SQL执行效率。 表本身存储的数据，如碎片过多、数据倾斜严重、数据存储分散度大等也会影响效率。

,_adj, ,ed等对SQL执行计划影响较大。

比如11g的ACS、path、SQL查询缓存等。一些新特性可能会带来问题，应谨慎使用。

SQL语句结构复杂，语法不合理。 例如，使用 UNION 而不是 UNION ALL 可能会导致性能低下。

无法收集准确的统计信息，无法正确执行查询转换操作等，比如SEMI JOIN、ANTI JOIN和or结合起来就会导致or操作。

主要涉及到设计问题。 例如，如果一个应用程序在业务高峰期运行，那么它实际上可以运行在相对空闲的状态。 表、索引、分区等设计不合理。

SQL 最佳实践：

SQL性能管理平台

应用系统中有很多SQL。 如果总是扮演消防员的角色来解决线上问题，显然无法满足当今IT系统快速发展的需求。 基于数据库的系统的主要性能问题在于SQL语句。 如果能够在开发测试阶段解决SQL问题，语句，找出需要优化的SQL，并提供智能提示快速辅助优化，这样可以避免很多线上问题。 此外，还可以持续监控在线SQL语句，及时发现存在性能问题的语句，从而达到SQL全生命周期管理的目的。

针对以上情况，我们新聚网络凭借多年的运维和优化经验，自主研发了SQL审计工具。 通过SQL采集-SQL分析-SQL优化-在线跟踪的四步SQL审计规则，大大提高了SQL审计能力。 优化和性能监控处理效率。 与传统的SQL优化方法不同，它侧重于系统上线前的SQL分析和优化，重点解决系统上线前的SQL问题，将性能问题消灭在萌芽状态。