MySQL準備原理の詳細な説明

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 0.93
useServerPrepStmts=false 1.01

結論は:

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

以下もご興味があるかもしれません:
  • MySQL で準備、実行、割り当て解除ステートメントを使用するチュートリアル
  • php+mysql の準備と通常のクエリのパフォーマンス比較
  • PHP5 mysqliのprepareステートメントの使用方法
  • MySQLのprepareステートメントのSQL構文

<<:  Linux 仮想メモリ設定のチュートリアルと実践

>>:  WeChat公式アカウントでReactプロジェクトを実行する方法

推薦する

仮想マシンを作成し、VMware に Redhat Linux オペレーティング システムをインストールする (グラフィック チュートリアル)

VMware で仮想マシンを作成し、Redhat Linux オペレーティング システムをインスト...

Linux の who コマンド例の紹介

誰についてシステムにログインしているユーザーを表示します。 who コマンドを実行すると、現在システ...

Centos 用の rpm パッケージのカスタマイズと yum リポジトリの構築に関するチュートリアル

1 yumでソフトウェアをインストールしたときにダウンロードしたrpmパッケージを保存しますyum ...

MySQLデータベースを定期的に自動バックアップする方法

データは貴重なものであることは誰もが知っています。データをバックアップしなければ、データをそのまま放...

MySQLの日付文字列タイムスタンプ変換の詳細な説明

時刻、文字列、タイムスタンプ間の変換は、日常生活でよく使用されます。よく使用されますが、私は使用する...

モバイルレイアウトにvw+remを使用する方法

まだ rem フレキシブルレイアウトを使用していますか?圧縮された js コードの大きなセクションを...

Node.jsはMySQLデータベースの実戦記録を追加、削除、変更、チェックします

目次プロジェクトでデータベースを操作する3つのステップデータベースを操作するための具体的な手順1: ...

CSS グラデーション効果の概要 (線形グラデーションと放射状グラデーション)

線形グラデーション 背景画像: linear-gradient(方向、開始色、中間色1、中間色2、....

mysql: [エラー] 不明なオプション '--skip-grant-tables'

MySQL データベースがエラー 1045 (28000): ユーザー 'ODBC'...

Vueはel-tree遅延読み込みを使用して、追加、削除、変更、クエリ機能を実装します。

Vue のツリー表示については、プロジェクトが使用されています: エフェクト ダイアグラムがツリー...

HTML シンプルショッピング数量アプレット

この記事では、参考までにシンプルなHTMLショッピング数量アプレットを紹介します。具体的な内容は次の...

Zabbixのカスタム監視項目とトリガーについて

目次1. 監視ポート関係の説明操作する2. 監視サービス関係の説明操作する3. テンプレートのインポ...

vue.js ルーターのネストされたルート

序文:ルートでは、主要部分は同じでも、基礎となる構造が異なることがあります。たとえば、ホームページに...

ウェブデザイナーが知っておくべき効率的なナビゲーションデザインの3つの原則

ウェブサイトのナビゲーションを設計することは、家の基礎を築くようなものです。基礎がしっかりしていなけ...

MySQL ログインおよび終了コマンドの形式

mysql ログインのコマンド形式は次のとおりです。 mysql -h [hostip] -u [ユ...