索引失效的场景总结

补充文档：not in、not exists、order by可能导致索引失效

EXPLAIN
为方便演示，提前先建立一张数据库表。新建一个用户表，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;

1. 查询条件包含or，可能导致索引失效

（1）执行一条简单的 SQL

EXPLAIN
SELECT * FROM t_user WHERE user_id = 123456;

查看执行计划的结果，很显然走了索引：

（2）加一个 or 条件，把 age 字段加上

EXPLAIN
SELECT * FROM t_user WHERE user_id = 123456 or age = 18;

查看执行计划的结果，很显然是全表扫描：

分析结论：

• 对于条件中出现 or 的情况，user_id 列加了索引，age 列是没有加索引的，假设 MySQL 一定要走索引，可能需要三步：索引扫描+全表扫描+合并；
• mysql是有优化器的，出于效率与成本，如果它一开始就走全表扫描，直接一遍扫描就完事了，遇到 or条件，索引可能失效，看起来也合情合理；
• 注意：如果使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效，这是一个大坑。

  上面说的这种情况索引有可能失效，没有说一定会失效；如果 or 条件的列都加了索引，索引可能会走的，大家可以自己试一试。

2. like通配符，可能导致索引失效

在业务中喜欢用模糊搜索的方式去做查询，举个例子，搜索名称为 leixiaoshuai 小伙子：

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

查看执行计划的结果，发现是全表扫描，说明索引失效了：

试着把前面的%去掉，再查一遍：

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

再把%加回来，这次不查询所有的字段，只查询 id 和 name：

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

惊不惊喜意不意外，这是因为覆盖索引的原因。
分析结论：
like查询以%开头，会导致索引失效。可以有两种方式优化：

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

  覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

3. 字段类型不匹配

从上面的建表语句中知道 name 列是 varchar 可变字符类型，如们在查询时忘了加引号会发生呢？
做一个简单的查询，此时 name 列没有加引号：

EXPLAIN
SELECT * FROM t_user WHERE name = 123;

把引号加起来：

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

这次果然走索引了：

分析结论：如果某一列是字符类型，但是使用 where 不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

4. 索引列上使用mysql的内置函数

name 列已经加了普通索引，但是在查询的时候给加了内置函数，作用是将 name 列变成全大写：

EXPLAIN
SELECT * FROM t_user WHERE UPPER(name) = 'LEIXIAOSHUAI';

套了一层壳子，就不认识了，直接全表扫描：

5. 索引字段上使用is null或is not null，可能导致索引失效

not in和 not exists也可能会导致索引失效，详细情况查看补充文档。

根据前面的建表语句知道 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;

索引好像失效了：

6. 索引列上有计算

对索引列进行四则运算（如，+、-、*、/），索引会失效。
user_id 是索引列，在查询的时候进行+1

EXPLAIN
SELECT * FROM t_user WHERE user_id+1= 456789;

索引直接迷路了：

7. 联合索引不满足最左匹配原则

重新建一张表，id 是主键，name 和 age 列加了一个联合索引。

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;

特别要注意一下联合索引的顺序，name 在前，age 在后。
（1）where 条件同时满足 name 和 age

EXPLAIN
SELECT * FROM t_user WHERE name = 'leixiaoshuai' AND age = 18;

毫无疑问肯定是走了索引：

（2）where 条件只有 name 列，这个能否走索引呢？

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

看结果，依然是走了联合索引。

（3）where 条件只有 age 列，这个应该也可以走索引吧？

EXPLAIN
SELECT * FROM t_user WHERE age = 18;

没走索引……

分析结论：

• 当创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
• 联合索引不满足最左原则，索引一般会失效，但是这个还跟MySQL优化器有关的。

补充

索引失效的10种场景