共計(jì) 2919 個(gè)字符，預(yù)計(jì)需要花費(fèi) 8 分鐘才能閱讀完成。

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

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

MySQL 臨時(shí)表的作用

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

MySQL 臨時(shí)表類型

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

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

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

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

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

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

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

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

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

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

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

MySQL 臨時(shí)表注意事項(xiàng)

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

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

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

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

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

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

2）truncate undo
當(dāng) innodb_undo_log_truncate 觸發(fā)的時(shí)候，undo 表空間截?cái)嗖僮髟诜?wù)器日志目錄中創(chuàng)建一個(gè)臨時(shí)的 undo_space_number_trunc.log 文件，該日志目錄由 innodb_log_group_home_dir 定義。如果在 truncate 操作期間發(fā)生系統(tǒng)故障，臨時(shí)日志文件允許啟動(dòng)進(jìn)程識(shí)別被截?cái)嗟?undo 表空間，并繼續(xù)操作。

4.binlog 緩存相關(guān)
使用二進(jìn)制日志緩存并且值達(dá)到了 binlog_cache_size 設(shè)置的值，用臨時(shí)文件存儲(chǔ)來自事務(wù)的變化這樣的事務(wù)數(shù)量。可通過 Binlog_stmt_cache_disk_use 狀態(tài)變量中單獨(dú)跟蹤。

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