共計 2944 個字符，預(yù)計需要花費 8 分鐘才能閱讀完成。

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

mysql 臨時表的作用：1、用戶自己創(chuàng)建的臨時表用于保存臨時數(shù)據(jù)；2、當(dāng)用戶在執(zhí)行復(fù)雜 SQL 時，可以借助臨時表進行分組，排序，去重等操作，并且默認(rèn)情況下，當(dāng)你斷開與數(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)建的表）默認(rèn)的存儲引擎。

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：臨時表目錄，當(dāng)臨時表大小超過一定閾值，就會從內(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ù)制，當(dāng)前 slave 創(chuàng)建了多少臨時表，binlog_format 只能是 statement 和 mixed 下有效.
備注：stop slave 也沒有用，必須主庫手動刪除 或則 session 退出 才可以。
以下是從庫 binlog 記錄信息：

MySQL 臨時表注意事項

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

可以為臨時表空間設(shè)置一個最大值，比如 10G，如下：
innodb_temp_data_file_path = ibtmp1:128M:autoextend:max:10G
當(dāng)臨時表空間達(dá)到最大值 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ù)量，默認(rèn)設(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
當(dāng) 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)
使用二進制日志緩存并且值達(dá)到了 binlog_cache_size 設(shè)置的值，用臨時文件存儲來自事務(wù)的變化這樣的事務(wù)數(shù)量。可通過 Binlog_stmt_cache_disk_use 狀態(tài)變量中單獨跟蹤。

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

丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！