MySQL JDBC StreamResult通信原理淺析

---------------------

作者：xieyu_zy

來源：CSDN

原文：MySQL JDBC StreamResult通信原理淺析

好幾年沒寫技術博客了，今天寫一個小的技術點給大家分享，關於MySQL JDBC StreamResult的原理分享，難度不大，就當程序員的閑聊。

如果使用MySQL JDBC讀取過比較大的數據（例如超過1GB），應該清楚在讀取的時候，很可能會Java堆內存溢出，我們的解決方案通常是使用useCursorFetch讀取或Stream讀取來處理。使用Stream讀取的方式通常的操作方式是在執行SQL前，設置FetchSize：statement.setFetchSize(Integer.MIN_VALUE)，同時確保遊標是隻讀、向前滾動的（為遊標的默認值），另一種做法是強制類型轉換為com.mysql.jdbc.StatementImpl，然後調用MySQL JDBC的內部方法：enableStreamingResults()，這兩者達到的效果是一致的，都是啟動Stream流方式讀取數據。也可以使用useCursorFetch方式，但是這種方式測試結果性能要比StreamResult慢很多，為什麼？在本文會闡述其大致的原理。

我在前面的部分文章和書籍中都有介紹過其MySQL JDBC在這一塊內部處理的代碼，按照默認JDBC讀取、useCursorFetch讀取和Stream讀取，其內部會分成3個不同的實現類來完成，不過我一直沒有去深究過資料庫和JDBC之間到底是如何通信的過程。有一段時間我一直認為這都屬於服務端行為或者是客戶端與服務端配合的行為，然後並不其然，今天給大家分享一下裡面到底是怎麼回事。

【先回顧一下簡單的通信】：

JDBC與資料庫之間的通信是通過Socket完成的，因此我們可以把資料庫當成一個SocketServer的提供方，因此當SocketServer返回數據的時候（類似於SQL結果集的返回）其流程是：服務端程序數據（資料庫） -> 內核Socket Buffer -> 網路 -> 客戶端Socket Buffer -> 客戶端程序（JDBC所在的JVM內存）

到目前為止，IT行業中大家所看到的JDBC無論是：MySQL JDBC、SQL Server JDBC、PG JDBC、Oracle JDBC。甚至於是NoSQL的Client：Redis Client、MongoDB Client，甚至於是SSH通信目前在Java中的實現做法也基本都是如此，也就是都是基於TCP通信的機制，它們的大致原理如下圖所示：

【方式1：直接使用MySQL JDBC默認參數讀取數據，為什麼會掛？】

（1）MySQL Server方在發起的SQL結果集會全部通過OutputStream向外輸出數據，也就是向本地的Kennel對應的socket buffer中寫入數據，這是一次內存拷貝（內存拷貝這個不是本文的重點）。

（2）此時Kennel的Buffer有數據的時候就會把數據通過TCP鏈路（JDBC主動發起的Socket鏈路），回傳數據，此時數據會回傳到JDBC所在機器上，會先進入Kennel Buffer區（注意，sendBuffer和reveiveBuffer在內核區域是兩個不同的Buffer，不同的socket相互不影響）。

（3）JDBC在發起SQL操作後，Java代碼是在inputStream.read()操作上阻塞，當緩衝區有數據的時候，就會被喚醒，然後將緩衝區的數據讀取到Java內存中，這是JDBC端的一次內存拷貝。

（4）接下來MySQL JDBC會不斷讀取緩衝區數據到Java內存中，MySQL Server會不斷發送數據。注意在數據沒有完全組裝完之前，客戶端發起的SQL操作不會響應，也就是給你的感覺MySQL服務端還沒響應，其實數據已經到本地，JDBC還沒對調用execute方法的地方返回結果集的第一條數據，而是不斷從緩衝器讀取數據。

（5）關鍵是這個傻帽就想一次性地把傳回來的數據讀取完，根本不管家裡放不放的下，整個表的內容讀取到Java內存中，如果表很大，自然而然地先是FULL GC，接下來就是內存溢出。

【方式2：JDBC參數上設置useCursorFetch=true可以解決問題】

這個方案配合FetchSize設置，確實可以解決問題，這個方案其實就是告訴MySQL服務端我要多少數據，每次要多少數據，通信過程有點像這樣：

這樣做就像我們生活中的那樣，我需要什麼就去超市買什麼，需要多少就去買多少。不過這種交互不像現在網購，坐在家裡就可以把東西送到家裡來，它一定要走路（網路鏈路），也就是需要網路的時間開銷，假如數據有1億數據，將FetchSize設置成1000的話，會進行10萬次來回通信；如果網路延遲同機房0.02ms，那麼10萬次通信會增加2秒的時間，不算大。那麼如果跨機房2ms的延遲時間會多出來200秒（也就是3分20秒），如果國內跨城市10~40ms延遲，那麼時間將會1000~4000秒，如果是跨國200~300ms呢？時間會多出十多個小時出來。

【PS：注意，這裡計算的延遲增加，僅僅計算了每一次發送請求的RT，其實每一次發送TCP報文發送後，TCP是要求返回ACK報文，來確保對應數據傳輸的可靠性，也就是發送請求就會有一個來回通信，而不是等到數據返回的時候纔有一次來回通信，真正數據返回的時候，還會有一次或多次來回通信】

在這裡的計算中，還沒有包含系統調用次數增加了很多，線程等待和喚醒的上下文次數變多，網路包重傳的情況對整體性能的影響，因此這種方案看似合理，但是性能確不怎麼樣。

另外，由於MySQL方不知道客戶端什麼時候將數據消費完，而自身的對應表可能會有DML寫入操作，此時MySQL需要建立一個臨時空間來存放需要拿走的數據。因此對於當你啟用useCursorFetch讀取大表的時候會看到MySQL上的幾個現象：

（1）IOPS飆升，因為存在大量的IO讀取和寫入，這個動作是正在準備要返回的數據到臨時空間中，此時監控MySQL的網路輸出是沒有變化的。由於IO寫入很大，如果是普通硬碟，此時可能會引起業務寫入的抖動

（2）磁碟空間飆升，這塊臨時空間可能比原表更大，如果這個表在整個庫內部佔用相當大的比重有可能會導致資料庫磁碟寫滿，空間會在結果集讀取完成後或者客戶端發起Result.close()時由MySQL去回收。

（3）CPU和內存會有一定比例的上升，根據CPU的能力決定。

（4）客戶端JDBC發起SQL後，長時間等待SQL響應數據，這段時間就是服務端在準備數據，這個等待與原始的JDBC不設置任何參數的方式也表現出等待，在內部原理上是不一樣的，前者是一直在讀取網路緩衝區的數據，沒有響應給業務，現在是MySQL資料庫在準備臨時數據空間，沒有響應給JDBC。

（5）在數據準備完成後，開始傳輸數據的階段，網路響應開始飆升，IOPS由「讀寫」轉變為「讀取」。

【userCursor原理說明】:

（1）在設置JDBC參數useCursorFetch=true後，通過Driver創建Connection的時候會自動將：detectServerPreparedStmts設置為true，這個對應JDBC參數是：useServerPrepStmts=true，也就是當設置useCursorFetch=true時useServerPrepStmt會被自動設置為true，源碼片段（ConnectionPropertiesImpl類的postInitialization()中，也就是連接初始化的時候會用的）：

內部多提供了另一個方法名，下面會提到：

（2）當執行SQL時，會調用到使用prepareStatment方法去執行（即使你自己用Statement內部也會轉換成PrepareStatemet，因為它要用服務端預編譯），代碼如下：

跟下代碼，這裡的userServerFetch()就是useCursorFetch參數的判定以及遊標類型和版本的判定，而遊標類型判定的就是為默認值。

（3）步驟1已提到detectServerPreparedStmts被設置為true，在prepareStatement的時候會選擇其ServerPreparedStatement作為實現類：具體請參考ConnectionImpl.prepareStatement(String , int , int)的代碼，代碼太長也不難，就不貼了。

（4）我要說的是ServerPreparedStatement在創建的時候，會在SQL發送前加一個指令在前面，讓伺服器端預編譯，這個指令就是1個int值：22（MysqlDefs.COM_PREPARE），如下：

（5）這裡僅僅是告知服務端預編譯SQL，還沒有指定遊標也在伺服器端，在真正發生execute、executeQuery，會調用到ServerPreparedStatement的serverExecute方法中。

（6）在步驟5描述的方法ServerPreparedStatement.serverExecute()方法中，會再一次判定useCursorFetch的判定，如果useCursorFetch成立，則在發送給服務端的package中，開啟遊標的指令：1（MysqlDefs.OPEN_CURSOR_FLAG），如下：

當開啟遊標的時候，服務端返回數據的時候，就會按照fetchSize的大小返回數據了，而客戶端接收數據的時候每次都會把換緩衝區數據全部讀取乾淨（可復用不開啟遊標方式的代碼）。

PS：關於PreparedStatement在MySQL JDBC當中是有潛在問題的，無論是否開啟服務端Prapare都有一些坑存在，這些我會在後續的一些文章當中逐步講到。

【方式3：Stream讀取數據】

方式1種默認參數讀取資料庫會導致Java掛掉，useCursorFetch通信效率較低，在資料庫端前期準備數據的時候IOPS會非常高，，客戶端響應也較慢，佔用大量的磁碟空間，我們接下來再看看Stream讀取方式。

前面提到當你使用statement.setFetchSize(Integer.MIN_VALUE)或com.mysql.jdbc.StatementImpl.enableStreamingResults()就可以開啟Stream讀取結果集的方式，在發起execute之前FetchSize不能再手工設置，且確保遊標是FORWARD_ONLY的。

這種方式很神奇，似乎內存也不掛了，響應也變快了，對MySQL的影響也變小了，至少IOPS不會那麼大了，磁碟佔用也沒有了。以前僅僅看到JDBC中走了單獨的代碼，認為這是MySQL和JDBC之間的另一種通信協議，殊不知，它竟然是「客戶端行為」，沒錯，你沒看錯，它就是客戶端行為。

它在發起enableStreamingResults()的時候，幾乎不會做任何與服務端的交互工作，也就是服務端依然會按照方式1回傳數據到JDBC的機器，那麼服務端使勁向緩衝區懟數據，客戶端是如何扛得住壓力的呢？

服務端準備好從第一條數據開始返回時，向緩衝區懟入數據，這些數據通過TCP鏈路，懟入客戶端機器的內核緩衝區，JDBC會的inputStream.read()方法會被喚醒去讀取數據，唯一的區別是開啟了stream讀取的時候，它每次只是從內核中讀取一些package大小的數據，更上層只是返回一行數據，如果1個package無法組裝1行數據，會再讀1個package。

對於業務程序來講，當第一行數據組裝好以後，程序就很快響應了，不過當應用程序在處理數據的過程中，消費速度一般來講不會比數據傳輸速度更快，所以客戶端機器的內核緩衝區就會被懟滿（僅僅是這個Socket的緩衝區），當服務端、客戶端兩邊的緩衝區都被懟滿後，MySQL通過Socket繼續write數據進去的時候，此時會被阻塞。這樣就像水管一樣，兩邊的蓄水池滿了，水管裡面的水也滿了，進水口就進不了水了，消費水的一方消費一部分，就可以再進一些水，這就是所謂的stream模式，也就是雙方會這樣達到一個平衡。

對於JDBC客戶端，數據獲取的時候每次都在本地的內核緩衝區當中，就在小區的快遞包裹箱拿回家一個距離，那麼自然比起每次去大超市的時間要少得多。另外，這個過程的數據包裹是準備好的，所以沒有I/O阻塞的過程（除非MySQL服務端傳遞的數據還不如消費端處理數據來得快，那一般也只有消費端不做任何業務邏輯，拿到數據直接放棄的測試代碼，才會發生這樣的事情，就像水廠的水在供應，每家每戶都把所有水管打開，而且不用來做任何事情的可能性幾乎為0）,參考水管的道理，這個時候不論：跨機房、跨地區、跨國家，只要服務端開始響應第一條數據，就會源源不斷地傳遞數據過來。

Stream讀取方式是不是就沒有問題了呢？肯定是有的，而且還不止一個兩個坑，這篇文章我沒法一一說清楚，也和每一個人所遇到的情況有所不同，也會遇到一些比較偏的問題和坑，在本文中主要針對對業務的影響程度來看：

【優缺點對比】：

【對業務的影響對比】：在MySQL 5.7下分別測試MyISAM、InnoDB兩種存儲引擎：

【理論上可以更進一步，只要你願意】

理論上這種方式是比較好的了，但是就完美主義來講，我們可以繼續探討一下，對於懶人來講，我們連到小區樓下快遞包裹箱去拿一下的動力也是沒有的，我們心裡想的就是要是誰給我拿到家裡來送到我嘴巴里，連嘴巴都給我掰開多好。

在技術上理論上確實可以做到這樣，因為JDBC從內核拷貝內存到Java當中是需要花時間的，要是有另一個人把這個事情做了，我在家裡幹別的事情的時候它就給我送到家裡來了，我要用的時候就直接從家裡來，這個時間豈不是省掉了。沒錯，對於你來講確實省掉了，不過問題就是誰來送？

在程序中一定需要加一個線程來幹這個事情，無論是應用線程還是內核線程，一定要有一個線程來做這個事情，來把內核的數據拷貝到應用內存，甚至於解析成JDBC的數據行，提供給應用程序直接使用，但這一定完美嗎？其實這個中間不能忽略一個協調問題，例如家裡要炒菜，缺一包調料，原本可以自己到樓下便利店去買，但是非要讓別人送家裡，這個時候送的速度不是取決於自己，而是送貨人的安排，在家裡其它的菜都下鍋了，就剩一包調料沒到位，那麼你沒別的辦法，只能等這包調料送到家裡來以後才能進行炒菜的下一道工序。所以，在理想情況下，它確實可以節約很多次內存拷貝時間，但是增加一些協調鎖的開銷。

【可不可以直接從內核緩衝區讀取數據呢？】

理論上也是可以的，在解釋這個問題之前，我們先了解下除了這一次內存拷貝還有那些：

JDBC按照二進位將內核緩衝區的數據讀取後，也會進一步解析成具體的結構化數據，由於此時要給業務方返回ResultSet的具體行的結構化數據，也就是生成RowData的數據一定會有一次拷貝，而且JDBC返回某些對象類型數據的時候（例如byte []數組），由於JDBC不希望你通過結果集修改返回結果中的byte []的內容（例如：byte[1] = 0xFF）去修改ResultSet本身內容，在某些JDBC的實現中，可能還會再做1次內存拷貝，業務代碼使用過程中還會存在拼字元串，網路輸出等，又會存在大量的內存拷貝，這些在業務層面是無法避免的，相對來講，內核到應用內存的這一點點拷貝，簡直微不足道，所以基本也沒去幹這事情，而是把心思放在更重要的地方，除非程序瓶頸在這裡，這種特殊的問題就需要特殊地探討了。

另一個角度，從技術上來講，雖然是可以做到直接從內核態直接讀取數據的，但需要按照位元組從Buffer將數據拿走，才能騰出空間讓更多的數據進來，那麼拿走的數據放哪裡呢？如果說拿走的數據進去JVM，那麼它本來是不是就是一次內存拷貝。

從服務端來講，服務端倒是可以優化直接將數據通過直接IO的方式傳遞（不過這種方式數據的協議就和數據的存儲格式一致了，顯然只是理論上的），要真正做到自定義的協議，又要通過內核態數據直接發送，需要通過修改OS級別的文件系統協議，來達到轉換的目的，這又回到比較特殊的場景下才會需要，例如資料庫的存儲層和計算層如果需要分離，也就是以前計算和存儲在一臺機器上，直接通過文件協議訪問數據，現在拆到不同的機器上，通過TCP去訪問，要降低RT的手段之一就有這樣的手段。

今天就說到這裡，不知道各位對JDBC獲取資料庫數據是否有了一些新的認知？