一文帶你搞懂mysql中的三種數(shù)據(jù)讀取方式

更新時間：2024年12月27日 08:28:58 作者：HBLOG

在與MySQL數(shù)據(jù)庫交互時,數(shù)據(jù)的讀取方式有多種選擇,每種方式都有其獨特的原理、優(yōu)勢和劣勢,本文將對這三種讀取方式進行詳細介紹,需要的可以參考下

1. 普通讀取

介紹

普通讀取是指通過JDBC的Statement或PreparedStatement執(zhí)行SQL查詢，JDBC驅(qū)動會阻塞的一次性讀取全部查詢的數(shù)據(jù)到 JVM 內(nèi)存中。這種方式適用于小型數(shù)據(jù)集的讀取。

原理

在普通讀取中，當執(zhí)行查詢時，JDBC會將整個結果集從數(shù)據(jù)庫加載到內(nèi)存中。開發(fā)者可以通過ResultSet對象逐行訪問數(shù)據(jù)。

示例代碼

Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;

try {
    String url = "jdbc:mysql://localhost:3307/test?useSSL=false";
    connection = DriverManager.getConnection(url, user, password);
    statement = connection.createStatement();
    resultSet = statement.executeQuery("SELECT * FROM table_name");

    while (resultSet.next()) {
        System.out.println(resultSet.getString("column_name"));
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    // close
    if (resultSet != null) resultSet.close();
    if (statement != null) statement.close();
    if (connection != null) connection.close();
}

優(yōu)勢

簡單易用：代碼結構簡單，易于理解和使用。
適合小數(shù)據(jù)集：對于小型數(shù)據(jù)集，性能良好，讀取速度快。

劣勢

內(nèi)存消耗：對于大型數(shù)據(jù)集，可能導致內(nèi)存消耗過大，甚至引發(fā)OutOfMemoryError。
不適合實時處理：無法實時處理數(shù)據(jù)，需等待整個結果集加載完成。

2. 游標讀取

介紹

游標讀取是指通過JDBC的Statement或PreparedStatement使用游標逐行讀取數(shù)據(jù)。游標允許在結果集中移動，適合處理較大的數(shù)據(jù)集。

原理

游標讀取通過在數(shù)據(jù)庫中維護一個指向結果集的指針，允許逐行訪問數(shù)據(jù)。每次讀取一行數(shù)據(jù)，游標向前移動，直到結果集結束。

示例代碼

在連接參數(shù)中需要拼接useCursorFetch=true；

創(chuàng)建Statement時需要設置ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY

設置fetchSize控制每一次獲取多少條數(shù)據(jù)

Connection connection = null; 
PreparedStatement preparedStatement = null; 
ResultSet resultSet = null;

try { 
    String url ="jdbc:mysql://localhost:3307/test?useSSL=false&useCursorFetch=true"; 
    connection = DriverManager.getConnection(url, user, password); 
    preparedStatement = connection.prepareStatement("SELECT * FROM table_name", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 
    preparedStatement.setFetchSize(100); 
    //set fetchSize 
    resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
    System.out.println(resultSet.getString("column_name"));
        
    }

} catch (SQLException e) { 
    e.printStackTrace(); 
    
} finally { 
    // close reources 
    if (resultSet != null) 
    resultSet.close(); 
    if (preparedStatement != null) 
    preparedStatement.close(); 
    if (connection != null) 
    connection.close(); 
    
}

優(yōu)勢

內(nèi)存效率：只在內(nèi)存中保留當前行，適合處理大型數(shù)據(jù)集。
逐行處理：可以逐行讀取和處理數(shù)據(jù)，適合實時數(shù)據(jù)處理場景。

劣勢

復雜性：相較于普通讀取，代碼結構稍復雜。
性能開銷：在某些情況下，逐行讀取可能會導致性能下降。

游標查詢需要注意的點：

由于MySQL方不知道客戶端什么時候?qū)?shù)據(jù)消費完，而自身的對應表可能會有DML寫入操作，此時MySQL需要建立一個臨時空間來存放需要拿走的數(shù)據(jù)。因此對于當你啟用useCursorFetch讀取大表的時候會看到MySQL上的幾個現(xiàn)象：

IOPS飆升（IOPS (Input/Output Per Second)：磁盤每秒的讀寫次數(shù)）
磁盤空間飆升
客戶端JDBC發(fā)起SQL后，長時間等待SQL響應數(shù)據(jù)，這段時間就是服務端在準備數(shù)據(jù)
在數(shù)據(jù)準備完成后，開始傳輸數(shù)據(jù)的階段，網(wǎng)絡響應開始飆升，IOPS由“讀寫”轉變?yōu)?ldquo;讀取”。
CPU和內(nèi)存會有一定比例的上升

3. 流式讀取

介紹

流式讀取是指通過JDBC的Statement或PreparedStatement以流的方式讀取數(shù)據(jù)，適合處理非常大的數(shù)據(jù)集。

原理

流式讀取通過設置ResultSet的類型和并發(fā)模式，允許在不將整個結果集加載到內(nèi)存的情況下，逐行讀取數(shù)據(jù)。通常結合setFetchSize()方法來控制每次從數(shù)據(jù)庫中獲取的行數(shù)。

示例代碼

Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;

try {
    String url = "jdbc:mysql://localhost:3307/test?useSSL=false";
    connection = DriverManager.getConnection(url, user, password);
    preparedStatement = connection.prepareStatement("SELECT * FROM table_name", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    preparedStatement.setFetchSize(1000); // 設置每次讀取的行數(shù)
    //OR USEING com.mysql.jdbc.StatementImpl
    //((StatementImpl) statement).enableStreamingResults();
    resultSet = preparedStatement.executeQuery();

    while (resultSet.next()) {
        System.out.println(resultSet.getString("column_name"));
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    // 關閉資源
    if (resultSet != null) resultSet.close();
    if (preparedStatement != null) preparedStatement.close();
    if (connection != null) connection.close();
}

優(yōu)勢

極高的內(nèi)存效率：適合處理超大數(shù)據(jù)集，內(nèi)存占用極低。
實時處理能力：可以實時處理數(shù)據(jù)，適合流式數(shù)據(jù)分析。

劣勢

復雜性：實現(xiàn)相對復雜，需要合理設置fetch size。
性能問題：在某些情況下，頻繁的數(shù)據(jù)庫訪問可能導致性能下降。

流式查詢應該注意的坑

WARN ] 2024-12-26 09:36:50.365 [] job-file-log-676bc326966a463e08520799 - [srtosr][sr35] - Query 'his_config_info_exp' snapshot row size failed: java.lang.RuntimeException: io.tapdata.flow.engine.V2.exception.node.NodeException: Query table 'his_config_info_exp' count failed: No operations allowed after connection closed.
java.util.concurrent.CompletionException: java.lang.RuntimeException: io.tapdata.flow.engine.V2.exception.node.NodeException: Query table 'his_config_info_exp' count failed: No operations allowed after connection closed.
at java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:273)
at java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:280)
at java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1643)
at java.util.concurrent.CompletableFuture$AsyncRun.exec(CompletableFuture.java:1632)
at java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289)
at java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056)
at java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692)
at java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:157)

MySQL Connector/J 5.1 Developer Guide中原文：

There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown. 也就是說當通過流式查詢獲取一個ResultSet后，通過next迭代出所有元素之前或者調(diào)用close關閉它之前，不能使用同一個數(shù)據(jù)庫連接去發(fā)起另外一個查詢，否者拋出異常（第一次調(diào)用的正常，第二次的拋出異常）。