nosqlmysql高级篇

mysql索引失效

本文字数: 1.1k 阅读时长: 4 分钟
阅读次数次

口诀orz

  • 1.全值匹配我最爱
  • 2.最佳左前缀法则
  • 3.带头大哥不能死
  • 4.中间兄弟不能断
  • 5.索引列上不计算
  • 6.范围之后全失效
  • 7.百分like加右边
  • 8.字符串里有引号

索引失效的情况

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

    1
    2
    explain select * from staffs where name=’july’;
    explain select * from staffs where left(name,4)=’july’;

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

    1
    2
    explain select * from staffs where name=’July’ and age > 25
    and pos=’manager’;

    各种范围：in like <> >= <= between之类的范围之后全失效
    解决方法：把范围值建在索引的最后一个。

  • 3.mysql在使用不等于（!=或<>）的时候无法使用索引

    1
    explain select * from staffs where name!=’july’;

    实际上mysql8.0.19用到了这些。

  • 4.is null, is not null也无法使用索引
    实际上mysql8.0.19 is not null 可以使用index。

  • 5.like以通配符开头（’%abc…’）mysql索引会失效变成全表

    1
    select * from staffs where name like ‘%July’;

    全表扫描type = all

    1
    selec * from staffs where name like ‘July%’;

    type = range

    • 1.一般写like查询只能在右边写%，否则为全表扫描。
      1
      2
      3
      4
      5
      index(c1, c2, c3)
      explain select * from test where c1=’a1’ and c2 like ‘kk%’ and c3=’a3’;
      这时候用到了c1, c2, c3
      explain select * from test where c1=’a1’ and c2 like ‘%kk%’ and c3=’a3’;
      这时候只用到了c1
    • 2.当我们非用%%两个%，如何解决索引失效？
      使用覆盖索引（复合索引）
      查的字段和复合字段顺序和名称一致

  • 6.字符串不加单引号索引失效
    explain select * from staffs where name=2000;
    mysql底层隐式类型转换，参考第3条，不要在索引列上做任何操作无论自动还是手动的。
    explain select * from staffs where name=’2000’;

  • 7.少用or，用它来链接是会索引失效

5.0不能使用索引 type为all
8.0以上type 为range

索引优化相关问题

  • 1.当我们建立三个联合索引时

    1
    2
    explain select * from staffs where name=’’ and pos=’’ and age=’’;
    explain select * from staffs where name=’’ and pos=’’ and age=’’;

  • 这些写法顺序不会影响执行，为什么呢？
    这是因为mysql的CBO cost based optimizer查询优化器会对mysql的命令进行调整以达到最佳的效果。
    但建议最好用按顺序的。

  • 2.有order by的情况 index(name, age, pos)

    1
    explain select * from staffs where name=’’ and age=’’ order by pos;

    这时候可以使用两个索引，索引pos用来排序未用来查找。

    1
    explain select * from staffs where name=’’  order by age，pos;

    这里用了一个索引，并且没有filesort，因为age和pos可以联合用于排序。

    1
    explain select * from staffs where name=’’  order by pos，age;

    当没有按照复合索引的顺序时，出现file sort。

    1
    explain select * from staffs where name=’’ and age=’’ order by pos，age;

    这个时候由于age已经未定值了，虽然orderby违反了顺序，也并不会产生filesort，默认age在orderby后无效

  • 3.group by的情况index(name, age, pos)

    1
    explain select * from staffs where name=’’ group by age, pos;

    这时的顺序是符合索引的所以很优质

    1
    explain select * from staffs where name=’’ group by pos, age;

    这时的extras会有：temporary, filesort恐怖如斯

  • 4.总结
    分组之前必排序，定值、范围还是排序，一般order by是给个范围
    group by基本上都需要进行排序可能有临时表。

  • 5.尽量使用覆盖索引

    1
    2
    explain select * from staffs where name=’’ and age>’’ and pos=’’
    explain name,age,pos from staffs where name=’’ and age>’’ and pos=’’

一般性建议

  • 1.对于单值索引，尽量选择对当前query过滤性更好的索引
  • 2.在选择组合索引的时候尽量选择可以能够包含当前query中的where字句中更多
    字段的索引，尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

口诀首尾呼应

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

分享到