一次关于SQL子查询的优化

最新推荐文章于 2024-06-12 10:49:09 发布

congyixia5658

最新推荐文章于 2024-06-12 10:49:09 发布

阅读量208

点赞数

文章标签：数据库 shell

近日,某金融公司的客户抱怨他们已经无法忍受他们的数据仓库系统加载数据的时间了。按理说该公司的大表的数据量也就百万级，根本不应该这么慢，系统肯定有问题。

通过现场对数据加载日志进行分析，发现该数据仓库在分析层加载的地方耗费大量时间，达到了不可思议的7-8小时，造成加载缓慢的原因看来就是在这个地方了。通过对该部分加载的进一步分析，发现该部分作用是从历史ODS中抽取当日数据为后续的统计报表进行数据准备，由于涉及的表比较少且转化较少，于是系统设计的时候采用的是将insert语句封装到shell里的方式来加载。既然如此，马上迫不及待的找到处理的shell程序，使用编辑器打开shell文件的瞬间，发现了性能问题的根源所在-----shell中的SQL语句逻辑异常复杂，执行效率低下。

既然发现了问题的根源，那么就着手进行修改，修改大致有以下两类：

1.大量使用相同子查询结果集作为主查询的表。

对于这类SQL，由于使用太多的嵌套查询，造成过多全表扫描，于是采用的是引入中间过程表的方式，先将子查询的结果保存为临时表，然后主查询 select from临时表完成相关查询操作。

为了具体说明，使用oracle HR用户下employees表作为演示。

首先建立一张大表

begin

for i in 1 .. 29876 loop

insert into t

value

select last_name||i,

email||i,

phone_number,

hire_date+trunc(i/10,0),

job_id,

salary+i,

manager_id,

department_id,

to_char(i*1000+employee_id)

from employees;

end loop;

end;

先来看看拆分前那种方式的SQL的执行计划

insert into tw value

select a.first_name,a.last_name,b.job_id,a.department_id from

(select last_name, job_id, manager_id,salary

from t

where hire_date between to_date('19900101', 'yyyymmdd') and

to_date('19990101', 'yyyymmdd'))b left join employees a on a.manager_id=b.manager_id

where a.department_id ='50'

union all

select a.first_name,a.last_name,b.job_id,a.department_id from

(select last_name, job_id, manager_id,salary

from t

where hire_date between to_date('19900101', 'yyyymmdd') and

to_date('19990101', 'yyyymmdd'))b left join employees a on a.manager_id=b.manager_id

where a.salary >1500

接下来，我们根据子查询构建一张临时表

create table tmp as select last_name, job_id, manager_id,salary

from t

where hire_date between to_date('19900101', 'yyyymmdd') and

to_date('19990101', 'yyyymmdd')

改变SQL逻辑关系后再来看看执行计划

insert into tw value

select a.first_name,a.last_name,b.job_id,a.department_id from

tmp b left join employees a on a.manager_id=b.manager_id

where a.department_id ='50'

union all

select a.first_name,a.last_name,b.job_id,a.department_id from

tmp b left join employees a on a.manager_id=b.manager_id

where a.salary >1500

虽然在创建临时表的时候对于大表同样进行了一次全表扫描，但对于多次用到该子查询作为主查询表的情况可以使执行效率大大提高。

2.使用子查询作为主查询查询字段

下面这段是执行特别缓慢的某段SQL的一部分，对敏感信息处理后如下：

insert into a_ls_fac

SELECT trim(a.acct_no),

a.CUST_NO,

b.APP_TYP,

case

when b.APP_SRC = 'S' then

'SALSE'

when b.APP_SRC = 'I' then

'内部录入'

when b.APP_SRC = 'E' then

'excel 内部录入'

end as APP_SRC,

(select STORE_NAME

from ff_merch_store a1

where a1.store_no = a.store_no

and to_date('20121102', 'yyyy-mm-dd') between a1.begin_dt and

a1.end_dt - 1) as STORE_NAME,

b.SALES_NO,

round(b.FIRST_PAY_AMT / decode(b.MERCH_TOTAL_PRICE,0,null,b.MERCH_TOTAL_PRICE), 2)/* round(b.FIRST_PAY_AMT / b.MERCH_TOTAL_PRICE, 2) */as FIRST_PAY_RATIO,

a.CAMPAIGN_ID,

(select CAMPAIGN_DESC

from ff_campaign a1

where a.CAMPAIGN_ID = a1.CAMPAIGN_ID

and to_date('20121102', 'yyyy-mm-dd') between a1.begin_dt and

a1.end_dt - 1) as CAMPAIGN_NAME,

a.MERCH_NO,

(select MERCH_NAME

from ff_cus_merch a1

where a1.MERCH_NO = a.MERCH_NO

and to_date('20121102', 'yyyy-mm-dd') between a1.begin_dt and

a1.end_dt - 1) as MERCH_NAME,

a.INT_BASIS,

(select int_rate

from ff_rate_def a1

where to_date('20121102', 'yyyy-mm-dd') between a1.begin_dt and

a1.end_dt - 1

and c.int_basis = a1.int_basis

and c.eff_dt = a1.eff_dt) int_rate,

a.RATE_FLOAT_RATIO,

b.appv_id,

(select opr_name

from ff_sys_opr a1

where b.APPV_ID = a1.OPR_ID

and to_date('20121102', 'yyyy-mm-dd') between a1.begin_dt and

a1.end_dt - 1) as APPV_NAME,

(select max(REPAY_DT)

from ff_repay a1

where a1.acct_no = trim(a.acct_no)

and to_date('20121102', 'yyyy-mm-dd') between a1.begin_dt and

a1.end_dt - 1) as MATURITY_DT,

a.CREATE_DT,

a.AUTH_DT,

a.AUTH_NO,

a.PERI_AMT,

a.OVER_PRD,

(select count(*)

from ff_repay a1

where trim(a1.ACCT_NO) = trim(a.acct_no)

and a1.SETTLE_FLG = 'Y'

and to_date('20121102', 'yyyy-mm-dd') between a1.begin_dt and

a1.end_dt - 1) as SETTLE_PRD_CNT,

a.CUR_BUCKET,

a.LAST_REPAY_AMT,

(select max(REPAY_DT)

from fud_LN_OD_DTL a1

where trim(a1.ACCT_NO) = trim(a.acct_no)

and to_date('20121102', 'yyyy-mm-dd') between a1.begin_dt and

a1.end_dt - 1) as last_OD_dt,

a.MAX_BUCKET,

a.MAX_OD_AMT,

(select nvl(to_date('20121102', 'yyyy-mm-dd') -

to_date(min(REPAY_DT), 'yyyy-mm-dd')+1,

from ln_OD_dtl a1

where trim(a1.ACCT_NO) = trim(a.acct_no)

and to_date('20121102', 'yyyy-mm-dd') between a1.begin_dt and

a1.end_dt - 1) as OD_DAYS,

(select min(REPAY_DT)

from ln_OD_dtl a1

where trim(a1.ACCT_NO) = trim(a.acct_no)

and to_date('20121102', 'yyyy-mm-dd') between a1.begin_dt and

a1.end_dt - 1) as LONG_OD_DT,

a.AUTOREPAY_TYP,

a.ADV_REPAY_CNT,

to_date('20121102', 'yyyy-mm-dd') data_dt

FROM ff_contract a

left join ff_app_list b on trim(a.acct_no) = b.app_no and

to_date('20121102', 'yyyy-mm-dd') between b.begin_dt and

b.end_dt - 1

left join rate_t c on trim(a.acct_no) = trim(c.acct_no)

where to_date('20121102', 'yyyy-mm-dd') between a.begin_dt and

a.end_dt - 1

可以直观的看到，里面有很多子查询作为主查询查询字段的情况，由于子查询关联的表也为相关事实表并非维表，故在实际的执行计划中，每个子查询的COST大致在200000至600000左右，由于这类方式在该SQL中很多，故该条SQL的COST达到了惊人的数量级。在对每个子查询的作用进一步分析后，决定修改此SQL逻辑方式，

以插入STORE_NAME字段为例：

(select STORE_NAME

from ff_merch_store a1

where a1.store_no = a.store_no

and to_date('20121102', 'yyyy-mm-dd') between a1.begin_dt and

a1.end_dt - 1) as STORE_NAME

该子查询目的是在主查询执行时关联相关的店铺名称以方便构造便于报表展现的宽表，于是首先构建一张维表dim_store，包含所有店铺的store_no和store_name字段，子查询中的表记录条数一下从几十万条降低至几百条，同时修改INSERT语句，在插入数据的时候仍然插入store_no到目标表，insert 完成后使用update更新该字段

update a_ls_fac a set STORE_NAME=

(select store_name

from dim_store e

where e.store_no=a.store_no)

where a.data_dt=to_date('20121102','yyyymmdd')

采用此方式完成所有这类子查询的修改后，SQL的执行效率得到了大大的提高。

至此，整个数据仓库的调优已基本达到预期要求，分析层的加载时间由以前的9小时缩短到至1小时左右，系统性能得到极大提高。事后了解，该部分SQL由客户公司的数据分析部门编写，在进行SQL开发时由于测试环境数据量很小，故根本没考虑SQL的执行效率。

最后，对于子查询的使用，我觉得应该注意以下方面：

1.避免相关子查询
一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。

2. 使用临时表加速查询
把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。
注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据

1.jpg