mysql臨時表的作用是什么

今天小編給大家分享一下mysql臨時表的作用是什么的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

mysql臨時表的作用：1、用戶自己創建的臨時表用于保存臨時數據；2、當用戶在執行復雜SQL時，可以借助臨時表進行分組，排序，去重等操作，并且默認情況下，當你斷開與數據庫的連接后，臨時表就會自動被銷毀。

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狀態變量中單獨跟蹤。

以上就是“mysql臨時表的作用是什么”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。