MySQL如何構建數(shù)據(jù)表索引

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

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

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

支持多種類型的過濾

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

國籍列可能篩選性不太好，但卻可能是最常查詢的。性別列通常不具備篩選性，但卻也經常用于查詢?；谶@樣的認識，我們?yōu)樵S多不同的列的組合創(chuàng)建了一系列的索引，這些索引使用(sex, country)開頭。

傳統(tǒng)的認知是對于低篩選性的列構建索引是沒用的。那我們?yōu)槭裁匆诿總€索引開頭都加上不具篩選性的列？ 我們有兩個理由這么做。第一個理由是，如前所述，基本每個查詢都會使用性別。我們甚至設計了用戶一次只能搜索一個性別。但更重要的是，增加這樣的列并沒有多少缺點，因為我們使用了一個小招數(shù)。

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

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

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

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

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

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

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

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

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

這個優(yōu)化器會轉變?yōu)?32=24種組合，WHERE條件會檢查每一種情況。24還不是一個很大的組合數(shù)字，但如果數(shù)量達到了幾千。舊版本的MySQL在IN查詢中數(shù)量過多時可能會有更多的問題。查詢優(yōu)化器會執(zhí)行更慢并且消耗很多內存。新版本的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約束出現(xiàn)時沒有age約束，或last_online比age更有篩選性，我們可能希望增加另一組索引，將last_online放到最后面。但是如果我們不能將age轉換為IN查詢，而我們也希望能夠在同時有l(wèi)ast_oinline和age范圍查詢時提高查詢速度怎么辦？這個時候，我們沒有直接的方法。但我們可以將一個范圍轉換為相等比較。去這么做的時候，我們增加一個預先計算的active列，這個列我們會定期維護。如果用戶登錄后，我們標記為1，如果7天內沒有連續(xù)登錄則重新標記為0。

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

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

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

優(yōu)化排序

最后一個議題是排序。小數(shù)據(jù)量的結果使用文件排序(filesort)很快，但如果是上百萬行數(shù)據(jù)呢？例如，如果只在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;

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

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

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如何構建數(shù)據(jù)表索引的詳細內容，更多關于MySQL構建數(shù)據(jù)表索引的資料請關注腳本之家其它相關文章！