今天朋友跟我说,有个sql消耗系统资源非常严重,看能不能帮忙优化下

sql本身不复杂,只是关联了四五个表,有个表resultreport 3百多万条数据库,其他几个表才几千条几万条记录

Select A.Lineid,
       A.Linecode,
       A.Linename,
       A.Man,
       A.Time,
       A.Way,
       A.Desc,
       D.Name,
       Count(Distinct B.Hospid) as HospNum,
       C.Lineman,
       C.Carid,
       Sum(decode(E.ReportType, '0', 1, Null)) As SumNomal,
       Sum(decode(E.ReportType, '1', 1, Null)) As SumExecp,
       Sum(decode(E.ReportType, '2', 1, Null)) As SumBacks,
       Sum(decode(E.ReportType, '3', 1, Null)) As SumUnTest
  from LineInfo A
  join LineHosp B
    ON A.Lineid = B.Lineid
  left join Lineclass C
    ON (A.Lineid = C.Lineid and C.Delidate = trunc(sysdate))
  left join dUsers D
    ON C.Lineman = D.Userid
  left join resultreport E
    on ((B.ServCycle like :ServCycle Or Trim(B.ServCycle) = '8') and
       (B.Hospid = E.Hospid and E.State = '1'))
 Group by A.Lineid,
          A.Linecode,
          A.Linename,
          A.Man,
          A.Time,
          A.Way,
          A.Desc,
          D.Name,
          C.Lineman,
          C.Carid
 Order by A.LineCode

cost代价为12万多

把对resultreport 大表有查询的条件,改成子查询,然后再和b表关联

改写后的语句如下:

Select A.Lineid,
       A.Linecode,
       A.Linename,
       A.Man,
       A.Time,
       A.Way,
       A.Desc,
       D.Name,
       Count(Distinct B.Hospid) as HospNum,
       C.Lineman,
       C.Carid,
       Sum(decode(E.ReportType, '0', 1, Null)) As SumNomal,
       Sum(decode(E.ReportType, '1', 1, Null)) As SumExecp,
       Sum(decode(E.ReportType, '2', 1, Null)) As SumBacks,
       Sum(decode(E.ReportType, '3', 1, Null)) As SumUnTest
  from LineInfo A,(select Lineid,Hospid from LineHosp
  where ServCycle like :ServCycle Or Trim(ServCycle) = '8' ) B,Lineclass C,
        dUsers D,(select ReportType,Hospid  from resultreport where State = '1') E
  where A.Lineid = B.Lineid
  and A.Lineid = C.Lineid and C.Delidate = trunc(sysdate)
  and C.Lineman = D.Userid
  and B.Hospid = E.Hospid
 Group by A.Lineid,
          A.Linecode,
          A.Linename,
          A.Man,
          A.Time,
          A.Way,
          A.Desc,
          D.Name,
          C.Lineman,
          C.Carid
 Order by A.LineCode;

cost代价变为1万多,提高了10倍多

这个sql优化主要是重写sql,对大表先查出需要的结果集再和其他小表关联,这样就提高查询速度。

而原来大表和小表直接做关联，扫描的数据更多，从而影响查询速度。