MySQL のストリーミング クエリおよびカーソル クエリ メソッド (概要共有)

この記事は、mysql に関する関連知識を提供します。主に MySQL のストリーミング クエリとカーソル クエリのメソッドを紹介します。非常に参考になり、皆様のお役に立てれば幸いです。

推奨学習: mysql ビデオ チュートリアル

1. ビジネス シナリオ

次に、ビジネス システムを開始する必要があります。 MySQL データベースから 500w のデータ行を読み取り、処理します。

• データを移行します。
• データをエクスポートします。
• データをバッチ処理します。

2 番目、リスト以下の3つ 処理方法

• 通常のクエリ：一度に500wのデータをJVMメモリに読み込む、またはページ単位で読み込む
• ストリーミングクエリ：一度に1つずつ読み込んでロードするビジネス処理
• カーソル クエリ: ストリーミングと同様に、fetchSize パラメータを通じて一度に読み込むデータの数を制御します

2.1 一般的なクエリ

デフォルトでは、完全な取得結果セットはメモリに保存されます。ほとんどの場合、これが最も効率的な操作方法であり、実装も簡単です。

1 つのテーブルのデータ量が 500 万であると仮定すると、一度にメモリにロードする人はいないため、ページングが一般的に使用されます。

ここでは、テスト デモは 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 オーバーヘッド制限を超えました

1。頻繁に GC

2 が発生します。OOM

の隠れた危険があります。

2.2 ストリーミング クエリ

ストリーミング クエリについて注意すべき点が 1 つあります。接続上で他のクエリを発行する前に、結果セット内のすべての行を読み取る (または閉じる) 必要があります。そうしないと、例外がスローされます。そしてそのクエリは接続を排他的に所有します。

テスト結果から、ストリーミング クエリではクエリ速度は向上しません

@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 をうまく回避でき、カーソル クエリはクエリ速度を最適化できることがわかりました。

3. RowData

ResultSet.next() のロジックは、ResultSetImpl クラスを実装し、それを取得することです。毎回 RowData から次の行のデータ。 RowData はインターフェイスであり、実装関係図は次のとおりです

##3.1 RowDataStatic

デフォルトでは、ResultSet は RowDataStatic インスタンスを使用し、次の場合に ResultSet が使用されます。 RowDataStatic オブジェクトを生成します。メモリ内のすべてのレコードをメモリに読み込み、next()

3.2 RowDataDynamic

ストリーミング処理を使用する場合、ResultSet をメモリから 1 つずつ読み込みます。 RowDataDynamic オブジェクトを使用します。このオブジェクト next() が呼び出されるたびに、IO が開始されて 1 行のデータが読み取られます。

#まず、内部バッファに返されていないデータがあるかどうかを確認し、存在する場合は次の行を返します。

すべての読み取りが完了したら、MySQL Server への新しいリクエストをトリガーして fetchSize 量の結果を読み取ります

そして返された結果を内部バッファーにバッファリングして、データの最初の行を返します

要約:

• デフォルトの RowDataStatic はすべてを読み取ります データはクライアント メモリ (JVM) に転送されます;
• RowDataDynamic は 1 つの部分を読み取ります各 IO 呼び出しのデータ量。
• RowDataCursor は一度に fetchSize 行を読み取り、消費が完了した後にリクエスト呼び出しを開始します。

4. JDBC 通信原理JDBC と MySQL サーバー間の対話はソケットを通じて完了します。ネットワーク プログラミングに対応して、MySQL は SocketServer と見なすことができるため、完全なリクエスト リンクは次のようになります。 :

JDBC クライアント -> クライアント ソケット -> MySQL -> 取得データの戻り -> MySQL カーネル ソケット バッファ -> ネットワーク -> クライアント ソケット バッファ -> JDBC クライアント

4.1 generalQuery 一般クエリ

一般クエリは、クエリされたすべてのデータを JVM にロードして処理します。

クエリ データの量が大きすぎる場合、GC が発生し続け、メモリ オーバーフローが発生します。

4.2 streamQuery ストリーミング クエリ

サーバーはデータの最初の部分から戻る準備ができています データがバッファにロードされると、データは TCP リンクを介してクライアント マシンのカーネル バッファにロードされます。JDBC の inputStream.read() メソッドが起動されて、データ。唯一の違いは、ストリームがオンになっていることです。読み取りの際、毎回 1 つのパッケージ サイズのみのデータがカーネルから読み取られ、1 行のデータのみが返されます。1 つのパッケージが 1 行のデータをアセンブルできない場合は、別のパッケージがデータの 1 行をアセンブルします。パッケージが読み取られます。

4.3 カーソルクエリカーソルクエリ

カーソルがオンになっている場合、サーバーがデータを返すとき、サーバーは fetchSize のサイズに従ってデータを返し、クライアントは毎回データを返します。データを受信したらバッファのデータを変更して全データを読み込む データが1億件ある場合、FetchSizeを1000にすると10万往復の通信が行われます;

MySQLは知らないためクライアントがデータの消費を終了したとき、およびクライアント自身の対応するテーブルに DML 書き込み操作がある場合があります。このとき、MySQL は、削除する必要があるデータを保存するための一時スペースを作成する必要があります。

したがって、大きなテーブルを読み取るために useCursorFetch を有効にすると、MySQL でいくつかの現象が見られるでしょう:

• 1. IOPS が上昇します
• 2. ディスク容量が上昇します
• 3. クライアント JDBC が SQL を開始した後、SQL 応答データを長時間待ちます。この間、サーバーはデータを準備しています
##4. データの準備が完了すると、データが送信開始段階では、ネットワーク応答が急増し始め、IOPS が「読み取りおよび書き込み」から「読み取り」に変化します。
• IOPS (1 秒あたりの入力/出力): 1 秒あたりのディスクの読み取りおよび書き込みの数
• 5。CPU とメモリは一定の割合で増加します

5. 同時実行シナリオ

同時呼び出し: Jmete 1 秒で 10 スレッドの同時呼び出し

ストリーミング クエリのメモリ パフォーマンス レポートは次のとおりです

同時呼び出しもメモリ使用量および存在しません 積み重ねられた増加

カーソル クエリ メモリ パフォーマンス レポートは次のとおりです

6。

1. カーソル クエリとストリーミング クエリはどちらも単一スレッドで OOM を回避できます;

2. クエリ速度の点では、カーソル クエリはストリーミング クエリよりも高速です。通常のクエリと比較して、ストリーミング クエリは時間を短縮できません。クエリ時間;

3. 同時シナリオでは、ストリーミング クエリ ヒープ メモリの傾向はより安定しており、追加的な増加はありません。

推奨学習:

mysql ビデオ チュートリアル

以上がMySQL のストリーミング クエリおよびカーソル クエリ メソッド (概要共有)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。