MySQLのprepare原則の詳細な説明

この記事では、prepare の生成、サーバー側での実行プロセス、jdbc の prepare および関連テストの処理など、MySQL prepare に関連する内容を主に紹介します。必要な方はさらに学習してください。お役に立てれば幸いです。

Prepare の利点

Prepare SQL が生成される理由。 mysql サーバー上で SQL を実行するプロセスから始めましょう。SQL 実行プロセスには、字句解析 -> 構文解析 -> 意味解析 -> 実行計画の最適化 -> 実行の段階が含まれます。字句解析 -> 文法解析 これら 2 つの段階はハード解析と呼ばれます。字句解析ではSQL中の各単語を識別し、構文解析ではSQL文がSQL文法に準拠しているかどうかを解析して構文木(Lex)を取得します。パラメータが異なるが他のパラメータが同じ SQL の場合、実行時間は異なりますが、ハード解析時間は同じです。同じ SQL のクエリ データが変更されると、複数のクエリの実行時間は異なる場合がありますが、ハード解析の時間は変わりません。 SQL の実行時間が短いほど、合計実行時間に対する SQL ハード解析時間の比率が高くなります。 Taobao 上のほとんどのトランザクション SQL アプリケーションでは、クエリはインデックスを経由するため、実行時間は比較的短くなります。したがって、淘宝網は大部分を占めるために db SQL ハード解析を使用します。

Prepare の登場は、ハード解析の問題を最適化することです。サーバー側でのPrepareの実行プロセスは以下の通りです

1) Prepareはクライアントから「?」付きのSQLを受け取り、ハードパースを行って構文ツリー(stmt->Lex)を取得し、スレッドが配置されているpreparestatementキャッシュ。このキャッシュは HASH MAP であり、キーは stmt->id などの情報がクライアントに返されます。

2) 実行はクライアントのstmt->idやパラメータなどの情報を受け取ります。クライアントはここで SQL を送信する必要がないことに注意してください。サーバーは、stmt->id に基づいてハード解析された stmt の preparestatement キャッシュを検索し、パラメータを設定すると、後続の最適化と実行を続行できます。

準備を行うと、実行段階での難しい解析時間を節約できます。 SQL が 1 回だけ実行され、準備モードで実行される場合、SQL の実行にはサーバーとの 2 つの対話 (準備と実行) が必要ですが、通常 (非準備) モードでは必要な対話は 1 つだけです。この方法で準備を使用すると、追加のネットワーク オーバーヘッドが発生するため、メリットに見合わない可能性があります。たとえば、同じ SQL が複数回実行される状況を見てみましょう。たとえば、準備モードで 10 回実行された場合、必要なハード解析は 1 回だけです。この時点で、追加のネットワーク オーバーヘッドは無視できる程度になります。したがって、prepare は頻繁に実行される SQL に適しています。

Prepare のもう 1 つの機能は SQL インジェクションを防ぐことですが、これはクライアント側の jdbc でエスケープすることで実現され、サーバーとは何の関係もありません。
ハードパースの割合

ストレステスト中のperfで得られた結果によると、ハードパースに関連する関数の割合が比較的高く(MYSQLparse 4.93%、lex_one_token 1.79%、lex_start 1.12%)、合計で8近くになっています。 %。したがって、サーバー上で prepare を使用すると、パフォーマンスがさらに向上する可能性があります。

jdbc と準備

jdbc サーバー側パラメーター:

useServerPrepStmts: サーバー準備スイッチを使用するかどうか

jdbc クライアント側パラメーター:

cachePrepStmts:デフォルトは false。prepareStatement オブジェクトをキャッシュするかどうか。各接続にはキャッシュがあり、これは SQL によって一意に識別される LRU キャッシュです。同じ接続の下では、異なる stmt が prepareStatement オブジェクトを再作成する必要はありません。

prepStmtCacheSize: LRU キャッシュ内の prepareStatement オブジェクトの数。通常は、最もよく使用される SQL の番号に設定されます。

prepStmtCacheSqlLimit: prepareStatement オブジェクトのサイズ。サイズを超えるとキャッシュされません。

Jdbc の prepare 処理:

useServerPrepStmts=true J​​dbc の prepare 処理

1) PreparedStatement オブジェクトを作成し、COM_PREPARE コマンドをサーバーに送信し、サーバーは疑問符付きの SQL を送信します。 st mt->ID等の情報

2) COM_EXECUTEコマンドをサーバーに送信し、パラメータ情報を送信します。

useServerPrepStmts=false の場合の Jdbc の prepare 処理

1) PreparedStatement オブジェクトを作成します。この時点ではサーバーと対話しません。

2) パラメーターと PreparedStatement オブジェクトに基づいて完全な SQL を結合し、サーバーに QUERY コマンドを送信します

パラメーターcachePrepStmts がオンになっているときと、useServerPrepStmts が true または false のときの PreparedStatement を見てみましょう。オブジェクトがキャッシュされます。ただし、useServerPrepStmts が true の場合、キャッシュされた PreparedStatement オブジェクトにはサーバーの stmt->id およびその他の情報が含まれます。つまり、PreparedStatement オブジェクトを再利用すると、サーバーとの通信 (COM_PREPARE コマンド) のオーバーヘッドがなくなります。また、 useServerPrepStmts=false は、PreparedStatement オブジェクトをキャッシュするためにcachePrepStmts をオンにすることは、単純な SQL 解析情報にすぎないため、現時点でcachePrepStmts をオンにすることはあまり意味がありません。

Java コードの一部を見てみましょう

Connection con = null;
      PreparedStatement ps = null;
      String sql = "select * from user where id=?";
      ps = con.prepareStatement(sql);      
      ps.setInt(1, 1);‍‍      
      ps.executeQuery();      
      ps.close();      
      ps = con.prepareStatement(sql);      
      ps.setInt(1, 3);      
      ps.executeQuery();      
      ps.close();

このコードは、同じセッション内で同じステートメントを 2 回準備して実行し、間に ps.close();

UseServerPrepStmts=false があるため、サーバーは同じ SQL を 2 回ハード解析します。

useServerPrepStmts=true およびcachePrepStmts=false の場合でも、サーバーは同じ SQL を 2 回ハード解析します。

UseServerPrepStmts=true、cachePrepStmts=true の場合、サーバーは SQL を 1 回だけハード解析します。

2 つの prepare の間に ps.close(); がない場合、cachePrepStmts=true とcachePrepStmts=false は 1 つのハード解析のみを必要とします

したがって、クライアントは同じ SQL に対して PreparedStatement オブジェクトを頻繁に割り当て、解放します。 cachePrepStmts パラメータを有効にする必要があります。

テスト

1) 主に準備の効果と useServerPrepStmts パラメーターの影響をテストする簡単なテストを作成しました

非準備時間と準備時間の比率

useServerPrepStmts=true0.93 useServerPrepStmts=false1.01useServerPrepStmts=true、prepare は 7% 増加します useServerPrepStmts= false の場合、prepare のパフォーマンスは非 prepa のパフォーマンスと同等になります。再。 ステートメントが select * from tc_biz_order_0030 whereparent_id =? に簡略化された場合。テストの結論は、useServerPrepStmts=true の場合、準備は 2% しか向上しません。SQL が単純になると、ハード解析にかかる時間が短くなり、準備の向上も少なくなります。

	結論:

注: このテストは、単一の接続と単一の SQL という理想的な条件下で実施されました。オンラインでは複数の接続と複数の SQL が存在し、SQL の実行頻度、SQL の複雑さなどにより、改善効果は異なります。準備の内容は特定の状況によって異なります。

2) 準備前後のパフォーマンストップの比較

以下は準備なしです

cnt = 5000;
    // no prepare
    String sql = "select biz_order_id,out_order_id,seller_nick,buyer_nick,seller_id,buyer_id,auction_id,auction_title,auction_price,buy_amount,biz_type,sub_biz_type,fail_reason,pay_status,logistics_status,out_trade_status,snap_path,gmt_create,status,ifnull(buyer_rate_status, 4) buyer_rate_status from tc_biz_order_0030 where " +
    "parent_id = 594314511722841 or parent_id =547667559932641;";
    begin = new Date();
    System.out.println("begin:" + df.format(begin));
    stmt = con.createStatement();
    for (int i = 0; i < cnt; i++)
    {      
      stmt.executeQuery(sql);
    } 
    end = new Date();
    System.out.println("end:" + df.format(end));
    long temp = end.getTime() - begin.getTime();
    System.out.println("no perpare interval:" + temp);
    
    // test prepare    
    sql = "select biz_order_id,out_order_id,seller_nick,buyer_nick,seller_id,buyer_id,auction_id,auction_title,auction_price,buy_amount,biz_type,sub_biz_type,fail_reason,pay_status,logistics_status,out_trade_status,snap_path,gmt_create,status,ifnull(buyer_rate_status, 4) buyer_rate_status from tc_biz_order_0030 where " +
        "parent_id = 594314511722841 or parent_id =?;";
    ps = con.prepareStatement(sql);
    BigInteger param = new BigInteger("547667559932641");
    begin = new Date();
    System.out.println("begin:" + df.format(begin));
    for (int i = 0; i < cnt; i++)
    {
      ps.setObject(1, param);
      ps.executeQuery(); 
    } 
    end = new Date();
    System.out.println("end:" + df.format(end));
    temp = end.getTime() - begin.getTime();
    System.out.println("prepare interval:" + temp);

以下はperpareです

6.46%  mysqld mysqld       [.] _Z10MYSQLparsePv
   3.74%  mysqld libc-2.12.so    [.] __memcpy_ssse3
   2.50%  mysqld mysqld       [.] my_hash_sort_utf8
   2.15%  mysqld mysqld       [.] cmp_dtuple_rec_with_match
   2.05%  mysqld mysqld       [.] _ZL13lex_one_tokenPvS_
   1.46%  mysqld mysqld       [.] buf_page_get_gen
   1.34%  mysqld mysqld       [.] page_cur_search_with_match
   1.31%  mysqld mysqld       [.] _ZL14build_templateP19row_prebuilt_structP3THDP5TABLEj
   1.24%  mysqld mysqld       [.] rec_init_offsets
   1.11%  mysqld libjemalloc.so.1  [.] free
   1.09%  mysqld mysqld       [.] rec_get_offsets_func
   1.01%  mysqld libjemalloc.so.1  [.] malloc
   0.96%  mysqld libc-2.12.so    [.] __strlen_sse42
   0.93%  mysqld mysqld       [.] _ZN4JOIN8optimizeEv
   0.91%  mysqld mysqld       [.] _ZL15get_hash_symbolPKcjb
   0.88%  mysqld mysqld       [.] row_search_for_mysql
   0.86%  mysqld [kernel.kallsyms]  [k] tcp_recvmsg

比較はMYSQLparse lexで確認できます_one_token は pre にありますペアリング中に最適化されました。

考察

1 前述したように、各接続にはキャッシュがあり、多くのサブテーブルと大規模な接続の場合、これは一意の識別子として SQL を持つ LRU キャッシュです。アプリケーションサーバーがメモリの問題を引き起こす可能性があります。ここでの前提は、ibatis がデフォルトで prepare を使用するということです。 mybatisでは、ラベルstatementTypeは、特定のSQLがprepare.

statementTypeのいずれかを使用するかどうかを指定できます。これにより、MyBatisはそれぞれStatement、PreparedStatement、またはCallableStatementを使用します。デフォルト: PREPARED.

これは正確です。頻度の高い SQL に対してのみ prepare の使用を制御することで、準備された SQL の数を制御し、メモリ消費を削減します。残念ながら、現在、ほとんどのグループが ibatis バージョン 2.0 を使用しているようですが、このバージョンでは、statementType タグがサポートされていません。

2 サーバー側の準備キャッシュは HASH MAP であり、キーは stmt->id であり、接続ごとに 1 つ維持されます。したがって、実際のテストが行​​われるまでは、メモリの問題も発生する可能性があります。必要に応じて、異なる接続に対して同じ準備 SQL を共有できるように、キー SQL を使用してグローバル キャッシュに変換する必要があります。

3 oracle prepare と mysql prepare の違い:

mysql と oracle の主な違いは、mysql には oracle のような実行プラン キャッシュがないことです。前に、SQL 実行プロセスには次の段階が含まれると述べました: 字句分析 -> 構文分析 -> 意味分析 -> 実行計画の最適化 -> 実行。 Oracle の prepare には実際には次の段階が含まれています: 字句解析 -> 構文分析 -> 意味分析 -> 実行計画の最適化。つまり、Oracle の prepare はより多くのことを実行し、execute は実行するだけで済みます。したがって、Oracle の prepare は MySQL よりも効率的です。

概要

以上がMySQLのprepare原則の詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。