MySQL进阶45讲【35】什么时候会使用内部临时表？

1 前言

在MySQL进阶45讲【15】“order by“是怎么工作的？和MySQL进阶45讲【32】到底可不可以使用join？文章中，分别介绍了sort buffer、内存临时表和join buffer。这三个数据结构都是用来存放语句执行过程中的中间数据，以辅助SQL语句的执行的。其中，我们在排序的时候用到了sort buffer，在使用join语句的时候用到了join buffer。

MySQL什么时候会使用内部临时表呢？

这篇文章，就先举两个需要用到内部临时表的例子，来看看内部临时表是怎么工作的。然后，我们再来分析，什么情况下会使用内部临时表。

2 union 执行流程

为了便于量化分析，用下面的表t1来举例。

create table t1(id int primary key, a int, b int, index(a));
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t1 values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();

然后，我们执行下面这条语句：

(select 1000 as f) union (select id from t1 order by id desc limit 2)

这条语句用到了union，它的语义是，取这两个子查询结果的并集。并集的意思就是这两个集合加起来，重复的行只保留一行。

下图是这个语句的explain结果。

在这里插入图片描述
可以看到：

  • 第二行的key=PRIMARY，说明第二个子句用到了索引id。
  • 第三行的Extra字段，表示在对子查询的结果集做union的时候，使用了临时表(Using temporary)。

这个语句的执行流程是这样的：

  1. 创建一个内存临时表，这个临时表只有一个整型字段f，并且f是主键字段。
  2. 执行第一个子查询，得到1000这个值，并存入临时表中。
  3. 执行第二个子查询：
    • 拿到第一行id=1000，试图插入临时表中。但由于1000这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；
    • 取到第二行id=999，插入临时表成功。
  4. 从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是1000和999。

这个过程的流程图如下所示：

在这里插入图片描述
可以看到，这里的内存临时表起到了暂存数据的作用，而且计算过程还用上了临时表主键id的唯一性约束，实现了union的语义。

顺便提一下，如果把上面这个语句中的union改成union all的话，就没有了“去重”的语义。这样执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此也就不需要临时表了。

在这里插入图片描述
可以看到，第二行的Extra字段显示的是Using index，表示只使用了覆盖索引，没有用临时表了。

3 group by 执行流程

另外一个常见的使用临时表的例子是group by，我们来看一下这个语句

select id%10 as m, count(*) as c from t1 group by m;

这个语句的逻辑是把表t1里的数据，按照 id%10 进行分组统计，并按照m的结果排序后输出。它的explain结果如下：

在这里插入图片描述
在Extra字段里面，我们可以看到三个信息：

  • Using index，表示这个语句使用了覆盖索引，选择了索引a，不需要回表；
  • Using temporary，表示使用了临时表；
  • Using filesort，表示需要排序。
    这个语句的执行流程是这样的：
  1. 创建内存临时表，表里有两个字段m和c，主键是m；
  2. 扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；
    • 如果临时表中没有主键为x的行，就插入一个记录(x,1);
    • 如果表中有主键为x的行，就将x这一行的c值加1；
  3. 遍历完成后，再根据字段m做排序，得到结果集返回给客户端。

这个流程的执行图如下：

在这里插入图片描述
图中最后一步，对内存临时表的排序，在MySQL进阶45讲【16】如何正确地显示随机消息？文章中已经有过介绍，现在把图贴过来，方便大家回顾。

在这里插入图片描述
其中，临时表的排序过程就是图6中虚线框内的过程。

接下来，我们再看一下这条语句的执行结果：

在这里插入图片描述
如果需求并不需要对结果进行排序，那可以在SQL语句末尾增加order bynull，也就是改成：

select id%10 as m, count(*) as c from t1 group by m order by null;

这样就跳过了最后排序的阶段，直接从临时表中取数据返回。返回的结果如图所示

在这里插入图片描述
由于表t1中的id值是从1开始的，因此返回的结果集中第一行是id=1；扫描到id=10的时候才插入m=0这一行，因此结果集里最后一行才是m=0。

这个例子里由于临时表只有10行，内存可以放得下，因此全程只使用了内存临时表。但是，内存临时表的大小是有限制的，参数tmp_table_size就是控制这个内存大小的，默认是16M。

如果执行下面这个语句序列：

set tmp_table_size=1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

把内存临时表的大小限制为最大1024字节，并把语句改成id %100，这样返回结果里有100行数据。但是，这时的内存临时表大小不够存下这100行数据，也就是说，执行过程中会发现内存临时表大小到达了上限（1024字节）。

那么，这时候就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是InnoDB。 这时，返的结果如图所示。

在这里插入图片描述
如果这个表t1的数据量很大，很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。

4 group by 优化方法 --索引

可以看到，不论是使用内存临时表还是磁盘临时表，group by逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。如果表的数据量比较大，上面这个group by语句执行起来就会很慢，我们有什么优化的方法呢？

要解决group by语句的优化问题，可以先想一下这个问题：执行group by语句为什么需要临时表？

group by的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的id%100的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。

那么，如果扫描过程中可以保证出现的数据是有序的，是不是就简单了呢？

假设，现在有一个类似如图的这么一个数据结构，我们来看看group by可以怎么做。

在这里插入图片描述

可以看到，如果可以确保输入的数据是有序的，那么计算group by的时候，就只需要从左到右，顺序扫描，依次累加。也就是下面这个过程：

  • 当碰到第一个1的时候，已经知道累积了X个0，结果集里的第一行就是(0,X);
  • 当碰到第一个2的时候，已经知道累积了Y个1，结果集里的第一行就是(1,Y);

按照这个逻辑执行的话，扫描到整个输入的数据结束，就可以拿到group by的结果，不需要临时表，也不需要再额外排序。

InnoDB的索引，就可以满足这个输入有序的条件。

在MySQL 5.7版本支持了generated column机制，用来实现列数据的关联更新。可以用下面的方法创建一个列z，然后在z列上创建一个索引（如果是MySQL 5.6及之前的版本，也可以创建普通列和索引，来解决这个问题）。

alter table t1 add column z int generated always as(id % 100), add index(z);

这样，索引z上的数据就是类似上图这样有序的了。上面的group by语句就可以改成：

在这里插入图片描述
从Extra字段可以看到，这个语句的执行不再需要临时表，也不需要排序了。

5 group by优化方法 --直接排序

所以，如果可以通过加索引来完成group by逻辑就再好不过了。但是，如果碰上不适合创建索引的场景，我们还是要老老实实做排序的。那么，这时候的group by要怎么优化呢？

如果我们明明知道，一个group by语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，看上去就有点儿傻。

那么，我们就会想了，MySQL有没有让我们直接走磁盘临时表的方法呢？

答案是，有的。

在group by语句中加入SQL_BIG_RESULT这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

MySQL的优化器一看，磁盘临时表是B+树存储，存储效率不如数组来得高。所以，数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。

因此，下面这个语句

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

的执行流程就是这样的：

  1. 初始化sort_buffer，确定放入一个整型字段，记为m；
  2. 扫描表t1的索引a，依次取出里面的id值, 将 id%100的值存入sort_buffer中；
  3. 扫描完成后，对sort_buffer的字段m做排序（如果sort_buffer内存不够用，就会利用磁盘临
    时文件辅助排序）；
  4. 排序完成后，就得到了一个有序数组。

根据有序数组，得到数组里面的不同值，以及每个值的出现次数。这一步的逻辑，已经从前面的图中了解过了。

下面两张图分别是执行流程图和执行explain命令得到的结果。

在这里插入图片描述
在这里插入图片描述
从Extra字段可以看到，这个语句的执行没有再使用临时表，而是直接用了排序算法。

基于上面的union、union all和group by语句的执行过程的分析，我们来回答文章开头的问题：MySQL什么时候会使用内部临时表？

  1. 如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果；
  2. join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构；
  3. 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union需要用到唯一索引约束， group by还需要用到另外一个字段来存累积计数。

6 小结

通过这篇文章重点讲了group by的几种实现算法，从中可以总结一些使用的指导原则：

  1. 如果对group by语句的结果没有排序要求，要在语句后面加 order bynull；
  2. 尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary和 Using filesort；
  3. 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
  4. 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/488752.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

BSV区块链的应用开发前景——通过标准化来促进创新

BSV区块链的应用开发前景——通过标准化来促进创新

​​发表时间&#xff1a;2024年3月5日 近年来区块链领域的发展日新月异&#xff0c;各种全新的技术和方法论正在迅猛涌现。在这个瞬息万变的环境之中&#xff0c;标准化不仅仅会为开发者们带来便利&#xff0c;同时也促进了应用之间的互操作性&#xff0c;并且推动着生态系统的…
阅读更多...
天地图-坐标拾取

天地图-坐标拾取

在网页使用高德&#xff0c;百度地图这些时&#xff0c;会因为没有购买他们的使用版权&#xff0c;从而出现以下水印&#xff0c;因此需要换一个地图-天地图 天地图官方文档&#xff1a;http://lbs.tianditu.gov.cn/ 然而天地图并不像其他地图一样这么方便&#xff0c;比如没有…
阅读更多...
MyBatis题库详解

MyBatis题库详解

1、介绍下MyBatis中的工作原理 介绍MyBatis的基本情况&#xff1a;ORM&#xff0c;对象关系映射&#xff08;英语&#xff1a;Object Relational Mapping&#xff0c;简称ORM&#xff0c;或O/RM&#xff0c;或O/R mapping&#xff09;&#xff0c;是一种程序技术 MyBatis框架的…
阅读更多...
2015年认证杯SPSSPRO杯数学建模D题(第一阶段)城市公共自行车全过程文档及程序

2015年认证杯SPSSPRO杯数学建模D题(第一阶段)城市公共自行车全过程文档及程序

2015年认证杯SPSSPRO杯数学建模 D题 城市公共自行车 原题再现&#xff1a; 城市交通问题直接影响市民的生活和工作。在地形平坦的城市&#xff0c;公共自行车出行系统是一种很好的辅助手段。一般来说&#xff0c;公共自行车出行系统由数据中心、驻车站点、驻车桩、自行车&…
阅读更多...
openlayers 入门教程（五）：sources 篇

openlayers 入门教程（五）：sources 篇

还是大剑师兰特&#xff1a;曾是美国某知名大学计算机专业研究生&#xff0c;现为航空航海领域高级前端工程师&#xff1b;CSDN知名博主&#xff0c;GIS领域优质创作者&#xff0c;深耕openlayers、leaflet、mapbox、cesium&#xff0c;canvas&#xff0c;webgl&#xff0c;ech…
阅读更多...
Memcached分布式内存对象数据库

Memcached分布式内存对象数据库

一 Memcached 概念 Memcached 是一个高性能的分布式内存对象缓存系统&#xff0c;用于动态 Web 应用以减轻数据库负载。它通过在内存中缓存数据和对象来减少读取数据库的次数&#xff0c;从而提高动态、数据库驱动网站的速度。 二 在架构中的位置 Memcached 处于前端或中间件后…
阅读更多...
Vue 发送Ajax请求多种方式

Vue 发送Ajax请求多种方式

1. 发送ajax请求的方式 方案一&#xff1a;jq 的ajax&#xff08;在 vue 中不推荐同时使用&#xff09;方案二&#xff1a;js 原始官方 fetch方法方案三&#xff1a;axios 第三方 2. 方案一 后端视图函数 from rest_framework.viewsets import ViewSet from rest_framework…
阅读更多...
UE4 根据任意多个点，生成最近的线条

UE4 根据任意多个点，生成最近的线条

1.计算所有线条的组合 2.Clear0宏&#xff1a;清除掉数组Distance0的值。注意这里是设置成最大值&#xff0c;而不是使用Clear&#xff01; 3.清除掉数组中的最小值&#xff0c;避免重复生成相同长度的线条。注意这里是设置成最大值&#xff0c;而不是使用Clear&#xff01; …
阅读更多...
python爬虫基础-----运算符（第三天）

python爬虫基础-----运算符（第三天）

&#x1f388;&#x1f388;作者主页&#xff1a; 喔的嘛呀&#x1f388;&#x1f388; &#x1f388;&#x1f388;所属专栏&#xff1a;python爬虫学习&#x1f388;&#x1f388; ✨✨谢谢大家捧场&#xff0c;祝屏幕前的小伙伴们每天都有好运相伴左右&#xff0c;一定要天天…
阅读更多...
GitHub加速访问最简单的方法

GitHub加速访问最简单的方法

Github是全球最大的代码开源平台&#xff0c;对于编程的小伙伴来说&#xff0c;这是一个巨大的宝库&#xff0c;也是编程学习的圣地。很对小伙伴在使用GitHub时会经常出现无法访问Github的情况。 一、解决方法——>修改hosts文件 通过 IP查询工具来获取当前Github网站的真实…
阅读更多...
全民采矿石赚钱小程序源码，附带详细搭建教程

全民采矿石赚钱小程序源码，附带详细搭建教程

安装教程 1、环境用宝塔Nginxphp7.0或者以下版本 2、可以更换各种模板&#xff0c;懂代码和标签的可以改模板&#xff0c;不懂的可以直接上站 3、上站前记得添加关键词和内容库 4、伪静态在绑定完百度站长之后再添加 目录说明&#xff1a; data/keyword 放关键词 标签&#xff…
阅读更多...
【扩散模型】论文精读：Denoising Diffusion Probabilistic Models（DDPM）

【扩散模型】论文精读：Denoising Diffusion Probabilistic Models（DDPM）

文章目录 前言Abstract1 Introduction2 Background3 Diffusion models and denoising autoencoders3.1 Forward process and LT3.2 Reverse process and L1:T −13.3 Data scaling, reverse process decoder, and L03.4 Simplified training objective 4 Experiments4.1 Sample…
阅读更多...
C语言： 指针讲解

C语言： 指针讲解

为什么需要指针? &#xff08;1&#xff09;指针的使用使得不同区域的代码可以轻易的共享内存数据。当然你也可以通过数据的复制达到相同的效果&#xff0c;但是这样往往效率不太好&#xff0c;因为诸如结构体等大型数据&#xff0c;占用的字节数多&#xff0c;复制很消耗性能…
阅读更多...
Linux-进程控制（进程创建、进程终止、进程等待）

Linux-进程控制（进程创建、进程终止、进程等待）

一、进程创建 1.1 fork函数介绍 在命令行下我们可以通过 ./ exe文件 来创建一个进程&#xff0c;通过fork函数&#xff0c;我们可以通过代码的形式从一个进程中创建一个进程&#xff0c;新进程为子进程&#xff0c;原进程为父进程&#xff0c;子进程在创建时&#xff0c;会与…
阅读更多...
软件项目-详细设计说明书范文参考（46页Word原件）

软件项目-详细设计说明书范文参考（46页Word原件）

一、 关于本文档 &#xff08;一&#xff09; 编写目的 &#xff08;二&#xff09; 预期读者 二、 项目概要 &#xff08;一&#xff09; 建设背景 &#xff08;二&#xff09; 建设目标 &#xff08;三&#xff09; 建设内容 三、 总体设计 &#xff08;一&#xff0…
阅读更多...
git基础-撤销操作

git基础-撤销操作

撤销操作 在任何阶段&#xff0c;我们都可能希望撤消某些操作。在这里&#xff0c;我们将回顾一些基本工具&#xff0c;用于撤消之前所做的更改。操作要谨慎&#xff0c;因为这些撤销可能无法修复。这是 Git 中为数不多的几个领域之一由于操作不当&#xff0c;导致丢失一些工作…
阅读更多...
一篇文章，告别Flutter状态管理争论，问题和解决

一篇文章，告别Flutter状态管理争论，问题和解决

起因 每隔一段时间&#xff0c;都会出现一个新的状态管理框架&#xff0c;最近在YouTube上也发现了有人在推signals, 一个起源于React的状态管理框架&#xff0c;人们总是乐此不疲的发明各种好用或者为了解决特定问题而产生的方案&#xff0c;比如Bloc, 工具会推陈出新&#x…
阅读更多...
JavaParser 手动安装和配置

JavaParser 手动安装和配置

目录 前言 一、安装 Maven 工具 1.1 Maven 软件的下载 1.2 Maven 软件的安装 1.3 Maven 环境变量配置 1.4 通过命令检查 Maven 版本 二、配置 Maven 仓库 2.1 修改仓库目录 2.2 添加国内镜像 三、从 Github 下载 JavaParser 3.1 下载并解压 JavaParser 3.2 从路径打…
阅读更多...
手摸手教你安装使用nvm（简单明了）

手摸手教你安装使用nvm（简单明了）

1.nvm定义 &#xff08;node.js version management&#xff09; nvm是node版本管理工具&#xff0c;通过nvm可以安装和切换不同版本的node.js 2.卸载之前安装的node 打开系统的控制面板&#xff0c;点击卸载程序&#xff0c;卸载nodejs 提示&#xff1a;如果你没有安装过…
阅读更多...
【使用postman发送post请求】

【使用postman发送post请求】

1&#xff09;post http://ip:8090/version?appVersion1.0.0&channelgoogle&platformandroid&deviceId90991c4465e1886a81b00dac855fe098&notice1 这样子选择json格式提交数据&#xff0c;可读性强 好处&#xff1a; 1.最大的好处莫过于我可以记录下来曾经做…
阅读更多...
最新文章

Copyright @ 2022~2023