Mysql高級調優篇——第四章：Sql實戰調優場景剖析（下）-有解無憂

上一章我們講述了索引調優實戰在Join的程序，那么本章重點闡述索引失效的場景及原因剖析！

1、索引失效場景

老規矩先匯入一些表作為資料使用，表的所有定義在這個鏈接中：

Mysql高級調優篇表補充——建表SQL_風清揚逍遙子的博客-CSDN博客??tbl_emp??CREATE TABLE `tbl_emp` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,`deptId` int(11) DEFAULT NULL,PRIMARY KEY (`id`) ,KEY `fk_dept_id`(`deptId`))ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;??tbl_dhttps://blog.csdn.net/qq_31821733/article/details/120886389?spm=1001.2014.3001.5501 匯入staffs員工表：

1.1、全值匹配我最愛

我們添加了索引：

看第一個Sql：select * from staffs where name='July';

我建立了復合索引分別是name，age，pos，我只查一個，索引會不會失效？

看下執行計劃可以知道走索引上，你建立三個，我走了1個，沒問題

再來一個Sql：select * from staffs where name='July' and age = 25;

執行計劃知道，走部分索引一樣沒問題，

但是不知道有沒有發現一個問題！！Extra為Null？？這個是第一次出現，那么這里我要重點強調下，Extra為Null的時候，如果走了索引，說明這個查詢，進行了回表！！

那么什么是回表呢？簡單來說，如果你查詢的欄位，存在非索引欄位，那么查詢的時候，Mysql雖然根據了你的條件得到了這個記錄，但是不在索引的欄位無法通過索引的方式直接得到，只能通過拿到該條記錄的主鍵索引，再從資料行里讀，我們知道Mysql索引檔案和資料檔案是在兩個不同的檔案里的，要去讀磁盤；所以索引檔案建立的效果，就是幫助我們對資料進行排序和查找效率的優化，不至于去讀資料行進行額外的IO開銷；

所以這里欄位我用select *，因為復合索引里沒有add_time這個欄位，所以無法直接查出來add_time這個列的記錄，要通過定位到主鍵，然后再讀一次資料行才可以得到這個記錄，稱為回表，

如果我這么寫，就不會出現回表，因為pos在索引列中！

而查add_time，就回表了：

那我再加一個欄位pos，很明顯精度越高，key_len的長度就越長，總結是【全值匹配我最愛】

現在三種2情況都正常，我們來看一些特殊場景！

explain select * from staffs where age = 23 and pos = 'dev'; 執行計劃走一下發現

索引怎么不走了？再看看這個sql：select * from staffs where pos = 'dev';

索引也不走！

再來一個sql：select * from staffs where name = 'zhangsan';走索引了

總結下來：如果查詢中沒有開頭的索引，也就是不告訴我1樓在哪里，讓我從2樓或者3口開始找索引，不好意思，我找不到，只能全表掃！違背了【最佳左前綴法則】

回顧下我們建立的索引，（name, age, pos）順序是1樓，2樓，3樓；如果索引了多列，要遵守最左前綴法則，指的是查詢從索引的最左前列開始并且不跳過索引中的列，即【帶頭大哥不能死】

再看下這個sql：select * from staffs where name = 'zhangsan' and pos = 'dev'; 這個sql等于中間2樓斷開了，執行計劃顯示這個key_len和只有name的時候一樣，說明只走了name索引，Extra中出現Using index condition，這個是5.6后新加的特性，會先條件過濾索引，過濾完索引后找到所有符合索引條件的資料行，隨后用 WHERE 子句中的其他條件去過濾這些資料行；其實沒有什么用，就是走到了索引上的意思，【中間兄弟不能斷】

總結：全值匹配我最愛，最左前綴要遵守；帶頭大哥不能死，中間兄弟不能斷；

1.2、勿在索引列做任何操作

不要在索引列上做任何操作，包括計算，函式，自動或者手動型別轉換，會導致索引失效而轉向全表掃描，

舉例子：explain select * from staffs where left(name, 4) = 'July'; 查找name左往右4個字符為July的行，索引失效了！

總結：【索引列上少計算】

1.3、范圍之后全失效

這個上一節提過，like后面的索引會失效，即Mysql存盤引擎不能使用索引中，范圍條件右邊的列！

舉例子，explain select * from staffs where name = 'July' and age > 14 and pos = 'manager'; 因為2樓已經是范圍查找了，age用到了索引，進行范圍查找，但是后面的索引pos就失效了，因為你告訴我2樓，沒告訴我去3樓的路，所以我找不到3樓的目錄，定位不到3樓，只能挨個去找入口！這里要注意，5.7以前的優化，是如果出現了范圍查找，則當前范圍的索引也不走，而5.7后，范圍索引之后的才失效，所以這里的key_len=78，單個name話是74，三個都走是140，

1.4、盡量使用覆寫索引

查詢陳述句中只訪問索引的查詢，索引列和查詢列盡量保持一致，或者查詢列<=索引列：

看這個sql：explain select * from staffs where name = 'July' and age = 15 and pos = 'manager'; 如果用了* ，我說過會回表！但是指定了索引的列，那么就走了覆寫索引，Using index是很好的效果！

包括以下的場景也是一樣！

1.5、不等于場景下索引失效

Mysql在使用不等于的場景下，無法使用索引導致全表掃描

explain select * from staffs where name != 'July'; 執行計劃后發現

以及explain select * from staffs where name <> 'July';

1.6、is null、is not null無法使用索引

看下這個sql：explain select * from staffs where name is null; 這個就很離譜了

explain select * from staffs where name is not null;

1.7、Like百分寫最右

like以通配符開頭('%abc...')時，Mysql索引會失效變成全表掃！

比如這個sql：explain select * from staffs where name like '%July%';

如果是這樣的，也是一樣：explain select * from staffs where name like '%July'; 原因我這里不多解釋了，上幾章節都有，

但是這樣的話，就不會失效：explain select * from staffs where name like 'July%';

因為like是范圍查找，百分號在后面，Mysql會拿到字典序進行排序的方式查找對應的情況，而百分號在前面，Mysql就不知道從哪個字母開始找，于是便全表掃描，

那這個是不是解決不了？實際面試中經常會這么問：如何解決like '%xxx%' 字符時索引不被使用的情況？

答案是用覆寫索引避免索引失效，我們這里的索引是(name, age, pos)，索引我們在查詢的時候不要寫select *，只要寫具體的欄位值，任何一個列被覆寫索引覆寫，就可以解決兩邊百分號的問題！！！

如果我這么寫的話，肯定不行，因為add_time不在覆寫索引內

總結：【like百分寫最右】+【覆寫索引解決雙百分問題】

1.8、字串不加單引號索引失效

比如這個sql：explain select * from staffs where name = 222; 索引失效

而這個是成功走到索引的：explain select * from staffs where name = '222';

Mysql很聰明，你以為你給我的我就查不到了，你給我的Int型的時候，實際這個欄位是varchar型，傳入數字會隱式的幫你轉換成varchar型別，前面說過不要讓Mysql做這些自動或者手動的型別轉換，否則索引失效！當然查詢的結果，是不會有變化的，只是sql執行上有轉換，

1.9、少用or

少用or，會導致索引失效，不是不用；

比如explain select * from staffs where name = 'July' or name = 'z3'; 雖然都是帶頭大哥的name，但是索引一樣丟失了，

2、總結口訣

全值匹配我最愛，最左前綴要遵守；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計算，范圍之后全失效；
Like百分寫最右，覆寫索引不寫星；
不等空值還有or，索引失效要少用；
VAR引號不可丟，SQL高級并不難！

這些口訣，可以幫助我們的記憶！