oracle性能优化(项目中的一个sql优化的简单记录)
在项目中,写的sql主要以查询为主,但是数据量一大,就会突出sql性能优化的重要性。其实在数据量2000W以内,可以考虑索引,但超过2000W了,就要考虑分库分表这些了。本文主要记录在实际项目中,一个需要查询很慢的sql的优化过程,如果有更好的方案,请在下面留言交流。
很多文章都有关于sql优化的方法,这里就不一一陈述了。如果有需要可以查看博客:https://blog.csdn.net/linhaiyun_ytdx/article/details/79101122
SELECT T.YHBH,
(SELECT NAME FROM DIM_REGION WHERE CODE = SUBSTR(T.GDDWBM, 0, 4)) GDDWMC,
(SELECT NAME FROM DIM_REGION WHERE CODE = T.GDDWBM) FJMC,
T.DFNY,
T.YHMC,
T.YDDZ,
(SELECT NAME FROM DIM_ELECTRICITY_TYPE WHERE CODE = T.YHLBDM) YDLBMC
FROM (SELECT DISTINCT T.YHBH,
DECODE(T.GDDWBM,
NULL,
"0000",
DECODE(T.GDDWBM, "09", "0000", T.GDDWBM)) AS GDDWBM,
T.BBNY AS DFNY,
T.YHLBDM AS YHLBDM,
T.YHMC,
T2.YDDZ
FROM V_TEMP_TABLE_JHCBHSTJ_HISTORY T, TMP_KH_YDKH T2
WHERE T.YHBH = T2.YHBH(+)
AND NOT EXISTS (SELECT 1
FROM DJHJSL_LSB_FZ_HISTORY B
WHERE B.BBNY = T.BBNY
AND B.YHBH = T.YHBH
AND B.GDDWBM = T.GDDWBM
AND B.YHLBDM = T.YHLBDM
AND B.ZDCBZHS <> "0")
) T
WHERE SUBSTR(T.GDDWBM, 0, 4) = "0946"
AND T.DFNY = "201911"
这个是我的sql脚本。其实这个脚本一点都不复杂。其中V_TEMP_TABLE_JHCBHSTJ_HISTORY,DJHJSL_LSB_FZ_HISTORY每个月增加330万,目前有1960多万, TMP_KH_YDKH表有330多万。DIM_REGION 和DIM_ELECTRICITY_TYPE 是两个数据字典项表。
在没有索引的情况下,这个脚本执行需要30s,看到执行过程,现在都是全表扫描的。接下来开始优化。
1.修改脚本的查询,将外层的查询条件放到里面,减少数据量。
SELECT T.YHBH,
(SELECT NAME FROM DIM_REGION WHERE CODE = SUBSTR(T.GDDWBM, 0, 4)) GDDWMC,
(SELECT NAME FROM DIM_REGION WHERE CODE = T.GDDWBM) FJMC,
T.DFNY,
T.YHMC,
T.YDDZ,
(SELECT NAME FROM DIM_ELECTRICITY_TYPE WHERE CODE = T.YHLBDM) YDLBMC
FROM (SELECT DISTINCT T.YHBH,
DECODE(T.GDDWBM,
NULL,
"0000",
DECODE(T.GDDWBM, "09", "0000", T.GDDWBM)) AS GDDWBM,
T.BBNY AS DFNY,
T.YHLBDM AS YHLBDM,
T.YHMC,
T2.YDDZ
FROM V_TEMP_TABLE_JHCBHSTJ_HISTORY T, TMP_KH_YDKH T2
WHERE T.YHBH = T2.YHBH(+)
AND NOT EXISTS (SELECT 1
FROM DJHJSL_LSB_FZ_HISTORY B
WHERE B.BBNY = T.BBNY
AND B.YHBH = T.YHBH
AND B.GDDWBM = T.GDDWBM
AND B.YHLBDM = T.YHLBDM
AND B.ZDCBZHS <> "0")
AND SUBSTR(T.GDDWBM, 0, 4) = "0946"
AND T.BBNY = "201911"
) T
2.对三个表都建上索引
对V_TEMP_TABLE_JHCBHSTJ_HISTORY根据DFNY,SUBSTR(T.GDDWBM, 0, 4)建上联合索引。
CREATE INDEX IDX_TMP_JHCBHSTJ_HISTORY_UNION ON V_TEMP_TABLE_JHCBHSTJ_HISTORY(BBNY,SUBSTR(GDDWBM, 0, 4));
对TMP_KH_YDKH表,使用了关联,所以需要对yhbh建个索引
create index IDX_YHBH_KH on TMP_KH_YDKH (YHBH);
对于DJHJSL_LSB_FZ_HISTORY表,在not EXISTS里面,会全表扫描这个表,现在对他建立联合索引试试。
CREATE INDEX IDX_DJHJSL_FZ_HISTORY_UNION ON V_TEMP_TABLE_JHCBHSTJ_HISTORY(BBNY,YHBH,GDDWBM,YHLBDM);
查看oracle的执行计划,建立联合索引,并没有让这个表走索引,还是在全表扫描的,但是查询已经提升到9s了。
接下来对分别对这四个字段建立索引:
create index IDX_DJHJSL_FZ_HISTORY_BBNY on DJHJSL_LSB_FZ_HISTORY (BBNY);
create index IDX_DJHJSL_FZ_HISTORY_YHBH on DJHJSL_LSB_FZ_HISTORY (YHBH);
create index IDX_DJHJSL_FZ_HISTORY_GDDWBM on DJHJSL_LSB_FZ_HISTORY (GDDWBM);
create index IDX_DJHJSL_FZ_HISTORY_YHLBDM on DJHJSL_LSB_FZ_HISTORY (YHLBDM);
从执行计划来看,oracle只走了IDX_DJHJSL_FZ_HISTORY_BBNY这个索引,现在最快已经到1.95s了。
虽然现在已经满足了查询3s内的要求,但是考虑到以后,每个月的数据增长,数据量有5000万,一亿这样的大数据量的时候还是会很慢。
其实我在正式环境测试的时候,NOT EXISTS 里面的这个表,建立单个索引是没有用的,建立联合索引才会使这个表走索引,可能是因为电脑的cpu不同等因素影响的。
上面的优化方法当然不能满足项目的需求,接下来结合业务进行优化。作为一个监控系统,数据是T+1的,不需要追求实时性,这些数据,都是使用etl抽取工具每天定时抽取的。而且每个月300万数据,用户只关注的只有几千条。所以结合业务,我们在使用etl抽取完数据后,将用户关注的数据插入到另一张表中,这样,每个月只有几千条数据,这样的话,一年也才几万条数据,对oracle来说决定是零压力的。
如果大家还有其他的方式优化,请在下方留言交流。
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341