1.索引分析
单表索引
where后面的查询字段card已经添加了索引idx_class_card
image-20200511032558409
两表索引
left join 左表全都有，右表关联字段添加索引
right join 右表全都要有，左表关联字段添加索引
image-20200511033130132
三表索引
索引要设置在经常查询的字段上
image-20200511034256489
2.索引失效
test03表作为测试表
image-20200511035520836
-
全值匹配我最爱
where后面的查询字段与复合索引一一匹配上，c1,c2,c3,c4和复合索引idx_test03_c1234
image-20200511035725036
-
最佳左前缀法则
复合索引的第一个字段一定不能丢失,否则索引失效
c1是复合索引第一个字段,没有它,type从ref变为ALL，idx_test03_c1234索引失效
image-20200511040402161
-
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
where后面的索引列c1使用了left函数后,idx_test03_c1234索引失效,type从ref变为ALL
image-20200511040936570
-
存储引擎不能使用索引中范围条件右边的列
where后面c2>'a2'使用了>,type从ref变为range,从key_len可以看出只有c1,c2有效,c3无效,范围之后索引无效
image-20200511041256367
-
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
select * 的Extra为NULL,而使用具体字段名的Extra则为Using index,使用了覆盖索引(Covering Index)
覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
image-20200511041736322
-
mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
where后面查询字段c2使用了!=或者<>,type从ref变为ALL,key变为NULL,索引失效
image-20200511042345614
-
is null,is not null 也无法使用索引
-
like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作
where后面的c1使用%1%,type从ref变成ALL,
c2使用%2%,只有c1索引有效,c2及之后的索引统统失效
如果把%放后面,索引有效,type从ref变成range
image-20200511043216418
-
字符串不加单引号索引失效
where之后c1='1',使用varchar类型,使用了索引
c1=1,mysql优化器会自动根据字段类型(varchar)转为'1',然后进行查找,自动转换使索引失效
image-20200511043704818
-
少用or,用它连接时会索引失效
where后面c1='a1' or c2='a2',查询条件使用or连接,type从ref变成ALL,key变为NULL,索引失效
image-20200511044003254
3.总结
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
VAR引号不可丢,SQL高级也不难.
网友评论