• 索引使用及注意事项


    1.全值匹配

    explain select * from employees where name = 'Lucy';

    img

    ref为const

    2.最左前缀法则

    当索引是组合索引时,需遵守最左前缀法则,即查询组合索引中的列时,从最左侧开始不能跳过列,否则索引会失效

    explain select * from employees where name = 'Lucy' and age=23 and position='dev';
    explain select * from employees where name = 'Lucy' and age=23;
    explain select * from employees where name = 'Lucy';
    explain select * from employees where age=23;
    explain select * from employees where position='dev';
    explain select * from employees where age=23 and position='dev';

    只有前三种是符合法则的,走了索引

    img

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

    explain select * from employees where left(name,2) = 'Lu';

    img

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

    组合索引时,比如有三个字段组合

    explain select * from employees where name = 'Lucy' and age>23 and position='dev';

    img

    从key_len可以看出,全部字段使用时是140,目前只有78,得知postion没有走索引。

    mysql5.6版本之前没有加入index condition pushdown,所以索引逻辑还是这样的:

    即便对于复合索引,从第一列开始先确定第一列索引范围,如果范围带=号,则对于=号情况,确定第二列索引范围加入索引结果集里,每列的处理方式都是一样的。

    确定完索引范围后,则回表查询数据,再用剩下的where条件进行过滤判断。

    mysql5.6后加入了ICP,对于确定完了索引范围后,会用剩下的where条件对索引范围再进行一次过滤,然后再回表,再用剩下的where条件进行过滤判断。(减少回表记录数量)。

    5.尽量使用覆盖索引

    (只访问索引的查询(索引列包含查询列)),减少 select * 语句。select的字段是索引所相关的字段,不然无法是覆盖索引,只是const级别的查询而已。

    6.mysql在使用不等于(!=或者<>),not in ,not exists 的时候无法使用索引会导致全表扫描

    < 小于、 > 大于、 <=、>= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

    explain select * from employees where name != 'Lucy' ;

    img

    7.is null 、 is not null 一般也会使索引失效

    explain select * from employees where age is not null ;

    img

    8.字符串不添加单引号也会使索引失效

    explain select * from employees where name = 20;

    img

     

    9.模糊查询-like 用通配符开头('%xxx...')会导致索引失效

    explain select * from employees where name like '%Lu';

    img

    explain select * from employees where name like '%Lu%';

    img

    前后都是通配符时,索引还是失效,需要使用覆盖索引,即select字段是索引相关字段。

    explain select name,age from employees where name like '%Lu%';

    img

    10.查询使用or或者in时

    不一定会走索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

     

    11.查询使用范围查询时会失效

    explain select * from employees where age >1 and age <=1000;

    img

    这里可能是数据量过大或者过小的情况下,直接不走索引,如果数据量太大可以优化范围查询(即分页)

    如:

    explain select * from employees where age >1 and age <=500;

    12.组合索引常见示例

    组合索引index_a_b_c(a,b,c)

    where子句

    索引是否有效

    a=3

    是,使用了最左字段a

    a=3 and b=4

    是,使用了a、b

    a=3 and b=4 and c=5

    是,都使用了

    b=4 and c=5或者b=4或者c=5

    否,没使用最左字段a

    a=3 and c=5

    a有用,c没用,b被中断了

    a=3 and b>4 and c=5

    a、b有用,c不在范围内,b是范围查找,中断了

    a=3 and b like 'AA%' and c=5

    是,都使用了

    a=3 and b like '%AA' and c=5

    是,使用了a

    a=3 and b like '%AA%' and c=5

    是,使用了a

    a=3 and b like 'A%AA%' and c=5

    是,都使用了

    like AA%相当于=常量,%AA和%AA% 相当于范围 

     

    我始终记住:青春是美丽的东西,而且对我来说,它永远是鼓舞的源泉。——(现代)巴金
  • 相关阅读:
    重磅!容器集群监控利器 阿里云Prometheus 正式免费公测
    阿里开源 KT Connnect,轻量级云原生测试环境治理平台来啦!
    使用Velero Restic快速完成云原生应用迁移至ACK集群
    Kubernetes 弹性伸缩全场景解析 (一):概念延伸与组件布局
    北京DAY1下午
    洛谷 P3041 [USACO12JAN] Video Game Combos
    Tyvj 1729 文艺平衡树
    bzoj 3238: [AHOI2013]差异
    bzoj 2957: 楼房重建
    bzoj 2660: [Beijing wc2012]最多的方案
  • 原文地址:https://www.cnblogs.com/flyinglion/p/15017994.html
Copyright © 2020-2023  润新知