数据源:tthslog
Fi_SerNo Fi_DeviceID Fd_Temperature Fd_humidity Fs_date
编号(自增INT) 设备ID号(INT) 温度 湿度 记录日期操作:从上述表中查询出每一台设备的最新温度和湿度记录我的SQL语句:SELECT max(fs_date),Fi_DeviceID,Fd_Temperature,Fd_humidity From tthslog GROUP BY Fi_DeviceID前期还很正常,现在数据量达到百万级别就特别慢,要好几分钟,甚至查不出来了,
请教高手帮忙优化下SQL语句(最好不用Group By)注:现在有大约150W条记录,唯一索引是Fi_SerNo列,此外还有(Fi_DeviceID,Fs_Date联合索引)我目前采用的办法是按设备ID号Fi_DeviceID构造多条SQL语句分别查询,很原始.效率稍微好了点,但是通讯数据包又太臃肿..新手上路,分不多,谢谢各位!
Fi_SerNo Fi_DeviceID Fd_Temperature Fd_humidity Fs_date
编号(自增INT) 设备ID号(INT) 温度 湿度 记录日期操作:从上述表中查询出每一台设备的最新温度和湿度记录我的SQL语句:SELECT max(fs_date),Fi_DeviceID,Fd_Temperature,Fd_humidity From tthslog GROUP BY Fi_DeviceID前期还很正常,现在数据量达到百万级别就特别慢,要好几分钟,甚至查不出来了,
请教高手帮忙优化下SQL语句(最好不用Group By)注:现在有大约150W条记录,唯一索引是Fi_SerNo列,此外还有(Fi_DeviceID,Fs_Date联合索引)我目前采用的办法是按设备ID号Fi_DeviceID构造多条SQL语句分别查询,很原始.效率稍微好了点,但是通讯数据包又太臃肿..新手上路,分不多,谢谢各位!
select t1.* from tthslog t1 left join
(select Fi_DeviceID,max(Fs_date) fd from tthslog group by Fi_DeviceID) t2
on t1.Fi_DeviceID=t2.Fi_DeviceID and t1.Fs_date=t2.fd
where t2.Fi_DeviceID is not null;
select t1.* from tthslog t1 inner join
(select Fi_DeviceID,max(Fs_date) fd from tthslog group by Fi_DeviceID) t2
on t1.Fi_DeviceID=t2.Fi_DeviceID and t1.Fs_date=t2.fd
where t2.Fi_DeviceID is not null;在Fi_DeviceID建立索引
where not exist(select 1 from tthslog where 1.Fi_DeviceID=t2.Fi_DeviceID and t1.Fs_date<Fs_date)
Fi_DeviceID,Fs_Date联合索引去掉,单独在Fi_DeviceID建立Btree索引,group by 也有个sort过程
如果not exists加子查询嵌套能比group by 好用,mysql的算法也太失败了……
from tthslog a inner join (select Fi_DeviceID,max(Fi_SerNo) as Fi_SerNo from tthslog group by Fi_DeviceID) b
on a.Fi_SerNo=b.Fi_SerNo
创建 (Fi_DeviceID,Fi_SerNo DESC) 的复合索引
select t1.* from tthslog t1
where not exist(select 1 from tthslog where t1.Fi_DeviceID=Fi_DeviceID and t1.Fs_date <Fs_date)
囧,还是没法通过语法检查:
错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select 1 from tthslog t2 where t1.Fi_DeviceID=t2.Fi_DeviceID and t1.Fs_date < t2' at line 2Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
WHERE NOT EXISTS(SELECT 1 FROM tthslog WHERE t1.Fi_DeviceID=Fi_DeviceID AND t1.Fs_date <Fs_date)
少一个S
select t1.* from tthslog t1
where not exists(select 1 from tthslog where t1.Fi_DeviceID=Fi_DeviceID and t1.Fs_date <Fs_date)