共計 3934 個字符，預計需要花費 10 分鐘才能閱讀完成。
這篇文章主要講解了“MySQL 中流式查詢及游標查詢的方式是什么”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“MySQL 中流式查詢及游標查詢的方式是什么”吧！
一、業務場景
現在業務系統需要從 MySQL 數據庫里讀取 500w 數據行進行處理
遷移數據
導出數據
批量處理數據
二、羅列一下三種處理方式
常規查詢：一次性讀取 500w 數據到 JVM 內存中，或者分頁讀取
流式查詢：每次讀取一條加載到 JVM 內存進行業務處理
游標查詢：和流式一樣，通過 fetchSize 參數，控制一次讀取多少條數據
2.1 常規查詢
默認情況下，完整的檢索結果集會將其存儲在內存中。在大多數情況下，這是最有效的操作方式，更易于實現。
假設單表 500w 數據量，沒有人會一次性加載到內存中，一般會采用分頁的方式。
在這里，測試 demo 中只是為了監控 JVM，所以沒有采用分頁，一次性將數據載入內存中
@Test
public void generalQuery() throws Exception {
// 1 核 2G：查詢一百條記錄：47ms
// 1 核 2G：查詢一千條記錄：2050 ms
// 1 核 2G：查詢一萬條記錄：26589 ms
// 1 核 2G：查詢五萬條記錄：135966 ms
String sql = select * from wh_b_inventory limit 10000
ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery(sql);
int count = 0;
while (rs.next()) {
count++;
}
System.out.println(count);
}JVM 監控
我們將對內存調小 -Xms70m -Xmx70m
整個查詢過程中，堆內存占用逐步增長，并且最終導致 OOM：
java.lang.OutOfMemoryError: GC overhead limit exceeded
1、頻繁觸發 GC
2、存在 OOM 隱患
2.2 流式查詢
流式查詢有一點需要注意：必須先讀取（或關閉）結果集中的所有行，然后才能對連接發出任何其他查詢，否則將引發異常，其 查詢會獨占連接。
從測試結果來看，流式查詢并沒有提升查詢的速度
@Test
public void streamQuery() throws Exception {
// 1 核 2G：查詢一百條記錄：138ms
// 1 核 2G：查詢一千條記錄：2304 ms
// 1 核 2G：查詢一萬條記錄：26536 ms
// 1 核 2G：查詢五萬條記錄：135931 ms
String sql = select * from wh_b_inventory limit 50000
statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(Integer.MIN_VALUE);
ResultSet rs = statement.executeQuery(sql);
int count = 0;
while (rs.next()) {
count++;
}
System.out.println(count);
}JVM 監控
我們將堆內存調小 -Xms70m -Xmx70m
我們發現即使堆內存只有 70m，卻依然沒有發生 OOM
2.3 游標查詢
注意：
1、需要在數據庫連接信息里拼接參數 useCursorFetch=true
2、其次設置 Statement 每次讀取數據數量，比如一次讀取 1000
從測試結果來看，游標查詢在一定程度縮短了查詢速度
@Test
public void cursorQuery() throws Exception {
Class.forName( com.mysql.jdbc.Driver
// 注意這里需要拼接參數，否則就是普通查詢
conn = DriverManager.getConnection( jdbc:mysql://101.34.50.82:3306/mysql-demo?useCursorFetch=true , root , 123456
start = System.currentTimeMillis();
// 1 核 2G：查詢一百條記錄：52 ms
// 1 核 2G：查詢一千條記錄：1095 ms
// 1 核 2G：查詢一萬條記錄：17432 ms
// 1 核 2G：查詢五萬條記錄：90244 ms
String sql = select * from wh_b_inventory limit 50000
((JDBC4Connection) conn).setUseCursorFetch(true);
statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
statement.setFetchSize(1000);
ResultSet rs = statement.executeQuery(sql);
int count = 0;
while (rs.next()) {
count++;
}
System.out.println(count);
}JVM 監控
我們將堆內存調小 -Xms70m -Xmx70m
我們發現在單線程情況下，游標查詢和流式查詢一樣，都能很好的規避 OOM，并且游標查詢能夠優化查詢速度。
三、RowData
ResultSet.next() 的邏輯是實現類 ResultSetImpl 每次都從 RowData 獲取下一行的數據。RowData 是一個接口，實現關系圖如下
3.1 RowDataStatic
默認情況下 ResultSet 會使用 RowDataStatic 實例，在生成 RowDataStatic 對象時就會把 ResultSet 中所有記錄讀到內存里，之后通過 next() 再一條條從內存中讀
3.2 RowDataDynamic
當采用流式處理時，ResultSet 使用的是 RowDataDynamic 對象，而這個對象 next() 每次調用都會發起 IO 讀取單行數據
3.3 RowDataCursor
RowDataCursor 的調用為批處理，然后進行內部緩存，流程如下：
首先會查看自己內部緩沖區是否有數據沒有返回，如果有則返回下一行
如果都讀取完畢，向 MySQL Server 觸發一個新的請求讀取 fetchSize 數量結果
并將返回結果緩沖到內部緩沖區，然后返回第一行數據
總結來說就是：
默認的 RowDataStatic 讀取全部數據到客戶端內存中，也就是我們的 JVM；
RowDataDynamic 每次 IO 調用讀取一條數據；
RowDataCursor 一次讀取 fetchSize 行，消費完成再發起請求調用。
四、JDBC 通信原理
在 JDBC 與 MySQL 服務端的交互是通過 Socket 完成的，對應到網絡編程，可以把 MySQL 當作一個 SocketServer，因此一個完整的請求鏈路應該是：
JDBC 客戶端 – 客戶端 Socket – MySQL – 檢索數據返回 – MySQL 內核 Socket Buffer – 網絡 – 客戶端 Socket Buffer – JDBC 客戶端
4.1 generalQuery 普通查詢
普通查詢會將當次查詢到的所有數據加載到 JVM，然后再進行處理。
如果查詢數據量過大，會不斷經歷 GC，然后就是內存溢出
4.2 streamQuery 流式查詢
服務端準備好從第一條數據開始返回時，向緩沖區懟入數據，這些數據通過 TCP 鏈路，懟入客戶端機器的內核緩沖區，JDBC 會的 inputStream.read() 方法會被喚醒去讀取數據，唯一的區別是開啟了 stream 讀取的時候，每次只是從內核中讀取一個 package 大小的數據，只是返回一行數據，如果 1 個 package 無法組裝 1 行數據，會再讀 1 個 package。
4.3 cursorQuery 游標查詢
當開啟游標的時候，服務端返回數據的時候，就會按照 fetchSize 的大小返回數據了，而客戶端接收數據的時候每次都會把換緩沖區數據全部讀取干凈，假如數據有 1 億數據，將 FetchSize 設置成 1000 的話，會進行 10 萬次來回通信；
由于 MySQL 方不知道客戶端什么時候將數據消費完，而自身的對應表可能會有 DML 寫入操作，此時 MySQL 需要建立一個臨時空間來存放需要拿走的數據。
因此對于當你啟用 useCursorFetch 讀取大表的時候會看到 MySQL 上的幾個現象：
1.IOPS 飆升
2. 磁盤空間飆升
3. 客戶端 JDBC 發起 SQL 后，長時間等待 SQL 響應數據，這段時間就是服務端在準備數據
4. 在數據準備完成后，開始傳輸數據的階段，網絡響應開始飆升，IOPS 由“讀寫”轉變為“讀取”。
IOPS (Input/Output Per Second)：磁盤每秒的讀寫次數
5.CPU 和內存會有一定比例的上升
五、并發場景
并發調用：Jmete 1 秒 10 個線程并發調用
流式查詢內存性能報告如下
并發調用對于內存占用情況也很 OK，不存在疊加式增加
游標查詢內存性能報告如下
感謝各位的閱讀，以上就是“MySQL 中流式查詢及游標查詢的方式是什么”的內容了，經過本文的學習后，相信大家對 MySQL 中流式查詢及游標查詢的方式是什么這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是丸趣 TV，丸趣 TV 小編將為大家推送更多相關知識點的文章，歡迎關注！
