Mysql大表全表查詢

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

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

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

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

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

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

1、Server層

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

• Mysql的 net buffer，由參數(shù) net_buffer_length控制，默認(rèn)大小為 16K；即一個(gè)查詢不論返回結(jié)果多大，讀Mysql的影響就是 net_buffer_length大小。
• 本地網(wǎng)絡(luò)棧： Mysql服務(wù)器的 socket send buffer【默認(rèn)配置在/proc/sys/net/core/wmem_default，當(dāng)寫滿時(shí)，會(huì)暫停接受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ù)包，因?yàn)榈讓邮莟cp協(xié)議。

而從表象上看，執(zhí)行 show processlist，查詢到的結(jié)果為 Sending to client，所以不能簡(jiǎn)單的理解成發(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%。

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

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

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

總結(jié)

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

最新評(píng)論