mysql查询大间隔数据会变得很慢

zwbhwa 2015-11-03 05:26:37

SELECT *

FROM HistoryGPSData h 

WHERE h.UTC BETWEEN ('2015-11-02 12:00:45') AND ('2015-11-02 13:00:45') 

AND SUResourceInfo_Index=1 

AND (

(h.PUID='151000000012132264' AND h.ResIdx='0') 

OR (h.PUID='151000000028909480' AND h.ResIdx='0') 

OR (h.PUID='151000000045686696' AND h.ResIdx='0') 

OR (h.PUID='151000000062463912' AND h.ResIdx='0') 

OR (h.PUID='151000000079241128' AND h.ResIdx='0') 

OR (h.PUID='151000000096018344' AND h.ResIdx='0') 

OR (h.PUID='151000000112795560' AND h.ResIdx='0') 

OR (h.PUID='151000000129572776' AND h.ResIdx='0') 

OR (h.PUID='151000000146349992' AND h.ResIdx='0') 

OR (h.PUID='151000000163127208' AND h.ResIdx='0')

) 

ORDER BY h.Index LIMIT 50

puid是设备号
utc是时间
SUResourceInfo_Index 、PUID、 ResIdx、 UTC 这四个列有个联合索引
所有数据都在HistoryGPSData一张表里有几亿数据
在查询的时候要是查询1个小时内10个设备的数据很快
要是查询10个小时内的1个设备的数据就很慢
明明两者的数据量是一样多的这是为什么查询会变慢该怎么优化希望大神来解答

...全文

200 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

Rotel-刘志东 2015-11-09

打赏
举报

between ... and mysql不会利用索引的。

阳泉酒家小当家 2015-11-06

打赏
举报

语句改成这样：

SELECT *
FROM HistoryGPSData h 
WHERE h.UTC BETWEEN ('2015-11-02 12:00:45') AND ('2015-11-02 13:00:45') 
AND SUResourceInfo_Index=1 
AND h.ResIdx='0'
AND (
(h.PUID='151000000012132264' ) 
OR (h.PUID='151000000028909480' ) 
OR (h.PUID='151000000045686696' ) 
OR (h.PUID='151000000062463912' ) 
OR (h.PUID='151000000079241128' ) 
OR (h.PUID='151000000096018344' ) 
OR (h.PUID='151000000112795560' ) 
OR (h.PUID='151000000129572776' ) 
OR (h.PUID='151000000146349992' ) 
OR (h.PUID='151000000163127208' )
) 
ORDER BY h.Index LIMIT 50

索引建议改成这样： SUResourceInfo_Index 、 ResIdx、PUID、 UTC

Rotel-刘志东 2015-11-04

打赏
举报

在一下执行计划explain

Rotel-刘志东 2015-11-04

打赏
举报

SELECT * FROM HistoryGPSData h WHERE h.UTC >= '2015-11-02 12:00:45' and h.UTC<= '2015-11-02 13:00:45' AND SUResourceInfo_Index=1 AND ( (h.PUID='151000000012132264' AND h.ResIdx='0') OR (h.PUID='151000000028909480' AND h.ResIdx='0') OR (h.PUID='151000000045686696' AND h.ResIdx='0') OR (h.PUID='151000000062463912' AND h.ResIdx='0') OR (h.PUID='151000000079241128' AND h.ResIdx='0') OR (h.PUID='151000000096018344' AND h.ResIdx='0') OR (h.PUID='151000000112795560' AND h.ResIdx='0') OR (h.PUID='151000000129572776' AND h.ResIdx='0') OR (h.PUID='151000000146349992' AND h.ResIdx='0') OR (h.PUID='151000000163127208' AND h.ResIdx='0') ) ORDER BY h.Index LIMIT 50

qiuchu2003 2015-11-04

打赏
举报


SELECT *
FROM HistoryGPSData h 
WHERE h.UTC BETWEEN ('2015-11-02 12:00:45') AND ('2015-11-02 13:00:45') 
AND SUResourceInfo_Index=1 
AND h.PUID IN('151000000012132264','151000000028909480','151000000045686696','151000000062463912','151000000079241128','151000000096018344','151000000112795560','151000000129572776','151000000146349992','151000000163127208')
 AND h.ResIdx='0'
ORDER BY h.Index LIMIT 50;

试试这个呢

zwbhwa 2015-11-04