讲一个索引失效应对措施的案例

建表SQL

create table staffs(
	id int primary key auto_increment,
	name varchar(24) not null default'' comment'姓名',
	age int not null default 0 comment'年龄',
	pos varchar(20) not null defalut'' comment'职位',
	add_time timestamp not null dafault current_timestamp comment'入职时间'
) charset utf8 comment '员工记录表';

insert into staffs(name,age,pos,add_time) values('z3','22','manager',now());
insert into staffs(name,age,pos,add_time) values('july','23','dev',now());
insert into staffs(name,age,pos,add_time) values('2000','23','dev',now());

alter table staffs add index idx_staffs_nameAgePos(name,age,pos);

仔细看我的SQL语句，建立一个员工表staffs，插入了三行数据，建立了索引idx_staffs_nameAgePos(name,age,pos)

不同查询语句下的索引使用情况

1.全值匹配效果最好

索引的三个值按顺序都用上是效果最好的，如图

2.最佳左前缀法则

如果索引了多列，要遵循该法则。该法则指的是查询从索引的最左前列开始并且不跳过索引中的列。

如上面的索引idx_staffs_nameAgePos(name,age,pos)中：

假设跳过了name直接查询，那么是全表扫描，这里不做图示，这种情况下你的索引完全没用到。

假设有name开头，跳过了age，用了pos，即跳过了中间的索引列，那么索引其实只会用到name而已，如图：

图片说明：有没有用到pos字段查询，图中的ref列都只有一个const，如果pos有用上，那么二表的ref列应该有两个const，说明pos没用上索引，同时key_len列也能说明这个问题。

3. 不要在索引列上做任何操作（计算、函数、（自动or手动）类型转换），这样会导致索引失效而转向全表扫描。

如图，left(name,4)='July’是mysql的函数，表示查找name字段左边起4位的值为’July’的结果。

从结果上来说，和上面的select语句查询结果是一致的，但是因为在索引列上做了操作，导致了索引失效。

4.存储引擎不能使用索引中范围条件右边的列。

前后两句SQL语句中，age字段因为变成了范围查询，所以age字段不再在索引里起到检索的作用，而是起到排序的作用，并且pos字段完全失效。

何以见得？

因为首先，如果只用到name字段，那么key_len的值应该为74而非78，下图是三个字段逐步使用时key_len的值：

那么现在key_len的值为78而非74，就说明age字段有在用，索引的作用不只有查找，还有排序，age的查找作用变弱，并且直接影响到后面的pos索引查找失效，那么age起作用的就是排序。

5. 尽量使用覆盖索引

只访问索引的查询，即索引列和查询列一致，减少使用select *。

即，在这个表中最好使用select name,age,pos。这个要根据实际业务来。

6. MySQL在使用不等于（!= 或者 <>）时无法使用索引会导致全表扫描。

注意：这里的<> 就是不等于，它是一个符号，而不是小于和大于两个符号。

7. is null，is not null 也无法使用索引。

8. like以通配符开头（如’%abc…’）mysql索引失效会变成全表扫描的操作。

但是右边可以加%（如’abc%’），这样索引不会失效。

这里会有一个面试题：解决like '%字符串%'时索引不被使用的方法？

答案：使用覆盖索引。select 主键/索引的任意一个字段都行/最好是按索引顺序来（只是最好），在本案例中，索引字段为name,age,pos。

可行的情况：

select id where 哪个字段都行 like '%aa%';  //主键
select age where 哪个字段都行 like '%aa%';  //索引单独第二位第三位也行
select age,pos where 哪个字段都行 like '%aa%';  //索引第二位第三位一起也行
select name where 哪个字段都行 like '%aa%';  //索引第一位肯定行

不行的情况：

//皆为有不是索引的字段
select * where 哪个字段都行 like '%aa%';
select age,add_time where 哪个字段都行 like '%aa%';

9. 字符串不加单引号索引失效。

这个问题的起因是这样的：

MySQL的功能比较强大，在本案例的表中，name字段有一个值是2000，那么在MySQL中，select * from staffs where name='2000'和select * from staffs where name=2000都能查出来数据且结果一样。

但是MySQL底层执行时，name字段本身是varchar类型，后一句select中2000是int类型，mysql自己会做一个转化，用的就是函数，那么参考第3条，索引就失效了。

虽然查询结果一样，但查询效率变低了。

10. 少用or，用它来连接时索引会失效。

小总结

假设有索引index(a,b,c)

where语句	索引使用情况
where a=3	a
where a=3 and b=5	a,b
where a=3 and b=5 and c=4	a,b,c
where b=3或者where b=3 and c=4或者where c=4	没有
where a=3 and c=5	a
where a=3 and b>4 and c=5	a,b
where a=3 and b like ‘kk%’ and c=4	a,b,c
where a=3 and b like ‘%kk’ and c=4	a
where a=3 and b like ‘%kk%’ and c=4	a
where a=3 and b like ‘k%kk%’ and c=4	a,b,c