精华内容
下载资源
问答
  • Mysql 索引下推

    2021-04-16 17:19:52
    索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后...

     

    导读

    • 索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。

    • 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 。

    • 在使用ICP的情况下,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

    • 索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。 

     

    开撸

    • 在开始之前先先准备一张用户表(user),其中主要几个字段有:id、name、age、address。建立联合索引(name,age)

    • 假设有一个需求,要求匹配姓名第一个为陈的所有用户,sql语句如下:

      SELECT * from user where  name like '陈%'
    • 根据 "最佳左前缀" 的原则,这里使用了联合索引(name,age)进行了查询,性能要比全表扫描肯定要高。

    • 问题来了,如果有其他的条件呢?假设又有一个需求,要求匹配姓名第一个字为陈,年龄为20岁的用户,此时的sql语句如下:

      SELECT * from user where  name like '陈%' and age=20
    • 这条sql语句应该如何执行呢?下面对Mysql5.6之前版本和之后版本进行分析。

     

    Mysql5.6之前的版本

    • 5.6之前的版本是没有索引下推这个优化的,因此执行的过程如下图:

     

    • 会忽略age这个字段,直接通过name进行查询,在(name,age)这课树上查找到了两个结果,id分别为2,1,然后拿着取到的id值一次次的回表查询,因此这个过程需要回表两次

     

    Mysql5.6及之后版本

    • 5.6版本添加了索引下推这个优化,执行的过程如下图:

    • InnoDB并没有忽略age这个字段,而是在索引内部就判断了age是否等于20,对于不等于20的记录直接跳过,因此在(name,age)这棵索引树中只匹配到了一个记录,此时拿着这个id去主键索引树中回表查询全部数据,这个过程只需要回表一次

     

    实践

    • 当然上述的分析只是原理上的,我们可以实战分析一下,因此陈某装了Mysql5.6版本的Mysql,解析了上述的语句,如下图:

    • 根据explain解析结果可以看出Extra的值为Using index condition,表示已经使用了索引下推。

     

    总结

    • 索引下推在非主键索引上的优化,可以有效减少回表的次数,大大提升了查询的效率。

    • 关闭索引下推可以使用如下命令,配置文件的修改不再讲述了,毕竟这么优秀的功能干嘛关闭呢:

      set optimizer_switch='index_condition_pushdown=off';
    展开全文
  • mysql索引下推

    2021-04-06 14:21:30
    索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后...
    • 索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。

    • 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 。

    • 在使用ICP的情况下,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

    • 索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数

     

    开撸

    • 在开始之前先先准备一张用户表(user),其中主要几个字段有:id、name、age、address。建立联合索引(name,age)

    • 假设有一个需求,要求匹配姓名第一个为陈的所有用户,sql语句如下:

      SELECT * from user where  name like '陈%'
    • 根据 "最佳左前缀" 的原则,这里使用了联合索引(name,age)进行了查询,性能要比全表扫描肯定要高。

    • 问题来了,如果有其他的条件呢?假设又有一个需求,要求匹配姓名第一个字为陈,年龄为20岁的用户,此时的sql语句如下:

      SELECT * from user where  name like '陈%' and age=20
    • 这条sql语句应该如何执行呢?下面对Mysql5.6之前版本和之后版本进行分析。

     

    Mysql5.6之前的版本

    • 5.6之前的版本是没有索引下推这个优化的,因此执行的过程如下图:

     

    • 会忽略age这个字段,直接通过name进行查询,在(name,age)这课树上查找到了两个结果,id分别为2,1,然后拿着取到的id值一次次的回表查询,因此这个过程需要回表两次

     

    Mysql5.6及之后版本

    • 5.6版本添加了索引下推这个优化,执行的过程如下图:

    • InnoDB并没有忽略age这个字段,而是在索引内部就判断了age是否等于20,对于不等于20的记录直接跳过,因此在(name,age)这棵索引树中只匹配到了一个记录,此时拿着这个id去主键索引树中回表查询全部数据,这个过程只需要回表一次

     

    实践

    • 当然上述的分析只是原理上的,我们可以实战分析一下,因此陈某装了Mysql5.6版本的Mysql,解析了上述的语句,如下图:

    • 根据explain解析结果可以看出Extra的值为Using index condition,表示已经使用了索引下推。

     

    总结

    • 索引下推在非主键索引上的优化,可以有效减少回表的次数,大大提升了查询的效率。

    • 关闭索引下推可以使用如下命令,配置文件的修改不再讲述了,毕竟这么优秀的功能干嘛关闭呢:

      set optimizer_switch='index_condition_pushdown=off';
    展开全文
  • MySQL索引下推

    MySQL索引下推

     

    展开全文
  • 前段时间看了一下数据库相关知识,出现了索引下推这个名词,有必要记录下来作为知识储备。索引下推用一句话总结是:索引下推是数据库检索数据过程中为减少回表次数而做的优化。首先介绍下什么是数据库回表,回表是一...

    前段时间看了一下数据库相关知识,出现了索引下推这个名词,有必要记录下来作为知识储备。索引下推用一句话总结是:索引下推是数据库检索数据过程中为减少回表次数而做的优化。

    首先介绍下什么是数据库回表,回表是一种数据库检索过程。通常发生在使用二级索引检索非主索引数据的过程中。举个例子:

    9d0d1072f7b62347370a3bc4be028e82.png

    假设有上面一张表(数据库是MYSQL,存储引擎是Innodb),上面的ID字段是主键索引,age是普通索引。

    对比下面两条SQL语句:

    select id from usertest where age = 10;

    select name from usertest where age = 10;

    第一条SQL语句不会产生回表:普通索引存储的值是主键的值。也就是说age索引里面存储的结构是下面的情况

    19baa18bd6c87fb80b5903cb5b3b5926.pngage索引

    根据age查询id的时候,索引中的值完全可以覆盖查询结果集字段时,不会产生回表操作。

    由此也可以看出第二条SQL语句会产生回表是因为查询的结果集无法通过索引中的值直接获取。需要根据age查询到的id值再回到主键索引里面再次查询,这个过程叫做回表。

    然后再看索引下推

    还是上面的usertest表,只是索引变了,ID字段是仍主键索引,但是我们加上一个复合索引name_age(name,age)。

    执行下面一条SQL语句:

    select * from usertest where name like 'a%' and age = 10;

    在Mysql5.6之前的执行流程是这样的:

    1.根据最左前缀原则,执行name like 'a%'可以快速检索出id的值为1,5。

    fa8f0fa653794441224caac5a80ef73d.png

    2.然后根据id的值进行回表操作,再次进行过滤age=10的数据。

    查询id=1回表1次,id=5回表1次,这个过程总共回表了2次。

    可能到这里都会有疑问,为什么不在索引里面直接过滤age=10的数据,因为复合索引里面也存了age的数据,这样明明可以减少回表1次。恭喜啦,Mysql5.6以后就这么做了,这就是索引下推。

    这样可以看出,索引下推具体是在复合索引的查询中,针对特定的过滤条件而进行减少回表次数而做的优化(个人觉得本来就该这么设计的)。

    展开全文
  • 如果没有使用索引下推技术,则MySQL会通过zipcode='95054'从存储引擎中查询对应的元祖,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断元祖是否符合条件。...
  • mysql这里说的是在使用索引查询时有关索引下推的有关知识点。sql综合前人的经验结果:索引下推是数据库检索数据过程当中为减小回表次数而作的优化。docker判断是否须要回表的是由mysql存储引擎控制,默认从mysql5.6...
  • 前置版本mysql> select version();+------------+| version() |+------------+| 5.7.26-log |+------------+先来建个表加个索引DROP TABLE IF EXISTS `indexconditionpushdown`;CREATE TABLE `...
  • mysql索引下推原理

    2021-03-21 12:15:23
    前段时间有读者提议讲讲索引下推,这期就把这事儿安排上。多余的前言就不赘述了,我们直接开始。 列位坐好! - 思维导图 - 回表操作 对于数据库来说,只要涉及到索引,必然绕不过去回表操作。当然这也是我们...
  • 1、索引下推mysql5.6(包括)之后的优化策略。 2、是否设置了索引下推,explain执行计划查看到了 rows 行数应该是一致的。因为索引下推只是减少了回表的次数。 打开索引下推。 set optimizer_switch='index_...
  • 索引下推 在前面学习整理mysql数据结果和索引相关知识的时候,遇到了几个概念,像是回表、聚簇索引、最左匹配等,这篇将常听到的概念整理到一起,便于以后翻阅和小伙伴们阅读,各位看到此博客的小伙伴,如有不对的...
  • 前段时间有读者提议讲讲索引下推,这期就把这事儿安排上。多余的前言就不赘述了,我们直接开始。列位坐好!图注:思维导图回表操作对于数据库来说,只要涉及到索引,必然绕不过去回表操作。当然这也是我们今天所讲...
  • [root@HE3 ~]# /usr/ local /mariadb/bin/mysql -uroot -S /tmp/mariadb.sockWelcome to the MariaDB monitor. Commands end with ; or g.Your MariaDB connection id is 26Server version: 10.1.16-MariaDB MariaDB...
  • 前段时间有读者提议讲讲索引下推,这期就把这事儿安排上。多余的前言就不赘述了,我们直接开始。列位坐好!- 思维导图 -回表操作对于数据库来说,只要涉及到索引,必然绕不过去回表操作。当然这也是我们今天所讲内容...
  • 索引条件下推,Index Condition Pushdown,简称ICP,是MySQL内部通过索引查询数据的一种优化方法,简单来说就是将原本需要在Server层对数据进行过滤的条件下推到了引擎层去做,在引擎层过滤更多的数据,这样从引擎层...
  • MySQL索引下推技术

    2016-03-10 15:19:07
    索引下推整个思路如下: To see how this optimization works, consider first how an index scan proceeds when Index Condition Pushdown is not used: Get the next row, first by reading the index tuple...
  • 索引下推(听着高大上,就是一层窗户纸而已) 有了索引下推优化,可以在有like条件查询的情况下,减少回表次数。 对于user_table表,我们现在有(username,age)联合索引 如果现在有一个需求,查出名称中以“张”...
  • 一 什么是“索引条件下推”“索引条件下推”,称为Index Condition Pushdown (ICP),这是MySQL提供的用某一个索引对一个特定的表从表中获取元组”,注意我们这里特意强调了“一个”,这是因为这样的索引优化不是用于...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 529
精华内容 211
关键字:

mysql索引下推

mysql 订阅