mysql 創(chuàng)建大量磁盤臨時(shí)表

發(fā)現(xiàn) mysql 中創(chuàng)建的臨時(shí)表基本都是磁盤臨時(shí)表，對(duì)此進(jìn)行排查。最后為臨時(shí)表機(jī)制說(shuō)明。

通過(guò)查看 mysql 狀態(tài)變量，看到創(chuàng)建的臨時(shí)表基本都是磁盤臨時(shí)表。

查看臨時(shí)表配置，默認(rèn)配置 16M，配置并沒(méi)有問(wèn)題。

創(chuàng)建兩個(gè)表，做一個(gè)查詢測(cè)試

create table t1(f1 int auto_increment primary key, f2 char(50), f3 datetime default now());
insert into t1 (f2) values ('趙'),('錢'),('孫'),('李');
create table t2(f1 int auto_increment primary key, f2 int, f3 char(50), f4 datetime default now());
insert into t2 (f2, f3) values ('3', '悟空'),('1', '四'),('4', '白');

刷新狀態(tài)，進(jìn)行查詢，發(fā)現(xiàn)創(chuàng)建的是磁盤表

兩張小表的查詢完全滿足創(chuàng)建內(nèi)存臨時(shí)表的條件，看來(lái)是配置的問(wèn)題。查看配置文件中有配置 big_tables。

查看官方文檔對(duì) big_tables 的說(shuō)明：

    如果設(shè)置為1，則所有臨時(shí)表都存儲(chǔ)在磁盤上而不是內(nèi)存中。 這有點(diǎn)慢，但是對(duì)于需要大型臨時(shí)表的 SELECT 操作，不會(huì)報(bào) The table tbl_name is full 的錯(cuò)誤。 新連接的默認(rèn)值為 0（使用內(nèi)存中的臨時(shí)表）。 通常，您不需要設(shè)置此變量，因?yàn)楦鶕?jù)需要將內(nèi)存表自動(dòng)轉(zhuǎn)換為基于磁盤的表。

修改該系統(tǒng)變量再測(cè)試一下，這次創(chuàng)建的臨時(shí)表就是內(nèi)存臨時(shí)表了。

mysql 中內(nèi)部臨時(shí)表（internal temporary table）機(jī)制

某些情況下，mysql 在執(zhí)行語(yǔ)句的時(shí)候會(huì)創(chuàng)建內(nèi)部臨時(shí)表。用戶不能直接控制何時(shí)發(fā)生。

服務(wù)器在以下情況會(huì)創(chuàng)建臨時(shí)表：

  ● 評(píng)估 UINON 語(yǔ)句；

  ● 評(píng)估一些視圖，例如使用 TEMPTABLE 算法，UNION 或聚合的視圖；

  ● 派生表的評(píng)估（FROM子句中的子查詢）；

  ● 為子查詢或半連接實(shí)現(xiàn)創(chuàng)建表；

  ● 評(píng)估包含 ORDER BY 子句和不同 GROUP BY 子句的語(yǔ)句，或 ORDER BY 或 GROUP BY 包含來(lái)自除加入隊(duì)列中第一個(gè)表之外的表的列的語(yǔ)句；

  ● DISTINCT與ORDER BY結(jié)合使用可能需要一個(gè)臨時(shí)表；

  ● 對(duì)于使用 SQL_SMALL_RESULT 修飾符的查詢，MySQL 使用內(nèi)存中的臨時(shí)表，除非查詢還包含需要磁盤存儲(chǔ)的元素；

  ● 評(píng)估多表 UPDATE 語(yǔ)句；

  ● 評(píng)估 GROUP_CONCAT() 或 COUNT(DISTINCT) 表達(dá)式。

想要知道一條語(yǔ)句有沒(méi)有使用臨時(shí)表，通過(guò) EXPLAIN 查看 Extra 列有沒(méi)有說(shuō)明 Using temporary。EXPLAIN 不一定會(huì)對(duì)臨時(shí)表或派生臨時(shí)表使用臨時(shí)表。

當(dāng)服務(wù)器創(chuàng)建一個(gè)內(nèi)部臨時(shí)表的時(shí)候（無(wú)論是內(nèi)存表或磁盤表），會(huì)增加 Created_tmp_tables 狀態(tài)變量的值。如果服務(wù)器創(chuàng)建一個(gè)磁盤臨時(shí)表（直接建立或者從內(nèi)存表轉(zhuǎn)換）會(huì)增加 Created_tmp_disk_tables 狀態(tài)變量的值。通過(guò) show global status like 'Create%'; 語(yǔ)句查看。

某些查詢條件會(huì)阻止使用內(nèi)存中的臨時(shí)表，在這種情況下，服務(wù)器會(huì)使用磁盤上的表：

  ● 表中存在BLOB或TEXT列；

  ● 如果使用 UNION 或 UNION ALL，則在 SELECT 列表中存在最大長(zhǎng)度大于512（二進(jìn)制字符串的字節(jié)，非二進(jìn)制字符串的字符）的任何字符串列

  ● SHOW COLUMNS 和 DESCRIBE 語(yǔ)句使用 BLOB 作為某些列的類型，因此用于結(jié)果的臨時(shí)表是一個(gè)磁盤上表。