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/