mysql的內部臨時表

什麼是內部臨時表？

不同於手工建立的臨時表，在sql執行過程中可能會用到臨時檔案儲存查詢結果，稱為internal temporary table；

該過程由Mysql自動完成，使用者無法手工干預；

這些表或使用memory引擎存於記憶體，或使用MyISAM引擎存於磁碟；

 

何時生成

使用order  by /group by的列並非全來自於join queue的第一個表

Distinct 和 order by聯合使用

多表連線需要儲存中間結果集

 

如何儲存

SQL_SMALL_RESULT會使用記憶體臨時表，除非包含必須使用磁碟臨時表的條件：

     當表包含blob/text列，或group by/distinct的列大於512位元組時，必須使用磁碟臨時表；

     當臨時表 > min(tmp_table_size, max_heap_table_size)時，會自動將記憶體臨時錶轉化為磁碟臨時表

 

可透過狀態變數created_tmp_tables/created_tmp_disk_tables檢視內部臨時表的使用情況

 

 

內部臨時表某些情況下會自動生成索引，以提升效能

MySQL does create two keys on internal temporary tables namely ‘group_key‘ and ‘distinct_key‘ in the following conditions:

If there is any aggregate function and/or group-by (group_key)

Distinct column name(group_key)

Distinct in combination with group-by/aggregation functions (distinct_key)

http://venublog.com/2010/03/08/when-indexes-are-created-in-internal-temporary-tables/

 

已經有人開發相應patch，可透過hint強行在內部臨時表上建立 index

SELECT

    SUM(aggrpt.imps) as imps,

    SUM(aggrpt.clicks) as clicks,

    SUM(aggrpt.pos) as pos

 

FROM aggrpt

LEFT JOIN

(

    SELECT

    DISTINCT ext_group_id, group_id

    FROM sub

) sub2  ON(sub2.ext_group_id=aggrpt.adgroupid)

 

GROUP BY

aggrpt.report_date,

aggrpt.campaignid,

aggrpt.adgroupid,

aggrpt.keywordid

ORDER BY NULL

INTO OUTFILE '/tmp/test-sub.txt'

--------------

 

Query OK, 47827 rows affected (6 min 47.48 sec)

 

有兩種方法改進：1  將子查詢改寫為一個臨時表，並在ext_group_id上建立索引；2 對中間結果集新增索引 ，sub2 <strong>USE INDEX(ext_group_id)</strong> ON(sub2.ext_group_id=aggrpt.adgroupid)

改進後的執行時間

Query OK, 47827 rows affected (7.18 sec)

 

 

http://venublog.com/2010/03/06/how-to-improve-subqueries-derived-tables-performance/