（关注“数据库架构师”公众号，提升数据库技能，助力职业发展）

一、背景

大表查询中使用order by rand()获取随机数据，产生大量临时文件引起磁盘暴涨。

1. order by rand()原理

作用：从一次查询中随机返回数据

操作流程：

1. 创建一个heap引擎的临时表，字段名为 ”” 表的字段, 第一个字段为匿名;
2. 将表tb中的id数据按行读入到临时表中，同时给第一字段填入一个随机实数(0,1);
3. 按照第一个字段排序，返回
4. 查询完成删除临时表

2. 事例

order by rand() 需要使用临时表（Using temporary），需要使用文件排序（Using filesort），效率低下。

数据量：16777216行

执行：select name from test1 order by rand();

出现问题：表的数据量过大，临时文件增大，磁盘暴涨。

文件解析:

# sql是固定的前缀
# 23df是进程号的十六进制表示
# 0代表累加

3. 更改大表随机获取数据方式

方法一：

1. 取得这张表主键id的最大值M和最小值N；

2. 用随机函数生成一个最大值和最小值之间的数 X=(M-N)*rand()+N；

3. 取不小于X的第一个ID行。

方法二：

1. 取得整个表的系统预估行数；

2. 根据主键，分段取值。

二、临时表简介

MySQL中临时表主要有两类，包括外部临时表和内部临时表。外部临时表是通过语句create temporary table...创建的临时表。内部临时表与外部临时表的一个区别在于，我们看不到内部临时表的表结构定义文件frm。

内部临时表是一种特殊轻量级的临时表，用来进行性能优化。这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。这些操作可能包括在优化阶段或者执行阶段。这种内部表对用户来说是不可见的。临时表与普通表的主要区别在于是否在实例，会话，或语句结束后，自动清理数据。比如，内部临时表，我们在一个查询中，如果要存储中间结果集，而查询结束后，临时表就会自动回收，不会影响用户表结构和数据。本文主要针对内部临时表进行讲解。

三、参数说明

max_heap_table_size：这个参数主要针对用户创建的MEMORY表，限制内存表最大空间大小，注意不是记录数目，与单条记录的长度有关。

tmp_table_size：对于用户手工创建的内存表，只有参数max_heap_table_size起作用；对于内部产生的内存表，则参数max_heap_table_size和tmp_table_size同时起作用。对于内部产生的内存表(比如union，group by等产生的临时表)，先是采用内存表(memory表)，然后超过设置的阀值(max_heap_table_size,tmp_table_size)就会转为磁盘表，使用innodb引擎或者myisam引擎，通过参数
internal_tmp_disk_storage_engine指定。

Tmpdir：如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下。

四、常见的产生内部临时表场景

1. select慢查询

Using temporary，代表已经用到了临时表,但是不是一定会使用磁盘临时文件。实际限制由tmp_table_size和max_heap_table_size的值中较小的一个确定，如果内存中的临时表超出限制，MySQL自动将其转换为磁盘上的表。

2. inplace情况下的DDL操作

一旦语句被执行完或者kill掉临时文件就会消失,inplace情况下的DDL操作，这个记录的地方就是online log，当然如果改动少的话，直接存在内存里(参数innodb_sort_buffer_size可控制，同时这个参数也控制online log每个读写块的大小)面即可。这个onlinelog也是用临时文件存，创建在innodb_tmpdir，最大大小为参数
innodb_online_alter_log_max_size控制，如果超过这个大小了，DDL就会失败。临时文件的名字也类似上述的排序临时文件的名字。

3. union操作

union操作的含义是，取两个子查询结果的并集，重复的数据只保留一行，通过建立一个带主键的临时表，就可以解决“去重”问题，通过临时表存储最终的结果集，所以能看到执行计划中Extra这一项里面有“Using temporary”。

4. 临时表导致的常见问题

由于慢查询堆积引起的大量临时文件导致磁盘暴涨；

由于DDL执行期间mysql cash导致的临时文件残留。

五、如何避免使用临时表

升级mysql版本 ,5.7使用独立临时表空间 不要设置为压缩方式,可以很大程度避免问题;
tmp_table_size 设置一个合适的值,尽量避免应用磁盘的临时表文件;
对于ddl-copy方式 尽量采用pt-osc方式,能不走临时表就不要走;
优化慢查询,尽量减少use temporay。

如果这篇文章对你有帮助，还请帮忙点赞、转发 以下，你的支持会激励我们输出更多高质量的文章！

如果你还想看更多优质文章，欢迎关注我的公众号「数据库架构师」，提升数据库技能，助力职业发展。