leftjoin多了性能下降_标量子查询产生的SQL性能瓶颈，该怎么合理优化？

作者介绍

郝昊喆，新炬网络数据库专家。擅长数据库方面的开发、整体架构及复杂SQL的调优，参与了多个行业核心系统的优化工作，目前专注于对开源技术、自动化运维和性能调优技术的研究。

一、关于标量子查询及伪代码表示

标量子查询由于需要传值，因此它和嵌套循环连接类似，被驱动表会被扫描N次。SQL语句中的主结果集为驱动表，标量查询为被驱动表，被驱动表的执行次数为主结果集在连接列上distinct值的数量，例如一条带标量子查询的SQL语句：

select ename, (select dname from dept d where d.deptno = e.deptno) dname

  from emp e

 where e.job in ('SALESMAN', 'ANALYST');

用伪代码可以表示为：

for i in (select distinct deptno from emp e where e.job in ('SALESMAN', 'ANALYST')):

select dname from dept d where d.deptno = i;

标量部分的执行次数可通过SQL语句计算出：

selet count(distinct deptno ) from emp e where e.job in ('SALESMAN', 'ANALYST');

二、标量子查询易产生的性能问题

结合伪代码，通常来说带有标量子查询的SQL语句易产生性能问题的地方有三点：

1、主查询过滤结果集时的效率，在上述例子中，是指对主表emp基于job字段进行过滤时的性能，如果访问路径较差，例如全表扫描、错误的索引扫描，易产生性能问题；

2、主查询过滤结果集后返回的数据量较大(这里的数据量指的是连接列的唯一值)，会导致标量部分多次查询，即使标量的访问路径为INDEX UNIQUE SCAN，也容易因为较多的查询次数产生性能问题；

3、标量部分的查询效率，如果标量部分的访问路径较差，易引起性能问题。

三、标量子查询常规优化方案

介绍了标量子查询的特点后，接下来聊聊优化的问题，通常来说，当标量子查询存在性能问题时，可采取的优化方案主要有3种方式：

1、对于查询语句中的标量子查询，通常使用left join改写，当然，如果标量部分与主表在连接列上为主键、外键关系时，可以改写为inner join，进一步提升性能；

2、对于update语句中的标量子查询，通常使用merge语句改写；

3、在某些环境下不能改写时，可通过索引手段优化标量部分的访问路径、连接方式；

个人建议优先选择改写优化，因为改写最大的优势是可以控制执行计划，改变标量的连接方式(例如通过Hint、profile或者让优化器自己去选择)，当某些环境下无法改变SQL语句时，再通过索引方式优化。

四、案例分析

本小节分享3个实际工作中遇到的标量子查询改写优化案例，3个案例分别是：

• 案例1：select语句中的标量子查询改写；

• 案例2：where语句后的标量子查询改写；

• 案例3：update语句中的标量子查询改写；

案例1：select中的标量子查询

该案例为数仓平台中的报表SQL，总执行时间约2分钟左右，主表order加上过滤条件并group by后，返回数据量约160万