谁能把这个sql优化下

2009-10-22 01:25:51 170 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

classnameextendsjava 墨衍会员 · AI 创作全网分发关注 + 加入社区



select 

	A.aaID,

	A.aaName,

	A.aaIP,

	D.ddName,

	E.eeName,

	count(distinct(f.ooID))

from

	A

	join B on A.bbID=B.bbID

	join C on B.ccID=C.ccID

	left join D on A.ddID=D.ddID

	left join E on A.eeID=E.eeID

	left join F on

		(F.xxID=A.aaID and F.yyID=1)

		or

		(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)

		or 

		(exists(select 1 from H,G where H.ggID=G.ggID and G.aaID=A.aaID and H.hhID=F.xxID ) and F.yyID=3) 

where 

	C.ccID= ?

group by 

	A.aaID,

	A.aaName,

	A.aaIP,

	D.ddName,

	E.eeName

这个sql执行需要30秒左右
(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)这个花的时间较长大约25秒 G表里有5W条数据

...全文

Sql语句的优化以及Sql语句优化工具这周在做项目的时候，发现有些Sql语句写的复杂，一个复杂的Sql语句不仅性能比较低下，当涉及到重构的时候也是不容易理解和读懂的，并且一个复杂的sql语句会显著增加程序的耦合度，所以Sql语句的优化就显的很必要了。 Sql语句复杂化的原因首先列举一下Sql语句复杂化的原因: 将业务逻辑由Sql语句实现数据库表的设计不符合第三范式 Sql语句中含有大量冗余的不必要逻辑没有很好的利用视图如何优化Sql语句呢不要超过5个表以上的连接超过5张表以上的连接会大大增加sql引擎的消耗。考虑使用临阅读详情

classnameextendsjava

基础和管理 17,379 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

classnameextendsjava 2009-10-22

打赏
举报

只能拆成小块写了

超叔csdn 2009-10-22

打赏
举报

不知道这样是不是会快点？
left join F on
(F.xxID=A.aaID and F.yyID=1)
or
(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)
or
(exists(select 1 from H,G where H.ggID=G.ggID and G.aaID=A.aaID and H.hhID=F.xxID ) and F.yyID=3)

改为
left join (
select distinct f.ooid,f.xxid,
from f
where F.yyID=1
union all
select f.ooID,G.aaID
from f join g on f.xxID=g.ggID
where f.yyID=2
union all
select f.ooid,g.aaID
from f join h on f.xxid=h.hhid
join g on h.ggid=g.ggid
where f.yyid=3
) f on A.aaid=f.xxid

KingSunSha 2009-10-22

打赏
举报

满足F.yyID=2条件的纪录有多少？join G表之后有多少记录？

classnameextendsjava 2009-10-22

打赏
举报

搞错了
select
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName,
from
A
join B on A.bbID=B.bbID
join C on B.ccID=C.ccID and C.ccID= ?
left join D on A.ddID=D.ddID
left join E on A.eeID=E.eeID
left join F on
(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)
0.2秒左右（这里也是25秒 0.2秒时1条的时间)
加上group by
group by
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName
25秒

应该是关联 (exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2的问题

classnameextendsjava 2009-10-22

打赏
举报

select
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName,
from
A
join B on A.bbID=B.bbID
join C on B.ccID=C.ccID and C.ccID= ?
left join D on A.ddID=D.ddID
left join E on A.eeID=E.eeID
1200条数据 0.1秒
加上
group by
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName
还是0.1秒左右

select
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName,
from
A
join B on A.bbID=B.bbID
join C on B.ccID=C.ccID and C.ccID= ?
left join D on A.ddID=D.ddID
left join E on A.eeID=E.eeID
left join F on
(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)
0.2秒左右
加上group by
group by
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName
25秒

select
A.aaID,
A.aaName,
A.aaIP,
D.ddName,
E.eeName,
from
A
join B on A.bbID=B.bbID
join C on B.ccID=C.ccID and C.ccID= ?
left join D on A.ddID=D.ddID
left join E on A.eeID=E.eeID
left join F on
(exists(select 1 from H,G where H.ggID=G.ggID and G.aaID=A.aaID and H.hhID=F.xxID ) and F.yyID=3)
0.2秒左右
加上group by 还是0.2秒左右

shiyiwan 2009-10-22

打赏
举报

#7

用了DISTINCT可以用UNION ALL了

王铎开 2009-10-22

打赏
举报

G表有没有索引？

王铎开 2009-10-22

打赏
举报

G表有没有主键？

KingSunSha 2009-10-22

打赏
举报

改掉把对F表的左联接,转换成UNION



SELECT   aaid, aaname, aaip, ddname, eename, COUNT (ooid)

    FROM (SELECT DISTINCT a.aaid, a.aaname, a.aaip, d.ddname, e.eename, f.ooid

                     FROM a, b, c, d, e, f

                    WHERE a.bbid = b.bbid

                      AND b.ccid = c.ccid

                      AND a.ddid = d.ddid(+)

                      AND a.eeid = e.eeid(+)

                      AND a.aaid = f.xxid

                      AND f.yyid = 1

                      AND c.ccid = :1

          UNION

          SELECT DISTINCT a.aaid, a.aaname, a.aaip, d.ddname, e.eename, f.ooid

                     FROM a, b, c, d, e, f, g

                    WHERE a.bbid = b.bbid

                      AND b.ccid = c.ccid

                      AND a.ddid = d.ddid(+)

                      AND a.eeid = e.eeid(+)

                      AND f.yyid = 2

                      AND g.aaid = a.aaid

                      AND g.ggid = f.xxid

                      AND c.ccid = :1

          UNION

          SELECT DISTINCT a.aaid, a.aaname, a.aaip, d.ddname, e.eename, f.ooid

                     FROM a, b, c, d, e, f, g, h

                    WHERE a.bbid = b.bbid

                      AND b.ccid = c.ccid

                      AND a.ddid = d.ddid(+)

                      AND a.eeid = e.eeid(+)

                      AND f.yyid = 3

                      AND h.ggid = g.ggid

                      AND g.aaid = a.aaid

                      AND h.hhid = f.xxid

                      AND c.ccid = :1)

GROUP BY aaid, aaname, aaip, ddname, eename;

对查询做执行计划分析，把结果贴出来。

KingSunSha 2009-10-22

打赏
举报

先把查询改成Oracle的传统语法



SELECT   a.aaid, a.aaname, a.aaip, d.ddname, e.eename

       , COUNT (DISTINCT (f.ooid))

    FROM a, b, c, d, e, f

   WHERE a.bbid = b.bbid

     AND b.ccid = c.ccid

     AND a.ddid = d.ddid(+)

     AND a.eeid = e.eeid(+)

     AND ( f.yyid(+) = 1 AND (a.aaid = f.xxid(+) )  -- 既然count ooid, 这里为什么要用左联接?

          OR (f.yyid = 2 AND EXISTS (SELECT 1

                                       FROM g

                                      WHERE g.aaid = a.aaid AND g.ggid = f.xxid))

          OR (    f.yyid = 3

              AND EXISTS (

                     SELECT 1

                       FROM h, g

                      WHERE h.ggid = g.ggid

                        AND g.aaid = a.aaid

                        AND h.hhid = f.xxid)))

     AND c.ccid = :1

GROUP BY a.aaid, a.aaname, a.aaip, d.ddname, e.eename;

classnameextendsjava 2009-10-22

打赏
举报

把or改成union拆成3段中间那个要25秒但是时间好像花在group by上面为什么前后两段的group by没有花多少时间呢总共查出来才1200条数据

mark620 2009-10-22

打赏
举报

创建的索引列出来看看。

Adebayor 2009-10-22

打赏
举报

帮顶

小灰狼W 2009-10-22

打赏
举报

两个exists可以写到一起
(exists(select 1 from G where G.aaID=A.aaID and G.ggID=F.xxID) and F.yyID=2)
or
(exists(select 1 from H,G where H.ggID=G.ggID and G.aaID=A.aaID and H.hhID=F.xxID ) and F.yyID=3)
-->
exists(select 1 from g,h where h.ggid(+)=g.ggid
and (G.aaID=A.aaID and G.ggID=F.xxID and F.yyID=2)
or (G.aaID=A.aaID and H.hhID=F.xxID and F.yyID=3 and h.ggid is not null))
效果如何还要看执行计划

hebo2005 2009-10-22

打赏
举报

贴执行计划，表结构、数据、索引情况，然后大家才好分析啊

谁能把这个sql优化下

相关推荐