Mysql大表全表查詢的全過程(分析底層的數(shù)據(jù)流轉(zhuǎn)過程)
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è)參考,也希望大家多多支持腳本之家。
相關(guān)文章
mysql 8.0.22壓縮包完整安裝與配置教程圖解(親測(cè)安裝有效)
這篇文章主要介紹了mysql 8.0.22壓縮包完整安裝與配置教程圖解(親測(cè)安裝有效),本文通過圖文并茂的形式給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-12-12mysql 5.7.20常用下載、安裝和配置方法及簡(jiǎn)單操作技巧(解壓版免安裝)
這篇文章主要介紹了mysql 5.7.20常用下載、安裝和配置方法及簡(jiǎn)單操作技巧(解壓版免安裝)的相關(guān)資料,需要的朋友可以參考下2017-11-11MySQL5綠色版windows下安裝總結(jié)(推薦)
這篇文章主要介紹了MySQL5綠色版windows下安裝總結(jié),需要的朋友可以參考下2017-03-03mysql創(chuàng)建函數(shù)出現(xiàn)1418錯(cuò)誤的解決辦法
本篇文章是對(duì)在Mysql中創(chuàng)建函數(shù)報(bào)“ERROR 1418”的解決方法進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06MYSQL神秘的HANDLER命令與實(shí)現(xiàn)方法
這篇文章主要介紹了MYSQL神秘的HANDLER命令與實(shí)現(xiàn)方法,需要的朋友可以參考下2016-07-07MySQL在生產(chǎn)環(huán)境出現(xiàn)無(wú)法啟動(dòng)的問題解決
在當(dāng)今的數(shù)據(jù)驅(qū)動(dòng)世界中,MySQL作為廣泛應(yīng)用的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng),在眾多生產(chǎn)環(huán)境中承擔(dān)著至關(guān)重要的角色,然而,面對(duì)復(fù)雜多變的業(yè)務(wù)場(chǎng)景,MySQL可能會(huì)遭遇各類故障和性能瓶頸,本文將深入探討MySQL在生產(chǎn)環(huán)境出現(xiàn)無(wú)法啟動(dòng)的問題解決,需要的朋友可以參考下2024-10-10在MySQL中使用mysqlbinlog flashback的簡(jiǎn)單教程
這篇文章主要介紹了在MySQL中使用mysqlbinlog flashback的簡(jiǎn)單教程,可以很方便地恢復(fù)數(shù)據(jù),作者還列出了使用時(shí)一些需要注意的地方,需要的朋友可以參考下2015-05-05MySQL數(shù)據(jù)庫(kù)遷移到Oracle數(shù)據(jù)庫(kù)的完整步驟記錄
在研發(fā)過程中可能會(huì)用到將表數(shù)據(jù)庫(kù)中的表結(jié)構(gòu)及數(shù)據(jù)遷移到另外一種數(shù)據(jù)庫(kù)中,比如說從mysql中遷移到oracle中,這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫(kù)遷移到Oracle數(shù)據(jù)庫(kù)的完整步驟,需要的朋友可以參考下2024-06-06