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 USE INDEX(ext_group_id) 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/