這篇文章主要介紹“MySQL數據查詢太多會怎么樣”的相關知識，小編通過實際案例向大家展示操作過程，操作方法簡單快捷，實用性強，希望這篇“MySQL數據查詢太多會怎么樣”文章能幫助大家解決問題。

10年積累的網站設計制作、成都網站設計經驗，可以快速應對客戶對網站的新想法和需求。提供各種問題對應的解決方案。讓選擇我們的客戶得到更好、更有力的網絡服務。我雖然不認識你，你也不認識我。但先網站設計制作后付款的網站建設流程，更有曹縣免費網站建設讓你可以放心的選擇與我們合作。

主機內存只有100G，現在要全表掃描一個200G大表，會不會把DB主機的內存用光？

邏輯備份時，可不就是做整庫掃描嗎？若這樣就會把內存吃光，邏輯備份不是早就掛了？

所以大表全表掃描，看起來應該沒問題。這是為啥呢？

全表掃描對server層的影響

假設，我們現在要對一個200G的InnoDB表db1. t，執(zhí)行一個全表掃描。當然，你要把掃描結果保存在客戶端，會使用類似這樣的命令：

mysql -h$host -P$port -u$user -p$pwd -e 
 "select * from db1.t" > $target_file

InnoDB數據保存在主鍵索引上，所以全表掃描實際上是直接掃描表t的主鍵索引。這條查詢語句由于沒有其他判斷條件，所以查到的每一行都可以直接放到結果集，然后返回給客戶端。

那么，這個“結果集”存在哪里呢？

服務端無需保存一個完整結果集。取數據和發(fā)數據的流程是這樣的：

獲取一行，寫到**「net_buffer」。這塊內存的大小是由參數「net_buffer_length」**定義，默認16k

重復獲取行，直到**「net_buffer」**寫滿，調用網絡接口發(fā)出去

若發(fā)送成功，就清空**「net_buffer」，然后繼續(xù)取下一行，并寫入「net_buffer」**

若發(fā)送函數返回**「EAGAIN」或「WSAEWOULDBLOCK」**，就表示本地網絡棧（socket send buffer）寫滿了，進入等待。直到網絡棧重新可寫，再繼續(xù)發(fā)送

查詢結果發(fā)送流程

可見：

• 一個查詢在發(fā)送過程中，占用的MySQL內部的內存最大就是**「net_buffer_length」**這么大，不會達到200G

• socket send buffer 也不可能達到200G（默認定義/proc/sys/net/core/wmem_default），若socket send buffer被寫滿，就會暫停讀數據的流程

所以MySQL其實是“邊讀邊發(fā)”。這意味著，若客戶端接收得慢，會導致MySQL服務端由于結果發(fā)不出去，這個事務的執(zhí)行時間變長。

比如下面這個狀態(tài)，就是當客戶端不讀**「socket receive buffer」**內容時，在服務端show processlist看到的結果。

服務端發(fā)送阻塞

若看到State一直是“Sending to client”，說明服務器端的網絡棧寫滿了。

若客戶端使用–quick參數，會使用mysql_use_result方法：讀一行處理一行。假設某業(yè)務的邏輯較復雜，每讀一行數據以后要處理的邏輯若很慢，就會導致客戶端要過很久才取下一行數據，可能就會出現上圖結果。

因此，對于正常的線上業(yè)務來說，若一個查詢的返回結果不多，推薦使用**「mysql_store_result」**接口，直接把查詢結果保存到本地內存。

當然前提是查詢返回結果不多。如果太多，因為執(zhí)行了一個大查詢導致客戶端占用內存近20G，這種情況下就需要改用**「mysql_use_result」**接口。

若你在自己負責維護的MySQL里看到很多個線程都處于“Sending to client”，表明你要讓業(yè)務開發(fā)同學優(yōu)化查詢結果，并評估這么多的返回結果是否合理。

若要快速減少處于這個狀態(tài)的線程的話，可以將**「net_buffer_length」**設置更大。

有時，實例上看到很多查詢語句狀態(tài)是“Sending data”，但查看網絡也沒什么問題，為什么Sending data要這么久？

一個查詢語句的狀態(tài)變化是這樣的：

• MySQL查詢語句進入執(zhí)行階段后，先把狀態(tài)設置成 「Sending data」

• 然后，發(fā)送執(zhí)行結果的列相關的信息（meta data) 給客戶端

• 再繼續(xù)執(zhí)行語句的流程

• 執(zhí)行完成后，把狀態(tài)設置成空字符串。

即“Sending data”并不一定是指“正在發(fā)送數據”，而可能是處于執(zhí)行器過程中的任意階段。比如，你可以構造一個鎖等待場景，就能看到Sending data狀態(tài)。

讀全表被鎖：

Sending data狀態(tài)

可見session2是在等鎖，狀態(tài)顯示為Sending data。

• 僅當一個線程處于“等待客戶端接收結果”的狀態(tài)，才會顯示"Sending to client"

• 若顯示成“Sending data”，它的意思只是“正在執(zhí)行”

所以，查詢的結果是分段發(fā)給客戶端，因此掃描全表，查詢返回大量數據，并不會把內存打爆。

以上是server層的處理邏輯，在InnoDB引擎里又是怎么處理？

全表掃描對InnoDB的影響

InnoDB內存的一個作用，是保存更新的結果，再配合redo log，避免隨機寫盤。

內存的數據頁是在Buffer Pool (簡稱為BP)管理，在WAL里BP起加速更新的作用。

BP還能加速查詢。

由于WAL，當事務提交時，磁盤上的數據頁是舊的，若這時馬上有個查詢來讀該數據頁，是不是要馬上把redo log應用到數據頁？

不需要。因為此時，內存數據頁的結果是最新的，直接讀內存頁即可。這時查詢無需讀磁盤，直接從內存取結果，速度很快。所以，Buffer Pool能加速查詢。

而BP對查詢的加速效果，依賴于一個重要的指標，即：內存命中率。

可以在show engine innodb status結果中，查看一個系統(tǒng)當前的BP命中率。一般情況下，一個穩(wěn)定服務的線上系統(tǒng)，要保證響應時間符合要求的話，內存命中率要在99%以上。

執(zhí)行show engine innodb status ，可以看到“Buffer pool hit rate”字樣，顯示的就是當前的命中率。比如下圖命中率，就是100%。

若所有查詢需要的數據頁都能夠直接從內存得到，那是最好的，對應命中率100%。

InnoDB Buffer Pool的大小是由參數 **「innodb_buffer_pool_size」**確定，一般建議設置成可用物理內存的60%~80%。

在大約十年前，單機的數據量是上百個G，而物理內存是幾個G；現在雖然很多服務器都能有128G甚至更高的內存，但是單機的數據量卻達到了T級別。

所以，**「innodb_buffer_pool_size」**小于磁盤數據量很常見。若一個 Buffer Pool滿了，而又要從磁盤讀入一個數據頁，那肯定是要淘汰一個舊數據頁的。

InnoDB內存管理

使用的最近最少使用 (Least Recently Used, LRU)算法，淘汰最久未使用數據。

基本LRU算法

InnoDB管理BP的LRU算法，是用鏈表實現的：

• state1，鏈表頭部是P1，表示P1是最近剛被訪問過的數據頁

• 此時，一個讀請求訪問P3，因此變成狀態(tài)2，P3被移到最前

• 狀態(tài)3表示，這次訪問的數據頁不存在于鏈表，所以需要在BP中新申請一個數據頁Px，加到鏈表頭。但由于內存已滿，不能申請新內存。于是清空鏈表末尾Pm數據頁內存，存入Px的內容，放到鏈表頭部

最終就是最久沒有被訪問的數據頁Pm被淘汰。

若此時要做一個全表掃描，會咋樣？若要掃描一個200G的表，而這個表是一個歷史數據表，平時沒有業(yè)務訪問它。

那么，按此算法掃描，就會把當前BP里的數據全部淘汰，存入掃描過程中訪問到的數據頁的內容。也就是說BP里主要放的是這個歷史數據表的數據。

對于一個正在做業(yè)務服務的庫，這可不行呀。你會看到，BP內存命中率急劇下降，磁盤壓力增加，SQL語句響應變慢。

所以，InnoDB不能直接使用原始的LRU。InnoDB對其進行了優(yōu)化。

改進的LRU算法

InnoDB按5:3比例把鏈表分成New區(qū)和Old區(qū)。圖中LRU_old指向的就是old區(qū)域的第一個位置，是整個鏈表的5/8處。即靠近鏈表頭部的5/8是New區(qū)域，靠近鏈表尾部的3/8是old區(qū)域。

改進后的LRU算法執(zhí)行流程：

狀態(tài)1，要訪問P3，由于P3在New區(qū)，和優(yōu)化前LRU一樣，將其移到鏈表頭部 =》狀態(tài)2

之后要訪問一個新的不存在于當前鏈表的數據頁，這時依然是淘汰掉數據頁Pm，但新插入的數據頁Px，是放在**「LRU_old」**處

處于old區(qū)的數據頁，每次被訪問的時候都要做如下判斷：

若該數據頁在LRU鏈表中存在的時間超過1s，就把它移動到鏈表頭部

若該數據頁在LRU鏈表中存在的時間短于1s，位置保持不變。1s是由參數**「innodb_old_blocks_time」**控制，默認值1000，單位ms。

該策略，就是為了處理類似全表掃描的操作量身定制。還是掃描200G歷史數據表：

4. 掃描過程中，需要新插入的數據頁，都被放到old區(qū)域

5. 一個數據頁里面有多條記錄，這個數據頁會被多次訪問到，但由于是順序掃描，這個數據頁第一次被訪問和最后一次被訪問的時間間隔不會超過1秒，因此還是會被保留在old區(qū)域

6. 再繼續(xù)掃描后續(xù)的數據，之前的這個數據頁之后也不會再被訪問到，于是始終沒有機會移到鏈表頭部（New區(qū)），很快就會被淘汰出去。

可以看到，這個策略最大的收益，就是在掃描這個大表的過程中，雖然也用到了BP，但對young區(qū)完全沒有影響，從而保證了Buffer Pool響應正常業(yè)務的查詢命中率。

關于“MySQL數據查詢太多會怎么樣”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識，可以關注創(chuàng)新互聯行業(yè)資訊頻道，小編每天都會為大家更新不同的知識點。

網站標題：MySQL數據查詢太多會怎么樣
網頁網址：http://www.rwnh.cn/article14/igpoge.html

成都網站建設公司_創(chuàng)新互聯，為您提供網站收錄、外貿建站、網站策劃、營銷型網站建設、手機網站建設、電子商務

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯