精华内容
下载资源
问答
  • 联合索引(也叫组合索引、复合索引、多列索引)是指对表上的多个列进行索引。联合索引的创建方法跟单个索引的创建方法一样,不同之处仅在于有多个索引列。 开讲之前我们先弄一张学生表,表数据如下: 下面我们给出...

    一、前言

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

    二、联合索引

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

    开讲之前我们先弄一张学生表,表数据如下:
    在这里插入图片描述
    下面我们给出一个需求:查询表中以字母"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项目源码

    展开全文
  • 联合索引的树结构、最左匹配原则、如何选择合适的索引列顺序、索引下推图文讲解

    联合索引

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

    例如,创建如下表,idx_name 是联合索引,索引列为 (name,age)

    CREATE TABLE `t_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `name` varchar(32) NOT NULL COMMENT '姓名',
      `age` tinyint(3) unsigned NOT NULL COMMENT '年龄',
      `gender` tinyint(3) unsigned NOT NULL COMMENT '性别:1男,0女',
      PRIMARY KEY (`id`),
      KEY `idx_name` (`name`,`age`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    假如表中有如下数据

    idnameagegender
    1LiLei181
    2HanMeimei170
    3Lucy170
    4Lili160
    5WeiHua320
    6ZhangWei251
    7Ann360
    8Lisa190
    9ZhangWei181
    10Kate171

    我们来看一下这棵索引树的结构
    在这里插入图片描述

    从图中我们可以看出,叶子节点中的键值都是按顺序存储的,即(“Ann”,36)、(“HanMeimei”,17)、(“Kate”,17)、(“LiLei”,18)、(“Lili”,16)、(“Lisa”,19)、(“Lucy”,17)、(“WeiHua”,32)、(“ZhangWei”,18)、(“ZhangWei”,25)。

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

    联合索引的最左匹配原则

    索引的目的其实就是为了提高数据查询的效率,联合索引也一样,使用联合索引时,一定要注意符合最左匹配原则:在通过联合索引检索数据时,从索引中最左边的列开始,一直向右匹配,如果遇到范围查询(>、<、between、like等),就停止后边的匹配。

    假如对字段 (a, b, c) 建立联合索引,如下查询语句可以使用到索引:

    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
    

    当然,像如下:

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

    这种查询条件书写顺序不影响对联合索引的使用,因为执行 sql 的时候,MySQL优化器会帮我们调整 where 后 a,b,c 的顺序,让我们用上索引。

    而还有一些语句是只能用到联合索引的一部分的。

    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 列的索引。

    最需要注意类似下边的这些查询

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

    这些查询语句,完全用不到 (a, b, c) 这个联合索引。

    如何选择合适的索引列顺序

    建立联合索引时,一般我们遵循的经验是:将选择性最高的列放在索引的最前列。这在某些场景下比较有用,但通常不如避免随机IO和排序那么重要。正确的顺序应该依赖于使用该索引的查询,并且同时需要考虑如何更好的满足排序和分组的需要。

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

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

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

    索引下推(索引条件下推)

    什么是索引下推(Index Condition Pushdown,ICP)呢?我们通过例子来了解下。

    假设我们想从一开始创建的表中,查询 name 以 ‘L’ 开头,并且 age 为 17 的人员信息。

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

    在不用索引下推的情况下,根据前边"最左匹配原则"描述的那样,该查询在联合索引中只有 name 列可以使用到索引,age 列是用不到索引的。在扫描 (‘name’, age) 索引树时,根据 name like 'L%' 这个条件,可以查找到 LiLeiLiliLisaLucy 四条索引数据,接下来,再根据这四条索引数据中的 id 值,逐一进行回表扫描,从聚簇索引中找到相应的行数据,将找到的行数据返回给 server 层。server 层中,再根据 age = 17 这个条件进行筛选,最终只留下 Lucy 用户的数据信息。

    不用索引下推的过程,如下图示:
    在这里插入图片描述

    在使用索引下推的情况下,存储引擎层还是先根据 name like 'L%' 这个条件,查找到 LiLeiLiliLisaLucy 四条索引数据,不过接下来不是直接进行回表操作,而是根据 age = 17 这个条件,对四条索引数据进行判断筛选,将符合条件的索引对应的 id 进行回表扫描,最终将找到的行数据返回给 server 层。(也就是我们把本应该在 server 层进行筛选的条件,下推到存储引擎层来进行筛选判断了。这个下推的前提是索引中有 age 列信息,如果是其它条件,如 gender = 0,这个即使下推下来也没用)

    使用索引下推的过程,如下图示:
    在这里插入图片描述

    由上比较可以看出,使用索引下推优化,可以有效减少回表次数,也可以减少 server 层从存储引擎层接收数据的次数,从而大大提升查询效率。

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

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

    话说,正常情况下,我们有什么理由来关闭这么好用的功能呢?

    展开全文
  • Mysql联合索引的使用索引下推、覆盖索引概念 建立一张user表,id、name、age、address。建立联合索引(name,age)。 理解索引下推、覆盖索引的概念首先要理解,联合索引的普通索引的区别。 比如(name, age) ...

    Mysql联合索引的使用索引下推、覆盖索引概念

    建立一张user表,id、name、age、address。建立联合索引(name,age)。
    CREATE TABLE user_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(10) DEFAULT NULL,
    age int(10) DEFAULT NULL,
    address varchar(10) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY name (name) USING BTREE,
    KEY name_age (name,age)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

    理解索引下推、覆盖索引的概念首先要理解,联合索引的普通索引的区别。 比如(name, age) 和 单独name索引的区别。
    语句1:
    select * from user_test where name = “aaa” and age=10
    此时用到的索引是联合索引,不过需要回表查询。
    在这里插入图片描述

    语句2:
    select * from user_test where name like “aa%” and age =10
    此时无法在aaa处定位到具体记录,也就是联合索引只用了一般。因为联合索引用完整的条件是,比如(name, age)首先通过name能定位到一类记录,在判断age。如果此时通过name定位到两类记录,那么联合索引只能用到一半,此时就会发生索引下推。
    mysql5.6前,这样的查询需要回表查询,需要一条条重新到聚簇索引定位到主键记录,然后进行判断。
    MySQL 5.6 后,索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表查询需要的次数。在联合索引查询时就做了判断。
    在这里插入图片描述
    注意:这个extra 并没有using where。说明只进行了回表,并没有在server层进行了逻辑判断。
    语句3:
    select name,age from user_test where name like “aa%” and age =10。
    此时这个语句就避免了回表查询,用到了覆盖索引和索引下推。直接在二级索引上,得到需要的数据。
    在这里插入图片描述
    其中 using where , using index . using where && using index. using index condition.区别,下面的博客讲的个人认为比较清晰。
    using where在server 层做了判断, using index 覆盖索引, using where && using index.是在非聚簇索引上,进行了查询,并且在server层做了判断,using index condition是回表查询,也用了索引下推(可能是用到了部分联合索引(name, age)只用到了name和整个联合索引(name,age)), 但是不用在server层做判断。

    https://blog.csdn.net/Saintyyu/article/details/99694649?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1.not_use_machine_learn_pai&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1.not_use_machine_learn_pai

    展开全文
  • 覆盖索引、联合索引、索引下推 阅读目录 面试三轮我倒在了一道sql题上——sql性能优化 回到目录 面试三轮我倒在了一道sql题上——sql性能优化 一、前言 最近小农在找工作,因为今年疫情的特殊原因,导致...

    覆盖索引、联合索引、索引下推

    阅读目录

    回到目录

    面试三轮我倒在了一道sql题上——sql性能优化

     

    一、前言

    最近小农在找工作,因为今年疫情的特殊原因,导致工作不是特别好找,所以一旦有面试电话,如果可以,都会去试一试,刚好接到一个面试邀请,感觉公司还不错,于是就确定了面试时间,准备了一下就去面试了。

    第一轮面试是小组组长面试,通过。
    第二轮是经理面试也是通过了。
    第三轮总监面试,前面都还有模有样,突然画风一转,面试官说:“问你最后一个问题”

    面试官:10W条数据,我要从其中查出100条不连续的数据,给你id,来查name和password进行展示,如何才能高性能的去使用?

    我:在id上建立聚簇索引,然后用 in id 来缩小表搜索范围,最后 使用条件查询 小于最大id,大于最小id,这样可以让sql速度能够比较快的展示,虽然In的性能比较低
    心里活动:雕虫小技,还最后一个问题,这样的问题再来一个吧

    只见面试官紧锁眉头,与我心里期待的表情有点不一样啊,难道是哪个环节出了问题?
    面试官:这样的性能不能达到最优化的程度,而且如果我给你的最小id是1,最大id是100000呢?

    你这就有点杠精了啊,那行吧,你是面试官你说了算
    我:既然id已经给出来了,而且只查询两个字段,用聚簇索引那么查询数据是很快的,用in id应该是可以的。

    面试官:好的,回去等通知吧
    我。。。。。

    二、后知

    于是回去后,查询资料,才知道原来面试官,真正想考的是 “覆盖索引”

    什么是覆盖索引:

    当sql语句的所求查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中 (联合索引),可以直接使用索引查询而不需要回表。这就是覆盖索引,通过使用覆盖索引,可以减少搜索树的次数,这就是 覆盖索引,在了解覆盖索引之前,我们先来看看什么是索引。

    三、什么是索引?

    我们有一个主键列为id的表,表中有字段name,并且在name上有索引

    表中 t_user 值分别为(1,张一)、(2,张二)、(3,张三)、(4,张四)、(5,张五)

    表结构如下:

    mysql> create table t_user (
    id bigint(20) not null auto_increment ,
    name varchar(255) not null,
    primary key (id),
    index index_name (name) using btree)
    engine=innodb
    default character set=utf8 collate=utf8_general_ci

    两棵树的示例示意图如下:
    在这里插入图片描述

    从图中不难看出,根据叶子节点的内容,索引类型分为主键索引和二级索引(非主键索引)。

    主键索引: 主键索引的叶子节点保存着主键即对应行的全部数据。在InnoDB里,主键索引也被称为聚簇索引(clustered index)。

    二级索引(非主键索引): 二级索引树中的叶子结点保存着索引值和主键值,当使用二级索引进行查询时,需要进行回表操作。在InnoDB里,非主键索引也被称为二级索引(secondary index)

    通过上面所讲的,我们来看看如何通过sql语句来区分 主键索引和普通索引的查询

    • select * from t_user where id=1 即主键查询方式,则只需要搜索id这棵B+树
    • select * from t_user where name=张三 即普通索引查询方式,则需要先搜索name索引树,得到id的值为3,再到id索引树搜索一次。这个过程称为回表

    也就是说,基于二级索引(非主键索引)的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。

    看到这里如果你看懂了上面的介绍,那么这里你会有一个疑问,我直接用in id不就好了吗,建立id主键索引,就可以不用回表了,速度不也就提升了吗?

    如果是 5.5 之前的版本确实不会走索引的,在 5.5 之后的版本,MySQL 做了优化。MySQL 在 2010 年发布 5.5 版本中,优化器对 in 操作符可以自动完成优化,针对建立了索引的列可以使用索引,没有索引的列还是会走全表扫描,也就是我们所说的回表。

    那么,有没有可能经过索引优化,避免回表过程呢?答应是有的

    四、覆盖索引

    sql语句如下,其中id自增,name为索引:

    mysql> create table t_user (
    id bigint(20) not null auto_increment ,
    name varchar(255) not null,
    password varchar(255) ,
    primary key (id),
    engine=innodb
    default character set=utf8 collate=utf8_general_ci

    比如有这么两句sql

    语句A: select id from user_table where name= '张三'
    语句B: select password from user_table where name= '张三'

    语句A: 因为 name索引树 的叶子结点上保存有 name和id的值 ,所以通过 name索引树 查找到id后,因此可以直接提供查询结果,不需要回表,也就是说,在这个查询里面,索引name 已经 “覆盖了” 我们的查询需求,我们称为 覆盖索引

    语句B: name索引树 上 找到 name='张三' 对应的主键id, 通过回表在主键索引树上找到满足条件的数据

    因此我们可以得知,当sql语句的所求查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中(联合索引),可以直接使用索引查询而不需要回表。这就是覆盖索引

    例如上面的语句B是一个高频查询的语句,我们可以建立(name,password)的联合索引,这样,查询的时候就不需要再去回表操作了,可以提高查询效率。

    所以关于上面的面试题我们就可以得出,使用联合索引就可以很好的回答面试官的问题(id,name,password)这样的联合索引就可以调用到覆盖索引,可以减少树的搜索次数,不再需要回表查整行记录,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

    说到了联合索引我们就不得不说联合索引中最重要的匹配原则,最左匹配原则了

    五、最左匹配原则

    最左前缀匹配原则,是非常重要的原则,mysql会从左向右进行匹配。

    例如我们定义了(name,password)两个联合索引字段,我们 使用 where name = '张三' and password = '2'索引可以生效的,当我们是颠倒了他们的顺序 使用where password = '1' and name = '王五',索引同样也是可以生效的,在mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划,我们能尽量的利用到索引时的查询顺序效率最高,所以mysql查询优化器会最终以这种顺序(where name = '张三' and password = '2')进行查询执行,就类似 我们的 order by name,password这样一种排序规则,先对张三的用户进行查询排序,在对password进行处理

    在这里插入图片描述

    比如我们要查询姓张的用户,我们的条件查询可以为 "where name like ‘张%’",但是不能是 where name like '%张%'或者是 where name like '%张',因为索引可以用于查询条件字段为索引字段,根据字段值必须是最左若干个字符进行的模糊查询,也就是需要是 '张%' 这样的添加才可以使用。

    索引的复用能力。因为可以支持最左前缀,所以当已经有了(name,password)这个联合索引后,一般就不需要单独在name上建立索引了。因此,第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

    如果既有联合查询,又有基于name,password各自的查询呢?查询条件里面只有password的语句,是无法使用(name,password)这个联合索引的,这时候你需要同时维护(name,password)、(password) 这两个索引。

    创建索引时,我们也要考虑空间代价,使用较少的空间来创建索引
    假设我们现在不需要通过name查询password了,需要通过name查询age或通过age查询name

    • 1.(name,age)联合索引+age单字段索引
    • 2.(age,name)联合索引+name单字段索引

    name字段是比age字段大的,所以,选择第一种,索引占用空间较小的一个

    六、索引下推

    上面我们说到满足最左前缀原则的时候,最左前缀可以用于在索引中定位记录。那么如果那些不符合最左前缀的部分,会怎么样呢?

    如果现在有一个需求:检索出表中“名字第一个字是张,而且没有删除的信息(is_del = 1)。SQL语句如下:

    mysql> select * from t_user where name like '张%' and is_del=1

    在MySQL 5.6之前,只能从匹配的位置一个个回表。到主键索引上找出数据行,再对比字段值

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

    根据(username,is_del)联合索引查询所有满足名称以“张”开头的索引,然后回表查询出相应的全行数据,然后再筛选出未删除的用户数据。过程如下图:

    每一个虚线箭头表示回表一次
    图一(无索引下推执行流程)
    图一

    每一个虚线箭头表示回表一次
    图二(索引下推执行流程)在这里插入图片描述

    图1跟图2的区别是,InnoDB在(name,is_del)索引内部就判断了数据是否逻辑删除,对于逻辑删除的记录,直接判断并跳过。在我们的这个例子中,只需要对ID1、ID4这两条记录回表取数据判断,就只需要回表2次

    mysql默认启用索引下推,我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制SET optimizer_switch = 'index_condition_pushdown=off';

    我们也需要注意:

    • innodb引擎的表,索引下推只能用于二级索引,因为innodb的主键索引树叶子结点上保存的是全行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果
    • 索引下推一般可用于所求查询字段(select列)不是/不全是联合索引的字段,查询条件为多条件查询且查询条件子句(where/order by)字段全是联合索引

    六、小结

    今天的内容就到这里了,我们在上面描述了数据库索引的概念,包括了覆盖索引、联合索引、索引下推,那么下次如果有面试官问你刚开始的问题,相信大家可以好好的回(dui)答(ta)一下面试官了,在sql优化中,减少回表次数,或者直接使用覆盖索引是比较重要的,尽量少地访问资源也是数据库设计的重要原则之一,谢谢大家,加油~

    展开全文
  • MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序What"s Index ?索引就是帮助RDBMS高效获取数据的数据结构。索引可以让我们避免一行一行进行全表扫描。它的价值就是可以帮助你对数据进行快速定位。...
  • 涉及到排序时,在有了联合索引的情况,充分利用联合索引,没有联合索引的情况,可以适当考虑对排序字段进行创建索引。 2. 覆盖索引 InnoDB存储引擎支持覆盖索引,覆盖索引就是:从辅助索引树上就能得到要查询的...
  • 1、覆盖索引:如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取正行数据 2、最左前缀:联合索引的最左 N 个字段...
  • 在之前《mysql索引初识》这篇文章中提到过,mysql的innodb引擎通过搜索树方式实现索引索引类型分为主键索引和二级索引(非主键索引),主键索引树中,叶子结点保存着主键即对应行的全部数据;而二级索引树中,叶子...
  • 3. 索引下推优化? 总结: 最左原则在like中也可以加速检索。 Q: 这个问题蛮有意思的,在什么时候建立了联合索引(a,b)但是却需要单独建立一个冗余索引a呢? Q: 一个字段只有0/1怎么加快搜索? Q: 加入索引的...
  • mysql主键索引、非主键索引、联合索引、覆盖索引、最左匹配、索引下推
  • 回表、覆盖索引、联合索引、最左前缀 匹配原则、索引下推 1.创建一张表, 并插入记录 create table user( -> id int not null auto_increment, -> name char(16) not null, -> age int not null, -> ...
  • 1)联合索引 2)最左前缀原则 3)覆盖索引 4)索引下推 1. 索引基础 索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点,索引就是为了提高数据查询的效率。索引可以包含一个或多个列的值...
  • 联合索引&最左匹配原则 联合索引 我们也可以同时以多个列的大小作为排序规则,也就是同时为多个列建立索引,比方说我们想让B+树按照c2c3列的大小进行排序,这个包含两层含义: 先把各个记录页按照c2列进行...
  • 联合索引 从上图可以看到: B+Tree 会优先比较 name 来确定一步应该搜索的方向,往左还是往右。 如果 name相同的时候再比较 phone。 失效原因: 但是如果查询条件没有name,就不知道第一步应该查哪个节点,因为...
  • 可以看到Extra列的值为Using index,这就表示没有进行回表,这也就说明了,在select后面为什么不要写*号,最好写上具体的列,这样有可能就可以避免回表 3、索引下推 索引下推是MySQL5.6版本推出的为了优化联合索引的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 12,689
精华内容 5,075
关键字:

联合索引和索引下推