【MySQL系列】- LIKE查询 以%开头一定会让索引失效吗

【MySQL系列】- LIKE查询 以%开头一定会让索引失效吗

MySQL查询导致索引失效的情况有很多，通过搜索引擎搜索MySQL索引失效 几乎都会提到一个情况，那就是like查询以“%”开头。但是 LIKE查询 以%开头一定会让索引失效吗？

验证准备

创建一张users表，表结构如下，表中有999999条数据：

在name字段上创建索引

验证阶段

要验证一段SQL语句有没有使用到索引，最好的方法莫过于使用SQL执行计划EXPLAIN了。

首先先看一下LIKE查询不以%开头的情况：

EXPLAIN SELECT * FROM users WHERE `name` LIKE '001%';

type=range,key=idx_name，说明这段是使用了索引的。

接着再看一下LIKE查询不以%开头的情况先看第一段SQL语句：

EXPLAIN SELECT * FROM users WHERE `name` LIKE '%001%'

type=ALL，所以这段SQL是没有使用索引的。

再来看第二段SQL语句：

EXPLAIN SELECT `name` FROM users WHERE `name` LIKE '%001%';

type=index，key=idx_name，这个结果说明这段SQL是使用了索引的。

接着看第三段SQL语句：

EXPLAIN SELECT id , `name` FROM users WHERE `name` LIKE '%001%';

第三段SQL语句是在第二段SQL语句的基础上多查询一列id。这个执行结果和第二段是一样的，说明这段也是使用了索引的。

最后看一下第四段SQL语句：

EXPLAIN SELECT id , `name` , gender FROM users WHERE `name` LIKE '%001%';

第四段SQL语句是在第三段SQL语句的基础上多查询两列id和gender。执行结果和第一段一样，是没有使用索引的。

验证结果

通过以上验证可以发现，LIKE查询 以%开头不一定会让索引失效。

为什么会走索引呢？

首先解释一下，执行计划中type=ALL和index。还有key的含义。

type=ALL：全表扫描，遍历整张表去查询匹配的结果，不走索引。type=index：使用索引覆盖，仅仅扫描索引树，比ALL要快。type=range：使用索引进行范围查询时就会用到range访问方法。key：实际使用到的索引，如果为NULL就是没使用索引。

LIKE查询以%开头使用了索引的原因就是使用了索引覆盖。 针对二级索引MySQL提供了一个优化技术。即从辅助索引中就可以得到查询的记录，就不需要回表再根据聚集索引查询一次完整记录。使用索引覆盖的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作，但是前提是要查询的所有列必须都加了索引。

LIKE查询以%开头会导致全索引扫描或者全表扫描，如果没有索引覆盖的话，查询到的数据会回表，多了一次IO操作，当MySQL预估全表扫描或全索引扫描的时间比走索引花费的时间更少时，就不会走索引。有了索引覆盖就不需要回表了，减少了IO操作，花费的时间更少，所以就使用了索引。

总结

LIKE查询 以%开头不一定会让索引失效。如果查询的结果中只包含主键和索引字段则会使用索引，反之则不会。

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