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/