MySQL のストリーミングクエリおよびカーソルクエリメソッド (概要共有)-mysql チュートリアル-php.cn

この記事は、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 サイトの他の関連記事を参照してください。