MySQL什麼時候會使用內部臨時表?

小二郎**發表於2019-05-24
MySql

1.union執行過程

首先我們建立一個表t1

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

然後我們執行一下這條語句

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

首先說下union的語義，union的語義是取兩個結果的並集，重複的保留一行,然後我們來看下explain的結果，第二行的key=PRIMARY，說明用到了主鍵索引。

第三行的Extra的Using temporary說明用到了臨時表

 

下面我們看下這條語句的執行流程：

1.建立一個臨時表，只有f一個欄位，且為主鍵

2.將1000這個資料插入臨時表

3.子查詢中步驟:

1.插入1000進入臨時表,因為主鍵衝突，插入失敗

2.插入第二行900，插入成功

4.將臨時表資料作為結果返回，並刪除臨時表

這個過程的流程圖如下：

如果我們把union改成union all，就不需要使用臨時表了，因為union all是重複的也保留，

大家可以看到extra這一列已經沒有了Using temporary

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

2.group by執行過程

我們來看下面這條語句:

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

可以看到explain結果

Using index(使用到了覆蓋索引a，不需要回表); Using temporary(用到了臨時表); Using filesort(對資料進行了排序)

這條語句的意思是將id%10進行分組統計，並按照m進行排序

執行流程如下:

1.建立臨時表，增加m,c欄位，m是主鍵

2.計算id%10的結果記為x

3.如果臨時表裡面沒有主鍵為x的行，則插入(x,1)，如果有的話，就將該行的c值加1

4.遍歷完成後，按照m欄位排序返回結果給客戶端

流程圖如下

接下來我們看下這條語句的執行結果

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

其實，如果我們不需要對查詢結果進行排序，我們可以加一個order by null

我們執行一下這條語句

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

可以看到這裡沒有進行排序，由於掃描是從表t的id是從1開始的，所以第一行是1

如果我們執行下列語句，會發生什麼呢？

我們上面說的臨時表，其實是記憶體臨時表，如果我們把記憶體臨時表的容量改的比我們要查詢的資料的容量小，那麼就會使用到磁碟臨時表，磁碟臨時表的預設引擎是innodb

et tmp_table_size=1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10

　　

group by 優化方法--直接排序

其實在上面的關於從記憶體臨時錶轉化成磁碟臨時表是很浪費時間的，也就是說mysql，在執行過程中發現空間不夠了，在轉成磁碟臨時表，但是如果我們直接告訴mysql，我要查詢的資料很大，那麼mysql優化器就會想到，既然你告訴我資料很大，那麼我就直接用sort_buffer進行排序，如果sort_buffer記憶體不夠大，會用到磁碟臨時表輔助排序。

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

小結一下:

1.如果我們不需要對統計結果進行排序，可以加上order by null省去排序流程。

2.儘量讓排序過程用上記憶體臨時表，可以通過適當調大tmp_table_size的值來避免用到磁碟臨時表。

3.如果資料量實在太大，使用SQL_BIG_RESULT告訴優化器，直接使用排序演算法。

 

相關文章