精华内容
下载资源
问答
  • MySQL索引下推技术
    2021-02-03 11:27:19

    Get the next row's index tuple (but not the full table row).

    Test the part of the WHERE condition that applies to this table and can be checked using only index columns. If the condition is not satisfied, proceed to the index tuple for the next row.

    If the condition is satisfied, use the index tuple to locate and read the full table row.

    Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result.

    举例如下:

    Suppose that we have a table containing information about people and their addresses and that the table has an index defined as INDEX (zipcode, lastname, firstname). If we know a person's zipcode value but are not sure about the last name, we can search like this:

    SELECT * FROM people

    WHERE zipcode='95054'

    AND lastname LIKE '%etrunia%'

    AND address LIKE '%Main Street%';

    people表中(zipcode,lastname,firstname)构成一个索引。

    如果没有使用索引下推技术,则MySQL会通过zipcode='95054'从存储引擎中查询对应的元祖,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断元祖是否符合条件。

    如果使用了索引下推技术,则MYSQL首先会返回符合zipcode='95054'的索引,然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合

    条件。如果符合条件,则根据该索引来定位对应的元祖,如果不符合,则直接reject掉。

    更多相关内容
  • mysql索引下推原理

    2021-03-21 12:15:23
    前段时间有读者提议讲讲索引下推,这期就把这事儿安排上。多余的前言就不赘述了,我们直接开始。 列位坐好! - 思维导图 - 回表操作 对于数据库来说,只要涉及到索引,必然绕不过去回表操作。当然这也是我们...

    前段时间有读者提议讲讲索引下推,这期就把这事儿安排上。多余的前言就不赘述了,我们直接开始。

    列位坐好!

    - 思维导图 -

    820678ca27043064cc19748f7b4a4c1f.png

    回表操作

    对于数据库来说,只要涉及到索引,必然绕不过去回表操作。当然这也是我们今天所讲内容的前调基础。

    说到回表,我们需要从索引开始说起。别担心,不会长篇大论,这里只是简单讲下主键索引与普通索引,目的是让大家对回表操作有个认识。如果你对回表操作很熟悉了,那么可以跳过这一段。

    这里我们只以 Innodb 存储引擎作为讲解对象。

    主键索引

    主键索引在底层的数据存储是通过 B+ 树来实现的。简单来说,就是除叶子节之外的其他节点都存储的是主键值。而叶子节点上存储的是整行的数据。

    大体结构如下图所示。

    013166e4a36b217c01cc49f74dbd956b.png

    非主键索引

    除了主键索引外,其它的索引都被称为非主键索引。与主键索引不同的是,非主键索引的叶子节点上存储的是主键的值。

    9d80f91e7ff93937bd148826768e522b.png

    那让我们再回到开始的问题,什么是回表操作?

    当我们在非主键索引上查找一行数据的时候,此时的查找方式是先搜索非主键索引树,拿到对应的主键值,再到主键索引树上查找对应的行数据。

    这种操作就叫作回表操作。

    好了,这里你应该了解了什么是回表操作了。简单来讲,就是在非主键索引树上拿到对应的主键值,然后回到主键索引上找到对应的行数据。

    这样做的前提条件是,所要查找的字段不存在于非主键索引树上。

    低版本操作

    讲完了回表操作,让我们继续回到这篇文章的主题——索引下推。

    其实在 Mysql 5.6 版本之前是没有索引下推这个功能的,从 5.6 版本后才加上了这个优化项。所以在引出索引下推前还是先回顾下没有这个功能时是怎样一种处理方式。

    我们以一个真实例子来进行讲解。

    在这里有张用户表 user,记录着用户的姓名,性别,身高,年龄等信息。表中 id 是自增主键,(name,sex) 是联合索引。在这里用 1 表示男,2 表示女。现在需要查找所有姓王的男性信息。

    SQL 实现起来很简单:

    2ad4280098ea469d21c896b673885117.png

    但是它的实现原理是什么呢?

    根据联合索引最左前缀原则,我们在非主键索引树上找到第一个满足条件的值时,通过叶子节点记录的主键值再回到主键索引树上查找到对应的行数据,再对比是否为当前所要查找的性别。

    整个原理可以用下边的图进行表示。

    50d213badeff9a90273d2d98c5bcff12.png

    看到了吧,低版本中需要每条数据都进行回表,增加了树的搜索次数。如果遇到所要查找的数据量很大的话,性能必然有所缺失。

    高版本操作

    讲完了低版本操作,让我们继续回到这篇文章的主题——索引下推。

    知道了痛点,那么怎么解决。很简单,只有符合条件了再进行回表。结合我们的例子来说就是当满足了性别 sex = 1 了,再回表查找。这样原本可能需要进行回表查找 4 次,现在可能只需要 2 次就可以了。

    96d9edcc30740b30c9932133c13c38e3.png

    所以本质来说,索引下推就是只有符合条件再进行回表,对索引中包含的字段先进行判断,不符合条件的跳过。减少了不必要的回表操作

    总结

    回表操作

    当所要查找的字段不在非主键索引树上时,需要通过叶子节点的主键值去主键索引上获取对应的行数据,这个过程称为回表操作。

    索引下推

    索引下推主要是减少了不必要的回表操作。对于查找出来的数据,先过滤掉不符合条件的,其余的再去主键索引树上查找。

    展开全文
  • 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,表示会先经过条件过滤索引,再经过过滤后的索引查询符合索引条件的数据。索引

    展开全文
  • MySQL:好好的索引,为什么要下推

    千次阅读 2021-02-03 11:27:19
    前段时间有读者提议讲讲索引下推,这期就把这事儿安排上。多余的前言就不赘述了,我们直接开始。列位坐好!- 思维导图 -回表操作对于数据库来说,只要涉及到索引,必然绕不过去回表操作。当然这也是我们今天所讲内容...

    9744b9b3b718c7886318557a075f246d.png

    前段时间有读者提议讲讲索引下推,这期就把这事儿安排上。多余的前言就不赘述了,我们直接开始。

    列位坐好!

    820678ca27043064cc19748f7b4a4c1f.png

    - 思维导图 -

    回表操作

    对于数据库来说,只要涉及到索引,必然绕不过去回表操作。当然这也是我们今天所讲内容的前调基础。

    说到回表,我们需要从索引开始说起。别担心,不会长篇大论,这里只是简单讲下主键索引与普通索引,目的是让大家对回表操作有个认识。如果你对回表操作很熟悉了,那么可以跳过这一段。

    这里我们只以 Innodb 存储引擎作为讲解对象。

    主键索引

    主键索引在底层的数据存储是通过 B+ 树来实现的。简单来说,就是除叶子节之外的其他节点都存储的是主键值。而叶子节点上存储的是整行的数据。

    大体结构如下图所示。

    013166e4a36b217c01cc49f74dbd956b.png

    非主键索引

    除了主键索引外,其它的索引都被称为非主键索引。与主键索引不同的是,非主键索引的叶子节点上存储的是主键的值。

    9d80f91e7ff93937bd148826768e522b.png

    那让我们再回到开始的问题,什么是回表操作?

    当我们在非主键索引上查找一行数据的时候,此时的查找方式是先搜索非主键索引树,拿到对应的主键值,再到主键索引树上查找对应的行数据。

    这种操作就叫作回表操作。

    好了,这里你应该了解了什么是回表操作了。简单来讲,就是在非主键索引树上拿到对应的主键值,然后回到主键索引上找到对应的行数据。

    这样做的前提条件是,所要查找的字段不存在于非主键索引树上。

    低版本操作

    讲完了回表操作,让我们继续回到这篇文章的主题——索引下推。

    其实在 Mysql 5.6 版本之前是没有索引下推这个功能的,从 5.6 版本后才加上了这个优化项。所以在引出索引下推前还是先回顾下没有这个功能时是怎样一种处理方式。

    我们以一个真实例子来进行讲解。

    在这里有张用户表 user,记录着用户的姓名,性别,身高,年龄等信息。表中 id 是自增主键,(name,sex) 是联合索引。在这里用 1 表示男,2 表示女。现在需要查找所有姓王的男性信息。

    SQL 实现起来很简单:

    2ad4280098ea469d21c896b673885117.png

    但是它的实现原理是什么呢?

    根据联合索引最左前缀原则,我们在非主键索引树上找到第一个满足条件的值时,通过叶子节点记录的主键值再回到主键索引树上查找到对应的行数据,再对比是否为当前所要查找的性别。

    整个原理可以用下边的图进行表示。

    50d213badeff9a90273d2d98c5bcff12.png

    看到了吧,低版本中需要每条数据都进行回表,增加了树的搜索次数。如果遇到所要查找的数据量很大的话,性能必然有所缺失。

    高版本操作

    讲完了低版本操作,让我们继续回到这篇文章的主题——索引下推。

    知道了痛点,那么怎么解决。很简单,只有符合条件了再进行回表。结合我们的例子来说就是当满足了性别 sex = 1 了,再回表查找。这样原本可能需要进行回表查找 4 次,现在可能只需要 2 次就可以了。

    96d9edcc30740b30c9932133c13c38e3.png

    所以本质来说,索引下推就是只有符合条件再进行回表,对索引中包含的字段先进行判断,不符合条件的跳过。减少了不必要的回表操作。

    总结

    回表操作

    当所要查找的字段不在非主键索引树上时,需要通过叶子节点的主键值去主键索引上获取对应的行数据,这个过程称为回表操作。

    索引下推

    索引下推主要是减少了不必要的回表操作。对于查找出来的数据,先过滤掉不符合条件的,其余的再去主键索引树上查找。

    【编辑推荐】

    【责任编辑:武晓燕 TEL:(010)68476606】

    点赞 0

    展开全文
  • MYSQL 索引下推

    2022-02-19 22:52:47
    MYSQL 索引下推(Index Condition) 背景介绍 官方文档链接 官方定义 Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, ...
  • 索引失效like 以%开头,索引失效组合索引,不是使用第一列索引索引失效数据类型出现隐式转化,索引失效其它情况不推荐使用索引的情况覆盖索引组合索引最左匹配原则注意组合索引数据结构索引下推
  • 我们都知道组合索引例如name_phone,名字和电话的组合索引,where name = xx and phone = 123这样严格的顺序才会使索引生效,像where phone = 123这样是不会走索引的。那么mysql是如何做的呢? 单值索引 联合索引 ...
  • 1、like 'KK%' 和索引下推 2、Order by 和 Group by 优化 3、Using filesort文件排序原理 // 以下建表结构和数据同《Mysql explain 索引优化案例》 1、like 'KK%' 和索引下推 like 'KK%'一般情况都会走索引,...
  • 详细介绍了各种高性能的索引使用策略,比如索引排序、索引下推、压缩索引等等,以及常见索引失效的情况。
  • MySQL联合索引索引下推图文详解

    千次阅读 2020-12-11 00:19:29
    联合索引的树结构、最左匹配原则、如何选择合适的索引列顺序、索引下推图文讲解
  • MySQL性能优化之:索引下推

    千次阅读 2020-08-23 10:21:04
    索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后...
  • 在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引,索引类型分为主键索引和二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引树中,叶子...
  • 索引下推(减少回表次数) 存储引擎与底层实现的数据结构 数据结构 - 索引怎么选择合适的数据结构?中分析过能作为索引的数据结构主要有散列表(Hash表)、红黑树、跳表、B+树(B树)以及有序数组,并且分析了它们...
  • 四、索引下推 五、小结 一、引入 在开始这篇文章之前,首先明确一个概念,聚集索引的B+树的每个节点就是一个索引页,索引页会根据先前规定好的度数来决定一个索引页放多少个索引值。 非叶子节点只有索引区...
  • Mysql索引底层原理

    千次阅读 2021-12-15 10:35:47
    1.2、我们为什么需要使用索引,我们看图是一张数据库表,最左边的每条数据的物理地址,Col1和Col2是字段,如果我们查找Clo2=89,没有任何索引优化的情况,我们就需要在磁盘里一条一条的读取出来去内存比较那条数据...
  • InnoDB首先会使用主键创建一个主键B+树索引和数据文件,此外还会通过联合索引(b,c,d)生成一个索引树,同样是B+树的结构,只不过它的data部分存储的是联合索引所在行的主键值(上图叶子节点紫色背景部分),这里...
  • 今天我们讲讲MySQL索引为什么会失效,很多文章和培训机构的教程,都只会告诉你,在什么情况索引会失效。 在讲之前,还是先把一些什么情况索引会失效的结论罗列一下,然后大家结合我讲的原理再来体会一下,就会...
  • mysql索引下推

    2021-04-06 14:21:30
    索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后...
  • MySQL只学有用的–MYSQL索引原理及使用索引的基础理解索引的常见模型(字典目录的编写形式)1. 哈希表2. 有序数组3. 二叉树聊一聊InnoDB的索引模型(B+树)索引的维护(《新华字典》的目录修改)SQL索引执行过程–...
  • 索引下推优化的原理 我们先简单了解一下MySQL大概的架构: MySQL服务层负责SQL语法解析、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。 索引下推下推其实就是指将部分上层(服务层)负责的事情...
  • Mysql 5.6 版本之前是没有索引下推这个功能的,从 5.6 版本后才加上了这个优化项。所以在引出索引下推前还是先回顾没有这个功能时是怎样一种处理方式。 我们以一个真实例子来进行讲解。 在这里有张用户表 ...
  • Mysql 索引下推

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

    万次阅读 多人点赞 2019-04-19 17:19:17
    1 mysql索引知识 1.1 B+Tree索引 1.2 主键索引和普通索引的区别 1.3 唯一索引vs普通索引 2 mysql索引优化 2.1 查看索引使用情况 2.2 mysql索引使用策略 2.3 mysql索引使用原则 1 mysql索引知识 1.1 ...
  • MySQL索引:回表、索引覆盖,最左匹配原则、索引下推 MySQL索引类型 1.普通索引:最基本的索引,没有任何限制 2.唯一索引(unique index):索引列的值必须唯一,但是允许为空 3.主键索引:特殊的唯一索引,但是不允许...
  • Mysql索引原理

    2021-11-22 19:35:39
    索引是帮助mysql高效获取数据的排好序的数据结构。 二叉树、红黑树、Hash表、B树等都可以作为索引。 红黑树:插入慢,自旋;数据量大时,高度高不可控,搜索慢 Mysql索引使用B+树来实现。 B树 我们希望在大...
  • 索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。 在不使用ICP的情况,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后...
  • 索引下推优化是MySQL5.6版本中新加的功能。索引条件下推(ICP)是对MySQL使用索引从表中检索行的情况的优化。如果没有ICP,存储引擎会遍历索引以查找基表中的行,并将它们返回给MySQL服务器,由server层再做一波筛选。...
  • 面试题-Mysql索引原理

    2021-03-16 19:59:39
    大家是不是感觉弱爆了,随着工作经验的增加,我对索引有了更深入的了解,下面就来分享我眼中的索引,分享以问题的形式,从敲门到进门。一、如果数据库中出现了慢查询怎么办?Anw:加索引可以提升查找效率。...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 17,425
精华内容 6,970
关键字:

mysql索引下推原理

mysql 订阅