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

    2021-02-14 15:50:59
    索引下推 比如一个表中有四个字段id,name,age,gender, 创建联合索引(name,age) 查询 select * from t1 where name = ‘zhangsan’ and age = 10 在没有索引下推的优化: 1、先根据name把符合的数据从存储引擎拉到...

    索引下推(只发生在联合索引中)

    比如一个表中有四个字段id,name,age,gender, 创建联合索引(name,age)

    查询
    select * from t1 where name = ‘zhangsan’ and age = 10

    在没有索引下推的优化:
    1、先根据name把符合的数据从存储引擎拉到mysql的server层
    2、在server层按照age进行过滤

    在这里插入图片描述

    索引下推优化:
    从存储引擎拉取数据的时候直接按照name和age进行判断,将符合的结果返回mysql的server层
    在这里插入图片描述
    1、不使用索引条件下推优化时存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件。
    2、当使用索引条件下推优化时,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。

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

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

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

    5661a1fca379229be44b455ba8ce7dab.png

    usertest数据表

    假设有上面一张表,上面的ID字段是主键索引,age是普通索引。

    对比下面两条SQL语句:

    select id from usertest where age = 10;

    select name from usertest where age = 10;

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

    8c3667ce76bcfb160e8dd762434431e7.png

    age索引

    根据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。

    ba595a0fc71bcace6a4a6323ef7ce7e5.png

    name like 'a%'查询结果

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

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

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

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

    展开全文
  • innodb的索引下推

    万次阅读 2020-08-25 16:32:18
    索引下推,是mysql优化多列索引查询的一种方案,叫做索引下推不如翻译为索引条件下推更合适(Index Condition Pushdown)简称ICP,因为他实际上是把where中的查询索引条件,下推给了存储引擎 本文涉及到的内容有: ...

    索引下推,是mysql优化联合索引查询的一种方案,叫做索引下推不如翻译为索引条件下推更合适(Index Condition Pushdown)简称ICP,因为他实际上是把where中的查询索引条件,下推给了存储引擎

    本文涉及到的内容有:

    • 回表
    • 索引下推
    • 索引覆盖

    预备知识:

    • b+tree
    • 主键索引和非主键索引
    • 聚簇索引和非聚簇索引

    主键索引和非主键索引

    说到回表,我们先回顾一下innodb主键索引和非主键索引(辅助索引)的区别(具体的之后写一篇文章来讲解,这里只是简单带过)。

    他们数据存储的方式是一样的,但是由于innodb使用的辅助索引的叶子节点上只有主键(不是数据指针哦),如果需要更多列数据,查找就需要分成了两步。

    1. 第一步在辅助索引B+树中检索,到达其叶子节点获取对应的主键。
    2. 第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。

    当我们使用辅助索引检索数据的时候,会有两种情况

    • 当想要查询的字段(列)并不都包含在索引中的时候,一定会涉及到回表取数据,即拿到辅助索引叶子节点上的主键再去主键索引上检索取到全部数据。这个过程叫做回表
    • 当sql语句的所求查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中,可以直接使用索引查询而不需要回表。这就是覆盖索引,可以减少一次树的检索,是常用的性能优化手段。

    索引下推为了解决什么问题

    对低于5.0的mysql来说,只能使用单个索引来筛选数据,从5.1开始,引入了 index merge 优化技术,对同一个表可以使用多个索引分别进行条件扫描,但是和全表扫描或只使用一个索引的速度比起来,去分析两个索引二叉树可能更加耗费时间,所以绝大多数情况下数据库都是是用一个索引。

    在这个前提下,当一个索引选择度(就是通过索引能过滤掉的数据比例)比较低的时候,可能单个索引过滤掉的数据并没有太多。所以我们一般会使用联合索引。但是联合索引又受最左前缀影响。有时候会不起作用,所以才做出了索引下推这个功能。(最左前缀具体请参看上一篇文章:https://blog.csdn.net/winterfeng123/article/details/108150223)

    我们来看一下有索引下推和没有索引下推,在一条sql执行过程中的区别。

    sql执行一般经过三层

    • mysql server
    • 接口层
    • 存储和事务管理层
      由于在索引下推过程中接口层没有什么实际操作,所以简略书写一下。下面分别说一下两种情况的流程
    不使用索引下推
    1. MySQL Server发出读取数据的命令,通过函数指针和handle接口(接口层)调用存储引擎的索引读或全表表读。如果有可使用的索引则进行的是索引读,但是只能选择一个索引。
    2. 指令进入存储引擎,读取索引树,在索引树上查找,拿到到符合条件的主键值,回表把满足条件的记录从表记录中读出,从存储引擎返回标识的结果
    3. 从存储引擎返回查找到的多条元组给MySQL Server,MySQL Server在得到单一索引过滤后的数据
    4. MySQL Server拿到上述数据后,按照where中其他的条件进行过滤,得到符合条件的最终结果。
    使用索引下推

    首先看上面的我们已经大概知道,如果就只有一列索引,是无所谓有没有索引下推这个功能的,所以我们只讨论存在多个where条件,这些条件中的多个列上有索引的情况。

    1. 同上,MySQL Server发出读取数据的命令,通过函数指针和handle接口(接口层)调用存储引擎的索引读或全表表读。如果有可使用的索引则进行的是索引读,但是这里会把含有索引的列的where条件都下放到存储引擎层。
    2. 指令进入存储引擎,读取某个索引树,在该索引树上查找过滤出相应数据后,并不直接去回表查询数据,而是继续通过联合索引的其他索引条件进行过滤把满足已经下推的条件的主键拿到。然后再去回表查询所有符合条件的数据(数据较少,io也就较少)。例如
    #假test表有个联合索引包含两列 a,b。
    
    假如语句如下:
    select * from test where a like "zhang%" and b > 10;
    两个条件都会下推到存储引擎,因为最左前缀,会先按照a来筛选。其次再使用b来过滤筛选过的索引值。然后再回表查询。
    
    
    1. 从存储引擎返回查找到的少量数据给MySQL Server,MySQL Server在根据其他的条件进行筛选。
    对比
    • 第 1 步中MySQL Server发送了额外的索引条件到存储引擎,多了一点点网络开销
    • 第 2 步中的回表阶段:由于比第一步过滤掉了更多的表数据,所以少了很多磁盘io
    • 第 3 步中

    接收数据阶段:要接收更多的数据,增加内部开销

    二次过滤数据阶段:要处理更多的数据,占用了更多的cpu和内存。

    总结:

    总体来说,在有联合索引的情况下,索引下推使得查询的效率有明显提升。但是也不是任何时候都可以使用索引下推的,因为其主要通过减少了回表查询的数量来优化查询,所以其限制如下:

    1. innodb引擎的表,索引下推只能用于辅助索引,这是因为主键索引树叶子结点上保存的是全行数据,根本不涉及到回表问题。

    2. 索引下推一般可用于非索引覆盖的情况下,因为索引覆盖也是没有必要回表的。

    优点如下:

    1. 提高了有联合索引时的查询效率
    2. 一定程度上打破了联合索引的最左前缀原则,详情请看 https://blog.csdn.net/winterfeng123/article/details/108150223

    索引下放也可以关闭,但是不建议管理。关闭和开启语句如下:

    #关闭
    SET optimizer_switch = 'use_index_extensions=off';
    #开启
    SET optimizer_switch = 'use_index_extensions=on';
    
    展开全文
  • mysql这里说的是在使用索引查询时有关索引下推的有关知识点。sql综合前人的经验结果:索引下推是数据库检索数据过程当中为减小回表次数而作的优化。docker判断是否须要回表的是由mysql存储引擎控制,默认从mysql5.6...

    对于mysql建表稍有点经验的开发人员都会为后续的where查询条件提早考虑建立索引。mysql

    这里说的是在使用索引查询时有关索引下推的有关知识点。sql

    综合前人的经验结果:索引下推是数据库检索数据过程当中为减小回表次数而作的优化。docker

    判断是否须要回表的是由mysql存储引擎控制,默认从mysql5.6版本开始支持。数据库

    下面用docker分别建立基于mysql5.5和mysql5.6的容器,表结构保持一致(docker建立mysql容器不作演示)。优化

    首先看mysql5.5:spa

    mysql> select version();

    +-----------+

    | version() |

    +-----------+

    | 5.5.62 |

    +-----------+

    1 row in set (0.00 sec)

    mysql> show create table testhh\G;

    *************************** 1. row ***************************

    Table: testhh

    Create Table: CREATE TABLE `testhh` (

    `id` int(10) unsigned NOT NULL,

    `age` int(10) unsigned DEFAULT '0',

    `name` char(10) NOT NULL DEFAULT '',

    `height` int(10) NOT NULL DEFAULT '0',

    `name2` char(10) NOT NULL DEFAULT '',

    `height2` int(10) NOT NULL DEFAULT '0',

    PRIMARY KEY (`id`),

    KEY `age_index` (`age`) USING HASH,

    KEY `un` (`name`,`height`)

    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    1 row in set (0.00 sec)

    ERROR:

    No query specified

    mysql> explain select * from testhh where name like 'a%' and height = 100\G;

    *************************** 1. row ***************************

    id: 1

    select_type: SIMPLE

    table: testhh

    type: range

    possible_keys: un

    key: un

    key_len: 14

    ref: NULL

    rows: 1

    Extra: Using where

    1 row in set (0.00 sec)

    ERROR:

    No query specified

    上面可见explain的extra字段结果时Using where,表示优化器须要经过索引回表查询数据。code

    再看mysql5.6:blog

    mysql> selectversion();+-----------+

    | version() |

    +-----------+

    | 5.6.50 |

    +-----------+

    1 row in set (0.00sec)

    mysql>show create table ua\G;*************************** 1. row ***************************Table: ua

    Create Table: CREATE TABLE `ua` (

    `id`int(10) NOT NULL AUTO_INCREMENT,

    `name`char(10) NOT NULL DEFAULT '',

    `height`int(10) NOT NULL DEFAULT '0',

    `name2`char(10) NOT NULL DEFAULT '',

    `height2`int(10) NOT NULL DEFAULT '0',

    PRIMARY KEY (`id`),

    KEY `nh` (`name`,`height`)

    ) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00sec)

    ERROR:

    No query specified

    mysql> explain select * from ua where name like 'a%' and height=10\G;*************************** 1. row ***************************id:1select_type: SIMPLE

    table: ua

    type: range

    possible_keys: nh

    key: nh

    key_len:14

    ref: NULL

    rows:1Extra: Using index condition1 row in set (0.00sec)

    ERROR:

    No query specified

    explain的extra字段是Using index condition,表示会先经过条件过滤索引,再经过过滤后的索引查询符合索引条件的数据。索引

    展开全文
  • 索引下推 即在使用联合索引进行查询的时候,第一次显示找到最左边的第一个索引所满足的所有值,然后在返回的结果中查找所有满足第二个索引的所有值,以此类推…(mysql5.6之后引入的索引下推,之前在联合索引的第一个...
  • 这里说的是在使用索引查询时有关索引下推的有关知识点。综合前人的经验结果:索引下推是数据库检索数据过程中为减少回表次数而做的优化。判断是否需要回表的是由mysql存储引擎控制,默认从mysql5.6版本开始支持。...
  • Mysql 索引下推

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

    2021-04-06 14:21:30
    索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后...
  • 索引下推 索引下推主要是减少了不必要的回表操作。对于查找出来的数据,先过滤掉不符合条件的,其余的再去主键索引树上查找。 在 Mysql 5.6 版本之前是没有索引下推这个功能的,从 5.6 版本后才加上了这个...
  • Mysql联合索引的使用索引下推、覆盖索引概念 建立一张user表,id、name、age、address。建立联合索引(name,age)。 理解索引下推、覆盖索引的概念首先要理解,联合索引的普通索引的区别。 比如(name, age) 和 ...
  • 联合索引的树结构、最左匹配原则、如何选择合适的索引列顺序、索引下推图文讲解
  • 覆盖索引、联合索引、索引下推 阅读目录 面试三轮我倒在了一道sql题上——sql性能优化 回到目录 面试三轮我倒在了一道sql题上——sql性能优化 一、前言 最近小农在找工作,因为今年疫情的特殊原因,导致...
  • MySQL索引下推
  • 1. 索引下推 官网介绍 其实就是针对组合索引,比如说(class,name)上建立一个组合索引,但是我们name不满足索引条件,如like,这时MySQL也会把name值当作一个条件看看满不满足,这样做的目的就是减少回表,假设有10个...
  • 索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后...
  • mysql索引下推原理

    2021-03-21 12:15:23
    前段时间有读者提议讲讲索引下推,这期就把这事儿安排上。多余的前言就不赘述了,我们直接开始。 列位坐好! - 思维导图 - 回表操作 对于数据库来说,只要涉及到索引,必然绕不过去回表操作。当然这也是我们...
  • 索引覆盖 最左前缀 索引下推 索引覆盖 总结问题 什么是索引覆盖? 怎么用到索引覆盖 索引覆盖的情况,using index ; using index using where select * from T where k betwee 3 and 5 这条语句的执行流程是...
  • 今天我们来聊聊在MySQL索引优化中两种常见的方式,索引覆盖和索引下推索引覆盖要了解索引覆盖,需要先了解几个索引的基础知识B+树索引B+树索引是InnoDB中的一种很常见的索引类型。关于B+树,这里不做深入的介绍,不...
  • 快速理解 Mysql 回表 索引覆盖 索引下推回表操作索引覆盖索引下推 回表操作 Mysql 每页大小为16K(B+树结构,所以16K足以),关于主键索引和辅助索引的结构这里简单说一下。 InnoDB 主键(聚簇索引):仅在叶子节点...
  • Mysql性能优化:什么是索引下推? 导读 本文章始发于本人公众号:码猿技术专栏,原创不易,谢谢关注推荐。 索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不...
  • 1、索引下推是mysql5.6(包括)之后的优化策略。 2、是否设置了索引下推,explain执行计划查看到了 rows 行数应该是一致的。因为索引下推只是减少了回表的次数。 打开索引下推。 set optimizer_switch='index_...
  • 索引下推(减少回表次数) 存储引擎与底层实现的数据结构 数据结构 - 索引怎么选择合适的数据结构?中分析过能作为索引的数据结构主要有散列表(Hash表)、红黑树、跳表、B+树(B树)以及有序数组,并且分析了它们...
  • MySQL中的索引下推

    2021-03-18 14:01:29
    索引下推用一句话总结是:索引下推是数据库检索数据过程中为减少回表次数而做的优化。 首先介绍下什么是数据库回表,回表是一种数据库检索过程。通常发生在使用二级索引检索非主索引数据的过程中。举个例子: 假设...

空空如也

空空如也

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

索引下推