大家好，我是君哥，今天来分享一道面试题。
面试官：使用 MySQL 时你遇到过哪些索引失效的场景？
我：MySQL 索引失效的场景有很多，我说一下我遇到的几个场景。 先假定有一张员工表，sql 如下：
CREATE TABLE`tb_staff` (
`staff_id`tinyint(3) NOTNULLCOMMENT'员工编号',
`id_no`varchar(
20) DEFAULTNULLCOMMENT'员工姓名',
`name`varchar(20) DEFAULTNULLCOMMENT'员工姓名',
`email`varchar(200) DEFAULTNULL
COMMENT'邮件地址',
`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=utf8
1. 使用 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';
面试官：恭喜你，通过了。
