问答首页 → 综合技术
0 0

请教一个mysql 的sql优化10

sql查询数据量大概是20W左右，必要的地方已经创建了索引了，但是执行时间还是在30秒，现在要优化到3秒左右，没有具体办法了

sql查询语句

 SELECT v.AnalyzerDATVersion as VER_X,count(distinct v.CLIENT_ID) as NUMBER,round(count(distinct v.CLIENT_ID)/t.TOTAL*100,2) 
as RATE from VIRUS_EVENTS_INFO as v
left join VIRUS_ComputerProperties as p on (p.CLIENT_ID=v.CLIENT_ID and p.SRC_SYSTEM=v.SRC_SYSTEM)
left join VIRUS_COMPUTER_ACCOUNT as ca on (ca.CLIENT_ID=p.CLIENT_ID and ca.SRC_SYSTEM=p.SRC_SYSTEM)
left join COMM_ACCOUNT as a on a.EMPLOYEE_NO=ca.EMPLOYEE_NO
inner join
(select count(*) as TOTAL from VIRUS_ComputerProperties v  WHERE v.SRC_SYSTEM='Mcafee'    ) as t 
 WHERE v.SRC_SYSTEM='Mcafee'    group by  VER_X   
UNION  

SELECT "9999999999"  AS VER_X,count(distinct temp.INFECTED_MAC) as NUMBER,
round(count(distinct temp.INFECTED_MAC)/t.TOTAL*100,2) as RATE 
from (select max(VIRUS_LIB_VER_X+0) as VIRUS_LIB_VER_X,INFECTED_MAC,temp.EMPLOYEE_NO as EMPLOYEE_NO, temp.SRC_SYSTEM as SRC_SYSTEM 
from SOC_VIRUS_AUDIT_INFO temp left join COMM_EMPLOYEE em on (temp.employee_no=em.employee_no) 
where temp.INFECTED_MAC!='' and temp.INFECTED_MAC is not null AND temp.SRC_SYSTEM='Mcafee'    group by INFECTED_MAC) temp 

left join COMM_EMPLOYEE em on (temp.employee_no=em.employee_no) 
inner join (select count(distinct INFECTED_MAC) AS TOTAL 
from SOC_VIRUS_AUDIT_INFO temp left join COMM_EMPLOYEE em on (temp.employee_no=em.employee_no)
where temp.INFECTED_MAC!='' and temp.INFECTED_MAC is not null AND temp.SRC_SYSTEM='Mcafee'   ) t 
where temp.INFECTED_MAC!='' and temp.INFECTED_MAC is not null  AND temp.SRC_SYSTEM='Mcafee'     group by  "9999999999"  order by  VER_X+0 desc ,RATE+0 desc limit 14

 其实主要费时在 UNION  的前半段，后半段的执行时间在0.07秒左右，所以主要是前半段问题，VIRUS_COMPUTER_ACCOUNT 是个视图 表

COMM_ACCOUNT ，VIRUS_EVENTS_INFO VIRUS_ComputerProperties 中的on字段后用到的条件字段都加了索引,求教一下看哪里还需要继续深入优化的。

MySQL数据库 
2010年2月23日 11:01
目前还没有答案

相关推荐

Magicbox
Ask-but
我的问答 FAQ | 勋章

相关推荐

已解决问题

未解决问题

排行榜

Global site tag (gtag.js) - Google Analytics