面試官：使用 MySQL 時你遇到過哪些索引失效的場景？
大家好，我是君哥，今天來分享一道面試題。
面試官：使用 MySQL 時你遇到過哪些索引失效的場景？
我：MySQL 索引失效的場景有很多，我說一下我遇到的幾個場景。 先假定有一張員工表，sql 如下：
CREATE TABLE`tb_staff` (
`staff_id`tinyint(3) NOTNULLCOMMENT'員工編號',
`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'創建時間',
`update_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'更新時間',
PRIMARY KEY (`staff_id`),
KEY`id_no` (`id_no`),
KEY`union_idno_name_email` (`id_no`,`name`,`email`)
) ENGINE=InnoDBDEFAULTCHARSET=utf81. 使用 like 語句做模糊查詢時，占位符 % 放在了最左邊。比如我們查找 id_no 以 8933 結尾的員工：
select * from tb_staff where id_no like '%8933';雖然 id_no 字段加了索引，但上面的 SQL 因為占位符在最左邊，也不能走索引。
2. 使用 not like 語句，不能走索引。下面的 sql 使用 id_no 作為條件 ，不能走索引：
SELECT * FROM db_staff WHERE id_no NOT LIKE '120%';3. 使用 not in 語句時，也不能走索引。舉個例子 ：
select * from tb_staff where id_no not in ('xxxx','yyyy');4. 使用 not exists 語句時，也不能走索引。舉個例子 ：再建一張專門保存 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 后面跟的是主鍵，有可能會走索引。比如 not in 排除的值比較少，這種情況是會走索引的。
面試官：你還遇到過其他索引失效的場景嗎？
我：還有幾個場景，我再說一下：
5. 在條件語句中使用函數、表達式或隱式轉換，比如下面的 sql：
--使用表達式
EXPLAIN SELECT * FROM db_staff WHERE staff_id + 1 = 2;
--使用隱式轉換，數值類型轉VARCHAR
EXPLAIN SELECT * FROM db_staff WHERE id_no = 110112202409881123;6. 使用不等于，!=，<> 時也不會走索引。
面試官：使用“不等于”條件時一定不能走索引嗎？
我：不一定，如果條件時主鍵時，也是可以走索引的。
面試官：還有其他索引失效的場景嗎？
我：我想想。。
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 語句有優化方法嗎？
我：可以使用 union 語句來替代，比如：
SELECT * FROM db_staff WHERE staff_id = 2 UNION SELECT * FROM db_staff WHERE id_no = '110112202409881123'如果查詢的字段比較少，可以走覆蓋索引，前面建表語句對 id_no、name、email 三個字段 加了覆蓋索引，比如下面 sql:
SELECT id_no,NAME,email FROM db_staff WHERE id_no = '110112202409881123' OR NAME='zhangsan'面試官：還有其他索引失效的場景嗎？
我：還有下面一個場景:
9. 如果查詢的數據集比較大，占整個表數據量比例較大時，MySQL 可能會認為走全表掃描代價更小，所以選擇走全表掃描。這時候可以增加條件過濾減小結果集，或者強制使用索引。
SELECT * FROM db_staff FORCE INDEX(union_idno_name_email)面試官：order by 語句有可能會讓查詢走不上索引嗎？
我：有可能。
10. order by 中的字段跟 where 條件中字段不一致時，也可能會導致索引失效。比如下面的 SQL：
SELECT id_no,NAME,email FROM db_staff WHERE id_no > '110112202409881120' ORDER BY create_time;而且，group by 也有這個問題。但如果覆蓋索引可以包含 where 條件和 order by 中的字段，則可以走覆蓋索引。
SELECT id_no,NAME,email FROM db_staff WHERE id_no > '110112202409881120' ORDER BY email;面試官：還有其他嗎？
我：我知道的就這些了，當然也跟數據庫的版本有一些關系。sql 是否走索引，決定因素很多，比如查詢語句、結果集等。我們寫 sql 語句時，如果表數據量比較大，最好用執行計劃 EXPLAIN 分析一下 sql 是否正確地走索引了。
面試官：執行計劃有哪些屬性呢，可以說一下嗎？
我： 我了解的屬性如下：
- type: 訪問類型，即索引的使用方式，查詢效率從高到底依次是：system > const > eq_ref > ref > range > index > ALL；
- key: 實際使用的索引，如果為NULL則表示未使用索引；
- key_len：索引字段的長度，使用聯合索引時這個字段可以看到使用了聯合索引的哪些字段；
- rows: 預計掃描行數，這個屬性值越小執行效率越高；
- Extra: 額外信息，比如 Using where、Using filesort、Using index 。
面試官：上面 type 屬性中的 eq_ref 和 ref 能講一下嗎？
我：好的
- eq_ref 是指每行數據都是通過主鍵或唯一索引與另一張表做 join，每次 join 只會匹配到一行數據。比如下面 sql：
SELECT f.staff_id FROM db_staff f LEFT JOIN db_staff a ON a.staff_id = f.staff_id- ref 是指使用普通索引(不包括唯一索引)進行查找，查詢條件可能匹配索引中的多個行。比如下面 sql：
SELECT id_no,NAME,email FROM db_staff WHERE id_no = '110112202409881120';面試官：恭喜你，通過了。
