MySQL中的全表扫描和索引树扫描

引言

在学习mysql时,我们经常会使用explain来查看sql查询的索引等优化手段的使用情况。在使用explain时,我们可以观察到,explain的输出有一个很关键的列,它就是 type属性,type表示的是扫描方式,代表 MySQL 使用了哪种索引类型,不同的索引类型的查询效率是不一样的。

在type这一列,有如下一些可能的选项:

  • system:系统表,少量数据,往往不需要进行磁盘IO
  • const:常量连接
  • eq_ref:主键索引(primary key)或者非空唯一索引(unique not null)等值扫描
  • ref:非主键非唯一索引等值扫描
  • range:范围扫描
  • index:索引树扫描
  • ALL:全表扫描(full table scan)

在上面列出的7种选项中,前面五种我就不详细讲了,可以参考Mysql Explain之type详解这篇文章。我当时对于前五种属性是比较容易就理解了的,但是对于后面两种即索引树扫描和全表扫描我还是存在一些疑问。

索引树扫描我们是比较熟悉的,它就是会遍历聚簇索引树,底层是一颗B+树,叶子节点存储了所有的实际行数据。其实,全表扫描也是扫描的聚簇索引树,因为聚簇索引树的叶子节点中存储的就是实际数据,只要扫描遍历聚簇索引树就可以得到全表的数据了。

那么,索引树扫描和全表扫描有什么不同呢?

[En]

So what’s the difference between index tree scanning and full table scanning?

下面将用一个例子来详细分析这两种扫描方法的区别。

[En]

The following will use an example to analyze the difference between the two scanning methods in detail.

实例

我们建立一张 t_article表:

create table t_article(
    t_article_id int primary key auto_increment,
    t_title varchar(40),
);

在我们创建的 t_article表中,只有两个字段,一个是主键 t_article_id,另一个是普通字段 t_title

我们知道,InnoDB会将聚簇索引默认建立在主键上,而聚簇索引树中的叶子节点就存储了整张表的行数据。

接着,我们分别设计两个sql查询case:

  1. 走主键索引
explain SELECT t_article_id FROM t_article;
  1. 走全表扫描:
explain SELECT t_title FROM t_article;

以上两个查询都没有where查询,按理来说底层的sql执行情况应该是差不多的。

结果分析

我们可以来看看上面两种查询的结果,在查询时使用explain语句输出sql执行的详细信息。

  1. 走索引扫描

id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t_article index PRIMARY 4 2 100 Using index

  1. 走全表扫描

id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t_article ALL 2 100

从以上两个查询结果中我们可以发现,走主键索引的查询和走全表的查询是不一样的。我们前面也提到了,InnoDB的索引是使用B+树来实现的,而主键索引中存储了整张表的数据,那全表扫描时其实也是扫描的主键索引。那为什么这两种查询会不一样呢?按理来说都是查询的主键索引,它们应该是一样的。

事实上,两者之间存在一些细微的差异。

[En]

In fact, there are some detailed differences between the two.

比如,第一个查询,它的优化手段是使用索引树扫描,也就是type中显示的index属性,而且它还使用了覆盖索引,即Extra列中的Using index属性。之所以第一个查询能够使用这两种优化手段,其实是因为select查询的结果列只包含主键,而主键的值是可以直接在遍历聚簇索引树时确定,也不需要回表查询了。

对于第二个查询,它也没有使用where进行过滤,而且它的select结果列包含的是普通列,并不是主键或者其他索引列,所以它会走全表扫描。而全表扫描其实底层也是扫描的聚簇索引树,也就是底层的B+树。这种全表扫描与索引树扫描有一个明显区别,那就是,全表扫描不仅仅需要扫描索引列,还需要扫描每个索引列中指向的实际数据,这里包含了所有的非索引列数据。

之前的分析可能仍有些生硬难懂。具体地说,让我们更直观地看看下面的图片:

[En]

The previous analysis may still be a little blunt and difficult to understand. Specifically, let’s take a more intuitive look at the following picture:

MySQL中的全表扫描和索引树扫描
图片源自:从数据页的角度看 B+ 树

从上面的图我们可以看到,对于索引扫描来讲,它只需要读取叶子节点的所有key,也就是索引的键,而不需要读取具体的data行数据;而对于全表扫描来说,它无法仅仅通过读取索引列获得需要的数据,还需要读取具体的data数据才能获取select中指定的非索引列的具体值。所以,全表扫描的效率相比于索引树扫描相对较低一点,但是差距不是很大。

参考

【mysql】全表扫描过程 & 聚簇索引 区别和联系
从数据页的角度看 B+ 树

Original: https://www.cnblogs.com/GarrettWale/p/16271622.html
Author: Garrett_Wale
Title: MySQL中的全表扫描和索引树扫描

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505383/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • 电脑必须打开的设置

    电脑性能设置 优化设置一:开启卓越性能 其实,win10系统中有一个卓越性能的隐藏设置,它可以让我们的电脑,在现有配置的情况下,发挥出最优良的性能。 1、 同时按住”w…

    数据库 2023年6月14日
    0152
  • Java8 Stream

    什么是Stream Java8 API添加了一个新的抽象称为流Stream,可以以一种声明的方式处理数据,给我们操作集合(Collection)提供了极大的便利。Stream将要处…

    数据库 2023年6月6日
    0128
  • linux下centos7.2下安装redis 4.0.6

    一、安装 redis 第一步:下载 redis 安装包 wget http://download.redis.io/releases/redis-4.0.6.tar.gz [roo…

    数据库 2023年6月14日
    0117
  • 编程过程中常用的英文单词

    引用 parameter和argument的区别 parameter和argument的区别 parameter是指函数定义中参数,而argument指的是函数调用时的实际参数。 …

    数据库 2023年6月9日
    0130
  • 正则表达式

    1.正则表达式分类 正则表达式:REGEXP,REGular EXPression。正则表达式分为两类: Basic REGEXP(基本正则表达式) Extended REGEXP…

    数据库 2023年6月15日
    0148
  • vue2框架基础

    一、什么是vue? vue是一个优秀的前端框架,他与Angular.js、React.js成为前端三大主流框架。他是一套构建用户界面的框架,只关注视图层,可以完成大型项目的开发,框…

    数据库 2023年6月14日
    0152
  • MySQL Operator 02 | 脚手架选型 & 工程创建

    高日耀 资深数据库内核研发毕业于华中科技大学,喜欢研究主流数据库架构和源码,并长期从事分布式数据库内核研发。曾参与分布式 MPP 数据库 CirroData 内核开发(东方国信),…

    数据库 2023年5月24日
    0156
  • 系统设计的11个考虑

    有了《系统架构的11条原则》,真正到设计阶段还有另外11个考虑。 系统正确性 考虑一:负负得正 假如我们看到某个代码,明显有逻辑错误,想随手改改。你就要考虑一件事情:这段明显有问题…

    数据库 2023年6月6日
    0131
  • kettle插入更新

    kettle实现若主键存在则更新,若主键不存在则插入 Original: https://www.cnblogs.com/cheng9999/p/14085922.htmlAuth…

    数据库 2023年6月16日
    0113
  • ShardingSphere 异构迁移最佳实践:将3.5亿量级的顾客系统 RTO 减少60倍

    Apache ShardingSphere 助力当当 3.5 亿用户量级顾客系统重构,由 PHP+SQL Server 技术栈无缝转型为 Java+ShardingSphere+M…

    数据库 2023年6月16日
    0171
  • Java正则表达式Pattern和Matcher类详解

    概述 Pattern类的作用在于编译正则表达式后创建一个匹配模式. Matcher类使用Pattern实例提供的模式信息对正则表达式进行匹配 Pattern类 常用方法及介绍 Pa…

    数据库 2023年6月11日
    0150
  • 分布式全局唯一ID

    方案一、UUID UUID的方式能生成一串唯一随机32位长度数据,它是无序的一串数据,按照开放软件基金会(OSF)制定的标准计算,UUID的生成用到了以太网卡地址、纳秒级时间、芯片…

    数据库 2023年6月9日
    0137
  • JUC学习笔记(五)

    创建线程的方法-一种是通过创建 Thread 类,另一种是通过使用 Runnable 创建线程。但是,Runnable 缺少的一项功能是,当线程终止时(即 run()完成时),我们…

    数据库 2023年6月6日
    0190
  • 简单的2021年终总结

    当大家开开心心跨年的时候,我在补年终总结。 小时候恨不得时间过得快一点,现在不这么想了。 我的 2021年,都是平静、反复的一天天,没有出书、没有开源、没有跳槽、没有升官。没错,这…

    数据库 2023年6月6日
    0122
  • [LeetCode]21. 合并两个有序链表

    将两个升序链表合并为一个新的 升序 链表并返回。新链表是通过拼接给定的两个链表的所有节点组成的。 示例: 输入:1->2->4, 1->3->4输出:1-&…

    数据库 2023年6月9日
    0158
  • 面试必问之 CopyOnWriteArrayList,你了解多少?

    一、摘要 在介绍 CopyOnWriteArrayList 之前,我们一起先来看看如下方法执行结果,代码内容如下: public static void main(String[]…

    数据库 2023年6月14日
    0122
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球