分享一道京東面試題：MySQL 內存表和臨時表有什么區別呢？

1.概念

臨時表用來給復雜查詢保存中間結果，分為內部臨時表和外部臨時表。

• 內部臨時表：MySQL 內部生成，比如 union 語句、group by 語句可以保存中間結果；
• 外部臨時表：由客戶端創建，SQL 語法：CREATE TEMPORARY TABLE。

內存表是指使用 Memory 存儲引擎的表，創建語句如下：

create table test_memory(`id` int, `a` VARCHAR(20) DEFAULT NULL, PRIMARY KEY (`id`)) engine=Memory;

內存表可以給所有客戶端看到，客戶端連接關閉后不影響內存表。但如果 MySQL 重啟服務，內存表的數據會丟失，表結構還在。

可以看到，臨時表也可以是內存表。

2.生命周期

臨時表是會話級別的，客戶端連接關閉后，臨時表自動刪除。同時，會話之間的臨時表是相互隔離的，不同會話可以創建相同名稱的臨時表。

內存表則不受會話的影響，只有使用 drop 才能刪除掉。

3.存儲

臨時表可以使用各種存儲引擎，比如 InnoDB、MyISAM 或者 MEMORY。創建時使用 ENGINE = xxx 來指定。如果臨時表使用的存儲引擎是 MEMORY（內存臨時表），則會受下面參數限制：

• tmp_table_size：臨時表的最大內存值；
• max_heap_table_size：臨時表的最大堆內存值，主要用來計算臨時表的最大行數。

內存臨時表的內存不能超過上面兩個參數最小值的，如果超出，則會轉化成磁盤臨時表，查詢效率下降。

內存表的數據存儲在內存中。如果服務重啟，或者內存不足，數據會丟失。

4.索引方式

內存表默認使用 hash 索引，不支持范圍掃描或排序。

臨時表則根據選擇的存儲引擎，支持的索引類型不同。如果選擇 Innodb，則支持 B+ 樹索引，支持事務，支持行級鎖。

5.使用場景

內存表主要用于數據量較小、需要快速訪問、允許數據丟失的場景。

臨時表則用于單個會話中需要保存臨時結果，會話結束可以自動清理的場景。比如 union、group by 語句。

下面看一下 union 語句執行過程，測試 SQL 如下： 先創建兩個表：

CREATE TABLE test_temp1(`id` INT, `a` VARCHAR(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB; 
CREATE TABLE test_temp2(`id` INT, `a` VARCHAR(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB;

然后執行查詢語句：

SELECT * FROM test_temp1 UNION SELECT * FROM test_temp2;

從執行計劃 Extra 可以看出，查詢語句用到了臨時表。

圖片

這個語句執行過程如下：

• 創建一個內存臨時表，這個臨時表有 2 個字段，id 和 a，其中 id 為主鍵；
• 執行第一個子查詢，獲取 test_temp1 的所有記錄，放到臨時表中；
• 執行第二個子查詢，獲取 test_temp2 表的所有記錄，依次插入臨時表中，如果有主鍵沖突，則失敗，繼續插入下一條；
• 將臨時表中數據作為結果集返回；
• 刪除臨時表。

6.總結

下面總結內存表和臨時表的區別：