1.简介

本文主要通过介绍Like索引及常见索引失效情况,以MySQL为例。

2.EXPLAIN关键字

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

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

like不走索引要怎么办 like索引失效怎么办_sql


语句使用:

EXPLAIN select * from user

3.Like索引失效情况

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

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

like不走索引要怎么办 like索引失效怎么办_数据_02


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

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

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

like不走索引要怎么办 like索引失效怎么办_数据_03


使用%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’);

like不走索引要怎么办 like索引失效怎么办_sql_04


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

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

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

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

索引失效,未走索引

like不走索引要怎么办 like索引失效怎么办_数据_05


50w数据查询,耗时0.410s

like不走索引要怎么办 like索引失效怎么办_like不走索引要怎么办_06

2.使用LOCATE函数

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

索引失效,未走索引

like不走索引要怎么办 like索引失效怎么办_mysql_07

50w数据查询,耗时0.403s

like不走索引要怎么办 like索引失效怎么办_数据库_08


3.使用POSITION函数

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

索引失效,未走索引

like不走索引要怎么办 like索引失效怎么办_数据_09

50w数据查询,耗时0.406s

like不走索引要怎么办 like索引失效怎么办_sql_10


4.使用INSTR函数

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

索引失效,未走索引

like不走索引要怎么办 like索引失效怎么办_like不走索引要怎么办_11


50w数据查询,耗时0.411s

like不走索引要怎么办 like索引失效怎么办_数据_12

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

5.常见索引失效情况

1、使用like后面紧跟着%,如’%3’

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

like不走索引要怎么办 like索引失效怎么办_数据_05


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

SELECT count(*) from tb_user_copy1

总条数: 416853

like不走索引要怎么办 like索引失效怎么办_mysql_14

explain SELECT * FROM tb_user_copy1 where age between 1 and 3

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

like不走索引要怎么办 like索引失效怎么办_数据库_15

explain SELECT * FROM tb_user_copy1 where age between 1 and 4

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

like不走索引要怎么办 like索引失效怎么办_mysql_16


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

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

like不走索引要怎么办 like索引失效怎么办_数据_17

3、不满足最左匹配原则

建立了一个name,age联合索引

like不走索引要怎么办 like索引失效怎么办_mysql_18

explain SELECT * FROM tb_user_copy1 where age =4

未走索引,全表扫描

like不走索引要怎么办 like索引失效怎么办_数据库_19

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

走了联合索引

like不走索引要怎么办 like索引失效怎么办_sql_20

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

explain SELECT * FROM tb_user_copy1 where name=123

未走索引,全表扫描

like不走索引要怎么办 like索引失效怎么办_数据_21

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

explain SELECT * FROM tb_user_copy1 where age >=5

全表扫描,未使用索引

like不走索引要怎么办 like索引失效怎么办_like不走索引要怎么办_22


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

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

全表扫描,未使用索引

like不走索引要怎么办 like索引失效怎么办_mysql_23


7.where中索引列有运算

explain SELECT * FROM tb_user_copy1 where age *2 =10

全表扫描,未使用索引

like不走索引要怎么办 like索引失效怎么办_mysql_24

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

explain SELECT * FROM tb_user_copy1 where name is null

is null走了索引

like不走索引要怎么办 like索引失效怎么办_数据库_25

explain SELECT * FROM tb_user_copy1 where name is not null

is not null 未走索引

like不走索引要怎么办 like索引失效怎么办_数据_26

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

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

只有一个age索引情况,未走索引

like不走索引要怎么办 like索引失效怎么办_数据_27

age和name索引都有,走了索引

like不走索引要怎么办 like索引失效怎么办_数据库_28