一線大廠為什么面試必問分布式？
在一次又一次的失敗中,我總結了這份萬字的《MySQL性能調優筆記》
并發編程詳解:十三個工具類,十大設計模式,從理論基礎到案例實戰
我經常會被問到這樣一個問題：我的主機內存只有 100G，現在要對一個 200G 的大表做全表掃描，會不會把數據庫主機的內存用光了？
這個問題確實值得擔心，被系統 OOM（out of ）可不是鬧著玩的。但是，反過來想想，邏輯備份的時候，可不就是做整庫掃描嗎？如果這樣就會把內存吃光，邏輯備份不是早就掛了？
所以說，對大表做全表掃描，看來應該是沒問題的。但是，這個流程到底是怎么樣的呢？
全表掃描對層的影響
假設，我們現在要對一個 200G 的 表 db1. t，執行一個全表掃描。當然，你要把掃描結果保存在客戶端，會使用類似這樣的命令：
1mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file
你已經知道了， 的數據是保存在主鍵索引上的，所以全表掃描實際上是直接掃描表t 的主鍵索引。這條查詢語句由于沒有其他的判斷條件，所以查到的每一行都可以直接放到結果集里面，然后返回給客戶端。
那么，這個“結果集”存在哪里呢？
實際上，服務端并不需要保存一個完整的結果集。取數據和發數據的流程是這樣的：
獲取一行，寫到 中。這塊內存的大小是由參數 定義的，默認是 16k。重復獲取行，直到 寫滿，調用網絡接口發出去。如果發送成功，就清空 ，然后繼續取下一行，并寫入 。如果發送函數返回 或 ，就表示本地網絡棧（ ）寫滿了，進入等待。直到網絡棧重新可寫，再繼續發送。
這個過程對應的流程圖如下所示。
查詢結果發送流程
從這個流程中，你可以看到：
一個查詢在發送過程中，占用的 MySQL 內部的內存最大就是 這么大，并不會達到 200G； send 也不可能達到 200G（默認定義/proc/sys/net/core/），如果 send 被寫滿，就會暫停讀數據的流程。
也就是說，MySQL 是“邊讀邊發的”，這個概念很重要。這就意味著，如果客戶端接收得慢，會導致 MySQL 服務端由于結果發不出去，這個事務的執行時間變長。
比如下面這個狀態，就是我故意讓客戶端不去讀 中的內容，然后在服務端 show 看到的結果。
服務端發送阻塞
如果你看到 State 的值一直處于“ to ”，就表示服務器端的網絡棧寫滿了。
之前曾經講過，如果客戶端使用–quick 參數，會使用 方法。這個方法是讀一行處理一行。你可以想象一下，假設有一個業務的邏輯比較復雜，每讀一行數據以后要處理的邏輯如果很慢，就會導致客戶端要過很久才會去取下一行數據，可能就會出現如圖 2 所示的這種情況。
因此，對于正常的線上業務來說，如果一個查詢的返回結果不會很多的話，我都建議你使用 這個接口，直接把查詢結果保存到本地內存。
當然前提是查詢返回結果不多。之前的評論區，有同學說到自己因為執行了一個大查詢導致客戶端占用內存近 20G，這種情況下就需要改用 接口了。
另一方面，如果你在自己負責維護的 MySQL 里看到很多個線程都處于“ ”這個狀態，就意味著你要讓業務開發同學優化查詢結果，并評估這么多的返回結果是否合理。
而如果要快速減少處于這個狀態的線程的話，將 參數設置為一個更大的值是一個可選方案。
與“ to ”長相很類似的一個狀態是“ data”，這是一個經常被誤會的問題。有同學問我說，在自己維護的實例上看到很多查詢語句的狀態是“”，但查看網絡也沒什么問題啊，為什么 data 要這么久？
實際上，一個查詢語句的狀態變化是這樣的（注意：這里，我略去了其他無關的狀態）：
MySQL 查詢語句進入執行階段后，首先把狀態設置成“ data”；然后，發送執行結果的列相關的信息（meta data) 給客戶端；再繼續執行語句的流程；執行完成后，把狀態設置成空字符串。
也就是說，“ data”并不一定是指“正在發送數據”，而可能是處于執行器過程中的任意階段。比如，你可以構造一個鎖等待的場景，就能看到 data 狀態。
讀全表被鎖
data 狀態
可以看到， B 明顯是在等鎖，狀態顯示為 data。
也就是說，僅當一個線程處于“等待客戶端接收結果”的狀態，才會顯示" "；而如果顯示成“ data”，它的意思只是“正在執行”。
現在你知道了，查詢的結果是分段發給客戶端的，因此掃描全表，查詢返回大量的數據，并不會把內存打爆。
在 層的處理邏輯我們都清楚了，在 引擎里面又是怎么處理的呢？ 掃描全表會不會對引擎系統造成影響呢？
全表掃描對的影響
之前在介紹 WAL 機制的時候，和你分析了 內存的一個作用，是保存更新的結果，再配合 redo log，就避免了隨機寫盤。
內存的數據頁是在 Pool (BP) 中管理的，在 WAL 里 Pool 起到了加速更新的作用。而實際上， Pool 還有一個更重要的作用，就是加速查詢。
之前的評論區有同學問道，由于有 WAL 機制，當事務提交的時候，磁盤上的數據頁是舊的，那如果這時候馬上有一個查詢要來讀這個數據頁，是不是要馬上把 redo log應用到數據頁呢？
答案是不需要。因為這時候內存數據頁的結果是最新的，直接讀內存頁就可以了。你看，這時候查詢根本不需要讀磁盤，直接從內存拿結果，速度是很快的。所以說， Pool 還有加速查詢的作用。
而 Pool 對查詢的加速效果，依賴于一個重要的指標，即：內存命中率。
你可以在 show 結果中，查看一個系統當前的 BP 命中率。一般情況下，一個穩定服務的線上系統，要保證響應時間符合要求的話，內存命中率要在 99% 以上。
執行 show ，可以看到“ pool hit rate”字樣，顯示的就是當前的命中率。比如圖 5 這個命中率，就是 99.0%。
show 顯示內存命中率
如果所有查詢需要的數據頁都能夠直接從內存得到，那是最好的，對應的命中率就是100%。但，這在實際生產上是很難做到的。
Pool 的大小是由參數 ize 確定的，一般建議設置成可用物理內存的 60%~80%。
在大約十年前，單機的數據量是上百個 G，而物理內存是幾個 G；現在雖然很多服務器都能有 128G 甚至更高的內存，但是單機的數據量卻達到了 T 級別。
所以，ize 小于磁盤的數據量是很常見的。如果一個 Pool 滿了，而又要從磁盤讀入一個數據頁，那肯定是要淘汰一個舊數據頁的。
內存管理用的是最近最少使用 (Least Used, LRU) 算法，這個算法的核心就是淘汰最久未使用的數據。
下圖是一個 LRU 算法的基本模型。
基本 LRU 算法
管理 Pool 的 LRU 算法，是用鏈表來實現的。
在圖 6 的狀態 1 里，鏈表頭部是 P1，表示 P1 是最近剛剛被訪問過的數據頁；假設內存里只能放下這么多數據頁；這時候有一個讀請求訪問 P3，因此變成狀態 2，P3 被移到最前面；狀態 3 表示，這次訪問的數據頁是不存在于鏈表中的，所以需要在 Pool 中新申請一個數據頁 Px，加到鏈表頭部。但是由于內存已經滿了，不能申請新的內存。于是，會清空鏈表末尾 Pm 這個數據頁的內存，存入 Px 的內容，然后放到鏈表頭部。從效果上看，就是最久沒有被訪問的數據頁 Pm，被淘汰了。
這個算法乍一看上去沒什么問題，但是如果考慮到要做一個全表掃描，會不會有問題呢？
假設按照這個算法，我們要掃描一個 200G 的表，而這個表是一個歷史數據表，平時沒有業務訪問它。
那么，按照這個算法掃描的話，就會把當前的 Pool 里的數據全部淘汰掉，存入掃描過程中訪問到的數據頁的內容。也就是說 Pool 里面主要放的是這個歷史數據表的數據。
對于一個正在做業務服務的庫，這可不妙。你會看到， Pool 的內存命中率急劇下降，磁盤壓力增加，SQL 語句響應變慢。
所以， 不能直接使用這個 LRU 算法。實際上， 對 LRU 算法做了改進。
改進的 LRU 算法
在 實現上，按照 5:3 的比例把整個 LRU 鏈表分成了 young 區域和 old 區域。圖中 指向的就是 old 區域的第一個位置，是整個鏈表的 5/8 處。也就是說，靠近鏈表頭部的 5/8 是 young 區域，靠近鏈表尾部的 3/8 是 old 區域。
改進后的 LRU 算法執行流程變成了下面這樣。
圖 7 中狀態 1，要訪問數據頁 P3，由于 P3 在 young 區域，因此和優化前的 LRU 算法一樣，將其移到鏈表頭部，變成狀態 2。之后要訪問一個新的不存在于當前鏈表的數據頁，這時候依然是淘汰掉數據頁 Pm，但是新插入的數據頁 Px，是放在 處。處于 old 區域的數據頁，每次被訪問的時候都要做下面這個判斷：
這個策略，就是為了處理類似全表掃描的操作量身定制的。還是以剛剛的掃描 200G 的歷史數據表為例，我們看看改進后的 LRU 算法的操作邏輯：
掃描過程中，需要新插入的數據頁，都被放到 old 區域 ;一個數據頁里面有多條記錄，這個數據頁會被多次訪問到，但由于是順序掃描，這個數據頁第一次被訪問和最后一次被訪問的時間間隔不會超過 1 秒，因此還是會被保留在old 區域；再繼續掃描后續的數據，之前的這個數據頁之后也不會再被訪問到，于是始終沒有機會移到鏈表頭部（也就是 young 區域），很快就會被淘汰出去。
可以看到，這個策略最大的收益復雜查詢 內存數據庫，就是在掃描這個大表的過程中，雖然也用到了 ，但是對 young 區域完全沒有影響復雜查詢 內存數據庫，從而保證了 Pool 響應正常業務的查詢命中率。
小結
今天，我用“大查詢會不會把內存用光”這個問題，和你介紹了 MySQL 的查詢結果，發送給客戶端的過程。
由于 MySQL 采用的是邊算邊發的邏輯，因此對于數據量很大的查詢結果來說，不會在 端保存完整的結果集。所以，如果客戶端讀結果不及時，會堵住 MySQL 的查詢過程，但是不會把內存打爆。
而對于 引擎內部，由于有淘汰策略，大查詢也不會導致內存暴漲。并且，由于 對 LRU 算法做了改進，冷數據的全表掃描，對 Pool 的影響也能做到可控。