MySQL索引失效常见情景

CREATE TABLE `staffs` (
  `id` int(10) NOT NULL,
  `name` varchar(20) NOT NULL,
  `age` int(2) NOT NULL,
  `pos` varchar(20) NOT NULL,
  `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP);CHARSET utf8;
 
 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());INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());

创建索引

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

索引失效 几种常见情景案例

索引失效针对的组合索引？ 若一个字段上有多种索引呢？某一索引失效，可以继续使用其他索引不影响。

全值匹配

索引  idx_staffs_nameAgePos 建立索引时 以 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';

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。1.当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。 既 select后的字段有索引，where 后的字段也有索引，则无关执行顺序。2.除开上述条件 才满足最左前缀法则。

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

没有车头.png

EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

image.png

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

满足最左前缀法则.png

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

EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

image.png

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

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

[图片上传失败...(image-7aee9-1614697987989)]失效

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

范围条件失效.png

**尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select ***

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

[图片上传失败...(image-538088-1614697987990)]

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

explain select * from staffs where name='July';explain select * from staffs where name!='July';

image.png

is not null 也无法使用索引,但是is null是可以使用索引的

explain select * from staffs where name is null;explain select * from staffs where name is not null;

image.png

like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

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

用staffs表做案例,索引：

image.png

执行以下操作：

查询全部

explain select * from staffs where name like '%aa%';

image.png

id查询条件

EXPLAIN SELECT id    FROM tbl_user WHERE NAME LIKE '%aa%';

image.png

NAME查询条件

EXPLAIN SELECT NAME     FROM staffs WHERE NAME LIKE '%aa%';

image.png

id,name查询条件

EXPLAIN SELECT id,NAME FROM staffs WHERE NAME LIKE '%aa%';

image.png

name,age查询条件

explain select name,age from staffs where name like '%aa%';

image.png

id,NAME,age查询条件

EXPLAIN SELECT id,NAME,age FROM staffs WHERE NAME LIKE '%aa%';

image.png

id,NAME,age,add_time查询条件

EXPLAIN SELECT id,NAME,age,add_time FROM staffs WHERE NAME LIKE '%aa%';

image.png

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

没有单引号情况，索引失效

explain select * from staffs where name=2000;

image.png

有单引号用到了索引

explain select * from staffs where name='2000';

image.png

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

explain select * from staffs where name='z3' or name='July';

image.png

小总结

假设index(a,b,c)序号7 左边定值，所以索引都能用到 序号8 左边值不确定

序列号	where语句	索引是否被引用
1	where a=3	Y，使用到a
2	where a=3 and b=5	Y，使用到a，b
3	where a=3 and b=5 and c=4	Y，使用到a，b,，c
4	where b=3 或者 where b=4 and c=5 或者 where c=5	N
5	where a=3 and c=5	使用到a，但是c不可以，b中间断了
6	where a=3 and b>4 and c=5	使用到a,b   c不能作用在范围之后，b断了
7	where a=3 and b like 'kk%' and c=5	a能用，b能用，c能用
8	where a=3 and b like '%kk' and c=5	Y，只用到a
9	where a=3 and b like '%kk%' and c=5	Y，只用到a
10	where a=3 and b like 'k%kk%' and c=5	Y，a,b,c

【优化总结口诀】

全值匹配我最爱，最左前缀要遵守； 带头大哥不能死，中间兄弟不能断； 索引列上少计算，范围之后全失效； LIKE百分写最右，覆盖索引不写星； 不等空值还有or，索引失效要少用；