【阿里MySQL面试题】内部临时表

公众号JavaEdge 2023-03-28 原文

sort buffer、内存临时表和join buffer，都是用来存放语句执行过程中的中间数据，以辅助SQL语句的执行。排序时用到sort buffer，使用join语句时用到join buffer。

1 union 执行流程

示例表：

create table test_union
(
    id int not null
        primary key,
    a  int null,
    b  int null
);

create index a
    on test_union (a);

创建数据

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;;

执行SQL

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

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
2	UNION	t1	NULL	index	NULL	PRIMARY	4	NULL	2	100	Backward index scan; Using index
NULL	UNION RESULT	<union1,2>	NULL	ALL	NULL	NULL	NULL	NULL	NULL	NULL	Using temporary

该语句的union语义：取这两个子查询结果的并集。重复行只保留一行。

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

执行流程

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

union 执行流程

这里的内存临时表用于暂存数据，且计算过程还用上了临时表主键id的唯一性约束，实现union语义。

若把上面语句的union改成union all，就失去“去重”语义。执行时，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。也就无需临时表。

union all的执行计划

Extra=Using index，只使用了覆盖索引，未用临时表。

2 group by 执行流程

把t1里的数据，按照 id%10 进行分组统计，并按m的结果排序后输出。

group by执行计划：

Extra字段信息：

Using index：使用覆盖索引，选择了索引a（因为key=a），无需回表
Using temporary：使用临时表
Using filesort：需排序

group by执行流程

创建内存临时表，表里有字段m、c，主键m
扫描t1的索引a，依次取出叶节点的id值，计算id%10，记为x，若：
- 临时表无主键为x的行，就插入一个记录(x,1)
- 表中有主键为x的行，则将x这行的c值加1
遍历完成后，根据m排序，得到结果集返回给客户端

图中最后一步，对内存临时表的排序

内存临时表排序流程

临时表的排序过程就是图中虚线框。

若业务需求无需对结果排序，可在SQL语句末尾增加order by null：

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

这就跳过最后的排序，直接从临时表取数据返回：

group + order by null 的结果（内存临时表）

由于t1中的id值从1开始（数据1~1000），因此返回的结果集中第一行id=1；扫描到id=10时，才插入m=0。

由于临时表只有10行，内存可放得下，因此全程只使用内存临时表。内存临时表的大小有限制，即参数tmp_table_size，默认16M。

若执行：

将内存临时表的大小限制为最大1024K，并把语句改成id % 100，这样返回结果里有100行数据。但这时内存临时表大小存不下这100行。

此时会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是InnoDB。这时，返回结果：

group + order by null 的结果（磁盘临时表）若t1的数据量很大，可能该查询需要的磁盘临时表就会占用大量磁盘空间。

3 优化group by

3.1 索引

无论内存临时表、磁盘临时表，group by都需构造一个带唯一索引的表，执行代价较高。若表数据量较大，上面这group by执行就很慢。

3.1.1 为何执行group by需要临时表？

group by，统计不同值出现的次数。但由于每行的id%100结果无序，所以要有个临时表，记录并统计结果。

若扫描过程可保证出现的数据有序，是不是就简单了？假设有如下数据结构，看group by怎么做的。

group by算法优化-有序输入：

所以，若确保输入数据有序，则计算group by时，就只需从左到右，顺序扫描，依次累加：

当碰到第一个1，已知累积了X个0，结果集里的第一行就是(0,X)
当碰到第一个2，已知累积Y个1，结果集里的第二行就是(1,Y); 按照这个逻辑执行的话，扫描到整个输入的数据结束，即可拿到group by的结果，无需临时表，也无需额外排序。

InnoDB索引刚好满足这个输入有序。 MySQL 5.7支持generated column，以实现列数据的关联更新。

创建一个列z，然后在z创建索引（≤5.6，也可创建普通列和索引）。

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

这样，索引z上的数据就有序了。上面的group by即可改成：

select z, count(*) as c
from t1
group by z;

group by 优化的执行计划：

Extra知该语句无需临时表，也无需排序。

直接排序

若可加索引完成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;

执行流程：

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

根据有序数组，得到数组里不同值，以及每个值的出现次数。

使用 SQL_BIG_RESULT的执行流程

SQL_BIG_RESULT explain：

该语句未使用临时表，而直接用排序算法。so：

MySQL何时使用内部临时表？

若语句执行过程可一边读数据，一边直接得到结果，就无需额外内存，否则就需额外内存，保存中间结果。

join_buffer是无序数组
sort_buffer是有序数组
临时表是二维表结构

若执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中：

union需要用到唯一索引约束
group by还需要用到另外一个字段来存累积计数

试题 MySQL text-align text align 数据 3c sqlMySQL 数据库yyds干货盘点

有关【阿里MySQL面试题】内部临时表的更多相关文章

使用canal同步MySQL数据到ES - 2
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
阿里云国际版免费试用：如何注册以及注意事项 - 2
作为新的阿里云用户，您可以50免费试用多种优惠，价值高达1,700美元（或8,500美元）。这将让您了解和体验阿里云平台上提供的一系列产品和服务。如果您以个人身份注册免费试用，您将获得价值1,700美元的优惠。但是，如果您是注册公司，您可以选择企业免费试用，提交基本信息通过企业实名注册验证，即可开始价值$8,500的免费试用！本教程介绍了如何设置您的帐户并使用您的免费试用版。关于免费试用在我们开始此试用之前，您还必须遵守以下条款和条件才能访问您的免费试用：只有在一年内创建的账户才有资格获得阿里云免费试用。通过此免费试用优惠，用户可以免费试用免费试用活动页面上列出的每种产品一次。如果您有多个帐
阿里云RDS——产品系列概述 - 2
基础版云数据库RDS的产品系列包括基础版、高可用版、集群版、三节点企业版，本文介绍基础版实例的相关信息。RDS基础版实例也称为单机版实例，只有单个数据库节点，计算与存储分离，性价比超高。说明RDS基础版实例只有一个数据库节点，没有备节点作为热备份，因此当该节点意外宕机或者执行重启实例、变更配置、版本升级等任务时，会出现较长时间的不可用。如果业务对数据库的可用性要求较高，不建议使用基础版实例，可选择其他系列（如高可用版），部分基础版实例也支持升级为高可用版。基础版与高可用版的对比拓扑图如下所示。优势性能由于不提供备节点，主节点不会因为实时的数据库复制而产生额外的性能开销，因此基础版的性能相对于
Hive SQL 五大经典面试题 - 2
目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类
ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2
我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin
ruby - 是否可以从也在该模块中的类内部调用模块函数 - 2
在这段Ruby代码中:ModuleMClassC当我尝试运行时出现“'M:Module'的未定义方法'helper'”错误c=M::C.new("world")c.work但直接从另一个类调用M::helper("world")工作正常。类不能调用在定义它们的同一模块中定义的模块函数吗？除了将类移出模块外，还有其他解决方法吗？最佳答案为了调用M::helper，你需要将它定义为defself.helper;结束为了进行比较，请查看以下修改后的代码段中的helper和helper2moduleMclassC
ruby - 如何使用 ruby mysql2 执行事务 - 2
我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi
ruby - 无法安装 gem - make 未被识别为内部或外部命令可运行程序或批处理文件 - 2
我想在Windows7上安装带有ruby1.9.3的rspec-railsgem。我收到一些错误消息，提示无法安装某些json库。所以，我使用下面的说明来解决它。来源=The'json'nativegemrequiresinstalledbuildtools从[rubyinstaller.org][3]下载[Ruby1.9.3][2]从[rubyinstaller.org][3]下载DevKit文件对于Ruby1.9.3，使用[DevKit-tdm-32-4.5.2-20110712-1620-sfx.exe][4]将DevKit解压到路径C:\Ruby193\DevKit运行cd
ruby - 如何在 Ruby 中实现私有(private)内部类 - 2
来自Java，我正在尝试在Ruby中实现LinkedList。我在Java中实现它的通常方法是有一个名为LinkedList的类和一个名为Node的私有(private)内部类，其中LinkedList的每个对象都作为Node对象。classLinkedListprivateclassNodeattr_accessor:val,:nextendend我不想将Node类暴露给外部世界。然而，通过Ruby中的这个设置，我可以使用这个访问LinkedList类之外的私有(private)Node类对象-node=LinkedList::Node.new我知道，在Ruby1.9中，我们可以使用
ruby-on-rails - 当我通过 rvm 使用 rails3 时，如何在 ubuntu 上安装 mysql2 gem？ - 2
我正在尝试绕过rails配置这个极其复杂的迷宫。到目前为止，我设法在ubuntu上设置了rvm(出于某种原因，ruby在ubuntu存储库中已经过时了)。我设法建立了一个Rails项目。我希望我的测试项目使用mysql而不是mysqlite。当我尝试“rakedb:migrate”时，出现错误:“!!!缺少mysql2gem。将其添加到您的Gemfile:gem'mysql2'”当我尝试“geminstallmysql”时，出现错误，告诉我需要为安装命令提供参数。但是，参数列表很大，我不知道该选择哪些。如何通过在ubuntu上运行的rvm和mysql获取rails3？谢谢。