五分钟带你搞懂MySQL索引下推

目录
  • 什么是索引下推
  • 索引下推优化的原理
  • 索引下推的具体实践
    • 没有使用icp
    • 使用icp
  • 索引下推使用条件
    • 相关系统参数
      • 总结

        如果你在面试中,听到mysql5.6”、“索引优化” 之类的词语,你就要立马get到,这个问的是“索引下推”。

        什么是索引下推

        索引下推(index condition pushdown,简称icp),是mysql5.6版本的新特性,它能减少回表查询次数,提高查询效率。

        索引下推优化的原理

        我们先简单了解一下mysql大概的架构:

        mysql服务层负责sql语法解析、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。

        索引下推的下推其实就是指将部分上层(服务层)负责的事情,交给了下层(引擎层)去处理。

        我们来具体看一下,在没有使用icp的情况下,mysql的查询:

        • 存储引擎读取索引记录;
        • 根据索引中的主键值,定位并读取完整的行记录;
        • 存储引擎把记录交给server层去检测该记录是否满足where条件。

        使用icp的情况下,查询过程:

        • 存储引擎读取索引记录(不是完整的行记录);
        • 判断where条件部分能否用索引中的列来做检查,条件不满足,则处理下一行索引记录;
        • 条件满足,使用索引中的主键去定位并读取完整的行记录(就是所谓的回表);
        • 存储引擎把记录交给server层,server层检测该记录是否满足where条件的其余部分。

        索引下推的具体实践

        理论比较抽象,我们来上一个实践。

        使用一张用户表tuser,表里创建联合索引(name, age)。

        如果现在有一个需求:检索出表中名字第一个字是张,而且年龄是10岁的所有用户。那么,sql语句是这么写的:

        select * from tuser where name like '张%' and age=10;

        假如你了解索引最左匹配原则,那么就知道这个语句在搜索索引树的时候,只能用 张,找到的第一个满足条件的记录id为1。

        那接下来的步骤是什么呢?

        没有使用icp

        在mysql 5.6之前,存储引擎根据通过联合索引找到name likelike '张%' 的主键id(1、4),逐一进行回表扫描,去聚簇索引找到完整的行记录,server层再对数据根据age=10进行筛选

        我们看一下示意图:

        可以看到需要回表两次,把我们联合索引的另一个字段age浪费了。

        使用icp

        而mysql 5.6 以后, 存储引擎根据(name,age)联合索引,找到name likelike '张%‘,由于联合索引中包含age列,所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

        我们看一下示意图:

        可以看到只回表了一次。

        除此之外我们还可以看一下执行计划,看到extra一列里 using index condition,这就是用到了索引下推。

        +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
        | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | extra                 |
        +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
        |  1 | simple      | tuser | null       | range | na_index      | na_index | 102     | null |    2 |    25.00 | using index condition |
        +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

        索引下推使用条件

        • 只能用于range ref eq_refref_or_null访问方法;
        • 只能用于innodb和 myisam存储引擎及其分区表;
        • innodb存储引擎来说,索引下推只适用于二级索引(也叫辅助索引);

        索引下推的目的是为了减少回表次数,也就是要减少io操作。对于innodb的聚簇索引来说,数据和索引是在一起的,不存在回表这一说。

        • 引用了子查询的条件不能下推;
        • 引用了存储函数的条件不能下推,因为存储引擎无法调用存储函数。

        相关系统参数

        索引条件下推默认是开启的,可以使用系统参数optimizer_switch来控制器是否开启。

        查看默认状态:

        mysql> select @@optimizer_switch\g;
        *************************** 1. row ***************************
        @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
        1 row in set (0.00 sec)
        

        切换状态:

        set optimizer_switch="index_condition_pushdown=off";
        set optimizer_switch="index_condition_pushdown=on";

        总结

        本篇文章就到这里了,希望能够给你带来帮助,也希望您能够多多关注www.887551.com的更多内容!

        (0)
        上一篇 2022年3月21日
        下一篇 2022年3月21日

        相关推荐