單鍵索引還是複合式索引

轉自 http://talentluke.iteye.com/blog/1843868

摘自http://book.51cto.com/art/200906/132406.htm

 

8.4.5  索引的利弊與如何判定，是否需要索引

 

相信讀者都知道索引能夠極大地提高資料檢索的效率，讓Query 執行得更快，但是可能並不是每一位朋友都清楚索引在極大提高檢索效率的同時，也給資料庫帶來了一些負面的影響。下面就分別對 MySQL 中索引的利與弊做一個簡單的分析。

 

索引的好處

 

索引帶來的益處可能很多讀者會認為只是"能夠提高資料檢索的效率，降低資料庫的IO成本"。

 

確實，在資料庫中表的某個欄位建立索引，所帶來的最大益處就是將該欄位作為檢索條件時可以極大地提高檢索效率，加快檢索時間，降低檢索過程中須要讀 取的資料量。但是索引帶來的收益只是提高表資料的檢索效率嗎。當然不是，索引還有一個非常重要的用途，那就是降低資料的排序成本。

我們知 道，每個索引中的資料都是按照索引鍵索引值進行排序後存放的，所以，當Query 語句中包含排序分組操作時，如果排序欄位和索引鍵欄位剛好一致，MySQL Query Optimizer 就會告訴 mysqld 在取得資料後不用排序了，因為根據索引取得的資料已經滿足客戶的排序要求。

 

那如果是分組操作呢。分組操作沒辦法直接利用索引完成。但是分組操作是須要先進行排序然後分組的，所以當Query 語句中包含分組操作，而且分組欄位也剛好和索引鍵欄位一致，那麼mysqld 同樣可以利用索引已經排好序的這個特性，省略掉分組中的排序操作。

 

排序分組操作主要消耗的是記憶體和 CPU 資源，如果能夠在進行排序分組操作中利用好索引，將會極大地降低CPU資源的消耗。

 

索引的弊端

 

索引的益處已經清楚了，但是我們不能只看到這些益處，並認為索引是解決 Query 最佳化的聖經，只要發現 Query 運行不夠快就將 WHERE 子句中的條件全部放在索引中。

 

確實，索引能夠極大地提高資料檢索效率，也能夠改善排序分組操作的效能，但有不能忽略的一個問題就是索引是完全獨立於基礎資料之外的一部分資料。假 設在Table ta 中的Column ca 建立了索引 idx_ta_ca，那麼任何更新 Column ca 的操作，MySQL在更新表中 Column ca的同時，都須要更新Column ca 的索引資料，調整因為更新帶來索引值變化的索引資訊。而如果沒有對 Column ca 進行索引，MySQL要做的僅僅是更新表中 Column ca 的資訊。這樣，最明顯的資源消耗就是增加了更新所帶來的 IO 量和調整索引所致的計算量。此外，Column ca 的索引idx_ta_ca須要佔用儲存空間，而且隨著 Table ta 資料量的增加，idx_ta_ca 所佔用的空間也會不斷增加，所以索引還會帶來儲存空間資源消耗的增加。

 

如何判定是否須要建立索引

 

在瞭解了索引的利與弊之後，那我們到底該如何來判斷某個索引是否應該建立呢。

 

實際上，並沒有一個非常明確的定律可以清晰地定義什麼欄位應該建立索引，什麼欄位不該建立索引。因為應用情境實在是太複雜，存在太多的差異。當然，還是仍然能夠找到幾點基本的判定策略來協助分析的。

 

1. 較頻繁的作為查詢條件的欄位應該建立索引

 

提高資料查詢檢索的效率最有效辦法就是減少須要訪問的資料量，從上面索引的益處中我們知道，索引正是減少通過索引鍵欄位作為查詢條件的 Query 的IO量之最有效手段。所以一般來說應該為較為頻繁的查詢條件欄位建立索引。

 

2. 唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件

 

唯一性太差的欄位主要是指哪些呢。如狀態欄位、類型欄位等這些欄位中存放的資料可能總共就是那麼幾個或幾十個值重複使用，每個值都會存在於成千上萬 或更多的記錄中。對於這類欄位，完全沒有必要建立單獨的索引。因為即使建立了索引，MySQL Query Optimizer 大多數時候也不會去選擇使用，如果什麼時候 MySQL Query Optimizer選擇了這種索引，那麼非常遺憾地告訴你，這可能會帶來極大的效能問題。由於索引欄位中每個值都含有大量的記錄，那麼儲存引擎在根據索引 訪問資料的時候會帶來大量的隨機IO，甚至有些時候還會出現大量的重複IO。

 

這主要是由於資料基於索引掃描的特點引起的。當我們通過索引訪問表中資料時，MySQL 會按照索引鍵的索引值順序來依序訪問。一般來說，每個資料頁中大都會存放多條記錄，但是這些記錄可能大多數都不會和你所使用的索引鍵的索引值順序一致。

 

假如有以下情境，我們通過索引尋找索引值為A和B的某些資料。在通過A索引值找到第一條滿足要求的記錄後，會讀取這條記錄所在的 X 資料頁，然後繼續往下尋找索引，發現 A 索引值所對應的另外一條記錄也滿足要求，但是這條記錄不在 X 資料頁上，而在Y資料頁上，這時候儲存引擎就會丟棄X資料頁，而讀取Y資料頁。如此繼續一直到尋找完A索引值所對應的所有記錄。然後輪到B索引值了，這時發現 正在尋找的記錄又在X資料頁上，可之前讀取的 X 資料頁已經被丟棄了，只能再次讀取 X 資料頁。這時候，實際上已經重複讀取 X 資料頁兩次了。在繼續往後的尋找中，可能還會出現一次又一次的重複讀取，這無疑給儲存引擎極大地增加了IO訪問量。

 

不僅如此，如果一個索引值對應了太多的資料記錄，也就是說通過該索引值會返回占整個表比例很大的記錄時，由於根據索引掃描產生的都是隨機 IO，其效率比進行全表掃描的順序IO效率低很多，即使不會出現重複 IO 的讀取，同樣會造成整體 IO 效能的下降。

 

很多比較有經驗的 Query 調優專家經常說，當一條Query返回的資料超過了全表的 15%時，就不應該再使用索引掃描來完成這個 Query 了。對於"15%"這個數字我們並不能判定是否很準確，但是至少側面證明了唯一性太差的欄位並不適合建立索引。

 

3. 更新非常頻繁的欄位不適合建立索引

 

上面在索引的弊端中已經分析過了，索引中的欄位被更新的時候，不僅要更新表中的資料，還要更新索引資料，以確保索引資訊是準確的。這個問題致使IO 訪問量較大增加，不僅僅影響了更新 Query 的回應時間，還影響了整個儲存系統的資源消耗，加大了整個儲存系統的負載。

 

當然，並不是存在更新的欄位就適合建立索引，從判定策略的用語上也可以看出，是"非常頻繁"的欄位。到底什麼樣的更新頻率應該算是"非常頻繁"呢。 每秒。每分鐘。還是每小時呢。說實話，還真難定義。很多時候是通過比較同一時間段內被更新的次數和利用該欄位作為條件的查詢次數來判斷的，如果通過該欄位 的查詢並不是很多，可能幾個小時或是更長才會執行一次，更新反而比查詢更頻繁，那這樣的欄位肯定不適合建立索引。反之，如果我們通過該欄位的查詢比較頻 繁，但更新並不是特別多，比如查詢幾十次或更多才可能會產生一次更新，那我個人覺得更新所帶來的附加成本也是可以接受的。

 

4. 不會出現在 WHERE 子句中的欄位不該建立索引

不會還有人會問為什麼吧。自己也覺得這是廢話了，哈哈。

8.4.6  單鍵索引還是複合式索引

 

在大概瞭解了MySQL 各種類型的索引，以及索引本身的利弊與判斷一個欄位是否須要建立索引之後，就要著手建立索引來最佳化Query 了。在很多時候，WHERE 子句中的過濾條件並不只是針對於單一的某個欄位，經常會有多個欄位一起作為查詢過濾條件存在於 WHERE 子句中。在這種時候，就必須要判斷是該僅僅為過濾性最好的欄位建立索引，還是該在所有欄位（過濾條件中的）上建立一個複合式索引。

 

對於這種問題，很難有一個絕對的定論，須要從多方面來分析考慮，平衡兩種方案各自的優劣，然後選擇一種最佳的方案。因為從上一節中已瞭解到索引在提 高某些查詢的效能同時，也會讓某些更新的效率下降。而複合式索引中因為有多個欄位存在，理論上被更新的可能性肯定比單鍵索引要大很多，這樣帶來的附加成本也 就比單鍵索引要高。但是，當WHERE 子句中的查詢條件含有多個欄位時，通過這多個欄位共同組成的複合式索引的查詢效率肯定比只用過濾條件中的某一個欄位建立的索引要高。因為通過單鍵索引過濾的 資料並不完整，和複合式索引相比，儲存引擎須要訪問更多的記錄數，自然就會訪問更多的資料量，也就是說需要更高的 IO 成本。

 

可能有朋友會說，那可以建立多個單鍵索引啊。確實可以將 WHERE 子句中的每一個欄位都建立一個單鍵索引。但是這樣真的有效嗎。在這樣的情況下，MySQL Query Optimizer 大多數時候都只會選擇其中的一個索引，然後放棄其他的索引。即使他選擇了同時利用兩個或更多的索引通過 INDEX_MERGE 來最佳化查詢，所收到的效果可能並不會比選擇其中某一個單鍵索引更高效。因為如果選擇通過 INDEX_MERGE 來最佳化查詢，就須要訪問多個索引，同時還要將幾個索引進行 merge 操作，這帶來的成本可能反而會比選擇其中一個最有效索引更高。

 

在一般的應用情境中，只要不是其中某個過濾欄位在大多數情境下能過濾90%以上的資料，而其他的過濾欄位會頻繁的更新，一般更傾向於建立複合式索引， 尤其是在並發量較高的情境下。因為當並發量較高的時候，即使只為每個Query節省了很少的 IO 消耗，但因為執行量非常大，所節省的資源總量仍然是非常可觀的。

 

當然，建立複合式索引並不是說就須要將查詢條件中的所有欄位都放在一個索引中，還應該盡量讓一個索引被多個 Query 語句利用，盡量減少同一個表上的索引數量，減少因為資料更新帶來的索引更新成本，同時還可以減少因為索引所消耗的儲存空間。

此外，MySQL 還提供了另外一個最佳化索引的功能，那就是首碼索引。在 MySQL 中，可以僅僅使用某個欄位的前面部分內容做為索引鍵索引該欄位，以達到減小索引佔用的儲存空間和提高索引訪問效率的目的。當然，首碼索引的功能僅僅適用於 欄位首碼隨機重複性很小的欄位。如果須要索引的欄位首碼內容有較多的重複，索引的過濾性自然也會隨之降低，通過索引所訪問的資料量就會增加，這時候首碼索 引雖然能夠減少儲存空間消耗，但是可能會造成 Query 訪問效率的極大降低，得不償失。

 

 

摘自http://www.canphp.com/article/show-130.html

 

複合索引最佳化

兩個或更多個列上的索引被稱作複合索引。
利用索引中的附加列，您可以縮小搜尋的範圍，但使用一個具有兩列的索引不同於使用兩個單獨的索引。複合索引的結構與電話簿類似，人名由姓和名構成，電話簿 首先按姓氏對進行排序，然後按名字對有相同姓氏的人進行排序。如果您知道姓，電話簿將非常有用；如果您知道姓和名，電話簿則更為有用，但如果您只知道名不 姓，電話簿將沒有用處。
所以說建立複合索引時，應該仔細考慮列的順序。對索引中的所有列執行搜尋或僅對前幾列執行搜尋時，複合索引非常有用；僅對後面的任意列執行搜尋時，複合索引則沒有用處。
如：建立 姓名、年齡、性別的複合索引。

複合索引的建立原則：

如果您很可能僅對一個列多次執行搜尋，則該列應該是複合索引中的第一列。如果您很可能對一個兩列索引中的兩個列執行單獨的搜尋，則應該建立另一個僅包含第二列的索引。
如上圖所示，如果查詢中需要對年齡和性別做查詢，則應當再建立一個包含年齡和性別的複合索引。
包含多個列的主鍵始終會自動以複合索引的形式建立索引，其列的順序是它們在表定義中出現的順序，而不是在主鍵定義中指定的順序。在考慮將來通過主鍵執行的搜尋，確定哪一列應該排在最前面。
請注意，建立複合索引應當包含少數幾個列，並且這些列經常在select查詢裡使用。在複合索引裡包含太多的列不僅不會給帶來太多好處。而且由於使用相當多的記憶體來儲存複合索引的列的值，其後果是記憶體溢出和效能降低。

         
複合索引對排序的最佳化：

複合索引只對和索引中排序相同或相反的order by 語句最佳化。
在建立複合索引時，每一列都定義了升序或者是降序。如定義一個複合索引： Sql代碼   CREATE INDEX idx_example     ON table1 (col1 ASC, col2 DESC, col3 ASC)     
其中 有三列分別是：col1 升序，col2 降序， col3 升序。現在如果我們執行兩個查詢
1： Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC
  和索引順序相同
2： Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC
 和索引順序相反
查詢1，2 都可以別複合索引最佳化。
如果查詢為：
Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC
  排序結果和索引完全不同時，此時的查詢不會被複合索引最佳化。


查詢最佳化工具在在where查詢中的作用：

如果一個多列索引存在於 列 Col1 和 Col2 上，則以下語句：Select   * from table where   col1=val1 AND col2=val2 查詢最佳化工具會試圖通過決定哪個索引將找到更少的行。之後用得到的索引去取值。
1． 如果存在一個多列索引，任何最左面的索引首碼能被最佳化器使用。所以聯合索引的順序不同，影響索引的選擇，盡量將值少的放在前面。
如：一個多列索引為 (col1 ，col2， col3)
    那麼在索引在列 (col1) 、(col1 col2) 、(col1 col2 col3) 的搜尋會有作用。

  Sql代碼  SELECT * FROM tb WHERE  col1 = val1    SELECT * FROM tb WHERE  col1 = val1 and col2 = val2    SELECT * FROM tb WHERE  col1 = val1 and col2 = val2  AND col3 = val3     

2． 如果列不構成索引的最左面首碼，則建立的索引將不起作用。
如：
Sql代碼  SELECT * FROM  tb WHERE  col3 = val3    SELECT * FROM  tb  WHERE  col2 = val2    SELECT * FROM  tb  WHERE  col2 = val2  and  col3=val3     
3． 如果一個 Like 語句的查詢條件不以萬用字元起始則使用索引。
如：%車 或 %車%   不使用索引。
    車%              使用索引。
索引的缺點：
1.       佔用磁碟空間。
2.       增加了插入和刪除的操作時間。一個表擁有的索引越多，插入和刪除的速度越慢。如 要求快速錄入的系統不宜建過多索引。

下面是一些常見的索引限制問題

1、使用不等於操作符(<>, !=)
下面這種情況，即使在列dept_id有一個索引，查詢語句仍然執行一次全表掃描
select * from dept where staff_num <> 1000;
但是開發中的確需要這樣的查詢，難道沒有解決問題的辦法了嗎。
有。
通過把用 or 文法替代不等號進行查詢，就可以使用索引，以避免全表掃描：上面的語句改成下面這樣的，就可以使用索引了。
Sql代碼  select * from dept shere staff_num < 1000 or dept_id > 1000;     

2、使用 is null 或 is not null
使用 is null 或is nuo null也會限制索引的使用，因為資料庫並沒有定義null值。如果被索引的列中有很多null，就不會使用這個索引（除非索引是一個位元影像索引，關於位元影像 索引，會在以後的blog文章裡做詳細解釋）。在sql語句中使用null會造成很多麻煩。
解決這個問題的辦法就是：建表時把需要索引的列定義為非空(not null)

3、使用函數
如果沒有使用基於函數的索引，那麼where子句中對存在索引的列使用函數時，會使最佳化器忽略掉這些索引。下面的查詢就不會使用索引：
Sql代碼  select * from staff where trunc(birthdate) = '01-MAY-82';     
但是把函數應用在條件上，索引是可以生效的，把上面的語句改成下面的語句，就可以通過索引進行尋找。
Sql代碼  select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);     

4、比較不匹配的資料類型
比較不匹配的資料類型也是難於發現的效能問題之一。
下面的例子中，dept_id是一個varchar2型的欄位，在這個欄位上有索引，但是下面的語句會執行全表掃描。
Sql代碼  select * from dept where dept_id = 900198;     
這是因為oracle會自動把where子句轉換成to_number(dept_id)=900198，就是3所說的情況，這樣就限制了索引的使用。
把SQL語句改為如下形式就可以使用索引
Sql代碼  select * from dept where dept_id = '900198';     

恩，這裡還有要注意的：

來自老王的部落格（http://hi.baidu.com/thinkinginlamp/blog/item/9940728be3986015c8fc7a85.html）

比方說有一個文章表，我們要實現某個類別下按時間倒序列表顯示功能：

SELECT * FROM articles WHERE category_id = ... ORDER BY created DESC LIMIT ...

這樣的查詢很常見，基本上不管什麼應用裡都能找出一大把類似的SQL來，學院派的讀者看到上面的SQL，可能會說SELECT *不好，應該僅僅查詢需要的欄位，那我們就索性徹底點，把SQL改成如下的形式：

SELECT id FROM articles WHERE category_id = ... ORDER BY created DESC LIMIT ...
 

我們假設這裡的id是主鍵，至於文章的具體內容，可以都儲存到memcached之類的索引值類型的緩衝裡，如此一來，學院派的讀者們應該挑不出什麼毛病來了，下面我們就按這條SQL來考慮如何建立索引：

不考慮資料分布之類的特殊情況，任何一個合格的WEB開發人員都知道類似這樣的SQL，應該建立一個”category_id, created“複合索引，但這是最佳答案不。不見得，現在是回頭看看標題的時候了：MySQL裡建立索引應該考慮資料庫引擎的類型。

如果我們的資料庫引擎是InnoDB，那麼建立”category_id, created“複合索引是最佳答案。讓我們看看InnoDB的索引結構，在InnoDB裡，索引結構有一個特殊的地方：非主鍵索引在其BTree的葉節 點上會額外儲存對應主鍵的值，這樣做一個最直接的好處就是Covering Index，不用再到資料檔案裡去取id的值，可以直接在索引裡得到它。

如果我們的資料庫引擎是MyISAM，那麼建立"category_id, created"複合索引就不是最佳答案。因為MyISAM的索引結構裡，非主鍵索引並沒有額外儲存對應主鍵的值，此時如果想利用上Covering Index，應該建立"category_id, created, id"複合索引。

嘮完了，應該明白我的意思了吧。希望以後大家在考慮索引的時候能思考的更全面一點，實際應用中還有很多類似的問題，比如說多數人在建立索引的時候不從 Cardinality（SHOW INDEX FROM ...能看到此參數）的角度看是否合適的問題，Cardinality表示唯一值的個數，一般來說，如果唯一值個數在總行數中所佔比例小於20%的話，則 可以認為Cardinality太小，此時索引除了拖慢insert/update/delete的速度之外，不會對select產生太大作用；還有一個 細節是建立索引的時候未考慮字元集的影響，比如說username欄位，如果僅僅允許英文，底線之類的符號，那麼就不要用gbk，utf-8之類的字元 集，而應該使用latin1或者ascii這種簡單的字元集，索引檔案會小很多，速度自然就會快很多。這些細節問題需要讀者自己多注意，我就不多說了。  摘自http://blog.chinaunix.net/uid-7692530-id-2567605.html 對於具有2個用and串連條件的語句，且2個列之間的關聯度較低的情況下，multi col index有一定優勢。 對於具有2個用and串連條件的語句，且2個列之間的關聯度較高的情況下，multi col index有很大優勢。 對於具有2個用or串連條件的語句，isolate col index有一定優勢，因為這種情況下multi col index 將會導致全表掃描，而前者可以用到index merge的最佳化。