聊聊索引失效的经典场景

前置条件

数据库版本为5.7.17。

创建一张user表，预置500w条数据，每个字段的值都是前缀加上从0开始计数的数字，累加到5000000，如图：

然后用explain来解析一下有没有走索引。

最左匹配原则

我们首先建一个组合索引，username，jobno，company三个字段：

首先执行以下几个sql：

EXPLAIN SELECT * FROM `user` WHERE username = cxj1000000 AND jobno = jn1000000 AND company = com1000000;

EXPLAIN SELECT * FROM `user` WHERE username = cxj1000000;

EXPLAIN SELECT * FROM `user` WHERE username = cxj1000000 AND jobno = jn1000000;

EXPLAIN SELECT * FROM `user` WHERE jobno = jn1000000 AND username = cxj1000000;

EXPLAIN SELECT * FROM `user` WHERE username = cxj1000000 AND company = com1000000;

发现都走了索引。

接下来再执行几个sql：

EXPLAIN SELECT * FROM `user` WHERE jobno = jn1000000 AND company = com1000000;

EXPLAIN SELECT * FROM `user` WHERE company = com1000000;

EXPLAIN SELECT * FROM `user` WHERE jobno = jn1000000 ;

发现没有走索引。

结论：最左匹配原则要求查询的sql语句中，必须包含最左边的字段，在username，jobno，company的组合索引中，username是最左边的字段，那么查询的sql语句中的where条件中，必须包含username字段，而与sql语句中username的使用顺序无关。

索引列上有计算

根据主键ID查询，毫无疑问会走主键索引，但如果像下面这种：

EXPLAIN SELECT * FROM `user` WHERE id 1 = 2

可以看到没有走索引。

总结：如果索引列参与了计算，不会命中索引。像这种情况可以变换一下等式，把运算放到等号右边，就会命中索引。

EXPLAIN SELECT * FROM `user` WHERE id = 2 - 1

查询条件带or

上面所说的最左匹配原则中，只要查询语句中包含username就会走索引，但如果我们把and条件换成or，即：

SELECT * FROM `user` WHERE username = cxj13 OR jobno = jn13

可以看到并没有走索引，而是全表扫描，所以在带有or的查询语句中，索引将失效，除非所有条件都带有索引。也就是说，username有索引，jobno也必须要建一个索引才会生效。

like查询

在username字段上新建索引user_idx_normal_username，不使用%模糊查询：

SELECT * FROM `user` WHERE username LIKE cxj

使用前置%：

使用后置%:

前后都用%：

结论：模糊查询中，只要使用了%都不会走索引，不使用%号时可以走索引。

字段类型不同

还是使用username进行查询，username的字段类型是字符串类型，我们知道以下语句：

SELECT * FROM `user` WHERE username = cxj13

肯定是会走索引的，但如果我们不用引号引用起来，而是：

SELECT * FROM `user` WHERE username = 100

会发现没有走索引：

结论：字符串的索引字段在查询时数据需要用引号引用，否则索引失效。

查询语句包含in

这种就比较特殊了，走不走索引不是绝对的，跟所查询的数据量跟总表数据量的比例有关。

我们新建一张表：

CREATE TABLE `test` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`name` varchar(50) DEFAULT NULL,

`dept` bigint(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

然后往这张表里插入10条数据：

首先执行以下语句：

SELECT * FROM test WHERE id IN (1)

执行结果：

可以看到走了索引，需要注意的是type，我们知道type表示所走索引的一个效率值，它的结果的好坏依次为：

system