MySQL準備原理の詳細な説明

準備のメリット

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

Prepare の登場は、ハード解析の問題を最適化するためです。サーバー側での準備の実行プロセスは次のようになります。

1) Prepare はクライアントから「?」を含む SQL を受信し、ハード解析を実行して構文ツリー (stmt->Lex) を取得し、スレッドが配置されている preparestatement キャッシュにキャッシュします。このキャッシュはハッシュマップです。キーは stmt->id です。次に、クライアントの stmt->id とその他の情報を返します。

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

準備により、実行フェーズでのハード解析時間を節約できます。 SQL ステートメントが準備モードで 1 回だけ実行される場合、SQL ステートメントの実行にはサーバーとの 2 回の対話 (準備と実行) が必要ですが、通常 (非準備) モードでは 1 回の対話のみが必要です。このように prepare を使用すると、追加のネットワーク オーバーヘッドが発生し、コストに見合わない可能性があります。同じ SQL ステートメントが複数回実行される場合、たとえば準備モードで 10 回実行される場合を考えてみましょう。この場合、ハード解析は 1 回だけ必要になります。現時点では、追加のネットワーク オーバーヘッドはごくわずかです。したがって、頻繁に実行される SQL には prepare が適しています。

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

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

jdbcと準備

JDBC サーバーのパラメータ:

useServerPrepStmts: デフォルトはfalse。サーバー準備スイッチを使用するかどうか

JDBC クライアント パラメータ:

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

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

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

Jdbc の準備の処理:

useServerPrepStmts=true の場合の準備の JDBC 処理

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

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

useServerPrepStmts=false の場合の準備の JDBC 処理

1) サーバーと対話しない PreparedStatement オブジェクトを作成します。

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

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

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

接続 con = null;
      PreparedStatement ps = null;
      文字列 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();

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

useServerPrepStmts=false の場合、サーバーは同じ SQL を 2 回ハード解析します。

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

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

2 つの準備の間に ps.close() がない場合、cachePrepStmts=true および cachePrepStmts=false では 1 つのハード解析のみが必要になります。

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

テスト

1) prepare の効果と useServerPrepStmts パラメータの影響をテストするために簡単なテストを実行しました。

カウント = 5000;
    // 準備なし
    文字列 sql = "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 または parent_id =547667559932641;";
    begin = 新しい日付();
    System.out.println("begin:" + df.format(begin));
    stmt = con.createStatement();
    (int i = 0; i < cnt; i++) の場合
    {      
      stmt.executeQuery(sql);
    } 
    end = 新しい日付();
    System.out.println("終了:" + df.format(終了));
    長い temp = end.getTime() - begin.getTime();
    System.out.println("持続間隔がありません:" + temp);
    
    //テスト準備    
    sql = "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 を tc_biz_order_0030 から選択します。" +
        "parent_id = 594314511722841 または parent_id =?;";
    ps = con.prepareStatement(sql);
    BigInteger パラメータ = 新しい BigInteger("547667559932641");
    begin = 新しい日付();
    System.out.println("begin:" + df.format(begin));
    (int i = 0; i < cnt; i++) の場合
    {
      ps.setObject(1, パラメータ);
      ps.executeQuery(); 
    } 
    end = 新しい日付();
    System.out.println("終了:" + df.format(終了));
    temp = end.getTime() - begin.getTime();
    System.out.println("準備間隔:" + temp);

複数のサンプルテストの結果は次の通りです。

結論は：

useServerPrepStmts=true の場合、準備が 7% 増加します。

useServerPrepStmts=false の場合、準備のパフォーマンスは準備しない場合と同等になります。

ステートメントが select * from tc_biz_order_0030 where parent_id =? と簡略化される場合。テストの結果、useServerPrepStmts=true の場合、準備は 2% しか改善されないことがわかりました。SQL が単純になればなるほど、ハード解析に費やす時間が短くなり、準備の改善も少なくなります。

注意: このテストは、単一の接続と単一の SQL ステートメントの理想的な条件下で実行されます。複数の接続と複数の SQL ステートメントがオンラインになっている場合があり、SQL 実行頻度と SQL の複雑さは異なる場合があります。したがって、prepare の改善効果は特定の環境によって異なります。

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

以下は準備不要です

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[.]空き
   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

以下は準備完了です

3.46% mysqld libc-2.12.so[.]__memcpy_ssse3
   2.32% mysqld mysqld[.] cmp_dtuple_rec_with_match
   2.14% mysqld mysqld[.]_ZL14build_templateP19row_prebuilt_structP3THDP5TABLEj
   1.96% mysqld mysqld[.]buf_page_get_gen
   1.66% mysqld mysqld[.]page_cur_search_with_match
   1.54% mysqld mysqld[.] row_search_for_mysql
   1.44% mysqld mysqld[.]btr_cur_search_to_nth_level
   1.41% mysqld libjemalloc.so.1[.]空き
   1.35% mysqld mysqld[.] rec_init_offsets
   1.32% mysqld [kernel.kallsyms] [k] kfree
   1.14% mysqld libjemalloc.so.1[.]malloc
   1.08% mysqld [kernel.kallsyms] [k] fget_light
   1.05% mysqld mysqld[.] rec_get_offsets_func
   0.99% mysqld mysqld[.]_ZN8Protocol24send_result_set_metadataEP4ListI4ItemEj
   0.90% mysqld mysqld[.]sync_array_print_long_waits
   0.87% mysqld mysqld[.]page_rec_get_n_recs_before
   0.81% mysqld mysqld[.]_ZN4JOIN8optimizeEv
   0.81% mysqld libc-2.12.so[.]__strlen_sse42
   0.78% mysqld mysqld[.]_ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array
   0.72% mysqld [kernel.kallsyms] [k] tcp_recvmsg
   0.63% mysqld libpthread-2.12.so[.] __pthread_getspecific_internal
   0.63% mysqld [kernel.kallsyms] [k] sk_run_filter
   0.60% mysqld mysqld[.]_Z19find_field_in_tableP3THDP5TABLEPKcjbPj
   0.60% mysqld mysqld[.]page_check_dir
   0.57% mysqld mysqld[.]_Z16ディスパッチコマンド19enum_server_commandP3THDP

比較すると、MYSQLparse lex_one_token が準備中に最適化されていることがわかります。

考える

1. cachePrepStmts を有効にする問題に関しては、各接続にキャッシュがあり、これは SQL によって一意に識別される LRU キャッシュであることを前に説明しました。サブテーブルが多く、接続が大きい場合、これによってアプリケーション サーバーのメモリに問題が発生する可能性があります。ここでの前提は、ibatis がデフォルトで prepare を使用することです。 mybatis では、statementType タグを使用して、特定の SQL ステートメントが prepare を使用するかどうかを指定できます。

statementType STATEMENT、PREPARED、CALLABLE のいずれか。これにより、MyBatis はそれぞれ Statement、PreparedStatement、CallableStatement を使用します。デフォルト: PREPARED。

これにより、頻度の高い SQL のみの準備の使用を正確に制御できるため、使用される準備 SQL の数を制御し、メモリの消費量を削減できます。残念ながら、ほとんどのグループは現在、ステートメントタイプをサポートしていないibatis 2.0を使用しているようです。
ラベル。

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

3 oracle prepare と mysql prepare の違い:

MySQL と Oracle の主な違いは、MySQL には Oracle のような実行プラン キャッシュがないことです。先ほど、SQL 実行プロセスには、字句解析 -> 構文解析 -> 意味解析 -> 実行プランの最適化 -> 実行という段階が含まれることを説明しました。 Oracle の準備には、実際には、字句解析 -> 構文解析 -> 意味解析 -> 実行計画の最適化という段階が含まれます。つまり、Oracle の準備ではより多くの作業が行われ、実行では実行のみが必要になります。したがって、Oracle の準備は MySQL よりも効率的です。

要約する

以上が、MySQL の準備原則の詳細な説明に関するこの記事の内容のすべてです。ご興味のある方は、このサイトの他の関連トピックを参照してください。ご質問がある場合、または記事、書籍、ソースコードが必要な場合は、いつでもメッセージを残していただければ、編集者が喜んでお答えします。このウェブサイトをご愛顧いただきありがとうございます。