1.索引失效（应该避免）

数据

CREATE TABLE staffs(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
age INT(11) NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT 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());

创建索引

CREATE INDEX index_nap ON staffs(name,age,pos) 

索引失效案例 

1.全值匹配我最爱（个数及顺序）

查看索引
SHOW INDEX FROM staffs

索引index_nap 顺序 Seq_in_index
name   age    pos  

EXPLAIN SELECT * FROM staffs WHERE `name` = 'July';
EXPLAIN SELECT * FROM staffs WHERE `name` = 'July' AND age=25;
EXPLAIN SELECT * FROM staffs WHERE `name` = 'July' AND age=25 AND pos = 'dev';

从age开始查询
EXPLAIN SELECT * FROM staffs WHERE  age=25 AND pos = 'dev';

没有用到索引 全表扫描

从pos开始查询
EXPLAIN SELECT * FROM staffs WHERE   pos = 'dev';

没有用到索引 全表扫描

只查询name
EXPLAIN SELECT * FROM staffs WHERE `name` = 'July';

2.**最左前缀法则**

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

口诀：带头大哥不能死，中间兄弟不能断

跳过中间索引列 
EXPLAIN SELECT * FROM staffs WHERE `name` = 'July'  AND pos = 'dev';

 结论：只用到了一部分索引，且只用到了第一个name列的索引

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

对索引第一列name使用mysql函数

EXPLAIN SELECT * FROM staffs WHERE LEFT(`name`,4)  = 'July';

结论：在索引列上使用了函数，转向了全表扫描

口诀：索引列上少计算

4.存储引擎不能使用索引中范围条件右边的列（like属于范围查询）

age为范围查询

EXPLAIN SELECT * FROM staffs WHERE `name` = 'July' AND age>25 AND pos = 'dev';

结论：name是检索，age是一个范围，主要用于排序，age之后不是精确检索

口诀：范围之后全失效

5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

不使用* 而是使用索引上的字段
EXPLAIN SELECT `name`,age,pos FROM staffs WHERE `name` = 'July' AND age=25 AND pos = 'dev';

age为范围查找

EXPLAIN SELECT `name`,age,pos FROM staffs WHERE `name` = 'July' AND age>25 AND pos = 'dev';

与之前的select * 对比

EXPLAIN SELECT * FROM staffs WHERE `name` = 'July' AND age>25 AND pos = 'dev';

截图按sql顺序排列

EXPLAIN SELECT `name` FROM staffs WHERE `name` = 'July' AND age=25 AND pos = 'dev';

EXPLAIN SELECT age FROM staffs WHERE `name` = 'July' AND age=25 AND pos = 'dev';

EXPLAIN SELECT pos FROM staffs WHERE `name` = 'July' AND age=25 AND pos = 'dev';

只查询其中一个也可以，截图如下 三个sql一样

结论：使用覆盖索引会从索引中直接查询到数据

口诀：覆盖索引查询好（自己想的）

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

EXPLAIN SELECT * FROM staffs WHERE `name` = 'July' AND age!=25 AND pos = 'dev';
EXPLAIN SELECT * FROM staffs WHERE `name` = 'July' AND age<>25 AND pos = 'dev';

EXPLAIN SELECT `name`,age,pos FROM staffs WHERE `name` = 'July' AND age!=25 AND pos = 'dev';
EXPLAIN SELECT `name`,age,pos FROM staffs WHERE `name` = 'July' AND age<>25 AND pos = 'dev';

EXPLAIN SELECT * FROM staffs WHERE `name` = 'July' AND age=25 AND pos != 'dev';

EXPLAIN SELECT `name`,age,pos FROM staffs WHERE `name` = 'July' AND age=25 AND pos != 'dev';

 结论：使用*查询 !=和<>，全部索引失效， 使用覆盖索引查询之后 !=和<>之后的索引失效

口诀：

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

EXPLAIN SELECT * FROM staffs WHERE `name` IS NULL ;

EXPLAIN SELECT * FROM staffs WHERE `name` IS NOT NULL ;

对比是否使用了覆盖索引

EXPLAIN SELECT `name`,age,pos,add_time FROM staffs WHERE `name` IS NOT NULL ;

EXPLAIN SELECT `name`,age,pos FROM staffs WHERE `name` IS NOT NULL ;

结论：注意覆盖索引，不使用覆盖索引前提下都会失效

8.like 以通配符开头('%abc')mysql索引失效会变成全表扫描的操作 （like 也相当于范围查询）

 MySQL 5.7.33版本LIKE是范围查询，可以自己测试一下

EXPLAIN SELECT * FROM staffs WHERE `name` LIKE '%July%'

EXPLAIN SELECT * FROM staffs WHERE `name` LIKE '%July'

EXPLAIN SELECT * FROM staffs WHERE `name` LIKE 'July%'

口诀：百分like加右边

问题：解决like '%字符串%'时索引不被使用的方法？？（覆盖索引）

数据

CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8


INSERT INTO tbl_user(`name`,age,email)VALUES('1aa1','21','b@163.com');
INSERT INTO tbl_user(`name`,age,email)VALUES('2aa2','222','a@163.com');
INSERT INTO tbl_user(`name`,age,email)VALUES('3aa3','265','c@163.com');
INSERT INTO tbl_user(`name`,age,email)VALUES('4aa4','21','d@163.com');
INSERT INTO tbl_user(`name`,age,email)VALUES('aa','121','e@163.com');

未建立索引

EXPLAIN SELECT * FROM tbl_user WHERE `name` LIKE '%aa%'
EXPLAIN SELECT id,`name`,age,email FROM tbl_user WHERE `name` LIKE '%aa%'
EXPLAIN SELECT id,`name`,age FROM tbl_user WHERE `name` LIKE '%aa%'
EXPLAIN SELECT `name`,age FROM tbl_user WHERE `name` LIKE '%aa%'
EXPLAIN SELECT `name` FROM tbl_user WHERE `name` LIKE '%aa%'
EXPLAIN SELECT age FROM tbl_user WHERE `name` LIKE '%aa%'
EXPLAIN SELECT id FROM tbl_user WHERE `name` LIKE '%aa%'

统统都是下面的全表扫描

 name与age字段创建联合索引

CREATE INDEX index_na ON tbl_user(`name`,age)

查看之前的查询

EXPLAIN SELECT id,`name`,age FROM tbl_user WHERE `name` LIKE '%aa%'
EXPLAIN SELECT `name`,age FROM tbl_user WHERE `name` LIKE '%aa%'
EXPLAIN SELECT `name` FROM tbl_user WHERE `name` LIKE '%aa%'
EXPLAIN SELECT  age FROM tbl_user WHERE `name` LIKE '%aa%'
EXPLAIN SELECT     id FROM tbl_user WHERE `name` LIKE '%aa%'
覆盖索引上面EXPLAIN都是下面的情况

EXPLAIN SELECT * FROM tbl_user WHERE `name` LIKE '%aa%'
EXPLAIN SELECT id,`name`,age,email FROM tbl_user WHERE `name` LIKE '%aa%'
无法使用覆盖索引之后 都是全表扫描

 结论：覆盖索引来解决like '%字符串%'时索引不被使用的情况

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

EXPLAIN SELECT * FROM staffs WHERE `name`=2000 

EXPLAIN SELECT * FROM staffs WHERE `name`='2000'

 **结论：千万不要用隐形的类型转换 会导致索引失效

口诀：字符串里有引号

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

EXPLAIN SELECT * FROM  staffs WHERE `name`='July' or `name`='z3'

***口诀

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

Like百分写最右，覆盖索引不写星；