在阿里云上部署了了mysql5.7.19,数据库里有34907,通过下列查询语句过滤了28476数据,花费了17秒钟,而本地服务器上花费不到1s。
SELECT
ID,
TRACK_ID,
LNG,
LAT,
`UTC_TIME`,
SPEED,
HEADING,
NO_OF_SATELLITES,
IGNITION,
MAIN_POWER_STATUS,
GSM_SIGNAL_STRENGTH,
GPS_FIX,
PACKET_STATUS
FROM
TRACK A
WHERE
TRACK_ID = '354972060039700'
AND `UTC_TIME` >= 1517189160000
AND `UTC_TIME` <= 1517880360000
ORDER BY
ID
profile分析情况,其中去掉ORDER BY ID的分析图:
有ORDER BY ID的分析图:
表结构、索引和状态定义如下:
explain图:
SELECT
ID,
TRACK_ID,
LNG,
LAT,
`UTC_TIME`,
SPEED,
HEADING,
NO_OF_SATELLITES,
IGNITION,
MAIN_POWER_STATUS,
GSM_SIGNAL_STRENGTH,
GPS_FIX,
PACKET_STATUS
FROM
TRACK A
WHERE
TRACK_ID = '354972060039700'
AND `UTC_TIME` >= 1517189160000
AND `UTC_TIME` <= 1517880360000
ORDER BY
ID
profile分析情况,其中去掉ORDER BY ID的分析图:
有ORDER BY ID的分析图:
表结构、索引和状态定义如下:
explain图:
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货