優化經緯度選擇查詢

我的表格如下所示。

CREATE TABLE `gdata` ( 
`alarmTypeID` tinyint(4) NOT NULL DEFAULT '0', 
`fleetID` smallint(11) NOT NULL, 
`fleetGroupID` smallint(11) DEFAULT NULL, 
`fleetSubGroupID` smallint(11) DEFAULT NULL, 
`deviceID` mediumint(11) NOT NULL, 
`vehicleID` mediumint(11) NOT NULL, 
`gDateTime` datetime NOT NULL, 
`insertDateTime` datetime NOT NULL, 
`latitude` float NOT NULL, 
`longitude` float NOT NULL, 
`speed` smallint(11) NOT NULL, 
(see full text) 
ALTER TABLE `gdata` 
ADD PRIMARY KEY (`vehicleID`,`gDateTime`,`alarmTypeID`), 
ADD KEY `gDateTime` (`gDateTime`), 
ADD KEY `fleetID` (`fleetID`,`vehicleID`,`gDateTime`); 
COMMIT; 

我現在的問題是我可以執行如下查詢，EXPLAIN select vehicleID,latitude,longitude from gdata where gDateTime between '2017-07-03 00:00:00' and '2017-07-04 00:00:00' 解釋的結果是

1   SIMPLE  gdata       range   gDateTime   gDateTime   5       1251    100.00  Using index condition   

我有以下函式可用於測試緯度和經度是否為矩形。

DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`root`@`localhost`
   FUNCTION `geoProcessor`(pt POINT, mp MULTIPOLYGON)
       RETURNS int(1)
       DETERMINISTIC
BEGIN 

DECLARE str, xy TEXT; 
DECLARE x, y, p1x, p1y, p2x, p2y, m, xinters DECIMAL(16, 13) DEFAULT 0; 
DECLARE counter INT DEFAULT 0; 
DECLARE p, pb, pe INT DEFAULT 0; 

SELECT MBRWithin(pt, mp) INTO p; 
IF p != 1 OR ISNULL(p) THEN 
RETURN p; 
END IF; 

SELECT X(pt), Y(pt), ASTEXT(mp) INTO x, y, str; 
SET str = REPLACE(str, 'POLYGON((',''); 
SET str = REPLACE(str, '))', ''); 
SET str = CONCAT(str, ','); 

SET pb = 1; 
SET pe = LOCATE(',', str); 
SET xy = SUBSTRING(str, pb, pe - pb); 
SET p = INSTR(xy, ' '); 
SET p1x = SUBSTRING(xy, 1, p - 1); 
SET p1y = SUBSTRING(xy, p + 1); 
SET str = CONCAT(str, xy, ','); 

WHILE pe > 0 DO 
SET xy = SUBSTRING(str, pb, pe - pb); 
SET p = INSTR(xy, ' '); 
SET p2x = SUBSTRING(xy, 1, p - 1); 
SET p2y = SUBSTRING(xy, p + 1); 
IF p1y < p2y THEN SET m = p1y; ELSE SET m = p2y; END IF; 
IF y > m THEN 
IF p1y > p2y THEN SET m = p1y; ELSE SET m = p2y; END IF; 
IF y <= m THEN 
IF p1x > p2x THEN SET m = p1x; ELSE SET m = p2x; END IF; 
IF x <= m THEN 
IF p1y != p2y THEN 
SET xinters = (y - p1y) * (p2x - p1x) / (p2y - p1y) + p1x; 
END IF; 
IF p1x = p2x OR x <= xinters THEN 
SET counter = counter + 1; 
END IF; 
END IF; 
END IF; 
END IF; 
SET p1x = p2x; 
SET p1y = p2y; 
SET pb = pe + 1; 
SET pe = LOCATE(',', str, pb); 
END WHILE; 

RETURN counter % 2; 

END$$

DELIMITER ;

我現在的問題是，我想找出哪個車輛在時間範圍值內的矩形內。我必須執行兩個單獨的查詢，首先執行第一個查詢獲取 lat、long 值，然後使用第二個查詢測試它是否在矩形內。如何優化這個？

要查找在特定日期哪些車輛在特定的“矩形”（“邊界框”）中，

SELECT ...
   FROM gdata
   WHERE gDateTime >= '2017-07-03'
     AND gDateTime  < '2017-07-03' + INTERVAL 1 DAY
     AND latitude > 34.567
     AND latitude < 35.678
     AND longitude > 123.456
     AND longitude < 124.567;

優化器無法完美地優化查詢，但您可以給它 3 個選擇，並希望它選擇最好的：

INDEX(gDateTime),
INDEX(latitude),
INDEX(longitude)

由於每一列都在一個“範圍”中使用，因此將兩列放在一個索引中沒有任何優勢。（僅使用第一列。）

或者，使用 SPATIAL 索引…

如果它是一個多邊形，而不是一個簡單的矩形，那麼就像

SELECT ...
   FROM gdata
   WHERE gDateTime >= '2017-07-03'
     AND gDateTime  < '2017-07-03' + INTERVAL 1 DAY
     AND MBRWithin(pt, ...);

和

• pt 是一個包含 POINT 的額外列
• 空間(pt)
• ‘…’ 是從其他地方預先計算的多邊形

不要在WHERE子句中呼叫你的 FUNCTION ；那將是非常低效的。

由於您只想要一個列表vehicleID，而不是所有位置，這可能會更好：

SELECT DISTINCT vehicleID
   FROM gdata
   WHERE ...

DISTINCT是一種退化的情況GROUP BY。我更喜歡DISTINCT在這種情況下使用，因為它可以更清楚地說明發生了什麼。

分區？

PARTITION BY RANGE(TO_DAYS(gDateTime))使用 20-50 個分區可能有助於提高性能，尤其是在表有數十億行長的情況下。分區會將焦點縮小到一個（可能更多）分區，然後僅在該分區內進行空間（或緯度/經度）查找。

此外，分區是清除“舊”數據的絕佳方式。

我的分區部落格的案例 1 和 2討論了這兩個想法。

意外地，我有一個包含 50+ 百萬 NMEA 行的表的數據庫。該表具有復雜索引（時間戳、緯度、經度）。簡單查詢已啟動：

SELECT DISTINCT vehicle_id
 FROM nmea 
WHERE ts  BETWEEN '2016-05-10' AND '2016-05-11'
  AND lat BETWEEN 50.00 AND 50.20
  AND lon BETWEEN 30.00 AND 30.20
;

所以我得到了：

+------------+
| vehicle_id |
+------------+
|      12787 |
|       3123 |
|       9734 |
|      11201 |
|       7241 |
|      21002 |
|       4509 |
+------------+
7 rows in set (0.01 sec)

EXPLAIN顯示下一個（有點修剪）：

+----+-------------+-------+-------+------------------+------------+---------+------+-------+----------------------------------------+
| id | select_type | table | type  | possible_keys    | key        | key_len | ref  | rows  | Extra                                  |
+----+-------------+-------+-------+------------------+------------+---------+------+-------+----------------------------------------+
|  1 | SIMPLE      | w     | range | ...ts_lat_lon... | ts_lat_lon | 12      | NULL | 57362 | Using index condition; Using temporary |
+----+-------------+-------+-------+------------------+------------+---------+------+-------+----------------------------------------+
1 row in set (0.00 sec)

BETWEEN在這裡我們可以看到，使用正確索引的 s沒有任何懲罰。

這是建議：經緯座標在本初子午線附近有異常。每一個被它剖析的矩形都應該被分割成PM左右兩個不同的矩形，分別檢查。

引用自：https://dba.stackexchange.com/questions/178084