共計(jì) 3840 個(gè)字符，預(yù)計(jì)需要花費(fèi) 10 分鐘才能閱讀完成。
這篇文章主要介紹“MySQL 數(shù)據(jù)查詢太多會(huì)怎么樣”的相關(guān)知識(shí)，丸趣 TV 小編通過(guò)實(shí)際案例向大家展示操作過(guò)程，操作方法簡(jiǎn)單快捷，實(shí)用性強(qiáng)，希望這篇“MySQL 數(shù)據(jù)查詢太多會(huì)怎么樣”文章能幫助大家解決問(wèn)題。
主機(jī)內(nèi)存只有 100G，現(xiàn)在要全表掃描一個(gè) 200G 大表，會(huì)不會(huì)把 DB 主機(jī)的內(nèi)存用光？
邏輯備份時(shí)，可不就是做整庫(kù)掃描嗎？若這樣就會(huì)把內(nèi)存吃光，邏輯備份不是早就掛了？
所以大表全表掃描，看起來(lái)應(yīng)該沒(méi)問(wèn)題。這是為啥呢？
全表掃描對(duì) server 層的影響
假設(shè)，我們現(xiàn)在要對(duì)一個(gè) 200G 的 InnoDB 表 db1. t，執(zhí)行一個(gè)全表掃描。當(dāng)然，你要把掃描結(jié)果保存在客戶端，會(huì)使用類似這樣的命令：
mysql -h$host -P$port -u$user -p$pwd -e
select * from db1.t $target_fileInnoDB 數(shù)據(jù)保存在主鍵索引上，所以全表掃描實(shí)際上是直接掃描表 t 的主鍵索引。這條查詢語(yǔ)句由于沒(méi)有其他判斷條件，所以查到的每一行都可以直接放到結(jié)果集，然后返回給客戶端。
那么，這個(gè)“結(jié)果集”存在哪里呢？
服務(wù)端無(wú)需保存一個(gè)完整結(jié)果集。取數(shù)據(jù)和發(fā)數(shù)據(jù)的流程是這樣的：
獲取一行，寫(xiě)到 **「net_buffer」。這塊內(nèi)存的大小是由參數(shù)「net_buffer_length」** 定義，默認(rèn) 16k
重復(fù)獲取行，直到 **「net_buffer」** 寫(xiě)滿，調(diào)用網(wǎng)絡(luò)接口發(fā)出去
若發(fā)送成功，就清空 **「net_buffer」，然后繼續(xù)取下一行，并寫(xiě)入「net_buffer」**
若發(fā)送函數(shù)返回 **「EAGAIN」或「WSAEWOULDBLOCK」**，就表示本地網(wǎng)絡(luò)棧（socket send buffer）寫(xiě)滿了，進(jìn)入等待。直到網(wǎng)絡(luò)棧重新可寫(xiě)，再繼續(xù)發(fā)送
查詢結(jié)果發(fā)送流程
可見(jiàn)：
一個(gè)查詢?cè)诎l(fā)送過(guò)程中，占用的 MySQL 內(nèi)部的內(nèi)存最大就是 **「net_buffer_length」** 這么大，不會(huì)達(dá)到 200G
socket send buffer 也不可能達(dá)到 200G（默認(rèn)定義 /proc/sys/net/core/wmem_default），若 socket send buffer 被寫(xiě)滿，就會(huì)暫停讀數(shù)據(jù)的流程
所以 MySQL 其實(shí)是“邊讀邊發(fā)”。這意味著，若客戶端接收得慢，會(huì)導(dǎo)致 MySQL 服務(wù)端由于結(jié)果發(fā)不出去，這個(gè)事務(wù)的執(zhí)行時(shí)間變長(zhǎng)。
比如下面這個(gè)狀態(tài)，就是當(dāng)客戶端不讀 **「socket receive buffer」** 內(nèi)容時(shí)，在服務(wù)端 show processlist 看到的結(jié)果。
服務(wù)端發(fā)送阻塞
若看到 State 一直是“Sending to client”，說(shuō)明服務(wù)器端的網(wǎng)絡(luò)棧寫(xiě)滿了。
若客戶端使用–quick 參數(shù)，會(huì)使用 mysql_use_result 方法：讀一行處理一行。假設(shè)某業(yè)務(wù)的邏輯較復(fù)雜，每讀一行數(shù)據(jù)以后要處理的邏輯若很慢，就會(huì)導(dǎo)致客戶端要過(guò)很久才取下一行數(shù)據(jù)，可能就會(huì)出現(xiàn)上圖結(jié)果。
因此，對(duì)于正常的線上業(yè)務(wù)來(lái)說(shuō)，若一個(gè)查詢的返回結(jié)果不多，推薦使用 **「mysql_store_result」** 接口，直接把查詢結(jié)果保存到本地內(nèi)存。
當(dāng)然前提是查詢返回結(jié)果不多。如果太多，因?yàn)閳?zhí)行了一個(gè)大查詢導(dǎo)致客戶端占用內(nèi)存近 20G，這種情況下就需要改用 **「mysql_use_result」** 接口。
若你在自己負(fù)責(zé)維護(hù)的 MySQL 里看到很多個(gè)線程都處于“Sending to client”，表明你要讓業(yè)務(wù)開(kāi)發(fā)同學(xué)優(yōu)化查詢結(jié)果，并評(píng)估這么多的返回結(jié)果是否合理。
若要快速減少處于這個(gè)狀態(tài)的線程的話，可以將 **「net_buffer_length」** 設(shè)置更大。
有時(shí)，實(shí)例上看到很多查詢語(yǔ)句狀態(tài)是“Sending data”，但查看網(wǎng)絡(luò)也沒(méi)什么問(wèn)題，為什么 Sending data 要這么久？
一個(gè)查詢語(yǔ)句的狀態(tài)變化是這樣的：
MySQL 查詢語(yǔ)句進(jìn)入執(zhí)行階段后，先把狀態(tài)設(shè)置成「Sending data」
然后，發(fā)送執(zhí)行結(jié)果的列相關(guān)的信息（meta data) 給客戶端
再繼續(xù)執(zhí)行語(yǔ)句的流程
執(zhí)行完成后，把狀態(tài)設(shè)置成空字符串。
即“Sending data”并不一定是指“正在發(fā)送數(shù)據(jù)”，而可能是處于執(zhí)行器過(guò)程中的任意階段。比如，你可以構(gòu)造一個(gè)鎖等待場(chǎng)景，就能看到 Sending data 狀態(tài)。
讀全表被鎖：
Sending data 狀態(tài)
可見(jiàn) session2 是在等鎖，狀態(tài)顯示為 Sending data。
僅當(dāng)一個(gè)線程處于“等待客戶端接收結(jié)果”的狀態(tài)，才會(huì)顯示 Sending to client
若顯示成“Sending data”，它的意思只是“正在執(zhí)行”
所以，查詢的結(jié)果是分段發(fā)給客戶端，因此掃描全表，查詢返回大量數(shù)據(jù)，并不會(huì)把內(nèi)存打爆。
以上是 server 層的處理邏輯，在 InnoDB 引擎里又是怎么處理？
全表掃描對(duì) InnoDB 的影響
InnoDB 內(nèi)存的一個(gè)作用，是保存更新的結(jié)果，再配合 redo log，避免隨機(jī)寫(xiě)盤(pán)。
內(nèi)存的數(shù)據(jù)頁(yè)是在 Buffer Pool (簡(jiǎn)稱為 BP) 管理，在 WAL 里 BP 起加速更新的作用。
BP 還能加速查詢。
由于 WAL，當(dāng)事務(wù)提交時(shí)，磁盤(pán)上的數(shù)據(jù)頁(yè)是舊的，若這時(shí)馬上有個(gè)查詢來(lái)讀該數(shù)據(jù)頁(yè)，是不是要馬上把 redo log 應(yīng)用到數(shù)據(jù)頁(yè)？
不需要。因?yàn)榇藭r(shí)，內(nèi)存數(shù)據(jù)頁(yè)的結(jié)果是最新的，直接讀內(nèi)存頁(yè)即可。這時(shí)查詢無(wú)需讀磁盤(pán)，直接從內(nèi)存取結(jié)果，速度很快。所以，Buffer Pool 能加速查詢。
而 BP 對(duì)查詢的加速效果，依賴于一個(gè)重要的指標(biāo)，即：內(nèi)存命中率。
可以在 show engine innodb status 結(jié)果中，查看一個(gè)系統(tǒng)當(dāng)前的 BP 命中率。一般情況下，一個(gè)穩(wěn)定服務(wù)的線上系統(tǒng)，要保證響應(yīng)時(shí)間符合要求的話，內(nèi)存命中率要在 99% 以上。
執(zhí)行 show engine innodb status，可以看到“Buffer pool hit rate”字樣，顯示的就是當(dāng)前的命中率。比如下圖命中率，就是 100%。
若所有查詢需要的數(shù)據(jù)頁(yè)都能夠直接從內(nèi)存得到，那是最好的，對(duì)應(yīng)命中率 100%。
InnoDB Buffer Pool 的大小是由參數(shù) **「innodb_buffer_pool_size」** 確定，一般建議設(shè)置成可用物理內(nèi)存的 60%~80%。
在大約十年前，單機(jī)的數(shù)據(jù)量是上百個(gè) G，而物理內(nèi)存是幾個(gè) G；現(xiàn)在雖然很多服務(wù)器都能有 128G 甚至更高的內(nèi)存，但是單機(jī)的數(shù)據(jù)量卻達(dá)到了 T 級(jí)別。
所以，**「innodb_buffer_pool_size」** 小于磁盤(pán)數(shù)據(jù)量很常見(jiàn)。若一個(gè) Buffer Pool 滿了，而又要從磁盤(pán)讀入一個(gè)數(shù)據(jù)頁(yè)，那肯定是要淘汰一個(gè)舊數(shù)據(jù)頁(yè)的。
InnoDB 內(nèi)存管理
使用的最近最少使用 (Least Recently Used, LRU) 算法，淘汰最久未使用數(shù)據(jù)。
基本 LRU 算法
InnoDB 管理 BP 的 LRU 算法，是用鏈表實(shí)現(xiàn)的：
state1，鏈表頭部是 P1，表示 P1 是最近剛被訪問(wèn)過(guò)的數(shù)據(jù)頁(yè)
此時(shí)，一個(gè)讀請(qǐng)求訪問(wèn) P3，因此變成狀態(tài) 2，P3 被移到最前
狀態(tài) 3 表示，這次訪問(wèn)的數(shù)據(jù)頁(yè)不存在于鏈表，所以需要在 BP 中新申請(qǐng)一個(gè)數(shù)據(jù)頁(yè) Px，加到鏈表頭。但由于內(nèi)存已滿，不能申請(qǐng)新內(nèi)存。于是清空鏈表末尾 Pm 數(shù)據(jù)頁(yè)內(nèi)存，存入 Px 的內(nèi)容，放到鏈表頭部
最終就是最久沒(méi)有被訪問(wèn)的數(shù)據(jù)頁(yè) Pm 被淘汰。
若此時(shí)要做一個(gè)全表掃描，會(huì)咋樣？若要掃描一個(gè) 200G 的表，而這個(gè)表是一個(gè)歷史數(shù)據(jù)表，平時(shí)沒(méi)有業(yè)務(wù)訪問(wèn)它。
那么，按此算法掃描，就會(huì)把當(dāng)前 BP 里的數(shù)據(jù)全部淘汰，存入掃描過(guò)程中訪問(wèn)到的數(shù)據(jù)頁(yè)的內(nèi)容。也就是說(shuō) BP 里主要放的是這個(gè)歷史數(shù)據(jù)表的數(shù)據(jù)。
對(duì)于一個(gè)正在做業(yè)務(wù)服務(wù)的庫(kù)，這可不行呀。你會(huì)看到，BP 內(nèi)存命中率急劇下降，磁盤(pán)壓力增加，SQL 語(yǔ)句響應(yīng)變慢。
所以，InnoDB 不能直接使用原始的 LRU。InnoDB 對(duì)其進(jìn)行了優(yōu)化。
改進(jìn)的 LRU 算法
InnoDB 按 5:3 比例把鏈表分成 New 區(qū)和 Old 區(qū)。圖中 LRU_old 指向的就是 old 區(qū)域的第一個(gè)位置，是整個(gè)鏈表的 5 / 8 處。即靠近鏈表頭部的 5 / 8 是 New 區(qū)域，靠近鏈表尾部的 3 / 8 是 old 區(qū)域。
改進(jìn)后的 LRU 算法執(zhí)行流程：
狀態(tài) 1，要訪問(wèn) P3，由于 P3 在 New 區(qū)，和優(yōu)化前 LRU 一樣，將其移到鏈表頭部 =》狀態(tài) 2
之后要訪問(wèn)一個(gè)新的不存在于當(dāng)前鏈表的數(shù)據(jù)頁(yè)，這時(shí)依然是淘汰掉數(shù)據(jù)頁(yè) Pm，但新插入的數(shù)據(jù)頁(yè) Px，是放在 **「LRU_old」** 處
處于 old 區(qū)的數(shù)據(jù)頁(yè)，每次被訪問(wèn)的時(shí)候都要做如下判斷：
若該數(shù)據(jù)頁(yè)在 LRU 鏈表中存在的時(shí)間超過(guò) 1s，就把它移動(dòng)到鏈表頭部
若該數(shù)據(jù)頁(yè)在 LRU 鏈表中存在的時(shí)間短于 1s，位置保持不變。1s 是由參數(shù) **「innodb_old_blocks_time」** 控制，默認(rèn)值 1000，單位 ms。
該策略，就是為了處理類似全表掃描的操作量身定制。還是掃描 200G 歷史數(shù)據(jù)表：
4. 掃描過(guò)程中，需要新插入的數(shù)據(jù)頁(yè)，都被放到 old 區(qū)域
5. 一個(gè)數(shù)據(jù)頁(yè)里面有多條記錄，這個(gè)數(shù)據(jù)頁(yè)會(huì)被多次訪問(wèn)到，但由于是順序掃描，這個(gè)數(shù)據(jù)頁(yè)第一次被訪問(wèn)和最后一次被訪問(wèn)的時(shí)間間隔不會(huì)超過(guò) 1 秒，因此還是會(huì)被保留在 old 區(qū)域
6. 再繼續(xù)掃描后續(xù)的數(shù)據(jù)，之前的這個(gè)數(shù)據(jù)頁(yè)之后也不會(huì)再被訪問(wèn)到，于是始終沒(méi)有機(jī)會(huì)移到鏈表頭部（New 區(qū)），很快就會(huì)被淘汰出去。
可以看到，這個(gè)策略最大的收益，就是在掃描這個(gè)大表的過(guò)程中，雖然也用到了 BP，但對(duì) young 區(qū)完全沒(méi)有影響，從而保證了 Buffer Pool 響應(yīng)正常業(yè)務(wù)的查詢命中率。
關(guān)于“MySQL 數(shù)據(jù)查詢太多會(huì)怎么樣”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)，可以關(guān)注丸趣 TV 行業(yè)資訊頻道，丸趣 TV 小編每天都會(huì)為大家更新不同的知識(shí)點(diǎn)。
