概述

定义

没用上索引,用explain查看sql,key这一栏为NULL

示例表结构

规则

全值匹配我最爱
最佳左前缀法则：如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
1. 带头大哥不能死,中间兄弟不能断
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描(mysql8就可以了)
is null，is not null 也无法使用索引（早期版本不能走索引，后续版本应该优化过，可以走索引）
1. c
2. 结论:字段尽量不要设置为允许null,可以设置一个默认值-1这样的

like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作

硬要用左边咋办;答案:索引覆盖

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 SELECT id, name FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age FROM tbl_user WHERE NAME LIKE '%aa%';
索引生效

EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';
索引失效

小结:索引覆盖是一种兜底方法吧,隐藏信息id也加入到索引中去了!

字符串不加单引号索引失效
1. 相当于就是3，函数计算后失效了
少用or，用它连接时会索引失效（8.0好像是可以的）
1. 总结
  带头大哥不能死，中间兄弟不能断；
  索引列上少计算，范围之后全失效；
  Like百分写最右，覆盖索引不写星；
  不等空值还有or，索引失效要少用；
  VAR引号不可丢，SQL高级也不难！
  题目
  
  like 是否是常量开头
  面试分析

select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4' and c5='a5';
select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
select * from test03 where c4='a1' and c2='a2' and c3='a3' and c1='a1'; # 顺序乱了，mysql自动优化
select * from test03 where c1='a1' and c2='a2' and c3='a3';
select * from test03 where c1='a1' and c2='a2' ;
select * from test03 where c1='a1'  ;
select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4'; # 范围以后失效
select * from test03 where c1='a1' and c2='a2' and c4>'a3' and c3='a4'; # 自动优化
select * from test03 where c1='a1' and c2='a2' and c4>'a4' ORDER BY c3; # 只用了2个 c3用于排序而不是查找，没有显示出来
select * from test03 where c1='a1' and c2='a2' ORDER BY c3; # 查询2个，排序1个c3
select * from test03 where c1='a1' and c2='a2' ORDER BY c4; # 查询2个，排序0个c4
select * from test03 where c1='a1' and c5='a5' ORDER BY c2,c3;# 查询1个，排序2个
select * from test03 where c1='a1' and c5='a5' ORDER BY c3,c2;# 查询1个，排序0个
select * from test03 where c1='a1' and c2='a2' ORDER BY c2,c3;# 查询2个，排序1个
select * from test03 where c1='a1' and c2='a2' and c5='a5' ORDER BY c2,c3;# 查询2个，排序1个 不影响 order中的和where里面一样的 其实order field没用，可以干掉
select * from test03 where c1='a1' and c2='a2' and c5='a5' ORDER BY c3,c2;# 查询2个，排序1个 不影响
select * from test03 where c1='a1' and c2='a2' and c5='a5' ORDER BY c3,c2;# 查询2个，排序1个 不影响
select * from test03 where c1='a1' and c4='a4' ORDER BY c2,c3;# 查询1个 有排序
select * from test03 where c1='a1' and c4='a4' ORDER BY c3,c2;# 查询1个 没排序filesort
# group by 排序产生临时表，搬家 类似order by 多了

java

6.索引失效

概述

定义

示例表结构

规则

总结

题目

面试分析