既要Like %x%，又要索引，是否可以兼得？全表扫描 or 全索引扫描？

本文有两个目的

1. 我有一个需求，根据输入的关键词查询垃圾的类别，只是一个小需求，不值得使用ES。如何利用like进行模糊查询，又如何建立索引呢？目前的思路，新增一个revname字段，存放name的反转字符串，在查询的时候使用两次like 'x%'。
2. like '%x%'是否一定不走索引，mysql的优化器是如何选择的？

一、准备工作

1.1 数据表

这是一张垃圾信息分类表，一共有四个字段，分别为：id、name、revname和type，其中revname是name的反转。

部分内容

1.2 建立索引

create index name_search on lajiinfokeyword(name);
create index revname_search on lajiinfokeyword(revname);

二、实验

2.1 实验一

explain select name,type from lajiinfokeyword where name like '%手机%';
explain select name,type from lajiinfokeyword where name like '手机%';
explain select name,type from lajiinfokeyword where revname like '机手%';

利用explain，从上表中可以看出，在查询name和type时，如果是%在关键词前面是无法走索引的。

后两个语句，分别走了name_search和revname_search，这是在意料之内的。

type为ALL，表示全表扫描，type为range，表示范围查询，说明利用了索引有序的特点。

由于数据比较少，看不出索引的优势。

2.2 实验二

与实验一相比，实验二只查询name，不查询type。

explain select name from lajiinfokeyword where name like '%手机%';
explain select name from lajiinfokeyword where name like '手机%';
explain select name from lajiinfokeyword where revname like '机手%';

可以发现表里面有很多地方不一样了。

1. 第一个查询居然也用索引了，不过type是index，这是说明走的是全索引扫描，为什么实验一就是全表扫描呢？
2. Extra里面增加了use index，说明使用的是覆盖索引，不需要回表查询了。

三、总结

1. like '%x%'是否一定不走索引？

答：不一定，如果要查询的字段，本身就是索引列，那么mysql优化器会选择全索引扫描，并且还是覆盖索引。如果要查询的结果，还包括其他字段，那么优化器选择走全表扫描。

2. 优化器为什么这样选择？

答：因为全索引扫描并没有利用上索引有序的特性。它和全表扫描的区别就在于数据量的大小。很明显全索引扫描的数据量更少，加载更快。

但是，如果要查询的不仅仅是索引列，还包括其他列，那么就无法使用覆盖索引，后面还需要进行回表查询。

对于优化器来说，还不如直接全表扫描呢。

总的来说，能用上覆盖索引就全索引扫描，用不上就全表扫描。

3. 利用两个后缀模糊查询。