使用索引的目的
使用索引的目的是提高數(shù)據(jù)庫查詢的效率。索引是怎么提高數(shù)據(jù)庫查詢的效率的呢？舉個通俗的例子，查字典。數(shù)據(jù)庫中的數(shù)據(jù)就好比新華字典中的詞條，索引就是新華字典的目錄。沒有建立索引的數(shù)據(jù)庫就好像被撕掉目錄的新華字典，只能從頭到尾一條一條地查詢，效率極其低下。
為了能更快地查字典，哦不，是查詢數(shù)據(jù)庫，我們就需要為數(shù)據(jù)庫建立索引。
索引的原理
索引的主要思想是將數(shù)據(jù)分段，從而減少查詢時的無效數(shù)據(jù)，提高查詢效率。比如有1000條數(shù)據(jù)，1到100分成第一段，101到200分成第二段，201到300分成第三段……這樣查第250條數(shù)據(jù)，只要找第三段就可以了，一下子去除了90%的無效數(shù)據(jù)。
當然，上面只是主要思想，MySQL使用了更加具體的數(shù)據(jù)結(jié)構(gòu)來實現(xiàn)索引。本文不對索引的數(shù)據(jù)結(jié)構(gòu)展開討論。
建立索引的幾大原則
如何建立合適的索引，從而最大程度地優(yōu)化查詢效率是一件需要精心設(shè)計的事情。本節(jié)只介紹幾個建立索引時須遵循的原則。
- 最左前綴匹配原則，非常重要的原則。mysql會一直向右匹配直到遇到范圍查詢
>、<、between、like就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整。關(guān)于最左前綴匹配原則，在最左前綴匹配原則一節(jié)有詳細說明。 - 盡量選擇區(qū)分度高的列作為索引,區(qū)分度公式為
count(distinct col)/count(*)，即一列中內(nèi)容不同的記錄數(shù)占總記錄數(shù)的比例。通過這個公式我們可以得到字段不重復的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0。所以唯一索引的效率是最高的。在不是唯一鍵的時候，就要具體情況具體分析了，這也是索引設(shè)計的關(guān)鍵點之一。 - 索引列不能參與計算，保持列“干凈”，比如
from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，索引的數(shù)據(jù)結(jié)構(gòu)中存的都是數(shù)據(jù)表中的字段值，但進行檢索時，需要把所有元素都應用函數(shù)才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’); - 盡量的擴展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可
- 使用短索引。如果對串列進行索引，應該指定一個前綴長度，只要有可能就應該這樣做 。
例如，如果有一個 CHAR(200) 列，如果在前 10 個或 20 個字符內(nèi)，多數(shù)值是惟一的，
那么就不要對整個列進行索引。對前 10 個或 20 個字符進行索引能夠節(jié)省大量索引空
間，也可能會使查詢更快。
最左前綴匹配原則
最左前綴使用場景是使用復合索引的時候。當使用復合索引時，如果想要索引有效，where之后的表達式就要滿足最左前綴匹配原則。
我總結(jié)了一下最左前綴匹配的特點，就是從表達式最左邊開始，到第一個范圍查詢結(jié)束，在這個閉區(qū)間內(nèi)的字段應該是索引字段的最左前綴。
最左前綴
在這里解釋一下最左前綴，因為沒有百度到感覺比較靠譜的解釋，所以我在這里談一下我的理解，僅供參考。
前綴應該不用解釋了，學過英語的都該懂點。前綴加個左就是左前綴了，表示從左邊開始查找的前綴。但是，這個左只是表示查找的順序是從左邊開始，不是從右邊開始，并沒有說從哪一位開始，可能是第一位，也可能是第三位。這時候再加一個 最 就表示了從最左邊開始。
比如復合索引是(a,b,d,c)，那么查詢時表達式的字段順序為(a)、(a,b)、(a,b,d)、(a,b,d,c)的這些都是它的最左前綴，而(b)、(a,d)、(a,b,c)、(a,b,c,d)這些就不是。
關(guān)于MySQL的查詢優(yōu)化器
最左前綴的概念我們已經(jīng)明白了，但是在MySQL中的情況又有點不同。比如我們發(fā)現(xiàn)，索引的順序是(a,b,d,c)，我們的查詢條件是這樣寫的：where b=10 and c=16 and a=26 and d=0，從嚴格意義上來講，這個順序是不符合最左前綴匹配原則的，但是MySQL的確使用索引完成了查詢。這是怎么回事呢？這是因為MySQL的查詢優(yōu)化器幫我們調(diào)整了查詢條件的順序。MySQL查詢優(yōu)化器會判斷糾正一條sql語句該以什么樣的順序執(zhí)行效率最高，最后才生成真正的執(zhí)行計劃。在有索引的情況下當然是利用索引查詢順序的效率最高咯，所以，MySQL查詢優(yōu)化器會最終以索引的順序進行查詢執(zhí)行。
最左前綴匹配原則的原理
上面說了這么多的最左前綴的概念，那么我們到底為什么一定要符合最左前綴匹配原則呢？
因為復合索引只有第一個字段是絕對有序的，從第二個開始的字段都只是相對前一個字段有序，在全局范圍內(nèi)是無序的。只有滿足最左前綴原則，才可以保證查詢內(nèi)容的有序，而有序又是索引使用的前提。
我們來看個例子，以該表的(name,cid)復合索引為例,它內(nèi)部結(jié)構(gòu)簡單說就是下面這樣排列的：
| name | cid |
|---|---|
| a | 6 |
| c | 4 |
| c | 5 |
| h | 1 |
| z | 9 |
MySQL創(chuàng)建復合索引的規(guī)則是首先會對復合索引的最左邊的，也就是第一個name字段的數(shù)據(jù)進行排序，在第一個字段的排序基礎(chǔ)上，然后再對后面第二個的cid字段進行排序。其實就相當于實現(xiàn)了類似 order by name cid這樣一種排序規(guī)則。
第一個name字段是絕對有序的，而第二字段就是無序的了。所以通常情況下，直接使用第二個cid字段進行條件判斷是用不到索引的。
那么什么時候才能用到呢?當然是cid字段的索引數(shù)據(jù)也是有序的情況下才能使用咯，什么時候才是有序的呢？觀察可知，當然是在name字段是等值匹配的情況下，cid才是有序的。發(fā)現(xiàn)沒有，觀察兩個name名字為 c 的cid字段是不是有序的呢。從上往下分別是4 5。這也就是MySQL索引規(guī)則中要求復合索引要想使用第二個索引，必須先使用第一個索引的原因（最左前綴匹配原則）。
補充使用索引時的具體情況
更多使用索引的詳細情況，可以參考最左前綴原理與相關(guān)優(yōu)化
本文的參考資料
http://blog.jobbole.com/86594/
