MySQL內(nèi)部臨時(shí)表的具體使用
發(fā)布日期：2022-02-05 17:35 | 文章來源：CSDN
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í)表成功
- 拿到第一行id=1000，試圖插入到內(nèi)存臨時(shí)表，但由于1000這個(gè)值已經(jīng)存在于內(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沒有去重的語(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í)行過程
- 創(chuàng)建內(nèi)存臨時(shí)表，表里有兩個(gè)字段m和c，m為主鍵
- 掃描t1的索引a，依次取出葉子節(jié)點(diǎn)上的id值，計(jì)算id%10，記為x
- 如果內(nèi)存臨時(shí)表中沒有主鍵為x的行，插入一行記錄
(x,1) - 如果內(nèi)存臨時(shí)表中有主鍵為x的行，將x這一行的c值加1
- 如果內(nèi)存臨時(shí)表中沒有主鍵為x的行，插入一行記錄
- 遍歷完成后，再根據(jù)字段m做排序，得到結(jié)果集返回給客戶端
排序過程
ORDER BY NULL
-- 跳過最后的排序階段，直接從臨時(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開始的 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)成磁盤臨時(shí)表，默認(rèn)采用InnoDB引擎 -- 如果t1很大，這個(gè)查詢需要的磁盤臨時(shí)表就會(huì)占用大量的磁盤空間 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í)表還是磁盤臨時(shí)表，GROUP BY都需要構(gòu)造一個(gè)帶唯一索引的表，執(zhí)行代價(jià)較高
需要臨時(shí)表的原因：每一行的id%100是無序的，因此需要臨時(shí)表，來記錄并統(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è)過程不需要臨時(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í)表，再退化成磁盤臨時(shí)表
可以直接用磁盤臨時(shí)表的形式，在GROUP BY語(yǔ)句中SQL_BIG_RESULT（告訴優(yōu)化器涉及的數(shù)據(jù)量很大）
磁盤臨時(shí)表原本采用B+樹存儲(chǔ)，存儲(chǔ)效率還不如數(shù)組，優(yōu)化器看到SQL_BIG_RESULT，會(huì)直接用數(shù)組存儲(chǔ)
- 即放棄使用臨時(shí)表，直接進(jìn)入排序階段
執(zhí)行過程
-- 沒有再使用臨時(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ì)利用磁盤臨時(shí)文件輔助排序）
排序完成后，得到一個(gè)有序數(shù)組，遍歷有序數(shù)組，得到每個(gè)值出現(xiàn)的次數(shù)（類似上面優(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：沒有了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)唯一鍵沖突，就跳過
- 否則插入成功
- 遍歷完成后，將臨時(shí)表作為結(jié)果集返回給客戶端
小結(jié)
- 用到內(nèi)部臨時(shí)表的場(chǎng)景
- 如果語(yǔ)句執(zhí)行過程中可以一邊讀數(shù)據(jù)，一邊得到結(jié)果，是不需要額外內(nèi)存的
- 否則需要額外內(nèi)存來保存中間結(jié)果
join_buffer是無序數(shù)組，sort_buffer是有序數(shù)組，臨時(shí)表是二維表結(jié)構(gòu)- 如果執(zhí)行邏輯需要用到二維表特性，就會(huì)優(yōu)先考慮使用臨時(shí)表如果對(duì)
GROUP BY語(yǔ)句的結(jié)果沒有明確的排序要求，加上ORDER BY NULL（MySQL 5.6） - 盡量讓
GROUP BY過程用上索引，確認(rèn)EXPLAIN結(jié)果沒有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來告訴優(yōu)化器直接使用排序算法（跳過臨時(shí)表）
參考資料
《MySQL實(shí)戰(zhàn)45講》
到此這篇關(guān)于MySQL內(nèi)部臨時(shí)表的具體使用的文章就介紹到這了,更多相關(guān)MySQL內(nèi)部臨時(shí)表內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信