聊聊索引失效的经典场景

聊聊索引失效的经典场景

前置条件

数据库版本为5.7.17。

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

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

最左匹配原则

首先执行以下几个sql：

发现都走了索引。

接下来再执行几个sql：

发现没有走索引。

索引列上有计算

根据主键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 > 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表来说明这种情况：

CREATE TABLE `demo` ( `id` int(11) NOT NULL, `a` int(11) NOT NULL default 0, `b` int(11) NOT NULL default 0, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`)) ENGINE=InnoDB;

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

select * from demo where (a between 1000 and 2000) and (b between 50000 and 100000) limit 1

看一下执行情况：

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

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

SELECT * FROM demo WHERE (a BETWEEN 1000 AND 2000) AND (b BETWEEN 50000 AND 100000) ORDER BY b LIMIT 1

再看下执行情况：

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

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

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

SELECT * FROM demo FORCE INDEX(a) WHERE (a BETWEEN 1000 AND 2000) AND (b BETWEEN 50000 AND 100000) ORDER BY b LIMIT 1;

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。