MySQL內(nèi)部臨時表的具體使用

更新時間：2021年10月26日 09:42:11 作者：Yungyu

MySQL臨時表在很多場景中都會用到，比如用戶自己創(chuàng)建的臨時表用于保存臨時數(shù)據(jù)，以及MySQL內(nèi)部在執(zhí)行復(fù)雜SQL時，需要借助臨時表進(jìn)行分組、排序、去重等操作，本文就來詳細(xì)的介紹一下MySQL內(nèi)部臨時表

UNION

UNION語義：取兩個子查詢結(jié)果的并集，重復(fù)的行只保留一行

表初始化

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

執(zhí)行語句

(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                  |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+

第二行的Key=PRIMARY，Using temporary

表示在對子查詢的結(jié)果做UNION RESULT的時候，使用了臨時表

UNION RESULT

創(chuàng)建一個內(nèi)存臨時表，這個內(nèi)存臨時表只有一個整型字段f，并且f為主鍵
執(zhí)行第一個子查詢，得到1000，并存入內(nèi)存臨時表中
執(zhí)行第二個子查詢
- 拿到第一行id=1000，試圖插入到內(nèi)存臨時表，但由于1000這個值已經(jīng)存在于內(nèi)存臨時表
  - 違反唯一性約束，插入失敗，繼續(xù)執(zhí)行
- 拿到第二行id=999，插入內(nèi)存臨時表成功
從內(nèi)存臨時表中按行取出數(shù)據(jù)，返回結(jié)果，并刪除內(nèi)存臨時表，結(jié)果中包含id=1000和id=999兩行
內(nèi)存臨時表起到了暫存數(shù)據(jù)的作用，還用到了內(nèi)存臨時表主鍵id的唯一性約束，實(shí)現(xiàn)UNION的語義

UNION ALL

UNION ALL沒有去重的語義，一次執(zhí)行子查詢，得到的結(jié)果直接發(fā)給客戶端，不需要內(nèi)存臨時表

mysql> EXPLAIN (SELECT 1000 AS f) UNION ALL (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 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------+

GROUP BY

內(nèi)存充足

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

執(zhí)行語句

-- MySQL 5.6上執(zhí)行
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 |
+------+-----+

Using index：表示使用了覆蓋索引，選擇了索引a，不需要回表

Using temporary：表示使用了臨時表

Using filesort：表示需要排序

執(zhí)行過程

創(chuàng)建內(nèi)存臨時表，表里有兩個字段m和c，m為主鍵
掃描t1的索引a，依次取出葉子節(jié)點(diǎn)上的id值，計算id%10，記為x
- 如果內(nèi)存臨時表中沒有主鍵為x的行，插入一行記錄(x,1)
- 如果內(nèi)存臨時表中有主鍵為x的行，將x這一行的c值加1
遍歷完成后，再根據(jù)字段m做排序，得到結(jié)果集返回給客戶端

排序過程

ORDER BY NULL

-- 跳過最后的排序階段，直接從臨時表中取回數(shù)據(jù)
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中的數(shù)據(jù)是從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 |
+------+-----+

內(nèi)存不足

SET tmp_table_size=1024;

執(zhí)行語句

-- 內(nèi)存臨時表的上限為1024 Bytes，但內(nèi)存臨時表不能完全放下100行數(shù)據(jù)，內(nèi)存臨時表會轉(zhuǎn)成磁盤臨時表，默認(rèn)采用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 |
+------+----+

優(yōu)化方案

優(yōu)化索引

不論使用內(nèi)存臨時表還是磁盤臨時表，GROUP BY都需要構(gòu)造一個帶唯一索引的表，執(zhí)行代價較高

需要臨時表的原因：每一行的id%100是無序的，因此需要臨時表，來記錄并統(tǒng)計結(jié)果

如果可以確保輸入的數(shù)據(jù)是有序的，那么計算GROUP BY時，只需要
從左到右順序掃描，依次累加即可

當(dāng)碰到第一個1的時候，已經(jīng)累積了X個0，結(jié)果集里的第一行為(0,X)
當(dāng)碰到第一個2的時候，已經(jīng)累積了Y個1，結(jié)果集里的第一行為(1,Y)
整個過程不需要臨時表，也不需要排序

-- MySQL 5.7上執(zhí)行
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 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
2

直接排序

一個GROUP BY語句需要放到臨時表的數(shù)據(jù)量特別大，還是按照先放在內(nèi)存臨時表，再退化成磁盤臨時表

可以直接用磁盤臨時表的形式，在GROUP BY語句中SQL_BIG_RESULT（告訴優(yōu)化器涉及的數(shù)據(jù)量很大）

磁盤臨時表原本采用B+樹存儲，存儲效率還不如數(shù)組，優(yōu)化器看到SQL_BIG_RESULT，會直接用數(shù)組存儲

即放棄使用臨時表，直接進(jìn)入排序階段

執(zhí)行過程

-- 沒有再使用臨時表，而是直接使用了排序算法
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 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+

初始化sort_buffer，確定放入一個整型字段，記為m

掃描t1的索引a，依次取出里面的id值，將id%100的值放入sort_buffer

掃描完成后，對sort_buffer的字段m做排序（sort_buffer內(nèi)存不夠時，會利用磁盤臨時文件輔助排序）

排序完成后，得到一個有序數(shù)組，遍歷有序數(shù)組，得到每個值出現(xiàn)的次數(shù)（類似上面優(yōu)化索引的方式）

對比DISTINCT

-- 標(biāo)準(zhǔn)SQL，SELECT部分添加一個聚合函數(shù)COUNT(*)
SELECT a,COUNT(*) FROM t GROUP BY a ORDER BY NULL;
-- 非標(biāo)準(zhǔn)SQL
SELECT a FROM t GROUP BY a ORDER BY NULL;

SELECT DISTINCT a FROM t;

標(biāo)準(zhǔn)SQL：按照字段a分組，計算每組a出現(xiàn)的次數(shù)

非標(biāo)準(zhǔn)SQL：沒有了COUNT(*)，不再需要執(zhí)行計算總數(shù)的邏輯

按照字段a分組，相同的a的值只返回一行，與DISTINCT語義一致

如果不需要執(zhí)行聚合函數(shù) ，DISTINCT和GROUP BY的語義、執(zhí)行流程和執(zhí)行性能是相同的

創(chuàng)建一個臨時表，臨時表有一個字段a，并且在這個字段a上創(chuàng)建一個唯一索引
遍歷表t，依次取出數(shù)據(jù)插入臨時表中
- 如果發(fā)現(xiàn)唯一鍵沖突，就跳過
- 否則插入成功
遍歷完成后，將臨時表作為結(jié)果集返回給客戶端

小結(jié)

用到內(nèi)部臨時表的場景
- 如果語句執(zhí)行過程中可以一邊讀數(shù)據(jù)，一邊得到結(jié)果，是不需要額外內(nèi)存的
- 否則需要額外內(nèi)存來保存中間結(jié)果
join_buffer是無序數(shù)組，sort_buffer是有序數(shù)組，臨時表是二維表結(jié)構(gòu)
如果執(zhí)行邏輯需要用到二維表特性，就會優(yōu)先考慮使用臨時表如果對GROUP BY語句的結(jié)果沒有明確的排序要求，加上ORDER BY NULL（MySQL 5.6）
盡量讓GROUP BY過程用上索引，確認(rèn)EXPLAIN結(jié)果沒有Using temporary和Using filesort
如果GROUP BY需要統(tǒng)計的數(shù)據(jù)量不大，盡量使用內(nèi)存臨時表（可以適當(dāng)調(diào)大tmp_table_size）
如果數(shù)據(jù)量實(shí)在太大，使用SQL_BIG_RESULT來告訴優(yōu)化器直接使用排序算法（跳過臨時表）