面试官：使用 MySQL 时你遇到过哪些索引失效的场景？

脚本之家 设为“星标⭐”
第一时间收到文章更新

来源 | 君哥聊技术（ID：gh_1f109b82d301）

我：MySQL 索引失效的场景有很多，我说一下我遇到的几个场景。先假定有一张员工表，sql 如下：

CREATE TABLE`tb_staff` (
`staff_id`tinyint(3) NOTNULLCOMMENT'员工编号',
`id_no`varchar(20) DEFAULTNULL


    
COMMENT'员工姓名',
`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=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';

面试官：恭喜你，通过了。





    
  推荐阅读：