Mysql 索引案例學習

標籤：

　　理解索引最好的辦法是結合樣本，所以這裡準備了一個索引的案例。

　　假設要設計一個線上約會網站，使用者資訊表有很多列，包裹國家，地區，城市，性別，眼睛顏色，等等。完整必須支援上面這些特徵的各種組合來搜尋使用者，還不行一些根據使用者的最後線上時間，其他會員對使用者的屏風等對使用者進行排序並對結果進行限制。如何世界索引滿足上面的負載需求呢？

　　出人意料的是第一件需要考慮的事情是需要使用索引來排序，還是先檢索資料再排序。使用索引排序會嚴格限制索引和查詢的設計。例如，如果希望使用索引做根據其他會員對使用者的評分排序，則WHERE 條件中的 age BETWEEN 18 AND 25 就無法使用索引。如果MySQL使用了某個索引進行範圍查詢，也就無法再使用另一個索引（或者是該索引的後續欄位）進行排序了。如果這是很常見的where 條件，那麼我們當然就會認為很多查詢需要做排序操作。

　　支援多種過濾條件

　　現在需要看看那些列擁有很多不同的取值，那些列在where子句中出現的最繁瑣。在有更多不同值的列上建立索引的選擇性更好。一般來說這樣都是對的，因為可以讓MySQL更有效過濾掉不需要的行。

　　country的選擇性通常不高，但是可能很多查詢都會用到。sex列的選擇性肯定很低，但也會在很多查詢中用到。索引考慮到使用的頻率還是建議在建立不同組合所以你的時候講（sex，country） 作為首碼。

　　但根據傳統的經驗，不是說不應該在選擇性地的列上建立索引了嗎？那麼為什麼這裡需要將戀歌選擇性都很低的欄位作為索引的首碼列？我們的腦子壞了？

　　我們的腦子當然沒有壞。這麼做有兩個理由：低一點，如前面所述幾乎所有的查詢都會用到sex列。前面曾提到，幾乎每一個查詢都會用到sex列，設定會吧網站設計成每次都只能按照某一種姓名搜尋使用者。更重要的一點是，索引中加上這一列也沒有花癡，即使查詢中沒有使用sex列也可以通過下面的訣竅繞過。

　　這個訣竅就是：查詢不限制性別，那麼可以通過在查詢條件中增加AND sex in (‘f‘,‘m‘)。來讓MySQL選擇索引。這樣寫並不會過濾掉如何行，和沒有這個條件時返回的結果相同。但是必須添加上這個條件，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列放在索引的最後面。age列有什麼特殊的地方嗎？為什麼要放在索引的最後？我們總是儘可能讓MySQL使用更多的索引列，因為在查詢中只能使用索引的最左首碼，直到遇到第一個範圍條件列。前面提到的列在WHERE 子句中都是等於條件，但是age列則多半是範圍查詢（例如，18-25歲之間的人）。

　　當然也可以使用in來代替範圍查詢，例如年齡條件改為in(18,19...25),但不是所有的查詢都可以轉換。這裡秒數的原則是，金坑能將需要做範圍查詢的列放在索引的最後面，以便遊虎丘能使用更多的索引列。

　　前面提到的可以在索引中加入更多的列，並通過in（）的方式覆蓋奈爾不在where子句中的列。但這種技巧也不能濫用，否則可能會帶來麻煩。因為每額外添加一個in（）條件，最佳化器需要做的組合將會以指數的形勢增加，最終可能會極大的降低查詢效能。

　　考慮下面WHERE 子句：

　　WHERE eye_color in(‘brown‘,‘blue‘,‘hazel‘)AND hair_colr in (‘black‘,‘red‘,‘blonde‘,‘borwn‘) AND sex in (‘f‘,‘m‘) 最佳化器會轉成3*4*2=24種組合，執行計畫需要價差where子句中所有的24種組合。對於MySQL來說，24種組合并不是很誇張，但如果組合數達到上千個則需要特別小心。老闆嗎的MySQL在in（）組合條件過多時就會有很多問題。查詢最佳化可能花很多時間，並消耗大量的記憶體。新版本的MySQL在組合數超過一定數量後就不再進行執行計畫評估了，可能會導致MySQL不能很好的利用索引。

　　避免多個範圍條件

　　假設我們有一個last_online列並希望通過下面的查詢顯示在過去幾周上限過的使用者：

　　WHERE eye_color in(‘brown‘,‘blue‘,‘hazel‘)

　　　　　　AND hair_colr in (‘black‘,‘red‘,‘blonde‘,‘borwn‘)

　　　　　　AND sex in (‘f‘,‘m‘)

　　　　　　AND last_online > DATE_SUB(NOW(),INTERVAL 7 DAY) 

　　　　　　AND age between 18 and 25

　　這個查詢有一個問題：他有兩個範圍條件 last_online 和age列，MySQL可以使用last_onlie 列索引或者age列索引，但無法同時使用它們。

　　如果條件中只有last_onlie而沒有age，那麼我們可能考慮在索引的後面加上last_onlie列。這裡考慮如果我們無法把age欄位轉換成一個in（）列表，並且人要求對於同時有last_onlie和age這兩個維度範圍查詢的速度很快，那麼該怎麼辦？答案是，很遺憾，沒有一個直接的辦法能夠解決這個問題。但是我們能夠將其中一個範圍查詢轉換成一個簡單的等值比較。為了實現這一點，我們需要事先計算好一個active列，這個欄位由地市任務來維護。黨使用者每次登陸時，將對應值設為1，並且將過去連續7天未曾登陸的使用者的值置為零。

　　這個方法可以讓MySQL使用（active,sex,country,age）索引。active並不是完全精確的，但是對於這個查詢來說，對精度的要求沒有那麼高。如果需要精確的資料，可以吧last_online 列放在where子句中，但不加入到索引中。這和之前通過計算urlhash值來實現url的快速尋找類似。所以這個查詢無法使用任何索引，但因為這個條件的過濾性不高，即使在索引中加入了該列也沒有太大的協助。換個角度說，缺乏合適的索引對該查詢影響也不明顯。

　　到目前位置，我們可以看到：如果使用者希望同時看到活躍和不活躍的使用者，可以在查詢中使用in（）列表。我們已經加入了很多這樣的列表，但另一個可選的方案只能是位不同組合列建立單獨的索引。至少需要建立如下索引（active,sex,country,age）,(active,country,age),(sex,country,age) 和(country,age).這些索引對某個具體的查詢來說可能都是更最佳化的，但是考慮到索引的維護和額外的空間佔用的代價，這個可選方案就不是一個好的策略了。

　　這這個案例中，最佳化器的特性是影響索引策略的一個很重要的因素。如果未來辦嗎的MySQL能夠實現鬆鬆索引掃描，就能在一個索引上使用多個範圍條件，那也就不需要為上面考慮這類型使用in（）列表了。

　　最佳化排序

　　在這個學習案例中，最後要介紹的是排序。使用檔案排序對小資料集是很快的，但如果一個查詢的結果又上百萬行的話會怎樣？例如where子句中只有sex列，如何排序？

　　對於那些選擇性非常低的列，可以增加一些特殊的索引來做排序。例如可以建立（sex,rating）索引用於如下的查詢：

　　SELECCT <cols> FROM  profiles WHERE sex=‘M‘ ORDER BY rating limit 10;

　　這個查詢同時使用了order by 和limit ，如果沒有索引的話，會很慢。

　　即使有索引，如果yoghurt介面上需要翻頁，並且翻頁到比較靠後時，查詢也可能非常慢。

　　下面這個查詢就可以通過ORDER BY 和LIMIT位移量的組合翻頁到很後面的時候。

　　SELECCT <cols> FROM  profiles WHERE sex=‘M‘ ORDER BY rating limit 100000,10;

　　無論如何建立索引，這種查詢都是個嚴重的問題。因為隨著位移量的增加，MySQL需要花費大量的 時間來掃描需要丟棄的資料。反範式化、預先計算和緩衝可能是解決這類查詢的僅有策略。一個更好的辦法是限制使用者能夠翻頁的數量，實際上這對使用者的體驗不大，因為使用者很少真正在乎搜尋過的低10000頁。

　　最佳化這類索引的另一個比較好的策略是使用延遲關聯，通過使用腹杆索引查詢返回需要的主鍵，在根據這些主鍵關聯原表格所需要的行。這可以減少MySQL掃描那些需要丟棄的行數。下面這個查詢顯示了如何高效的使用（sex，rating）索引進行排序和分頁

　　SELECT <cols> FROM profiles INNER JOIN (SELECT <primary key cols> FROM profiles WHERE x.sex = ‘M‘ ORDER BY rating 10000,10) AS X using (<primary key cols>);

　　

　　

Mysql 索引案例學習