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

我們就來看看，出現(xiàn)這個(gè)問題的原因吧。

內(nèi)存表的數(shù)據(jù)組織結(jié)構(gòu)

為了便于分析，我來把這個(gè)問題簡化一下，假設(shè)有以下的兩張表 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); 復(fù)制代碼

然后，我分別執(zhí)行 select * from t1 和 select * from t2。

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖 1 兩個(gè)查詢結(jié)果 -0 的位置

可以看到，內(nèi)存表 t1 的返回結(jié)果里面 0 在最后一行，而 InnoDB 表 t2 的返回結(jié)果里 0 在第一行。

出現(xiàn)這個(gè)區(qū)別的原因，要從這兩個(gè)引擎的主鍵索引的組織方式說起。

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

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 2 表 t2 的數(shù)據(jù)組織

主鍵索引上的值是有序存儲(chǔ)的。在執(zhí)行 select * 的時(shí)候，就會(huì)按照葉子節(jié)點(diǎn)從左到右掃描，所以得到的結(jié)果里，0 就出現(xiàn)在第一行。

與 InnoDB 引擎不同，Memory 引擎的數(shù)據(jù)和索引是分開的。我們來看一下表 t1 中的數(shù)據(jù)內(nèi)容。

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 3 表 t1 的數(shù)據(jù)組織

可以看到，內(nèi)存表的數(shù)據(jù)部分以數(shù)組的方式單獨(dú)存放，而主鍵 id 索引里，存的是每個(gè)數(shù)據(jù)的位置。主鍵 id 是 hash 索引，可以看到索引上的 key 并不是有序的。

在內(nèi)存表 t1 中，當(dāng)我執(zhí)行 select * 的時(shí)候，走的是全表掃描，也就是順序掃描這個(gè)數(shù)組。因此，0 就是最后一個(gè)被讀到，并放入結(jié)果集的數(shù)據(jù)。

可見，InnoDB 和 Memory 引擎的數(shù)據(jù)組織方式是不同的：

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

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

InnoDB 表的數(shù)據(jù)總是有序存放的，而內(nèi)存表的數(shù)據(jù)就是按照寫入順序存放的；

當(dāng)數(shù)據(jù)文件有空洞的時(shí)候，InnoDB 表在插入新數(shù)據(jù)的時(shí)候，為了保證數(shù)據(jù)有序性，只能在固定的位置寫入新值，而內(nèi)存表找到空位就可以插入新值；

數(shù)據(jù)位置發(fā)生變化的時(shí)候，InnoDB 表只需要修改主鍵索引，而內(nèi)存表需要修改所有索引；

InnoDB 表用主鍵索引查詢時(shí)需要走一次索引查找，用普通索引查詢的時(shí)候，需要走兩次索引查找。而內(nèi)存表沒有這個(gè)區(qū)別，所有索引的“地位”都是相同的。

InnoDB 支持變長數(shù)據(jù)類型，不同記錄的長度可能不同；內(nèi)存表不支持 Blob 和 Text 字段，并且即使定義了 varchar(N)，實(shí)際也當(dāng)作 char(N)，也就是固定長度字符串來存儲(chǔ)，因此內(nèi)存表的每行數(shù)據(jù)長度相同。

由于內(nèi)存表的這些特性，每個(gè)數(shù)據(jù)行被刪除以后，空出的這個(gè)位置都可以被接下來要插入的數(shù)據(jù)復(fù)用。比如，如果要在表 t1 中執(zhí)行：

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

就會(huì)看到返回結(jié)果里，id=10 這一行出現(xiàn)在 id=4 之后，也就是原來 id=5 這行數(shù)據(jù)的位置。

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

select * from t1 where id<5; 復(fù)制代碼

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

hash 索引和 B-Tree 索引

實(shí)際上，內(nèi)存表也是支 B-Tree 索引的。在 id 列上創(chuàng)建一個(gè) B-Tree 索引，SQL 語句可以這么寫：

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

這時(shí)，表 t1 的數(shù)據(jù)組織形式就變成了這樣：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 4 表 t1 的數(shù)據(jù)組織 -- 增加 B-Tree 索引

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

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

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

可以看到，執(zhí)行 select * from t1 where id<5 的時(shí)候，優(yōu)化器會(huì)選擇 B-Tree 索引，所以返回結(jié)果是 0 到 4。 使用 force index 強(qiáng)行使用主鍵 id 這個(gè)索引，id=0 這一行就在結(jié)果集的最末尾了。

其實(shí)，一般在我們的印象中，內(nèi)存表的優(yōu)勢是速度快，其中的一個(gè)原因就是 Memory 引擎支持 hash 索引。當(dāng)然，更重要的原因是，內(nèi)存表的所有數(shù)據(jù)都保存在內(nèi)存，而內(nèi)存的讀寫速度總是比磁盤快。

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

鎖粒度問題；

數(shù)據(jù)持久化問題。

內(nèi)存表的鎖

我們先來說說內(nèi)存表的鎖粒度問題。

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

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

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 6 內(nèi)存表的表鎖 -- 復(fù)現(xiàn)步驟

在這個(gè)執(zhí)行序列里，session A 的 update 語句要執(zhí)行 50 秒，在這個(gè)語句執(zhí)行期間 session B 的查詢會(huì)進(jìn)入鎖等待狀態(tài)。session C 的 show processlist 結(jié)果輸出如下：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖 7 內(nèi)存表的表鎖 -- 結(jié)果

跟行鎖比起來，表鎖對并發(fā)訪問的支持不夠好。所以，內(nèi)存表的鎖粒度問題，決定了它在處理并發(fā)事務(wù)的時(shí)候，性能也不會(huì)太好。

數(shù)據(jù)持久性問題

接下來，我們再看看數(shù)據(jù)持久性的問題。

數(shù)據(jù)放在內(nèi)存中，是內(nèi)存表的優(yōu)勢，但也是一個(gè)劣勢。因?yàn)?#xff0c;數(shù)據(jù)庫重啟的時(shí)候，所有的內(nèi)存表都會(huì)被清空。

你可能會(huì)說，如果數(shù)據(jù)庫異常重啟，內(nèi)存表被清空也就清空了，不會(huì)有什么問題啊。但是，在高可用架構(gòu)下，內(nèi)存表的這個(gè)特點(diǎn)簡直可以當(dāng)做 bug 來看待了。為什么這么說呢？

我們先看看 M-S 架構(gòu)下，使用內(nèi)存表存在的問題。

我們來看一下下面這個(gè)時(shí)序：

業(yè)務(wù)正常訪問主庫；

備庫硬件升級(jí)，備庫重啟，內(nèi)存表 t1 內(nèi)容被清空；

備庫重啟后，客戶端發(fā)送一條 update 語句，修改表 t1 的數(shù)據(jù)行，這時(shí)備庫應(yīng)用線程就會(huì)報(bào)錯(cuò)“找不到要更新的行”。

這樣就會(huì)導(dǎo)致主備同步停止。當(dāng)然，如果這時(shí)候發(fā)生主備切換的話，客戶端會(huì)看到，表 t1 的數(shù)據(jù)“丟失”了。

在圖 8 中這種有 proxy 的架構(gòu)里，大家默認(rèn)主備切換的邏輯是由數(shù)據(jù)庫系統(tǒng)自己維護(hù)的。這樣對客戶端來說，就是“網(wǎng)絡(luò)斷開，重連之后，發(fā)現(xiàn)內(nèi)存表數(shù)據(jù)丟失了”。

你可能說這還好啊，畢竟主備發(fā)生切換，連接會(huì)斷開，業(yè)務(wù)端能夠感知到異常。

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

如果你使用是如圖 9 所示的雙 M 結(jié)構(gòu)的話：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 9 雙 M 結(jié)構(gòu)

在備庫重啟的時(shí)候，備庫 binlog 里的 delete 語句就會(huì)傳到主庫，然后把主庫內(nèi)存表的內(nèi)容刪除。這樣你在使用的時(shí)候就會(huì)發(fā)現(xiàn)，主庫的內(nèi)存表數(shù)據(jù)突然被清空了。

基于上面的分析，你可以看到，內(nèi)存表并不適合在生產(chǎn)環(huán)境上作為普通數(shù)據(jù)表使用。

有同學(xué)會(huì)說，但是內(nèi)存表執(zhí)行速度快呀。這個(gè)問題，其實(shí)你可以這么分析：

如果你的表更新量大，那么并發(fā)度是一個(gè)很重要的參考指標(biāo)，InnoDB 支持行鎖，并發(fā)度比內(nèi)存表好；

能放到內(nèi)存表的數(shù)據(jù)量都不大。如果你考慮的是讀的性能，一個(gè)讀 QPS 很高并且數(shù)據(jù)量不大的表，即使是使用 InnoDB，數(shù)據(jù)也是都會(huì)緩存在 InnoDB Buffer Pool 里的。因此，使用 InnoDB 表的讀性能也不會(huì)差。

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

但是，有一個(gè)場景卻是例外的。

這個(gè)場景就是，我們在第 35 和 36 篇說到的用戶臨時(shí)表。在數(shù)據(jù)量可控，不會(huì)耗費(fèi)過多內(nèi)存的情況下，你可以考慮使用內(nèi)存表。

內(nèi)存臨時(shí)表剛好可以無視內(nèi)存表的兩個(gè)不足，主要是下面的三個(gè)原因：

臨時(shí)表不會(huì)被其他線程訪問，沒有并發(fā)性的問題；

臨時(shí)表重啟后也是需要?jiǎng)h除的，清空數(shù)據(jù)這個(gè)問題不存在；

備庫的臨時(shí)表也不會(huì)影響主庫的用戶線程。

現(xiàn)在，我們回過頭再看一下第 35 篇 join 語句優(yōu)化的例子，當(dāng)時(shí)我建議的是創(chuàng)建一個(gè) InnoDB 臨時(shí)表，使用的語句序列是：

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); 復(fù)制代碼

了解了內(nèi)存表的特性，你就知道了， 其實(shí)這里使用內(nèi)存臨時(shí)表的效果更好，原因有三個(gè)：

相比于 InnoDB 表，使用內(nèi)存表不需要寫磁盤，往表 temp_t 的寫數(shù)據(jù)的速度更快；

索引 b 使用 hash 索引，查找的速度比 B-Tree 索引快；

臨時(shí)表數(shù)據(jù)只有 2000 行，占用的內(nèi)存有限。

因此，你可以對第 35 篇文章的語句序列做一個(gè)改寫，將臨時(shí)表 t1 改成內(nèi)存臨時(shí)表，并且在字段 b 上創(chuàng)建一個(gè) 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); 復(fù)制代碼

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖 10 使用內(nèi)存臨時(shí)表的執(zhí)行效果

可以看到，不論是導(dǎo)入數(shù)據(jù)的時(shí)間，還是執(zhí)行 join 的時(shí)間，使用內(nèi)存臨時(shí)表的速度都比使用 InnoDB 臨時(shí)表要更快一些。

小結(jié)

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

可以看到，由于重啟會(huì)丟數(shù)據(jù)，如果一個(gè)備庫重啟，會(huì)導(dǎo)致主備同步線程停止；如果主庫跟這個(gè)備庫是雙 M 架構(gòu)，還可能導(dǎo)致主庫的內(nèi)存表數(shù)據(jù)被刪掉。

因此，在生產(chǎn)上，我不建議你使用普通內(nèi)存表。

如果你是 DBA，可以在建表的審核系統(tǒng)中增加這類規(guī)則，要求業(yè)務(wù)改用 InnoDB 表。我們在文中也分析了，其實(shí) InnoDB 表性能還不錯(cuò)，而且數(shù)據(jù)安全也有保障。而內(nèi)存表由于不支持行鎖，更新語句會(huì)阻塞查詢，性能也未必就如想象中那么好。

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

最后，我給你留一個(gè)問題吧。

假設(shè)你剛剛接手的一個(gè)數(shù)據(jù)庫上，真的發(fā)現(xiàn)了一個(gè)內(nèi)存表。備庫重啟之后肯定是會(huì)導(dǎo)致備庫的內(nèi)存表數(shù)據(jù)被清空，進(jìn)而導(dǎo)致主備同步停止。這時(shí)，最好的做法是將它修改成 InnoDB 引擎表。

假設(shè)當(dāng)時(shí)的業(yè)務(wù)場景暫時(shí)不允許你修改引擎，你可以加上什么自動(dòng)化邏輯，來避免主備同步停止呢？

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

上期問題時(shí)間

今天文章的正文內(nèi)容，已經(jīng)回答了我們上期的問題，這里就不再贅述了。

轉(zhuǎn)載于:https://juejin.im/post/5d05f6f2e51d45108223fc5a

總結(jié)

以上是生活随笔為你收集整理的mysql实战38 | 都说InnoDB好，那还要不要使用Memory引擎？的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。