一文解答什么是MySQL的回表

目录
  • 引言
  • 聚簇索引和非聚簇索引是什么？
  • 主键索引和非主键索引有什么区别？
  • B-Tree 和 B+Tree 的简单理解
  • 如何避免回表？

引言

简单来说，回表就是 MySQL 要先查询到主键索引，然后再用主键索引定位到数据。

下面，对一些问题进行分析与回答：

  • 什么是聚簇索引？什么是非聚簇索引？
  • 为什么回表要先查到主键索引？
  • 主键索引和非主键索引有什么区别？
  • 如何避免回表？

聚簇索引和非聚簇索引是什么？

MySQL 的索引有不同的角度的分类方式，例如：按数据结构分、按逻辑角度分、按物理存储分。

其中，按物理存储分有两种索引：聚簇索引和非聚簇索引。

简单来说，聚簇索引是主键索引。

主键索引之外的就是非聚簇索引，非聚簇索引又叫辅助索引或者二级索引。

主键索引和非主键索引有什么区别？

相同点：都使用的是 B+Tree 。

不同点：叶子节点存储的数据不同

  • 主键索引的叶子节点存储的是一行完整的数据；
  • 非主键索引的叶子节点存储的是主键值。叶子节点不包含记录的全部数据，非主键的叶子节点除了用来排序的 key 还包含一个书签（bookmark），其中存储了聚簇索引的 key。

那么这两种索引在使用方面上有什么区别呢？

使用主键索引查询：

# 主键索引的的叶子节点存储的是**一行完整的数据**，
# 所以只需搜索主键索引的 B+Tree 就可以轻松找到全部数据
select * from user where id = 1;

使用非主键索引查询：

# 非主键索引的叶子节点存储的是**主键值**，
# 所以MySQL会先查询到 name 列的索引的 B+Tree，搜索得到对应的主键值
# 然后再去搜索该主键值查询主键索引的 B+Tree 才可以找到对应的数据
select * from user where name = 'Jack';

可以看出使用非主键索引要比主键索引多使用一次 B+Tree。

B-Tree 和 B+Tree 的简单理解

理解聚簇索引和非聚簇索引的关键在于 B+Tree 的理解。

用一幅图来表示，其它的就不再过多解释了：

这里只是简单介绍一下 B-Tree 和 B+Tree 的区别：

  • B+树中只有叶子节点会带有指向记录的指针，而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。
  • B+树中所有叶子节点都是通过指针连接在一起，而B树不会。

如何避免回表？

使用覆盖索引，所谓覆盖索引就是指索引中包含了查询中的所有字段，这种情况下就不需要再进行回表查询了。

到此这篇关于一文解答什么是MySQL的回表的文章就介绍到这了,更多相关MySQL回表内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL 的覆盖索引与回表的使用方法

    两大类索引 使用的存储引擎:MySQL5.7 InnoDB 聚簇索引 * 如果表设置了主键,则主键就是聚簇索引 * 如果表没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引 * 以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引 InnoDB的聚簇索引的叶子节点存储的是行记录(其实是页结构,一个页包含多行数据),InnoDB必须要有至少一个聚簇索引. 由此可见,使用聚簇索引查询会很快,因为可以直接定位到行记录. 普通索引 普通索引也叫二级索引,除聚簇索引外的

  • mysql回表致索引失效案例讲解

    mysql回表致索引失效案例讲解

    简介 mysql的innodb引擎查询记录时在无法使用索引覆盖的场景下,需要做回表操作获取记录的所需字段. mysql执行sql前会执行sql优化.索引选择等操作,mysql会预估各个索引所需要的查询代价以及不走索引所需要的查询代价,从中选择一个mysql认为代价最小的方式进行sql查询操作.而在回表数据量比较大时,经常会出现mysql对回表操作查询代价预估代价过大而导致索引使用错误的情况. 案例 示例如下,在5.6版本的mysql.1CPU2G内存的Linux环境下,新建一个测试表,并创建将近

  • MySQL回表的性能伤害程度有多大

    目录 1回表的性能消耗 2覆盖索引 1 回表的性能消耗 无论单列索引 还是 联合索引,一个索引就对应一个独立的B+索引树,索引树节点仅包含: 索引里的字段值 主键值 即使根据索引树按条件找到所需数据,也仅是索引里的几个字段的值和主键值,万一你搞个select *,那就还得其他字段,就需回表,根据主键到聚簇索引里找,聚簇索引的叶节点是数据页,找到数据页才能把一行数据所有字段值读出来.所以类似 select * from table order by xx1,xx2,xx3 得从联合索引的索引树里按

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

    MySQL 回表,覆盖索引,索引下推

    目录 回表 覆盖索引 索引下推 无索引下推: 查看索引下推的状态 有索引下推: 开启索引下推 回表 在研究mysql二级索引的时候,发现Mysql回表这个操作,往下研究了一下 字面意思,找到索引,回到表中找数据 解释一下就是: 先通过索引扫描出数据所在的行,再通过行主键ID 取出数据. 举个例子说明: SELECT * FROM INNODB_USER WHERE AGE = 18 AND USER_NAME LIKE '模糊查%'; 假如age和user_name两个字段是个联合索引,我们通过

  • MySQL中的回表和索引覆盖示例详解

    MySQL中的回表和索引覆盖示例详解

    目录 索引类型 索引结构 非聚簇索引查询 索引覆盖 总结 索引类型 聚簇索引: 叶子节点存储的是行记录,每个表必须要有至少一个聚簇索引.使用聚簇索引查询会很快,因为可以直接定位到行记录 普通索引:二级索引,除聚簇索引外的索引,即非聚簇索引.普通索引叶子节点存储的是主键(聚簇索引)的值. 聚簇索引递推规则: 如果表设置了主键,则主键就是聚簇索引 如果表没有主键,则会默认第一个NOT NULL,且唯一(UNIQUE)的列作为聚簇索引 以上都没有,则会默认创建一个隐藏的row_id作为聚簇索引 索引结

  • mysql利用覆盖索引避免回表优化查询

    mysql利用覆盖索引避免回表优化查询

    前言 说到覆盖索引之前,先要了解它的数据结构:B+树. 先建个表演示(为了简单,id按顺序建): id name 1 aa 3 kl 5 op 8 aa 10 kk 11 kl 14 jk 16 ml 17 mn 18 kl 19 kl 22 hj 24 io 25 vg 29 jk 31 jk 33 rt 34 ty 35 yu 37 rt 39 rt 41 ty 45 qt 47 ty 53 qi 57 gh 61 dh 以主键以外的列值作为键值构建的 B+ 树索引,我们称之为非聚集索引.

  • 一文解答什么是MySQL的回表

    一文解答什么是MySQL的回表

    目录 引言 聚簇索引和非聚簇索引是什么? 主键索引和非主键索引有什么区别? B-Tree 和 B+Tree 的简单理解 如何避免回表? 引言 简单来说,回表就是 MySQL 要先查询到主键索引,然后再用主键索引定位到数据. 下面,对一些问题进行分析与回答: 什么是聚簇索引?什么是非聚簇索引? 为什么回表要先查到主键索引? 主键索引和非主键索引有什么区别? 如何避免回表? 聚簇索引和非聚簇索引是什么? MySQL 的索引有不同的角度的分类方式,例如:按数据结构分.按逻辑角度分.按物理存储分. 其中

  • 一文解答为什么MySQL的count()方法这么慢

    一文解答为什么MySQL的count()方法这么慢

    目录 前言 count()的原理 各种count()方法的原理 允许粗略估计行数的场景 必须精确估计行数的场景 总结 前言 mysql用count方法查全表数据,在不同的存储引擎里实现不同,myisam有专门字段记录全表的行数,直接读这个字段就好了.而innodb则需要一行行去算. 比如说,你有一张短信表(sms),里面放了各种需要发送的短信信息. sms建表sql: sms表; 需要注意的是state字段,为0的时候说明这时候短信还未发送. 此时还会有一个异步线程不断的捞起未发送(state=

  • php找出指定范围内回文数且平方根也是回文数的方法

    本文实例讲述了php找出指定范围内回文数且平方根也是回文数的方法.分享给大家供大家参考.具体如下: 一.要求: 给出两个数值X和Y,统计在这个区间里的回文数,并且要求它们的平方根也是回文数.其中 1<= x <= y < 10 14 二.解决方法: <?php error_reporting(E_ALL); ini_set("display_errors", 1); //避免超时 set_time_limit(0); $t1=microtime(); funct

  • MySQL闪回(flashback)原理与实战

    DBA或开发人员,有时会误删或者误更新数据,如果是线上环境并且影响较大,就需要能快速回滚.传统恢复方法是利用备份重搭实例,再应用去除错误sql后的binlog来恢复数据.此法费时费力,甚至需要停机维护,并不适合快速回滚.也有团队利用LVM快照来缩短恢复时间,但快照的缺点是会影响mysql的性能. MySQL闪回(flashback)利用binlog直接进行回滚,能快速恢复且不用停机.本文将介绍闪回原理,给出笔者的实战经验,并对现存的闪回工具作比较. 开胃菜 某天,小明因种种原因,误删了大批线上用

  • 解析php mysql 事务处理回滚操作(附实例)

    很多新手在进行项目过程中,会碰到这样一种情况,如:论坛扣币项目中,用户支付论坛币的时候如果突然断网.电脑死机.停电.等其它自然灾害时,导致本次交易没有成功(即用户的币已经扣掉了,但是服务器数据库中没有消费记录等其它情况),这种情况应该怎么样进行处理呢? 在这个时候,我们可以用Mysql事务回滚来进行处理,那代码怎么写呢? 那下面我就来说一说这个mysql事务回滚应该怎么处理. 首先MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理!其他的类型是不支持的! 那如果,我们的数据表已经存

  • mysql闪回工具binlog2sql安装配置教程详解

    概述 binlog2sql是一个Python开发开源的MySQL Binlog解析工具,能够将Binlog解析为原始的SQL,也支持将Binlog解析为回滚的SQL,去除主键的INSERT SQL,是DBA和运维人员数据恢复好帮手. 一.安装配置 1.1 用途 数据快速回滚(闪回) 主从切换后新master丢数据的修复 从binlog生成标准SQL,带来的衍生功能 支持MySQL5.6,5.7 1.2 安装 shell> git clone https://github.com/danfengc

随机推荐