索引知识一：索引失效的情况

1.简介
常见索引失效情况，以MySQL为例。

失效原因：

2.EXPLAIN关键字
一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划。

EXPLAIN 语句 就可以看到某个查询数据的执行计划，以下是执行计划的各个参数：

语句使用：

EXPLAIN select * from user

3.Like索引失效情况
1. 使用 3%进行查询索引不会失效

explain SELECT * from tb_user where page like ‘3%’

可以看到可以使用的索引是page，实际使用索引page。

2. 使用 %3进行查询索引会失效

explain SELECT * from tb_user where page like ‘%3’

使用%3查询走的是全表扫描，并没有用到page索引。

解决办法
1.创建倒叙索引，reverse（）

reverse（）应用场景，比如你根据身份证查询一个市洲的人员信息。众所周知，同一个市洲大部分人员身份证前6位是相同的，比如黄石，前六位420222。这个时候你就可以通过reverse（）字段，创建身份证后6位的前缀索引进行查询。

CREATE INDEX pagereverse ON tb_user (reverse(page ));

2.修改SQL语句

select page from tb_user where reverse(page ) like reverse(‘%3’);

没有进行全表扫描，使用并走了pagereverse 索引

3. 使用%3% 进行查询索引会失效

1. like concat(‘%’, ‘3’, ‘%’)

explain SELECT * from tb_user where page like concat(‘%’, ‘3’, ‘%’)

索引失效，未走索引

50w数据查询，耗时0.410s

2.使用LOCATE函数

explain SELECT * from tb_user where LOCATE(‘3’, page)>0

索引失效，未走索引
50w数据查询，耗时0.403s

3.使用POSITION函数

explain SELECT * from tb_user where POSITION(‘3’ in page)>0

索引失效，未走索引

50w数据查询，耗时0.406s

4.使用INSTR函数

explain SELECT * from tb_user where INSTR(page,‘3’)>0

索引失效，未走索引

50w数据查询，耗时0.411s

个人觉得前期如果数据量不大，其实是可以用like的。毕竟其实在少量数据的情况下他们的查询效率都差不多。目前还没有测试过千万数据或者亿及数据他们的效率是怎样。

5.常见索引失效情况
1、使用like后面紧跟着%，如’%3’

explain SELECT * from tb_user where page like concat(‘%’, ‘3’,‘%’)

2、查询数据占总数据 30% 则MYSQL不会再使用索引。因为使用索引的开销反而更大。

SELECT count(*) from tb_user_copy1

总条数： 416853

explain SELECT * FROM tb_user_copy1 where age between 1 and 3

rows预计扫描行数96168，占比百分之23 走索引

explain SELECT * FROM tb_user_copy1 where age between 1 and 4

rows预计扫描行数超过百分之30 ，未走索引

也可以使用 force index(age) 让MySQL强行使用索引查询

explain SELECT * FROM tb_user_copy1 force index(age) where age BETWEEN 1 and 4

3、不满足最左匹配原则
建立了一个name，age联合索引

explain SELECT * FROM tb_user_copy1 where age =4

未走索引，全表扫描

explain SELECT * FROM tb_user_copy1 where name LIKE ‘w%’ and age =4

走了联合索引

4、字符串不加单引号索引会失效

explain SELECT * FROM tb_user_copy1 where name=123

未走索引，全表扫描

5、mysql使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效

explain SELECT * FROM tb_user_copy1 where age >=5

全表扫描，未使用索引

6.where 子句里对有索引列使用函数，用不上索引

explain SELECT * FROM tb_user_copy1 where ABS(age) =5

全表扫描，未使用索引

7.where中索引列有运算

explain SELECT * FROM tb_user_copy1 where age *2 =10

全表扫描，未使用索引

8、is null可以走索引，is not null无法使用索引

is null走了索引
explain SELECT * FROM tb_user_copy1 where name is not null

is not null 未走索引

9、条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

explain SELECT * FROM tb_user_copy1 where name = “G1” or age =8

只有一个age索引情况，未走索引
age和name索引都有，走了索引