MySQL 索引詳解
一、單列索引和組合索引（也叫復合索引）的選擇效率問題
先闡述下單列索引和組合索引的概念：
單列索引：即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。
組合索引：即一個索包含多個列。
如果我們的查詢where條件只有一個，我們完全可以用單列索引，這樣的查詢速度較快，索引也比較瘦身。如果我們的業務場景是需要經常查詢多個組合列，不要試圖分別基于單個列建立多個單列索引。這是因為當SQL語句所查詢的列，全部都出現在復合索引中時，此時由于只需要查詢索引塊即可獲得所有數據，當然比使用多個單列索引要快得多。下面以實際例子說明：
舉例：
| 以下是代碼片段： CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) ); |
下面是我們插入到這個people表的數據：
這個數據片段中有四個名字為“Mikes”的人(其中兩個姓Sullivans，兩個姓McConnells)，有兩個年齡為17歲的人，還有一個名字與眾不同的Joe Smith。
這個表的主要用途是根據指定的用戶姓、名以及年齡返回相應的peopleid。例如，我們可能需要查找姓名為Mike Sullivan、年齡17歲用戶的peopleid(SQL命令為SELECT peopleid FROM people WHERE firstname="Mike" AND lastname="Sullivan" AND age=17;)。由于我們不想讓MySQL每次執行查詢就去掃描整個表，這里需要考慮運用索引。
首先，我們可以考慮在單個列上創建索引，比如firstname、lastname或者age列。如果我們創建firstname列的索引(ALTER TABLE people ADD INDEX firstname (firstname);)，MySQL將通過這個索引迅速把搜索范圍限制到那些firstname="Mike"的記錄，然后再在這個“中間結果集”上 進行其他條件的搜索：它首先排除那些lastname不等于“Sullivan”的記錄，然后排除那些age不等于17的記錄。當記錄滿足所有搜索條件之 后，MySQL就返回最終的搜索結果。
由于建立了firstname列的索引，與執行表的完全掃描相比，MySQL的效率提高了很多，但我們要求MySQL掃描的記錄數量仍舊遠遠超過了實際所 需要的。雖然我們可以刪除firstname列上的索引，再創建lastname或者age列的索引，但總地看來，不論在哪個列上創建索引搜索效率仍舊相 似。
為了提高搜索效率，我們需要考慮運用多列索引。如果為firstname、lastname和age這三個列創建一個多列索引，MySQL只需一次檢索就能夠找出正確的結果!下面是創建這個多列索引的SQL命令：
| 以下是代碼片段： ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age); |
由于索引文件以B+樹格式保存，MySQL能夠立即轉到合適的firstname，然后再轉到合適的lastname，最后轉到合適的age。在沒有掃描數據文件任何一個記錄的情況下，MySQL就正確地找出了搜索的目標記錄!
那么，如果在firstname、lastname、age這三個列上分別創建單列索引，效果是否和創建一個firstname、lastname、age的多列索引一樣呢？答案是否定的，兩者完全不同。當我們執行查詢的時候，MySQL只能使用一個索引。如果你有三個單列的索引，MySQL會試圖選擇一個限制最嚴格的索引。但是，即使是限制最嚴格的單列索引，它的限制能力也肯定遠遠低于firstname、lastname、age這三個列上的多列索引。
二、謹防最左前綴索引失效問題
繼 續考慮前面的例子，現在我們有一個firstname、lastname、age列上的多列索引，我們稱這個索引為fname_lname_age。它相 當于我們創建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)這些列組合上的 索引。為什么沒有 (lastname,age)等這樣的組合索引呢？這是因為 mysql 組合索引"最左前綴"（Leftmost Prefixing）的結果。簡單的理解就是只從最左面的開始組合。并不是只要包含這三列的查詢都會用到該組合索引。
|
以下是代碼片段： The following queries can use the Leftmost Prefixing index: SELECT peopleid FROM people WHERE firstname="Mike" AND lastname="Sullivan" AND age="17"; SELECT peopleid FROM people WHERE firstname="Mike" AND lastname="Sullivan"; SELECT peopleid FROM people WHERE firstname="Mike"; The following queries cannot use the Leftmost Prefixing index at all: SELECT peopleid FROM people WHERE lastname="Sullivan"; SELECT peopleid FROM people WHERE age="17"; SELECT peopleid FROM people WHERE lastname="Sullivan" AND age="17"; |
這里我特地實踐了下：
執行結果如下：
可以看出這里最左前綴索引失效了。這里沒有用到索引直接全表掃描了。
我們再看下如果這樣呢？
執行結果如下：
可見還是用到了索引，不是應該失效嗎？是不是有點迷糊了？
特地請教了DBA玄慚大師，這里只是最左前綴索引失效，但是不代表整個索引失效，只是效率沒有那么高了。最左前綴索引的效率是比較高的。本來我誤以為只要第一個查詢字段不是組合索引的最左前綴索引字段整個索引會失效，其實不然。
這里強調下只是最有效率的最左前綴索引失效不是整個索引失效。
三、使用explain分析索引
在 不確定應該在哪些數據列上創建索引的時候，我們可以從EXPLAIN SELECT命令那里往往可以獲得一些幫助。這其實只是簡單地給一條普通的SELECT命令加一個EXPLAIN關鍵字作為前綴而已。有了這個關鍵 字，MySQL將不是去執行那條SELECT命令，而是去對它進行分析。MySQL將以表格的形式把查詢的執行過程和用到的索引(如果有的話)等信息列出 來。這里我基本闡述下每個信息字段含義，不展開闡述，我們只要注意幾個關鍵點（關鍵點以下用紅色加粗顯示）能大概看懂即可呵呵~~
1.id:SQL執行的順利的標識。
sql從里向外執行，通過以上觀察發現sql是按照id從大到小執行的。
2.select_type:SELECT類型
1)簡單SELECT(不使用UNION或子查詢等)
2) PRIMARY：最外層的select
3）DERIVED：派生表的SELECT(FROM子句的子查詢)
4）UNION：UNION中的第二個或后面的SELECT語句
5）UNION RESULT：UNION的結果。
6）DEPENDENT UNION：UNION中的第二個或后面的SELECT語句，取決于外面的查詢
7）SUBQUERY：子查詢中的第一個SELECT
8）DEPENDENT SUBQUERY：子查詢中的第一個SELECT，取決于外面的查詢
PS:這里我總結了下子查詢的in語句會用到DEPENDENT關鍵字，如果子查詢是union則是DEPENDENT UNION；如果子查詢是簡單的條件語句則是DEPENDENT SUBQUERY。這里不一定準確是我自己總結的哈~~如果不對望指正
3.table：表的名字。
有時不是真實的表名字,看到的是derivedx(x是個數字,我的理解是第幾步執行的結果)
4.type：連接操作的類型。
這列很重要,顯示了連接使用了哪種類別,有無使用索引。在各種類型的關聯關系當中，效率最高的是system，然后依次是const、eq_ref、ref、range、index和 All。一般來說，得保證查詢至少達到range級別，最好能達到ref，否則就可能會出現性能問題。
這個連接類型只有在查詢使用了不是惟一或主鍵的鍵或者是這些類型的部分（比如，利用最左邊前綴）時發生。對于之前的表的每一個行聯合，全部記錄都將從表中讀出。這個類型嚴重依賴于根據索引匹配的記錄多少（越少越好）
5.possible_keys：MySQL在搜索數據記錄時可以選用的各個索引的名字。
這里的索引名字是創建索引時指定的索引昵稱;如果索引沒有昵稱，則默認顯示的是索引中第一個列的名字(在上一節舉的例子中是“firstname”)。默認索引名字的含義往往不是很明顯。
6.key：它顯示了MySQL實際使用的索引的名字。
key數據列是MySQL實際選用的索引，如果它為空(或NULL)，則MySQL不使用索引。
7.key_len：索引中被使用部分的長度，以字節計。
key_len的值可以告訴你在聯合索引中mysql會真正使用了哪些索引。 在上例中，key_len是102，其中firstname占50字節，lastname占50字節，age占2字節（smallint存儲大小為2字節）。如果MySQL只使用索引中的firstname部分，則key_len將是50。 在不損失精確性的情況下 ，key_len數據列里的值越小越好(意思是更快)。
8.ref：顯示使用哪個列或常數與key一起從表中選擇行。
ref數據列給出了關聯關系中另一個數據表里的數據列的名字。
9.rows：MySQL所認為的它在找到正確的結果之前必須掃描的記錄數。
顯然，這里最理想的數字就是1。
10.extra：附加信息
Using index和Using where會遇到的比較多，可以重點記下，其他的我沒怎么遇到過了解即可，遇到具體問題可以查閱哈
先說到這，下面一篇給大家總結下如何選擇索引列以及使用索引的注意事項。
REF：
http://www.taobaotest.com/blogs/2395