Optimierung korrelierter Unterabfragen in Babelfish - Amazon Aurora

Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

Optimierung korrelierter Unterabfragen in Babelfish

Eine korrelierte Unterabfrage verweist auf Tabellenspalten aus der äußeren Abfrage. Sie wird einmal für jede Zeile ausgewertet, die von der äußeren Abfrage zurückgegeben wird. Im folgenden Beispiel verweist die Unterabfrage auf eine Spalte aus Tabelle t1. Diese Tabelle ist nicht in der Klausel der Unterabfrage enthalten, aber sie wird in der FROM Klausel der äußeren Abfrage referenziert. FROM Wenn Tabelle t1 1 Million Zeilen hat, muss die Unterabfrage 1 Million Mal ausgewertet werden.

SELECT col1, col2 FROM t1 WHERE col1 < (SELECT sum(col1) FROM t2 WHERE t1.col2 = t2.col2);

Verbesserung der Babelfish-Abfrageleistung mithilfe der Transformation von Unterabfragen

Babelfish kann korrelierte Unterabfragen beschleunigen, indem es sie in äquivalente äußere Verknüpfungen umwandelt. Diese Optimierung gilt für die folgenden zwei Arten von korrelierten Unterabfragen:

  • Unterabfragen, die einen einzelnen Aggregatwert zurückgeben und in der Liste erscheinen. SELECT Weitere Informationen finden Sie in der SELECTKlausel in der Microsoft SQL Transact-Dokumentation.

    SELECT ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) FROM outer_sb_t1;
  • Unterabfragen, die einen einzelnen Aggregatwert zurückgeben und in einer WHERE Klausel vorkommen.

    SELECT * FROM outer_sb_t1 WHERE ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) > 1.0;

Transformation in der Unterabfrage aktivieren

Um die Transformation korrelierter Unterabfragen in äquivalente Outer-Joins zu ermöglichen, setzen Sie den apg_enable_correlated_scalar_transform Parameter auf. ON Dieser Parameter ist in Babelfish 4.2.0 und späteren Versionen verfügbar. Der Standardwert für diesen Parameter ist OFF.

Sie können den Cluster oder die Instanzparametergruppe ändern, um die Parameter festzulegen. Weitere Informationen hierzu finden Sie unter Parametergruppen für Amazon Aurora.

Alternativ können Sie die Einstellung nur für die aktuelle Sitzung konfigurieren, indem Sie die Funktion aufrufenset_config. Führen Sie beispielsweise den folgenden Befehl aus, um den Unterabfrage-Cache in Babelfish zu aktivieren. Weitere Informationen finden Sie unter Funktionen der Konfigurationseinstellungen.

1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO

Überprüfung der Transformation

Verwenden Sie den EXPLAIN Befehl, um zu überprüfen, ob die korrelierte Unterabfrage im Abfrageplan in eine äußere Verknüpfung umgewandelt wurde. Weitere Informationen finden Sie unter Verwenden eines Erläuterungsplans zur Verbesserung der Abfrageleistung von Babelfish.

Wenn die Transformation aktiviert ist, wird der entsprechende korrelierte Teil der Unterabfrage in einen Outer-Join umgewandelt. Beispielsweise:

1> select set_config('apg_enable_correlated_scalar_transform', 'true', false); 2> GO 1> set BABELFISH_STATISTICS PROFILE on 2> GO 1> select customer_name, ( select max(o.cost) from correlated_orders o 2> where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount 3> from correlated_customers c order by customer_name ; 4> GO QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Text: select customer_name, ( select max(o.cost) from correlated_orders o where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10 ) AS max_order_amount from correlated_customers c order by customer_name Sort (cost=88.23..90.18 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Hash Left Join (cost=30.90..50.76 rows=780 width=40) Hash Cond: (c.customer_id = o.customer_id) -> Seq Scan on correlated_customers c (cost=0.00..17.80 rows=780 width=36) -> Hash (cost=28.73..28.73 rows=174 width=12) -> HashAggregate (cost=26.99..28.73 rows=174 width=12) Group Key: o.customer_id -> Seq Scan on correlated_orders o (cost=0.00..25.30 rows=338 width=12) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10)) ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Dieselbe Abfrage wird nicht transformiert, wenn der GUC Parameter geändert wird. OFF Der Plan wird keine äußere Verbindung haben, sondern stattdessen einen Unterplan.

1> select set_config('apg_enable_correlated_scalar_transform', 'false', false); 2> GO 1> select customer_name, ( select max(o.cost) 2> from correlated_orders o 3> where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount 4> from correlated_customers c order by customer_name ; 5> GO QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Sort (cost=21789.97..21791.92 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Seq Scan on correlated_customers c (cost=0.00..21752.50 rows=780 width=40) SubPlan 1 -> Aggregate (cost=27.86..27.87 rows=1 width=8) -> Seq Scan on correlated_orders o (cost=0.00..27.85 rows=2 width=8) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10) AND (customer_id = c.customer_id))

Einschränkungen

  • Die Unterabfrage muss sich in der select_list oder in einer der Bedingungen in der WHERE-Klausel befinden. Andernfalls wird sie nicht transformiert.

  • Die Unterabfrage muss eine Aggregatfunktion zurückgeben. Benutzerdefinierte Aggregatfunktionen werden für die Transformation nicht unterstützt.

  • Eine Unterabfrage, deren Rückgabeausdruck keine einfache Aggregatfunktion ist, wird nicht transformiert.

  • Die korrelierte Bedingung in WHERE Unterabfrageklauseln sollte eine einfache Spaltenreferenz sein. Andernfalls wird sie nicht transformiert.

  • Die korrelierte Bedingung in einer Unterabfrage, bei der Klauseln ein einfaches Gleichheitsprädikat sein müssen.

  • Eine korrelierte Unterabfrage, die eine Klausel enthält, kann nicht transformiert werden. TOP

  • Die Unterabfrage darf HAVING weder eine noch eine BY-Klausel enthalten. GROUP

  • Die WHERE-Klausel in der Unterabfrage kann ein oder mehrere Prädikate in Kombination mit enthalten. AND Wenn die WHERE Klausel eine OR-Klausel enthält, kann sie nicht transformiert werden.

Verwenden des Unterabfrage-Caches zur Verbesserung der Babelfish-Abfrageleistung

Ab Version 4.2.0 unterstützt Babelfish den Unterabfrage-Cache zum Speichern der Ergebnisse korrelierter Unterabfragen. Diese Funktion überspringt wiederholte Ausführungen von korrelierten Unterabfragen, wenn sich die Ergebnisse der Unterabfragen bereits im Cache befinden.

Grundlegendes zum Unterabfrage-Cache

SQLDer Memoize-Knoten von Postgre ist der wichtigste Teil des Unterabfrage-Cache. Der Memoize-Knoten verwaltet eine Hashtabelle im lokalen Cache, um Eingabeparameterwerte den Ergebniszeilen der Abfrage zuzuordnen. Das Speicherlimit für die Hash-Tabelle ist das Produkt aus work_mem und hash_mem_multiplier. Weitere Informationen finden Sie unter Ressourcenverbrauch.

Während der Abfrageausführung verwendet der Unterabfrage-Cache die Cache-Trefferrate (CHR), um abzuschätzen, ob der Cache die Abfrageleistung verbessert, und um zur Laufzeit der Abfrage zu entscheiden, ob der Cache weiterhin verwendet werden soll. CHRist das Verhältnis der Anzahl der Cache-Treffer zur Gesamtzahl der Anfragen. Wenn beispielsweise eine korrelierte Unterabfrage 100 Mal ausgeführt werden muss und 70 dieser Ausführungsergebnisse aus dem Cache abgerufen werden können, CHR ist der Wert 0,7.

Für jede Anzahl fehlgeschlagener Cache-Fehler in apg_subquery_cache_check_interval wird der Nutzen des Subquery-Caches bewertet, indem geprüft wird, ob der größer als apg_subquery_cache_hit_rate_threshold ist. CHR Andernfalls wird der Cache aus dem Speicher gelöscht und die Abfrageausführung kehrt zur ursprünglichen, nicht zwischengespeicherten erneuten Ausführung der Unterabfrage zurück.

Parameter, die das Verhalten des Unterabfrage-Caches steuern

In der folgenden Tabelle sind die Parameter aufgeführt, die das Verhalten des Unterabfrage-Caches steuern.

Parameter

Beschreibung

Standard

Zulässig

apg_enable_subquery_cache

Aktiviert die Verwendung von Cache für korrelierte skalare Unterabfragen.

OFF

EIN, OFF

apg_subquery_cache_check_interval

Legt die Häufigkeit (in Anzahl der Cache-Fehlschläge) fest, mit der die Trefferrate für den Cache von Unterabfragen ausgewertet wird.

500

0 – 2147483647

apg_subquery_cache_hit_rate_threshold

Legt den Schwellenwert für die Trefferrate des Unterabfrage-Caches fest.

0.3

0,0—1,0
Anmerkung
  • Größere Werte von apg_subquery_cache_check_interval können die Genauigkeit der auf der Grundlage der Schätzung CHR des Cache-Nutzens ermitteln, erhöhen jedoch den Cache-Overhead, da CHR sie erst ausgewertet werden, wenn die Cache-Tabelle Zeilen enthält. apg_subquery_cache_check_interval

  • Größere Werte bedeuten, apg_subquery_cache_hit_rate_threshold dass der Unterabfrage-Cache aufgegeben und zur ursprünglichen, nicht zwischengespeicherten erneuten Ausführung der Unterabfrage zurückgekehrt wird.

Sie können den Cluster oder die Instance-Parametergruppe ändern, um die Parameter festzulegen. Weitere Informationen finden Sie unter Mit Parametergruppen arbeiten.

Alternativ können Sie die Einstellung nur für die aktuelle Sitzung konfigurieren, indem Sie die Funktion aufrufenset_config. Führen Sie beispielsweise den folgenden Befehl aus, um den Unterabfrage-Cache in Babelfish zu aktivieren. Weitere Informationen finden Sie unter Funktionen der Konfigurationseinstellungen.

1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO

Den Unterabfrage-Cache in Babelfish einschalten

Derzeit ist der Unterabfrage-Cache standardmäßig aktiviert. OFF Wie oben erwähnt, können Sie ihn aktivieren, indem Sie Ihre Parametergruppe ändern. Wenn dies apg_enable_subquery_cache der Fall istON, wendet Babelfish den Unterabfrage-Cache an, um die Ergebnisse von Unterabfragen zu speichern. Unter dem Abfrageplan befindet sich dann ein Memoize-Knoten. SubPlan

Die folgende Befehlssequenz zeigt beispielsweise den voraussichtlichen Abfrageausführungsplan einer einfachen korrelierten Unterabfrage ohne Unterabfrage-Cache. Weitere Informationen finden Sie unter Verwenden des Explain-Plans zur Verbesserung der Babelfish-Abfrageleistung.

1> CREATE TABLE outer_table (outer_col1 INT, outer_col2 INT) 2> CREATE TABLE inner_table (inner_col1 INT, inner_col2 INT) 3> GO 1> EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'off' 2> GO 1> SET BABELFISH_SHOWPLAN_ALL ON 2> GO 1> SELECT outer_col1, ( 2> SELECT inner_col1 3> FROM inner_table 4> WHERE inner_col2 = outer_col2 5> ) FROM outer_table 6> GO QUERY PLAN ------------------------------------------------------------ Query Text: SELECT outer_col1, ( SELECT inner_col1 FROM inner_table WHERE inner_col2 = outer_col2 ) FROM outer_table Seq Scan on outer_table SubPlan 1 -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)
1> SET BABELFISH_SHOWPLAN_ALL OFF 2> GO 1> EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'on' 2> GO

Nach dem apg_enable_subquery_cache Einschalten enthält der Abfrageplan einen Memoize-Knoten unter dem Knoten, der SubPlan darauf hinweist, dass die Unterabfrage beabsichtigt, den Cache zu verwenden.

Seq Scan on outer_table SubPlan 1 -> Memoize Cache Key: outer_table.outer_col2 Cache Mode: logical -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)

Der eigentliche Abfrageausführungsplan enthält weitere Details zum Unterabfrage-Cache, einschließlich Cache-Treffer und Cache-Fehlschläge. Die folgende Ausgabe zeigt den tatsächlichen Abfrageausführungsplan der obigen Beispielabfrage nach dem Einfügen einiger Werte in die Tabellen.

Seq Scan on outer_table (actual rows=10 loops=1) SubPlan 1 -> Memoize (actual rows=1 loops=10) Cache Key: outer_table.outer_col2 Cache Mode: logical Hits: 4 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Seq Scan on inner_table (actual rows=1 loops=6) Filter: (inner_col2 = outer_table.outer_col2) Rows Removed by Filter: 4

Die Gesamtzahl der Cache-Treffer ist 4, und die Gesamtzahl der Cache-Fehlschläge ist 6. Wenn die Gesamtzahl der Treffer und Fehlschläge geringer ist als die Anzahl der Schleifen im Memoize-Knoten, bedeutet dies, dass die CHR Auswertung nicht bestanden wurde und der Cache irgendwann bereinigt und verlassen wurde. Die Ausführung der Unterabfrage kehrte dann zur ursprünglichen, nicht zwischengespeicherten Neuausführung zurück.

Einschränkungen

Der Unterabfrage-Cache unterstützt bestimmte Muster korrelierter Unterabfragen nicht. Diese Arten von Abfragen werden ohne Cache ausgeführt, auch wenn der Unterabfrage-Cache aktiviert ist:

  • EXISTSANYIN///korrelierte Unterabfragen ALL

  • Korrelierte Unterabfragen, die nichtdeterministische Funktionen enthalten.

  • Korrelierte Unterabfragen, die auf eine äußere Tabellenspalte oder einen Datentyp verweisen. BIT VARBINARY BINARY