MySQL如何構建數據表索引

Posted on by

理解索引概念最簡單的方式是通過一個案例來進行，以下就是這樣的一個案例。

假設我們需要設計一個在線的約會網站，這個網站的用戶資料有許多列，例如國籍、省份、城市、性別、年齡、眼睛顏色等等。這個網站必須支持通過多種組合方式搜索用戶資料。同時，也需要支持支持排序和根據用戶最近在線時間和其他用戶的評價返回有限的結果等等。對於這種復雜場景我們如何設計索引？

有點奇怪，首先要做的事情是要決定我們是否必須使用索引排序，或者檢索後再排序是否能夠接受。索引排序限制瞭索引和查詢構建的方式。例如，在WHERE age BETWEEN 18 AND 25這樣的查詢條件和基於其他用戶評價排序的場景中，我們不能使用同一個索引。如果MySQL在范圍查詢中使用瞭一個索引，那就沒法在排序中使用另一個索引。假設這是一個最常用的WHERE條件，同時我們還需要支持大多數查詢都可以排序。

支持多種類型的過濾

現在我們需要看看哪些列的值比較分散以及哪些列在WHERE條件中最常出現。數據列值比較分散的篩選性很好。這通常會是一個好事情，因為這讓MySQL可以將高效過濾掉不相關的數據行。

國籍列可能篩選性不太好，但卻可能是最常查詢的。性別列通常不具備篩選性，但卻也經常用於查詢。基於這樣的認識，我們為許多不同的列的組合創建瞭一系列的索引，這些索引使用(sex, country)開頭。

傳統的認知是對於低篩選性的列構建索引是沒用的。那我們為什麼要在每個索引開頭都加上不具篩選性的列？ 我們有兩個理由這麼做。第一個理由是，如前所述，基本每個查詢都會使用性別。我們甚至設計瞭用戶一次隻能搜索一個性別。但更重要的是，增加這樣的列並沒有多少缺點，因為我們使用瞭一個小招數。

這是我們的招數：即便不限制性別查詢，我們也能夠保證在WHERE語句中加上AND sex IN(‘m’, ‘f’)讓索引生效。這不會過濾掉我們所需要的行，因此與WHERE語句中不包含性別作用相同。然而，因為MySQL會在更多列的索引中前置這個列，我們需要包含這個列。這個招術在這樣的場景下有效，但是如果是這個列具有很多不同的值，那反而不起作用，這是因為這會導致IN()中的列過多。

這個例子闡述瞭一個基本的原則：在數據表設計上保留所有的選項。當你設計索引的時候，不要隻想著那種查詢中的那類索引，也同時考慮優化查詢。當你需要一個索引卻發現其他查詢可能會受其影響，你應該先問問自己能否改變查詢。你應該同時優化查詢和索引去找到解決之道。你不一定需要設計完美的索引。

接下來，我們需要考慮可能用到的其他組合的WHERE條件，然後考慮其中的哪些組合在沒有合理索引的情況下會變慢。(sex, country, age)這樣的索引是很明顯的選擇，但我們也可能需要(sex, country, region, age)和(sex, country, region, city, age)這樣的索引。

這會導致需要建立很多的索引。如果我們能夠重復利用索引，那就不會產生過多的組合。我們可以使用IN()這種小招數來去掉(sex, country, age)和(sex, country, region, age)索引。如果這些列在搜索表單中沒有指定，我們可以使用國傢清單、地區清單來保證滿足索引前置的約束（全部國傢，全部地區和全部性別的組合可能很多）。

這些索引會滿足指定的大部分搜索查詢，但我們如何設計那些不那麼常見的篩選，例如上傳瞭圖片(has_pictures)，眼睛顏色（eye_color），頭發顏色（hair_color）和教育水平（education）？如果這些列不是那麼具有篩選性並且不那麼常用，我們可以直接跳過他們，讓MySQL去掃描額外的一些數據行。相應地，我們可以在age列前增加他們，並且使用IN()技巧去提前描述以處理那種這些列沒有指定的情況。

你也許註意到我們將age放到瞭索引的最後面。為什麼要特別處理這個列？我們在試圖保證MySQL能夠盡可能多地利用索引列。由於MySQL使用最左匹配規則，直到遇到第一個范圍查詢條件。所有我們提到的列都可以在WHERE語句中使用相等條件，但年齡(age)大概率是范圍查詢。

我們也能夠將范圍查詢改為清單使用IN查詢，例如age IN(18, 19, 20, 21, 22, 23, 24, 25)來替代age BETWEEN 18 AND 25，但這並不總是能夠這麼做。通用的原則是我們盡量將范圍判決條件放到索引的末尾，因此優化器會盡可能地使用索引。

我們提到你可以使用盡可能多的列使用IN查詢去覆蓋那些在WHERE條件中未指定的索引條件。但你可能做得過頭瞭導致新的問題。使用更多的這樣的IN查詢清單導致優化器需要評估大量的組合，這反而可能降低查詢速度。考慮下面的查詢條件語句：

WHERE eye_color 	IN('brown', 'blue', 'hazel')
	AND hair_color	IN('black', 'red', 'blonde', 'brown')
  	AND sex 	IN('M', 'F')

這個優化器會轉變為432=24種組合，WHERE條件會檢查每一種情況。24還不是一個很大的組合數字，但如果數量達到瞭幾千。舊版本的MySQL在IN查詢中數量過多時可能會有更多的問題。查詢優化器會執行更慢並且消耗很多內存。新版本的MySQL會在組合過多時停止評估，但這會影響MySQL使用索引。

避免多個范圍查詢

讓我們假設有一個last_online(最近在線時間）的列，然後我們需要展示最近一周在線的用戶：

WHERE eye_color		IN('brown', 'blue', 'hazel')
	AND hair_color	IN('black', 'red', 'blonde', 'brown')
  	AND sex 	IN('M', 'F')
 	AND last_online	 > DATE_SUB(NOW(), INTERVAL 7 DAY)
 	AND age		BETWEEN 18 AND 25

這個查詢的問題在於它有兩個范圍查詢。MySQL可以使用last_online或age條件，但不能同時使用。 如果last_online約束出現時沒有age約束，或last_online比age更有篩選性，我們可能希望增加另一組索引，將last_online放到最後面。但是如果我們不能將age轉換為IN查詢，而我們也希望能夠在同時有last_oinline和age范圍查詢時提高查詢速度怎麼辦？這個時候，我們沒有直接的方法。但我們可以將一個范圍轉換為相等比較。去這麼做的時候，我們增加一個預先計算的active列，這個列我們會定期維護。如果用戶登錄後，我們標記為1，如果7天內沒有連續登錄則重新標記為0。

這個方法可以讓MySQL使用如(active, sex, country, age)這樣的索引。這個列也許沒那麼精準，但這類查詢也許不需要很高的精準度。如果我們需要精準查詢，我們可以保留last_online在WHERE條件中，但不增加索引。這種技巧與URL查找的情況類似。這種條件不會使用任何索引，因為它不太可能會將索引命中的行給過濾掉。增加索引未必能夠讓查詢收益。

現在，你可以看到這個模式：如果用戶想同時查找活躍和不活躍的結果，我們可以使用IN查詢。我們增加瞭很多這樣的清單查詢，一個變通的方式是通過將各個組合分開的查詢單獨建立索引，例如，我們可以使用如下的索引：(active, sex, country, age)，(active, country, age)，(sex, country, age)和(country, age)。雖然這樣的索引對於特定的查詢可能是更優的選擇，但維護這些組合的負面效果，組合所需的額外存儲空間都可能導致是一個很弱的策略。

這是一個優化器改變後可以真正影響索引優化的案例。如果在未來的MySQL版本中可以真正丟棄索引掃描，它可能能夠在一個索引上使用多個范圍條件，此時我們不再需要通過IN查詢這種方式解決此類問題。

優化排序

最後一個議題是排序。小數據量的結果使用文件排序(filesort)很快，但如果是上百萬行數據呢？例如，如果隻在WHERE條件中指定瞭性別時的排序。

對於這類低篩選性的場景，我們可以增加特定的索引用於排序。例如，一個(sex, rating)的索引可以用於下面的查詢：

SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 10;

這個查詢同時有排序和LIMIT子句，在沒有索引的情況下可能很慢。即便是有索引，這個查詢在用戶界面有分頁查詢，而頁碼不在起始位置附近時也可能很慢。下面的例子的ORDER BY和LIMIT造成瞭一個糟糕的組合：

SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000, 10;

即便有索引，這樣的查詢也可能導致十分嚴重的問題。這是因為很高的偏移會導致花費大量的時間掃描大量的數據，且這些數據會被丟棄。反范式設計，提前計算和緩存可能能夠解決這類查詢的問題。一個更好的策略是限制用戶可查詢的頁碼。這不太可能會降低用戶的體驗，因為實際上不會有人會關心第10000頁的搜索結果。

另一個好的策略是使用推斷聯合查詢，這是我們利用覆蓋索引去獲取主鍵列後再獲取數據行的方式。你可以將需要獲取的列全部聯合，這會減少MySQL收集那些需要丟棄的數據的工作。下面是一個例子：

SELECT <cols> FROM profiles INNER JOIN (
  SELECT <primary key cols> FROM profiles
  WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
AS x USING(<primary key cols>);

以上就是MySQL如何構建數據表索引的詳細內容，更多關於MySQL構建數據表索引的資料請關註WalkonNet其它相關文章！

推薦閱讀：