MySQL 需要創建隱式臨時表來解決某些類型的查詢。往往查詢的排序階段需要依賴臨時表。例如,當您使用 GROUP BY,ORDER BY 或DISTINCT 時。這樣的查詢分兩個階段執行:首先是收集數據并將它們放入臨時表中,然后是在臨時表上執行排序。
創新互聯專注于昂昂溪網站建設服務及定制,我們擁有豐富的企業做網站經驗。 熱誠為您提供昂昂溪營銷型網站建設,昂昂溪網站制作、昂昂溪網頁設計、昂昂溪網站官網定制、小程序開發服務,打造昂昂溪網絡公司原創品牌,更為您提供昂昂溪網站排名全網營銷落地服務。
對于某些 UNION 語句,不能合并的 VIEW,子查詢時用到派生表,多表 UPDATE 以及其他一些情況,還需要使用臨時表。如果臨時表很小,可以到內存中創建,否則它將在磁盤上創建。MySQL 在內存中創建了一個表,如果它變得太大,就會被轉換為磁盤上存儲。內存臨時表的最大值由 tmp_table_size 或 max_heap_table_size 值定義,以較小者為準。MySQL 5.7 中的默認大小為 16MB。如果運行查詢的數據量較大,或者尚未查詢優化,則可以增加該值。設置閾值時,請考慮可用的 RAM 大小以及峰值期間的并發連接數。你無法無限期地增加變量,因為在某些時候你需要讓 MySQL 使用磁盤上的臨時表。
注意:如果涉及的表具有 TEXT 或 BLOB 列,則即使大小小于配置的閾值,也會在磁盤上創建臨時表。
本文內容來源于對客戶的三個問題的思考:
以下測試都是在 MySQL 8.0.21 版本中完成,不同版本可能存在差異,可自行測試;
臨時表和臨時文件都是用于臨時存放數據集的地方;
一般情況下,需要臨時存放在臨時表或臨時文件中的數據集應該符合以下特點:
從臨時表|臨時文件產生的主觀性來看,分為2類:
用戶創建臨時表:
??-- 用戶創建臨時表(只有創建臨時表的會話才能查看其創建的臨時表的內容)
??注意:
??可以創建和普通表同名臨時表,其他會話可以看到普通表(因為看不到其他會話創建的臨時表);
??創建臨時表的會話會優先看到臨時表;
??-- 同名表的創建的語句如下
??當存在同名的臨時表時,會話都是優先處理臨時表(而不是普通表),包括:select、update、delete、drop、alter 等操作;
查看用戶創建的臨時表:
??任何 session 都可以執行下面的語句;
??查看用戶創建的當前 active 的臨時表(不提供 optimizer 使用的內部 InnoDB 臨時表信息)
??注意
??用戶創建的臨時表,表名為t1,
??但是通過 INNODB_TEMP_TABLE_INFO 查看到的臨時表的 NAME 是#sql開頭的名字,例如:#sql45aa_7c69_2 ;
??另外 information_schema.tables 表中是不會記錄臨時表的信息的。
用戶創建的臨時表的回收:
用戶創建的臨時表的其他信息參數:
??會話臨時表空間存儲 用戶創建的臨時表和優化器 optimizer 創建的內部臨時表(當磁盤內部臨時表的存儲引擎為 InnoDB 時);
??innodb_temp_tablespaces_dir 變量定義了創建 會話臨時表空間的位置,默認是數據目錄下的#innodb_temp 目錄;
??文件類似temp_[1-20].ibt ;
??查看會話臨時表空間的元數據:
??用戶創建的臨時表刪除后,其占用的空間會被釋放(temp_[1-20].ibt文件會變小)。
??在 MySQL 8.0.16 之前,internal_tmp_disk_storage_engine 變量定義了用戶創建的臨時表和 optimizer 創建的內部臨時表的引擎,可選 INNODB 和 MYISAM ;
??從 MySQL 8.0.16 開始,internal_tmp_disk_storage_engine參數被移除,默認使用InnoDB存儲引擎;
??innodb_temp_data_file_path 定義了用戶創建的臨時表使用的回滾段的存儲文件的相對路徑、名字、大小和屬性,該文件是全局臨時表空間(ibtmp1);
??可以使用語句查詢全局臨時表空間的數據文件大小:
SQL 什么時候產生臨時表|臨時文件呢?
??需要用到臨時表或臨時文件的時候,optimizer 自然會創建使用(感覺是廢話,但是又覺得有道理=.=!);
??(想象能力強的,可以牢記上面這句話;想象能力弱的,只能死記下面的 SQL 了。我也弱,此處有個疲憊的微笑????)
下面列舉一些 server 在處理 SQL 時,可能會創建內部臨時表的 SQL :
??SQL 包含 union | union distinct 關鍵字
??SQL 中存在派生表
??SQL 中包含 with 關鍵字
??SQL 中的order by 和 group by 的字段不同
??SQL 為多表 update
??SQL 中包含 distinct 和 order by 兩個關鍵字
我們可以通過下面兩種方式判斷 SQL 語句是否使用了臨時表空間:
??# 如果 explain 的 Extra 列包含 Using temporary ,那么說明會使用臨時空間,如果包含 Using filesort ,那么說明會使用文件排序(臨時文件);
??如果執行 SQL 后,表的 ID 列變為了show processlist 中的 id 列的值,那么說明 SQL 語句使用了臨時表空間
SQL創建的內部臨時表的存儲信息:
??SQL 創建內部臨時表時,優先選擇在內存中,默認使用 TempTable 存儲引擎(由參數 internal_tmp_mem_storage_engine 確定),
??當 temptable 使用的內存量超過 temptable_max_ram 定義的大小時,
??由 temptable_use_mmap 確定是使用內存映射文件的方式還是 InnoDB 磁盤內部臨時表的方式存儲數據
??(temptable_use_mmap 參數在 MySQL 8.0.16 引入,MySQL 8.0.26 版本不推薦,后續應該會移除);
??temptable_use_mmap 的功能將由MySQL 8.0.23 版本引入的 temptable_max_mmap 代替,
??當 temptable_max_mmap=0 時,說明不使用內存映射文件,等價于 temptable_use_mmap=OFF ;
??當 temptable_max_mmap=N 時,N為正整數,包含了 temptable_use_mmap=ON 以及聲明了允許為內存映射文件分配的最大內存量。
??該參數的定義解決了這些文件使用過多空間的風險。
??內存映射文件產生的臨時文件會存放于 tmpdir 定義的目錄中,在 TempTable 存儲引擎關閉或 mysqld 進程關閉時,回收空間;
??當 SQL 創建的內部臨時表,選擇 MEMORY 存儲引擎時,如果內存中的臨時表變的太大,MySQL 將自動將其轉為磁盤臨時表;
??其能使用的內存上限為 min(tmp_table_size,max_heap_table_size);
監控 TempTable 從內存和磁盤上分配的空間:
??具體的字段含義見:Section 27.12.20.10, “Memory Summary Tables”.
監控內部臨時表的創建:
??當在內存或磁盤上創建內部臨時表,服務器會增加 Created_tmp_tables 的值;
??當在磁盤上創建內部臨時表時,服務器會增加 Created_tmp_disk_tables 的值,
??如果在磁盤上創建了太多的內部臨時表,請考慮增加 tmp_table_size 和 max_heap_table_size 的值;
??created_tmp_disk_tables 不計算在內存映射文件中創建的磁盤臨時表;
例外項:
??臨時表/臨時文件一般較小,但是也存在需要大量空間的臨時表/臨時文件的需求:
因為這些例外項一般需要較大的空間,所以需要考慮是否要將其存放在獨立的掛載點上。
其他:
??列出由失敗的 alter table 創建的隱藏臨時表,這些臨時表以#sql開頭,可以使用 drop table 刪除;
??通過 lsof +L1 可以查看標識為 delete ,但還未釋放空間的文件。
???如果想釋放這些 delete 狀態的文件,可以嘗試下面的方法(不推薦,后果自負):
普通的磁盤臨時表|臨時文件(一般需要較小的空間):
??臨時表|臨時文件的一般所需的空間較小,會優先存放于內存中,若超過一定的大小,則會轉換為磁盤臨時表|臨時文件;
??磁盤臨時表默認為 InnoDB 引擎,其存放在臨時表空間中,由 innodb_temp_tablespaces_dir 定義表空間的存放目錄,表空間文件類似:temp_[1-20].ibt ;MySQL 未定義 InnoDB 臨時表空間的最大使用上限;
??當臨時表|臨時文件使用完畢后,會自動回收臨時表空間文件的大??;
??innodb_temp_data_file_path 定義了用戶創建的臨時表使用的回滾段的存儲文件的相對路徑、名字、大小和屬性,該文件是全局臨時表空間(ibtmp1),該文件可以設置文件最大使用大??;
例外項(一般需要較大的空間):
??load data local 語句,客戶端讀取文件并將其內容發送到服務器,服務器將其存儲在 tmpdir 參數指定的路徑中;
??在 replica 中,回放 load data 語句時,需要將從 relay log 中解析出來的數據存儲在 slave_load_tmpdir(replica_load_tmpdir) 指定的目錄中,該參數默認和 tmpdir 參數指定的路徑相同;
??需要 rebuild table 的在線 alter table 需要使用 innodb_tmpdir 存放排序磁盤排序文件,如果 innodb_tmpdir 未指定,則使用 tmpdir 的值;
若用戶判斷產生的臨時表|臨時文件一定會轉換為磁盤臨時表|臨時文件,那么可以設置 set session big_tables=1;讓產生的臨時表|臨時文件直接存放在磁盤上;
??對于需要較小空間的臨時表|臨時文件,MySQL 要么將其存儲于內存,要么放在統一的磁盤臨時表空間中,用完即釋放;
??對于需要較大空間的臨時表|臨時文件,可以通過設置參數,將其存儲于單獨的目錄|掛載點;例如:load local data 語句或需要重建表的在線 alter table 語句,都有對應的參數設置其存放臨時表|臨時文件的路徑;
??當前只有 innodb_temp_data_file_path 參數可以限制 用戶創建的臨時表使用的回滾段的存儲文件的大小,無其他參數可以限制臨時表|臨時文件可使用的磁盤空間;
MySql判斷臨時表是否存在,不存在就創建之實例:
CREATE TEMPORARY TABLE IF NOT EXISTS myTempTbl1
(id int,item varchar(10));
附Mysql 創建表語法如下供參考:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
當前文章:mysql臨時表怎么判斷,mysql 如何查看臨時表
分享地址:http://vcdvsql.cn/article26/dsdjsjg.html
成都網站建設公司_創新互聯,為您提供電子商務、Google、App設計、營銷型網站建設、做網站、動態網站
聲明:本網站發布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網站立場,如需處理請聯系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源: 創新互聯