索引的利弊與如何判定，是否需要索引
相信讀者都知道索引能夠極大地提高數據檢索的效率，讓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這種簡單的字符集，索引文件會小很多，速度自然就會快很多。這些細節問題需要讀者自己多注意，我就不多說了。
- 對於具有2個用and連接條件的語句，且2個列之間的關聯度較低的情況下，multi col index有一定優勢。
- 對於具有2個用and連接條件的語句，且2個列之間的關聯度較高的情況下，multi col index有很大優勢。
- 對於具有2個用or連接條件的語句，isolate col index有一定優勢，因為這種情況下multi col index 將會導致全表掃描，而前者可以用到index merge的優化。
閱讀更多 先知小店 的文章
