像这样的语句要怎么优化,5000条需要2秒,太慢了。
能否顺便介绍下mysql优化的书籍或网站呢?
set @d=now();
select unix_timestamp(date_format(from_unixtime(a.`time`),'%Y-%m-%d %H:%i:00')) as dtt,
a.srcip,a.destip,a.srcdomainid,a.destdomainid,a.userid,d.access_policy,count(b.id) as http_recordnum,
b.op as http_op,count(c.id) as ftp_recordnum,c.op as ftp_op,c.type as ftp_type,sum(e.inbytes) as inbytes,
sum(e.inpkts) as inpkts,sum(e.outbytes) as outbytes,sum(e.outpkts) as outpkts from fw_koma_20100813_s a
left join fw_koma_20100813_apphttp b on a.sessionkey=b.sessionkey left join hdlogfw.fw_koma_20100813_appftp c
on a.sessionkey=c.sessionkey,fw_koma_20100813_policy d,fw_koma_20100813_flow e where a.id>0
and a.id<=10000 and a.sessionkey=d.sessionkey and a.sessionkey=e.sessionkey group by a.srcip,a.destip,a.srcdomainid,
a.destdomainid,a.userid,d.access_policy,b.op ,c.op ,c.type,a.`time` order by null;
select timestampdiff(second,@d,now());\g
能否顺便介绍下mysql优化的书籍或网站呢?
set @d=now();
select unix_timestamp(date_format(from_unixtime(a.`time`),'%Y-%m-%d %H:%i:00')) as dtt,
a.srcip,a.destip,a.srcdomainid,a.destdomainid,a.userid,d.access_policy,count(b.id) as http_recordnum,
b.op as http_op,count(c.id) as ftp_recordnum,c.op as ftp_op,c.type as ftp_type,sum(e.inbytes) as inbytes,
sum(e.inpkts) as inpkts,sum(e.outbytes) as outbytes,sum(e.outpkts) as outpkts from fw_koma_20100813_s a
left join fw_koma_20100813_apphttp b on a.sessionkey=b.sessionkey left join hdlogfw.fw_koma_20100813_appftp c
on a.sessionkey=c.sessionkey,fw_koma_20100813_policy d,fw_koma_20100813_flow e where a.id>0
and a.id<=10000 and a.sessionkey=d.sessionkey and a.sessionkey=e.sessionkey group by a.srcip,a.destip,a.srcdomainid,
a.destdomainid,a.userid,d.access_policy,b.op ,c.op ,c.type,a.`time` order by null;
select timestampdiff(second,@d,now());\g
from fw_koma_20100813_s a
left join fw_koma_20100813_apphttp b on a.sessionkey=b.sessionkey
left join hdlogfw.fw_koma_20100813_appftp c on a.sessionkey=c.sessionkey,
fw_koma_20100813_policy d,
fw_koma_20100813_flow e
where a.id>0
and a.id<=10000
and a.sessionkey=d.sessionkey
and a.sessionkey=e.sessionkey
group by
a.srcip,
a.destip,
a.srcdomainid,
a.destdomainid,
a.userid,
d.access_policy,
b.op ,
c.op ,
c.type,
a.`time`
order by null;
优化上面这个SQL语句,需要大量信息提供, 主要是相关字段的不同值有多少。
贴出你所有表的show index from ...