什么是內(nèi)部臨時(shí)表？

創(chuàng)新互聯(lián)公司是一家專注于成都做網(wǎng)站、網(wǎng)站制作與策劃設(shè)計(jì),剛察網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)公司做網(wǎng)站,專注于網(wǎng)站建設(shè)十年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:剛察等地區(qū)。剛察做網(wǎng)站價(jià)格咨詢:13518219792

不同于手工創(chuàng)建的臨時(shí)表，在sql執(zhí)行過程中可能會(huì)用到臨時(shí)文件存儲(chǔ)查詢結(jié)果，稱為internal temporary table；

該過程由Mysql自動(dòng)完成，用戶無法手工干預(yù)；

這些表或使用memory引擎存于內(nèi)存，或使用MyISAM引擎存于磁盤；

 

何時(shí)生成

使用order  by /group by的列并非全來自于join queue的第一個(gè)表

Distinct 和 order by聯(lián)合使用

多表連接需要保存中間結(jié)果集

 

如何保存

SQL_SMALL_RESULT會(huì)使用內(nèi)存臨時(shí)表，除非包含必須使用磁盤臨時(shí)表的條件：

     當(dāng)表包含blob/text列，或group by/distinct的列大于512字節(jié)時(shí)，必須使用磁盤臨時(shí)表；

     當(dāng)臨時(shí)表 > min(tmp_table_size, max_heap_table_size)時(shí)，會(huì)自動(dòng)將內(nèi)存臨時(shí)表轉(zhuǎn)化為磁盤臨時(shí)表

 

可通過狀態(tài)變量created_tmp_tables/created_tmp_disk_tables查看內(nèi)部臨時(shí)表的使用情況

 

 

內(nèi)部臨時(shí)表某些情況下會(huì)自動(dòng)生成索引，以提升性能

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/

 

已經(jīng)有人開發(fā)相應(yīng)patch，可通過hint強(qiáng)行在內(nèi)部臨時(shí)表上創(chuàng)建 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)

 

有兩種方法改進(jìn)：1  將子查詢改寫為一個(gè)臨時(shí)表，并在ext_group_id上創(chuàng)建索引；2 對中間結(jié)果集添加索引 ，sub2 USE INDEX(ext_group_id) ON(sub2.ext_group_id=aggrpt.adgroupid)

改進(jìn)后的運(yùn)行時(shí)間

Query OK, 47827 rows affected (7.18 sec)

 

 

http://venublog.com/2010/03/06/how-to-improve-subqueries-derived-tables-performance/

 

新聞標(biāo)題：mysql的內(nèi)部臨時(shí)表-創(chuàng)新互聯(lián)
分享地址：http://weahome.cn/article/jgcgo.html