使用索引翻車了，差點(diǎn)被辭退
前一陣子生產(chǎn)環(huán)境出現(xiàn)了接口響應(yīng)慢的問題，最終定位是由于一條慢 SQL 導(dǎo)致的。分析了一下這條慢 SQL，發(fā)現(xiàn)對(duì)應(yīng)數(shù)據(jù)庫表也加了索引，最終結(jié)論是某些場(chǎng)景下索引失效了。
索引失效導(dǎo)致全表掃描，執(zhí)行速度非常慢，導(dǎo)致大量的 SQL 處于阻塞狀態(tài)。
領(lǐng)導(dǎo)：你好好反省一下，為啥出這么低級(jí)的問題。
我：好的，今晚通宵整一篇總結(jié)出來，年底不讓我扛績(jī)效了吧？！
領(lǐng)導(dǎo)：……看你這篇文章點(diǎn)贊能不能超過 100
我：……沒戲，他們喜歡白嫖
下面總結(jié)一下使用索引翻車的常見名場(chǎng)面，希望能幫助到大家，拿個(gè)好年終獎(jiǎng)！
為方便演示，我們提前先建立一張數(shù)據(jù)庫表。新建一個(gè)用戶表，id 為主鍵，user_id 為唯一索引，name 為普通索引，address 為普通索引：
CREATE?TABLE?`t_user`?(
??`id`?int?NOT?NULL?AUTO_INCREMENT,
??`name`?varchar(255)?DEFAULT?NULL,
??`age`?int?DEFAULT?NULL,
??`address`?varchar(255)?DEFAULT?NULL,
??`user_id`?int?DEFAULT?NULL,
??PRIMARY?KEY?(`id`),
??UNIQUE?KEY?`index_user_id`?(`user_id`)?USING?BTREE,
??KEY?`index_name`?(`name`)?USING?BTREE,
??KEY?`index_address`?(`address`)?USING?BTREE
)?ENGINE=InnoDB;
查詢條件包含or，可能導(dǎo)致索引失效
（1）執(zhí)行一條簡(jiǎn)單的 SQL
EXPLAIN
SELECT?*?FROM?t_user?WHERE?user_id?=?123456;
查看執(zhí)行計(jì)劃的結(jié)果，很顯然走了索引：
（2）加一個(gè) or 條件，把 age 字段加上
EXPLAIN
SELECT?*?FROM?t_user?WHERE?user_id?=?123456?or?age?=?18;
查看執(zhí)行計(jì)劃的結(jié)果，很顯然是全表掃描：
分析結(jié)論：
對(duì)于條件中出現(xiàn) or 的情況，user_id 列加了索引，age 列是沒有加索引的，假設(shè) MySQL 一定要走索引，可能需要三步：索引掃描+全表掃描+合并； 如果它一開始就走全表掃描，直接一遍掃描就完事了； mysql是有優(yōu)化器的，處于效率與成本，遇到 or條件，索引 可能失效，看起來也合情合理。
敲黑板: 上面說的這種情況索引有可能失效，沒有說一定會(huì)失效；如果 or 條件的列都加了索引，索引可能會(huì)走的，大家可以自己試一試。
like通配符可能導(dǎo)致索引失效
在業(yè)務(wù)中我們喜歡用模糊搜索的方式去做查詢，舉個(gè)例子，我們想搜索名稱為 leixiaoshuai 小伙子：
EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?=?'%leixiaoshuai%';
查看執(zhí)行計(jì)劃的結(jié)果，發(fā)現(xiàn)是全表掃描，說明索引失效了：
我們?cè)囍亚懊娴?去掉，再查一遍：
EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?=?'leixiaoshuai%';
執(zhí)行計(jì)劃告訴我們正常走索引了：
我們?cè)侔?加回來，這次不查詢所有的字段，只查詢 id 和 name：
EXPLAIN
SELECT?id,?name?FROM?t_user?WHERE?name?=?'%leixiaoshuai%';
你會(huì)驚奇的發(fā)現(xiàn)居然又走索引了：
驚不驚喜意不意外，這是因?yàn)楦采w索引的原因。
分析結(jié)論：
like查詢以%開頭，會(huì)導(dǎo)致索引失效。可以有兩種方式優(yōu)化：
使用覆蓋索引 把%放后面
敲黑板 索引包含所有滿足查詢需要的數(shù)據(jù)的索引，稱為覆蓋索引(Covering Index)。
字符串列使用 where 一定用引號(hào)括起來，否則索引失效
從上面的建表語句中我們知道 name 列是 varchar 可變字符類型，如果我們?cè)诓樵儠r(shí)忘了加引號(hào)會(huì)發(fā)生呢？
我們做一個(gè)簡(jiǎn)單的查詢，此時(shí) name 列沒有加引號(hào)：
EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?=?123;
name 列是建了普通索引的，理論上是應(yīng)該走索引的，實(shí)際上是全表掃描：
驚不驚喜意不意外，嚇得我趕緊把引號(hào)加起來了：
EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?=?'123';
這次果然乖乖走索引了：
分析結(jié)論：如果某一列是字符類型，但是使用 where 不加單引號(hào)時(shí)，是字符串跟數(shù)字的比較，它們類型不匹配，MySQL會(huì)做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點(diǎn)數(shù)再做比較。
在索引列上使用mysql的內(nèi)置函數(shù)，索引失效
name 列已經(jīng)加了普通索引，但是在查詢的時(shí)候給加了內(nèi)置函數(shù)，作用是將 name 列變成全大寫：
EXPLAIN
SELECT?*?FROM?t_user?WHERE?UPPER(name)?=?'LEIXIAOSHUAI';
索引直接 GG 了，套了一層殼子，我就不認(rèn)識(shí)你了，我直接全表掃描：
索引字段上使用is null或is not null，可能導(dǎo)致索引失效
根據(jù)前面的建表語句我們知道 name 列，address 列都加了普通索引。
（1）查找 name 不為空的所有列
EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?is?not?null;
意料之中，正常走了索引：
（2）查找 address 不為空的所有列
EXPLAIN
SELECT?*?FROM?t_user?WHERE?address?is?not?null;
意料之中，正常走了索引：
（3）查找 name 不為空或者 address 不為空
EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?is?not?null?or?address?is?not?null;
咦？？？索引好像失效了：
對(duì)索引列運(yùn)算索引失效
對(duì)索引列進(jìn)行四則運(yùn)算（如，+、-、*、/），索引會(huì)失效。
user_id 是索引列，在查詢的時(shí)候進(jìn)行+1
EXPLAIN
SELECT?*?FROM?t_user?WHERE?user_id+1=?456789;
索引直接迷路了：
聯(lián)合索引ABC問題導(dǎo)致索引失效
我們重新建一張表，id 是主鍵，name 和 age 列加了一個(gè)聯(lián)合索引。
CREATE?TABLE?`t_user`?(
??`id`?int?NOT?NULL?AUTO_INCREMENT,
??`name`?varchar(255)?DEFAULT?NULL,
??`age`?int?DEFAULT?NULL,
??`address`?varchar(255)?DEFAULT?NULL,
??`user_id`?int?DEFAULT?NULL,
??PRIMARY?KEY?(`id`),
??KEY?`index_name_age`?(`name`,`age`)?USING?BTREE
)?ENGINE=InnoDB;
特別要注意一下聯(lián)合索引的順序，name 在前，age 在后。
（1）where 條件同時(shí)滿足 name 和 age
EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?=?'leixiaoshuai'?AND?age?=?18;
毫無疑問肯定是走了索引：
（2）where 條件只有 name 列，這個(gè)能否走索引呢？
EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?=?'leixiaoshuai';
看結(jié)果，依然是走了聯(lián)合索引，開心~
（3）where 條件只有 age 列，這個(gè)應(yīng)該也可以走索引吧？！
EXPLAIN
SELECT?*?FROM?t_user?WHERE?age?=?18;
完蛋，翻車了，沒走索引……
分析結(jié)論：
當(dāng)我們創(chuàng)建一個(gè)聯(lián)合索引的時(shí)候，如(k1,k2,k3)，相當(dāng)于創(chuàng)建了（k1）、(k1,k2)和(k1,k2,k3)三個(gè)索引，這就是 最左匹配原則。聯(lián)合索引不滿足最左原則，索引一般會(huì)失效，但是這個(gè)還跟Mysql優(yōu)化器有關(guān)的。
總結(jié)：
以上就是使用索引時(shí)最常見的 7 種翻車場(chǎng)景，大家有沒有中過招？
最后，我向領(lǐng)導(dǎo)也坦白了，我是因?yàn)槁?lián)合索引 ABC 問題中招的，實(shí)在是草率了……
-- End --
這些文章你可得看：
????『死磕Java并發(fā)編程系列』 01 十張圖告訴你多線程那些破事
『死磕Java并發(fā)編程系列』 02 面試官：說說什么是Java內(nèi)存模型？
『死磕Java并發(fā)編程系列』 03 面試必問的CAS原理你會(huì)了嗎？
作者簡(jiǎn)介：
博主從華中科技大學(xué)碩士畢業(yè)，是一個(gè)對(duì)技術(shù)有追求，對(duì)生活有激情的程序員。
幾年間浪跡于多個(gè)一線互聯(lián)網(wǎng)大廠，具有多年開發(fā)實(shí)戰(zhàn)經(jīng)驗(yàn)。
如果你熱愛技術(shù)或者你也不滿足現(xiàn)狀喜歡搞事情，那你不妨關(guān)注我，讓我們一路同行，一起分享技術(shù)干貨、交流面試技巧，吐槽職場(chǎng)故事。
我有技術(shù)和故事，你來嗎？
我該不會(huì)被辭退了吧