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

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

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

UNION

UNION語(yǔ)義：取兩個(gè)子查詢結(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í)行語(yǔ)句

(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

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

UNION RESULT

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

UNION ALL

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

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í)行語(yǔ)句

-- 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：表示使用了臨時(shí)表

Using filesort：表示需要排序

執(zhí)行過(guò)程

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

排序過(guò)程

ORDER BY NULL

-- 跳過(guò)最后的排序階段，直接從臨時(shí)表中取回?cái)?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開(kāi)始的
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í)行語(yǔ)句

-- 內(nèi)存臨時(shí)表的上限為1024 Bytes，但內(nèi)存臨時(shí)表不能完全放下100行數(shù)據(jù)，內(nèi)存臨時(shí)表會(huì)轉(zhuǎn)成磁盤(pán)臨時(shí)表，默認(rèn)采用InnoDB引擎
-- 如果t1很大，這個(gè)查詢需要的磁盤(pán)臨時(shí)表就會(huì)占用大量的磁盤(pán)空間
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)存臨時(shí)表還是磁盤(pán)臨時(shí)表，GROUP BY都需要構(gòu)造一個(gè)帶唯一索引的表，執(zhí)行代價(jià)較高

需要臨時(shí)表的原因：每一行的id%100是無(wú)序的，因此需要臨時(shí)表，來(lái)記錄并統(tǒng)計(jì)結(jié)果

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

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

-- MySQL 5.7上執(zhí)行
ALTER TABLE t1 ADD COLUMN z INT GENERATED ALWAYS AS(id % 100), ADD INDEX(z);
-- 使用了覆蓋索引，不需要臨時(shí)表，也不需要排序
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

直接排序

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

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

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

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

執(zhí)行過(guò)程

-- 沒(méi)有再使用臨時(shí)表，而是直接使用了排序算法
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，確定放入一個(gè)整型字段，記為m

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

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

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

對(duì)比DISTINCT

-- 標(biāo)準(zhǔn)SQL，SELECT部分添加一個(gè)聚合函數(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分組，計(jì)算每組a出現(xiàn)的次數(shù)

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

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

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

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

小結(jié)

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