MySQL查询执行（七）：临时表

临时表重名

---

思考：临时表和内存表的区别?

1. 内存表， 指的是使用Memory引擎的表， 建表语法是create table …engine=memory。 这种表的数据都保存在内存里， 系统重启的时候会被清空， 但是表结构还在。 除了这两个特性看上去比较“奇怪”外， 从其他的特征上看， 它就是一个正常的表。
2. 而临时表， 可以使用各种引擎类型 。 如果是使用InnoDB引擎或者MyISAM引擎的临时表， 写数据的时候是写到磁盘上的，即磁盘临时表。当然， 临时表也可以使用Memory引擎，此时为内存临时表。

临时表的特性

---

操作序列如下：

可以看到， 临时表在使用上有以下几个特点：

1. 建表语法是create temporarytable …。
2. 一个临时表只能被创建它的session访问， 对其他线程不可见。 所以， 图中session A创建的临时表t， 对于session B就是不可见的。
3. 临时表可以与普通表同名。
4. session A内有同名的临时表和普通表的时候， show create语句， 以及增删改查语句访问的是临时表。
5. show tables命令不显示临时表。
6. 临时表只能被创建它的session访问， 所以在这个session结束的时候， 会自动删除临时表。

思考：内存临时表和磁盘临时表有什么区别？

1. 内存临时表
   1. 高性能：内存临时表的数据存储在内存中，因此其读写速度通常比磁盘临时表快得多。对于需要大量中间结果计算或需要频繁访问数据的操作，内存临时表可以提供更高的性能。
   2. 无需IO操作：由于数据存储在内存中，因此不需要进行磁盘I/O操作，这进一步提高了其性能。
   3. 数据持久性：内存临时表在系统重启或会话结束后会消失，因此它们不适合存储需要长期保存的数据。
   4. 内存限制：内存临时表的大小受限于系统可用内存的大小。如果系统内存不足，可能会导致性能下降或操作失败。
   5. 不支持大型数据集：由于内存容量的限制，内存临时表可能无法处理大型数据集。
2. 磁盘临时表
   1. 数据持久性：磁盘临时表在会话结束后仍然存在，因此它们可以存储需要长期保存的数据。
   2. 支持大型数据集：由于磁盘具有更大的存储容量，因此磁盘临时表可以处理大型数据集。
   3. 不受内存限制：磁盘临时表的大小不受系统可用内存的限制，因此它们可以存储更多的数据。
   4. 性能较低：与内存临时表相比，磁盘临时表的读写速度较慢，因为它们需要进行磁盘I/O操作。
   5. 可能导致IO瓶颈：当大量并发用户或查询访问磁盘临时表时，可能会导致磁盘IO成为性能瓶颈。

临时表的应用

---

由于不用担心线程之间的重名冲突， 临时表经常会被用在复杂查询的优化过程中。 其中， 分库分表系统的跨库查询就是一个典型的使用场景。

一般分库分表的场景， 就是要把一个逻辑上的大表分散到不同的数据库实例上。 比如。 将一个大表ht， 按照字段f， 拆分成1024个分表， 然后分布到32个数据库实例上。 如下图所示：

一般情况下， 这种分库分表系统都有一个中间层proxy。 不过， 也有一些方案会让客户端直接连接数据库， 也就是没有proxy这一层。

在这个架构中， 分区key的选择是以“减少跨库和跨表查询”为依据的。 如果大部分的语句都会包含f的等值条件， 那么就要用f做分区键。

比如下面这条语句：

select v from ht where f=N;

这时， 我们就可以通过分表规则（比如， N%1024)来确认需要的数据被放在了哪个分表上。 这种语句只需要访问一个分表， 是分库分表方案最欢迎的语句形式了。

但是， 如果这个表上还有另外一个索引k， 并且查询语句是这样的：

select v from ht where k >= M order by t_modified desc limit 100;

这时候， 由于查询条件里面没有用到分区字段f， 只能到所有的分区中去查找满足条件的所有行， 然后统一做order by的操作。 这种情况下， 有两种比较常用的思路。

1. 在proxy层的进程代码中实现排序。
   1. 处理速度快， 拿到分库的数据以后， 直接在内存中参与计算。
   2. 需要的开发工作量比较大，对中间层的开发能力要求比较高。
   3. 对proxy端的压力比较大， 尤其是很容易出现内存不够用和CPU瓶颈的问题。
2. 把各个分库拿到的数据， 汇总到一个MySQL实例的一个表中， 然后在这个汇总实例上做逻辑操作。

比如上面这条语句， 执行流程可以类似这样：

1）在汇总库上创建一个临时表temp_ht， 表里包含三个字段v、 k、 t_modified。

2）在各个分库上执行如下语句。

select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;

3）把分库执行的结果插入到temp_ht表中。

4）执行如下语句得到结果。

select v from temp_ht order by t_modified desc limit 100

该过程对应流程图：

为什么临时表可以重名

---

执行如下语句的时候，MySQL要给InnoDB表创建一个frm文件，用于保存表结构定义，还要有地方保存表数据。

create temporary table temp_t(id int primary key)engine=innodb;

这个frm文件放在临时文件目录下， 文件名的后缀是.frm， 前缀是“#sql{进程id}_{线程id}_序列号”。 你可以使用select @@tmpdir命令， 来显示实例的临时文件目录。

关于表中数据存放的方式，在不同的MySQL版本中有着不同的处理方式：

1. 在5.6以及之前的版本里， MySQL会在临时文件目录下创建一个相同前缀、 以.ibd为后缀的文件， 用来存放数据文件。
2. 而从 5.7版本开始， MySQL引入了一个临时文件表空间， 专门用来存放临时文件的数据。 因此， 我们就不需要再创建ibd文件了。

从文件名的前缀规则， 我们可以看到， 其实创建一个叫作t1的InnoDB临时表， MySQL在存储上认为我们创建的表名跟普通表t1是不同的， 因此同一个库下面已经有普通表t1的情况下， 还是可以再创建一个临时表t1的。

示例如下：

这个进程的进程号是1234， session A的线程id是4， session B的线程id是5。 所以你看到了， session A和session B创建的临时表， 在磁盘上的文件不会重名。

MySQL维护数据表， 除了物理上要有文件外， 内存里面也有一套机制区别不同的表， 每个表都对应一个table_def_key。

1. 一个普通表的table_def_key的值是由“库名+表名”得到的， 所以如果你要在同一个库下创建两个同名的普通表， 创建第二个表的过程中就会发现table_def_key已经存在了。
2. 对于临时表， table_def_key在“库名+表名”基础上， 又加入了“server_id+thread_id”。

也就是说， session A和sessionB创建的两个临时表t1， 它们的table_def_key不同， 磁盘文件名也不同， 因此可以并存。

在实现上， 每个线程都维护了自己的临时表链表。 这样每次session内操作表的时候， 先遍历链表， 检查是否有这个名字的临时表， 如果有就优先操作临时表， 如果没有再操作普通表； 在session结束的时候， 对链表里的每个临时表， 执行 “DROP TEMPORARY TABLE +表名”操作。

这时候你会发现， binlog中也记录了DROP TEMPORARY TABLE这条命令。 你一定会觉得奇怪， 临时表只在线程内自己可以访问， 为什么需要写到binlog里面？

这就涉及到临时表和主备复制了。

临时表和主备复制

---

既然写binlog， 就意味着备库需要。

假设在主库上执行如下语句序列：

create table t_normal(id int primary key, c int)engine=innodb;/*Q1*/ 
create temporary table temp_t like t_normal;/*Q2*/
insert into temp_t values(1,1);/*Q3*/
insert into t_normal select * from temp_t;/*Q4*/

如果关于临时表的操作都不记录， 那么在备库就只有create table t_normal表和insert into t_normal select * from temp_t这两个语句的binlog日志， 备库在执行到insert into t_normal的时候， 就会报错“表temp_t不存在”。

注1：如果把binlog设置为row格式，则跟临时表有关的语句，就不会记录到binlog。因为binlog是row格式时，在记录insert into t_normal的binlog时，记录的是这个操作的数据，即：write_row event里面记录的逻辑是“插入一行数据（1，1）”。

注2：只在binlog_format=statment/mixed的时候，binlog才会记录临时表的操作。

这种情况下，创建临时表的语句会传到备库执行，因此备库的同步线程就会创建这个临时表。主库在线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的。所以，这时候我们就需要在主库上再写一个DROP TEMPOPARY TABLE传给备库执行。

问1：MySQL在记录binlog的时候， 不论是create table还是alter table语句， 都是原样记录， 甚至于连空格都不变。 但是如果执行drop table t_normal， 系统记录binlog就会写成：

DROP TABLE `t_normal` /* generated by server */

也即是改成了标准格式，为什么要这么做？

答：drop table命令是可以一次删除多个表的。 比如， 在上面的例子中， 设置binlog_format=row， 如果主库上执行 "drop table t_normal, temp_t"这个命令， 那么binlog中就只能记录：

DROP TABLE `t_normal` /* generated by server */

因为备库上并没有表temp_t， 将这个命令重写后再传到备库执行， 才不会导致备库同步线程停止。

所以， drop table命令记录binlog的时候， 就必须对语句做改写。 “/* generated byserver */”说明了这是一个被服务端改写过的命令。

问2：主库上不同的线程创建同名的临时表是没关系的，但是传到备库执行是怎么处理的？

举例说明，下面的序列中实例S是M的备库。

主库M上的两个session创建了同名的临时表t1， 这两个create temporarytable t1 语句都会被传到备库S上。

但是， 备库的应用日志线程是共用的， 也就是说要在应用线程里面先后执行这个create 语句两次。 （即使开了多线程复制， 也可能被分配到从库的同一个worker中执行） 。 那么， 这会不会导致同步线程报错 ？

答：不会，否则临时表就是一个bug了。也就是说，备库线程在执行的时候，要把这两个t1表当作两个不同的临时表处理。

问3：上一个问题中所说的，要把两个t1表当作两个不同的临时表处理。是如何实现的？

MySQL在记录binlog的时候， 会把主库执行这个语句的线程id写到binlog中。 这样， 在备库的应用线程就能够知道执行每个语句的主库线程id， 并利用这个线程id来构造临时表的table_def_key：

1. session A的临时表t1， 在备库的table_def_key就是： 库名+t1+“M的serverid”+“session A的thread_id”。
2. session B的临时表t1， 在备库的table_def_key就是 ： 库名+t1+“M的serverid”+“session B的thread_id”。

由于table_def_key不同， 所以这两个表在备库的应用线程里面是不会冲突的。

小结：思考题

---

思考1：在复杂的join连接中，为什么可以使用临时表，是基于什么考虑的？

1. 不同session的临时表是可以重名的，如果有多个session同时执行join优化，无需担心表名重复导致建表失败问题。
2. 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。

思考2：下面的语句序列是创建一个临时表，并将其改名：

从上图可以看到，可以使用alter table语句修改临时表的表名，而不能使用rename语法。这是为什么？

答：在实现上，执行rename table语句的时候，要求按照“库名/表名.frm”的规则去磁盘找文件，但是临时表在磁盘上的frm文件是放在tmpdir目录下的，并且文件名的规则是“#sql{进程id}_{线程id}_序列号.frm”， 因此会报“找不到文件名”的错误。

临时表的使用

---

union执行流程

---

假设有如下表结构，t1：

-- 创建表t1
create table t1(id int primary key,a int, b int, index(a));

-- 写入1000条数据
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();

查询执行计划：

由图可以看到：

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

语句执行流程如下：

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

上述过程流程图：

从图中可以看到，这里的内存临时表起到了暂存数据的作用（暂存，用于去重），而且计算过程还用上了临时表主键id的唯一性约束，实现了union的语义。

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

可以看到， 第二行的Extra字段显示的是Using index， 表示只使用了覆盖索引， 没有用临时表了。

group by执行流程

---

另外一个常见的使用临时表的例子是group by，语句如下：

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

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

在Extra字段里面， 我们可以看到三个信息：

1. Using index， 表示这个语句使用了覆盖索引， 选择了索引a， 不需要回表。
2. Using temporary， 表示使用了临时表。
3. Using filesort， 表示需要排序。

注：group by需要暂存结果，所以需要临时表。

语句执行流程如下：

1. 创建内存临时表， 表里有两个字段m和c， 主键是m。
2. 扫描表t1的索引a， 依次取出叶子节点上的id值， 计算id%10的结果， 记为x。
   1. 如果临时表中没有主键为x的行， 就插入一个记录(x,1)。
   2. 如果表中有主键为x的行， 就将x这一行的c值加1。
3. 遍历完成后， 再根据字段m做排序， 得到结果集返回给客户端。

注：group by默认会对结果进行排序。

执行流程图如下：

内存临时表排序流程图：

上图中对内存临时表的排序是虚线框内的过程。

该语句的执行结果如下：

如果你的结果并不需要对结果进行排序，则可以在SQL语句末尾增加order by null，也就是改成：

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的数据量很大， 很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。

group by优化方法（索引）

---

group by语句存在问题

不论是使用内存临时表还是磁盘临时表， group by逻辑都需要构造一个带唯一索引的表， 执行代价都是比较高的。 如果表的数据量比较大，则group by语句执行起来会很慢。

group by优化思路

问1：执行group by语句为什么需要临时表？

答：当分组的key无序时，需要先排序，才能做聚合操作。如果分组的key有序，则直接顺序扫描，并做聚合操作即可。而InnoDB的索引都是有序的，所以可以被善加利用。

假设，现在有一个类似下图的数据结构：

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

具体流程如下：

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

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

问2：如果表的数据量比较大，上面这个group by语句执行起来就会很慢，有没有什么方法可以对其进行优化？

在MySQL 5.7版本支持了generated column机制， 用来实现列数据的关联更新。 你可以用下面的方法创建一个列z， 然后在z列上创建一个索引。

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语句的explain结果， 如下图所示：

从Extra字段可以看到， 这个语句的执行不再需要临时表， 也不需要排序了。

注：对于不支持generated column机制的场景（MySQL 5.6及之前的版本），可以自己给表增加字段，并加索引，模拟上述过程。

group by优化方法（直接排序）

---

临时表可以是基于内存的（Memory引擎），也可以是基于磁盘的（MyISAM或InnoDB引擎）。

问：如果遇到无法加索引或者不适合加索引的场景，这时候group by应该怎么优化？

在group by语句中加上SQL_BIG_RESULT这个提示（hint），告知优化器：语句涉及数据量很大，直接使用磁盘临时表。

磁盘临时表默认是B+树存储，存储效率不如数组。所以优化器在得知数据量很大后，直接用数组存储，即sort buffer。当排序操作无法在ssort buffer中完成时，会创建磁盘临时表来完成排序。

总结：在group by语句中加上SQL_BIG_RESULT提示，优化器会对数据进行排序。

示例如下：

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. 排序完成后， 就得到了一个有序数组。

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

执行流程图：

执行计划：

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

小结：思考题

---

小结：

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

思考：MySQL什么时候会使用内部临时表？

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

思考：sort buffer、内存临时表、磁盘临时表之间的联系？

无论是内存临时表还是磁盘临时表，如果需要排序，都是在sort buffer中进行，只是排序后保存的位置不同。一个保存在内存临时表中，一个保存在磁盘临时表中。

---

划重点：每一篇文章都有对应PDF，若有需要请留言！（PDF中的侧重点看起来更加分明）

原文地址：https://blog.csdn.net/weixin_47156401/article/details/143865994

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！