mysql臨時表的作用是什么

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

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

MySQL臨時表的作用

MySQL臨時表在很多場景中都會用到，比如用戶自己創(chuàng)建的臨時表用于保存臨時數(shù)據(jù)，以及MySQL內(nèi)部在執(zhí)行復(fù)雜SQL時，需要借助臨時表進行分組、排序、去重等操作。下面將會對MySQL臨時表的一些概念、分類和常見問題進行整理。

MySQL臨時表類型

1.外部臨時表，通過create temporary table語法創(chuàng)建的臨時表，可以指定存儲引擎為memory，innodb, myisam等等，這類表在會話結(jié)束后，會被自動清理。如果臨時表與非臨時表同時存在，那么非臨時表不可見。show tables命令不顯示臨時表信息。
可通過information_schema.INNODB_TEMP_TABLE_INFO系統(tǒng)表可以查看外部臨時表的相關(guān)信息，這部分使用的還是比較少。

2.內(nèi)部臨時表，通常在執(zhí)行復(fù)雜SQL，比如group by, order by, distinct, union等，執(zhí)行計劃中如果包含Using temporary，還有undo回滾的時候，但空間不足的時候，MySQL內(nèi)部將使用自動生成的臨時表，以輔助完成工作。

MySQL臨時表相關(guān)參數(shù)

1.max_heap_table_size：用戶創(chuàng)建的內(nèi)存表的最大值，也用于和tmp_table_size一起，限制內(nèi)部臨時表在內(nèi)存中的大小。
2.tmp_table_size：內(nèi)部臨時表在內(nèi)存中的的最大值，與max_heap_table_size參數(shù)共同決定，取二者的最小值。如果臨時表超過該值，就會從內(nèi)存轉(zhuǎn)移到磁盤上。
3.innodb_tmpdir：online ALTER TABLE operations that rebuild the table max_tmp_tables

4.default_tmp_storage_engine：外部臨時表（create temporary table創(chuàng)建的表）默認的存儲引擎。

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

6.Internal_tmp_disk_storage_engine：磁盤上的內(nèi)部臨時表存儲引擎，可選值為myisam或者innodb。使用innodb表在某些場景下，比如臨時表列太多，或者行大小超過限制，可能會出現(xiàn)“ Row size too large or Too many columns”的錯誤，這時應(yīng)該將臨時表的innodb引擎改回myisam。tmpdir：臨時表目錄，當臨時表大小超過一定閾值，就會從內(nèi)存轉(zhuǎn)移到磁盤上，
7.tmpdir變量表示磁盤上臨時表所在的目錄。

MySQL臨時表相關(guān)狀態(tài)變量

1.Created_tmp_disk_tables：執(zhí)行SQL語句時，MySQL在磁盤上創(chuàng)建的內(nèi)部臨時表數(shù)量，如果這個值很大，可能原因是分配給臨時表的最大內(nèi)存值較小，或者SQL中有大量排序、分組、去重等操作，SQL需要優(yōu)化。

2.Created_tmp_files：創(chuàng)建的臨時表數(shù)量

3.Created_tmp_tables：執(zhí)行SQL語句時，MySQL創(chuàng)建的內(nèi)部臨時表數(shù)量。

4.Slave_open_temp_tables statement 或則 mix模式下才會看到有使用。
slave_open_temp_tables 的值顯示，通過復(fù)制，當前slave創(chuàng)建了多少臨時表，binlog_format只能是statement 和 mixed 下有效.
備注：stop slave 也沒有用，必須主庫手動刪除 或則 session退出 才可以。
以下是從庫binlog記錄信息：

MySQL臨時表注意事項

1.MySQL臨時表可能導(dǎo)致磁盤可用空間減少：
在MySQL5.7版本之前，臨時表的存儲引擎默認為myisam，myisam臨時表在SQL執(zhí)行結(jié)束后，會自動刪除臨時表。然而從5.7版本開始，臨時表的默認存儲引擎變?yōu)閕nnodb，雖然在性能上有了一定的提升，但是由于innodb引擎的臨時表共用表空間ibtmp1，導(dǎo)致在高并發(fā)下，多個session同時創(chuàng)建臨時表時，該表空間會變得非常大，并且不能動態(tài)縮小，除非重啟MySQL，否則無法釋放。

可以為臨時表空間設(shè)置一個最大值，比如10G，如下：
innodb_temp_data_file_path = ibtmp1:128M:autoextend:max:10G
當臨時表空間達到最大值10G時，SQL執(zhí)行將會報錯，影響應(yīng)用的正常執(zhí)行。
對于臨時表空間過大的問題，通常也有一些其他方法解決，比如：
將臨時表的存儲引擎設(shè)置為myisam，雖然可能有一些性能問題，但不會導(dǎo)致磁盤空間問題。

2.SQL語句：
（1）加上合適的索引
（2）在where條件中過濾更多的數(shù)據(jù)
（3）重寫SQL，優(yōu)化執(zhí)行計劃
（4）如果不得不使用臨時表，那么一定要減少并發(fā)。建議使用SSD硬盤。

3.undo相關(guān)
1）使用innodb_rollback_segments配置選項定義回滾segment的數(shù)量，默認設(shè)置是128，也是最大值。一個回滾segment總是分配給系統(tǒng)表空間，32個回滾segment預(yù)留給臨時表空間(ibtmp1)。因此，要分配回滾段來撤消表空間，將innodb_rollback_segments設(shè)置為大于33的值。配置單獨的undo表空間時，system表空間中的回滾段將呈現(xiàn)為非活動狀態(tài)。

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

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

2）truncate undo
當innodb_undo_log_truncate觸發(fā)的時候，undo表空間截斷操作在服務(wù)器日志目錄中創(chuàng)建一個臨時的undo_space_number_trunc.log文件，該日志目錄由innodb_log_group_home_dir定義。如果在truncate操作期間發(fā)生系統(tǒng)故障，臨時日志文件允許啟動進程識別被截斷的undo表空間，并繼續(xù)操作。

4.binlog 緩存相關(guān)
使用二進制日志緩存并且值達到了binlog_cache_size設(shè)置的值，用臨時文件存儲來自事務(wù)的變化這樣的事務(wù)數(shù)量?？赏ㄟ^Binlog_stmt_cache_disk_use狀態(tài)變量中單獨跟蹤。

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