MySQL 索引优化

1.索引分析

单表索引

where后面的查询字段card已经添加了索引idx_class_card

image-20200511032558409

两表索引

left join 左表全都有，右表关联字段添加索引

right join 右表全都要有，左表关联字段添加索引

image-20200511033130132

三表索引

索引要设置在经常查询的字段上

image-20200511034256489

2.索引失效

test03表作为测试表

image-20200511035520836

1. 全值匹配我最爱

   where后面的查询字段与复合索引一一匹配上，c1,c2,c3,c4和复合索引idx_test03_c1234

   image-20200511035725036

2. 最佳左前缀法则

   复合索引的第一个字段一定不能丢失,否则索引失效

   c1是复合索引第一个字段,没有它,type从ref变为ALL，idx_test03_c1234索引失效

   image-20200511040402161

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

   where后面的索引列c1使用了left函数后,idx_test03_c1234索引失效,type从ref变为ALL

   image-20200511040936570

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

   where后面c2>'a2'使用了>,type从ref变为range,从key_len可以看出只有c1,c2有效,c3无效,范围之后索引无效

   image-20200511041256367

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

   select * 的Extra为NULL,而使用具体字段名的Extra则为Using index,使用了覆盖索引(Covering Index)

   覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

   image-20200511041736322

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

   where后面查询字段c2使用了!=或者<>,type从ref变为ALL,key变为NULL,索引失效

   image-20200511042345614

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

8. like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

   where后面的c1使用%1%,type从ref变成ALL,

   c2使用%2%,只有c1索引有效,c2及之后的索引统统失效

   如果把%放后面,索引有效,type从ref变成range

   image-20200511043216418

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

   where之后c1='1',使用varchar类型,使用了索引

   c1=1,mysql优化器会自动根据字段类型(varchar)转为'1',然后进行查找,自动转换使索引失效

   image-20200511043704818

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

    where后面c1='a1' or c2='a2',查询条件使用or连接,type从ref变成ALL,key变为NULL,索引失效

    image-20200511044003254

3.总结

全值匹配我最爱,最左前缀要遵守;

带头大哥不能死,中间兄弟不能断;

索引列上少计算,范围之后全失效;

LIKE百分写最右,覆盖索引不写星;

不等空值还有or,索引失效要少用;

VAR引号不可丢,SQL高级也不难.