建表
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';
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';
EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age = 23 AND pos = 'dev';
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';
存储引擎不能使用索引中范围条件右边的列
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';
**尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少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';
is not null 也无法使用索引,但是is null是可以使用索引的
explain select * from staffs where name is null;explain select * from staffs where name is not null;
like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
问题：解决like '%字符串%'时索引不被使用的方法？
用staffs表做案例,索引：
执行以下操作：
- 查询全部
explain select * from staffs where name like '%aa%';
- id查询条件
EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
- NAME查询条件
EXPLAIN SELECT NAME FROM staffs WHERE NAME LIKE '%aa%';
- id,name查询条件
EXPLAIN SELECT id,NAME FROM staffs WHERE NAME LIKE '%aa%';
- name,age查询条件
explain select name,age from staffs where name like '%aa%';
- id,NAME,age查询条件
EXPLAIN SELECT id,NAME,age FROM staffs WHERE NAME LIKE '%aa%';
- id,NAME,age,add_time查询条件
EXPLAIN SELECT id,NAME,age,add_time FROM staffs WHERE NAME LIKE '%aa%';
字符串不加单引号索引失效
没有单引号情况，索引失效
explain select * from staffs where name=2000;
有单引号用到了索引
explain select * from staffs where name='2000';
少用or,用它来连接时会索引失效
explain select * from staffs where name='z3' or name='July';
小总结
假设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，索引失效要少用；
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69989885/viewspace-2760831/ ，如需转载，请注明出处，否则将追究法律责任。
