mysql innodb 索引：更好的一大還是許多緊湊？

我有一個關於如何建構 innodb 索引的專家的問題（mysql 8.0.18）

假設我在一個有 10 億行的表上有 4 個 varchar 列：

country, state, city, attraction

我有查詢要按國家、州、城市或景點名稱查找所有或某些景點。

query1: "select * from table where attraction like 'asd%' and country = 'X'"  
query2: "select * from table where attraction like 'asd%' and country = 'X' and state = 'Y' and city = 'Z'"    
query3: "select distinct attraction from table where country='X'"     
query4: "select distinct attraction from table where attraction like 'Ux%'



Combined index: (attraction, country, state, city)  

該索引將涵蓋所有 4 個查詢。

與專用索引相比，我可以期望 query1、3、4 的性能相似嗎？

Specialized index1:  (attraction, country)  
Specialized index2:  (attraction)  

我沒有時間深入研究 innodb 儲存的細節，我希望有人已經這樣做了；）

我對此的主要想法：

1. 更多的索引將需要更多的記憶體和儲存（假設有十億行），所以這是一個問題。
2. 如果在只需要一列（第一列）或兩列（前兩列）的查詢上呼叫為 4 列創建的索引，則數據訪問是順序的，並且與具有小型專用索引（基本上包含重複的索引）時一樣有效數據）？

那麼我應該有一個索引，涵蓋所有 4 個查詢或 3 個索引的 WHERE 要求，每個索引都專用於它所服務的查詢嗎？

danblack 的文章回答了有關您查詢的最佳索引策略的主要問題。

但是，我會添加一個有時被遺忘的索引策略優化，它在最新版本的 RDBM（MySQL、MariaDB、PostgreSQL…）中實現：覆蓋索引

**覆蓋索引的定義：（**來自MySQL 文件）

包含查詢檢索到的所有列的索引。查詢不是使用索引值作為指針來查找完整的表行，而是從索引結構中返回值，從而節省磁碟 I/O

這意味著您的第三個查詢：

select distinct attraction from table where country='X'

(country, attraction)比簡單的索引更能從索引中受益(country)。

MySQL 引用 ( country=X) 元素應attraction like 'asd%'位於索引中的範圍 () 之前。

要覆蓋 query4，需要一個attraction索引。

涵蓋 query1 和 query2 的索引將是（國家，吸引力）。

理想的 query3 索引將是(country, state, city, attraction)，但是如果國家/地區、景點查詢充分縮小搜尋(country, attraction, state, city)範圍，可以以合理的效率使用，它將縮小到國家和範圍搜尋景點標準並在經過此範圍時根據州/城市進行過濾. 這是對 query1/query2 優化索引的方便擴展。

基於此，我建議：

• 指數(attraction)
• 指數(country, attraction, state, city)

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