Mysql之索引失效

一、数据准备

建立一个index_test库：CREATE DATABASE index_test;

在index_test库中创建一张user表：

USE index_test;
CREATE TABLE `user`(
    `id` BIGINT,
    `name` VARCHAR(50),
    `age` INT(4),
    `gender` VARCHAR(4),

     'time' datetime
)ENGINE = INNODB;

二、索引失效分类

1、复合索引最好最值匹配

在user上建立复合索引：CREATE INDEX idx_name_age_gender ON `user`(`name`,`age`,`gender`) ;

（1）当使用复合索引顺序查询索引不失效：

EXPLAIN SELECT * FROM USER WHERE NAME='张三' AND age=20 AND gender='male';

（2）使用部分索引查询（索引使用的字段是索引间的顺序）索引不失效

EXPLAIN SELECT * FROM USER WHERE NAME='张三' AND age=20；

（3）使用部分索引查询(使用的字段顺序不是索引建的顺序)，索引失效

EXPLAIN SELECT * FROM `user` WHERE  age=20 AND gender='male';

注意：违背最左前缀原则

（4）使用name和gender，索引不失效，只是使用到部分索引

EXPLAIN SELECT * FROM `user` WHERE NAME='张三' AND gender='male';

注意：复合索引中间不能断

2、不在索引列上做任何操作（计算，函数，类型转换），会导致索引失效

EXPLAIN SELECT * FROM `user` WHERE LEFT(NAME,3)='neo'

3、存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM `user` WHERE NAME='张三' AND age>20 AND gender='male';

4、尽量使用覆盖索引（索引列和查询列一致）不要使用select *

EXPLAIN SELECT NAME,age,gender FROM `user` WHERE NAME='张三' AND age=20 AND gender='male';

EXPLAIN SELECT NAME,age,gender FROM `user` WHERE NAME='张三' AND age>20 AND gender='male';

注意：和上面使用select *相比多出了Using index

5、使用不等于（!=或者<>）时候会导致索引失效

EXPLAIN SELECT * FROM `user` WHERE NAME='张三' AND age!=20 AND gender='male';

6、is null，is not null无法使用索引

EXPLAIN SELECT * FROM `user` WHERE NAME IS NOT NULL

7、like 以通配符开头（‘%...’）索引失效

EXPLAIN SELECT * FROM `user` WHERE NAME LIKE '%张'

EXPLAIN SELECT * FROM `user` WHERE NAME LIKE '张%'

注意：like时%加在右边

如果业务需要使用“%...%”时，查询字段需要和索引一致

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

EXPLAIN SELECT * FROM `user` WHERE NAME=10

9、少用or，用它连接查询时会索引失效

EXPLAIN SELECT * FROM `user` WHERE NAME='张三' OR NAME=‘10’

三、总结练习