热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

一周工作总结--一次SQL优化记录

今天收到一个同事的问题,有一段SQL跑了很久很久,根本没有结果,根据同事的反映,这个SQL一个月比一个月要慢。这是不被允许的事情,我们要做的就是对这个SQL进行一次优化。下面就是这次优化的记

     今天收到一个同事的问题,有一段SQL跑了很久很久,根本没有结果,根据同事的反映,这个SQL一个月比一个月要慢。这是不被允许的事情,我们要做的就是对这个SQL进行一次优化。下面就是这次优化的记录。

     首先说SQL:

select t.month_id,
t1.area_id,
t1.local_id,
count(distinct case
when t.type_id = '02' and t.valid_flag = 1 and
t3.trade_id
= '1008601' then
t.
user_id
else
null
end),
count(distinct case
when t.type_id = '02' and t.valid_flag = 1 and
t3.trade_id
= '1008602' then
t.
user_id
else
null
end)
from product_flag_m t,
... --省略部分都是类似上面的运算,很多,为了节省篇幅都取消了
left join VW_CODE_LOCALNET t1
on t.local_id = t1.root_local_id
LEFT JOIN TRADE_LIST T3
ON T.id2 = T3.id2
AND T3.trade_id IN ('1008601', '1008602')
where t.month_id = '201212'
group by t.month_id, t1.area_id, t1.local_id;

      这段代码隐藏了敏感信息,可能会有一些修改的时候错漏的问题。

      接下来就是比较老的套路了,查看这段SQL的执行计划:

      

      这个时候可以初步判断是因为product_flag_m表太大造成的查询效率低下。既然只需要12月的数据,那么我自然而然的想到了将12月的分区压缩一下,利用压缩表的特点进行查询效率的提高。但是这是张生产表,不能随便操作,于是我就将12月份的type_id='02'的数据单独抽取出来形成一张新的表,当然这张表是压缩过的,而且我抽取的时候只抽取自己需要的字段,这样做的好处是尽量减少数据量,减轻数据库的负担。

     下面就是使用了压缩表之后的执行计划:

     

       可以看到COST是有所降低,但是这个和没有降低没什么区别。还是面临执行不出来的问题。

       这个时候我注意到了ID=2的这一部执行计划。在id=3的hash join right outer之后,不管是COST还是BYTES都是在一个比较正常的水平之内的,那么问题就应该出在TRADE_LIST这个表上。

       这个表是一张编码表,本身并不大,但是注意这里:

       

       上图所示应该就是罪魁了。于是我想到了,既然最后需要过滤一下trade_id,那么为什么不直接就用一张只有trade_id为1008601和1008602的表呢?

       于是我鬼使神差的建立了一张视图,这个视图就是只取了上面说的那么多数据,然后替换掉原来的SQL中的TRADE_LIST,删除了其中的

AND T3.trade_id IN ('1008601', '1008602') 语句,再看执行计划:

       

        这个效果就非常好了。

        我本身很担心这个视图用了以后会影响查询结果集。于是我自己造了一张表做了一个小测试。test3中有object_id为2, 3, 4, 5, 6, 7的记录,编码表中只有id为2, 3, 4, 5, 6的编码记录,SQL如下:

        

select t1.object_id, t2.id, t2.name
from test3 t1
left join test4 t2
on t1.object_id = t2.id
and t2.id in (2, 3);

       这个结果有48行。制造一个视图:

create view test5 as select * from test4 where id in (2, 3)

      然后替换成视图:

      

select t1.object_id, t2.id, t2.name
from test3 t1
left join test5 t2
on t1.object_id = t2.id;

      结果还是48行。也就是说这个方法是可行的。

      这样的话,如果在原来的SQL上加上并行提示,效果会更好。经过我的实际测试,3分钟以内就跑出了所有的结果。

      或许会有人问我,为什么不加上索引?我并不是反对加索引,我不习惯使用索引的习惯是因为我们的现实环境所限,我们的磁盘空间基本上每隔一段时间就会满,所以我没办法随心所欲的添加会占用空间的索引,而是更倾向于使用压缩表,节省表空间。而且,id2字段进行关联的时候有一个隐式类型转换,这个字段起码没有办法加索引。至于其他字段,我没办法实验,如果有机会,可以做个实验试试。

       


推荐阅读
author-avatar
CCTV2财经2677
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有