项目中的表连接优化分享

表连接的优化分享

梁敬彬这三个例子来源于近期一个月内我们部门遇到的经典SQL问题，都来自身边，也许都是大家非常熟悉的SQL，他们分别来自集团应用、安徽应用、浙江应用，但是他们都有一个共同的特点，就是表连接的优化，现总结如下：

1.子查询与表连接优化（来自集团ITSM应用）

我发现身边的开发人员，很多人喜欢使用复杂子查询写各类SQL，其实很多时候，要是不注意默写细节，就会出现很多问题，如性能问题甚至结果值不对。举个身边例子（来自ITSM集团项目的一条SQL）具体如下：

-----略去

select ta.tch_id, ta.flow_id

from tache pr,

(select TCH_ID, c.flow_id

from event_q a, staff_event b, tache c

where (a.event_type = '2' OR a.event_type = '1')

and a.event_id = b.event_id

and (a.flag is null or a.flag = '1')

and b.staff_id = 1

and a.content_id = c.tch_id) ta

where ta.flow_id = pr.sub_flow_id(+)

and pr.flow_id is null

-------略去

这条SQL本身很复杂，我把其他部分略去，只显示典型的子查询写法的这一小部分，另外主要也是这一小部分慢，如果把这一小部分注释掉，执行速度在1秒内完成，否则需要30多秒才可以完成。

我们单看这一小部分的执行计划，截取如下：

我们可以很清楚的判断出来，该子查询的连接顺序是什么？首先是外面的tache 表先和ta结果集的tache 表连接，然后再和STAFF_EVENT连接，最后和EVENT_Q完成连接！

由于staff_event 这个表和TACHE表没有连接条件，就是没有体现staff_event的某某字段和TACHE表的某某字段关联的地方，所以这里产生了笛卡尔乘积！因此本次查询非常的慢。

该如何处理呢？其实很简单，我们发现，整个语句最终只返回42条

说明ta这个结果集本身返回的记录数并不多，虽然tache是一张大表，但是这段语句如果没有笛卡尔乘积，应该非常快！

简单研究发现，只要ta结果集内部先完成表连接，再和外部连接，就不至于产生笛卡尔乘积了，因为在ta结果集里，我们可以STAFF_EVENT b和EVENT_Q a先完成连接，他们是有连接条件的，条件是

a.event_id =

b.event_id

接下来这两个连接再和ta结果集中的STAFF c表连接，又有连接条件，条件是：

a.content_id = c.tch_id

最好再和外面的tache pr表完成连接，连接条件是：

ta.flow_id = pr.sub_flow_id(+)

如此看来，不可能有笛卡尔！而如何让ORACLE的子查询不要先自行逛街出去和外面表先连接，再会过来连接内部剩下的部分呢？

很简单，只要修改为如下即可,增加rownum部分

select ta.tch_id, ta.flow_id

from tache pr,

(select TCH_ID, c.flow_id,rownum

from event_q a, staff_event b, tache c

where (a.event_type = '2' OR a.event_type = '1')

and a.event_id = b.event_id

and (a.flag is null or a.flag = '1')

and b.staff_id = 1

and a.content_id = c.tch_id) ta

where ta.flow_id = pr.sub_flow_id(+)

and pr.flow_id is null

为了保证rownum的结果集是对的，oracle不可能先出去和结果集外的表关联一部分再回到结果集内，只可能内部先关联了，因此，我们成功了！最终执行计划如下，笛卡尔乘积消失，总的SQL语句执行时间从30秒变为1秒

2.NL连接与表连接优化(来自安徽拨侧应用)

在我们电信行业，80%的表连接都是NL连接，因为这是由oltp 系统的特性决定的：“虽然数据量很大，查询更新很频繁，但是最终需要查询返回及更新的数据量却很少。”

但是NL连接应用不当，却是有致命的问题，因为NL最大的特点是驱动表返回多少条，被驱动表访问多少次，所以NL连接在驱动表返回极少的时候，性能不低，但是驱动表返回很多的时候，性能却很低下，万万不可使用！

下面再举一个身边的例子，来自安徽拨测应用的一条SQL，具体如下：

--略去

from (select a.alarm_title,

--略去其他字段

from ne_alarm_list a

where a.alarm_type = 20

and a.alarm_Level in ('1', '2')

and a.alarm_state in (0)

and https://www.360docs.net/doc/d412110580.html,st_send_time >= TO_DATE('&P_DATE_BEGIN' || ' 00:00:00', 'YYYY-MM-DD hh24:mi:ss')

and https://www.360docs.net/doc/d412110580.html,st_send_time <= TO_DATE('&P_DATE_END' || ' 23:59:59', 'YYYY-MM-DD hh24:mi:ss')

) a1,

manage_region b,

net_element d,

(select *

from tp_domain_listvalues

where domain_code LIKE 'DOMAIN_ALARM_STATE%') f,

kpi_code_list g,

ne_alarm_msg_source_rela h,

net_element k,

manage_region l

where a1.alarm_region_origin = b.region_id(+)

and a1.ne_id = d.ne_id

--and instr(d.path, :P_NE_ID, 1, 1) > 0

and a1.alarm_state = f.list_value

and a1.kpi_id = g.kpi_id

and a1.ne_alarm_list_id = h.ne_alarm_msg_id

and h.source_type = '19'

and h.source_id = k.ne_id

and k.region_id = l.region_id

and l.region_id in ('&PSOURCE_REGION')

order by a1.alarm_state, a1.create_time desc

截取部分执行计划分析，我们惊奇的发现，ORACLE预估NE_ALARM_MSG_SOURCE_RELA表source_type=’19’的条件是返回1条，实际返回了477K条，预估的严重不准确！怪不得会使用NL连接。

结果NL连接导致后续的被驱动表被访问了477K次，真是惊人的数字！

那该如何优化呢？优化招式没有定式，有的时候要改写SQL，有的时候需要增加索引，而有的时候，却只要重新收集一下表的统计信息或者直方图即可。而此时，我们非常清楚的判断出来，ORACLE对NE_ALARM_MSG_SOURCE_RELA 表的SOURCE_TYPE列的数据分别严重错误了。

实际这张表记录有快200万，而这个表的SOURCE_TYPE的列只有3个取值，都是大量的返回。因此我们要做的事情非常简单，重新收集这个表的列的直方图即可，如下：

exec dbms_stats.gather_table_stats(ownname => 'BOSSWG',tabname => 'NE_ALARM_MSG_SOURCE_RELA',estimate_percent => 10,method_opt=>'for all columns size 254',cascade=>TRUE) ;

新的执行计划如下从NL更新为部分HASH了。

执行时间从原先的20秒缩减为0.2秒

3.NOT IN 与表连接优化（来自浙江环境）

很多人说不要用NOT IN ,要考虑用NOT EXISTS，其实很多时候NOT IN是可以很快的，而且很多场合NOT IN 也和NOT EXISTS不完全等价，在列明确为非空的时候，NOT IN在可以用到ANTI算法的时候，是可以很快的，我们可以看看浙江环境的一次SQL优化

SELECT T.FLOW_ID

FROM FLOW_HIS T

WHERE T.FLOW_MOD = 11263

AND T.FLOW_ID NOT IN (SELECT ZJ.FLOW_ID FROM ZJ_EVENT_HIS ZJ)

执行计划如下：

该SQL是NOT IN 写法的SQL，理论上应该是anti算法的执行计划最高效，但是却是走filter，因此效率极低，在确定ZJ_EVENT_HIS表的FLOW_ID字段不会为空后，将该语句增加where flow_id is not null，或者将该列的属性修改为NOT NULL属性。

修改为如下，效率可以提升很多，从1万多秒缩减为1秒，具体修改如下：SELECT T.FLOW_ID

FROM FLOW_HIS T

WHERE T.FLOW_MOD = 11263

AND T.FLOW_ID NOT IN

(SELECT ZJ.FLOW_ID FROM ZJ_EVENT_HIS ZJ where flow_id is not null)

如果这样修改，执行计划将会边为如下