mysql臨時表有什麼用

mysql臨時表的作用：1、使用者自己建立的臨時表用於保存臨時資料；2、當使用者在執行複雜SQL時，可以藉助臨時表進行分組，排序，去重等操作，並且預設情況下，當你斷開與資料庫的連線後，臨時表就會自動被銷毀。

本教學操作環境：Windows10系統、MySQL5.7版本、Dell G3電腦。

mysql臨時表有什麼用?

MySQL臨時表的作用

MySQL臨時表在許多場景中都會用到，例如使用者自己建立的臨時表用於保存臨時數據，以及MySQL內部在執行複雜SQL時，需要藉助臨時表進行分組、排序、去重等操作。以下將針對MySQL臨時表的一些概念、分類和常見問題進行整理。

MySQL暫存資料表類型

1.外部暫存資料表，透過create temporary table語法建立的暫存表，可以指定儲存引擎為memory ，innodb, myisam等等，這類表在會話結束後，會自動清理。如果臨時表與非臨時表同時存在，那麼非臨時表不可見。 show tables指令不顯示臨時表格資訊。
可透過information_schema.INNODB_TEMP_TABLE_INFO系統表可以查看外部臨時表的相關信息，這部分使用的還是比較少。

2.內部臨時表，通常在執行複雜SQL，例如group by, order by, distinct, union等，執行計劃中如果包含Using temporary，還有undo回滾的時候，但空間不足的時候，MySQL內部會使用自動產生的臨時表，以輔助完成工作。

MySQL臨時表相關參數

1.max_heap_table_size：使用者建立的記憶體表的最大值，也用於和tmp_table_size一起，限制內部臨時表在記憶體中的大小。
2.tmp_table_size：內部臨時表在記憶體中的最大值，與max_heap_table_size參數共同決定，取二者的最小值。如果臨時表超過該值，就會從記憶體轉移到磁碟上。
3.innodb_tmpdir：online ALTER TABLE operations that rebuild the table max_tmp_tables

4.default_tmp_storage_engine：外部臨時表（create temporary table創建的表）預設的儲存引擎。

5.innodb_temp_data_file_path：innodb引擎下temp檔屬性。建議限制innodb_temp_data_file_path = ibtmp1:1G:autoextend:max:30G

6.Internal_tmp_disk_storage_engine：磁碟上的內部暫存表儲存引擎，可選值為myisam或innodb。使用innodb表在某些場景下，例如臨時表列太多，或者行大小超過限制，可能會出現“ Row size too large or Too many columns”的錯誤，這時應該將臨時表的innodb引擎改回myisam 。 tmpdir：臨時表目錄，當臨時表大小超過一定閾值，就會從記憶體轉移到磁碟上，
7.tmpdir變數表示磁碟上臨時表所在的目錄。

MySQL暫存資料表相關狀態變數

1.Created_tmp_disk_tables：執行SQL語句時，MySQL在磁碟上建立的內部暫存表數量，如果這個值很大，可能原因是分配給臨時表的最大記憶體值較小，或是SQL中有大量排序、分組、去重等操作，SQL需要最佳化。

2.Created_tmp_files：建立的臨時表數量

3.Created_tmp_tables：執行SQL語句時，MySQL建立的內部臨時表數量。

4.Slave_open_temp_tables statement 或則 mix模式下才會看到有使用。
slave_open_temp_tables 的值顯示，透過複製，目前slave建立了多少臨時表，binlog_format只能是statement 和mixed 下有效.
備註：stop slave 也沒有用，必須主庫手動刪除或則session退出才可以。
以下是從庫binlog記錄資訊：

MySQL臨時表注意事項

1.MySQL臨時表可能導致磁碟可用空間減少：
在MySQL5.7版本之前，臨時表的儲存引擎預設為myisam，myisam臨時表在SQL執行結束後，會自動刪除臨時表。然而從5.7版本開始，臨時表的預設儲存引擎變為innodb，雖然在性能上有了一定的提升，但是由於innodb引擎的臨時表共用表空間ibtmp1，導致在高並發下，多個session同時創建臨時表時，該表空間會變得非常大，且無法動態縮小，除非重啟MySQL，否則無法釋放。

可以為暫存表空間設定一個最大值，例如10G，如下：
innodb_temp_data_file_path = ibtmp1:128M:autoextend:max:10G
當暫存表空間達到最大值10G時，SQL執行將會報錯，影響應用程式的正常執行。
對於臨時表空間過大的問題，通常也有一些其他方法解決，例如：
將臨時表的儲存引擎設定為myisam，雖然可能有一些效能問題，但不會導致磁碟空間問題。

2.SQL語句：
（1）加上適當的索引
（2）在where條件中過濾更多的資料
（3）重寫SQL，最佳化執行計劃
（4）如果不得不使用臨時表，那麼一定要減少並發。建議使用SSD硬碟。

3.undo相關
1）使用innodb_rollback_segments配置選項定義回溯segment的數量，預設為128，也是最大值。一個回滾segment總是被指派給系統表空間，32個回滾segment預留給暫存表空間(ibtmp1)。因此，要指派回滾段來撤銷表空間，將innodb_rollback_segments設定為大於33的值。配置單獨的undo表空間時，system表空間中的回滾段將呈現為非活動狀態。

就是說超過128回滾segement的時候，就需要臨時表出來救急。

tablespace -> segment -> extent(64个page，1M) -> page（16kb）

2）truncate undo
當innodb_undo_log_truncate觸發的時候，undo表空間截斷操作在伺服器日誌目錄中建立一個臨時的undo_space_number_trunc.log文件，該日誌目錄由innodb_log_group_home_dir定義。如果在truncate操作期間發生系統故障，則臨時日誌檔案允許啟動程序識別被截斷的undo表空間，並繼續操作。

4.binlog 快取相關
使用二進位日誌快取並且值達到了binlog_cache_size設定的值，用臨時檔案儲存來自交易的變化這樣的交易數量。可透過Binlog_stmt_cache_disk_use狀態變數中單獨追蹤。

總結

1.從上述了解來看MySQL臨時表，平時的監控和最佳化是避免不了的。
2.除此之外，在業務實現中也可以適當的使用，如作為中間表臨時保存少量資訊 等。
3.在複製環節中binlog_format等於ROW模式時，臨時表相關是不記錄binlog日誌的（除了drop指令），這部分需要注意。
【相關推薦：mysql影片教學】

#

以上是mysql臨時表有什麼用的詳細內容。更多資訊請關注PHP中文網其他相關文章！