分享一道京東面試題：MySQL 內(nèi)存表和臨時表有什么區(qū)別呢？

1.概念

臨時表用來給復(fù)雜查詢保存中間結(jié)果，分為內(nèi)部臨時表和外部臨時表。

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

內(nèi)存表是指使用 Memory 存儲引擎的表，創(chuàng)建語句如下：

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

內(nèi)存表可以給所有客戶端看到，客戶端連接關(guān)閉后不影響內(nèi)存表。但如果 MySQL 重啟服務(wù)，內(nèi)存表的數(shù)據(jù)會丟失，表結(jié)構(gòu)還在。

可以看到，臨時表也可以是內(nèi)存表。

2.生命周期

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

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

3.存儲

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

• tmp_table_size：臨時表的最大內(nèi)存值；
• max_heap_table_size：臨時表的最大堆內(nèi)存值，主要用來計算臨時表的最大行數(shù)。

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

內(nèi)存表的數(shù)據(jù)存儲在內(nèi)存中。如果服務(wù)重啟，或者內(nèi)存不足，數(shù)據(jù)會丟失。

4.索引方式

內(nèi)存表默認(rèn)使用 hash 索引，不支持范圍掃描或排序。

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

5.使用場景

內(nèi)存表主要用于數(shù)據(jù)量較小、需要快速訪問、允許數(shù)據(jù)丟失的場景。

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

下面看一下 union 語句執(zhí)行過程，測試 SQL 如下： 先創(chuàng)建兩個表：

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;

然后執(zhí)行查詢語句：

SELECT * FROM test_temp1 UNION SELECT * FROM test_temp2;

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

圖片

這個語句執(zhí)行過程如下：

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

6.總結(jié)

下面總結(jié)內(nèi)存表和臨時表的區(qū)別：