MySQL如何創(chuàng)建臨時(shí)表 內(nèi)存臨時(shí)表與磁盤臨時(shí)表區(qū)別

創(chuàng)建mysql臨時(shí)表的方法是使用create temporary table語句，1.語法與普通表相同但需添加temporary關(guān)鍵字；2.臨時(shí)表在會(huì)話結(jié)束后自動(dòng)刪除；3.不同會(huì)話可創(chuàng)建同名表互不影響；4.用戶需擁有create temporary tables權(quán)限；5.磁盤空間不足或tmpdir配置問題會(huì)導(dǎo)致創(chuàng)建失敗；6.內(nèi)存臨時(shí)表適用于小數(shù)據(jù)量、速度快，磁盤臨時(shí)表適合大數(shù)據(jù)量或含blob/text字段；7.可通過explain命令查看是否使用臨時(shí)表及類型；8.優(yōu)化技巧包括避免復(fù)雜where條件、合理設(shè)置tmp_table_size和max_heap_table_size、使用索引、避免blob/text字段；9.監(jiān)控可使用show global status、performance_schema或第三方工具；10.存儲(chǔ)過程中可創(chuàng)建臨時(shí)表處理中間結(jié)果，函數(shù)中受限；11.相比子查詢，臨時(shí)表更適合多次使用或大數(shù)據(jù)量場(chǎng)景；12.mysql 8.0支持with子句、create or replace語法并優(yōu)化存儲(chǔ)引擎提升性能。

創(chuàng)建MySQL臨時(shí)表，本質(zhì)上就是創(chuàng)建一個(gè)只在當(dāng)前會(huì)話存在的表。會(huì)話斷開，表自動(dòng)消失，數(shù)據(jù)也隨之不見。它像一個(gè)草稿本，用完就扔，非常適合處理一些中間結(jié)果集，避免污染正式的數(shù)據(jù)庫表。

創(chuàng)建臨時(shí)表使用 CREATE TEMPORARY TABLE 語句。語法和創(chuàng)建普通表幾乎一樣，只是多了 TEMPORARY 關(guān)鍵字。

CREATE TEMPORARY TABLE temp_orders (     order_id INT PRIMARY KEY,     customer_id INT,     order_date DATE,     total_amount DECIMAL(10, 2) );  -- 插入數(shù)據(jù) INSERT INTO temp_orders (order_id, customer_id, order_date, total_amount) SELECT order_id, customer_id, order_date, total_amount FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';  -- 使用臨時(shí)表進(jìn)行查詢 SELECT customer_id, SUM(total_amount) AS total_spent FROM temp_orders GROUP BY customer_id ORDER BY total_spent DESC;  -- 會(huì)話結(jié)束，臨時(shí)表自動(dòng)刪除

臨時(shí)表最大的好處是隔離性。不同會(huì)話可以創(chuàng)建同名的臨時(shí)表，互不影響。這在并發(fā)環(huán)境下非常有用，避免了數(shù)據(jù)沖突。

MySQL臨時(shí)表創(chuàng)建失敗的常見原因及解決方法

臨時(shí)表創(chuàng)建失敗，除了語法錯(cuò)誤，更常見的原因是權(quán)限問題。用戶需要擁有 CREATE TEMPORARY TABLES 權(quán)限。另外，如果磁盤空間不足，也可能導(dǎo)致創(chuàng)建失敗。

解決方法也很簡(jiǎn)單：

1. 檢查語法：仔細(xì)核對(duì) SQL 語句，特別是表名、字段類型等。
2. 檢查權(quán)限：使用 SHOW GRANTS FOR ‘your_user’@’your_host’; 查看用戶權(quán)限，如果沒有 CREATE TEMPORARY TABLES 權(quán)限，需要管理員授權(quán)。
3. 檢查磁盤空間：使用 df -h 命令查看磁盤空間，如果空間不足，需要清理磁盤或增加磁盤空間。
4. 檢查 tmpdir 配置：臨時(shí)表默認(rèn)存儲(chǔ)在 tmpdir 指定的目錄下。如果該目錄不存在或權(quán)限不足，也會(huì)導(dǎo)致創(chuàng)建失敗。可以在 MySQL 配置文件（my.cnf 或 my.ini）中修改 tmpdir 的值。
5. 并發(fā)沖突：在高并發(fā)環(huán)境下，如果多個(gè)會(huì)話同時(shí)創(chuàng)建同名的臨時(shí)表，可能會(huì)出現(xiàn)沖突。可以嘗試使用更復(fù)雜的表名，或者使用鎖機(jī)制避免沖突。

一個(gè)容易被忽略的點(diǎn)是，如果臨時(shí)表依賴于其他表，而你沒有這些表的訪問權(quán)限，創(chuàng)建過程也會(huì)失敗。

內(nèi)存臨時(shí)表與磁盤臨時(shí)表：性能差異與選擇策略

內(nèi)存臨時(shí)表和磁盤臨時(shí)表，顧名思義，一個(gè)存儲(chǔ)在內(nèi)存中，一個(gè)存儲(chǔ)在磁盤上。 性能差異巨大。內(nèi)存臨時(shí)表速度飛快，但受限于內(nèi)存大小；磁盤臨時(shí)表速度較慢，但可以存儲(chǔ)更多數(shù)據(jù)。

MySQL 決定使用哪種類型的臨時(shí)表，主要取決于幾個(gè)因素：

• 臨時(shí)表的大小：如果臨時(shí)表的大小超過 tmp_table_size 和 max_heap_table_size 這兩個(gè)參數(shù)的最小值，MySQL 會(huì)自動(dòng)將內(nèi)存臨時(shí)表轉(zhuǎn)換為磁盤臨時(shí)表。
• 字段類型：如果臨時(shí)表包含 BLOB 或 TEXT 類型的字段，MySQL 也會(huì)直接使用磁盤臨時(shí)表。
• 索引：內(nèi)存臨時(shí)表不支持 BLOB 和 TEXT 類型的字段建立索引，如果需要對(duì)這些字段進(jìn)行索引，只能使用磁盤臨時(shí)表。

選擇策略也很簡(jiǎn)單：

• 小數(shù)據(jù)量：優(yōu)先使用內(nèi)存臨時(shí)表，速度更快。
• 大數(shù)據(jù)量：只能使用磁盤臨時(shí)表。
• 需要對(duì) BLOB 或 TEXT 類型的字段進(jìn)行索引：只能使用磁盤臨時(shí)表。

可以通過 EXPLaiN 命令查看 MySQL 是否使用了臨時(shí)表，以及使用了哪種類型的臨時(shí)表。

EXPLAIN SELECT customer_id, SUM(total_amount) AS total_spent FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' GROUP BY customer_id ORDER BY total_spent DESC;

如果 Extra 列包含 using temporary，則表示使用了臨時(shí)表。如果還包含 Using filesort，則表示使用了磁盤臨時(shí)表。

優(yōu)化臨時(shí)表性能：避免磁盤臨時(shí)表的出現(xiàn)

盡可能避免使用磁盤臨時(shí)表，是優(yōu)化 SQL 性能的關(guān)鍵。以下是一些常用的優(yōu)化技巧：

1. 優(yōu)化 SQL 語句：避免在 WHERE 子句中使用復(fù)雜的表達(dá)式或函數(shù)，盡量使用索引。
2. 增加 tmp_table_size 和 max_heap_table_size 的值：增加內(nèi)存臨時(shí)表的最大大小，減少轉(zhuǎn)換為磁盤臨時(shí)表的可能性。但要注意，這兩個(gè)參數(shù)的值不能設(shè)置過大，否則會(huì)占用過多的內(nèi)存。
3. 使用索引：在經(jīng)常用于 GROUP BY 和 ORDER BY 的字段上建立索引，可以避免使用臨時(shí)表。
4. 避免使用 BLOB 和 TEXT 類型的字段：盡量使用 VARCHAR 或 TEXT 類型的字段代替 BLOB 和 TEXT 類型的字段。
5. 使用 SQL_BIG_RESULT 或 SQL_SMALL_RESULT 提示：SQL_BIG_RESULT 告訴 MySQL 結(jié)果集可能會(huì)很大，應(yīng)該使用磁盤臨時(shí)表；SQL_SMALL_RESULT 告訴 MySQL 結(jié)果集可能會(huì)很小，應(yīng)該使用內(nèi)存臨時(shí)表。

需要注意的是，過度優(yōu)化也可能導(dǎo)致性能下降。例如，過度增加 tmp_table_size 和 max_heap_table_size 的值，可能會(huì)導(dǎo)致內(nèi)存不足，反而降低性能。因此，需要根據(jù)實(shí)際情況進(jìn)行調(diào)整。

如何監(jiān)控MySQL臨時(shí)表的使用情況？

監(jiān)控 MySQL 臨時(shí)表的使用情況，可以幫助我們發(fā)現(xiàn)潛在的性能問題。

1. 使用 SHOW GLOBAL STATUS LIKE ‘Created_tmp%’; 命令查看全局臨時(shí)表的統(tǒng)計(jì)信息。

   SHOW GLOBAL STATUS LIKE 'Created_tmp%';
   • Created_tmp_disk_tables：表示創(chuàng)建的磁盤臨時(shí)表的數(shù)量。
   • Created_tmp_tables：表示創(chuàng)建的內(nèi)存臨時(shí)表的數(shù)量。

   如果 Created_tmp_disk_tables 的值很高，則表示存在大量的磁盤臨時(shí)表，需要進(jìn)行優(yōu)化。

2. 使用 PERFORMANCE_SCHEMA 數(shù)據(jù)庫：PERFORMANCE_SCHEMA 數(shù)據(jù)庫提供了更詳細(xì)的臨時(shí)表信息。

   SELECT     OBJECT_NAME,     SUM(IF(OBJECT_TYPE='TABLE',1,0)) AS TABLES,     SUM(IF(OBJECT_TYPE='TABLE',COUNT,0)) AS TABLE_COUNT,     SUM(IF(OBJECT_TYPE='TABLE',SUM_TIMER_WAIT,0)) AS TABLE_SUM_TIMER_WAIT FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NULL AND OBJECT_SCHEMA = 'your_database' GROUP BY OBJECT_NAME ORDER BY TABLE_SUM_TIMER_WAIT DESC;

   這個(gè)查詢可以顯示每個(gè)表的 I/O 等待時(shí)間，可以幫助我們找到哪些表使用了大量的磁盤 I/O。

3. 使用第三方監(jiān)控工具：例如 prometheus、grafana 等。這些工具可以提供更全面的監(jiān)控指標(biāo)，例如 CPU 使用率、內(nèi)存使用率、磁盤 I/O 等。

監(jiān)控臨時(shí)表的使用情況，是一個(gè)持續(xù)的過程。需要定期檢查，并根據(jù)實(shí)際情況進(jìn)行調(diào)整。

臨時(shí)表在存儲(chǔ)過程和函數(shù)中的應(yīng)用場(chǎng)景

存儲(chǔ)過程和函數(shù)中，臨時(shí)表可以發(fā)揮很大的作用。比如，在復(fù)雜的報(bào)表統(tǒng)計(jì)中，可以將中間結(jié)果存儲(chǔ)在臨時(shí)表中，然后進(jìn)行進(jìn)一步的計(jì)算。

DELIMITER // CREATE PROCEDURE CalculateMonthlySales(IN year INT, IN month INT) BEGIN     CREATE TEMPORARY TABLE IF NOT EXISTS MonthlyOrders (         order_id INT PRIMARY KEY,         customer_id INT,         total_amount DECIMAL(10, 2)     );      -- 清空臨時(shí)表     TRUNCATE TABLE MonthlyOrders;      -- 插入當(dāng)月訂單數(shù)據(jù)     INSERT INTO MonthlyOrders (order_id, customer_id, total_amount)     SELECT order_id, customer_id, total_amount     FROM orders     WHERE YEAR(order_date) = year AND MONTH(order_date) = month;      -- 計(jì)算總銷售額     SELECT SUM(total_amount) AS total_sales     FROM MonthlyOrders;      -- 刪除臨時(shí)表（可選，存儲(chǔ)過程結(jié)束會(huì)自動(dòng)刪除）     -- DROP TEMPORARY TABLE MonthlyOrders; END // DELIMITER ;  -- 調(diào)用存儲(chǔ)過程 CALL CalculateMonthlySales(2023, 01);

在這個(gè)例子中，MonthlyOrders 臨時(shí)表用于存儲(chǔ)當(dāng)月的訂單數(shù)據(jù)。存儲(chǔ)過程首先清空臨時(shí)表，然后插入當(dāng)月訂單數(shù)據(jù)，最后計(jì)算總銷售額。

需要注意的是，在存儲(chǔ)過程中創(chuàng)建的臨時(shí)表，在存儲(chǔ)過程執(zhí)行完畢后會(huì)自動(dòng)刪除。因此，如果需要在存儲(chǔ)過程外部訪問臨時(shí)表，需要手動(dòng)刪除臨時(shí)表。

另外，在函數(shù)中使用臨時(shí)表需要注意一些限制。例如，函數(shù)不能修改全局變量，因此不能在函數(shù)中創(chuàng)建或刪除臨時(shí)表。

臨時(shí)表與子查詢：性能對(duì)比與最佳實(shí)踐

臨時(shí)表和子查詢都可以用于處理中間結(jié)果集，但它們的性能差異很大。

• 臨時(shí)表：將中間結(jié)果存儲(chǔ)在臨時(shí)表中，可以避免重復(fù)計(jì)算。臨時(shí)表可以被多個(gè)查詢使用，因此可以提高性能。
• 子查詢：每次執(zhí)行查詢時(shí)，都需要重新計(jì)算子查詢的結(jié)果。如果子查詢的結(jié)果集很大，或者子查詢的執(zhí)行時(shí)間很長，則會(huì)導(dǎo)致性能下降。

一般來說，如果子查詢只被使用一次，且結(jié)果集很小，則可以使用子查詢。如果子查詢被多次使用，或者結(jié)果集很大，則應(yīng)該使用臨時(shí)表。

以下是一個(gè)使用臨時(shí)表代替子查詢的例子：

-- 使用子查詢 SELECT customer_id, (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) AS order_count FROM customers c;  -- 使用臨時(shí)表 CREATE TEMPORARY TABLE IF NOT EXISTS CustomerOrderCount (     customer_id INT PRIMARY KEY,     order_count INT );  INSERT INTO CustomerOrderCount (customer_id, order_count) SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;  SELECT c.customer_id, coc.order_count FROM customers c LEFT JOIN CustomerOrderCount coc ON c.customer_id = coc.customer_id;  DROP TEMPORARY TABLE CustomerOrderCount;

在這個(gè)例子中，使用臨時(shí)表 CustomerOrderCount 存儲(chǔ)每個(gè)客戶的訂單數(shù)量，然后將客戶表和臨時(shí)表進(jìn)行連接，得到每個(gè)客戶的訂單數(shù)量。使用臨時(shí)表可以避免重復(fù)計(jì)算子查詢的結(jié)果，從而提高性能。

最佳實(shí)踐是，在復(fù)雜的查詢中，優(yōu)先考慮使用臨時(shí)表，特別是當(dāng)子查詢被多次使用，或者結(jié)果集很大時(shí)。

MySQL 8.0 對(duì)臨時(shí)表的改進(jìn)

MySQL 8.0 對(duì)臨時(shí)表進(jìn)行了多項(xiàng)改進(jìn)，包括：

• 支持 TEMPORARY TABLE 的 WITH 子句：可以在 WITH 子句中創(chuàng)建臨時(shí)表，使 SQL 語句更簡(jiǎn)潔。
• 支持 TEMPORARY TABLE 的 CREATE OR REPLACE 語法：如果臨時(shí)表已經(jīng)存在，則會(huì)先刪除臨時(shí)表，然后重新創(chuàng)建臨時(shí)表。
• 優(yōu)化了臨時(shí)表的存儲(chǔ)引擎：MySQL 8.0 使用更高效的存儲(chǔ)引擎存儲(chǔ)臨時(shí)表，提高了性能。

這些改進(jìn)使得臨時(shí)表更加易于使用，性能也更高。

總的來說，臨時(shí)表是 MySQL 中一個(gè)非常重要的功能。掌握臨時(shí)表的使用技巧，可以幫助我們編寫更高效的 SQL 語句。

以上就是MySQL如何創(chuàng)建臨時(shí)表 內(nèi)存臨時(shí)表與磁盤臨時(shí)表