前言：索引優化的目的主要是讓索引不失效，本篇通過相關案例對索引優化進行講解。
0.準備
建立經典的tb_emp表。
DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` int(11) NOT NULL,
gender varchar(10) NOT NULL,
email varchar(20),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Tom', '22','male','[email protected]');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Mary', '21','female','[email protected]');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Jack', '27','male','[email protected]');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Rose', '23','female','[email protected]'); 注：建立了tb_emp表，并插入了4條資料。
1.最佳左字首法則
#1.定義：在建立了多列索引的情況下，查詢從索引的最左前列開始且不能跳過索引中的列。
最佳左字首法則就是說如果建立了多個索引，在使用索引時要按照建立索引的順序來使用，不能缺少或跳過，當然如果隻使用最左邊的索引列，也就是第一個索引是可以的，通俗了解：“帶頭大哥不能死，中間兄弟不能斷”。要點：“頭不能掉”。下面将用案例進行說明。
#2.建立組合索引，并執行explain。
Case 1：
分析：
①索引的建立順序為name，age，gender；
②直接使用name（帶頭大哥）作為條件，可以看到type=ref，key_len=82，ref=const，效果還不錯。
Case 2：
沒使用帶頭大哥（name），直接用兄弟，type=ALL，為全表掃描。
Case 3：
①對比上面兩句sql語句可發現：我們使用：火車頭（name）和中間車廂（age）、火車頭（name）和車尾（gender）。
②雖然type=ref，但是觀察key_len和ref兩項，并對比Case1中的結果，可得出在使用火車頭（name）和車尾（gender）時，隻使用了部分索引也就是火車頭（name）的索引。
③通俗了解：火車頭單獨跑沒問題，火車頭與直接相連的車廂一起跑也沒問題，但是火車頭與車尾，如果中間沒有車廂，隻能火車頭自己跑。
Case 4：
火車頭加車廂加車尾，三者串聯，就變成了奔跑的小火車。type=ref，key_len=128，ref=const，const，const。
最佳左字首法則總結：帶頭大哥不能死，中間兄弟不能斷；帶頭大哥可跑路，老二也可跟着跑，其餘兄弟隻能死。
2.不要在索引列上做任何操作
在索引列上做任何操作（計算、函數、（自動or手動）類型轉換），會導緻索引失效進而轉向全表掃描。
這裡使用了函數計算，type=ALL，導緻索引失效。
将name=‘Tom’的值修改為‘123’，使用sql後，發生了類型轉換，type=ALL，導緻全表掃描。
結論：在索引列上做任何操作，都會導緻索引失效轉向全表掃描。
3.範圍右邊全失效
存儲引擎不能使用索引中範圍右邊的列，也就是說範圍右邊的索引列會失效。
對以上4個case進行分析：
①條件單獨使用name時，type=ref，key_len=82，ref=const。
②條件加上age時（使用常量等值），type=ref，key_len=86，ref=const，const。
③當全值比對時，type=ref，key_len=128，ref=const，const，const。說明索引全部用上，從key_len與ref可以看出。
④當使用範圍時（age>27），type=range，key_len=86，ref=Null，與Case 1、Case2和Case3可知，使用了部分索引，但gender索引沒用上（與Case 3對比）。
結論：範圍右邊的索引列失效。
4.盡量使用覆寫索引
盡量使用覆寫索引（查詢列和索引列盡量一緻，通俗說就是對A、B列建立了索引，然後查詢中也使用A、B列），減少select *的使用。
對比Case1和Case2，Case1使用select *，Case2使用覆寫索引（查詢列與條件列對應），可看到Extra從Null變成了Using index，提高檢索效率。
5.使用不等于（！=或<>）會使索引失效
結論：使用！=會使type=ALL，key=Null，導緻全表掃描，并且索引失效。
6.is null 或 is not null也無法使用索引
在使用is null的時候，索引完全失效，使用is not null的時候，type=ALL全表掃描，key=Null索引失效。
這裡的例子可能有點特殊，具體情況肯能和case上的有所不同，但是還是要注意is null和is not null的使用。
7.like通配符以%開頭會使索引失效
①like的%位置不同，所産生的效果不一樣，當%出現在左邊的時候，type=ALL，key=Null（全表掃描，索引失效），當%出現在右邊的時候，type=range，索引未失效。
②like查詢為範圍查詢，%出現在左邊，則索引失效。%出現在右邊索引未失效。口訣：like百分加右邊。
但是在實際生産環境中，%僅出現在右邊可能不能夠解決我們的問題，是以解決%出現在左邊索引失效的方法：使用覆寫索引。
分析：對比Case1可知，通過覆寫索引type=index，并且使用了Using index，從全表掃描變成了全索引掃描，還是不錯的。
Case 5：
分析：這裡出現type=index，因為主鍵自動建立唯一索引。
Case 6：
分析：上面四組explain執行的結果都相同，表明都使用了索引，從這裡可以深刻的體會到覆寫索引：完全吻合或者沾邊（age），都可以使type=index。
Case 7：
分析：由于隻在（name，age，gender）上建立索引，當包含email時，導緻結果集偏大（email未建索引）【鍋大，鍋蓋小，不能比對】，是以type=ALL。
8.字元串不加單引号導緻索引失效
分析：上述兩條sql語句都能查詢出相同的資料。
通過explain執行結果可以看出，字元串（name）不加單引号在查詢的時候，導緻索引失效（type=ref變成了type=ALL，并且key=Null），并全表掃描。
結論：varchar類型的字段，在查詢的時候不加單引号導緻索引失效，轉向全表掃描。
9.少用or，用or連接配接會使索引失效
結論：通過上述explain的執行結果可看出，在使用or連接配接的時候type=ALL，key=Null，索引失效，并全表掃描。
總結
①全值比對。
②最佳左字首法則：帶頭大哥不能死，中間兄弟不能斷；帶頭大哥可跑路，老二也可跟着跑，其餘兄弟隻能死。
③索引列上不計算。
④覆寫索引記住用。
⑤不等于、is null、is not null導緻索引失效。
⑥like百分加右邊，加左邊導緻索引失效，解決方法：使用覆寫索引。
⑦字元串不加單引号導緻索引失效。
⑧少用or，用or導緻索引失效。
by Shawn Chen，2018.6.25日，上午。
相關内容
MySQL進階知識系列目錄