線上使用索引翻車了，績(jī)效涼涼

前一陣子生產(chǎn)環(huán)境出現(xiàn)了接口響應(yīng)慢的問(wèn)題，最終定位是由于一條慢 SQL 導(dǎo)致的。分析了一下這條慢 SQL，發(fā)現(xiàn)對(duì)應(yīng)數(shù)據(jù)庫(kù)表也加了索引，最終結(jié)論是某些場(chǎng)景下索引失效了。

索引失效導(dǎo)致全表掃描，執(zhí)行速度非常慢，導(dǎo)致大量的 SQL 處于阻塞狀態(tài)。

領(lǐng)導(dǎo)：你好好反省一下，為啥出這么低級(jí)的問(wèn)題。

我：好的，今晚通宵整一篇總結(jié)出來(lái)，年底不讓我扛績(jī)效了吧？！

領(lǐng)導(dǎo)：……看你這篇文章點(diǎn)贊能不能超過(guò) 100

我：……沒(méi)戲，他們喜歡白嫖

下面總結(jié)一下使用索引翻車的常見(jiàn)名場(chǎng)面，希望能幫助到大家，拿個(gè)好年終獎(jiǎng)！

為方便演示，我們提前先建立一張數(shù)據(jù)庫(kù)表。新建一個(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 列是沒(méi)有加索引的，假設(shè) MySQL 一定要走索引，可能需要三步：索引掃描+全表掃描+合并；
• 如果它一開(kāi)始就走全表掃描，直接一遍掃描就完事了；
• mysql是有優(yōu)化器的，處于效率與成本，遇到 or條件，索引可能失效，看起來(lái)也合情合理。

敲黑板: 上面說(shuō)的這種情況索引有可能失效，沒(méi)有說(shuō)一定會(huì)失效；如果 or 條件的列都加了索引，索引可能會(huì)走的，大家可以自己試一試。

like通配符可能導(dǎo)致索引失效

在業(yè)務(wù)中我們喜歡用模糊搜索的方式去做查詢，舉個(gè)例子，我們想搜索名稱為 leixiaoshuai 小伙子：

EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?=?'%leixiaoshuai%';

查看執(zhí)行計(jì)劃的結(jié)果，發(fā)現(xiàn)是全表掃描，說(shuō)明索引失效了：

我們?cè)囍亚懊娴?去掉，再查一遍：

EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?=?'leixiaoshuai%';

執(zhí)行計(jì)劃告訴我們正常走索引了：

我們?cè)侔?加回來(lái)，這次不查詢所有的字段，只查詢 id 和 name：

EXPLAIN
SELECT?id,?name?FROM?t_user?WHERE?name?=?'%leixiaoshuai%';

你會(huì)驚奇的發(fā)現(xiàn)居然又走索引了：

驚不驚喜意不意外，這是因?yàn)楦采w索引的原因。

分析結(jié)論：

like查詢以%開(kāi)頭，會(huì)導(dǎo)致索引失效?？梢杂袃煞N方式優(yōu)化：

• 使用覆蓋索引
• 把%放后面

敲黑板 索引包含所有滿足查詢需要的數(shù)據(jù)的索引，稱為覆蓋索引(Covering Index)。

字符串列使用 where 一定用引號(hào)括起來(lái)，否則索引失效

從上面的建表語(yǔ)句中我們知道 name 列是 varchar 可變字符類型，如果我們?cè)诓樵儠r(shí)忘了加引號(hào)會(huì)發(fā)生呢？

我們做一個(gè)簡(jiǎn)單的查詢，此時(shí) name 列沒(méi)有加引號(hào)：

EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?=?123;

name 列是建了普通索引的，理論上是應(yīng)該走索引的，實(shí)際上是全表掃描：

驚不驚喜意不意外，嚇得我趕緊把引號(hào)加起來(lái)了：

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ù)前面的建表語(yǔ)句我們知道 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問(wèn)題導(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;

毫無(wú)疑問(wèn)肯定是走了索引：

（2）where 條件只有 name 列，這個(gè)能否走索引呢？

EXPLAIN
SELECT?*?FROM?t_user?WHERE?name?=?'leixiaoshuai';

看結(jié)果，依然是走了聯(lián)合索引，開(kāi)心~

（3）where 條件只有 age 列，這個(gè)應(yīng)該也可以走索引吧？！

EXPLAIN
SELECT?*?FROM?t_user?WHERE?age?=?18;

完蛋，翻車了，沒(méi)走索引……

分析結(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í)最常見(jiàn)的 7 種翻車場(chǎng)景，大家有沒(méi)有中過(guò)招？

最后，我向領(lǐng)導(dǎo)也坦白了，我是因?yàn)槁?lián)合索引 ABC 問(wèn)題中招的，實(shí)在是草率了……

-- End --

圖解系列文章：