我有条sql统计语句 查询很慢 求指导优化

[heng722]

历史数据busuab 这里面有大概100W条数据 基础信息表basic
查询一段时间内的 最大值 最大值时间 最小值 最小值时间 然后关联基本信息

我加了 每个字段的索引 但这条语句查询还要10s多 有没有办法优化到1s左右？

select
a.deviceId,d.area_name,d.stationname,d.stationUe,d.devicename,a.q1,a.q2
,a.avg_Uab
,a1.max_Uab, a1.time
,a2.min_Uab, a2.time
,a1.max_Uab-a2.min_Uab
,a.q3
from (
select
COUNT(Uab>10.7)*15 q1,
COUNT(Uab<10)*15 q2,
COUNT(Uab>10 && Uab<10.7)/COUNT(*) q3,
deviceId,
avg(Uab) as avg_Uab
from busuab a
group by deviceId
) as a
join (
SELECT maxb.deviceId,maxb.Uab max_Uab,maxa.time from busuab maxa LEFT JOIN (SELECT deviceId,MAX(Uab) Uab from busuab GROUP BY deviceId,date(time) ) maxb
on maxa.deviceId=maxb.deviceId and maxa.Uab=maxb.Uab where maxb.deviceId is not NULL GROUP BY maxb.deviceId

) as a1 on a1.deviceId = a.deviceId
join (

SELECT minb.deviceId,minb.Uab min_Uab,mina.time from busuab mina LEFT JOIN (SELECT deviceId,MIN(Uab) Uab from busuab GROUP BY deviceId,date(time) ) minb
on mina.deviceId=minb.deviceId and mina.Uab=minb.Uab where minb.deviceId is not null GROUP BY minb.deviceId

) as a2 on a2.deviceId = a.deviceId
LEFT JOIN basic d ON d.deviceID = a.deviceId





CREATE TABLE `busuab` (
`deviceId` bigint(20) DEFAULT NULL,
`deviceName` varchar(255) DEFAULT NULL,
`time` varchar(255) DEFAULT NULL,
`Uab` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `basic` (
`deviceID` bigint(19) NOT NULL AUTO_INCREMENT,
`devicename` char(80) DEFAULT NULL,
`deviceUe` int(11) DEFAULT NULL,
`stationname` char(30) DEFAULT NULL,
`stationID` bigint(19) DEFAULT NULL,
`stationpingyin` char(30) DEFAULT NULL,
`devicenamepingyin` char(80) DEFAULT NULL,
`area_namepingyin` char(20) DEFAULT NULL,
`stationUe` int(11) DEFAULT NULL,
`area_name` char(20) DEFAULT NULL,
`devicetype` char(10) DEFAULT NULL,
`switchID` bigint(20) DEFAULT NULL,
`switchname` char(50) DEFAULT NULL,
`Gear_Mavr` float(5,1) DEFAULT NULL,
PRIMARY KEY (`deviceID`),
KEY `Vavc_TCL_SETindex` (`stationUe`,`stationname`,`devicetype`,`deviceUe`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=117938017036927845 DEFAULT CHARSET=gbk;

[学海无涯-回头是岸]

100w条的 数据 做统计是全部数据的扫描 不能降到1s吧

[heng722]

那怎样做能才能到1s ？