union執(zhí)行

為了便于分析，使用一下sql來進行舉例

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í)行以下sql

(select 1000 as f) union (select id from t1 order by id desc limit 2);

這段sql的語義是，取兩個子查詢的的并集，并且去重

可以看到，第二行的key是primary，說明第二個子查詢使用索引id。第三行的Extra字段，表示在子查詢union的時候，使用了臨時表（Using temporary）。
這個語句的執(zhí)行流程是這樣的：
1）創(chuàng)建一個內存臨時表，這個臨時表只有一個整形字段f，并且f是主鍵字段
2）執(zhí)行第一個子查詢，將1000存在臨時表
3）執(zhí)行第二個子查詢，拿到第一行id=1000，并試圖插入到臨時表，但是由于1000這個值已經存在臨時表了，違法了唯一性約束，所以插入失敗，接著取到第二行數據999，插入臨時表成功
4）從臨時表中按行取出數據，返回結果，并刪除臨時表，結果中包含兩條數據就是1000和999
可以看到，臨時表起到了暫存數據的作用，而且存在唯一性約束，實現了union去重的語義

group by

另外一個常見的使用臨時表的例子就是group by，我們看一下以下sql

select id%10 as m, count(*) as c from t1 group by m;

這個語句就是根據t1表的數據，根據id%10進行分組，并按照m的結果排序后輸出

在Extra字段中，我們看到了三個信息：

1）Using index，表示這個語句使用了覆蓋索引，選擇了索引 a；
2）Using temporary，表示使用了臨時表；
3）Using filesort，表示需要排序；

這個語句的執(zhí)行流程是這樣的：

1）創(chuàng)建內存臨時表，表里有字段m和c，主鍵是m；
2）掃描表t1的索引a，依次取出葉子節(jié)點上面的id值，計算id%10的結果，記為x；

• 如果臨時表沒有主鍵x，就插入一個記錄（x,1）；
• 如果表中有主鍵x的行，就將x這一行的c值加1；

3）遍歷完成之后，再根據字段m做排序，得到結果

內存臨時表的大小是有限制的，參數tmp_table_size就是控制這個內存大小的，默認是16M，如果內存臨時表大小達到了上線，這時候就會把內存臨時表轉成磁盤臨時表，磁盤臨時表的默認引擎是InnoDB，如果表的數據量很大，很可能查詢就會占用大量的磁盤空間

到此這篇關于淺談Mysql在什么情況下會使用內部臨時表的文章就介紹到這了,更多相關Mysql 內部臨時表內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論