189 8069 5689
什么是内部临时表？
不同于手工创建的临时表，在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/