我在上一篇文章末尾留給你的問題是：兩個 group by 語句都用了 order by null，為什么使用內存臨時表得到的語句結果里，0 這個值在最后一行；而使用磁盤臨時表得到的結果里，0 這個值在第一行？今天

我們就來看看，出現這個問題的原因吧。

內存表的數據組織結構

為了便于分析，我來把這個問題簡化一下，假設有以下的兩張表 t1 和 t2，其中表 t1 使用 Memory 引擎， 表 t2 使用 InnoDB 引擎。

create table t1(id int primary key, c int) engine=Memory;
create table t2(id int primary key, c int) engine=innodb;
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
insert into t2 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
復制代碼

然后，我分別執行 select * from t1 和 select * from t2。

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖 1 兩個查詢結果 -0 的位置

可以看到，內存表 t1 的返回結果里面 0 在最后一行，而 InnoDB 表 t2 的返回結果里 0 在第一行。

出現這個區別的原因，要從這兩個引擎的主鍵索引的組織方式說起。

表 t2 用的是 InnoDB 引擎，它的主鍵索引 id 的組織方式，你已經很熟悉了：InnoDB 表的數據就放在主鍵索引樹上，主鍵索引是 B+ 樹。所以表 t2 的數據組織方式如下圖所示：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 2 表 t2 的數據組織

主鍵索引上的值是有序存儲的。在執行 select * 的時候，就會按照葉子節點從左到右掃描，所以得到的結果里，0 就出現在第一行。

與 InnoDB 引擎不同，Memory 引擎的數據和索引是分開的。我們來看一下表 t1 中的數據內容。

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 3 表 t1 的數據組織

可以看到，內存表的數據部分以數組的方式單獨存放，而主鍵 id 索引里，存的是每個數據的位置。主鍵 id 是 hash 索引，可以看到索引上的 key 并不是有序的。

在內存表 t1 中，當我執行 select * 的時候，走的是全表掃描，也就是順序掃描這個數組。因此，0 就是最后一個被讀到，并放入結果集的數據。

可見，InnoDB 和 Memory 引擎的數據組織方式是不同的：

• InnoDB 引擎把數據放在主鍵索引上，其他索引上保存的是主鍵 id。這種方式，我們稱之為索引組織表（Index Organizied Table）。
  
• 而 Memory 引擎采用的是把數據單獨存放，索引上保存數據位置的數據組織形式，我們稱之為堆組織表（Heap Organizied Table）。
  

從中我們可以看出，這兩個引擎的一些典型不同：

1. InnoDB 表的數據總是有序存放的，而內存表的數據就是按照寫入順序存放的；
   
2. 當數據文件有空洞的時候，InnoDB 表在插入新數據的時候，為了保證數據有序性，只能在固定的位置寫入新值，而內存表找到空位就可以插入新值；
   
3. 數據位置發生變化的時候，InnoDB 表只需要修改主鍵索引，而內存表需要修改所有索引；
   
4. InnoDB 表用主鍵索引查詢時需要走一次索引查找，用普通索引查詢的時候，需要走兩次索引查找。而內存表沒有這個區別，所有索引的“地位”都是相同的。
   
5. InnoDB 支持變長數據類型，不同記錄的長度可能不同；內存表不支持 Blob 和 Text 字段，并且即使定義了 varchar(N)，實際也當作 char(N)，也就是固定長度字符串來存儲，因此內存表的每行數據長度相同。
   

由于內存表的這些特性，每個數據行被刪除以后，空出的這個位置都可以被接下來要插入的數據復用。比如，如果要在表 t1 中執行：

delete from t1 where id=5;
insert into t1 values(10,10);
select * from t1;
復制代碼

就會看到返回結果里，id=10 這一行出現在 id=4 之后，也就是原來 id=5 這行數據的位置。

需要指出的是，表 t1 的這個主鍵索引是哈希索引，因此如果執行范圍查詢，比如

select * from t1 where id<5;
復制代碼

是用不上主鍵索引的，需要走全表掃描。你可以借此再回顧下第 4 篇文章的內容。那如果要讓內存表支持范圍掃描，應該怎么辦呢 ？

hash 索引和 B-Tree 索引

實際上，內存表也是支 B-Tree 索引的。在 id 列上創建一個 B-Tree 索引，SQL 語句可以這么寫：

alter table t1 add index a_btree_index using btree (id);
復制代碼

這時，表 t1 的數據組織形式就變成了這樣：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 4 表 t1 的數據組織 -- 增加 B-Tree 索引

新增的這個 B-Tree 索引你看著就眼熟了，這跟 InnoDB 的 b+ 樹索引組織形式類似。

作為對比，你可以看一下這下面這兩個語句的輸出：

? ? ? ? ? ? ? ? ? ? ? ? ? ?圖 5 使用 B-Tree 和 hash 索引查詢返回結果對比

可以看到，執行 select * from t1 where id<5 的時候，優化器會選擇 B-Tree 索引，所以返回結果是 0 到 4。 使用 force index 強行使用主鍵 id 這個索引，id=0 這一行就在結果集的最末尾了。

其實，一般在我們的印象中，內存表的優勢是速度快，其中的一個原因就是 Memory 引擎支持 hash 索引。當然，更重要的原因是，內存表的所有數據都保存在內存，而內存的讀寫速度總是比磁盤快。

但是，接下來我要跟你說明，為什么我不建議你在生產環境上使用內存表。這里的原因主要包括兩個方面：

1. 鎖粒度問題；
   
2. 數據持久化問題。
   

內存表的鎖

我們先來說說內存表的鎖粒度問題。

內存表不支持行鎖，只支持表鎖。因此，一張表只要有更新，就會堵住其他所有在這個表上的讀寫操作。

需要注意的是，這里的表鎖跟之前我們介紹過的 MDL 鎖不同，但都是表級的鎖。接下來，我通過下面這個場景，跟你模擬一下內存表的表級鎖。

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 6 內存表的表鎖 -- 復現步驟

在這個執行序列里，session A 的 update 語句要執行 50 秒，在這個語句執行期間 session B 的查詢會進入鎖等待狀態。session C 的 show processlist 結果輸出如下：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖 7 內存表的表鎖 -- 結果

跟行鎖比起來，表鎖對并發訪問的支持不夠好。所以，內存表的鎖粒度問題，決定了它在處理并發事務的時候，性能也不會太好。

數據持久性問題

接下來，我們再看看數據持久性的問題。

數據放在內存中，是內存表的優勢，但也是一個劣勢。因為，數據庫重啟的時候，所有的內存表都會被清空。

你可能會說，如果數據庫異常重啟，內存表被清空也就清空了，不會有什么問題啊。但是，在高可用架構下，內存表的這個特點簡直可以當做 bug 來看待了。為什么這么說呢？

我們先看看 M-S 架構下，使用內存表存在的問題。

我們來看一下下面這個時序：

1. 業務正常訪問主庫；
   
2. 備庫硬件升級，備庫重啟，內存表 t1 內容被清空；
   
3. 備庫重啟后，客戶端發送一條 update 語句，修改表 t1 的數據行，這時備庫應用線程就會報錯“找不到要更新的行”。
   

這樣就會導致主備同步停止。當然，如果這時候發生主備切換的話，客戶端會看到，表 t1 的數據“丟失”了。

在圖 8 中這種有 proxy 的架構里，大家默認主備切換的邏輯是由數據庫系統自己維護的。這樣對客戶端來說，就是“網絡斷開，重連之后，發現內存表數據丟失了”。

你可能說這還好啊，畢竟主備發生切換，連接會斷開，業務端能夠感知到異常。

但是，接下來內存表的這個特性就會讓使用現象顯得更“詭異”了。由于 MySQL 知道重啟之后，內存表的數據會丟失。所以，擔心主庫重啟之后，出現主備不一致，MySQL 在實現上做了這樣一件事兒：在數據庫重啟之后，往 binlog 里面寫入一行 DELETE FROM t1。

如果你使用是如圖 9 所示的雙 M 結構的話：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 9 雙 M 結構

在備庫重啟的時候，備庫 binlog 里的 delete 語句就會傳到主庫，然后把主庫內存表的內容刪除。這樣你在使用的時候就會發現，主庫的內存表數據突然被清空了。

基于上面的分析，你可以看到，內存表并不適合在生產環境上作為普通數據表使用。

有同學會說，但是內存表執行速度快呀。這個問題，其實你可以這么分析：

1. 如果你的表更新量大，那么并發度是一個很重要的參考指標，InnoDB 支持行鎖，并發度比內存表好；
   
2. 能放到內存表的數據量都不大。如果你考慮的是讀的性能，一個讀 QPS 很高并且數據量不大的表，即使是使用 InnoDB，數據也是都會緩存在 InnoDB Buffer Pool 里的。因此，使用 InnoDB 表的讀性能也不會差。

所以，我建議你把普通內存表都用 InnoDB 表來代替。我建議你把普通內存表都用 InnoDB 表來代替。

但是，有一個場景卻是例外的。

這個場景就是，我們在第 35 和 36 篇說到的用戶臨時表。在數據量可控，不會耗費過多內存的情況下，你可以考慮使用內存表。

內存臨時表剛好可以無視內存表的兩個不足，主要是下面的三個原因：

1. 臨時表不會被其他線程訪問，沒有并發性的問題；
   
2. 臨時表重啟后也是需要刪除的，清空數據這個問題不存在；
   
3. 備庫的臨時表也不會影響主庫的用戶線程。
   

現在，我們回過頭再看一下第 35 篇 join 語句優化的例子，當時我建議的是創建一個 InnoDB 臨時表，使用的語句序列是：

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);
復制代碼

了解了內存表的特性，你就知道了， 其實這里使用內存臨時表的效果更好，原因有三個：

1. 相比于 InnoDB 表，使用內存表不需要寫磁盤，往表 temp_t 的寫數據的速度更快；
   
2. 索引 b 使用 hash 索引，查找的速度比 B-Tree 索引快；
   
3. 臨時表數據只有 2000 行，占用的內存有限。
   

因此，你可以對第 35 篇文章的語句序列做一個改寫，將臨時表 t1 改成內存臨時表，并且在字段 b 上創建一個 hash 索引。

create temporary table temp_t(id int primary key, a int, b int, index (b))engine=memory;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);
復制代碼

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 10 使用內存臨時表的執行效果

可以看到，不論是導入數據的時間，還是執行 join 的時間，使用內存臨時表的速度都比使用 InnoDB 臨時表要更快一些。

小結

今天這篇文章，我從“要不要使用內存表”這個問題展開，和你介紹了 Memory 引擎的幾個特性。

可以看到，由于重啟會丟數據，如果一個備庫重啟，會導致主備同步線程停止；如果主庫跟這個備庫是雙 M 架構，還可能導致主庫的內存表數據被刪掉。

因此，在生產上，我不建議你使用普通內存表。

如果你是 DBA，可以在建表的審核系統中增加這類規則，要求業務改用 InnoDB 表。我們在文中也分析了，其實 InnoDB 表性能還不錯，而且數據安全也有保障。而內存表由于不支持行鎖，更新語句會阻塞查詢，性能也未必就如想象中那么好。

基于內存表的特性，我們還分析了它的一個適用場景，就是內存臨時表。內存表支持 hash 索引，這個特性利用起來，對復雜查詢的加速效果還是很不錯的。

最后，我給你留一個問題吧。

假設你剛剛接手的一個數據庫上，真的發現了一個內存表。備庫重啟之后肯定是會導致備庫的內存表數據被清空，進而導致主備同步停止。這時，最好的做法是將它修改成 InnoDB 引擎表。

假設當時的業務場景暫時不允許你修改引擎，你可以加上什么自動化邏輯，來避免主備同步停止呢？

你可以把你的思考和分析寫在評論區，我會在下一篇文章的末尾跟你討論這個問題。感謝你的收聽，也歡迎你把這篇文章分享給更多的朋友一起閱讀。

上期問題時間

今天文章的正文內容，已經回答了我們上期的問題，這里就不再贅述了。