索引失效场景+引擎类型

wk_云清
184 阅读4分钟

  • 连接

  • 内容

    • 常用概念：搜索条件，搜索范围

    • 1.索引失效场景

      • 1.使用like模糊匹配，通配符在左就不行（因为搜索范围过大，这个时候用索引意义也不大），在右就可以用到索引（用到的是范围索引range）
      • 2.在搜索条件中使用函数（在查询位置使用函数没关系）
      • 3.在搜索条件中使用计算（b-1=6不可以，但是b=6+1依然可以用到索引）
      • 4.搜索条件中使用or，如果or两边其中一个不是索引的列就会失效，两边都是才不会失效
      • 5.in的范围过大，会导致走不了索引（not in同样也是）
      • 6.使用order by，即使索引列已经排好序了，这时也要进行回表，mysql认为这个时候全文扫描比回表的速度更快

    • 2.联合索引的最左匹配原则是什么

      • 比如具有联合索引，sname, s_code, address，那么必须从最左边开始搜索条件必须包括sname（但是如果是address='123' and sname=1，也没关系，因为优化器这个时候排上用场了，会对这些搜索条件进行顺序改变）
      • 如果碰到范围查找，总共四个,>,<,between,like，那么就会主动停止到这个位置（注意这里不是说范围查找就用不到索引了，而是说这个字段依然用到索引，但是后面就停止用索引）。比如，再比如sname = "变成派大星" and s_code > 1 and address = "上海" address是用不到索引的

      • 为什么要设定最左匹配原则，是因为它符合底层的数据结构

        • 对于联合索引，它在构建B+树的时候是会根据从左到右，一个一个按值构建，也就是先对第一个字段排好序后，在第一个字段相同的情况下，再对第二个字段进行排序

    • 3.mysql不同版本有什么区别

      • 8.0

        • 引入跳跃扫描的方法，可以打破最左匹配原则
        • 比如bcd进行联合索引，如果b的唯一值较少，比如只有1、2，那么即使搜索条件中没有b，也会用到联合索引。因为唯一值少，那么b本身所要用到的索引的意义也不大

      • 5.6之后5.7开始

        • 添加了索引下推
        • 默认引擎从MylSAM变到了InnoDB

    • 4.select*会导致索引失效吗，有什么其它弊端吗

      • 这个要看情况，取决于搜索范围是否过大
      • 如果搜索条件是用到非聚簇索引，那么就会发生回表操作，增加查询时间
      • 分析器要将*拆分成各个字段，增加分析器分析成本

    • 5.索引下推是什么（ICP 全称是 Index Condition Pushdown），目的是什么

      • 目的是减少回表的次数，前提是联合索引

      • select * from table1 where b like '3%' and c = 3（bc是联合索引）

        • 5.6之前是搜索每一个b中有3开头的数据，因为按照最左匹配原则，就立即回表查询一次
        • 5.6之后还要继续进行索引匹配，把c=3的值找出来，然后再进行回表搜索

    • 6.不同引擎的区别

      • 叶节点存储的数据

        • InnoDB存储的是数据库的数据本身
        • MyISAM存储的是地址

      • 索引的读取速度：

        • MyISAM 引擎保存了单独的索引文件 .myi，且它的索引是直接定位到 OFFSET 的，而 InnoDB 没有单独的物理索引存储文件，且 InnoDB 索引寻址是先定位到块数据，再定位到行数据，所以 MyISAM 的查询效率是比 InnoDB 的查询效率要高。它的适用场景是读多写少，且对完整性要求不高的业务场景。
      • memory：内存型数据库引擎，所有的数据都存储在内存中，因此它的读写效率很高，但 MySQL 服务重启之后数据会丢失。它同样不支持事务、不支持外键。

      • 对于事务，外键，行级锁等支持：

        • MyISAM：读取效率较高，占用数据空间较少，但不支持事务、不支持行级锁、不支持外键等特性。因为不支持行级锁，因此在添加和修改操作时，会执行锁表操作，所以它的写入效率较低。
avatar
文章
阅读
粉丝