什么是内部临时表？

创新互联公司是一家专注于成都做网站、网站制作与策划设计,刚察网站建设哪家好?创新互联公司做网站,专注于网站建设十年,网设计领域的专业建站公司;建站业务涵盖:刚察等地区。刚察做网站价格咨询:13518219792

不同于手工创建的临时表，在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/

 

当前名称：mysql的内部临时表-创新互联
URL网址：http://csdahua.cn/article/jgcgo.html