标量子查询产生的SQL性能瓶颈，该怎么合理优化？

作者简介:郝浩哲，新居网络数据库专家他擅长数据库开发、整体架构和复杂SQL的调优。他参与了许多行业核心系统的优化。目前，他专注于开源技术、自动化运输和性能调优技术的研究。

1。标准量子查询和伪代码表示

标准量子查询。因为它需要传递值，所以它类似于嵌套循环连接，并且驱动表将被扫描n次SQL语句中的主要结果集是从动表，标量查询是从动表，从动表的执行时间是主要结果集的连接列中不同值的数量，例如，条带量子查询的SQL语句:

选择名称。(从部门d中选择dname，其中d.deptno = e.deptno)从emp中选择dname，其中e . job in(' SALER '，' ANALYST ')；

可以表示为:

表示in(从empe where e . jobin(' sales '，' analyst ')中选择distinct deptno:从dept where d . dept no = I中选择dname

的标量部分的执行时间可以通过以下SQL语句来计算:

selet count(distinct deptno)来自empe，其中e . jobin(' sales '，' analyst ')；

2、标量查询

与伪代码结合容易引起性能问题，一般来说，带有标量查询的SQL语句在三个方面容易出现性能问题:

1、主查询在过滤结果集中的效率。在上例中，它指的是基于作业字段过滤时主表emp的性能。如果访问路径不好，例如全表扫描和错误的索引扫描，很容易导致性能问题；

2，主查询在过滤结果集之后返回大量数据(这里的数据量是指连接列的唯一值)，这将导致对标量部分的多个查询。即使标量的访问路径是INDEX UNIQUE SCAN，也很容易由于更多的查询而导致性能问题。

3，标量部分的查询效率，如果标量部分的访问路径不好，很容易引起性能问题。

3。标准量子查询通用优化方案

在介绍了标准量子查询的特点之后，我们接下来将讨论优化问题。一般来说，当标准量子查询有性能问题时，主要有三种优化方案可以采用:

1，对于查询语句中的标准量子查询，通常使用左连接进行重写。当然，如果标量部分和主表在联接列上具有主键和外键关系，则可以将其重写为内部联接，以进一步提高性能。

2。对于update语句中的标准量程查询，merge语句通常用于重写。

3。当在某些情况下不能重写时，标量部分的访问路径和连接方式可以通过索引方式进行优化。

有人建议重写优化是首选，因为重写的最大优势在于它可以控制执行计划并更改标量连接方法(例如，通过提示、配置文件或让优化器自己选择)。当SQL语句在某些情况下不能改变时，通过索引方法进行优化。

4，案例分析

本节共有3个在实际工作中遇到的标量查询重写的优化案例，3个案例分别是

案例1:1:select语句中的标量查询重写；案例2:在2:where语句之后重写标准量子查询；案例3:在3中重写标准量子查询:更新语句；

案例1:标准量程查询

in 1:选择此案例在仓库平台中执行报表SQL大约需要2分钟。在向主表顺序添加过滤条件并分组后，返回的数据量约为160万

的实际执行计划显示，此步骤只需不到2秒钟，并且大部分时间花在id = 3-access(“TT”)上。" order _ id" =: B1)，这反映在SQL语句中。然后从数字转换器中选择计数(1)，其中数字转换器的顺序id =标量部分的顺序id。由于连接列distinct值中的主表返回大量数据，标量部分扫描次数更多(约160万次)，导致性能瓶颈

选择t.num_id，t.create_time，1 if_3to4，max(case when(从dhoe tt中选择count(1 ),其中tt.order_id = t.order_id，tt.otype = ' 101 ')>;0然后0(从dhoe tt中选择计数(1)，其中tt.order_id = t.order_id，TT . ot type = ' 102 ')& gt。0然后1否则0结束)如果来自订单t的_hk，其中off_rype = '9601 '由t.num_id分组，t.create _ time

对于标量查询，在select语句之后，我们通常使用左连接或内连接方法重写它。在这种情况下，在join列order_id中，标量部分表dhoe和主表顺序之间没有主外键关系，因此我们只能使用左连接重写它。在重写期间，我们只需要将join列上的标量部分分组，并在预先计算之后将结果与主表相关联。重写

后，优化器会选择将主表与原始标量查询连接起来。每个表只能访问一次以完成查询，从而大大减少了物理输入/输出次数

，当然，这并不意味着优化器在重写后总是会选择散列外连接。在某些情况下(例如统计数据，特殊查询会干扰优化器对行的估计，等等)。)，优化器还会选择将联接列谓词推入重写的标量视图，从而使重写的标量视图在驱动表和主表之间进行嵌套循环联接。这个执行计划通常比标量写更糟糕。因此，我们仍然需要检查重写后的下一个执行计划是否有任何问题

选择num_id，create_time，1 if_3to4，b.cnt101 >时的情况；当c.cnt102 >时为0，然后为0。0然后1 else 0 end) if_hk from(选择t.num_id，t.create_time，1 if_3to4，order_id from order t where off _ rype = ' 9601 ')a left outer join(选择order _ id，count(当otype = '101 '然后1 end时的情况)cnt101，count(当otype = '102 '然后1 end时的情况)CNT 102 from dhoe group by order _ id)b & gt。按编号id、创建时间分组；

案例2:where

之后的标准量程查询当where之后有标准量程查询时，由于值的转移，标量部分也需要执行n次，除了关键字Filter出现在执行计划中，这与选择后的标准量程查询执行计划有些不同，但操作方法是相同的。过滤器的驱动表是主要的结果集，驱动表是标准的量子查询

这是一个大屏幕的SQL语句，定期刷新页面。每次执行需要52秒。从实际执行计划中可以看出，大部分时间都花在标量值传递的计算上:

从WP _ infows内部联接WP _ centerwa中选择count (*)，其中ws.status = 'VALID '和wa.is_del = 'V '和(从wp_bas wb左联接wp_rep wr中选择count(1)>;wb在哪里？(2，3，4)中的WP_STATUS和wr.is_valid = 'VALID '和wr.created >。sysdate - 7和wr . service _ no = ws . num)& lt；ws.total _ num

与select: scalar部分中量子查询的重写方法一致，它根据连接列的分组预先计算结果，并与主结果集进行左连接。在这种情况下，应该注意使用nvl函数来处理重写标量字段的空值，然后比较:和nvl (cc。cn t，0)-lt。ws . total _ num

从wp_info ws内部联接wp_center wa >中选择count(*)。左连接(选择wr.service_no，计数(1)来自wp_bas wb的cnt在wr.id = wb.id处的左连接wp_rep wr。(2，3，4)中的WP_STATUS和wr.is_valid = 'VALID '和wr.created >。sysdate - 7分组方式(wr.service_no) cc >。其中ws.status = 'VALID '和wa.is_del = 'V '和nvl(cc.cnt，0)& lt；ws.total _ num

在这里与您谈谈另一个想法:在某些情况下，我们可以使用+物化来优化标量的计算过程。这种重写具有简单方便的优点，可以减少每次标量计算带来的性能开销。缺点是它不能像左连接重写那样控制执行计划，即它不能改变驱动表的执行时间。

对于情况2，具有+物化的重写方案如下:

with tas(select/*+物化*/wr . service _ nofromwp _ baswbleft join WP _ repwr >其中wb。(2，3，4)中的WP_STATUS和wr.is_valid = 'VALID '和wr.created >。sysdate - 7)从wp_info ws左加入wp_center wa >中选择count(*)。其中ws.status = 'VALID '和wa.is_del = 'V '和(从其中wr.service_no = ws.num的t wr中选择计数(1)& lt；ws.total _ num

情况3:当标准量程查询

update set语句后面跟有一个传递值时，它还会导致子查询被扫描n次。它通常用merge语句重写。用merge语句重写时，要注意关联条件的编写

这种情况下的update语句每次执行大约需要20分钟。从实际的执行计划中可以看出，标量部分257次的执行是这个SQL语句的性能瓶颈:

updatere _ rpta seta . tcnt =(选择nvl (sum (g. redcode))。0)从RP_GRANT G，(选择DISTINCT REDCODE，REDCODE_MD5从RP_SCAN) S，IMT_CODE V，(选择DISTINCT W_ID，NAME FROM MATER WHERE SUBSTER(ORDER，1，2) = 'OF '且W_ID不为空)Mwhere g. redcode = s. redcode且s. redcode _ md5 = v.n _ code且v.w _ code = m.w _ id且g.rp _ class =' valid '且g.create _ date =' 2018-05-05 '且

还可以通过以下查询计算标量执行时间:

SQL>。选择计数(不同的名称)2，从资源角色扮演角色扮演角色扮演角色扮演角色扮演角色扮演角色扮演角色扮演角色扮演= ' 2018-05-05 '；计数(不同名称)-257

，因为每次执行该语句时，每日数据都会完全更新。因此，合并语句的关联条件可以写成外部连接的方式:A . NAME = M . NAME(+)

merge into(SELECT * FROM re _ rpta WHERE A . choose _ time = ' 2018-05-05 ')A USING(SELECT M . NAME，SUM(G . REDCODE)SUM _ CODE FROM RP _ GRANT G，(SELECT DISTINCT REDCODE，REDCODE_MD5 FROM RP_SCAN) S，IMT_CODE V，(SELECT DISTINCT W_ID，NAME FROM MATER WHERE 2) = 'OF '和W_ID不为空)Mwhere g. redcode = s. redcode和s. redcode _ md5 = v.n _ code和v.w _ code = m.w _ id和g.rp _ class =' valid '和G.CREATE_DATE = '2018-05-05 ')由M.NAME) M ON (A.NAME = M.NAME(+))分组，当匹配时，则更新集A.TCNT = nvl(SUM_CODE，0)；

5，总结

本文主要介绍标准量子查询的特点，并结合实际工作中遇到的三种情况讨论常见的重写方案。如果SQL语句的性能瓶颈是标准量子查询，那么可以通过重写SQL来改变主表和标量查询之间的连接，或者通过建立索引来优化标量部分的访问路径。本质是一样的:减少物理输入/输出次数，以达到提高性能的目的

从过去的40年到现在，数据库的形式基本上经历了从传统商业数据库、开源数据库到云原生数据库的演变过程在云时代，数据库将如何创新和改变？金融业核心数据库的迁移和构建如何安全、顺利地进行？来广发银行全球敏捷运营峰会北京站寻找答案:

< BR >

“全在云时代，下一代云原生数据库技术与趋势”阿里巴巴集团副总裁(飞刀)、达摩研究院首席数据库科学家、“人工智能与云始祖时代的数据库进化之路”、腾讯数据库产品中心总经理林(丁琪)、“工行MySQL探索之路”、魏亚东、 中国工商银行软件开发中心，“MySQL高可用性在金融行业的实践”，爱康技术总监明思远，“海洋基地分布式数据库在Xi岸的登陆与实践”，蚂蚁金融高级专家P9·江志勇

，家庭/海洋基地核心领导者，让我们一起展望9月11日北京数据库发展与转型的未来！

本文由 在线网速测试 整理编辑，转载请注明出处，原文链接：https://www.wangsu123.cn/news/2422.html。