聊聊索引失效的经典场景

前置条件

数据库版本为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

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

我们新建一张表：

CREATETABLE `test` (
  `id` bigint(20)NOTNULL 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 > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

一般来说，至少要达到range级别，当type=index或者ALL时，表示效率低下，需要优化。我们看到只查询一条记录时，不但走了索引，而且type=const，效率较高。我们扩大下范围：

SELECT*FROM test WHERE id IN(1,2,3)

从执行结果上看，也走了索引，但此时type=range，效率降低了。再扩大下范围：

SELECT*FROM test WHERE id IN(1,2,3,4,5)

可以看到没有走索引了，type=ALL，全表扫描。

结论：mysql优化器会根据所查询的数据量决定是走索引还是全表扫描。

mysql选错索引

我们再建一个demo表来说明这种情况：

CREATETABLE `demo` (
  `id` int(11)NOTNULL,
  `a` int(11)NOTNULL default 0,
  `b` int(11)NOTNULL default 0,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

然后插入100w条数据，执行以下sql:

select*from demo where(a between1000and2000)and(b between50000and100000)limit1

看一下执行情况：

可以看到走了索引a，且只扫描了1001行，其实这正是我们需要的。

但如果我们加个排序，变成这样：

SELECT*FROM demo WHERE(a BETWEEN1000AND2000)AND(b BETWEEN50000AND100000)ORDERBY b LIMIT1

再看下执行情况：

可以看到走了索引b，并且扫描了5w多行数据，这样效率显然会降低，为什么会走索引b呢?

因为在有多个索引的情况下，mysql优化器一般会通过比较扫描行数、是否需要临时表以及是否需要排序等，来作为选择索引的判断依据。在这个例子中，优化器看到根据b来进行排序，认为使用b效率更高，所以走了索引b。实际上，我们应该使用a索引。

这种情况可以使用force index来强制使用索引a。

SELECT*FROM demo FORCE INDEX(a)WHERE(a BETWEEN1000AND2000)AND(b BETWEEN50000AND100000)ORDERBY b LIMIT1;

可以看到，查询走了索引a，并且只扫描了1001行。