like使用索引如何避免失效

1、创建表

2、准备数据

3、在name和address上创建索引，不对fav创建索引

3.1.1 使用like模糊查询全部字段的的情况
a、当两边使用%时：

结果是全表扫描，索引失效

b、当左边使用%时：

结果也是全表扫描，索引失效

c、当右边使用%时

结果是range，索引生效

由上述三种情况：当使用like模糊查询所有字段（包括不包含在索引列中的字段）时，只有%放在右边索引才会生效
3.1.2 但我们实际的应用场景中，大部分都是需要两边使用%的。
使用like模糊查询，两边都是用%的情况

a、explain select id from test where name like ‘%三%’，因为id是主键所以有索引，而name也在索引列，所以索引生效，也结果如下：

b、explain select name from test where name like ‘%三%’，因为name 在索引列，所以索引生效，结果如下:

c、explain select address from test where name like ‘三%’,因为address和name都在索引列，所以索引生效。结果如下:

d、explain select id,name from test where name like ‘%三%’,因为name，id都在索引，所以索引生效，结果如下

e、explain id,name,address from test where name like ‘%三%’,因为name，id,address都在索引，所以索引生效,结果如下；

同理可得，只要查询的字段及where条件字段是在索引列的情况下，索引都不会失效。

f、explain select * from test where name like ‘%三%’，因为*包括了fav字段，它不再索引列，所以索引失效，结果如下

g、explain select id,name,address from test where fav like ‘%b%’,因为where条件里的fav不在索引列，所以索引失效，结果如下:

通过上面的例子，我们可以得出，两边都是用%的情况下，当这个作为模糊查询的条件字段（例子中的name）以及所想要查询出来的数据字段（例子中的 id & name & address）都在索引列上时，才能真正使用索引，否则，索引失效全表扫描（比如多了一个 fav 字段）。我想，这应该就是 ‘覆盖索引（索引覆盖）’ 的本质吧。同时，这也能很好的证实 “尽量避免SELECT * 而是一一罗列出所需要查询的字段” 的道理吧，因为，搞不好 SELECT * 就多了一个字段，就导致了全表扫描。

覆盖索引

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
只扫描索引而无需回表的优点：
1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引