在我們使用MySQL的過程中，隨著數(shù)據(jù)量越來越大，查詢顯得有些吃力，這時(shí)候就要針對查詢就行優(yōu)化，針對查詢優(yōu)化，通過給字段添加索引可以提高數(shù)據(jù)的讀取速度，提高項(xiàng)目的并發(fā)能力和抗壓能力。在上一篇我們講了給數(shù)據(jù)庫中的表添加索引，來提高它的查詢速度，但是會有另外一種情況出現(xiàn)，那就是我們給表中字段加了索引，但是查詢的時(shí)候依舊很慢，沒有什么變化，這時(shí)就是因?yàn)榻⒌乃饕Я耍裉炀蛠碇v一講索引失效的情況！
對于查看索引是否失效，MySQL數(shù)據(jù)庫針對每一條SQL語句，提供了一個(gè)查看它的執(zhí)行計(jì)劃的工具，叫做EXPLAIN，咱們先來了解一下這個(gè)工具。首先說一下接下來用到的表，創(chuàng)建的表結(jié)構(gòu)為：
CREATE TABLE `tb_user` ( `id` BIGINT (20), `user_name` VARCHAR (200), `user_password` VARCHAR (200), `birth` DATETIME , `sex` CHAR (4), `age` int(8), `email` VARCHAR (200), `mobile` VARCHAR (200), `create_date` DATETIME , `update_date` DATETIME , `description` VARCHAR (800) ) ENGINE=INNODB;
創(chuàng)建一個(gè)存儲函數(shù)，向里面插入一百萬條數(shù)據(jù)：
DELIMITER $$
CREATE
PROCEDURE insert_tb_user()
BEGIN
DECLARE i INT;
SET i=0;
WHILE i>=0 && i<= 1000000 DO
INSERT INTO tb_user
(`id`, `user_name`, `user_password`, `birth`, `sex`, `age`, `email`, `mobile`, `create_date`, `update_date`, `description`) VALUES
(i,concat('admin',i),concat('admin',i),NOW(),'M',i,'admin@qq.com',concat('12345',i),NOW(),NOW(),concat('超級管理員',i));
SET i=i + 1;
END WHILE;
END$$
DELIMITER ;
-- 執(zhí)行存儲函數(shù)
call insert_tb_user()
生成的表數(shù)據(jù)如下：
利用這個(gè)表，我們來介紹一下執(zhí)行計(jì)劃和索引失效的情況。
首先來看一下執(zhí)行計(jì)劃的結(jié)果是什么樣的，執(zhí)行計(jì)劃的查看，就是在查詢語句的前面加上EXPLAIN關(guān)鍵字就可以了：
對于這些字段的意思，咱們一一來解釋：
一、 id，SELECT識別符。
*id相同時(shí)，執(zhí)行順序由上至下
*如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，越先被執(zhí)行
二、select_type，查詢中每個(gè)select子句的類型
*SIMPLE(簡單SELECT，不使用UNION或子查詢等)
*PRIMARY(子查詢中最外層查詢)
*UNION(UNION中的第二個(gè)或后面的SELECT語句)
*DEPENDENT UNION(UNION中的第二個(gè)或后面的SELECT語句)
*UNION RESULT(UNION的結(jié)果，union語句中第二個(gè)select開始后面所有select)
*SUBQUERY(子查詢中的第一個(gè)SELECT，結(jié)果不依賴于外部查詢)
*DEPENDENT SUBQUERY(子查詢中的第一個(gè)SELECT，依賴于外部查詢)
* DERIVED(派生表的SELECT, FROM子句的子查詢)
* UNCACHEABLE SUBQUERY(一個(gè)子查詢的結(jié)果不能被緩存，必須重新評估外鏈接的第一行)
三、 table，顯示這一步所訪問數(shù)據(jù)庫中表名稱。
四、type，對表訪問方式
*all：Full Table Scan， MySQL將遍歷全表以找到匹配的行
*index: Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹
*range:只檢索給定范圍的行，使用一個(gè)索引來選擇行
*ref: 表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值
*eq_ref: 類似ref，區(qū)別就在使用的索引是唯一索引，對于每個(gè)索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件
*const、system: 當(dāng)MySQL對查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個(gè)常量時(shí)，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量，system是const類型的特例，當(dāng)查詢的表只有一行的情況下，使用system
*NULL: MySQL在優(yōu)化過程中分解語句，執(zhí)行時(shí)甚至不用訪問表或索引，例如從一個(gè)索引列里選取最小值可以通過單獨(dú)索引查找完成。
五、 possible_keys，可能使用的索引。
六、key，實(shí)際使用的索引
七、key_len，索引中使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中使用的索引的長度
八、ref，列與索引的比較，表示上述表的連接匹配條件
九、rows，估算出結(jié)果集行數(shù)
十、Extra，MySQL解決查詢的詳細(xì)信息
*Using where:不用讀取表中所有信息，僅通過索引就可以獲取所需數(shù)據(jù)
*Using temporary：表示MySQL需要使用臨時(shí)表來存儲結(jié)果集，group by ; order by
*Using filesort：包含 order by ，而且無法利用索引完成的排序操作稱為“文件排序”
*Using join buffer：該值強(qiáng)調(diào)了在獲取連接條件時(shí)沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結(jié)果。
*Impossible where：這個(gè)值強(qiáng)調(diào)了where語句會導(dǎo)致沒有符合條件的行。
*Select tables optimized away：這個(gè)值意味著僅通過使用索引，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行
*No tables used：Query語句中使用from dual 或不含任何from子句
以上就是對EXPLAIN工具的一個(gè)介紹，了解了這個(gè)工具后，我們結(jié)合這個(gè)執(zhí)行工具，來看一看哪些情況會導(dǎo)致這個(gè)索引失效！
第一種情況：針對聯(lián)合索引，是否遵循最左匹配原則；
我們user_name,user_password,mobile建立一個(gè)聯(lián)合索引，如下：
聯(lián)合索引其實(shí)是建立了三個(gè)索引，也就是user_name的索引，user_name,user_password的索引，user_name,user_password,mobile的索引，最左匹配原則的意思，是否在語句中使用了最左建立的索引，也就是user_name的索引，演示一下：
當(dāng)我們把user_name的查詢條件去掉之后，會是什么情況呢？
我們會看到索引已經(jīng)失效了，用的是全表掃描，違背了最左匹配的原則，那么對于查詢語句select * from tb_user where user_password='admin66666' and user_name='admin66666' and mobile='1234566666'會用到索引嗎？答案如下：
也是用到了聯(lián)合索引，這和你條件中寫的順序是沒有關(guān)系的！
第二種情況：在索引列上做了函數(shù)操作，會導(dǎo)致索引失效而導(dǎo)致全表掃描
我們先把那個(gè)聯(lián)合索引刪除掉，然后在user_name這一列上建立一個(gè)唯一索引：
先不在索引列上做函數(shù)操作，執(zhí)行計(jì)劃如下：
很顯然使用了索引，那么索引列進(jìn)行函數(shù)操作呢？例如做一個(gè)字符拼接的操作：
第三種情況：語句中l(wèi)ike查詢是以%開頭，索引會失效變成全表掃描，覆蓋索引。
示例如下：
如果模糊查詢不是以%開頭的，那么也是可以用到索引的：
第四種情況：使用is not null 會導(dǎo)致無法使用索引
示例如下：
第五種情況：查詢語句中，如果條件中有or，即使其中有條件帶索引也不會使用。要想使用or，又想讓索引生效，只能將or條件中的每個(gè)列都加上索引
上面的sql語句是可以用到索引的，當(dāng)我們把a(bǔ)nd換成or時(shí)，就會變成全表掃描：
這時(shí)我們對mobile也加上索引，這條sql語句也就會使用上索引：
第六種情況：使用不等于(!= 或者<>)的時(shí)候，無法使用索引，會導(dǎo)致索引失效
第七種情況：不能使用索引中范圍條件右邊的列，范圍之后索引失效。（< ,> between and）
這些情況就不在進(jìn)行實(shí)際操作了，感興趣的朋友可以動手操作一下，也許隨著MySQL版本的更新迭代，對這些查詢語句進(jìn)行內(nèi)部優(yōu)化，一些索引失效的情況就會消失。除了以上這些情況會導(dǎo)致索引失效，還有哪些情況會導(dǎo)致索引失效呢？
