如何將性能提高到 1200 萬行表？

我在基於 Geonames 的大表（1200 萬條記錄）中的查詢存在性能問題，這是一個只讀數據庫，因此NO DELETE，UPDATE或INSERT only SELECT。

我不時進行一些查詢，然後按不是鍵的不同列（緯度和經度、fcode 和國家、只有名稱等）進行過濾，問題是我的伺服器資源需要 30 多秒才能完成他們。

我製作了視圖和小表（大表的複製，但只有一個國家的數據）來檢查如何改進它。

使用視圖，我得到與大表相似的結果，並且使用解釋我看到視圖檢查的行數與大表相同（1200 萬行）

在其中一張小表中，我得到的時間少於 200 毫秒，或多或少取決於表的大小。

我不是數據庫專家，但在小表中複製數據感覺很尷尬。我不確定這是否是可以在那裡完成的最佳方法。

所有查詢都是從我的後端發送的，所以沒有儲存過程。

不過，按主鍵過濾的查詢工作得非常快！

提前感謝您的任何建議！

更新評論

• 表定義

CREATE TABLE `geoname` (
 `geonameid` INT,
 `name` VARCHAR(200),
 `asciiname` VARCHAR(200),
 `alternatenames` VARCHAR(4000),
 `latitude` DECIMAL(10,7),
 `longitude` DECIMAL(10,7),
 `fclass` VARCHAR(1),
 `fcode` VARCHAR(10),
 `country` VARCHAR(2),
 `cc2` VARCHAR(60),
 `admin1` VARCHAR(20),
 `admin2` VARCHAR(80),
 `admin3` VARCHAR(20),
 `admin4` VARCHAR(20),
 `population` INT,
 `elevation` INT,
 `gtopo30` INT,
 `timezone` VARCHAR(40),
 `moddate` DATE,
 PRIMARY KEY (geonameid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 我可以在那裡添加什麼樣的索引？

我想我一直在使用那張桌子。當您想查看州/省和其他類似的東西時，這很笨拙。

如果這就是你所需要的，那麼肯定會打破一個國家。但不要計劃將所有約 250 個國家分成單獨的表格（加上大洲等）。

VIEWs不是性能增強劑*。*他們可以把笨拙的本性隱藏在一張這樣的桌子上。（特別是由於fcode檢查。）

這可能會有所幫助：

INDEX(fcode, country_code)

WHERE feature_code LIKE 'PCL%' AND ...
WHERE feature_code = 'ADM1' AND country_code = 'ES'

如果您願意提供所需的查詢（而不是視圖）和所需的表，我可以提供更多建議。

經緯度搜尋

Lat/lng 需要比簡單的複合索引更多的工作。建議您從“邊界框”和這兩個複合索引開始：

INDEX(lat, lng),
INDEX(lng, lat)

如果這樣的搜尋不夠快，那麼看看http://mysql.rjweb.org/doc.php/find_nearest_in_mysql中更複雜的方法

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