精华内容
下载资源
问答
  • MySQL优化:如何避免回表查询?什么是索引覆盖?

    我们知道Mysql底层使用的B+树来存储索引的,而且数据都存在叶子节点上。对于innodb来说,它的主键索引和行记录是存储在一起的,因此叫做聚集索引。

    ps:MyISAM的行记录是单独存储的,不和索引在一起,因此MYISAM是没有聚集索引的。

    除了聚集索引,其他的索引都叫非聚集索引。(普通索引,唯一索引等)

    另外需要注意的,在innodb中有且只有一个聚集索引。它有三种情况:

    • 若表中存在主键,那主键索引就是聚集索引。
    • 若表中没有主键,那第一个非用空的唯一索引就是聚集索引。
    • 否则,就会隐式的定义一个rowid作为聚集索引。

    为了方便理解,下边以 InnoDB 的主键索引和普通索引为例,看下它们的存储结构。

    创建一张表,结构如下,并添加几条记录(张三,李四,王五,孙七):

      CREATE TABLE `student` (
        `id` int(11) NOT NULL,
        `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
        `age` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `idx_stu` (`name`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
      
      insert into student(id,name,age) values(1,'zs',12);
      insert into student(id,name,age) values(5,'ls',14);
      insert into student(id,name,age) values(9,'ww',12);
      insert into student(id,name,age) values(11,'sq',13);
    

    在 InnoDB 中,主键索引的叶子节点存储的是主键和行记录,而普通索引的叶子节点存储的是主键(对于 MyISAM来说主键索引的叶子节点存储的是主键和对应行记录的指针,普通索引的叶子节点存储的是当前索引列和对应行记录的指针)。

    在这里插入图片描述
    两个B+树索引分别如上图:

    (1)id为PK,聚集索引,叶子节点存储行记录;

    (2)name为KEY,普通索引,叶子节点存储PK值,即id;

    什么是回表查询?

    从上面的索引存储结构,我们可以看到,在主键索引树上,通过主键就可以一次性查出我们所需要的数据,速度非常快。

    因为主键和行记录就存储在一起,定位到了主键,也就定位到了所要找的记录,当前行的所有字段都在这(这也是我们为什么说,在创建表的时候,最好是创建一个主键,查询时也尽量用主键来查询)。

    对于普通索引,如例子中的name,则需要根据name的索引树(非聚集索引)找到叶子节点对应的主键,然后在通过主键索引树查询一遍,才可以得到要找的记录,这就是回表查询

    什么是索引覆盖?

    对于回表查询来说,无疑会降低查询的效率。那么有什么办法让他不回表呢?

    • 那就是索引覆盖

    什么是索引覆盖,就是不管是SQL-Server官网,还是MySQL官网,都表达了:只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。

    三、如何实现索引覆盖?

    常见的方法是:将被查询的字段,建立到联合索引里去。

    还是以上边的表为例,现在 zs 对应的索引树上边,只有它本身和主键的数据,并不能覆盖到 age 字段。那么,我们就可以创建联合索引,如 KEY(name,age)。并且,查询的时候,显式的写出联合索引对应的字段(name和age)。

    创建联合索引如下,

      KEY `idx_stu` (`name`,`age`)
    

    查询语句修改如下,

      -- 覆盖联合索引中的字段
      select id,name,age from student where name='zs' and age=12;
    

    这样,当查询索引树的时候,就不用回表,可以一次性查出所有的字段。对应的索引树结构如下:
    在这里插入图片描述
    PS:图中,联合索引中的字段(name,age)都应该出现在索引树上的,这里为了画图方便,且因数据量太小,没有画出来。只表现出了:叶子节点存储了所有的联合索引字段。

    四、哪些场景可以利用索引覆盖来优化SQL?

    场景1:全表count查询优化
    在这里插入图片描述
    原表为:

    user(PK id, name, sex);

    直接:

    select count(name) from user;

    不能利用索引覆盖。

    添加索引:

    alter table user add key(name);

    就能够利用索引覆盖提效。

    场景2:列查询回表优化

    select id,name,sex … where name=‘shenjian’;

    这个例子不再赘述,将单列索引(name)升级为联合索引(name, sex),即可避免回表。

    场景3:分页查询

    select id,name,sex … order by name limit 500,100;

    将单列索引(name)升级为联合索引(name, sex),也可以避免回表。

    InnoDB聚集索引普通索引回表索引覆盖,希望这1分钟大家有收获。

    提示,如果你不清楚explain结果Extra字段为Using index的含义,请阅读前序文章:《如何利用工具,迅猛定位低效SQL?

    展开全文
  • 保证给你讲明白,看不懂你砍我。...所以不需要回表操作,如果我们select出来的某列,不在该联合索引的叶子节点上(比如上表的e列),那就需要根据对应索引值,去聚簇索引树上回表查询对应的e列值了。

    保证给你讲明白,看不懂你砍我。

    首先弄明白两个概念-大概说一下,具体的网上都有:
    覆盖索引-select b,c,d from t1 ;
    select b,c,d from t1 where b=1 and c =1 and d=1 ;
    select a,b,c,d from t1 where b=1 and c =1 and d=1;
    【a是主键,给bcd建立联合索引】,如上几个sql,select出来的内容,和where条件字段,刚好和建立的索引一致.
    回表-使用非聚簇索引进行查找数据时,需要根据主键值去聚簇索引中再查找一遍完整的用户记录,这个过程叫做回表.

    上面两个概念清楚以后,继续往下看。
    新建一张测试表 t1.如下。
    备注:a列设置为主键列,bcd列建立联合索引,其他列暂时没有建立索引。

    abcde
    1151617x
    2252627x
    3353637x
    4454647x
    5555657x
    6656667x
    7757677x
    8858687x

    执行sql,如下:
    select b,c,d from t1 where b=15 and c=16 and d=17
    这里使用了 覆盖索引。我们看下他的B+树。
    在这里插入图片描述
    分析上图,满足条件的结果,是不是完整的显示在了叶子节点上???【注意:我们select查询的内容不是全表,是bcd三个字段,在叶子节点上,这3个字段是不是都已经有对应的值了。】
    即使我们sql写这样子:
    select a,b,c,d from t1 where b=15 and c=16 and d=17
    a是主键列,但是在联合索引的叶子节点上,页存储了对应的主键值,所以依旧不需要回表操作。

    总结:使用覆盖索引,我们需要select出来的列,都已经存在了索引树的叶子节点上。所以不需要回表操作,如果我们select出来的某列,不在该联合索引的叶子节点上(比如上表的e列),那就需要根据对应索引值,去聚簇索引树上回表查询对应的e列值了。

    展开全文
  • ,也可以避免回表。   InnoDB聚集索引普通索引 , 回表 , 索引覆盖 ,希望这 1分钟 大家有收获。   提示,如果你不清楚explain结果Extra字段为 Using index 的含义,请阅读前序文章: 《 如何利用工具,迅猛定位...

    迅猛定位低效SQL?》留了一个尾巴:

    select id,name where name='shenjian'

    select id,name,sex where name='shenjian'

    多查询了一个属性,为何检索过程完全不同?

     

    什么是回表查询?

    什么是索引覆盖?

    如何实现索引覆盖?

    哪些场景,可以利用索引覆盖来优化SQL?

     

    这些,这是今天要分享的内容。

    画外音:本文试验基于MySQL5.6-InnoDB。

     

    一、什么是回表查询?

     

    这先要从InnoDB的索引实现说起,InnoDB有两大类索引:

    • 聚集索引(clustered index)

    • 普通索引(secondary index)

     

    InnoDB聚集索引和普通索引有什么差异?

    InnoDB聚集索引的叶子节点存储行记录,因此, InnoDB必须要有,且只有一个聚集索引:

    (1)如果表定义了PK,则PK就是聚集索引;

    (2)如果表没有定义PK,则第一个not NULL unique列是聚集索引;

    (3)否则,InnoDB会创建一个隐藏的row-id作为聚集索引;

    画外音:所以PK查询非常快,直接定位行记录。

     

    InnoDB普通索引的叶子节点存储主键值。

    画外音:注意,不是存储行记录头指针,MyISAM的索引叶子节点存储记录指针。

     

    举个栗子,不妨设有表:

    t(id PK, name KEY, sex, flag);

    画外音:id是聚集索引,name是普通索引。

     

    表中有四条记录:

    1, shenjian, m, A

    3, zhangsan, m, A

    5, lisi, m, A

    9, wangwu, f, B

    两个B+树索引分别如上图:

    (1)id为PK,聚集索引,叶子节点存储行记录;

    (2)name为KEY,普通索引,叶子节点存储PK值,即id;

     

    既然从普通索引无法直接定位行记录,那普通索引的查询过程是怎么样的呢?

    通常情况下,需要扫码两遍索引树。

     

    例如:

    select * from t where name='lisi';

    是如何执行的呢?

    粉红色路径,需要扫码两遍索引树:

    (1)先通过普通索引定位到主键值id=5;

    (2)在通过聚集索引定位到行记录;

     

    这就是所谓的回表查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。

     

    二、什么是索引覆盖(Covering index)

    额,楼主并没有在MySQL的官网找到这个概念。

    画外音:治学严谨吧?

     

    借用一下SQL-Server官网的说法。

    MySQL官网,类似的说法出现在explain查询计划优化章节,即explain的输出结果Extra字段为Using index时,能够触发索引覆盖。

     

    不管是SQL-Server官网,还是MySQL官网,都表达了:只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表,速度更快。

     

    三、如何实现索引覆盖?

    常见的方法是:将被查询的字段,建立到联合索引里去。

     

    仍是《迅猛定位低效SQL?》中的例子:

    create table user (

    id int primary key,

    name varchar(20),

    sex varchar(5),

    index(name)

    )engine=innodb;

     

    第一个SQL语句:

    select id,name from user where name='shenjian';

    能够命中name索引,索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表,符合索引覆盖,效率较高。

    画外音,Extra:Using index

     

    第二个SQL语句:                     

    select id,name,sex from user where name='shenjian';

    能够命中name索引,索引叶子节点存储了主键id,但sex字段必须回表查询才能获取到,不符合索引覆盖,需要再次通过id值扫码聚集索引获取sex字段,效率会降低。

    画外音,Extra:Using index condition

    如果把(name)单列索引升级为联合索引(name, sex)就不同了。

    create table user (

    id int primary key,

    name varchar(20),

    sex varchar(5),

    index(name, sex)

    )engine=innodb;

    可以看到:

    select id,name ... where name='shenjian';

    select id,name,sex ... where name='shenjian';

    都能够命中索引覆盖,无需回表。

    画外音,Extra:Using index

    四、哪些场景可以利用索引覆盖来优化SQL?

    场景1:全表count查询优化

    原表为:

    user(PK id, name, sex);

     

    直接:

    select count(name) from user;

    不能利用索引覆盖。

     

    添加索引:

    alter table user add key(name);

    就能够利用索引覆盖提效。

     

    场景2:列查询回表优化

    select id,name,sex ... where name='shenjian';

    这个例子不再赘述,将单列索引(name)升级为联合索引(name, sex),即可避免回表。

     

    场景3:分页查询

    select id,name,sex ... order by name limit 500,100;

    将单列索引(name)升级为联合索引(name, sex),也可以避免回表。

     

    InnoDB聚集索引普通索引回表索引覆盖,希望这1分钟大家有收获。

     

    提示,如果你不清楚explain结果Extra字段为Using index的含义,请阅读前序文章:《如何利用工具,迅猛定位低效SQL?

    架构师之路-分享可落地技术

    相关推荐

    缓冲池(buffer pool),这次彻底懂了!

    写缓冲(change buffer),这次彻底懂了!

    数据库索引,到底是什么做的?》干货

    展开全文
  • 回表:在数据中,当查询数据的时候,在索引中查找索引后,获得该行的rowid,根据rowid再查询表中数据,是回表。  在数据库中,数据的存储都是以块为单位的,称为数据块,表中每一行数据都有的地址标志ROWID。每次...
  • * 如果设置了主键,则主键就是聚簇索引 * 如果没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引 * 以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引 其他 非聚簇 中有四条记录...

    聚簇索引

    • * 如果表设置了主键,则主键就是聚簇索引
    • * 如果表没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引
    • * 以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引

    其他 非聚簇

     

    表中有四条记录:

      1, shenjian, m, A

      3, zhangsan, m, A

      5, lisi, m, A

      9, wangwu, f, B

     

     

    聚集索引(存储行记录)           普通索引(叶子节点存储聚簇索引值)

     

    聚簇索引查询会很快,因为可以直接定位到行记录

    id是聚簇索引,name是普通索引。

    普通索引  需要扫码两遍索引树

    (1)先通过普通索引定位到主键值id=5;

    (2)在通过聚集索引定位到行记录;

    这就是所谓的回表查询,先定位主键值,再定位行记录,它的性能较扫一遍索引树更低。

     

    如何实现覆盖索引

    常见的方法是:将被查询的字段,建立到联合索引里去。

     

    哪些场景适合使用索引覆盖来优化SQL

    全表count查询优化

    1

    2

    3

    4

    5

    6

    mysql> create table user(

      -> id int(10) auto_increment,

      -> name varchar(30),

      -> age tinyint(4),

      -> primary key (id),

      -> )engine=innodb charset=utf8mb4;

    例如:select count(age) from user;

     使用索引覆盖优化:创建age字段索引

    1

    create index idx_age on user(age);

     

    列查询回表优化

    前文在描述索引覆盖使用的例子就是

    例如:select id,age,name from user where age = 10;

    使用索引覆盖:建组合索引idx_age_name(age,name)即可

     

    分页查询

    例如:select id,age,name from user order by age limit 100,2;

    因为name字段不是索引,所以在分页查询需要进行回表查询,此时Extra为Using filesort文件排序,查询性能低下。

    使用索引覆盖:建组合索引idx_age_name(age,name)

     

     参考https://www.jb51.net/article/180267.htm

    展开全文
  • 回表

    千次阅读 多人点赞 2019-08-28 17:03:35
    面试官:刚刚你提到主键索引查询只会查一次,而非主键索引需要回表查询多次。(后来我才知道,原来这个过程叫做回表)是所有情况都是这样的吗?非主键索引一定会查询多次吗? 我:(额、这个问题我回答的不好,后来...
  • mysql回表

    千次阅读 2020-11-25 16:06:36
    当mysql查询时,使用非聚簇索引(也叫二级索引,辅助索引) 查到相应的叶子节点获取主键值,然后通过 主键索引(聚簇索引) 再查到相应的数据行信息,找到主键后通过聚簇索引 找到相应数据行的过程叫做回表。...
  • mysql 回表

    万次阅读 多人点赞 2019-07-15 15:22:43
    今天,逛论坛发现一个专业名词回表。一开始,听着也是一头雾水。特地查了下资料,记录下mark。 什么是回表? 简单来说就是数据库根据索引(非主键)找到了指定的记录所在行后,还需要根据主键再次到数据块里获取...
  • mysql回表查询

    千次阅读 2019-10-24 23:52:32
    order by和group by这两种情况要避免回表查询。那么先来了解下什么是回表查询。 以下文章来源于https://www.cnblogs.com/yanggb/p/11252966.html 感谢作者 了解一下MySQL中的回表查询与索引覆盖。 回表查询 ...
  • 要写出高效的SQL,那么必须必须得清楚SQL执行路径,介绍如何提高SQL性能的文章很多,这里不再赘述,本人来谈谈如何从 减少SQL回表次数 来提高查询性能,因为回表将导致扫描更多的数据块。 我们大家都知道,数据库表...
  • 福哥答案2020-07-12: 回表:先定位【主键值】,再定位【行记录】,扫描...用覆盖索引可以避免回表。将被查询的字段,建立到联合索引里去。 explain的输出结果Extra字段为Using index时,能够触发覆盖索引。 评论 ...
  • 回表一般是单块读,回表次数太多会严重影响SQL性能,如果回表次数太多,就不应该走索引扫描了,应该直接走全表扫描。 在进行SQL优化的时候,一定要注意回表次数!特别是要注意回表的物理I/O次数! SQL> select ...
  • Database--回表

    万次阅读 2018-11-01 09:26:07
    什么是回表? 简单来说就是数据库根据索引找到了指定的记录所在行后,还需要根据rowid再次到数据块里取数据的操作。 比如这样的执行计划,先索引扫描,再通过rowid去取索引...怎么避免回表? 将需要的字段放在索...
  • 这里写目录标题事故现场解决方案提到的“回表查询”InnoDB的索引什么是回表查询怎么优化回表查询 事故现场 数据库使用的MySQL,有一个日志表,需要进行分页查询,于是很容易就想到了limit [offset偏移量] [count数量...
  • select用索引(避免回表) 排序走索引 (避免查询出来后还要进行排序) 按照[《数据库索引设计与优化》][1]的说法满足1,2和3的索引就是三星索引了 2. 例子 测试表有3308670行数据,数据是我网上找了个人名...
  • 2020最新Java常见面试题及答案

    万次阅读 多人点赞 2019-10-26 15:53:35
    一张自增里面总共有 7 条数据,删除了最后 2 条数据,重启 mysql 数据库,又插入了一条数据,此时 id 是几? 166.如何获取当前数据库版本? 167.说一下 ACID 是什么? 168.char 和 varchar 的区别是什么? 169....
  • 入门学习Linux常用必会60个命令实例详解doc/txt

    千次下载 热门讨论 2011-06-09 00:08:45
    出于安全考虑,输入账户密码时字符不会在屏幕上显,光标也不移动。 登录后会看到下面这个界面(以超级用户为例): [root@localhost root]# last login:Tue ,Nov 18 10:00:55 on vc/1 上面显示的是登录星期、月...
  • mysql中的回表查询与索引覆盖

    千次阅读 2019-07-30 08:01:00
    了解一下MySQL中的回表查询与索引覆盖。 回表查询 要说回表查询,先要从InnoDB的索引实现说起。InnoDB有两大类索引,一类是聚集索引(Clustered Index),一类是普通索引(Secondary Index)。 InnoDB的聚集索引 ...
  • 【数据库学习】数据库总结

    万次阅读 多人点赞 2018-07-26 13:26:41
    数据库管理系统在三级模式之间提供了两层映像: 外模式/模式映像(保证数据的逻辑独立性) 模式/内模式映像(保证了物理独立性) ④ 分为临时和永久。 临时 临时存储在tempdb中(如下),当不再使用时...
  • 一文搞懂MySQL索引所有知识点(建议收藏)

    万次阅读 多人点赞 2020-10-24 12:19:05
    显然这种情况不稳定的我们再选择设计上必然会避免这种情况的 平衡二叉树 平衡二叉树是采用二分法思维,平衡二叉查找树除了具备二叉树的特点,最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过...
  • mysql 锁详解

    千次阅读 2016-06-17 09:50:35
    为了给高并发情况下的MySQL进行更好的优化,有必要了解一下mysql查询更新时的锁机制。 一、概述 MySQL有三种锁的级别:页级、级、行级。 MyISAM和MEMORY存储引擎采用的是级锁(table-level locking);BDB...
  • oracle引起全扫描的几种sql

    千次阅读 2019-04-22 08:32:06
    查询语句的时候尽量避免扫描,会引起全扫描的几种SQL如下 1、模糊查询效率很低: 原因:like本身效率就比较低,应该尽量避免查询条件使用like;对于like ‘%...%’(全模糊)这样的条件,是无法使用索引的,...
  • 【JAVA面试】java面试题整理(3)

    千次阅读 2018-10-28 12:50:13
    java面试题整理(3) JAVA常考点 3 ...InnoDB:所有的都保存在同一个数据文件中(也可能是多个文件,或者是独立的空间文件),InnoDB的大小只受限于操作系统文件的大小,一般为2GB。 ...
  • 建立测试 hive> CREATE EXTERNAL TABLE table_for_test_add_column( > original_column string COMMENT '原始数据' > ) > COMMENT 'add_column的测试' > PARTITIONED...
  • 1.查看一个所占的空间大小: SELECT SEGMENT_NAME,  TABLESPACE_NAME,  BYTES B,  BYTES / 1024 KB,  BYTES / 1024 / 1024 MB  FROM USER_SEGMENTS  WHERE segment_name = 'T_RL_INTG_LOGALL'  AND ...
  • 《图书管理系统》毕业论文

    万次阅读 多人点赞 2008-11-24 11:13:00
    选择“上一步”按钮可以跳上一步对表单中所要显示的字段进行修改。设置好表单样式后,单击“下一步”按钮。单击“下一步”后,向导进行到步骤 3 一排序次序。选择“种类编号”为 索引,并选择升序排列。最后预览...
  • 如何让你的调更具Kotlin风味

    万次阅读 2019-01-29 20:35:57
    1、定义一个调的Builder类,并且在类中定义调lamba表达式对象成员,最后再定义Builder类的成员函数,这些函数就是暴露给外部调的函数。个人习惯把它作为一个类的内部类。类似下面这样 class AudioPlayer...
  • 图文并茂说MySQL索引——入门进阶必备

    万次阅读 多人点赞 2021-06-17 23:25:43
    本文不仅仅是mysql索引介绍,当你插入记录,页中的记录结构的变化一一图解,图解聚集索引、非聚集索引、联合索引,介绍索引覆盖和避免回表的情况。
  • MySQL - 高效的设计MySQL库

    千次阅读 多人点赞 2020-08-16 21:42:15
    文章目录范式与反范式范式第一范式第二范式第三范式第二范式 VS 第三范式设计符合 2NF 的范式优缺点反范式 范式与反范式 范式 范式是关系数据库理论的基础,也是我们在设计数据库结构过程中所要遵循的规则和指导...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 349,188
精华内容 139,675
关键字:

如何避免回表