下面分享一个生产异常sql的优化，已在生产库上测试过，性能有近百倍的提升并且查出来的1703条数据完全相同。原sql执行使用了174.984秒，优化后的sql执行了1.829秒。
原SQL如下：
select *
from (select rownum rdd, d.*
from (select content.*,
(case content.recom
when '1' then
1
when '2' then
1
else
0
end) recomIndex,
(casecontent.istop
when '1' then
1
else
0
end) istopIndex,
round(to_number(to_date(content.hiddenDate,
'yyyy-MM-dd') - trunc(sysdate))) as days,
round(to_number(to_date(content.dateEnd, 'yyyy-MM-dd') -
trunc(sysdate))) as days2
from (selectmax(t.resource_link) as url,
max(t.resource_title) as title,
max(t.istop) as istop,
max(t.date_released) as publishDate,
max(to_char(to_date('01/01/1970','MM/DD/YYYY') +
(decode(cms_expandx.expand_key,
'endtime',
cms_expandx.expand_value) +
1000 * 60 * 60 * 8) /
(1000 * 60 * 60 * 24),
'yyyy-MM-dd')) as endtime,
max(to_char(to_date('01/01/1970', 'MM/DD/YYYY') +
(decode(cms_expandx.expand_key,
'dateEnd',
cms_expandx.expand_value) +
1000 * 60 * 60 * 8) /
(1000 * 60 * 60 * 24),
'yyyy-MM-dd')) as dateEnd,
max(to_char(to_date('01/01/1970', 'MM/DD/YYYY') +
(decode(cms_expandx.expand_key,
'hiddenDate',
cms_expandx.expand_value) +
1000* 60 * 60 * 8) /
(1000 * 60 * 60 * 24),
'yyyy-MM-dd')) as hiddenDate,
max(decode(cms_expandx.expand_key,
'notPublish',
cms_expandx.expand_value)) as isPublish,
max(decode(cms_expandx.expand_key,
'isNewPic',
cms_expandx.expand_value)) as isNewPic,
max(t.ispublish) as isPublish2,
max(decode(cms_expandx.expand_key,
'redirectUrl',
cms_expandx.expand_value)) as contentPageUrl,
max(decode(cms_expandx.expand_key,
'recom',
cms_expandx.expand_value))as recom
from (select tt.resource_id,
tt.resource_link,
tt.resource_title,
tt.date_released,
tt.ispublish,
tt.istop
from (select distinct cms_expand.resource_id,
cms_props.resource_link,
cms_props.resource_title,
cms_props.date_released,
cms_props.ispublish,
cms_props.istop,
(select count(8)
from cms_online_resource_expand citycount
where citycount.expand_key like
'city%'
and citycount.resource_id =
cms_props.resource_id
group by citycount.resource_id) as citycount
from cms_online_resource_expand cms_expand
left join cms_online_resource_props cms_props on cms_expand.resource_id =
cms_props.resource_id
where(cms_props.resource_path like
'/sites/creditcard.pingan.com/youhuihuodong/%.shtml' or
cms_props.resource_path like
'/sites/creditcard.pingan.com/youhuinew/%')
and (cms_props.resource_type = '8027' or
cms_props.resource_type =
'19010')) tt
left join cms_online_resource_expand cms_expand1 on tt.resource_id =
cms_expand1.resource_id
)t
left join cms_online_resource_expand cms_expandx on cms_expandx.resource_id =
t.resource_id
group by cms_expandx.resource_id) content
where (isPublish = 'false' or isPublish2 = '1')
order by istopIndex desc,
recomIndex desc,
content.publishDatedesc) d
);
写的确实太复杂了，说一下几个突出的问题：
1， 标红的子查询是没有必要的（有用的只是where后的几个条件，这个完全可以放在父查询中）：
1.1 子查询生成的citycount字段父查询是没有引用的;
1.2 使用的是左连接，但select 出来的全是右表的字段，而且where条件全是对右表的限制。
2， 对大表cms_online_resource_expand （生产数据库量855513）使用了4次，分析了一下，这4次完全可以改成1次与cms_online_resource_props 连接实现。
其实仔细分析一下这个sql，逻辑是不难的，只是思路有点问题导致使用太多子查询，走了太多的弯路。
下面思路是比较合理的：
1， 根据cms_online_resource_props表（生产数据库量114189）查出符合条件的较小的结果集 记作rows1，
2, 根据rows1结果集中的RESOURCE_ID与大表cms_online_resource_expand的RESOURCE_ID做关联 得到rows2，并做相应的行转列操作。
所以所有的逻辑只有以下几条
FROM cms_online_resource_expand cms_expandx,
cms_online_resource_props cms_props
WHERE cms_expandx.resource_id = cms_props.resource_id
AND (cms_props.resource_path LIKE
'/sites/creditcard.pingan.com/youhuihuodong/%.shtml'OR
cms_props.resource_path LIKE
'/sites/creditcard.pingan.com/youhuinew/%')
AND (cms_props.resource_type = '8027'OR
cms_props.resource_type = '19010')
GROUP BYcms_props.resource_id
HAVINGMAX(decode(cms_expandx.expand_key,'notPublish', cms_expandx.expand_value)) = 'false'ORMAX(cms_props.ispublish) = '1'
ORDER BY istopindex DESC,
recomindex DESC,
MAX(cms_props.date_released) DESC
再加上对字段的操作就变成了优化后的sql：
SELECTrownum rdd,
d.*
FROM (SELECTMAX(cms_props.resource_link)ASurl,
MAX(cms_props.resource_title) AS title,
MAX(cms_props.istop) AS istop,
MAX(cms_props.date_released) AS publishdate,
MAX(to_char(to_date('01/01/1970', 'MM/DD/YYYY') +
(decode(cms_expandx.expand_key,
'endtime',
cms_expandx.expand_value) +
1000 * 60 * 60 * 8) / (1000 * 60 * 60 * 24),
'yyyy-MM-dd')) AS endtime,
MAX(to_char(to_date('01/01/1970', 'MM/DD/YYYY') +
(decode(cms_expandx.expand_key,
'dateEnd',
cms_expandx.expand_value) +
1000 * 60 * 60 * 8) / (1000 * 60 * 60 * 24),
'yyyy-MM-dd')) AS dateend,
MAX(to_char(to_date('01/01/1970', 'MM/DD/YYYY') +
(decode(cms_expandx.expand_key,
'hiddenDate',
cms_expandx.expand_value) +
1000 * 60 * 60 * 8) / (1000 * 60 * 60 * 24),
'yyyy-MM-dd')) AS hiddendate,
MAX(decode(cms_expandx.expand_key,
'notPublish',
cms_expandx.expand_value)) AS ispublish,
MAX(decode(cms_expandx.expand_key,
'isNewPic',
cms_expandx.expand_value)) AS isnewpic,
MAX(cms_props.ispublish)AS ispublish2,
MAX(decode(cms_expandx.expand_key,
'redirectUrl',
cms_expandx.expand_value)) AS contentpageurl,
MAX(decode(cms_expandx.expand_key,
'recom',
cms_expandx.expand_value)) AS recom,
(CASEMAX(decode(cms_expandx.expand_key,
'recom',
cms_expandx.expand_value))
WHEN'1'THEN
1
WHEN'2'THEN
1
ELSE
0
END) recomindex,
(CASEMAX(cms_props.istop)
WHEN'1'THEN
1
ELSE
0
END) istopindex,
round(to_number(to_date(
MAX(to_char(to_date('01/01/1970',
'MM/DD/YYYY') +
(decode(cms_expandx.expand_key,
'hiddenDate',
cms_expandx.expand_value)+
1000 * 60 * 60 * 8) /
(1000 * 60 * 60 * 24),
'yyyy-MM-dd')),
'yyyy-MM-dd') - trunc(SYSDATE))) AS days,
round(to_number(to_date(
MAX(to_char(to_date('01/01/1970',
'MM/DD/YYYY') +
(decode(cms_expandx.expand_key,
'dateEnd',
cms_expandx.expand_value)+
1000 * 60 * 60 * 8) /
(1000 * 60 * 60 * 24),
'yyyy-MM-dd')),
'yyyy-MM-dd') - trunc(SYSDATE))) AS days2
FROM cms_online_resource_expand cms_expandx,
cms_online_resource_props cms_props
WHERE cms_expandx.resource_id = cms_props.resource_id
AND (cms_props.resource_path LIKE
'/sites/creditcard.pingan.com/youhuihuodong/%.shtml'OR
cms_props.resource_path LIKE
'/sites/creditcard.pingan.com/youhuinew/%')
AND (cms_props.resource_type = '8027'OR
cms_props.resource_type = '19010')
GROUP BYcms_props.resource_id
HAVINGMAX(decode(cms_expandx.expand_key,'notPublish', cms_expandx.expand_value)) = 'false'ORMAX(cms_props.ispublish) = '1'
ORDER BY istopindex DESC,
recomindex DESC,
MAX(cms_props.date_released) DESC
) d;
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
