MySQL中的全表扫描和索引树扫描的实例详解

2024-09-29 08:14:24

引言

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

在type这一列，有如下一些可能的选项：

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

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

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

那索引树扫描和全表扫描究竟有什么区别呢？

以下将以一个实例来详细分析这两种扫描方式的区别。

实例

我们建立一张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：

走主键索引

explain SELECT t_article_id FROM t_article;

走全表扫描：

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

2.走全表扫描

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

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

其实，它们两者是有一些细节区别的。

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

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

前面的分析可能还是有点生硬和难以理解，具体地，我们通过下面一张图来更直观地看一下：

图片源自:从数据页的角度看 B+ 树

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

参考

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

到此这篇关于MySQL中的全表扫描和索引树扫描的文章就介绍到这了,更多相关mysql全表扫描和索引树扫描内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

MySQL 8.0 之索引跳跃扫描(Index Skip Scan)

前言 MySQL 8.0.13开始支持 index skip scan 也即索引跳跃扫描.该优化方式支持那些SQL在不符合组合索引最左前缀的原则的情况,优化器依然能组使用组合索引. talk is cheap ,show me the code 实践使用官方文档的例子,构造数据 mysql> CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2)); Query OK, 0 rows affected (0.
大幅提升MySQL中InnoDB的全表扫描速度的方法

在 InnoDB中更加快速的全表扫描一般来讲,大多数应用查询的时候都会用索引,查找很少的几行数据(主键查找或百行内的查询),但有时候我们需要全表查询.典型的全表扫描就是逻辑备份 (mysqldump) 和 online schema changes( 注:在线上对大表 schema 的操作,也是 facebook 的一个开源项目) (SELECT ... INTO OUTFILE). 在 Facebook我们用 mysqldump 来备份数据库. 正如你所知MySql提供两种备份方式,提
MySQL 使用索引扫描进行排序

目录安装sakila 索引扫描排序表结构可以使用索引扫描来做排序的情况补足前导列 order by 中只包含一种排序无法使用索引扫描的情况查询条件中包含不同排序方向查询条件中引用不在索引中的列无法组合最左前缀时第一列是查询范围时 where中有多个等于条件总结安装sakila 我们将会使用MySQL示例数据库sakila来进行sql的演示和讲解 dev.mysql.com/doc/sakila/- 索引扫描排序 MySQL有两种方式可以生成有序的结果:通过排序操作﹔或者按索
导致MySQL做全表扫描的几种情况

这两天看到了两种可能会导致全表扫描的sql,这里给大家看一下,希望可以避免踩坑: 情况1: 强制类型转换的情况下,不会使用索引,会走全表扫描. 举例如下: 首先我们创建一个表 CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `age` int(11) DEFAULT NULL, `score` varchar(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KE
MySQL中的全表扫描和索引树扫描的实例详解

目录引言实例引言在学习mysql时,我们经常会使用explain来查看sql查询的索引等优化手段的使用情况.在使用explain时,我们可以观察到,explain的输出有一个很关键的列,它就是type属性,type表示的是扫描方式,代表 MySQL 使用了哪种索引类型,不同的索引类型的查询效率是不一样的. 在type这一列,有如下一些可能的选项: system:系统表,少量数据,往往不需要进行磁盘IOconst:常量连接eq_ref:主键索引(primary key)或者非空唯一索引(u
MySQL中IF()、IFNULL()、NULLIF()、ISNULL()函数的使用详解

在MySQL中可以使用IF().IFNULL().NULLIF().ISNULL()函数进行流程的控制. 1.IF()函数的使用 IF(expr1,expr2,expr3),如果expr1的值为true,则返回expr2的值,如果expr1的值为false,则返回expr3的值. SELECT IF(TRUE,'A','B'); -- 输出结果:A SELECT IF(FALSE,'A','B'); -- 输出结果:B 2.IFNULL()函数的使用 IFNULL(expr1,expr2),如果
Linux 中（加、减、乘、除）实例详解

Linux 中(加.减.乘.除)实例详解实现代码: #!/bin/bash num1=10 num2=2 #两个数相加 add=$[$num1+$num2] echo $num1 + $num2 '=' $add #两个数相减 sub=$[$num1-$num2] echo $num1 - $num2 '=' $sub #两个数相乘 mut=$[$num1*$num2] echo $num1 '*' $num2 '=' $mut #两个数相除 div=$[$num1/$num2] echo
java ant包中的org.apache.tools.zip实现压缩和解压缩实例详解

java ant包中的org.apache.tools.zip实现压缩和解压缩实例详解其实apache中的ant包(请自行GOOGLE之ant.jar)中有一个更好的类,已经支持中文了,我们就不重复制造轮子了,拿来用吧, 这里最主要的功能是实现了可以指定多个文件到同一个压缩包的功能用org.apache.tools.zip压缩/解压缩zip文件的例子,用来解决中文乱码问题. 实例代码: import Java.io.BufferedInputStream; import java.io.
jQuery中通过ajax调用webservice传递数组参数的问题实例详解

下面通过实例给大家说明比较直观些,更方便大家了解. 本人的项目中通过jquery.ajax调用webservice. 客户端代码如下: $.ajax({ url: "test/xxx.asmx", type: 'POST', dataType: 'xml', timeout: , data: { name: "zhangsan", tags: ["aa", "bb", "cc"] }, error: fun
Android从网络中获得一张图片并显示在屏幕上的实例详解

Android从网络中获得一张图片并显示在屏幕上的实例详解看下实现效果图: 1:androidmanifest.xml的内容 <?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="cn.capinftotech.image" an
Vue中使用方法、计算属性或观察者的方法实例详解

熟悉 Vue 的都知道方法methods.计算属性computed.观察者watcher 在 Vue 中有着非常重要的作用,有些时候我们实现一个功能的时候可以使用它们中任何一个都是可以的,但是它们之间又存在一些不同之处,每一个都有一些适合自己的场景,我们要想知道合适的场景,肯定先对它们有一个清楚的了解,先看一个小例子. <div id="app"> <input v-model="firstName" type="text"&
对Python中DataFrame选择某列值为XX的行实例详解

如下所示: #-*-coding:utf8-*- import pandas as pd all_data=pd.read_csv("E:/协和问答系统/SenLiu/熵测试数据.csv") #获取某一列值为xx的行的候选列数据 print(all_data) feature_data=all_data.iloc[:,[0,-1]][all_data[all_data.T.index[0]]=='青年'] print(feature_data) 实验结果如下: "C:\Pro
vue-form表单验证是否为空值的实例详解

重点部分:点击表单的 submit按钮触发form 部分 @submit="submit"事件: submit事件定义在js部分: prevent:文档上说了在事件后面加上 .prevent就可以阻止默认事件了. form @submit.prevent="submit" <form @submit.prevent="submit"> <input type="text" v-model="us
MySQL索引最左匹配原则实例详解

目录简介准备理论详解聚集索引和非聚集索引回表查询索引覆盖最左匹配原则详细规则补充:为什么要使用联合索引总结简介这篇文章的初衷是很多文章都告诉你最左匹配原则,却没有告诉你,实际场景下它到底是如何工作的,本文就是为了阐述清这个问题. 准备为了方面后续的说明,我们首先建立一个如下的表(MySQL5.7),表中共有5个字段(a.b.c.d.e),其中a为主键,有一个由b,c,d组成的联合索引,存储引擎为InnoDB,插入三条测试数据.强烈建议自己在MySQL中尝试本文的所有语句

MySQL中的全表扫描和索引树扫描 的实例详解

目录

引言

实例

相关推荐

随机推荐

MySQL中的全表扫描和索引树扫描的实例详解