SQLKonstrukte für parallel Abfragen in Aurora My SQL

Im folgenden Abschnitt finden Sie weitere Informationen darüber, warum bestimmte SQL Anweisungen parallel Abfragen verwenden oder nicht. In diesem Abschnitt wird auch beschrieben, wie die SQL Funktionen von Aurora My mit parallel Abfragen interagieren. Mit Hilfe dieser Informationen können Sie Leistungsprobleme in Clustern diagnostizieren, die Parallel Query verwenden. Außerdem können Sie nachvollziehen, wie Parallel Query auf Ihren spezifischen Workload angewendet wird.

Die Entscheidung für Parallel Query hängt von vielen Faktoren ab, die zu dem Zeitpunkt wirken, an dem die Anweisung ausgeführt wird. Es kann also sein, dass Parallel Query für bestimmte Abfragen immer, nie oder nur unter bestimmten Bedingungen zum Einsatz kommt.

Tipp

Wenn Sie sich diese Beispiele unter ansehenHTML, können Sie das Widget „Kopieren“ in der oberen rechten Ecke jeder Codeliste verwenden, um den SQL Code zu kopieren und es selbst auszuprobieren. Durch die Verwendung des Widgets Copy (Kopieren) wird ein Kopieren der zusätzlichen Zeichen um die Aufforderung mysql> und die Fortsetzungszeilen -> vermieden.

EXPLAINAussage

Wie aus den Beispielen aus diesem Abschnitt hervorgeht, gibt die EXPLAIN-Anweisung an, ob die jeweilige Stufe einer Abfrage derzeit parallelabfragetauglich ist. Darüber hinaus gibt sie auch an, welche Aspekte einer Abfrage auf die nächste Speicherschicht herabgestuft werden können. Die wichtigsten Bestandteile im Abfrageplan sind folgende:

• Wenn in der NULL-Spalte ein anderer Wert als key steht, liegt nahe, dass die Abfrage mit Index-Lookups effizient möglich ist. Der Einsatz von Parallel Query ist unwahrscheinlich.

• Wenn in der Spalte rows ein kleiner Wert steht (d. h. nicht im Millionenbereich), kann dies bedeuten, dass für die Abfrage zu wenig Daten verfügbar sind, als dass sich eine Parallelabfrage lohnen würde. Das bedeutet, dass eine parallele Abfrage unwahrscheinlich ist.

• Die Spalte Extra zeigt an, ob mit einer Parallelabfrage zu rechnen ist. Die Ausgabe kann in etwa wie im folgenden Beispiel aussehen.

  Using parallel query (A columns, B filters, C exprs; D extra)
  

  Die Zahl vor columns gibt an, wie viele Spalten im Abfrageblock hinterlegt sind.

  Die Zahl vor filters gibt an, wie viele WHERE-Prädikate einen einfachen Vergleich zwischen Spaltenwert und einer Konstante darstellen. Es können Größen wie „Gleichheit“ und „Ungleichheit“, aber auch Bereiche verglichen werden. Aurora parallelisiert diese Prädikattypen am effektivsten.

  Die Zahl vor exprs gibt die Anzahl der Ausdrücke (z. B. Funktionsaufrufe, Operatoren oder sonstige Ausdrücke) an, die auch parallelisiert werden können – wenn auch nicht so effektiv wie eine Filterbedingung.

  Die Zahl vor extra gibt an, wie viele Ausdrücke nicht herabgestuft werden können und deshalb vom Hauptknoten ausgeführt werden.

Betrachten Sie dazu die folgende EXPLAIN-Ausgabe.

mysql> explain select p_name, p_mfgr from part
    -> where p_brand is not null
    -> and upper(p_type) is not null
    -> and round(p_retailprice) is not null;
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+
| id | select_type | table |...| rows     | Extra                                                                      |
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+
|  1 | SIMPLE      | part  |...| 20427936 | Using where; Using parallel query (5 columns, 1 filters, 2 exprs; 0 extra) |
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+

Aus der Extra-Spalte geht hervor, dass aus jeder Zeile fünf Spalten extrahiert werden, mit denen die Abfragebedingungen bewertet und der Ergebnissatz konstruiert werden. Ein WHERE-Prädikat beinhaltet einen Filter – in diesem Fall eine Spalte, die direkt in der WHERE-Klausel getestet wird. Kommen zwei WHERE-Klauseln vor, müssen kompliziertere Ausdrücke bewertet werden (die in diesem Fall Funktionsaufrufe beinhalten). Das Feld 0 extra bestätigt, dass alle Operationen in der WHERE-Klausel im Zuge der Parallelabfrageverarbeitung in die Speicherschicht herabgestuft werden.

Wird Parallel Query nicht ausgewählt, lässt sich der Grund dafür meist aus den anderen Spalten der EXPLAIN-Ausgabe ableiten. So kann möglicherweise sein, dass der rows-Wert zu klein ist oder dass die Spalte possible_keys angibt, dass die Abfrage statt auf einen datenintensiven Scan auf einen Index-Lookup zurückgreift. Das folgende Beispiel zeigt eine Abfrage, bei der der Optimierer schätzen kann, dass die Abfrage nur eine kleine Anzahl von Zeilen durchsucht. Dies erfolgt auf der Basis der Eigenschaften des Primärschlüssels. Eine Parallelabfrage ist deshalb nicht erforderlich.

mysql> explain select count(*) from part where p_partkey between 1 and 100;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | part  | range | PRIMARY       | PRIMARY | 4       | NULL |   99 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

Die Ausgabe mit Auskunft zur Verwendung von Parallel Query berücksichtigt alle Faktoren, die zu dem Zeitpunkt verfügbar waren, als die EXPLAIN-Anweisung ausgeführt wurde. Möglicherweise trifft der Optimierer zum Zeitpunkt der tatsächlichen Abfrageausführung eine andere Auswahl, wenn bis dahin eine andere Ausgangslage herrscht. EXPLAIN könnte beispielsweise melden, dass eine Anweisung Parallel Query verwendet. Wird die Abfrage später dann tatsächlich ausgeführt, könnte sein, dass Parallel Query aufgrund der dann vorliegenden Bedingungen nicht nötig ist. Zu solchen Bedingungen können mehrere weitere parallele Abfragen gehören, die gleichzeitig ausgeführt werden. Es kann sich auch um Zeilen handeln, die aus der Tabelle gelöscht werden, um einen neuen Index, der erstellt wird, um zu viel Zeit, die in einer offenen Transaktion verstreicht, usw.

WHERE-Klausel

Damit die parallele Abfrageausführung genutzt werden kann, muss die Abfrage eine WHERE-Klausel enthalten.

Die parallele Abfrageausführung beschleunigt viele unterschiedliche Ausdrücke, die in der WHERE-Klausel vorkommen:

• Einfache Abgleiche zwischen einem Spaltenwert und einer Konstante (Filter). Bei diesen Abgleichen lohnt sich die Herabstufung auf die Speicherschicht am meisten. Wie viele Filterausdrücke in einer Abfrage vorkommen, ist in der EXPLAIN-Ausgabe zusammengefasst.

• Wo dies möglich ist, werden auch andere Ausdrücke aus der WHERE-Klausel in die Speicherschicht hinabgestuft. Wie viele solche Ausdrücke in einer Abfrage vorkommen, ist in der EXPLAIN-Ausgabe zusammengefasst. Bei diesen Ausdrücken kann es sich um Funktionsaufrufe, LIKE-Operatoren, CASE-Ausdrücke und Ähnliches handeln.

• Bestimmte Funktionen und Operatoren werden von Parallel Query derzeit noch nicht herabgestuft. Wie viele solche Ausdrücke in einer Abfrage vorkommen, wird mit dem extra-Zähler in der EXPLAIN-Ausgabe angegeben. Der Rest der Abfrage verwendet Parallel Query.

• Ausdrücke aus der Auswahlliste werden nicht herabgestuft. Für Abfragen mit solchen Funktionen kann es sich aber positiv auswirken, dass die Zwischenergebnisse von Parallelabfragen weniger Netzwerkdatenverkehr verursachen. So können beispielsweise Abfragen, die Aggregationsfunktionen aus der Auswahlliste aufrufen, von Parallelabfragen profitieren, selbst wenn die Aggregationsfunktionen selbst nicht herabgestuft werden.

Die nachfolgend abgebildete Abfrage scannt beispielsweise die gesamte Tabelle und verarbeitet alle Werte aus der P_BRAND-Spalte. Es handelt sich dabei allerdings nicht um eine Parallelabfrage, weil sie keine WHERE-Klausel enthält.

mysql> explain select count(*), p_brand from part group by p_brand;
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+
|  1 | SIMPLE      | part  | ALL  | NULL          | NULL | NULL    | NULL | 20427936 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+

Die nächste Abfrage enthält hingegen WHERE-Prädikate, die die Ergebnisse filtern. Deshalb ist eine Parallelabfrage möglich:

mysql> explain select count(*), p_brand from part where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000
    -> group by p_brand;
+----+...+----------+-------------------------------------------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                                                       |
+----+...+----------+-------------------------------------------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using temporary; Using filesort; Using parallel query (5 columns, 1 filters, 2 exprs; 0 extra) |
+----+...+----------+-------------------------------------------------------------------------------------------------------------+

Wenn der Optimierer davon ausgeht, dass zu einem Abfrageblock nur wenige Zeilen zurückgegeben werden, wird Parallel Query für diesen Abfrageblock nicht verwendet. Im nachfolgenden Beispiel wird ein Größer-als-Operator in der Primärschlüssel-Spalte auf mehrere Millionen Zeilen angewendet. Das sorgt dafür, dass Parallel Query verwendet wird. Dagegen wird davon ausgegangen, dass die Kleiner-als-Gegenprobe nur auf wenige Zeilen anwendbar ist. Parallel Query kommt nicht zum Einsatz.

mysql> explain select count(*) from part where p_partkey > 10;
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+

mysql> explain select count(*) from part where p_partkey < 10;
+----+...+------+--------------------------+
| id |...| rows | Extra                    |
+----+...+------+--------------------------+
|  1 |...|    9 | Using where; Using index |
+----+...+------+--------------------------+

Sprache der Datendefinition (DDL)

In Aurora My SQL Version 2 ist die parallel Abfrage nur für Tabellen verfügbar, für die keine Fast Data Definition Language (DDL) -Operationen ausstehen. In Aurora My SQL Version 3 können Sie die parallel Abfrage für eine Tabelle gleichzeitig mit einer DDL Sofortoperation verwenden.

Instant DDL in Aurora My SQL Version 3 ersetzt die DDL Schnellfunktion in Aurora My SQL Version 2. Informationen zu Instant finden DDL Sie unterSofortige DDL (Aurora MySQL Version 3).

Spaltendatentypen

In Aurora My SQL Version 3 kann die parallel Abfrage mit Tabellen funktionieren, die Spalten mit den DatentypenTEXT, BLOBJSON, und enthaltenGEOMETRY. Es kann auch mit VARCHAR- und CHAR-Spalten mit einer maximalen deklarierten Länge von mehr als 768 Byte arbeiten. Wenn sich Ihre Abfrage auf Spalten bezieht, die so große Objekttypen enthalten, erhöht die zusätzliche Arbeit zum Abrufen der Abfrageverarbeitung einen gewissen Aufwand. Überprüfen Sie in diesem Fall, ob die Abfrage die Verweise auf diese Spalten weglassen kann. Wenn dies nicht der Fall ist, führen Sie Benchmarks aus, um zu bestätigen, ob solche Abfragen schneller sind, wenn die parallele Abfrage aktiviert oder deaktiviert ist.

In Aurora My SQL Version 2 gelten für parallel Abfragen die folgenden Einschränkungen für große Objekttypen:

• Die Datentypen TEXT, BLOB, JSON und GEOMETRY werden von parallelen Abfragen nicht unterstützt. Eine Abfrage, die sich auf Spalten dieses Typs bezieht, kann keine parallelen Abfragen nutzen.

• Spalten unterschiedlicher Länge (Datentypen VARCHAR und CHAR) sind bis zur deklarierten Länge (max. 768 Byte) mit Parallel Query kompatibel. Eine Abfrage, die sich auf Spalten der Typen bezieht, die mit mehr Höchstlänge deklariert sind, kann Parallel Query nicht nutzen. In Spalten, in denen Zeichensätze mit mehreren Byte Länge vorkommen, ist im Byte-Höchstwert die maximale Byte-Anzahl des Zeichensatzes berücksichtigt. Für eine utf8mb4-Spalte mit dem Zeichensatz VARCHAR(192) (maximal 4 Byte Zeichenlänge) kann beispielsweise eine Parallelabfrage gestartet werden, nicht jedoch für eine VARCHAR(193)-Spalte.

Partitionierte Tabellen

In Aurora My SQL Version 3 können Sie partitionierte Tabellen mit parallel Abfrage verwenden. Da partitionierte Tabellen intern als mehrere kleinere Tabellen dargestellt werden, verwendet eine Abfrage, die eine parallele Abfrage für eine nicht partitionierte Tabelle verwendet, möglicherweise keine parallele Abfrage für eine identische partitionierte Tabelle. Aurora My SQL prüft, ob jede Partition groß genug ist, um sich für die parallel Abfrageoptimierung zu qualifizieren, anstatt die Größe der gesamten Tabelle zu bewerten. Prüfen Sie, ob die Statusvariable Aurora_pq_request_not_chosen_small_table inkrementiert wird, wenn eine Abfrage in einer partitionierten Tabelle die parallele Abfrage nicht wie erwartet verwendet.

Betrachten Sie beispielsweise eine Tabelle, die mit PARTITION BY HASH (column) PARTITIONS 2 partitioniert ist, und eine andere Tabelle, die mit PARTITION BY HASH (column) PARTITIONS 10 partitioniert ist. In der Tabelle mit zwei Partititionen sind die Partitionen fünfmal so groß wie die Tabelle mit zehn Partitionen. Daher wird eine parallele Abfrage eher für Abfragen gegen die Tabelle mit weniger Partitionen verwendet. Im folgenden Beispiel hat die Tabelle PART_BIG_PARTITIONS zwei Partitionen und PART_SMALL_PARTITIONS hat zehn Partitionen. Bei identischen Daten wird eine parallele Abfrage eher für die Tabelle mit weniger großen Partitionen verwendet.

mysql> explain select count(*), p_brand from part_big_partitions where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000 group by p_brand;
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+
| id | select_type | table               | partitions | Extra                                                                                                             |
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | part_big_partitions | p0,p1      | Using where; Using temporary; Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra; 1 group-bys, 1 aggrs) |
+----+-------------+---------------------+------------+-------------------------------------------------------------------------------------------------------------------+

mysql> explain select count(*), p_brand from part_small_partitions where p_name is not null
    ->   and p_mfgr in ('Manufacturer#1', 'Manufacturer#3') and p_retailprice > 1000 group by p_brand;
+----+-------------+-----------------------+-------------------------------+------------------------------+
| id | select_type | table                 | partitions                    | Extra                        |
+----+-------------+-----------------------+-------------------------------+------------------------------+
|  1 | SIMPLE      | part_small_partitions | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | Using where; Using temporary |
+----+-------------+-----------------------+-------------------------------+------------------------------+

Aggregatfunktionen, GROUP BY-Klauseln und HAVING Klauseln

In Abfragen mit Aggregationsfunktionen werden große Tabellen mit sehr vielen Zeilen gescannt. Sie eignen sich deshalb oft besonders gut für Parallelabfragen.

In Aurora My SQL 3 kann die parallel Abfrage aggregierte Funktionsaufrufen in der Auswahlliste und der HAVING Klausel optimieren.

Vor Aurora My SQL 3 wurden Aggregatfunktionsaufrufen in der Auswahlliste oder der HAVING Klausel nicht auf die Speicherebene übertragen. Eine parallele Abfrage kann dennoch die Leistung solcher Abfragen mit Aggregationsfunktionen verbessern – indem sie zuerst auf der Speicherschicht Spaltenwerte aus den Rohdatenseiten parallel extrahiert. Anschließend überträgt Parallel Query diese Werte in einem kompakten Tupelformat an den Hauptknoten zurück und nicht als vollständige Datenseiten. Wie immer muss die Abfrage mindestens 1 WHERE-Prädikat enthalten, das für Parallel Query aktiviert ist.

Die nachfolgenden einfach gehaltenen Beispiele veranschaulichen, welche aggregierte Abfragen von Parallel Query profitieren. Dies gilt zum einen durch Rückgabe unmittelbarer, kompakt gehaltener Ergebnisse an den Hauptknoten und eventuell zusätzlich durch Herausfilterung nicht passender Zeilen aus den Zwischenergebnissen.

mysql> explain select sql_no_cache count(distinct p_brand) from part where p_mfgr = 'Manufacturer#5';
+----+...+----------------------------------------------------------------------------+
| id |...| Extra                                                                      |
+----+...+----------------------------------------------------------------------------+
|  1 |...| Using where; Using parallel query (2 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------------------------------------------------------------------------+

mysql> explain select sql_no_cache p_mfgr from part where p_retailprice > 1000 group by p_mfgr having count(*) > 100;
+----+...+-------------------------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                                       |
+----+...+-------------------------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using filesort; Using parallel query (3 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+-------------------------------------------------------------------------------------------------------------+

Funktionsaufrufe in der WHERE Klausel

Aurora kann die Optimierung der parallelen Abfrageausführung auf Aufrufe der meisten integrierten Funktionen der WHERE-Klausel anwenden. Durch die Parallelisierung dieser Funktionsaufrufen wird dem Hauptknoten ein Teil der CPU Arbeit entzogen. Die parallel ablaufende Bewertung der Prädikatfunktionen in der ersten Abfragephase ermöglicht es Aurora, die Datenmenge zu minimieren, die im späteren Verlauf übertragen und verarbeitet werden muss.

Aktuell werden nicht alle Funktionsaufrufe aus der Auswahlliste parallelisiert. Diese Funktionen werden vom Hauptknoten auch dann bewertet, wenn in der WHERE-Klausel dieselben Funktionsaufrufe enthalten sind. Die ursprünglichen Werte aus betroffenen Spalten werden in die Tupel aufgenommen, die vom Speicherknoten zum Hauptknoten zurückübertragen werden. Der Hauptknoten führt alle Transformationen aus, z. B. UPPER, CONCATENATE usw., und generiert die endgültigen Werte für den Abfragesatz.

Im folgenden Beispiel parallelisiert Parallel Query den Aufruf von LOWER, da diese Funktion in der WHERE-Klausel enthalten ist. Parallel Query wirkt sich nicht auf die Aufrufe von SUBSTR und UPPER aus, da sie in der Auswahlliste stehen.

mysql> explain select sql_no_cache distinct substr(upper(p_name),1,5) from part
    -> where lower(p_name) like '%cornflower%' or lower(p_name) like '%goldenrod%';
+----+...+---------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                       |
+----+...+---------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+---------------------------------------------------------------------------------------------+

Die gleichen Überlegungen gelten für andere Ausdrücke wie CASE-Ausdrücke oder LIKE-Operatoren. Im der folgenden Beispiel ist beispielsweise zu sehen, dass die parallele Abfrage in der CASE-Klausel den LIKE-Ausdruck und die WHERE-Operatoren evaluiert.

mysql> explain select p_mfgr, p_retailprice from part
    -> where p_retailprice > case p_mfgr
    ->   when 'Manufacturer#1' then 1000
    ->   when 'Manufacturer#2' then 1200
    ->   else 950
    -> end
    -> and p_name like '%vanilla%'
    -> group by p_retailprice;
+----+...+-------------------------------------------------------------------------------------------------------------+
| id |...| Extra                                                                                                       |
+----+...+-------------------------------------------------------------------------------------------------------------+
|  1 |...| Using where; Using temporary; Using filesort; Using parallel query (4 columns, 0 filters, 2 exprs; 0 extra) |
+----+...+-------------------------------------------------------------------------------------------------------------+

LIMIT-Klausel

Derzeit können Abfrageblöcke mit LIMIT-Klausel nicht parallel abgefragt werden. Parallel Query könnte jedoch für frühere Abfragephasen mit GROUP BY, ORDER BY oder Join-Abfragen geeignet sein.

Vergleichsoperatoren

Der Optimierer schätzt ab, wie viele Zeilen gescannt werden müssen, um die Vergleichsoperatoren bewerten zu können, und entscheidet ausgehend von dieser Schätzung, ob Parallel Query verwendet wird.

Das erste Beispiel belegt, dass ein Gleichheitsvergleich gegen die Hauptschlüsselspalte effizient ohne Parallel Query möglich ist. Das zweite Beispiel belegt, dass für einen ähnlichen Vergleich gegen eine nicht indizierte Spalte mehrere Millionen Zeilen gescannt werden müssen. Parallel Query lohnt sich deshalb.

mysql> explain select * from part where p_partkey = 10;
+----+...+------+-------+
| id |...| rows | Extra |
+----+...+------+-------+
|  1 |...|    1 | NULL  |
+----+...+------+-------+

mysql> explain select * from part where p_type = 'LARGE BRUSHED BRASS';
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (9 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+

Die gleichen Kriterien gelten für "ist ungleich"-Prüfungen und Bereichsvergleiche (z. B. kleiner als, größer als, ist gleich oder BETWEEN. Der Optimierer schätzt ab, wie viele Zeilen zu scannen sind, und entscheidet dann, ob sich eine parallele Abfrage angesichts des E/A-Gesamt-Volumens lohnt.

Joins

Join-Abfragen mit großen Tabellen sind in der Regel datenintensive Operationen, die von der Optimierung der parallelen Abfrageausführung profitieren. Derzeit werden Vergleiche von Spaltenwerten aus mehreren Tabellen (also die Join-Prädikate) nicht parallelisiert. Parallel Query kann allerdings einen Teil der internen Verarbeitung aus anderen Join-Phasen herunterstufen – z. B. die Erstellung des Bloom-Filters während eines Hash-Join. Parallel Query kann auch ohne WHERE-Klausel auf Join-Abfragen angewendet werden. Join-Abfragen sind in dieser Hinsicht Ausnahmen von der Regel, dass Parallelabfragen ohne WHERE-Klausel nicht möglich sind.

Jede Phase der Join-Verarbeitung wird ausgewertet, um festzustellen, ob sie für eine Parallelabfrage in Frage kommt. Falls mehrere Phasen parallelabfragetauglich sind, werden sie nacheinander ausgeführt. Was die Obergrenze für gleichzeitige Abfragen angeht, bedeutet dies, dass jede Join-Abfrage als separate Parallelabfragesitzung zählt.

Wenn eine Join-Abfrage beispielsweise mit WHERE-Prädikaten die Zeilen einer verknüpften Tabelle filtert, kann diese Filteroption Parallel Query verwenden. Ein weiteres Beispiel ist die Verknüpfung einer großen mit einer kleinen Tabelle mittels Hash-Join in einer Join-Abfrage. In diesem Fall kann der Tabellenscan für die Generierung der Datenstruktur des Bloom-Filters Parallel Query möglicherweise verwenden.

Anmerkung

Parallele Abfragen werden typischerweise für ressourcenintensive Abfragen verwendet, die von der Hash-Join-Optimierung profitieren. Die Methode zum Aktivieren der Hash-Join-Optimierung hängt von der Aurora SQL My-Version ab. Einzelheiten zu den einzelnen Versionen finden Sie unter Hash-Join für parallele Abfrage-Cluster aktivieren. Informationen zur effektiven Verwendung von Hash-Joins finden Sie unter Optimierung großer Aurora My SQL Join-Abfragen mit Hash-Joins.

mysql> explain select count(*) from orders join customer where o_custkey = c_custkey;
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+
| id |...| table    | type  | possible_keys | key         |...| rows      | Extra                                                                                                           |
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+
|  1 |...| customer | index | PRIMARY       | c_nationkey |...|  15051972 | Using index                                                                                                     |
|  1 |...| orders   | ALL   | o_custkey     | NULL        |...| 154545408 | Using join buffer (Hash Join Outer table orders); Using parallel query (1 columns, 0 filters, 1 exprs; 0 extra) |
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+

In Join-Abfragen, die mit geschachtelten Schleifen arbeiten, könnte der äußerste geschachtelte Schleifenblock parallele Abfragen verwenden. Die Anwendung von parallelen Abfragen hängt von den gleichen Faktoren wie immer ab, z. B. dem Vorhandensein zusätzlicher Filterbedingungen in der WHERE-Klausel.

mysql> -- Nested loop join with extra filter conditions can use parallel query.
mysql> explain select count(*) from part, partsupp where p_partkey != ps_partkey and p_name is not null and ps_availqty > 0;
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+
| id | select_type | table    |...| rows     | Extra                                                                      |
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+
|  1 | SIMPLE      | part     |...| 20427936 | Using where; Using parallel query (2 columns, 1 filters, 0 exprs; 0 extra) |
|  1 | SIMPLE      | partsupp |...| 78164450 | Using where; Using join buffer (Block Nested Loop)                         |
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+

Unterabfragen

Der äußere Abfrageblock und der innere Unterabfrageblock können jeweils parallele Abfragen verwenden oder nicht. Ob sie dies tun, ist für jeden Block von den gewöhnlichen Eigenschaften der Tabelle, der WHERE-Klausel usw. abhängig. In der folgenden Abfrage kommt Parallel Query am Unterabfrageblock zum Einsatz, nicht jedoch am Außenblock.

mysql> explain select count(*) from part where
   --> p_partkey < (select max(p_partkey) from part where p_name like '%vanilla%');
+----+-------------+...+----------+----------------------------------------------------------------------------+
| id | select_type |...| rows     | Extra                                                                      |
+----+-------------+...+----------+----------------------------------------------------------------------------+
|  1 | PRIMARY     |...|     NULL | Impossible WHERE noticed after reading const tables                        |
|  2 | SUBQUERY    |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
+----+-------------+...+----------+----------------------------------------------------------------------------+

Für korrelierte Unterabfragen steht die Optimierung der parallelen Abfrageausführung nicht zur Verfügung.

UNION

Jeder Abfrageblock einer UNION-Abfrage kann Parallel Query nutzen (oder nicht). Ausschlaggebend sind die Tabelleneigenschaften und die WHERE-Klausel usw. des jeweiligen UNION-Teils.

mysql> explain select p_partkey from part where p_name like '%choco_ate%'
    -> union select p_partkey from part where p_name like '%vanil_a%';
+----+----------------+...+----------+----------------------------------------------------------------------------+
| id | select_type    |...| rows     | Extra                                                                      |
+----+----------------+...+----------+----------------------------------------------------------------------------+
|  1 | PRIMARY        |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
|  2 | UNION          |...| 20427936 | Using where; Using parallel query (2 columns, 0 filters, 1 exprs; 0 extra) |
| NULL | UNION RESULT | <union1,2> |...|     NULL | Using temporary                                           |
+----+--------------+...+----------+----------------------------------------------------------------------------+

Anmerkung

Die UNION-Klauseln in der Abfrage werden nacheinander ausgeführt. Selbst wenn die Abfrage mehrstufig ist und in jeder Phase Parallel Query zum Einsatz kommt, führt sie Parallel Query jedes Mal separat aus. Deshalb gilt mit Hinblick auf die Höchstzahl gleichzeitig zulässiger Parallelabfragen auch eine komplexe mehrstufige Abfrage nur als 1 Abfrage.

Ansichten

Der Optimierer verwendet beim Umschreiben jeder Abfrage eine Ansicht als längere Abfrage. Dazu werden die zugrunde liegenden Tabellen verwendet. Das bedeutet, dass die Funktionsweise von Parallel Query unabhängig von der Tabellenreferenz (Ansicht oder tatsächliche Tabelle) gleich ist. Für die fertige umgeschriebene Abfrage gelten die gleichen Kriterien hinsichtlich der Verwendung von Parallel Query für eine Abfrage und hinsichtlich der Herabstufung einzelner Teile.

Beispielsweise zeigt der folgende Abfrageplan eine Ansichtsdefinition, die Parallel Query in der Regel nicht verwendet. Wenn die Ansicht mit zusätzlichen WHERE Klauseln abgefragt wird, SQL verwendet Aurora My eine parallel Abfrage.

mysql> create view part_view as select * from part;
mysql> explain select count(*) from part_view where p_partkey is not null;
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (1 columns, 0 filters, 0 exprs; 1 extra) |
+----+...+----------+----------------------------------------------------------------------------+

Anweisungen in der Datenmanipulationssprache () DML

Die INSERT-Anweisung kann Parallel Query für die SELECT-Verarbeitungsphase verwenden, wenn der SELECT-Teil die sonstigen Bedingungen für eine Parallelabfrage erfüllt.

mysql> create table part_subset like part;
mysql> explain insert into part_subset select * from part where p_mfgr = 'Manufacturer#1';
+----+...+----------+----------------------------------------------------------------------------+
| id |...| rows     | Extra                                                                      |
+----+...+----------+----------------------------------------------------------------------------+
|  1 |...| 20427936 | Using where; Using parallel query (9 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+----------+----------------------------------------------------------------------------+

Anmerkung

In der Regel liegen die Daten zu den neu eingefügten Zeilen nach der INSERT-Anweisung im Bufferpool. Deshalb kann sein, dass eine Tabelle unmittelbar nach dem Einfügen vieler Zeilen nicht für Parallel Query in Frage kommt. Erst wenn die Daten im normalen Betrieb aus dem Bufferpool entfernt wurden, können in Abfragen gegen die Tabelle wieder Parallelabfragen zum Einsatz kommen.

Die CREATE TABLE AS SELECT-Anweisung greift nicht auf Parallel Query zurück – auch dann nicht, wenn der SELECT-Teil der Anweisung ansonsten parallelabfragefähig ist. Der DDL Aspekt dieser Anweisung macht sie mit der parallel Abfrageverarbeitung nicht kompatibel. Dagegen kann der INSERT ... SELECT-Teil der SELECT-Anweisung Parallel Query verwenden.

Parallel Query wird nie verwendet, wenn DELETE- oder UPDATE-Anweisungen vorliegen. Dies gilt unabhängig von der Größe der Tabelle und der Prädikate aus der WHERE-Klausel.

mysql> explain delete from part where p_name is not null;
+----+-------------+...+----------+-------------+
| id | select_type |...| rows     | Extra       |
+----+-------------+...+----------+-------------+
|  1 | SIMPLE      |...| 20427936 | Using where |
+----+-------------+...+----------+-------------+

Transaktionen und Sperren

Sie können alle Isolationsebenen auf der primären Aurora-Instance verwenden.

Auf Aurora-Reader-DB-Instances gilt die parallele Abfrage für Anweisungen, die unter der REPEATABLE READ-Isolierungsstufe ausgeführt werden. Aurora My SQL Version 2.09 oder höher kann auch die READ COMMITTED Isolationsstufe für Reader-DB-Instances verwenden. REPEATABLE READist die Standard-Isolationsstufe für Aurora-Reader-DB-Instances. Um die Isolierungsstufe READ COMMITTED auf DB-Leser-Instances anzuwenden, muss die Konfigurationsoption aurora_read_replica_read_committed auf Sitzungsebene festgelegt werden. Die READ COMMITTED Isolationsstufe für Reader-Instances entspricht dem SQL Standardverhalten. Die Isolierung ist jedoch bei Reader-Instances weniger streng als bei Abfragen, welche die READ COMMITTED-Isolierungsstufe bei der Writer-Instance verwenden.

Weitere Informationen zu Aurora-Isolierungsstufen, insbesondere zu den Unterschieden in READ COMMITTED zwischen Writer- und Reader-Instances, finden Sie unter Aurora Meine SQL Isolationsstufen.

Nach Abschluss einer großen Transaktion kann es sein, dass die Tabellenstatistik veraltet ist. Für solche Statistiken ist möglicherweise eine ANALYZE TABLE-Anweisung erforderlich, damit Aurora die Zeilenzahl zuverlässig schätzen kann. Eine umfangreiche DML Anweisung könnte auch einen erheblichen Teil der Tabellendaten in den Pufferpool bringen. Sind diese Daten im Bufferpool kann sein, dass diese Tabelle weniger häufig von Parallel Query abgefragt wird. Dies ändert sich, wenn die Daten aus dem Pool entfernt sind.

Wenn Ihre Sitzung Teil einer langwierigen Transaktion (standardmäßig 10 Minuten) ist, verwenden weitere Abfragen aus dieser Sitzung keine Parallelabfragen. Eine einzelne lang laufende Abfrage kann auch wegen Zeitüberschreitung abgebrochen werden. Dieser Überschreitungsabbruch kann eintreten, wenn die Abfrage länger läuft als zulässig (Höchstdauer derzeit: 10 Minuten), bevor die Parallelverarbeitung der Abfragen beginnt.

Das Risiko, dass lang laufende Transaktionen ungewollt gestartet werden, kann reduziert werden. Legen Sie dazu in autocommit=1-Sitzungen, in denen Sie Ad-hoc-Abfragen durchführen, die Einstellung mysql fest. Selbst eine SELECT-Anweisung gegen eine Tabelle startet eine Transaktion, indem sie eine Leseansicht erstellt. Eine Leseansicht ist ein einheitlicher Datensatz für nachfolgende Abfragen. Dieser bleibt bestehen, bis die Transaktion übernommen wurde. Beachten Sie diese Einschränkung auch bei der Verwendung von JDBC oder ODBC Anwendungen mit Aurora, da solche Anwendungen möglicherweise mit ausgeschalteter autocommit Einstellung ausgeführt werden.

Im nachfolgenden Beispiel ist zu sehen, wie eine Abfrageausführung gegen eine Tabelle (Einstellung autocommit deaktiviert) eine Leseansicht erzeugt, die implizit eine Transaktion in Gang setzt. Abfragen, die kurz danach gestartet werden, können Parallel Query noch nutzen. Nach mehreren Minuten kommen Abfragen jedoch nicht mehr für Parallel Query in Frage. Wenn Sie ans Ende der Transaktion COMMIT oder ROLLBACK stellen, kann Parallel Query wieder ausgeführt werden.

mysql> set autocommit=0;

mysql> explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+----+...+---------+----------------------------------------------------------------------------+
| id |...| rows    | Extra                                                                      |
+----+...+---------+----------------------------------------------------------------------------+
|  1 |...| 2976129 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+---------+----------------------------------------------------------------------------+

mysql> select sleep(720); explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+------------+
| sleep(720) |
+------------+
|          0 |
+------------+
1 row in set (12 min 0.00 sec)

+----+...+---------+-------------+
| id |...| rows    | Extra       |
+----+...+---------+-------------+
|  1 |...| 2976129 | Using where |
+----+...+---------+-------------+

mysql> commit;

mysql> explain select sql_no_cache count(*) from part where p_retailprice > 10.0;
+----+...+---------+----------------------------------------------------------------------------+
| id |...| rows    | Extra                                                                      |
+----+...+---------+----------------------------------------------------------------------------+
|  1 |...| 2976129 | Using where; Using parallel query (1 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+---------+----------------------------------------------------------------------------+

Um festzustellen, wie oft Abfragen nicht für Parallel Query in Frage kamen, weil sie Teil lang laufender Transaktionen waren, untersuchen Sie die Statusvariable Aurora_pq_request_not_chosen_long_trx.

mysql> show global status like '%pq%trx%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Aurora_pq_request_not_chosen_long_trx | 4     |
+-------------------------------+-------+

SELECT-Anweisungen, die Sperren annehmen (z. B. Syntax SELECT FOR UPDATE oder SELECT LOCK IN SHARE MODE), können Parallel Query nicht verwenden.

Parallel Query kann an Tabellen funktionieren, die mit einer LOCK TABLES-Anweisung gesperrt sind.

mysql> explain select o_orderpriority, o_shippriority from orders where o_clerk = 'Clerk#000095055';
+----+...+-----------+----------------------------------------------------------------------------+
| id |...| rows      | Extra                                                                      |
+----+...+-----------+----------------------------------------------------------------------------+
|  1 |...| 154545408 | Using where; Using parallel query (3 columns, 1 filters, 0 exprs; 0 extra) |
+----+...+-----------+----------------------------------------------------------------------------+

mysql> explain select o_orderpriority, o_shippriority from orders where o_clerk = 'Clerk#000095055' for update;
+----+...+-----------+-------------+
| id |...| rows      | Extra       |
+----+...+-----------+-------------+
|  1 |...| 154545408 | Using where |
+----+...+-----------+-------------+

B-Baum-Indizes

Die von der ANALYZE TABLE-Anweisung erfassten Statistiken unterstützen den Optimierer bei der Entscheidung für Parallelabfragen oder Index-Lookups. Ausschlaggebend sind die Eigenschaften der Daten in den Spalten. Halten Sie die Statistiken auf ANALYZE TABLE dem neuesten Stand, indem Sie DML Operationen nachverfolgen, die wesentliche Änderungen an den Daten in einer Tabelle vornehmen.

Wenn Index-Lookups ausreichen, um eine Abfrage effizient ohne datenintensiven Scan auszuführen, verwendet Aurora möglicherweise Index-Lookups. Dadurch erübrigt sich der Zusatzaufwand der Parallelabfrageverarbeitung. Außerdem regeln Obergrenzen, wie viele Abfragen gleichzeitig in einem Aurora-DB-Cluster zulässig sind. Die Einhaltung bewährter Methoden bei der Indizierung von Tabellen trägt dazu bei, dass in den häufigsten und besonders oft gleichzeitig ausgeführten Abfragen Index-Lookups zur Anwendung kommen.

Indizes für die Volltextsuche (FTS)

Zurzeit wird Parallel Query nicht für Tabellen mit Volltextsuchindex verwendet. Dies gilt unabhängig davon, ob sich die Abfrage auf solche indizierten Spalten bezieht oder ob sie den Operator MATCH verwendet.

Virtuelle Spalten

Zurzeit wird Parallel Query nicht für Tabellen verwendet, die eine virtuelle Spalte enthalten. Dies gilt unabhängig davon, ob die Abfrage auf virtuelle Spalten verweist.

Integrierte Caching-Mechanismen

Aurora enthält integrierte Caching-Mechanismen: den Bufferpool und den Abfrage-Cache. Der Aurora-Optimierer entscheidet, ob einer dieser Caching-Mechanismen oder Parallel Query für eine bestimmte Abfrage besser geeignet ist.

Wenn eine Parallelabfrage Zeilen filtert und Spaltenwerte transformiert/extrahiert, werden Daten als Tupel an den Hauptknoten zurückübertragen – nicht als Datenseiten. Das bedeutet also, dass bei Verwendung von Parallel Query dem Bufferpool keine Seiten hinzugefügt werden. Es werden auch keine Seiten entfernt, die bereits im Bufferpool sind.

Aurora überprüft die Anzahl der Tabellendatenseiten, die im Pufferpool vorhanden sind und welchen Anteil der Tabellendaten diese Zahl darstellt. Aurora verwendet diese Informationen, um zu bestimmen, ob es effizienter ist, parallele Abfragen zu verwenden (und die Daten im Pufferpool zu umgehen). Es ist auch möglich, dass Aurora den nicht-parallelen Abfrageverarbeitungspfad verwendet, der auf Daten aus dem Bufferpool zurückgreift. Welche Seiten im Cache abgelegt sind und wie sich datenintensive Abfragen auf das Caching und die Bereinigung auswirken, hängt von den Konfigurationseinstellungen des Bufferpools ab. Es kann deshalb schwierig vorherzusagen sein, ob eine Abfrage mit Parallel Query ausgeführt wird. Dies hängt von den Daten im Bufferpool ab, deren Zusammensetzung sich ständig ändert.

Außerdem begrenzt Aurora, wie viele Parallelabfragen gleichzeitig möglich sind. Parallel Query kommt nicht für jede Abfrage zum Einsatz. Deshalb befinden sich die Daten von Tabellen, die von mehreren Abfragen gleichzeitig genutzt werden, zu einem beträchtlichen Teil im Bufferpool. Dementsprechend nutzt Aurora diese Tabellen nicht oft für Parallelabfragen.

In einer Abfolge nicht-paralleler Abfragen gegen dieselbe Tabelle ist die erste Abfrage möglicherweise langsam, weil die Daten nicht im Bufferpool sind. Die zweite und nachfolgende Abfragen laufen bereits schneller ab, weil der Bufferpool inzwischen sozusagen "warmgelaufen" ist. In der Regel zeigen Parallelabfragen ab der ersten Tabellenabfrage gleichbleibende Leistung Für Leistungstests empfehlen sich Vergleichswerte für einen kalten und einen warmen Bufferpool. In einigen Fällen sind die Ergebnisse des warmen Bufferpools eine gute Vergleichsbasis für Parallelabfragezeiten. Berücksichtigen Sie in diesen Fällen Faktoren wie die Häufigkeit von Abfragen für diese Tabelle. Überlegen Sie auch, ob es sich lohnt, die Daten für diese Tabelle im Pufferpool zu behalten.

Der Abfrage-Cache vermeidet die erneute Ausführung einer Abfrage, wenn eine identische Abfrage abgesendet wird und die zugrunde liegenden Tabellendaten nicht geändert wurden. Mit Parallel Query optimierte Abfragen können im Abfrage-Cache abgelegt werden. Wird die gleiche Abfrage noch einmal gestartet, liegt sofort ein Ergebnis vor.

Anmerkung

Bei Leistungsvergleichen können aufgrund des Abfrage-Cache künstlich niedrige Zeitangaben zustandekommen. Für Benchmark-ähnliche Aufgabenstellungen empfiehlt sich der sql_no_cache-Hinweis. Dieser verhindert, dass das Ergebnis aus dem Abfrage-Cache kommt. Auch dann nicht, wenn die gleiche Abfrage schon einmal ausgeführt wurde. Der Hinweis folgt in der Abfrage unmittelbar nach der SELECT-Anweisung. Viele Beispiele für parallele Abfragen in diesem Thema enthalten diesen Hinweis, um die Abfragezeiten zwischen den Versionen der Abfrage vergleichbar zu machen, für welche die parallele Abfrage aktiviert und deaktiviert ist.

Entfernen Sie diesen Hinweis aus Ihrem Quellcode, bevor Sie parallele Abfragen in einer Produktionsumgebung verwenden.

Optimierungshinweise

Eine andere Möglichkeit, den Optimierer zu steuern, besteht in der Verwendung von Optimierungshinweisen, die in einzelnen Anweisungen angegeben werden können. Sie können beispielsweise eine Optimierung für eine Tabelle in einer Anweisung aktivieren und dann die Optimierung für eine andere Tabelle deaktivieren. Weitere Informationen zu diesen Hinweisen finden Sie unter Optimizer-Hinweise im Referenzhandbuch. SQL

Sie können SQL Hinweise mit Aurora SQL My-Abfragen verwenden, um die Leistung zu optimieren. Sie können auch Hinweise verwenden, um zu verhindern, dass Ausführungspläne für wichtige Abfragen aufgrund unvorhersehbarer Bedingungen geändert werden.

Wir haben die SQL Hinweisfunktion erweitert, damit Sie die Optimierer-Optionen für Ihre Abfragepläne kontrollieren können. Diese Hinweise gelten für Abfragen, bei denen die Parallelabfrageoptimierung verwendet wird. Weitere Informationen finden Sie unter Aurora Meine SQL Tipps.

Meine ISAM temporären Tabellen

Die parallele Abfrageausführung ist nur möglich, wenn InnoDB-Tabellen vorliegen. Da Aurora My ISAM hinter den Kulissen für temporäre Tabellen SQL verwendet, verwenden interne Abfragephasen, die temporäre Tabellen beinhalten, niemals parallel Abfragen. Diese Abfragephasen erkennen Sie am Code Using temporary in der EXPLAIN-Ausgabe.