MySQL-36：内部临时表

素颜马尾好姑娘i 2022-12-31 13:20 140阅读 0赞

#### 36.1 MySQL中的两种临时表 ####

*  外部临时表

通过`CREATE TEMPORARY TABLE` 创建的临时表，这种临时表称为外部临时表。这种临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭。这种临时表的命名与非临时表可以同名。

*  内部临时表

内部临时表会被MySQL自动创建并用来存储某些操作的中间结果，通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。

内部临时表有两种类型：**①：内存临时表：** 所有数据都会存在内存中。**②：磁盘临时表：** 数据存储在磁盘上，使用那种类型的内部临时表取决于参数`tmp_table_size`，当需求的临时表大小大于参数设定值，则会转用磁盘临时表。

[MySQL 临时表使用][MySQL]

#### 36.2 内部临时表的使用时机 ####

[using where ;using index;using null解释][using where _using index_using null]

##### 1. union #####

现有表t，字段有主键id，字段a、b，a带有普通索引，数据(1，1，1)到(1000，1000，1000)。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

现在执行如下语句：通过`explain命令`我们可以看到，在Extra字段可以看到：第三行可以看到Using temporary，表示使用了临时表。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]

它的执行流程如下：

*  创建一个临时表，只有一个字段a，且a为主键。
 *  执行第一个查询，查到1000插入临时表。
 *  执行第二个查询，因为union的语义，查到的996~1004中的1000会插入临时表失败。
 *  取出数据，返回结果，删除临时表

如果使用的是union all，则使用不到临时表。

##### 2. group by #####

现有如下sql，我们可以通过`explain命令`可以看见Extra字段中：

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]

执行流程如下：

*  建立临时表，有字段s，且s为主键
 *  插入1、2、3…9、0的数值
 *  将插入的数据进行排序
 *  返回结果，删除临时表

#### 36.3 group by 的优化 ####

优化有两个方向，①：将分组的数据建立一个索引，因为索引是有序，所以不需要临时表；②如果可能的话，使用的临时表直接磁盘临时表，可减少麻烦。

##### 1.索引 #####

通过如下方式添加索引，分组的数据如果有索引，就不需要临时表，因为它可以直接从索引上数出有多少个。

# 加字段
    alter table t add m int(11) not null 
    # 加索引
    alter TABLE t add INDEX m(m);
    # 赋值
    update t set m = id%10

我们再执行explain命令就可以发现差异，没有Using temporary

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 3]

不过这个麻烦了，在MySQL5.6之后，引入 generated column 机制 ，可以直接达到上面的效果。

sql如下：

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

##### 2.直接排序 #####

这个情况前提为内存表不够用的情况下，我们通过参数设置直接使用磁盘临时表，避免浪费。我们加上 SQL\_BIG\_RESULT即可直接使用磁盘临时表。

select SQL_BIG_RESULT id%10 as m, from t group by m;

[MySQL]: https://www.cnblogs.com/duanxz/p/3724120.html
[using where _using index_using null]: https://www.cnblogs.com/wy123/p/7366486.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/6168fcfd1ff14383bbfc826f554f9fed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201229214319543.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201229214329995.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201229214340532.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center