MySQL 内部临时表

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

执行语句

(SELECT 1000 AS f) UNION (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);

mysql> EXPLAIN (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.00 | Backward index scan; Using index |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary                  |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+

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

union result

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没有去重的语义，一次执行子查询，得到的结果直接发给客户端，不需要内存临时表

mysql> EXPLAIN (SELECT 1000 AS f) UNION ALL (SELECT id FROM t1 ORDER BY id DESC LIMIT 2);
+----+----------