大家好，我是君哥，今天來分享一道面試題。

面試官：使用 MySQL 時(shí)你遇到過哪些索引失效的場景？

我：MySQL 索引失效的場景有很多，我說一下我遇到的幾個(gè)場景。 先假定有一張員工表，sql 如下：

CREATE TABLE`tb_staff` (
`staff_id`tinyint(3) NOTNULLCOMMENT'員工編號(hào)',
`id_no`varchar(20) DEFAULTNULLCOMMENT'員工姓名',
`name`varchar(20) DEFAULTNULLCOMMENT'員工姓名',
`email`varchar(200) DEFAULTNULLCOMMENT'郵件地址',
`age`tinyint(3) DEFAULTNULLCOMMENT'年齡',
`sex`tinyint(1) DEFAULT'0'COMMENT'性別，0:男 1:女',
`address`varchar(300) DEFAULTNULLCOMMENT'家庭住址',
`create_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'創(chuàng)建時(shí)間',
`update_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'更新時(shí)間',
  PRIMARY KEY (`staff_id`),
KEY`id_no` (`id_no`),
KEY`union_idno_name_email` (`id_no`,`name`,`email`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8

1. 使用 like 語句做模糊查詢時(shí)，占位符 % 放在了最左邊。比如我們查找 id_no 以 8933 結(jié)尾的員工：

select * from tb_staff where id_no like '%8933';

雖然 id_no 字段加了索引，但上面的 SQL 因?yàn)檎嘉环谧钭筮?，也不能走索引?/span>

2. 使用 not like 語句，不能走索引。下面的 sql 使用 id_no 作為條件 ，不能走索引：

SELECT * FROM db_staff WHERE id_no NOT LIKE  '120%';

3. 使用 not in 語句時(shí)，也不能走索引。舉個(gè)例子 ：

select * from tb_staff where id_no not in ('xxxx','yyyy');

4. 使用 not exists 語句時(shí)，也不能走索引。舉個(gè)例子 ：再建一張專門保存 staff_id 的表 db_staff_id

SELECT * FROM db_staff f WHERE NOT EXISTS (SELECT staff_id FROM db_staff_id a WHERE a.staff_id = f.staff_id);

面試官：not in 語句一定不能走索引嗎？

我：不一定。如果 not in 后面跟的是主鍵，有可能會(huì)走索引。比如 not in 排除的值比較少，這種情況是會(huì)走索引的。

面試官：你還遇到過其他索引失效的場景嗎？

我：還有幾個(gè)場景，我再說一下： 

5. 在條件語句中使用函數(shù)、表達(dá)式或隱式轉(zhuǎn)換，比如下面的 sql：

--使用表達(dá)式 
EXPLAIN SELECT * FROM db_staff WHERE staff_id + 1 = 2;
--使用隱式轉(zhuǎn)換，數(shù)值類型轉(zhuǎn)VARCHAR
EXPLAIN SELECT * FROM db_staff WHERE id_no = 110112202409881123;

6. 使用不等于，!=，<> 時(shí)也不會(huì)走索引。

面試官：使用“不等于”條件時(shí)一定不能走索引嗎？

我：不一定，如果條件時(shí)主鍵時(shí)，也是可以走索引的。

面試官：還有其他索引失效的場景嗎？

我：我想想。。

 7. IS NOT NULL 語句也不能走索引，比如：

SELECT * FROM db_staff WHERE id_no IS NOT NULL;

8. 使用 or 語句也不能走索引，比如：

SELECT * FROM db_staff WHERE staff_id = 2 OR id_no ='4';

但如果 or 語句涉及的條件都是主鍵，也是可以走索引的：

SELECT * FROM db_staff WHERE staff_id = 2 OR staff_id =3;

面試官：or 語句有優(yōu)化方法嗎？

我：可以使用 union 語句來替代，比如：

SELECT * FROM db_staff WHERE staff_id = 2 UNION SELECT * FROM db_staff WHERE id_no = '110112202409881123'

如果查詢的字段比較少，可以走覆蓋索引，前面建表語句對(duì) id_no、name、email 三個(gè)字段 加了覆蓋索引，比如下面 sql:

SELECT id_no,NAME,email FROM db_staff WHERE id_no = '110112202409881123' OR NAME='zhangsan'

面試官：還有其他索引失效的場景嗎？

我：還有下面一個(gè)場景: 

9. 如果查詢的數(shù)據(jù)集比較大，占整個(gè)表數(shù)據(jù)量比例較大時(shí)，MySQL 可能會(huì)認(rèn)為走全表掃描代價(jià)更小，所以選擇走全表掃描。這時(shí)候可以增加條件過濾減小結(jié)果集，或者強(qiáng)制使用索引。

SELECT * FROM db_staff FORCE INDEX(union_idno_name_email)

面試官：order by 語句有可能會(huì)讓查詢走不上索引嗎？

我：有可能。

10. order by 中的字段跟 where 條件中字段不一致時(shí)，也可能會(huì)導(dǎo)致索引失效。比如下面的 SQL：

SELECT id_no,NAME,email FROM db_staff WHERE id_no > '110112202409881120' ORDER BY create_time;

而且，group by 也有這個(gè)問題。但如果覆蓋索引可以包含 where 條件和 order by 中的字段，則可以走覆蓋索引。

SELECT id_no,NAME,email FROM db_staff WHERE id_no > '110112202409881120' ORDER BY email;

面試官：還有其他嗎？

我：我知道的就這些了，當(dāng)然也跟數(shù)據(jù)庫的版本有一些關(guān)系。sql 是否走索引，決定因素很多，比如查詢語句、結(jié)果集等。我們寫 sql 語句時(shí)，如果表數(shù)據(jù)量比較大，最好用執(zhí)行計(jì)劃 EXPLAIN 分析一下 sql 是否正確地走索引了。

面試官：執(zhí)行計(jì)劃有哪些屬性呢，可以說一下嗎？

我： 我了解的屬性如下：

• type: 訪問類型，即索引的使用方式，查詢效率從高到底依次是：system > const > eq_ref > ref > range > index > ALL；
• key: 實(shí)際使用的索引，如果為NULL則表示未使用索引；
• key_len：索引字段的長度，使用聯(lián)合索引時(shí)這個(gè)字段可以看到使用了聯(lián)合索引的哪些字段；
• rows: 預(yù)計(jì)掃描行數(shù)，這個(gè)屬性值越小執(zhí)行效率越高；
• Extra: 額外信息，比如 Using where、Using filesort、Using index 。

面試官：上面 type 屬性中的 eq_ref 和 ref 能講一下嗎？

我：好的

• eq_ref 是指每行數(shù)據(jù)都是通過主鍵或唯一索引與另一張表做 join，每次 join 只會(huì)匹配到一行數(shù)據(jù)。比如下面 sql：

SELECT f.staff_id FROM db_staff f LEFT JOIN db_staff a ON a.staff_id = f.staff_id

• ref 是指使用普通索引(不包括唯一索引)進(jìn)行查找，查詢條件可能匹配索引中的多個(gè)行。比如下面 sql：

SELECT id_no,NAME,email FROM db_staff WHERE id_no = '110112202409881120';

面試官：恭喜你，通過了。