Detaillierte Erläuterung des Vorbereitungsprinzips in MySQL

In diesem Artikel werden hauptsächlich die zugehörigen Inhalte von MySQL Prepare vorgestellt, einschließlich der Generierung von Prepare, des Ausführungsprozesses auf der Serverseite sowie der Verarbeitung von Prepare und verwandten Tests durch JDBC. Freunde, die es benötigen, können mehr erfahren. Ich hoffe, es hilft allen.

Vorteile von Prepare

Der Grund, warum Prepare SQL generiert wird. Beginnen wir mit dem Prozess der Ausführung von SQL auf dem MySQL-Server. Der SQL-Ausführungsprozess umfasst die folgenden Phasen: lexikalische Analyse -> Syntaxanalyse -> semantische Analyse -> Ausführungsplanoptimierung -> Ausführung. Lexikalische Analyse -> Grammatische Analyse Diese beiden Phasen werden als harte Analyse bezeichnet. Die lexikalische Analyse identifiziert jedes Wort in SQL, und die syntaktische Analyse analysiert, ob die SQL-Anweisung der SQL-Grammatik entspricht, und erhält einen Syntaxbaum (Lex). Für SQL mit unterschiedlichen Parametern, aber den gleichen anderen, ist ihre Ausführungszeit unterschiedlich, aber die harte Analysezeit ist gleich. Da sich die Abfragedaten für dasselbe SQL ändern, kann die Ausführungszeit mehrerer Abfragen unterschiedlich sein, die Dauer der harten Analyse bleibt jedoch unverändert. Je kürzer die SQL-Ausführungszeit ist, desto höher ist das Verhältnis der SQL-Hard-Parsing-Zeit zur Gesamtausführungszeit. Bei den meisten transaktionalen SQL-Anwendungen auf Taobao durchlaufen Abfragen den Index und die Ausführungszeit ist relativ kurz. Daher verwendet Taobao die harte Analyse von Datenbank-SQL, um einen großen Teil auszumachen.

Die Entstehung von Prepare dient dazu, das Problem der harten Analyse zu optimieren. Der Ausführungsprozess von Prepare auf der Serverseite ist wie folgt:

1) Prepare empfängt die SQL mit „?“ vom Client und führt eine harte Analyse durch, um den Syntaxbaum (stmt->Lex) zu erhalten. und speichert es im Preparestatement-Cache, in dem sich der Thread befindet. Dieser Cache ist eine HASH-MAP. Der Schlüssel ist stmt->id. Dann werden Informationen wie stmt->id an den Client zurückgegeben.

2) Execute empfängt Informationen wie Client-StMT->ID und Parameter. Beachten Sie, dass der Client hier kein SQL senden muss. Der Server durchsucht den Preparestatement-Cache nach dem hart geparsten StMT basierend auf der stmt->id und legt die Parameter fest. Anschließend kann er mit der anschließenden Optimierung und Ausführung fortfahren.

Prepare kann harte Analysezeit während der Ausführungsphase sparen. Wenn SQL nur einmal und im Vorbereitungsmodus ausgeführt wird, erfordert die SQL-Ausführung zwei Interaktionen mit dem Server (Vorbereiten und Ausführen), während im normalen Modus (ohne Vorbereitung) nur eine Interaktion erforderlich ist. Die Verwendung von Prepare auf diese Weise bringt zusätzlichen Netzwerkaufwand mit sich, der die Vorteile möglicherweise überwiegt. Schauen wir uns die Situation an, in der dasselbe SQL mehrmals ausgeführt wird. Wenn es beispielsweise zehnmal im Vorbereitungsmodus ausgeführt wird, ist nur eine harte Analyse erforderlich. Zu diesem Zeitpunkt wird der zusätzliche Netzwerk-Overhead vernachlässigbar. Daher eignet sich Prepare für häufig ausgeführtes SQL.

Eine weitere Funktion von Prepare besteht darin, SQL-Injection zu verhindern. Dies wird jedoch durch Escape in JDBC auf der Clientseite erreicht und hat nichts mit dem Server zu tun.
Anteil des harten Parsens

Gemäß den durch perf während des Stresstests erzielten Ergebnissen sind die Anteile der Funktionen im Zusammenhang mit dem harten Parsen relativ hoch (MYSQLparse 4,93 %, lex_one_token 1,79 %, lex_start 1,12). %). Die Summe liegt bei knapp 8 %. Daher kann die Verwendung von Prepare auf dem Server zu weiteren Leistungsverbesserungen führen.

jdbc und vorbereiten

jdbc serverseitige Parameter:

useServerPrepStmts: Der Standardwert ist false Ob um den Server-Vorbereitungsschalter zu verwenden

jdbc-Client-Parameter:

cachePrepStmts: Standardmäßig falsch, ob PrepareStatement-Objekte zwischengespeichert werden sollen. Jede Verbindung verfügt über einen Cache, bei dem es sich um einen durch SQL eindeutig identifizierten LRU-Cache handelt. Unter derselben Verbindung müssen verschiedene StMTs das PrepareStatement-Objekt nicht neu erstellen.

prepStmtCacheSize: Die Anzahl der PrepareStatement-Objekte im LRU-Cache. Im Allgemeinen wird die Nummer des am häufigsten verwendeten SQL festgelegt.

prepStmtCacheSqlLimit: Die Größe des PrepareStatement-Objekts. Überschreitungen der Größe werden nicht zwischengespeichert.

Jdbcs Handhabung der Vorbereitung:

Jdbcs Handhabung der Vorbereitung, wenn useServerPrepStmts=true

1 ) Erstellen Sie eine PreparedStatement-Objekt, senden Sie den COM_PREPARE-Befehl an den Server und übertragen Sie die SQL mit dem Fragezeichen. Der Server gibt JDBC stmt->id und andere Informationen zurück

2) Senden Sie den COM_EXECUTE-Befehl an den Server und Übertragen Sie die Parameterinformationen.

Jdbcs Umgang mit Prepare, wenn useServerPrepStmts=false

1) Erstellen Sie ein PreparedStatement-Objekt und interagieren Sie zu diesem Zeitpunkt nicht mit dem Server.

2) Splice das komplette SQL basierend auf den Parametern und dem PreparedStatement-Objekt und sende den QUERY-Befehl an den Server

Schauen wir uns den Parameter „cachePrepStmts“ an, wenn er aktiviert wird on und useServerPrepStmts sind true oder false. Objekte werden zwischengespeichert. Es ist nur so, dass useServerPrepStmts wahr ist und das zwischengespeicherte PreparedStatement-Objekt die stmt->id des Servers und andere Informationen enthält. Das heißt, wenn das PreparedStatement-Objekt wiederverwendet wird, entfällt der Overhead der Kommunikation mit dem Server (COM_PREPARE-Befehl). Und „useServerPrepStmts=false“ bedeutet, dass es sich bei der Aktivierung von „cachePrepStmts“ zum Zwischenspeichern von PreparedStatement-Objekten nur um einfache SQL-Analyseinformationen handelt, sodass die Aktivierung von „cachePrepStmts“ zu diesem Zeitpunkt wenig Sinn macht.

Werfen wir einen Blick auf einen Teil des Java-Codes

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();

Dieser Code bereitet sich darauf vor, dieselbe Anweisung zweimal in derselben Sitzung auszuführen, und es gibt ps.close();

Wenn useServerPrepStmts=false, führt der Server eine harte Analyse zweimal durch. Das Gleiche SQL.

Wenn useServerPrepStmts=true, cachePrepStmts=false, analysiert der Server dasselbe SQL immer noch zweimal hart.

Wenn useServerPrepStmts=true, cachePrepStmts=true, führt der Server nur einmal eine harte Analyse von SQL durch.

Wenn es zwischen zwei Vorbereitungen kein ps.close(); gibt, dann erfordern „cachePrepStmts=true“ und „cachePrepStmts=false“ nur eine harte Analyse.

Daher muss der Client Für dasselbe SQL ist es beim häufigen Zuweisen und Freigeben von PreparedStatement-Objekten erforderlich, den Parameter „cachePrepStmts“ zu aktivieren.

Test

1) Einen einfachen Test durchgeführt, der hauptsächlich die Wirkung von „prepare“ und den Einfluss von „useServerPrepStmts“-Parametern testet.  

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);

Die Ergebnisse mehrerer Stichprobentests sind wie folgt

	非prepare和prepare时间比
useServerPrepStmts=true	0.93
useServerPrepStmts=false	1.01

Nicht-Vorbereitungszeit- und Vorbereitungszeitverhältnis

useServerPrepStmts=true

0,93

useServerPrepStmts=false

1,01

Schlussfolgerung:

Bei useServerPrepStmts=true erhöht sich Prepare um 7 % ;

Bei useServerPrepStmts=false sind Vorbereitungs- und Nicht-Vorbereitungsleistung gleichwertig.

Wenn die Anweisung vereinfacht wird, um * aus tc_biz_order_0030 auszuwählen, wobei parent_id =?. Das Testergebnis lautet dann: Wenn useServerPrepStmts=true, verbessert sich die Vorbereitung nur um 2 %.

Hinweis: Dieser Test wird unter den idealen Bedingungen einer einzelnen Verbindung und einer einzelnen SQL durchgeführt. Es werden mehrere Verbindungen und mehrere SQL online sein, sowie die Häufigkeit der SQL-Ausführung und die Komplexität davon Daher variiert der Verbesserungseffekt von Prepare je nach der jeweiligen Umgebung.

2) Perf-Top-Vergleich vor und nach der Vorbereitung

Das Folgende ist nicht vorbereiten

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

Das Folgende ist vorbereitet

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  [.] free
   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       [.] _Z16dispatch_command19enum_server_commandP3THDP

Der Vergleich zeigt, dass MYSQLparse lex_one_token während der Vorbereitung optimiert wurde.

Denken

1 Das Problem beim Öffnen von CachePrepStmts. Wie bereits erwähnt, verfügt jede Verbindung über einen Cache, bei dem es sich um eine LRU mit SQL handelt Eindeutiger Bezeichner-Cache. Wenn viele Untertabellen und große Verbindungen vorhanden sind, kann es zu Speicherproblemen für den Anwendungsserver kommen. Die Voraussetzung hierfür ist, dass ibatis standardmäßig Prepare verwendet. In mybatis kann die Bezeichnung „statementType“ angeben, ob eine bestimmte SQL-Anweisung „prepared“ oder „prepared“ verwendet. Dies führt dazu, dass MyBatis „Statement“, „preparedstatement“ oder „callablestatement“ verwendet 🎜>

Dadurch kann die Verwendung von „Prepare“ nur für SQL mit höherer Frequenz genau gesteuert werden, wodurch die Anzahl der vorbereiteten SQL gesteuert und der Speicherverbrauch reduziert wird. Leider scheinen die meisten Gruppen derzeit ibatis Version 2.0 zu verwenden, die das Tag „statementType

“ nicht unterstützt.

2 Der serverseitige Vorbereitungscache ist eine HASH-MAP. Der Schlüssel ist stmt->id und einer wird für jede Verbindung verwaltet. Daher kann es bis zum eigentlichen Test auch zu Speicherproblemen kommen. Bei Bedarf muss es mit dem Schlüssel-SQL in einen globalen Cache umgewandelt werden, damit dasselbe Vorbereitungs-SQL verschiedener Verbindungen gemeinsam genutzt werden kann.

3 Der Unterschied zwischen Oracle Prepare und MySQL Prepare:

Ein wesentlicher Unterschied zwischen MySQL und Oracle besteht darin, dass MySQL keinen Ausführungsplan-Cache wie Oracle hat. Wir haben bereits erwähnt, dass der SQL-Ausführungsprozess die folgenden Phasen umfasst: Lexikalische Analyse –>Syntaktische Analyse –>Semantische Analyse –>Optimierung des Ausführungsplans –>Ausführung. Die Vorbereitung von Oracle umfasst tatsächlich die folgenden Phasen: lexikalische Analyse -> Syntaxanalyse -> semantische Analyse -> Optimierung des Ausführungsplans. Dies bedeutet, dass die Vorbereitung von Oracle mehr Dinge ausführt und nur ausgeführt werden muss. Daher ist Oracles Prepare effizienter als MySQL.

Zusammenfassung

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung des Vorbereitungsprinzips in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!