• SQL查询优化---单表使用索引及常见索引失效优化


    如何避免索引失效

    1、全值匹配

    系统中经常出现的sql语句如下:

     EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30  
     EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
     EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'  
    
    • 1
    • 2
    • 3

    优化后

    CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME)
    
    • 1

    建立索引前
    在这里插入图片描述

    索引后
    在这里插入图片描述

    2、最佳左前缀法则

    如果系统经常出现的sql如下:

     EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30   AND emp.name = 'abcd'   
    
    • 1

    或者

    EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1   AND emp.name = 'abcd'   
    
    • 1

    那原来的idx_age_deptid_name 还能否正常使用?
    如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

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

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

    如果系统经常出现的sql如下:

     EXPLAIN SELECT  SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ; 
    
    • 1

    那么索引 idx_age_deptid_name这个索引还能正常使用么?
    在这里插入图片描述

    如果这种sql 出现较多
    应该建立:

    create index idx_age_name_deptid on emp(age,name,deptid)
    
    • 1

    效果
    在这里插入图片描述

    # drop index idx_age_name_deptid on emp
    
    • 1

    5、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

     CREATE INDEX idx_name ON emp(NAME)
      EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name <>  'abc' 
    
    • 1
    • 2

    在这里插入图片描述

    6、is not null 也无法使用索引,但是is null是可以使用索引的

      UPDATE emp SET age =NULL WHERE id=123456;
       下列哪个sql语句可以用到索引
      EXPLAIN SELECT * FROM emp WHERE age IS NULL
      EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL
    
    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

    7、like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作

    在这里插入图片描述

    8、字符串不加单引号索引失效

    在这里插入图片描述

    9、总结

    假设index(a,b,c)
          														
    where a = 3			索引是否被使用:Y,使用到a
    where a = 3 and b = 5						索引是否被使用:Y,使用到a,b
    where a = 3 and b = 5 and c = 4										索引是否被使用:Y,使用到a,b,c
    where b = 3 或者 where b = 3 and c = 4  或者 where c = 4		索引是否被使用:N
    where a = 3 and c = 5	     索引是否被使用:使用到a, 但是c不可以,b中间断了
    where a = 3 and b > 4 and c = 5	 索引是否被使用:使用到a和b, c不能用在范围之后,b断了
    where a is null and b is not null  	 索引是否被使用: is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是  b不可以使用
    where a <> 3   	 索引是否被使用:不能使用索引
    where   abs(a) =3	索引是否被使用:不能使用 索引
    where a = 3 and b like 'kk%' and c = 4	索引是否被使用:Y,使用到a,b,c
    where a = 3 and b like '%kk' and c = 4	索引是否被使用:Y,只用到a
    where a = 3 and b like '%kk%' and c = 4	索引是否被使用:Y,只用到a
    where a = 3 and b like 'k%kk%' and c = 4	索引是否被使用:Y,使用到a,b,c
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
  • 相关阅读:
    入门力扣自学笔记199 C++ (题目编号:791)
    MySQL版数据库原理与应用期末复习重点(1)---关系代数(除运算和自连接查询、手写例题)
    算法分享三个方面学习方法(做题经验,代码编写经验,比赛经验)
    python算法
    SolidWorks如何绘制环形波纹垫片
    【wiki知识库】09.欢迎页面展示(浏览量统计)SpringBoot部分
    广州虚拟动力数字人实时驱动解决方案,赋能虚拟IP、虚拟直播、品牌发布会...
    我的三周年创作纪念日
    drag 拖动元素让元素跟着鼠标移动,但是拖拽时的阴影咋隐藏不让他显示 ,
    Tableau数据的四种连接方式
  • 原文地址:https://blog.csdn.net/qq_39311377/article/details/133946764