Mysql大表全表查詢

當(dāng)我們需要對一整張大表的數(shù)據(jù)執(zhí)行全量查詢操作，比如select * from t 沒有where條件，整個數(shù)據(jù)有幾千萬條占用內(nèi)存大概 100G，而Mysql所在服務(wù)器的內(nèi)存只有8G，那就不直接OOM,將整個數(shù)據(jù)庫打崩了嗎?

剛開始開發(fā)的時候會有這樣的疑問，但是隨著時間的推移知道是不會打崩的，但是為什么不會崩，慢慢地就沒有好奇心了。

下面對整個流程進(jìn)行分析，主要的沖擊點就是Mysql和InnoDB，所以下面還是分成兩個部分進(jìn)行分析。

下面的分析同樣適用于所有的查詢流程，只是其他查詢操作流程更復(fù)雜，但是數(shù)據(jù)量無論大小都會按照下面的流程執(zhí)行。

查詢整張表其實就是查詢 主鍵聚簇索引的那棵B+樹，比如查詢的就是InnoDB 表 db1. t。

查詢和返回按照java方式理解為 request和response流程，request查詢流程可以理解為：Mysql架構(gòu)圖 ，即下面分析的是返回的流程：

1、Server層

Server層不會一次調(diào)用InnoDB存儲引擎接口獲取全量數(shù)據(jù)，也不是一次將所有數(shù)據(jù)發(fā)生給Mysql客戶端，Mysql是邊讀邊發(fā)送的，發(fā)送的過程中依賴兩個緩存池：

• Mysql的 net buffer，由參數(shù) net_buffer_length控制，默認(rèn)大小為 16K；即一個查詢不論返回結(jié)果多大，讀Mysql的影響就是 net_buffer_length大小。
• 本地網(wǎng)絡(luò)棧： Mysql服務(wù)器的 socket send buffer【默認(rèn)配置在/proc/sys/net/core/wmem_default，當(dāng)寫滿時，會暫停接受net buffer的數(shù)據(jù)】、 Mysql客戶端的 socket receive buffer；

具體的查詢執(zhí)行流程如下：

• 獲取一行，寫到 net buffer 中。這塊內(nèi)存的大小是由參數(shù) net_buffer_length 定義的，默認(rèn)是 16k。
• 重復(fù)獲取行，直到 net buffer 寫滿，調(diào)用網(wǎng)絡(luò)接口發(fā)出去。
• 如果發(fā)送成功，就清空 net buffer，然后繼續(xù)取下一行，并寫入 net buffer。
• 如果發(fā)送函數(shù)返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地網(wǎng)絡(luò)棧（socket send buffer）寫滿了，進(jìn)入等待。直到網(wǎng)絡(luò)棧重新可寫，再繼續(xù)發(fā)送。

并且在此數(shù)據(jù)查詢過程當(dāng)前，底層的表現(xiàn)就是Mysql服務(wù)端的socket send buffer 和 Mysql客戶端 socket receive buffer，在不停的發(fā)生和接收數(shù)據(jù)包，因為底層是tcp協(xié)議。

而從表象上看，執(zhí)行 show processlist，查詢到的結(jié)果為 Sending to client，所以不能簡單的理解成發(fā)生數(shù)據(jù)給客戶端，僅僅表示服務(wù)器端的網(wǎng)絡(luò)棧寫滿了。

2、innoDB層

在前面博客分析了InnoDB的架構(gòu)圖，分為內(nèi)存和磁盤架構(gòu)。內(nèi)存架構(gòu)中最大的一塊兒內(nèi)存就是 Buffer Pool，可以占用到物理內(nèi)存的 60~80%。

并且分析了針對當(dāng)前這種大表查詢流程，會將所有的B+樹緩存頁都在變種的 LRU緩存隊列中過一遍。

所以Mysql將緩存鏈表分成young和old區(qū)，使用配置參數(shù) innodb_old_blocks_time控制緩存頁真正加入的young取余的條件。

即大表查詢流程對innodb層的影響就是，將所有主鍵聚簇索引B+樹上的頁，全部在 Buffer Pool內(nèi)部的 LRU鏈的 old區(qū)域全部執(zhí)行一遍，當(dāng)超過內(nèi)存大小限制時，再從 old鏈的尾部出隊列。流程圖如下：

總結(jié)

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論