精华内容
下载资源
问答
  • MySQL 覆盖索引、最左前缀原则、索引下推
    千次阅读
    2019-05-14 14:42:58

    1、覆盖索引

    1.1 概念

    索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引。

    1.2 判断标准

    使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

    1.3 注意

    不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列,而哈希索引、空间索引和全文索引等都不存储索引列的值,所以MySQL只能使用B+Tree索引做覆盖索引
    不同的存储引擎实现覆盖索引都是不同的,并不是所有的存储引擎都支持它们

    1.4 优点

    覆盖索引是一种非常强大的工具,能大大提高查询性能,只需要读取索引而不用读取数据有以下一些优点
    1、索引项通常比记录要小,所以MySQL访问更少的数据
    2、索引都按值的大小顺序存储,相对于随机访问记录,需要更少的I/O
    3、大多数据引擎能更好的缓存索引,比如MyISAM只缓存索引
    4、覆盖索引对于InnoDB表尤其有用,因为InnoDB使用聚集索引组织数据,如果二级索引中包含查询所需的数据,就不再需要在聚集索引中查找了

    2、最左前缀原则

    mysql建立多列索引(联合索引)有最左前缀的原则,即最左优先,如:
    字段有3列,a,b,c,建立索引:idx_a_b_c
    则生效的查询条件有:

    1. a =x
    2. a =x and b=y 或者(b =y and a=x,即顺序不影响,SQL优化器会自动处理,以下同理)
    3. a =x and c =y
    4. a=x and b=y and c=z

    3、索引下推

    “索引条件下推”,称为 Index Condition Pushdown (ICP)是mysql中一个常用的优化,尤其是当mysql需要从一张表里检索数据时。 如果没有ICP,存储引擎将会根据WHERE子句的条件遍历整个表单数据,然后返回给mysql服务器。启用ICP,如果可以通过使用索引的列来满足WHERE条件,MySQL服务器将WHERE条件的这部分推送到存储引擎。然后,存储引擎通过使用索引来确定推送的条件,并且通过这样的方式从表中读取行。 ICP可以减少存储引擎必须访问基础表的次数以及MySQL服务器必须访问存储引擎的次数。

    不使用ICP的查询SQL流程图
    在这里插入图片描述

    过程解释
    ①:MySQL Server发出读取数据的命令,这是在执行器中执行如下代码段,通过函数指针和handle接口调用存储引擎的索引读或全表表读。此处进行的是索引读。
    if (in_first_read)
    {
    in_first_read= false;
    error= (*qep_tab->read_first_record)(qep_tab); //设定合适的读取函数,如设定索引读函数/全表扫描函数
    }
    else
    error= info->read_record(info);

    ②、③:进入存储引擎,读取索引树,在索引树上查找,把满足条件的(经过查找,红色的满足)从表记录中读出(步骤④,通常有IO),从存储引擎返回⑤标识的结果。此处,不仅要在索引行进行索引读取(通常是内存中,速度快。步骤③),还要进行进行步骤④,通常有IO。

    ⑥:从存储引擎返回查找到的多条元组给MySQL Server,MySQL Server在⑦得到较多的元组。

    ⑦–⑧:⑦到⑧依据WHERE子句条件进行过滤,得到满足条件的元组。注意在MySQL Server层得到较多元组,然后才过滤,最终得到的是少量的、符合条件的元组。

    使用ICP的查询SQL流程图
    在这里插入图片描述
    过程解释
    ①:MySQL Server发出读取数据的命令,过程同图一。

    ②、③:进入存储引擎,读取索引树,在索引树上查找,把满足已经下推的条件的(经过查找,红色的满足)从表记录中读出(步骤④,通常有IO),从存储引擎返回⑤标识的结果。此处,不仅要在索引行进行索引读取(通常是内存中,速度快。步骤③),还要在③这个阶段依据下推的条件进行进行判断,不满足条件的,不去读取表中的数据,直接在索引树上进行下一个索引项的判断,直到有满足条件的,才进行步骤④,这样,较没有ICP的方式,IO量减少。

    ⑥:从存储引擎返回查找到的少量元组给MySQL Server,MySQL Server在⑦得到少量的元组。因此比较图一无ICP的方式,返回给MySQL Server层的即是少量的、符合条件的元组。

    另外,图中的部件层次关系,不再进行解释。

    更多相关内容
  • 上一节我们讲解了聚集索引和非聚集索引的区别(索引知识系列一:聚集索引与非索引详解 ),我们知道非聚集索引在查询过程中有回表的过程,这就造成了效率的下降。那如何不用回表或者减少回表以提高查询速度呢?这...

    一、前言

    上一节我们讲解了聚集索引和非聚集索引的区别(索引知识系列一:聚集索引与非索引详解 ),我们知道非聚集索引在查询过程中有回表的过程,这就造成了效率的下降。那如何不用回表或者减少回表以提高查询速度呢?这就是本章要讲的内容。

    二、联合索引

    联合索引(也叫组合索引、复合索引、多列索引)是指对表上的多个列进行索引。联合索引的创建方法跟单个索引的创建方法一样,不同之处仅在于有多个索引列。

    开讲之前我们先弄一张学生表,表数据如下:
    在这里插入图片描述
    下面我们给出一个需求:查询表中以字母"L"开头的姓名及年龄。

    1、常规的写法(回表查询)

    SELECT name,age FROM `t_user` where name like 'l%' ;
    

    这种写法,明显效率是低下的,我们用explain 分析一下:
    在这里插入图片描述
    由图中可以看出,在数据库中进行了全表扫描。下面我们看一下数据库中的执行过程。

    第一步:全表扫描数据,找出以“l”开头的主键id.
    第二步:将所有查询出来的数据每一个都回表,根据id来查询出想要的数据。
    

    2.优化写法(索引覆盖)
    因为我们要查询name和age。所以,我们对name和age建立了联合索引,建立后的索引图如下:
    在这里插入图片描述
    从图中我们可以看出,叶子节点中的键值都是按顺序存储的并且都包含了名字和年龄,即(“Ann”,36)、(“HanMeimei”,17)、(“Kate”,17)、(“LiLei”,18)、(“Lili”,16)、(“Lisa”,19)、(“Lucy”,17)、(“WeiHua”,32)、(“ZhangWei”,18)、(“ZhangWei”,25)。

    索引会先根据 name 排序,如果 name 相同,再根据 age 进行排序。

    我们对name和age建立索引后,当我们查询name和age二个字段时,直接会从索引中查出来而不需要回表查询,这种方式就是索引覆盖。执行步骤是这样的:

    第一步:使用联合索引(name,age)查询以“l”开头的数据
    第二步:在索引中取出name和age.
    

    这种方式是不是高效多了,你要是还不信,我们用explain看一下,如下图:

    EXPLAIN SELECT name,age FROM `t_user` where name like 'l%' ;
    

    在这里插入图片描述
    从图中我们看的出,使用了(name,age)索引。

    2.1 联合索引最左匹配原则

    联合索引在使用的时候一定要注意顺序,一定要注意符合最左匹配原则。

    最左匹配原则:在通过联合索引检索数据时,从索引中最左边的列开始,一直向右匹配,如果遇到范围查询(>、<、between、like等),就停止后边的匹配。

    这个定义不太好理解,我解释一下:

    假如对字段 (a, b, c) 建立联合索引,现在有这样一条查询语句:

    where a > xxx and b=yyy and c=zzz
    where a like 'xxx%' and b=yyy and c=zzz
    

    在这个条件语句中,只有a用到了索引,后面的b,c就不会用到索引。这就是“如果遇到范围查询(>、<、between、like等),就停止后边的匹配。”的意思。

    我们还是假如对字段 (a, b, c) 建立联合索引,

    1.如下查询语句可以使用到索引:

    where a = xxx
    where a = xxx and b = xxx
    where a = xxx and b = xxx and c = xxx
    where a like 'xxx%'
    where a > xxx
    where a = xxx order by b
    where a = xxx and b = xxx order by c group by a
    

    2.如下查询条件也会使用索引:

    where b = xxx and a = xxx
    where a = xxx and c = xxx and b = xxx
    

    虽然b和a的顺序换了,但是mysql中的优化器会帮助我们调整顺序。

    3.如下查询条件只用到联合索引的一部分:

    where a = xxx and c = xxx   可以用到 a 列的索引,用不到 c 列索引。
    where a like 'xxx%' and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
    where a > xxx and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
    

    4.如下查询条件完全用不到索引

    where b = xxx
    where c = xxx
    where a like '%xxx'			-- 不满足最左前缀
    where d = xxx order by a	-- 出现非排序使用到的索引列 d 
    where a + 1 = xxx	-- 使用函数、运算表达式及类型隐式转换等
    

    如何选择合适的联合索引

    1.where a = xxx and b = xxx and c = xxx 如果我们的查询是这样的,建索引时,就可以考虑将选择性高的列放在索引的最前列,选择性低的放后边。

    2.如果是 where a > xxx and b = xxx 或 where a like ‘xxx%’ and b = xxx 这样的语句,可以对 (b, a) 建立索引。

    3.如果是 where a = xxx order by b 这样的语句,可以对 (a, b) 建立索引。

    三、索引覆盖

    索引覆盖在上面我们已经介绍了。由上面的介绍我们知道,建立了联合索引后,直接在索引中就可以得到查询结果,从而不需要回表查询聚簇索引中的行数据信息。

    索引覆盖可以带来很多的好处:

    • 辅助索引不包含行数据的所有信息,故其大小远小于聚簇索引,因此可以减少大量的IO操作。
    • 索引覆盖只需要扫描一次索引树,不需要回表扫描聚簇索引树,所以性能比回表查询要高。
    • 索引中列值是按顺序存储的,索引覆盖能避免范围查询回表带来的大量随机IO操作。 判断一条语句是否用到索引覆盖:

    这个我们需要用explain查看一下。
    在这里插入图片描述
    Using index 就表示使用到了索引 , 并且所取的数据完全在索引中就能拿到,也就是用到了索引覆盖。

    四、索引下推

    索引下推是索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。

    索引下推是把本应该在 server 层进行筛选的条件,下推到存储引擎层来进行筛选判断,这样能有效减少回表。

    举例说明:

    首先使用联合索引(name,age),现在有这样一个查询语句:

    select *  from t_user where name like 'L%' and age = 17;
    

    这条语句从最左匹配原则上来说是不符合的,原因在于只有name用的索引,但是age并没有用到。

    不用索引下推的执行过程:

    第一步:利用索引找出name带'L'的数据行:LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步:再根据这四条索引数据中的 id 值,逐一进行回表扫描,从聚簇索引中找到相应的行数据,将找到的行数据返回给 server 层。
    第三步:在server层判断age = 17,进行筛选,最终只留下 Lucy 用户的数据信息。
    

    使用索引下推的执行过程:

    第一步:利用索引找出name带'L'的数据行:LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步:根据 age = 17 这个条件,对四条索引数据进行判断筛选,最终只留下 Lucy 用户的数据信息。
    (注意:这一步不是直接进行回表操作,而是根据 age = 17 这个条件,对四条索引数据进行判断筛选)
    第三步:将符合条件的索引对应的 id 进行回表扫描,最终将找到的行数据返回给 server 层。
    

    比较二者的第二步我们发现,索引下推的方式极大的减少了回表次数。

    索引下推需要注意的情况:

    下推的前提是索引中有 age 列信息,如果是其它条件,如 gender = 0,这个即使下推下来也没用

    开启索引下推:

    索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。默认情况下,索引下推处于启用状态。我们可以使用如下命令来开启或关闭。

    set optimizer_switch='index_condition_pushdown=off'; 	-- 关闭索引下推
    set optimizer_switch='index_condition_pushdown=on';		-- 开启索引下
    

    五、结尾

    好了,本章就讲到这里吧,下一章,我们对所有的知识进行一下总结。

    另外大家帮忙关注我,每天更新优质内容。关注我有大量学习资料和学习视频赠送。
    在这里插入图片描述
    扫二维码关注公众号【Java程序员的奋斗路】可领取如下学习资料:
    1.1T视频教程(大约有100多个视频):涵盖Javaweb前后端教学视频、机器学习/人工智能教学视频、Linux系统教程视频、雅思考试视频教程,android.等
    2.项目源码:20个JavaWeb项目源码

    展开全文
  • 覆盖索引 在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引,索引类型分为主键索引二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引...

    覆盖索引

    在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引,索引类型分为主键索引和二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引树中,叶子结点保存着索引值和主键值,当使用二级索引进行查询时,需要进行回表操作。假如我们现在有如下表结构

    CREATE TABLE `user_table` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `username` varchar(255) NOT NULL,
      `password` varchar(255) DEFAULT NULL,
      `age` int(11) unsigned Not NULL,
      PRIMARY KEY (`id`),
      key (`username`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8
    

    执行语句(A) select id from user_table where username = 'lzs'时,因为username索引树的叶子结点上保存有username和id的值,所以通过username索引树查找到id后,我们就已经得到所需的数据了,这时候就不需要再去主键索引上继续查找了。
    执行语句(B) select password from user_table where username = 'lzs'时,流程如下

    1、username索引树上找到username=lzs对应的主键id
    2、通过回表在主键索引树上找到满足条件的数据

    由上面可知,当sql语句的所求查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中,可以直接使用索引查询而不需要回表。这就是覆盖索引,通过使用覆盖索引,可以减少搜索树的次数,是常用的性能优化手段。
    例如上面的语句B是一个高频查询的语句,我们可以建立(username,password)的联合索引,这样,查询的时候就不需要再去回表操作了,可以提高查询效率。当然,添加索引是有维护代价的,所以添加时也要权衡一下。

    前缀索引

    mysql的b+树索引遵循“最左前缀”原则,继续以上面的例子来说明,为了提高语句B的执行速度,我们添加了一个联合索引(username,password),特别注意这个联合索引的顺序,如果我们颠倒下顺序改成(password,username),这样查询能使用这个索引吗?答案是不能的!这是最左前缀的第一层含义:联合索引的多个字段中,只有当查询条件为联合索引的一个字段时,查询才能使用该索引。
    现在,假设我们有一下三种查询情景:
    1、查出用户名的第一个字是“张”开头的人的密码。即查询条件子句为"where username like '张%'"
    2、查处用户名中含有“张”字的人的密码。即查询条件子句为"where username like '%张%'"
    3、查出用户名以“张”字结尾的人的密码。即查询条件子句为"where username like '%张'"

    以上三种情况下,只有第1种能够使用(username,password)联合索引来加快查询速度。这就是最左前缀的第二层含义:索引可以用于查询条件字段为索引字段,根据字段值最左若干个字符进行的模糊查询。

    维护索引需要代价,所以有时候我们可以利用“最左前缀”原则减少索引数量,上面的(username,password)索引,也可用于根据username查询age的情况。当然,使用这个索引去查询age的时候是需要进行回表的,当这个需求(根据username查询age)也是高频请求时,我们可以创建(username,password,age)联合索引,这样,我们需要维护的索引数量不变。

    创建索引时,我们也要考虑空间代价,使用较少的空间来创建索引
    假设我们现在不需要通过username查询password了,相反,经常需要通过username查询age或通过age查询username,这时候,删掉(username,password)索引后,我们需要创建新的索引,我们有两种选择
    1、(username,age)联合索引+age字段索引
    2、(age,username)联合索引+username单字段索引
    一般来说,username字段比age字段大的多,所以,我们应选择第一种,索引占用空间较小。

    索引下推

    对于user_table表,我们现在有(username,age)联合索引
    如果现在有一个需求,查出名称中以“张”开头且年龄小于等于10的用户信息,语句C如下:"select * from user_table where username like '张%' and age > 10".
    语句C有两种执行可能:
    1、根据(username,age)联合索引查询所有满足名称以“张”开头的索引,然后回表查询出相应的全行数据,然后再筛选出满足年龄小于等于10的用户数据。过程如下图。

     

    图片来自课程文章

     

    2、根据(username,age)联合索引查询所有满足名称以“张”开头的索引,然后直接再筛选出年龄小于等于10的索引,之后再回表查询全行数据。过程如下图。

     

    图片来自课程文章


    明显的,第二种方式需要回表查询的全行数据比较少,这就是mysql的索引下推。mysql默认启用索引下推,我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制

    SET optimizer_switch = 'index_condition_pushdown=off';
    
    • 注意点:
      1、innodb引擎的表,索引下推只能用于二级索引。

      就像之前提到的,innodb的主键索引树叶子结点上保存的是全行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果。

      2、索引下推一般可用于所求查询字段(select列)不是/不全是联合索引的字段,查询条件为多条件查询且查询条件子句(where/order by)字段全是联合索引。

      假设表t有联合索引(a,b),下面语句可以使用索引下推提高效率
      select * from t where a > 2 and b > 10;

    展开全文
  • 简单易懂的MySQL覆盖索引、前缀索引、索引下推

    千次阅读 多人点赞 2021-07-17 20:08:04
    文章目录前言聚集索引/非聚集索引 前言 聚集索引/非聚集索引

    前言

    索引的出现是为了提高数据查询效率,像书的目录一样。对于数据库的表而言,索引其实就是“目录”。

    关于MySQL的系列文章,请跳转至 MySQL专栏


    常见的索引类型

    • 哈希表
    • 有序数组
    • 搜索树

    哈希表

    哈希表是以 KV 形式存储数据的结构,只要输入key,就可以找到对应的 value,思路很简单,就是放到数组中,根据 hash 算法计算出key在数组中确定的索引位置,把 value 存储在这个索引位置。当两个不同的 key 经过 hash 算法计算出的 索引位置相同时,就出现了哈希碰撞,这时用一个链表解决。查询的时候,根据 hash 算法计算出 value 所在数组的索引位置,然后按链表顺序遍历,直到找到 key 对应的 value

    哈希表中存储数据不是按照 key 的顺序递增排序的,如果需要做范围查询的话,使用哈希结构就不得不全部扫描一遍了。

    所以,哈希结构适用于等值查询的场景,比如 NoSQL


    有序数组

    有序数组不管是等值查询还是范围查询场景中性能都很好。等值查询时,可以根据二分查找法定位元素,时间复杂度是 O(logn);如果是范围查询时,可以先用二分法查找到范围的下限,再向右遍历,知道查到第一个不符合范围的值,退出循环。

    如果只看查询效率,有序数组是最好的数据结构了。但是,如果要新增数据,往其中数组中插入一个元素,要挪动后面所有记录,成本太高。所以,有序数组只适用于静态存储引擎,比如不会再修改,只需要查询的数据。


    搜索树

    二叉搜索树的节点是有序的,左节点都小于根节点,右节点都大于根节点。二叉搜索树如果出现极端的情况,性能极差,所以,平衡二叉树的查询性能更好。但是平衡二叉树的结构,每次读数据块都需要从磁盘加载到内存,为了减少 I/O 操作,要尽可能的少读磁盘,平衡多叉树可以解决这个问题,每个节点有多个子节点,这样树的高度相较于二叉树来说,低的多,查询过程中就会访问较少的数据块,从而提高查找效率。


    聚簇索引/非聚簇索引

    众所周知,InnoDB采用的是B+Tree这种数据结构,B+Tree 也是一种平衡多叉树。InnoDB 表中的数据都是根据主键顺序以索引的形式存放的,每一个索引在 InnoDB 中对应一棵 B+Tree ,索引类型又分为:主键索引和非主键索引。

    在这里插入图片描述

    主键索引的叶子节点存的是整行数据,在 InnoDB 中,主键索引也被称为聚簇索引。

    非主键索引的叶子节点存的是主键的值,在 InnoDB 中,非主键索引也被称为二级索引。

    基于主键索引和普通索引的查询有什么区别?

    如果 SQL 中的 where 条件是主键,则只需要搜索主键索引的B+Tree即可查询该数据;

    如果 SQL 中的 where 条件是普通索引,则先搜索普通索引的 B+Tree,得到主键的值,再根据主键的值搜索主键索引的B+Tree查询到需要的数据,这个过程称为回表。

    所以,基于非主键索引的查询需要多扫描一棵树,因此,在应用中应该尽量使用主键查询。


    为什么说主键的长度要尽量小并且是自增的?

    B+Tree 在插入新节点时,如果插入的节点值比当前树中的值都大时,则直接插入即可。而如果插入的节点值在当前树中的值处于中间位置,就需要挪动部分数据,空出位置再插入新数据。而如果要插入数据页满了,根据B+Tree 的算法,需要申请一个新的数据页,再挪动部分数据,这个过程叫做页分裂。有分裂的情况就有合并的情况,当从树中删除一个节点时,剩余的数据在数据页中的利用率很低的时候,会将数据页做合并操作,就是分裂的逆过程。

    而之所以要求主键自增是因为,如果每次要插入的节点都比当前树中最大值还大,都是追加操作,则不会触发叶子节点的分裂。

    要求主键的长度要尽量短,上面我们说过,每个非聚集索引的B+Tree中存的是元素对应的主键,如果主键越长,占用的内存空间越大;而主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间就越小。


    覆盖索引

    这条 SQL中 select * from T where k between 3 and 5,id 为主键,k 为普通索引。这条SQL的执行流程是什么样的?

    • k是普通索引,遍历k索引树,找到范围的下限3,取得对应的主键,再到主键索引树中查询主键对应的数据行。
    • 再回到k索引树取下一个值,判断是不是在要查询数据的范围内,如果在,再取到对应的主键,再去主键索引树查询到对应的数据行。
    • 直到找到超出查找的数据范围时,查找结束

    上述过程中不可避免的出现了回表操作,因为数据行只有主键索引上存在,只能去遍历主键索引树。

    如果执行的 SQL 是:select id from T where k between 3 and 5

    • 只需要查到遍历 k 索引树从叶子节点查到 id 的值,直到找到超出查找的数据范围时,查找结束,不需要回表操作

    在这个查询中,索引 k 已经覆盖了查询需求,称为覆盖索引。由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。


    前缀索引

    有一种索引是联合索引,指的是由多个字段组成的索引,也称复合索引。

    如果有一个联合索引(name,age),下面通过实例说明最左前缀原则。

    根据上面的联合索引,现在有如下需求:

    1. 要求查出所有名字是“张三”的人
    2. 要求查出所有姓“李”的人
    3. 要求查出所有名字中最后一个字是 “山”的人
    4. 要求查出所有年龄为 18 的人

    1 的SQL条件写成:where name = "张三",可以使用(name,age)联合索引查询,也就是从索引的最左侧开始,这个最左前缀可以是联合索引的最左N个字段。

    2 的SQL条件:where name = "李%",这时,也可以使用联合索引。

    3 的SQL条件:where name = "%山",这时,不可以使用联合索引,会导致索引失效

    4 的SQL条件:where age = 18,这时,不可以使用联合索引,因为查询条件不是联合索引的最左N个字段。

    综上所述,在建立联合索引时,要安排好索引的字段顺序。否则,很有可能导致索引失效。对于(name,age)联合索引,符合条件的查询条件是:

    • where name = …
    • where name = … and age = …
    • where name = … and age in (… , …)
    • order by name,age
    • where name = … order by age

    还有很多情况会导致索引失效,看看都有哪些情况:

    • or 前后查询条件不都是索引子段(or 前后查询字段都是索引时会生效)
    • 未遵循最左N个字段
    • 模糊查询 like 以 % 开头
    • 需要类型转换
    • where 中索引列有计算
    • where 中索引列用到了函数
    • 索引字段上使用 not , <> , != (不等于操作符会触发全表扫描)
    • 当全表扫描速度比索引速度快时

    索引下推

    上面讲解了最左前缀原则,如果联合索引中不符合最左前缀原则的部分,会怎么样呢?

    我们还是以联合索引(name, age)为例,要求检索出表中 “名字第一个字是张,而且年龄是10岁的所有男孩”。那么,SQL语句是这么写的:

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

    这个语句在搜索索引树的时候可以使用联合索引,先覆盖了 name 索引,找到第一个name字段满足"张%"的记录后,开始回表查询数据行,再从联合索引树上找下一个字段值做对比。

    在MySQL 5.6之前,只能从第一个满足name条件的值开始一个个回表。到主键索引上找出数据行,再对比字段值。

    而MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

    如下图,分别是 MySQL 5.6 前 和 5.6 后的执行流程图。

    无索引下推执行流程图
    索引下推执行流程
    第一个图中,也就是无索引下推的流程图中,这个过程InnoDB并不会去看age的值,只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此,需要回表4次。

    使用了索引下推的流程图中,InnoDB在(name,age)索引内部就判断了age是否等于10,对于不等于10的记录,直接判断并跳过。在我们的这个例子中,只需要对ID4、ID5这两条记录回表取数据判断,就只需要回表2次。

    展开全文
  • 索引覆盖 最左前缀 索引下推 索引覆盖 总结问题 什么是索引覆盖? 怎么用到索引覆盖 索引覆盖的情况,using index ; using index using where select * from T where k betwee 3 and 5 这条语句的执行流程是...
  • 通俗的讲就是,如果索引的列在 select 所需获得的列中(因为在 mysql 中索引是根据索引列的值进行排序的,所以索引节点中存在该列中的部分值)或者根据一次索引查询就能获得记录就不需要回表,如果 select 所需获得...
  • MySQL回表、索引覆盖、索引下推

    千次阅读 2020-06-09 00:55:00
    创建一张表,并创建一个自增主键索引和一个组合索引 1 2 3 4 5 6 7 8 9 10 CREATE TABLE index_opt_test ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(11) DEFAULT NULL, title varchar(11) DEFAULT NULL...
  • 今天看到一篇关于数据库优化和索引写的非常好的文章,分享一下.文章转载至:https://www.cnblogs.com/Leo_wl/p/13093705.html 一、前言 最近小农在找工作,因为今年疫情的特殊原因,导致工作不是特别好找,所以一旦...
  • select id,name where name='shenjian' select id,name,sexwhere name='shenjian' 多查询了一个属性,为何检索过程完全不同?...什么是索引下推? 这些,这是今天要分享的内容。 画外音:本文试验基于My...
  • 二、覆盖索引 三、前缀索引 四、索引下推 一、回表详解 建立如下表: mysql> create table T ( ID int primary key, k int NOT NULL DEFAULT 0, s varchar(16) NOT NULL DEFAULT '', index k(k) )...
  • 1、覆盖索引:如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取正行数据 2、最左前缀:联合索引的最左 N 个字段...
  • 快速理解 Mysql 回表 索引覆盖 索引下推回表操作索引覆盖索引下推 回表操作 Mysql 每页大小为16K(B+树结构,所以16K足以),关于主键索引辅助索引的结构这里简单说一下。 InnoDB 主键(聚簇索引):仅在叶子节点...
  • 覆盖索引(不用回标) 索引下推(减少回表次数) 存储引擎与底层实现的数据结构 数据结构 - 索引怎么选择合适的数据结构?中分析过能作为索引的数据结构主要有散列表(Hash表)、红黑树、跳表、B+树(B树)以及...
  • MySQL-索引 回表 覆盖索引 索引下推 https://www.cnblogs.com/lisq/p/12634457.html 索引类型 索引类型分为主键索引非主键索引。(一定要牢记,是怎么存储数据的); 主键索引的叶子节点存的是整行数据。在 InnoDB 里...
  • 索引失效like 以%开头,索引失效组合索引,不是使用第一列索引,索引失效数据类型出现隐式转化,索引失效其它情况不推荐使用索引的情况覆盖索引组合索引最左匹配原则注意组合索引数据结构索引下推
  • 索引覆盖 索引下推 索引合并 查询的字段在命中的联合索引里 查询条件命中了联合索引的第一个字段,后续条件可以根据联合索引其它字段直接进行过滤 对多个索引进行分别扫描,然后根据结果进行集合操作...
  • \|/ +--------------+ | 执行器 | +--------------+ 我们都知道索引其中一个最主要作用就是加快数据的访问,那么回表、索引覆盖、最左匹配、索引下推 都是mysql的内部优化方式,部分的功能是Mysql5.6的版本上推出的...
  • 2、覆盖索引 MySQL innodb存储引擎中,每个表,有且仅有一个聚簇索引 也就是说,所有的数据都在聚集索引上,那么其他的普通索引(二级索引),他们组成的B+树,存放的就是主键的值,想要通过普通索引查询数据,就...
  • 索引覆盖 索引覆盖指的是查询字段包含在索引列里,直接通过索引树就能查询出来,不需要通过回表操作,减少磁盘IO次数。 例子: 某表t_test组合索引(name,age) SQL: select name,age from t_test where name='a...
  • 1. 索引种类 创建数据表& 插入数据 create table user( id int(10) auto_increment, name varchar(30), age tinyint(4), primary key (id), index idx_age (age) USING BTREE )engine=innodb charset=...
  • 覆盖索引 在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引,索引类型分为主键索引二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引...
  • 文章目录准备索引回表覆盖索引索引下推 准备 实验平台:MySQL 5.6.35,Engine:innodb innodb引擎使用B+树维护 聚簇索引 非聚簇索引: 聚簇索引:聚簇索引树非叶子节点存储主键值,叶子节点存储整行数据(包括...
  • 什么是“索引下推”?这种方法带来了什么好处? 通过两个alter 语句重建索引k,以及通过两个alter语句重建主键索引是否合理? round1:覆盖索引 查询数据时如何减少回表的次数? 回答这个问题前,我们要知道什么是回...
  •  索引的B+树上包含了所要查询的所有字段,此时不需要再回表查询,这个过程称之为索引覆盖,推荐使用索引覆盖 比如table表有a,b,c,d,e个字段,其中a是主键,另外有一联合索引idx_bcd (b,c,d) 查询select * from ...
  • MySQL索引:回表、索引覆盖,最左匹配原则、索引下推 MySQL索引类型 1.普通索引:最基本的索引,没有任何限制 2.唯一索引(unique index):索引列的值必须唯一,但是允许为空 3.主键索引:特殊的唯一索引,但是不允许...
  • 文章目录1 回表2 最左匹配原则3 索引覆盖4 索引下推 1 回表 这先要从InnoDB的索引实现说起,InnoDB有两大类索引: 聚集索引(clustered index) 普通索引(secondary index) InnoDB聚集索引普通索引有什么差异? ...
  • Mysql联合索引的使用索引下推覆盖索引概念 建立一张user表,id、name、age、address。建立联合索引(name,age)。 理解索引下推覆盖索引的概念首先要理解,联合索引的普通索引的区别。 比如(name, age) ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 34,011
精华内容 13,604
关键字:

索引覆盖和索引下推