Detaillierte Erklärung des MySQL-Prepare-Prinzips

Vorteile von Prepare

Der Grund, warum Prepare SQL generiert wird. Beginnen wir zunächst mit dem Prozess der SQL-Ausführung auf dem MySQL-Server. Der SQL-Ausführungsprozess umfasst die folgenden Phasen: lexikalische Analyse -> Syntaxanalyse -> semantische Analyse -> Ausführungsplanoptimierung -> Ausführung. Die beiden Phasen lexikalische Analyse -> grammatische Analyse werden als Hard Parsing bezeichnet. Die lexikalische Analyse identifiziert jedes Wort in SQL und die Syntaxanalyse analysiert die SQL-Anweisung, um zu sehen, ob sie der SQL-Syntax entspricht, und erhält einen Syntaxbaum (Lex). Bei SQL-Anweisungen, die bis auf die Parameter gleich sind, ist die Ausführungsdauer unterschiedlich, die Zeit für die harte Analyse bleibt jedoch gleich. Da sich die Abfragedaten ändern, kann die Ausführungszeit mehrerer Abfragen desselben SQL unterschiedlich sein, die Zeit für die harte 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 gesamten Ausführungszeit. Bei den meisten transaktionalen SQL-Anweisungen von Taobao-Anwendungen werden die Abfragen über Indizes durchgeführt und die Ausführungszeit ist relativ kurz. Daher macht die harte Analyse der SQL-Anwendungsdatenbank von Taobao einen großen Teil aus.

Der Grund für die Einführung von Prepare besteht in der Optimierung des Problems des Hard Parsing. Der Ausführungsprozess von Prepare auf der Serverseite ist wie folgt

1) Prepare empfängt das SQL mit „?“ vom Client, führt eine harte Analyse durch, um den Syntaxbaum (stmt->Lex) zu erhalten, und speichert ihn im Preparestatement-Cache zwischen, wo sich der Thread befindet. Dieser Cache ist eine HASH MAP. Der Schlüssel ist stmt->id. Anschließend werden die stmt->id des Clients und andere Informationen zurückgegeben.

2) Execute empfängt Informationen wie stmt->id und Parameter vom Client. Beachten Sie, dass der Client hier kein SQL senden muss. Der Server durchsucht den PreparedStatement-Cache nach dem hart analysierten Anweisungssatz gemäß stmt->id, legt die Parameter fest und fährt dann mit der anschließenden Optimierung und Ausführung fort.

Durch die Vorbereitung kann in der Ausführungsphase Zeit für die komplexe Analyse eingespart werden. Wenn die SQL-Anweisung nur einmal und im Vorbereitungsmodus ausgeführt wird, erfordert die Ausführung der SQL-Anweisung zwei Interaktionen mit dem Server (Vorbereiten und Ausführen), während im normalen (nicht vorbereitenden) Modus nur eine Interaktion erforderlich ist. Die Verwendung von „Prepare“ auf diese Weise führt zu zusätzlichem Netzwerk-Overhead, der die Kosten möglicherweise nicht rechtfertigt. Betrachten wir den Fall, in dem dieselbe SQL-Anweisung mehrere Male ausgeführt wird, beispielsweise 10 Mal im Vorbereitungsmodus. Dann ist nur eine harte Analyse erforderlich. Zu diesem Zeitpunkt ist der zusätzliche Netzwerkaufwand 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 Escapen auf der Clientseite von JDBC erreicht und hat nichts mit dem Server zu tun.
Anteil harter Analyse

Den durch den Stresstest ermittelten Ergebnissen zufolge ist der Anteil der Funktionen im Zusammenhang mit Hard Parsing relativ hoch (MYSQLparse 4,93 %, lex_one_token 1,79 %, lex_start 1,12 %) und beträgt insgesamt fast 8 %. Daher kann die Verwendung von Prepare auf dem Server zu erheblichen Leistungsverbesserungen führen.

jdbc und vorbereiten

Parameter des JDBC-Servers:

useServerPrepStmts: Standardmäßig auf „false“ eingestellt. Ob der Server-Prep-Schalter verwendet werden soll

JDBC-Client-Parameter:

cachePrepStmts: Der Standardwert ist „false“. Gibt an, ob PrepareStatement-Objekte zwischengespeichert werden sollen. Jede Verbindung verfügt über einen Cache, einen LRU-Cache, der eindeutig durch SQL identifiziert wird. Unter derselben Verbindung müssen verschiedene Anweisungen PrepareStatement-Objekte nicht neu erstellen.

prepStmtCacheSize: Die Anzahl der PrepareStatement-Objekte im LRU-Cache. Es wird im Allgemeinen auf die Anzahl der am häufigsten verwendeten SQL-Anweisungen eingestellt.

prepStmtCacheSqlLimit: Die Größe des PrepareStatement-Objekts. Eine Überschreitung der Größe wird nicht zwischengespeichert.

JDBCs Vorbereitungsverarbeitung:

JDBC-Verarbeitung der Vorbereitung, wenn useServerPrepStmts=true

1) Erstellen Sie ein PreparedStatement-Objekt, senden Sie einen COM_PREPARE-Befehl an den Server und senden Sie das SQL mit einem Fragezeichen. Der Server gibt Informationen wie jdbc stmt->id zurück.

2) Senden Sie den Befehl COM_EXECUTE an den Server und übergeben Sie die Parameterinformationen.

JDBC-Verarbeitung der Vorbereitung, wenn useServerPrepStmts=false

1) Erstellen Sie ein PreparedStatement-Objekt, das nicht mit dem Server interagiert.

2) Erstellt die vollständige SQL-Anweisung basierend auf den Parametern und dem PreparedStatement-Objekt und sendet einen QUERY-Befehl an den Server.

Schauen wir uns den Parameter cachePrepStmts an. Wenn useServerPrepStmts true oder false ist, werden PreparedStatement-Objekte zwischengespeichert. Wenn useServerPrepStmts jedoch wahr ist, enthält das zwischengespeicherte PreparedStatement-Objekt Informationen wie die stmt->id des Servers. Das heißt, wenn das PreparedStatement-Objekt wiederverwendet wird, wird der Aufwand für die Kommunikation mit dem Server (Befehl COM_PREPARE) eingespart. Und useServerPrepStmts=false bedeutet, dass das Aktivieren von cachePrepStmts zum Zwischenspeichern von PreparedStatement-Objekten lediglich einfache SQL-Analyseinformationen sind. Daher ist das Aktivieren von cachePrepStmts zu diesem Zeitpunkt nicht sehr sinnvoll.

Schauen wir uns einen Teil des Java-Codes an

Verbindung con = null;
      Vorbereitete Anweisung ps = null;
      Zeichenfolge SQL = "Wählen Sie * vom Benutzer aus, wobei ID=?";
      ps = con.prepareStatement(sql);      
      ps.setInt(1, 1);      
      ps.executeQuery();      
      ps.schließen();      
      ps = con.prepareStatement(sql);      
      ps.setInt(1, 3);      
      ps.executeQuery();      
      ps.schließen();

Dieser Code bereitet die gleiche Anweisung vor und führt sie zweimal in der gleichen Sitzung aus, mit ps.close() dazwischen.

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

Wenn useServerPrepStmts=true und cachePrepStmts=false ist, führt der Server dennoch zweimal ein Hardparsing für dasselbe SQL durch.

Wenn useServerPrepStmts=true und cachePrepStmts=true, führt der Server das SQL nur einmal hart durch.

Wenn zwischen zwei Vorbereitungen kein ps.close() vorhanden ist, ist für cachePrepStmts=true und cachePrepStmts=false nur eine harte Analyse erforderlich.

Wenn der Client häufig PreparedStatement-Objekte für dasselbe SQL zuweist und freigibt, muss der Parameter cachePrepStmts aktiviert werden.

prüfen

1) Es wurde ein einfacher Test durchgeführt, um die Wirkung von „Prepare“ und den Einfluss des Parameters „useServerPrepStmts“ zu testen.

Anzahl = 5000;
    // keine Vorbereitung
    String sql = "Wählen Sie 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 aus tc_biz_order_0030 aus, wobei " +
    "parent_id = 594314511722841 oder parent_id =547667559932641;";
    begin = neues Datum();
    System.out.println("beginnen:" + df.format(beginnen));
    stmt = con.createStatement();
    für (int i = 0; i < cnt; i++)
    {      
      stmt.executeQuery(sql);
    } 
    Ende = neues Datum();
    System.out.println("Ende:" + df.format(Ende));
    lange temp = end.getTime() - begin.getTime();
    System.out.println("kein Persistenzintervall:" + temp);
    
    //Test vorbereiten    
    sql = "Wählen Sie 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 aus tc_biz_order_0030, wobei " +
        "parent_id = 594314511722841 oder parent_id =?;";
    ps = con.prepareStatement(sql);
    BigInteger-Parameter = neuer BigInteger("547667559932641");
    begin = neues Datum();
    System.out.println("beginnen:" + df.format(beginnen));
    für (int i = 0; i < cnt; i++)
    {
      ps.setObject(1, param);
      ps.executeQuery(); 
    } 
    Ende = neues Datum();
    System.out.println("Ende:" + df.format(Ende));
    temp = end.getTime() - begin.getTime();
    System.out.println("Intervall vorbereiten:" + temp);

Nach mehreren Probenahmetests sind die Ergebnisse wie folgt

abschließend:

Wenn useServerPrepStmts=true, wird die Vorbereitung um 7 % erhöht;

Wenn useServerPrepStmts=false ist, ist die Leistung der Vorbereitung gleichwertig mit der der Nicht-Vorbereitung.

Wenn die Anweisung vereinfacht wird, wählen Sie * aus tc_biz_order_0030, wobei parent_id =? ist. Das Fazit des Tests lautet, dass sich die Vorbereitung bei „useServerPrepStmts=true“ nur um 2 % verbessert. Je einfacher das SQL, desto weniger Zeit wird für die harte Analyse aufgewendet und desto geringer sind die Verbesserungen bei der Vorbereitung.

Hinweis: Dieser Test wird unter den Idealbedingungen einer einzelnen Verbindung und einer einzelnen SQL-Anweisung durchgeführt. Es können mehrere Verbindungen und mehrere SQL-Anweisungen online sein, und die SQL-Ausführungshäufigkeit und SQL-Komplexität können variieren. Daher variiert der Verbesserungseffekt von Prepare je nach spezifischer Umgebung.

2) Vergleich der Leistung vor und nach der Vorbereitung

Das Folgende ist nicht vorbereitet

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[.]frei
   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

Folgendes 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[.]frei
   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

Durch Vergleich können wir feststellen, dass MYSQLparse lex_one_token während der Vorbereitung optimiert wurde.

denken

1. In Bezug auf das Aktivieren von cachePrepStmts haben wir bereits erwähnt, dass jede Verbindung einen Cache hat, bei dem es sich um einen LRU-Cache handelt, der eindeutig durch SQL identifiziert wird. Bei vielen Untertabellen und großen Verbindungen kann dies zu Speicherproblemen für den Anwendungsserver führen. Die Prämisse hierbei ist, dass ibatis standardmäßig „Prepare“ verwendet. In mybatis kann das Tag „statementType“ angeben, ob eine bestimmte SQL-Anweisung „prepare“ verwendet.

Anweisungstyp: Ein beliebiges STATEMENT, PREPARED oder CALLABLE. Dies bewirkt, dass MyBatis jeweils Statement, PreparedStatement oder CallableStatement verwendet. Standard: PREPARED.

Auf diese Weise können Sie die Verwendung von „Prepare Only“ für SQL mit höherer Frequenz genau steuern und dadurch die Anzahl der verwendeten „Prepare SQL“-Anweisungen kontrollieren und den Speicherverbrauch reduzieren. Leider scheinen die meisten Gruppen derzeit ibatis 2.0 zu verwenden, das statementType nicht unterstützt.
Etikett.

2 Der serverseitige Vorbereitungscache ist eine HASH MAP. Der Schlüssel ist stmt->id, und jede Verbindung verwaltet einen. Daher kann es zu Speicherproblemen kommen, die in der Praxis getestet werden müssen. Bei Bedarf muss es in einen globalen Cache mit dem Schlüssel „SQL“ umgewandelt werden, damit das gleiche vorbereitete SQL von verschiedenen 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 nicht über einen Ausführungsplancache wie Oracle verfügt. Wir haben bereits erwähnt, dass der SQL-Ausführungsprozess die folgenden Phasen umfasst: lexikalische Analyse -> Syntaxanalyse -> semantische Analyse -> Ausführungsplanoptimierung -> 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 Arbeit leistet und die Ausführung nur ausgeführt werden muss. Daher ist die Vorbereitung von Oracle effizienter als MySQL.

Zusammenfassen

Dies ist der gesamte Inhalt dieses Artikels zur detaillierten Erläuterung des MySQL-Vorbereitungsprinzips. Interessierte Freunde können auf dieser Site auf andere verwandte Themen verweisen. Wenn Sie Fragen haben oder Artikel, Bücher oder Quellcodes benötigen, können Sie jederzeit eine Nachricht hinterlassen und der Herausgeber wird Ihnen gerne antworten. Vielen Dank für Ihre Unterstützung dieser Website.