Mysql大表全表查詢的全過程(分析底層的數(shù)據(jù)流轉(zhuǎn)過程)
Mysql大表全表查詢
當(dāng)我們需要對(duì)一整張大表的數(shù)據(jù)執(zhí)行全量查詢操作，比如select * from t 沒有where條件，整個(gè)數(shù)據(jù)有幾千萬條占用內(nèi)存大概 100G，而Mysql所在服務(wù)器的內(nèi)存只有8G，那就不直接OOM,將整個(gè)數(shù)據(jù)庫打崩了嗎?
剛開始開發(fā)的時(shí)候會(huì)有這樣的疑問，但是隨著時(shí)間的推移知道是不會(huì)打崩的，但是為什么不會(huì)崩，慢慢地就沒有好奇心了。
下面對(duì)整個(gè)流程進(jìn)行分析，主要的沖擊點(diǎn)就是Mysql和InnoDB，所以下面還是分成兩個(gè)部分進(jìn)行分析。
下面的分析同樣適用于所有的查詢流程，只是其他查詢操作流程更復(fù)雜，但是數(shù)據(jù)量無論大小都會(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，所以不能簡單的理解成發(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+樹緩存頁都在變種的 LRU緩存隊(duì)列中過一遍。
所以Mysql將緩存鏈表分成young和old區(qū)，使用配置參數(shù) innodb_old_blocks_time控制緩存頁真正加入的young取余的條件。
即大表查詢流程對(duì)innodb層的影響就是，將所有主鍵聚簇索引B+樹上的頁，全部在 Buffer Pool內(nèi)部的 LRU鏈的 old區(qū)域全部執(zhí)行一遍，當(dāng)超過內(nèi)存大小限制時(shí)，再從 old鏈的尾部出隊(duì)列。流程圖如下：
總結(jié)
以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。
相關(guān)文章
詳解JDBC數(shù)據(jù)庫鏈接及相關(guān)方法的封裝
這篇文章主要介紹了詳解JDBC數(shù)據(jù)庫鏈接及相關(guān)方法的封裝的相關(guān)資料,下面是封裝的具體類，用到了泛型和反射，希望能幫助到大家，需要的朋友可以參考下2017-08-08
本文主要介紹了MySQL DQL語句的具體使用，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-03-03
SQL函數(shù)實(shí)現(xiàn)Group_concat用法
GROUP_CONCAT用于將每個(gè)分組內(nèi)的值連接成一個(gè)字符串,并通過指定的分隔符進(jìn)行分隔,本文主要介紹了SQL函數(shù)實(shí)現(xiàn)Group_concat用法,具有一定的參考價(jià)值,感興趣的可以了解一下2024-08-08
window下mysql 8.0.15 winx64安裝配置方法圖文教程
這篇文章主要為大家詳細(xì)介紹了window下mysql 8.0.15 winx64安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-03-03
MySQL數(shù)據(jù)庫監(jiān)控軟件lepus使用問題以及解決辦法
這篇文章主要介紹了MySQL數(shù)據(jù)庫監(jiān)控軟件lepus使用問題及解決辦法,非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2018-09-09
hash分區(qū)的目的是將數(shù)據(jù)按照某列進(jìn)行hash計(jì)算后更加均勻的分散到各個(gè)分區(qū),下面這篇文章主要給大家介紹了關(guān)于MySQL分區(qū)之HASH分區(qū)的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-04-04
這篇文章主要介紹了MySQL回表的性能傷害程度有多大?下面我們就帶著疑問進(jìn)入下面文章了解詳細(xì)內(nèi)容，需要的小伙伴可以參考一下，希望對(duì)你的學(xué)習(xí)有所幫助2022-02-02
這篇文章主要介紹了Mysql鎖機(jī)制,行鎖表鎖的使用,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2025-06-06
mysql聚集索引、輔助索引、覆蓋索引、聯(lián)合索引的使用
本文主要介紹了mysql聚集索引、輔助索引、覆蓋索引、聯(lián)合索引的使用，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2022-02-02
Mysql連接join查詢?cè)碇R(shí)點(diǎn)
在本文里我們給大家整理了一篇關(guān)于Mysql連接join查詢?cè)碇R(shí)點(diǎn)文章，對(duì)此感興趣的朋友們可以學(xué)習(xí)下。2019-02-02