iT邦幫忙

4

MySQL 中的 temporary table (臨時表) 觀念

前言

有時候在寫 SQL 語法時為了方便,可能會去建立臨時表存一些資料,以利後面需要用到的時候可以拿來使用。也就是利用 CREATE TEMPORARY TABLE table_table ........ ,我們會稱它叫臨時表。
另外有時候在看一些優化效能的網站時,也會看到有些語法會去建立臨時表??
這兩種臨時表有什麼不一樣,在這裡做一個整理。
這裡主要介紹一些臨時表的概念,至於怎麼建立,相信這塊網路上已經非常多了,就不另外說明。

外部臨時表

首先臨時表之一的就是外部臨時表,也就是當我們使用 CREATE TEMPORARY TABLE table_table ........去建立的,此種表只能在當前的 session 使用,也就是說

  • 當 session 斷掉此臨時表就會自動 drop 掉,當然我們也可以自己 drop 這個臨時表
  • 其他 session 可以用相同名稱的臨時表

★ 外部臨時表存放位置

這裡只要先知道外部臨時表是放在 disk 上,後面會再探討。

內部臨時表

內部臨時表是由 MySQL 自己創建的,用來進行性能優化用的,對用戶來說是不可見的,只有透過 explain 才會看到,藉以了解是否有用到內部臨時表來協助完成某些事情。
有很多動作都會利用到內部臨時表來進行優化,例如

  • UNION
  • DISTINCT
  • view
  • derived tables
  • CTE
  • ORDER BY , GROUP BY

如果有用到內部臨時表,會在 explain 的 extra 欄位看到,如下圖
https://ithelp.ithome.com.tw/upload/images/20200220/20124671wh9Ym1Djvz.png

內部臨時表又分成兩種

  1. 放在 memory 中,HEAP 臨時表,不需 I/O 操作
  2. 放在 disk,當中間結果集較大的時候,例如超過參數 MAX_HEAP_TABLE_SIZE 設定的大小,就會放到 disk

★ 內部臨時表存放位置

如同上面提到的,有分 memory 和 disk

★ 內部臨時表相關參數

只要 MySQL Server 有建立內部臨時表(不管事建在 memory 還是 disk 上) Created_tmp_tables 這個 status variable 就會增加。
如果是建立到 disk 上,那麼 Created_tmp_disk_tables 這個參數就會增加。
藉由這兩個可以去觀察內部臨時表到底建立到 memory 還是 disk 上。

show status like '%Created_tmp_disk_tables%';
show status like '%Created_tmp_tables%';

關於臨時表空間 temporary tablespace

這裡之所以會特別提到 temporary tablespace 是因為前面有談到關於臨時表是建立在 disk 上還是 memory 上,對於資料庫來說, I/O 絕對是效能重要的瓶頸,所以我們有必要了解這一塊。
在 InnoDB 中 temporary tablespace 有兩種

  1. session temporary tablespaces
  2. global temporary tablespace.

這裡我們主要討論的是 session temporary tablespaces。

session temporary tablespaces

這裡主要存放了以下兩種
1.外部臨時表:使用者自己建的 create temporary table
2.內部臨時表: 由 optimizer 建立的,但是前提是這個內部臨時表是 on disk

這裡以 mysql8.0.16 為例,session temporary tablespaces 在 mysql 8.0.16 之後會在 #innodb_temp/ 這個目錄下。從下圖可以看到有 10 個檔案,這10個檔案組成一個 pool。
https://ithelp.ithome.com.tw/upload/images/20200224/20124671c4cCi8lV1e.png

  • 這個 pool 是在 server start 就建立的,也就是說 server 一開始就有這10個檔案,也就是 pool
  • 這個 pool 不會 shrinks,且有必要的會會自動 add
  • 這些 ibt 副檔名的就是 session tamporary tablespace
  • 每一次重啟 server ,這些 .ibt 檔案就會重建
  • 只要 session 結束,這些 session tamporary tables 就會 truncate 且還給 pool

小結

這篇文章大略介紹了內部外部臨時表的差異,主要有鑑於自己在查資料時,大多網站只有寫臨時表,但其實還是有所區分,所以在這裡幫助自己也希望幫助到大家有稍微了解到這兩者的差異。
此外跟此主題相關的還有

◎ Materialization: 

這是 MySQL 的特性,主要是跟優化 subquery, cte 相關,當然也跟內部臨時表密不可分,大家有興趣可以上網查詢一下,下次也會另外寫一篇介紹此特性。

資料庫知識相當廣泛,文中若有不正確的地方,也煩請各位大神不吝指教,謝謝


參考網站
CREATE TEMPORARY TABLE Statement
https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html
Internal Temporary Table Use in MySQL
https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
Temporary Tablespaces
https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html
Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization
https://dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html
Optimizing Subqueries with Materialization
https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html


1 則留言

0
ckp6250
iT邦新手 2 級 ‧ 2020-02-24 22:04:32

十分感恩,獲益良多。

很棒的分享.

Stock iT邦新手 5 級‧ 2020-02-25 12:47:28 檢舉

謝謝大大的賞識,日後會繼續分享資料庫中不同的議題!

我要留言

立即登入留言