知識星球來自：程式設計無界（微訊號：qianshic），作者：假不理
本文旨在用最通俗的語言講述最枯燥的基本知識
這個話題比較有意思。
昨天中午吃完飯間突然有個同事蹦出了一句：“like有索引嗎？”，我順口就說沒有，另一個同事反駁說有啊，還有些同事說看情況的有，這下有點懵逼了，都不知道那種說法是正確的，於是決定花了個半小時來研究驗證這個問題，終於得到答案。
怎麼驗證的呢？
坊間有傳言：MySQL效能最佳化有個神器，叫做explain，它可以對select陳述句進行分析並且輸出詳細的select執行過程的詳細資訊，讓開發者從這些資訊中獲得最佳化的思路。
下麵來講講這個MySQL提供的explain命令：
語法：explain SQL陳述句
例如：1explain select * from user where id=1
執行完畢之後，它的輸出有以下欄位：
id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
Extra
要想知道explain命名怎麼使用，就必須把這些欄位搞清楚
1. id
SELECT查詢的識別符號， 每個SELECT陳述句都會自動分配一個唯一的識別符號
2. select_type
每個select查詢字句的型別，具體型別以及對應作用如下表：
| 型別名 | 解釋 |
|---|---|
| SIMPLE | 簡單SELECT,不使用UNION或子查詢等 |
| PRIMARY | 查詢中若包含任何複雜的子部分,最外層的select被標記為PRIMARY |
| UNION | UNION中的第二個或後面的SELECT陳述句 |
| DEPENDENT UNION | UNION中的第二個或後面的SELECT陳述句，取決於外面的查詢 |
| UNION RESULT | UNION的結果 |
| SUBQUERY | 子查詢中的第一個SELECT |
| DEPENDENT SUBQUERY | 子查詢中的第一個SELECT，取決於外面的查詢 |
| DERIVED | 派生表的SELECT, FROM子句的子查詢 |
| UNCACHEABLE SUBQUERY | 一個子查詢的結果不能被快取，必須重新評估外連結的第一行 |
3. table
顯示這一行的資料是查哪張表的，不過有時短路顯示的不是真實的表名。
4. partitions
匹配的分割槽(這個目前用處不大)
5. type
訪問型別，表示MySQL在表中找到所需行的方式，對應的值和解釋如下：
| 型別名 | 優級別 | 解釋 |
|---|---|---|
| system | 1 | 表僅有一行 |
| const | 2 | 表最多有一個匹配行,在查詢開始時即被讀取 |
| eq_ref | 3 | 使用primary key或者unique key作為多表連線的條件,僅從該表中讀取一行 |
| ref | 4 | 作為查詢條件的索引在每個表匹配索引值的行從表中讀取出來 |
| fulltext | 5 | 全文索引檢索 |
| ref_or_null | 6 | 和ref一致，但增加了NULL值查詢支援 |
| index_merge | 7 | 表示使用了索引合併最佳化方法 |
| unique_subquery | 8 | 使用了替換了in子查詢 |
| index_subquery | 9 | 使用了替換了in子查詢，但只適用於子查詢中的非唯一索引 |
| range | 10 | 只檢索給定範圍的行,使用一個索引來選擇行 |
| index | 11 | 全表掃描，但掃描表的方式是按索引的次序進行 |
| ALL | 12 | 全表掃描的方式找到匹配的行 |
type作為訪問型別，其值代表著當前查詢所用的型別，是體現效能的一個重要指標，從表中可以看到，從上到下，掃描表的方式越來越寬，效能也就越來越差，因此，對於一個查詢，最好能保持在range級別以上。
6. possible_keys
主動指出查詢能用哪個索引在表中找到記錄
也就是會列出在查詢中的欄位中有索引的欄位，但不一定被查詢所用。
7. key
顯示再查詢中實際使用的索引/鍵，如果沒有索引，則顯示NULL。
但如果想強制查詢中使用或忽視possible_keys列中的索引，則可以在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
8. key_len
表示索引中使用的位元組數。
9. ref
表示哪些列或常量被用於查詢索引列上的值。
10. rows
顯示當前查詢估算到的查詢到匹配記錄所需的記錄行數。
11. Extra
顯示當前查詢所用的解決方式，它有以下幾種情況：
| 型別名 | 解釋 |
|---|---|
| Using where | 列資料是從僅僅使用了索引中的資訊而沒有讀取實際的行動的表傳回的， |
| Using temporary | 表示MySQL需要使用臨時表來儲存結果集，常見於排序和分組查詢 |
| Using filesort | MySQL中無法利用索引完成的排序操作稱為“檔案排序” |
| Using join buffer | 改值強調了在獲取連線條件時沒有使用索引，並且需要連線緩衝區來儲存中間結果。如果出現了這個值，那應該註意，根據查詢的具體情況可能需要新增索引來改進能。 |
| Impossible where | 這個值強調了where陳述句會導致沒有符合條件的行。 |
| Select tables optimized away | 這個值意味著僅透過使用索引，最佳化器可能僅從聚合函式結果中傳回一行 |
講完了語法，我們來實際操作一波，首先建立個表：
1-- 建立表
2CREATE TABLE test(
3id INT(11) NOT NULL AUTO_INCREMENT,
4uname VARCHAR(255),
5PRIMARY KEY(id)
6);
然後給uname欄位加上索引：
1-- 新增索引
2ALTER TABLE test ADD INDEX uname_index (uname);
檢視一下索引是否新增成功：
1-- 檢視是否有索引
2SHOW INDEX FROM test;
輸出結果為：
可以看出索引已經建立成功，接下來新增一些資料：
1-- 新增一些資料
2INSERT INTO test VALUES(1,'jay');
3INSERT INTO test VALUES(2,'ja');
4INSERT INTO test VALUES(3,'bril');
5INSERT INTO test VALUES(4,'aybar');
一切準備就緒，下麵用explain這個命令來探究一些like陳述句是否有索引,
like有四種情況，分別為沒有%、 %% 、左%、右%、
1. like 欄位名
1EXPLAIN SELECT * FROM test WHERE uname LIKE 'j';
輸出為：
可以看出：
type的值為：range，key的值為uname_index，也就是說這種情況下，使用了索引。
2. like %欄位名%
1EXPLAIN SELECT * FROM test WHERE uname LIKE '%j%';
輸出為：
可以看出：
type的值為ALL也就是全表掃描，而且key的值為NULL，也就是說沒用到任何索引。
3. like %欄位名
1EXPLAIN SELECT * FROM test WHERE uname LIKE '%j';
輸出為：
可以看出：
type的值為ALL，key的值為NULL，同樣沒用到索引。
4. like 欄位名%
1EXPLAIN SELECT * FROM test WHERE uname LIKE 'j%';
輸出為：
可以看出：
type的值為：range，key的值為uname_index，也就是說這種情況下，使用了索引。
總結
由上面的試驗可以總結出like是否使用索引的規律：
like陳述句要使索引生效，like後不能以%開始，也就是說 :
（like %欄位名%） 、（like %欄位名）這類陳述句會使索引失效，（like 欄位名）、（like 欄位名%）這類陳述句索引是可以正常使用。
其它
為了查證like索引的問題，研究了MySQL神奇explain，但explain不僅僅只能檢查索引使用情況，還可以提供很多其它的效能最佳化方面的幫助，至於具體的使用，其實跟上面講的一樣，把explain結果列出來，然後順藤摸瓜查閱相關的欄位就可以得到相應的內容。
●編號428，輸入編號直達本文
●輸入m獲取文章目錄
Web開發
更多推薦《18個技術類微信公眾號》
涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。