思考這樣一個問題：主機內(nèi)存只有 100G，現(xiàn)在要對一個 200G 的大表做全表掃描奏赘，會不會把數(shù)據(jù)庫主機的內(nèi)存用光了焊夸？
這個問題確實值得擔心，被系統(tǒng) OOM（out of memory）可不是鬧著玩的右冻。但是，反過來想想著拭，邏輯備份的時候纱扭，可不就是做整庫掃描嗎？如果這樣就會把內(nèi)存吃光儡遮，邏輯備份不是早就掛了乳蛾？
所以說，對大表做全表掃描鄙币，看來應該是沒問題的肃叶。但是，這個流程到底是怎么樣的呢十嘿？
全表掃描對 server 層的影響
- 假設因惭，我們現(xiàn)在要對一個 200G 的 InnoDB 表 db1. t，執(zhí)行一個全表掃描绩衷。當然蹦魔，你要把掃描結果保存在客戶端激率，會使用類似這樣的命令：
mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file
- 你已經(jīng)知道了，InnoDB 的數(shù)據(jù)是保存在主鍵索引上的勿决，所以全表掃描實際上是直接掃描表 t 的主鍵索引乒躺。這條查詢語句由于沒有其他的判斷條件，所以查到的每一行都可以直接放到結果集里面低缩，然后返回給客戶端嘉冒。那么，這個“結果集”存在哪里呢咆繁？
- 實際上讳推，服務端并不需要保存一個完整的結果集。取數(shù)據(jù)和發(fā)數(shù)據(jù)的流程是這樣的：
- 獲取一行玩般，寫到 net_buffer 中娜遵。這塊內(nèi)存的大小是由參數(shù) net_buffer_length 定義的，默認是 16k壤短。
- 重復獲取行设拟，直到 net_buffer 寫滿，調(diào)用網(wǎng)絡接口發(fā)出去久脯。
- 如果發(fā)送成功纳胧，就清空 net_buffer，然后繼續(xù)取下一行帘撰，并寫入 net_buffer跑慕。
- 如果發(fā)送函數(shù)返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地網(wǎng)絡棧（socket send buffer）寫滿了摧找，進入等待核行。直到網(wǎng)絡棧重新可寫，再繼續(xù)發(fā)送蹬耘。
查詢結果發(fā)送流程
- 從這個流程中兄纺，你可以看到：
- 一個查詢在發(fā)送過程中砾淌，占用的 MySQL 內(nèi)部的內(nèi)存最大就是 net_buffer_length 這么大，并不會達到 200G；
- socket send buffer 也不可能達到 200G（默認定義 /proc/sys/net/core/wmem_default）厌衔，如果 socket send buffer 被寫滿玫镐，就會暫停讀數(shù)據(jù)的流程躁锡。
- 也就是說则涯，MySQL 是“邊讀邊發(fā)的”，這個概念很重要杨刨。這就意味著晤柄，如果客戶端接收得慢，會導致 MySQL 服務端由于結果發(fā)不出去妖胀，這個事務的執(zhí)行時間變長芥颈。
- 比如下面這個狀態(tài)惠勒，就是故意讓客戶端不去讀 socket receive buffer 中的內(nèi)容，然后在服務端 show processlist 看到的結果浇借。
服務端發(fā)送阻塞
- 如果你看到 State 的值一直處于“Sending to client”，就表示服務器端的網(wǎng)絡棧寫滿了怕品。如果客戶端使用–quick 參數(shù)妇垢，會使用 mysql_use_result 方法。這個方法是讀一行處理一行肉康。你可以想象一下闯估，假設有一個業(yè)務的邏輯比較復雜，每讀一行數(shù)據(jù)以后要處理的邏輯如果很慢吼和，就會導致客戶端要過很久才會去取下一行數(shù)據(jù)涨薪，可能就會出現(xiàn)如圖所示的這種情況。
- 因此炫乓，對于正常的線上業(yè)務來說刚夺，如果一個查詢的返回結果不會很多的話，建議你使用 mysql_store_result 這個接口末捣，直接把查詢結果保存到本地內(nèi)存侠姑。如果數(shù)據(jù)量很大，這種情況下就需要改用 mysql_use_result 接口了
- 另一方面箩做，如果你在自己負責維護的 MySQL 里看到很多個線程都處于“Sending to client”這個狀態(tài)莽红，就意味著你要讓業(yè)務開發(fā)同學優(yōu)化查詢結果，并評估這么多的返回結果是否合理邦邦。
- 而如果要快速減少處于這個狀態(tài)的線程的話安吁，將 net_buffer_length 參數(shù)設置為一個更大的值是一個可選方案。
- 與“Sending to client”長相很類似的一個狀態(tài)是“Sending data”燃辖，這是一個經(jīng)常被誤會的問題鬼店。有同學問我說，在自己維護的實例上看到很多查詢語句的狀態(tài)是“Sending data”黔龟，但查看網(wǎng)絡也沒什么問題啊薪韩，為什么 Sending data 要這么久？
- 實際上捌锭，一個查詢語句的狀態(tài)變化是這樣的（注意：這里俘陷，我略去了其他無關的狀態(tài)）：
- MySQL 查詢語句進入執(zhí)行階段后，首先把狀態(tài)設置成“Sending data”观谦；
- 然后拉盾，發(fā)送執(zhí)行結果的列相關的信息（meta data) 給客戶端；
- 再繼續(xù)執(zhí)行語句的流程豁状；
- 執(zhí)行完成后捉偏，把狀態(tài)設置成空字符串倒得。
- 也就是說，“Sending data”并不一定是指“正在發(fā)送數(shù)據(jù)”夭禽，而可能是處于執(zhí)行器過程中的任意階段霞掺。比如，你可以構造一個鎖等待的場景讹躯，就能看到 Sending data 狀態(tài)菩彬。
讀全表被鎖
seding data狀態(tài)
- 可以看到，session B 明顯是在等鎖潮梯，狀態(tài)顯示為 Sending data骗灶。
也就是說，僅當一個線程處于“等待客戶端接收結果”的狀態(tài)秉馏，才會顯示"Sending to client"耙旦；而如果顯示成“Sending data”，它的意思只是“正在執(zhí)行”萝究。 - 現(xiàn)在你知道了免都，查詢的結果是分段發(fā)給客戶端的，因此掃描全表帆竹，查詢返回大量的數(shù)據(jù)琴昆，并不會把內(nèi)存打爆。
- 在 server 層的處理邏輯我們都清楚了馆揉，在 InnoDB 引擎里面又是怎么處理的呢业舍？ 掃描全表會不會對引擎系統(tǒng)造成影響呢？
全表掃描對 InnoDB 的影響
- 在前面介紹 WAL 機制的時候升酣，分析了 InnoDB 內(nèi)存的一個作用舷暮，是保存更新的結果，再配合 redo log噩茄，就避免了隨機寫盤下面。
- 內(nèi)存的數(shù)據(jù)頁是在 Buffer Pool (BP) 中管理的，在 WAL 里 Buffer Pool 起到了加速更新的作用绩聘。而實際上沥割，Buffer Pool 還有一個更重要的作用，就是加速查詢凿菩。
- 由于有 WAL 機制机杜，當事務提交的時候，磁盤上的數(shù)據(jù)頁是舊的衅谷，那如果這時候馬上有一個查詢要來讀這個數(shù)據(jù)頁椒拗，是不是要馬上把 redo log 應用到數(shù)據(jù)頁呢？
答案是不需要。因為這時候內(nèi)存數(shù)據(jù)頁的結果是最新的蚀苛，直接讀內(nèi)存頁就可以了在验。你看，這時候查詢根本不需要讀磁盤堵未，直接從內(nèi)存拿結果腋舌，速度是很快的。所以說渗蟹，Buffer Pool 還有加速查詢的作用块饺。 - 而 Buffer Pool 對查詢的加速效果，依賴于一個重要的指標拙徽，即：內(nèi)存命中率刨沦。
你可以在 show engine innodb status 結果中诗宣，查看一個系統(tǒng)當前的 BP 命中率膘怕。一般情況下，一個穩(wěn)定服務的線上系統(tǒng)召庞，要保證響應時間符合要求的話岛心，內(nèi)存命中率要在 99% 以上。
執(zhí)行 show engine innodb status 篮灼，可以看到“Buffer pool hit rate”字樣忘古，顯示的就是當前的命中率。比如下圖這個命中率诅诱，就是 99.0%髓堪。
show engine innodb status 顯示內(nèi)存命中率
- 如果所有查詢需要的數(shù)據(jù)頁都能夠直接從內(nèi)存得到，那是最好的娘荡，對應的命中率就是 100%干旁。但，這在實際生產(chǎn)上是很難做到的炮沐。
- InnoDB Buffer Pool 的大小是由參數(shù) innodb_buffer_pool_size 確定的争群，一般建議設置成可用物理內(nèi)存的 60%~80%。
- 在大約十年前大年，單機的數(shù)據(jù)量是上百個 G换薄，而物理內(nèi)存是幾個 G；現(xiàn)在雖然很多服務器都能有 128G 甚至更高的內(nèi)存翔试，但是單機的數(shù)據(jù)量卻達到了 T 級別轻要。
- 所以，innodb_buffer_pool_size 小于磁盤的數(shù)據(jù)量是很常見的垦缅。如果一個 Buffer Pool 滿了伦腐，而又要從磁盤讀入一個數(shù)據(jù)頁，那肯定是要淘汰一個舊數(shù)據(jù)頁的失都。
- InnoDB 內(nèi)存管理用的是最近最少使用 (Least Recently Used, LRU) 算法柏蘑，這個算法的核心就是淘汰最久未使用的數(shù)據(jù)幸冻。
基本LRU算法
- InnoDB 管理 Buffer Pool 的 LRU 算法，是用鏈表來實現(xiàn)的咳焚。
- 在圖的狀態(tài) 1 里洽损，鏈表頭部是 P1，表示 P1 是最近剛剛被訪問過的數(shù)據(jù)頁革半；假設內(nèi)存里只能放下這么多數(shù)據(jù)頁碑定；
- 這時候有一個讀請求訪問 P3，因此變成狀態(tài) 2又官，P3 被移到最前面延刘；
- 狀態(tài) 3 表示，這次訪問的數(shù)據(jù)頁是不存在于鏈表中的六敬，所以需要在 Buffer Pool 中新申請一個數(shù)據(jù)頁 Px碘赖，加到鏈表頭部。但是由于內(nèi)存已經(jīng)滿了外构，不能申請新的內(nèi)存普泡。于是，會清空鏈表末尾 Pm 這個數(shù)據(jù)頁的內(nèi)存审编，存入 Px 的內(nèi)容撼班，然后放到鏈表頭部。
- 從效果上看垒酬，就是最久沒有被訪問的數(shù)據(jù)頁 Pm砰嘁，被淘汰了。
- 這個算法乍一看上去沒什么問題勘究，但是如果考慮到要做一個全表掃描矮湘，會不會有問題呢？
- 假設按照這個算法乱顾，我們要掃描一個 200G 的表板祝，而這個表是一個歷史數(shù)據(jù)表，平時沒有業(yè)務訪問它走净。
- 那么券时，按照這個算法掃描的話，就會把當前的 Buffer Pool 里的數(shù)據(jù)全部淘汰掉伏伯，存入掃描過程中訪問到的數(shù)據(jù)頁的內(nèi)容橘洞。也就是說 Buffer Pool 里面主要放的是這個歷史數(shù)據(jù)表的數(shù)據(jù)。
- 對于一個正在做業(yè)務服務的庫说搅，這可不妙炸枣。你會看到，Buffer Pool 的內(nèi)存命中率急劇下降，磁盤壓力增加适肠，SQL 語句響應變慢霍衫。
- 所以，InnoDB 不能直接使用這個 LRU 算法侯养。實際上敦跌，InnoDB 對 LRU 算法做了改進
改進的LRU算法
- 在 InnoDB 實現(xiàn)上，按照 5:3 的比例把整個 LRU 鏈表分成了 young 區(qū)域和 old 區(qū)域逛揩。圖中 LRU_old 指向的就是 old 區(qū)域的第一個位置柠傍，是整個鏈表的 5/8 處。也就是說辩稽，靠近鏈表頭部的 5/8 是 young 區(qū)域惧笛，靠近鏈表尾部的 3/8 是 old 區(qū)域。
- 改進后的 LRU 算法執(zhí)行流程變成了下面這樣逞泄。
- 圖中狀態(tài) 1患整，要訪問數(shù)據(jù)頁 P3，由于 P3 在 young 區(qū)域炭懊，因此和優(yōu)化前的 LRU 算法一樣并级，將其移到鏈表頭部拂檩，變成狀態(tài) 2侮腹。
- 之后要訪問一個新的不存在于當前鏈表的數(shù)據(jù)頁，這時候依然是淘汰掉數(shù)據(jù)頁 Pm稻励，但是新插入的數(shù)據(jù)頁 Px父阻，是放在 LRU_old 處。
- 處于 old 區(qū)域的數(shù)據(jù)頁望抽，每次被訪問的時候都要做下面這個判斷：
- 若這個數(shù)據(jù)頁在 LRU 鏈表中存在的時間超過了 1 秒加矛，就把它移動到鏈表頭部；
- 如果這個數(shù)據(jù)頁在 LRU 鏈表中存在的時間短于 1 秒煤篙，位置保持不變斟览。1 秒這個時間，是由參數(shù) innodb_old_blocks_time 控制的辑奈。其默認值是 1000苛茂，單位毫秒。
- 這個策略鸠窗，就是為了處理類似全表掃描的操作量身定制的妓羊。還是以剛剛的掃描 200G 的歷史數(shù)據(jù)表為例，我們看看改進后的 LRU 算法的操作邏輯：
- 掃描過程中稍计，需要新插入的數(shù)據(jù)頁躁绸，都被放到 old 區(qū)域 ;
一個數(shù)據(jù)頁里面有多條記錄，這個數(shù)據(jù)頁會被多次訪問到，但由于是順序掃描净刮， - 這個數(shù)據(jù)頁第一次被訪問和最后一次被訪問的時間間隔不會超過 1 秒剥哑，因此還是會被保留在 old 區(qū)域；
- 再繼續(xù)掃描后續(xù)的數(shù)據(jù)淹父，之前的這個數(shù)據(jù)頁之后也不會再被訪問到星持，于是始終沒有機會移到鏈表頭部（也就是 young 區(qū)域），很快就會被淘汰出去弹灭。
- 掃描過程中稍计，需要新插入的數(shù)據(jù)頁躁绸，都被放到 old 區(qū)域 ;
- 可以看到督暂，這個策略最大的收益，就是在掃描這個大表的過程中穷吮，雖然也用到了 Buffer Pool逻翁，但是對 young 區(qū)域完全沒有影響，從而保證了 Buffer Pool 響應正常業(yè)務的查詢命中率捡鱼。
小結
- 本文介紹了 MySQL 的查詢結果八回，發(fā)送給客戶端的過程。
- 由于 MySQL 采用的是邊算邊發(fā)的邏輯驾诈，因此對于數(shù)據(jù)量很大的查詢結果來說缠诅，不會在 server 端保存完整的結果集。所以乍迄，如果客戶端讀結果不及時管引，會堵住 MySQL 的查詢過程，但是不會把內(nèi)存打爆闯两。
- 而對于 InnoDB 引擎內(nèi)部褥伴，由于有淘汰策略，大查詢也不會導致內(nèi)存暴漲漾狼。并且重慢，由于 InnoDB 對 LRU 算法做了改進，冷數(shù)據(jù)的全表掃描逊躁，對 Buffer Pool 的影響也能做到可控似踱。
- 當然，我們前面文章有說過稽煤，全表掃描還是比較耗費 IO 資源的核芽，所以業(yè)務高峰期還是不能直接在線上主庫執(zhí)行全表掃描的。
