union執(zhí)行

為了便于分析，使用一下sql來進(jìn)行舉例

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)建一個內(nèi)存臨時表，這個臨時表只有一個整形字段f，并且f是主鍵字段
2）執(zhí)行第一個子查詢，將1000存在臨時表
3）執(zhí)行第二個子查詢，拿到第一行id=1000，并試圖插入到臨時表，但是由于1000這個值已經(jīng)存在臨時表了，違法了唯一性約束，所以插入失敗，接著取到第二行數(shù)據(jù)999，插入臨時表成功
4）從臨時表中按行取出數(shù)據(jù)，返回結(jié)果，并刪除臨時表，結(jié)果中包含兩條數(shù)據(jù)就是1000和999
可以看到，臨時表起到了暫存數(shù)據(jù)的作用，而且存在唯一性約束，實現(xiàn)了union去重的語義

group by

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

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

這個語句就是根據(jù)t1表的數(shù)據(jù)，根據(jù)id%10進(jìn)行分組，并按照m的結(jié)果排序后輸出

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

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

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

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

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

3）遍歷完成之后，再根據(jù)字段m做排序，得到結(jié)果

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

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

最新評論