對索引不正確的 600gb 表執行查詢

這是我在這裡的第一篇文章，我對此感到非常困惑。我加入了一個團隊，在過去的某個地方創建了一個可怕的表，該表已經膨脹到 600+gb 的數據點。

本來這個數據應該是針對一個州的，但是全國的數據已經在這裡儲存了很長一段時間了。

我負責執行查詢以LATITUDE/LONGITUDE在特定日期範圍內隔離這個重要數據集中的邊界框。

更糟糕的是，該數據庫位於一台舊伺服器上，該伺服器執行在一個只有大約 4GB 記憶體的四核 p3 處理器上，因此速度非常慢。

我想執行的查詢，但由於顯而易見的原因，只看一眼：

INSERT INTO gpsdata.vehicle_location_wa SELECT * FROM gpsdata.vehicle_location 
WHERE LOCATION_TIMESTAMP BETWEEN '2014-03-20' AND '2014-07-20' 
AND LATITUDE BETWEEN 46.86275036 AND 47.80149551
AND LONGITUDE BETWEEN -122.44599707 AND -116.94458691;

表結構：

CREATE TABLE `vehicle_location` (  
`SEQ_ID` decimal(10 , 0 ) default NULL,  
`DEVICE_ID` varchar(100) collate utf8_unicode_ci default NULL,  
`DATA_TYPE` varchar(80) collate utf8_unicode_ci default NULL,  
`DATA_DESC` varchar(180) collate utf8_unicode_ci default NULL,  
`SPEED` decimal(6 , 3 ) default '0.000', 
`SPEED_UOM` char(3) collate utf8_unicode_ci default NULL,  
`DIRECTION` decimal(3 , 0 ) default NULL,  
`DURATION` decimal(10 , 0 ) default '0',  
`LATITUDE` decimal(16 , 13 ) default NULL,  
`LONGITUDE` decimal(16 , 13 ) default NULL,  
`GPS_STATUS` tinyint(4) default NULL,  
`LOCATION_TIMESTAMP` datetime default NULL,  
`TIMEZONE` tinyint(1) default NULL,  
`DST` tinyint(1) default NULL,  
`STATUS` tinyint(1) default NULL,  
`MILEAGE` decimal(15 , 2 ) default '0.00',  
`QUEUE_TIME` datetime default NULL,  
`OBSERVATION_TIME` datetime default NULL,  
`ID` BIGINT NOT NULL auto_increment,  
PRIMARY KEY (`ID`),  
KEY `DEVICE_ID` (`DEVICE_ID`),  
KEY `Location_Timestamp` (`LOCATION_TIMESTAMP`),  
KEY `Data_Type` (`DATA_TYPE`)  
)  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE = utf8_unicode_ci  

我想過要做的一些事情：

• 僅按日期範圍選擇具有兩個額外索引的新表：LATITUDE和LONGITUDE $$ Seems like best option $$
• 將數據庫移動到具有大量 ram 負載的速度更快的機器上（這有其自身的問題，整個數據庫在一次 raid 中是 3.5 tb 的數據）
• 向表中添加索引 ( gasp )

我希望在我開始執行可能需要數天才能處理的查詢之前，有更多經驗的人可以幫助指導我正確的方向或確認我的一些想法。

謝謝您的幫助！

您有一個大問題，原因有一個：BTree 索引（這是 InnoDB 唯一可用的格式）對於過濾超過 1 個範圍的效率非常低。

您必須了解一件事：一般來說，每個表訪問使用超過 1 個索引是不可能/有效的（在某些情況下 union_merge 更快，但這是一個例外） - 通常，對於您想要的 AND 條件多個欄位的單個索引。

所以，那沒問題，不是嗎？我們只是索引(LOCATION_TIMESTAMP, LATITUDE, LONGITUDE)對嗎？抱歉，沒有。由於您對所有列的條件使用範圍，因此只會使用索引的第一列。原因很容易看到，但不畫樹很難解釋，所以我希望你能相信我，我稍後會解釋為什麼。

有一個例外，這是 5.6 中發現的一個新功能，稱為索引條件下推（這並不能完全解決問題，但使 3 列索引比 1 列索引更有用）。

那麼有哪些選擇呢？

1. 將一些條件轉換為ref類型，也就是說，將它們轉換為相等檢查。這是可以通過時間戳完成的事情。聽起來可能有點奇怪，但索引 (LOCATION_TIMESTAMP, LATITUDE) 並有這樣的查詢：

WHERE LOCATION_TIMESTAMP IN ('2014-03-20', '2014-03-21', ..., '2014-07-20') AND 
LATITUDE BETWEEN 46.86275036 AND 47.80149551;

實際上可能要快得多。當然，這取決於子句的選擇性等。

我們可以對 LONGITUDE 做同樣的事情嗎？LATITUDE_INT在相同的情況下，是的，您可以創建一個名為with的附加欄位floor(LATITUDE)，並執行以下操作：

WHERE LOCATION_TIMESTAMP IN ('2014-03-20', '2014-03-21', ..., '2014-07-20') AND 
LATITUDE_INT IN (46, 47) AND
LATITUDE BETWEEN 46.86275036 AND 47.80149551 AND 
LONGITUDE BETWEEN -122.44599707 AND -116.94458691;

在這種情況下，將使用索引的 3 列。但坦率地說，這開始變得有點瘋狂。 2. 使用地理空間擴展。MySQL 支持諸如 POINT 之類的空間類型，儘管恰好適用於地理應用程序，並且具有自己的索引 ( R-trees )，它可以同時在多個維度上進行索引-因此它們可以加速多範圍搜尋-並擁有自己的優化一組邊界框功能（MBRContains()是你想要的）。

這很棒，不是嗎？壞消息：它僅適用於 5.5 和 5.6 中的 MyISAM，並且僅適用於 5.7 中的 InnoDB。更改也不是很透明（不像添加索引那麼容易）。

因此，您有一個未索引的表是可能的，因為您的選項非常狹窄，特別是對於 5.5。如果您對錶結構（如您所建議的分區）或執行其他技巧有點靈活，有一些方法可以繞過它，但需要注意。

正是因為這個原因，有些人最終將 MySQL 與外部工具結合起來，而我們直到 5.7 才會有真正的解決方案。

最後，還有大表的問題。這可能會成為一個問題。只有當主鍵的大部分非葉部分都適合記憶體時，InnoDB 才有效。正如 ypercube 所說，4GB 伺服器上的 3.5TB 表令人恐懼。插入該表必須很慢。您將需要更多記憶體並考慮對其進行分區，例如，如您所說，按狀態（如果您的查詢可以）或按時間戳進行分區，並利用手動或自動修剪。

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