MySQL 内部临时表

た入场券 2023-03-02 10:18 84阅读 0赞

# UNION #

UNION语义：取两个子查询结果的并集，重复的行只保留一行

## 表初始化 ##

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

## 执行语句 ##

1.  第二行的Key=PRIMARY，说明第二个子查询用到了索引id
2.  第三行的Extra字段为Using temporary。 **表示在对子查询的结果做UNION RESULT的时候，使用了临时表**

## union result ##

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

1.  创建一个内存临时表，这个内存临时表只有一个整型字段f，并且f为主键
2.  执行第一个子查询，得到1000，并存入内存临时表中
3.  执行第二个子查询
    
    1.  拿到第一行id=1000，试图插入到内存临时表，但由于1000这个值已经存在于内存临时表。**违反唯一性约束，插入失败，继续执行**
    2.  拿到第二行id=999，插入内存临时表成功
4.  从内存临时表中按行取出数据，返回结果，并删除内存临时表，结果中包含id=1000和id=999两行
5.  内存临时表起到了暂存数据的作用，还用到了内存临时表主键id的唯一性约束，实现UNION的语义

## UNION ALL ##

UNION ALL没有去重的语义，一次执行子查询，得到的结果直接发给客户端，不需要内存临时表

## GROUP BY ##

### 内存充足 ###

-- 16777216 Bytes = 16 MB
    mysql> SHOW VARIABLES like '%tmp_table_size%';
    +----------------+----------+
    | Variable_name  | Value    |
    +----------------+----------+
    | tmp_table_size | 16777216 |
    +----------------+----------+

### 执行语句 ###

-- MySQL 5.6上执行
    mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
    | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                        |
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
    |  1 | SIMPLE      | t1    | index | PRIMARY,a     | a    | 5       | NULL | 1000 | Using index; Using temporary; Using filesort |
    +----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------+
    
    mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
    +------+-----+
    | m    | c   |
    +------+-----+
    |    0 | 100 |
    |    1 | 100 |
    |    2 | 100 |
    |    3 | 100 |
    |    4 | 100 |
    |    5 | 100 |
    |    6 | 100 |
    |    7 | 100 |
    |    8 | 100 |
    |    9 | 100 |
    +------+-----+

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

### 执行过程 ###

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

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

## 排序过程 ##

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

## ORDER BY NULL ##

-- 跳过最后的排序阶段，直接从临时表中取回数据
    mysql> EXPLAIN SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL;
    +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+
    | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                        |
    +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+
    |  1 | SIMPLE      | t1    | index | PRIMARY,a     | a    | 5       | NULL | 1000 | Using index; Using temporary |
    +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------+
    
    -- t1中的数据是从1开始的
    mysql> SELECT id%10 AS m, COUNT(*) AS c FROM t1 GROUP BY m ORDER BY NULL;
    +------+-----+
    | m    | c   |
    +------+-----+
    |    1 | 100 |
    |    2 | 100 |
    |    3 | 100 |
    |    4 | 100 |
    |    5 | 100 |
    |    6 | 100 |
    |    7 | 100 |
    |    8 | 100 |
    |    9 | 100 |
    |    0 | 100 |
    +------+-----+

## 内存不足 ##

SET tmp_table_size=1024;

### 执行语句 ###

-- 内存临时表的上限为1024 Bytes，但内存临时表不能完全放下100行数据，内存临时表会转成磁盘临时表，默认采用InnoDB引擎
    -- 如果t1很大，这个查询需要的磁盘临时表就会占用大量的磁盘空间
    mysql> SELECT id%100 AS m, count(*) AS c FROM t1 GROUP BY m ORDER BY NULL LIMIT 10;
    +------+----+
    | m    | c  |
    +------+----+
    |    1 | 10 |
    |    2 | 10 |
    |    3 | 10 |
    |    4 | 10 |
    |    5 | 10 |
    |    6 | 10 |
    |    7 | 10 |
    |    8 | 10 |
    |    9 | 10 |
    |   10 | 10 |
    +------+----+

## 优化方案 ##

### 优化索引 ###

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

1.  不论使用内存临时表还是磁盘临时表，GROUP BY都需要构造一个带唯一索引的表，执行代价较高
2.  需要临时表的原因：每一行的id%100是无序的，因此需要临时表，来记录并统计结果
3.  如果可以确保输入的数据是有序的，那么计算GROUP BY时，只需要从左到右顺序扫描，依次累加即可
    
    1.  当碰到第一个1的时候，已经累积了X个0，结果集里的第一行为(0,X)
    2.  当碰到第一个2的时候，已经累积了Y个1，结果集里的第一行为(1,Y)
    3.  整个过程不需要临时表，也不需要排序

-- MySQL 5.7上执行
    ALTER TABLE t1 ADD COLUMN z INT GENERATED ALWAYS AS(id % 100), ADD INDEX(z);
    
    -- 使用了覆盖索引，不需要临时表，也不需要排序
    mysql> EXPLAIN SELECT z, COUNT(*) AS c FROM t1 GROUP BY z;
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
    |  1 | SIMPLE      | t1    | NULL       | index | z             | z    | 5       | NULL | 1000 |   100.00 | Using index |
    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

## 直接排序 ##

1.  一个GROUP BY语句需要放到临时表的数据量特别大，还是按照先放在内存临时表，再退化成磁盘临时表
2.  可以直接用磁盘临时表的形式，在GROUP BY语句中SQL\_BIG\_RESULT（告诉优化器涉及的数据量很大）
3.  磁盘临时表原本采用B+树存储，存储效率还不如数组，优化器看到SQL\_BIG\_RESULT，会直接用数组存储。**即放弃使用临时表，直接进入排序阶段**

### 执行过程 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 4]

-- 没有再使用临时表，而是直接使用了排序算法
    mysql> EXPLAIN SELECT SQL_BIG_RESULT id%100 AS m, COUNT(*) AS c FROM t1 GROUP BY m;
    +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
    | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                       |
    +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
    |  1 | SIMPLE      | t1    | index | PRIMARY,a     | a    | 5       | NULL | 1000 | Using index; Using filesort |
    +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+

1.  初始化sort\_buffer，确定放入一个整型字段，记为m
2.  扫描t1的索引a，依次取出里面的id值，将id%100的值放入sort\_buffer
3.  扫描完成后，对sort\_buffer的字段m做排序（sort\_buffer内存不够时，会利用磁盘临时文件辅助排序）
4.  排序完成后，得到一个有序数组，遍历有序数组，得到每个值出现的次数（类似上面优化索引的方式）

## 对比DISTINCT ##

-- 标准SQL，SELECT部分添加一个聚合函数COUNT(*)
    SELECT a,COUNT(*) FROM t GROUP BY a ORDER BY NULL;
    -- 非标准SQL
    SELECT a FROM t GROUP BY a ORDER BY NULL;
    
    SELECT DISTINCT a FROM t;

1.  标准SQL：按照字段a分组，计算每组a出现的次数
2.  非标准SQL：没有了COUNT(\*)，不再需要执行计算总数的逻辑
    
    1.  按照字段a分组，相同的a的值只返回一行，与DISTINCT语义一致
3.  如果不需要执行聚合函数，DISTINCT和GROUP BY的语义、执行流程和执行性能是相同的
    
    1.  创建一个临时表，临时表有一个字段a，并且在这个字段a上创建一个唯一索引
    2.  遍历表t，依次取出数据插入临时表中。 如果发现唯一键冲突，就跳过。否则插入成功
    3.  遍历完成后，将临时表作为结果集返回给客户端

## 小结 ##

1.  用到内部临时表的场景
    
    1.  如果语句执行过程中可以一边读数据，一边得到结果，是不需要额外内存的
    2.  否则需要额外内存来保存中间结果
2.  join\_buffer是无序数组，sort\_buffer是有序数组，临时表是二维表结构
3.  如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表
4.  如果对GROUP BY语句的结果没有明确的排序要求，加上ORDER BY NULL（MySQL 5.6）
5.  尽量让GROUP BY过程用上索引，确认EXPLAIN结果没有Using temporary和Using filesort
6.  如果GROUP BY需要统计的数据量不大，尽量使用内存临时表（可以适当调大tmp\_table\_size）
7.  如果数据量实在太大，使用SQL\_BIG\_RESULT来告诉优化器直接使用排序算法（跳过临时表）

PS：参考资料 《MySQL实战45讲》

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70]: /images/20230208/7ccd8113dcc5421088d6c9fac141a46c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 1]: /images/20230208/0c0366a9e51e4fe8aa8f562c83ad4b42.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 2]: /images/20230208/b0650b09ddb54ae5905b3185d2882608.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 3]: /images/20230208/06cad60af01d4c738a2400e1fc4c3f26.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMyNDY4OTg_size_16_color_FFFFFF_t_70 4]: /images/20230208/151838af9ea84779a058910e8de0fb61.png