SQLconstructions pour les requêtes parallèles dans Aurora My SQL - Amazon Aurora
EXPLAINdéclarationWHEREClause Langage de définition des données (DDL)Types de données de colonneTables partitionnéesFonctions d'agrégation, clauses GROUP BY et HAVING clausesWHEREClause d'appels de fonctionLIMITClause Opérateurs de comparaisonJointuresSous-requêtesUNIONVuesDéclarations du langage de manipulation des données (DML)Transactions et verrouillageIndex B-TreeRecherche en texte intégral (FTS) indexColonnes virtuellesMécanismes intégrés de mise en cacheIndicateurs de l'optimiseurMes tables ISAM temporaires

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

SQLconstructions pour les requêtes parallèles dans Aurora My SQL

Dans la section suivante, vous trouverez plus de détails sur les raisons pour lesquelles certaines SQL instructions utilisent ou n'utilisent pas de requête parallèle. Cette section explique également comment les SQL fonctionnalités d'Aurora My interagissent avec les requêtes parallèles. Ces informations peuvent vous aider à diagnostiquer les problèmes de performances d'un cluster qui utilise les requêtes parallèles, ou à comprendre comment cette fonction s'applique pour une charge de travail spécifique.

La décision d'utiliser la fonction de requête parallèle dépend d'un grand nombre de facteurs qui interviennent au moment de l'exécution de l'instruction. Dès lors, les requêtes parallèles peuvent être déclenchées pour certaines requêtes à chaque fois, jamais ou uniquement dans certaines conditions.

Astuce

Lorsque vous consultez ces exemples dansHTML, vous pouvez utiliser le widget Copier situé dans le coin supérieur droit de chaque liste de codes pour copier le SQL code et l'essayer vous-même. L'utilisation du widget Copy (Copier) permet d'éviter de copier les caractères supplémentaires autour de l'invite mysql> et des lignes -> suivantes.

EXPLAINdéclaration

Comme illustré dans divers exemples de cette section, l'instruction EXPLAIN indique si chaque stade d'une requête est éligible à la fonction de requête parallèle. Elle indique également quels aspects d'une requête peuvent être délégués à la couche de stockage. Voici les éléments les plus importants du plan de requête :

  • Une valeur autre que NULL pour la colonne key suggère que la requête peut être effectuée efficacement via les recherches d'index et qu'une requête parallèle est peu probable.

  • Une faible valeur pour la colonne rows (valeur inférieure à 1 million) suggère que la requête n'accède pas à suffisamment de données pour que la fonction de requête parallèle soit justifiée. Cela signifie que la requête parallèle est peu probable.

  • La colonne Extra vous indique si l'utilisation de la fonction de requête parallèle est prévue. La sortie ressemble à l'exemple suivant. 

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

    Le nombre columns représente le nombre de colonnes auquel le bloc de requêtes fait référence.

    Le nombre filters indique de nombre de prédicats WHERE représentant une simple comparaison d'une valeur de colonne par rapport à une constante. La comparaison peut rechercher une égalité, une inégalité ou une plage. Aurora permet de mettre en parallèle ces types de prédicats plus efficacement.

    Le nombre exprs représente le nombre d'expressions, comme les appels de fonction, les opérateurs ou autres, qui peuvent également être mis en parallèle, bien que cela soit moins efficace qu'avec une condition de filtrage.

    Le nombre extra représente le nombre d'expressions qui ne peuvent pas être déléguées et qui sont effectuées par le nœud principal.

Par exemple, considérons la sortie EXPLAIN suivante. 

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) |
+----+-------------+-------+...+----------+----------------------------------------------------------------------------+

Les informations de la colonne Extra montre que cinq colonnes sont extraites de chaque ligne afin d'évaluer les conditions de la requête et de construire le jeu de résultats. Un prédicat WHERE implique un filtre, à savoir une colonne directement testée dans la clause WHERE. Deux clauses WHERE nécessitent l'évaluation d'expressions plus complexes et qui impliquent ici des appels de fonction. Le champ 0 extra confirme que toutes les opérations de la clause WHERE sont déléguées à la couche de stockage dans le cadre du traitement de requête parallèle.

Dans les cas où une requête parallèle n'est pas choisie, vous pouvez généralement déduire la raison à partir des autres colonnes de la sortie EXPLAIN. Par exemple, la valeur rows peut être trop faible, ou la colonne possible_keys peut indiquer que la requête est en mesure d'utiliser une recherche d'index au lieu d'une analyse à usage intensif de données. L'exemple suivant montre une requête dans laquelle l'optimiseur peut estimer que la requête n'analysera qu'un petit nombre de lignes. Cette estimation est basée sur les caractéristiques de la clé principale. Dans ce cas, aucune requête parallèle n'est requise. 

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 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

La sortie indiquant si une requête parallèle sera utilisée tient compte de tous les facteurs qui entrent en jeu au moment de l'exécution de l'instruction EXPLAIN. L'optimiseur peut choisir une autre option lorsque la requête est exécutée, si la situation a changé entre-temps. Par exemple, EXPLAIN peut indiquer qu'une instruction utilisera la fonction de requête parallèle. Toutefois, au moment d'exécuter la requête, il peut choisir de ne pas utiliser cette fonction selon les conditions qui s'appliquent à ce moment-là. Ces conditions peuvent inclure plusieurs autres requêtes parallèles s'exécutant simultanément. Elles peuvent également inclure des lignes supprimées de la table, un nouvel index en cours de création, trop de temps passé dans une transaction ouverte, etc.

WHEREClause

Pour qu'une requête puisse tirer parti de l'optimisation via les requêtes parallèles, elle doit inclure une clause WHERE.

L'optimisation via les requêtes parallèles accélère de nombreux types d'expressions utilisés dans la clause WHERE :

  • Simples comparaisons d'une valeur de colonne par rapport à une constante, aussi connues en tant que filtres. Ces comparaisons sont optimales lorsqu'elles sont déléguées à la couche de stockage. Le nombre d'expressions de filtrage d'une requête est indiqué dans la sortie EXPLAIN.

  • Les autres types d'expressions de la clause WHERE sont également déléguées à la couche de stockage, le cas échéant. Le nombre d'expressions de ce type dans une requête est indiqué dans la sortie EXPLAIN. Il peut s'agir d'appels de fonction, d'opérateurs LIKE, d'expressions CASE, etc.

  • Certaines fonctions ne sont pas déléguées par les requêtes parallèles. Le nombre d'expressions de ce type dans une requête est indiqué sous la forme du compteur extra dans la sortie EXPLAIN. Le reste de la requête peut utiliser la fonction de requête parallèle.

  • Bien que les expressions de la liste de sélection ne soient pas déléguées, les requêtes contenant ces fonctions peuvent bénéficier d'une réduction du trafic réseau pour les résultats intermédiaires des requêtes parallèles. Par exemple, les requêtes qui appellent des fonctions d'agrégation dans la liste de sélection peuvent bénéficier des requêtes parallèles même si les fonctions d'agrégation ne sont pas déléguées.

Par exemple, la requête suivante effectue une analyse de la table complète et traite toutes les valeurs pour la colonne P_BRAND. Toutefois, elle n'a pas recours à une requête, car elle n'inclut pas de clause WHERE. 

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 |
+----+-------------+-------+------+---------------+------+---------+------+----------+---------------------------------+

En revanche, la requête suivante comprend les prédicats WHERE qui filtrent les résultats, de sorte qu'une requête parallèle peut être appliquée : 

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) |
+----+...+----------+-------------------------------------------------------------------------------------------------------------+

Si l'optimiseur estime que le nombre de lignes renvoyées pour un bloc de requêtes est faible, la fonction de requête parallèle n'est pas utilisée pour ce bloc. L'exemple suivant présente un scénario où un opérateur « supérieur à » dans la colonne de clé primaire s'applique à des millions de lignes, ce qui entraîne l'utilisation d'une requête parallèle. Il est estimé que l'opérateur contraire « inférieur à » s'applique uniquement à quelques lignes et qu'il n'utilise donc pas la fonction de requête parallèle. 

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 |
+----+...+------+--------------------------+

Langage de définition des données (DDL)

Dans Aurora My SQL version 2, la requête parallèle n'est disponible que pour les tables pour lesquelles aucune opération du langage de définition de données rapide (DDL) n'est en attente. Dans Aurora My SQL version 3, vous pouvez utiliser une requête parallèle sur une table en même temps qu'une DDL opération instantanée.

Instant DDL in Aurora My SQL version 3 remplace la DDL fonctionnalité rapide d'Aurora My SQL version 2. Pour plus d'informations sur l'instantDDL, consultezInstant DDL (Aurora MySQL version 3).

Types de données de colonne

Dans Aurora My SQL version 3, les requêtes parallèles peuvent fonctionner avec des tables contenant des colonnes de types de données TEXTBLOB,JSON, etGEOMETRY. Elles peuvent également être utilisées avec les colonnes VARCHAR et CHAR dont la longueur maximale déclarée est supérieure à 768 octets. Si votre requête fait référence à des colonnes contenant de tels types d'objets volumineux, le travail supplémentaire de récupération ajoute une surcharge au traitement des requêtes. Le cas échéant, vérifiez si la requête peut omettre les références à ces colonnes. Dans le cas contraire, exécutez des points de référence pour vérifier si ces requêtes sont plus rapides lorsque la requête parallèle est activée ou désactivée.

Dans Aurora My SQL version 2, les requêtes parallèles présentent les limites suivantes pour les types d'objets de grande taille :

  • Les types de données TEXT, BLOB, JSON et GEOMETRY ne sont pas pris en charge avec les requêtes parallèles. Les requêtes faisant référence à des colonnes de ce type ne peuvent pas utiliser les requêtes parallèles.

  • Les colonnes de longueur variable (types de données VARCHAR et CHAR) sont compatibles avec les requêtes parallèles jusqu'à une longueur déclarée maximale de 768 octets. Les requêtes faisant référence à des colonnes de ce type ayant une longueur maximale supérieure ne peuvent pas utiliser les requêtes parallèles. Pour les colonnes utilisant des jeux de caractères multi-octets, la limite du nombre d'octets tient compte du nombre maximal d'octets de ces jeux de caractères. Par exemple, pour le jeu de caractères utf8mb4 (dont la longueur de caractères maximale est de 4 octets), une colonne VARCHAR(192) est compatible avec les requêtes parallèles, mais pas une colonne VARCHAR(193).

Tables partitionnées

Vous pouvez utiliser des tables partitionnées avec une requête parallèle dans Aurora My SQL version 3. Les tables partitionnées étant représentées en interne sous la forme de plusieurs tables plus petites, une requête qui utilise une requête parallèle sur une table non partitionnée peut ne pas utiliser de requête parallèle sur une table partitionnée identique. Aurora My SQL détermine si chaque partition est suffisamment grande pour bénéficier de l'optimisation des requêtes parallèles, au lieu d'évaluer la taille de la table entière. Vérifiez si la variable d'état Aurora_pq_request_not_chosen_small_table est incrémentée si une requête sur une table partitionnée n'utilise pas de requête parallèle lorsque vous l'attendez.

Par exemple, considérez une table partitionnée avec PARTITION BY HASH (column) PARTITIONS 2 et une autre table partitionnée avec PARTITION BY HASH (column) PARTITIONS 10. Dans le tableau comportant deux partitions, les partitions sont cinq fois plus volumineuses que la table avec dix partitions. Par conséquent, la requête parallèle est plus susceptible d'être utilisée pour les requêtes sur la table comportant moins de partitions. Dans l'exemple suivant, la table PART_BIG_PARTITIONS compte deux partitions et PART_SMALL_PARTITIONS possède dix partitions. Avec des données identiques, la requête parallèle est plus susceptible d'être utilisée pour la table comportant moins de partitions volumineuses. 


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 |
+----+-------------+-----------------------+-------------------------------+------------------------------+

Fonctions d'agrégation, clauses GROUP BY et HAVING clauses

Les requêtes impliquant des fonctions d'agrégation sont souvent adaptées aux requêtes parallèles, car elles impliquent l'analyse de grand nombres de lignes dans des tables de grande taille.

Dans Aurora My SQL 3, les requêtes parallèles peuvent optimiser les appels de fonctions agrégés dans la liste de sélection et dans la HAVING clause.

Avant Aurora My SQL 3, les appels de fonction agrégés figurant dans la liste de sélection ou dans la HAVING clause n'étaient pas transférés vers la couche de stockage. Cependant, la fonction de requête parallèle peut tout de même améliorer les performances de ces requêtes avec les fonctions d'agrégation. Pour ce faire, elle commence par extraire en parallèle les valeurs de colonne à partir des pages de données brutes au niveau de la couche de stockage. Puis, elle retransmet ces valeurs au nœud principal sous forme de tuple compact au lieu de pages de données complètes. Comme toujours, la requête nécessite au moins un prédicat WHERE pour que la fonction de requête parallèle soit activée.

Les exemples simples suivants illustrent les types de requêtes agrégées pouvant bénéficier d'une requête parallèle. Les résultats intermédiaires sont renvoyés sous forme compacte au nœud principal et/ou les lignes qui ne correspondent pas sont filtrées des résultats intermédiaires. 

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) |
+----+...+-------------------------------------------------------------------------------------------------------------+

WHEREClause d'appels de fonction

Aurora permet d'appliquer l'optimisation des appels via les requêtes parallèles dans la plupart des fonctions intégrées de la clause WHERE. La parallélisation de ces appels de fonction décharge une partie du CPU travail du nœud principal. L'évaluation des fonctions de prédicat en parallèle lors des premiers stades d'une requête permet à Aurora de minimiser la quantité de données transmises et traitées lors des stades ultérieurs.

À ce stade, la parallélisation ne s'applique pas aux appels de fonction qui se trouvent dans la liste de sélection. Ces fonctions sont évaluées par le nœud principal même si des appels de fonction identiques apparaissent dans la clause WHERE. Les valeurs d'origine issues des colonnes appropriées sont incluses dans les tuples retransmis depuis les nœuds de stockage vers le nœud principal. Le nœud principal effectue toutes les transformations telles que UPPER, CONCATENATE, etc., afin de générer les valeurs finales du jeu de résultats.

Dans l'exemple suivant, la requête parallèle met en parallèle l'appel à LOWER, car il apparaît dans la clause WHERE. La fonction de requête parallèle ne s'applique pas aux appels à SUBSTR et UPPER, car ils se trouvent dans la liste de sélection. 

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) |
+----+...+---------------------------------------------------------------------------------------------+

Les mêmes considérations sont valables pour les autres expressions, telles que les expressions CASE ou les opérateurs LIKE. L'exemple suivant montre une requête parallèle évaluant l'expression CASE et les opérateurs LIKE dans la clause WHERE. 

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) |
+----+...+-------------------------------------------------------------------------------------------------------------+

LIMITClause

À ce stade, les requêtes parallèles ne sont pas utilisées pour les blocs de requêtes incluant une clause LIMIT. Les requêtes parallèles peuvent être utilisées pour les phases de requêtes précédentes avec les clauses GROUP BY, ORDER BY ou JOIN.

Opérateurs de comparaison

L'optimiseur estime le nombre de lignes à analyser pour évaluer les opérateurs de comparaison et se base sur cette estimation pour déterminer si une requête parallèle est justifiée ou non.

Le premier exemple ci-dessous montre qu'une comparaison d'égalité par rapport à la colonne de clé primaire peut être effectuée efficacement sans requête parallèle. Le deuxième exemple ci-dessous montre qu'une comparaison similaire par rapport à une colonne non indexée nécessite l'analyse de millions de lignes et peut donc tirer parti de la fonction de requête parallèle. 

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) |
+----+...+----------+----------------------------------------------------------------------------+

Les mêmes considérations s'appliquent pour les comparaisons d'inégalité ou de plages telles que celles réalisées avec les opérateurs « inférieur à », « supérieur à » ou « égal à », ou encore BETWEEN. L'optimiseur estime le nombre de lignes à analyser et se base sur le volume total d'I/O pour déterminer si une requête parallèle est justifiée.

Jointures

Les requêtes de jointure comprenant des tables de grande taille impliquent généralement des opérations à usage intensif de données qui tirent parti de l'optimisation via les requêtes parallèles. À ce stade, les comparaisons de valeurs de colonne entre plusieurs tables (autrement dit, les prédicats de jointure eux-mêmes) ne sont pas mises en parallèle. Cependant, la fonction de requête parallèle peut déléguer une partie du traitement interne pour d'autres phases de jointure, telles que la construction du filtre Bloom lors d'une jointure de hachage. La fonction de requête parallèle peut d'appliquer aux requêtes de jointure même sans clause WHERE. Par conséquent, les requêtes de jointure sont l'exception à la règle selon laquelle une clause WHERE est requise pour pouvoir utiliser une requête parallèle.

Chaque phase de traitement d'une jointure est évaluée afin de déterminer si elle est éligible à la fonction de requête parallèle. Si plusieurs phases sont éligibles, elles sont effectuées l'une après l'autre. De cette manière, chaque requête de jointure est comptabilisée comme une seule session de requête parallèle en termes de limites de simultanéité.

Par exemple, lorsqu'une requête de jointure inclut des prédicats WHERE pour filtrer les lignes de l'une des tables jointes, cette option de filtrage peut utiliser la fonction de requête parallèle. Un autre exemple est celui d'une requête de jointure qui utilise le mécanisme de jointure de hachage pour joindre une table de grande taille à une petite table. Dans ce cas, la fonction de requête parallèle peut s'appliquer à l'analyse des tables permettant de générer la structure de données du filtre Bloom.

Note

Les requêtes parallèles sont généralement utilisées pour les types de requêtes gourmandes en ressources qui tirent parti de l'optimisation de la jointure par hachage. La méthode d'activation de l'optimisation de la jointure par hachage dépend de la SQL version d'Aurora My. Pour de plus amples informations sur chaque version, consultez Activation de la jointure par hachage pour les clusters de requête parallèle. Pour de plus amples informations sur la façon d'utiliser les jointures par hachage efficacement, consultez Optimisation des requêtes Aurora My SQL join de grande taille avec des jointures par hachage. 

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) |
+----+...+----------+-------+---------------+-------------+...+-----------+-----------------------------------------------------------------------------------------------------------------+

Pour une requête de jointure qui utilise le mécanisme de boucle imbriquée, le bloc de boucles imbriquées qui se trouve le plus à l'extérieur peut utiliser la fonction de requête parallèle. L'utilisation des requêtes parallèles dépend des facteurs habituels, tels que la présence d'autres conditions de filtrage dans la clause WHERE. 

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)                         |
+----+-------------+----------+...+----------+----------------------------------------------------------------------------+

Sous-requêtes

Le bloc de requête externe et le bloc de sous-requête interne peuvent chacun utiliser ou non une requête parallèle. Cela dépend des caractéristiques habituelles de la table, de la clause WHERE, et ainsi de suite, pour chaque bloc. Par exemple, la requête suivante utilise la fonction de requête parallèle pour le bloc de sous-requêtes, mais pas pour le bloc externe. 

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) |
+----+-------------+...+----------+----------------------------------------------------------------------------+

Actuellement, les sous-requêtes corrélées ne peuvent pas utiliser la fonction d'optimisation via les requêtes parallèles.

UNION

Chaque bloc de requêtes d'une instruction UNION peut utiliser la fonction de requête parallèle ou pas selon les caractéristiques habituelles de la table, de la clause WHERE, etc. pour chaque partie de l'instruction UNION. 

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                                           |
+----+--------------+...+----------+----------------------------------------------------------------------------+
Note

Chaque clause UNION de la requête est exécutée dans l'ordre. Même si la requête inclut plusieurs stades qui utilisent tous la fonction de requête parallèle, elle exécute une seule et même requête parallèle. Par conséquent, même une requête complexe à plusieurs phases est comptabilisée comme une seule et même requête dans la limite de requêtes parallèles simultanées.

Vues

L'optimiseur réécrit les requêtes avec une vue comme requête de longue taille utilisant les tables sous-jacentes. Dès lors, la requête parallèle fonctionne de la même manière, et ce que les références de tables soient des vues ou des tables réelles. Toutes les considérations concernant l'utilisation de la fonction de requête parallèle pour une requête, ainsi que les parties qui sont déléguées, s'appliquent à la requête réécrite finale.

Par exemple, le plan de requête suivant présente une définition de vue qui n'utilise généralement pas les requêtes parallèles. Lorsque la vue est interrogée avec des WHERE clauses supplémentaires, Aurora My utilise une requête SQL parallèle. 

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) |
+----+...+----------+----------------------------------------------------------------------------+

Déclarations du langage de manipulation des données (DML)

L'instruction INSERT peut utiliser la fonction de requête parallèle pour la phase de traitement SELECT si la partie SELECT remplit les autres conditions relatives à cette fonction. 

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) |
+----+...+----------+----------------------------------------------------------------------------+
Note

Habituellement, après une instruction INSERT, les données des lignes qui viennent d'être insérées se trouvent dans le pool de mémoires tampons. Dès lors, une table n'est pas toujours éligible à la fonction de requête parallèle juste après l'insertion d'un grand nombre de lignes. Ultérieurement, une fois que les données seront éliminées du pool de mémoires tampons pendant le fonctionnement normal, les requêtes liées à la table pourront commencer à réutiliser la fonction de requête parallèle.

L'instruction CREATE TABLE AS SELECT n'utilise pas la fonction de requête parallèle même si la portion SELECT de l'instruction y est éligible. L'DDLaspect de cette instruction la rend incompatible avec le traitement parallèle des requêtes. En revanche, dans l'instruction INSERT ... SELECT, la portion SELECT peut utiliser la fonction de requête parallèle.

Les requêtes parallèles ne sont jamais utilisées pour les instructions DELETE ou UPDATE, indépendamment de la taille de la table et des prédicats de la clause WHERE. 

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

Transactions et verrouillage

Vous pouvez utiliser tous les niveaux d'isolation de l'instance principale Aurora.

Sur les instances de base de données de lecteur Aurora, la requête parallèle s'applique aux instructions exécutées sous le niveau d'isolation REPEATABLE READ. Aurora My SQL version 2.09 ou ultérieure peut également utiliser le niveau d'READ COMMITTEDisolation sur les instances de base de données du lecteur. REPEATABLE READest le niveau d'isolation par défaut pour les instances de base de données du lecteur Aurora. Pour utiliser le niveau d'isolation READ COMMITTED sur les instances de base de données de lecteur, l'option de configuration aurora_read_replica_read_committed doit être définie au niveau de la session. Le niveau READ COMMITTED d'isolation des instances de lecteur est conforme au comportement SQL standard. Toutefois, l'isolation est moins stricte sur les instances de lecteur que lorsque les requêtes utilisent le niveau d'isolation READ COMMITTED sur l'instance d'enregistreur.

Pour de plus amples informations sur les niveaux d'isolation d'Aurora, en particulier sur les différences dans READ COMMITTED entre les instances d'enregistreur et de lecteur, consultez Niveaux d'SQLisolation d'Aurora My.

Une fois qu'une transaction importante est terminée, les statistiques de la table peuvent être obsolètes. Ces statistiques obsolètes peuvent nécessiter une instruction ANALYZE TABLE avant qu'Aurora puisse estimer précisément le nombre de lignes. Une DML instruction à grande échelle peut également intégrer une partie importante des données de la table dans le pool de mémoire tampon. Le stockage de ces données dans le pool de mémoires tampons peut entraîner une utilisation moins fréquente de la fonction de requête parallèle pour cette table tant que les données ne seront pas éliminées du pool.

Lorsque votre session fait partie d'une transaction de longue durée (par défaut, 10 minutes), les autres requêtes de cette session n'utilisent pas la fonction de requête parallèle. L'expiration du délai d'attente peut également avoir lieu lors d'une requête unique de longue durée. Cela peut se produit si la requête dure plus longtemps que l'intervalle maximal autorisé (actuellement fixé à 10 minutes) avant le début du traitement de requête parallèle

Pour limiter le risque de lancement accidentel de transactions de longue durée, définissez autocommit=1 dans les sessions mysql dans lesquelles vous effectuez des requêtes ad hoc (exceptionnelles). Même une instruction SELECT par rapport à une table commence une transaction en créant une vue de lecture. Une vue de lecture est un ensemble de données constant pour les requêtes ultérieures. Elle est conservée jusqu'à ce que la transaction soit validée. Tenez compte de cette restriction également lorsque vous utilisez JDBC des ODBC applications avec Aurora, car ces applications peuvent s'exécuter avec le autocommit paramètre désactivé.

L'exemple suivant montre comment l'exécution d'une requête liée à une table crée une vue de lecture lançant implicitement une transaction lorsque le paramètre autocommit est désactivé. Les requêtes exécutées peu de temps après peuvent utiliser la fonction de requête parallèle. Toutefois, après une pause de plusieurs minutes, ces requêtes ne sont plus éligibles à la fonction de requête parallèle. Mettre fin à la transaction avec COMMIT ou ROLLBACK restaure l'éligibilité à cette fonction. 

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) |
+----+...+---------+----------------------------------------------------------------------------+

Pour déterminer le nombre de fois que des requêtes n'ont pas été éligibles à une requête parallèle parce qu'elles faisaient partie de transactions de longue durée, vérifiez la variable de statut 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     |
+-------------------------------+-------+

Toute instruction SELECT qui acquiert des verrous, telle la syntaxe SELECT FOR UPDATE ou SELECT LOCK IN SHARE MODE, ne peut pas utiliser la fonction de requête parallèle.

Les requêtes parallèles peuvent s'appliquer aux tables verrouillées par une instruction LOCK TABLES. 

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 |
+----+...+-----------+-------------+

Index B-Tree

Les statistiques recueillies par l'instruction ANALYZE TABLE permettent à l'optimiseur de déterminer quand utiliser la fonction de requête parallèle ou les recherches d'index en fonction des caractéristiques des données pour chaque colonne. Maintenez les statistiques à jour ANALYZE TABLE en exécutant DML des opérations qui modifient de manière substantielle les données d'une table.

Si les recherches d'index peuvent effectuer une requête efficacement sans analyse à usage intensif de données, Aurora peut privilégier cette option. Cette approche permet d'éviter les frais supplémentaires liés au traitement de requête parallèle. Des limites de simultanéité s'appliquent également au nombre de requêtes parallèle qui peuvent être exécutées simultanément dans un cluster de bases de données Aurora. Veillez à respecter les bonnes pratiques pour l'indexation des tables, de sorte que les requêtes les plus fréquentes et utilisant le plus la simultanéité aient recours aux recherches d'index.

Recherche en texte intégral (FTS) index

Actuellement, les requêtes parallèles ne sont pas utilisées pour les tables qui contiennent un index de recherche en texte intégral, peu importe que la requête fasse référence à ces colonnes indexées ou qu'elle utilise l'opérateur MATCH.

Colonnes virtuelles

Actuellement, la requête parallèle n'est pas utilisée pour les tables qui contiennent une colonne virtuelle, que la requête se réfère ou non à des colonnes virtuelles.

Mécanismes intégrés de mise en cache

Aurora inclut des mécanismes intégrés de mise en cache, notamment le pool de mémoires tampons et le cache de requête. L'optimiseur Aurora choisit entre ces mécanismes de mise en cache et la fonction de requête parallèle selon leur efficacité pour une requête spécifique.

Lorsqu'une requête parallèle filtre les lignes et transforme et extrait les valeurs de colonne, les données sont retransmises au nœud principal sous forme de tuples au lieu de pages de données. Dès lors, l'exécution d'une requête parallèle n'ajoute aucune page au pool de mémoires tampons et n'élimine aucune page qui se trouve déjà dans ce pool.

Aurora vérifie le nombre de pages de données de table présentes dans le pool de mémoires tampons, ainsi que la proportion des données de table que ce nombre représente. Aurora utilise ces informations pour déterminer s'il est plus efficace d'utiliser une requête parallèle (et de contourner ainsi les données du pool de mémoires tampons). Aurora peut également recourir au chemin de traitement de requête non parallèle, qui utilise les données mises en cache dans le pool de mémoires tampons. Les pages mises en caches ainsi que l'impact des requêtes à usage intensif de données sur la mise en cache et l'éviction dépendent des paramètres de configuration liés au pool de mémoires tampons. Dès lors, il peut être difficile de déterminer si une requête spécifique utilisera la fonction de requête parallèle, car le choix dépend des données qui se trouvent dans le pool de mémoires tampons, lesquelles changent constamment.

Aurora impose également des limites de simultanéité pour les requêtes parallèles. Comme les requêtes n'utilisent pas toute la fonction de requête parallèle, une partie significative des données des tables interrogées par plusieurs requêtes simultanément se trouve dans le pool de mémoires tampons. Dès lors, Aurora ne choisit pas souvent ces tables pour les requêtes parallèles.

Lorsque vous exécutez une séquence de requêtes non parallèles pour la même table, la première requête peut prendre du temps, car les données ne sont pas dans le pool de mémoires tampons. Les requêtes suivantes sont beaucoup plus rapides, car le pool de mémoires tampons contient déjà des données. Les requêtes parallèles se traduisent généralement par des performances constantes entre les différentes requêtes d'une même table. Lorsque vous effectuez des tests de performance, comparez les requêtes non parallèles à la fois avec un pool de mémoires tampons à froid et à chaud. Dans certains cas, les résultats pour le pool de mémoires tampons à chaud sont comparables à ceux des requêtes parallèles. Dans ces cas de figure, tenez compte de facteurs tels que la fréquence des requêtes sur cette table. Déterminez également s'il est intéressant de conserver les données de cette table dans le pool de mémoires tampons.

Le cache de requête évite de devoir réexécuter une requête lorsqu'une requête identique est soumise et que les données de la table sous-jacente n'ont pas changé. Les requêtes optimisées par la fonction de requête parallèle peuvent être acheminées dans le cache de requête afin de fournir des résultats instantanés la prochaine fois qu'elles seront réexécutées.

Note

Lorsque l'on compare les performances, le cache de requête peut fournir des chiffres artificiellement bas en matière de durée. Dès lors, lorsque vous souhaitez effectuer une comparaison, vous pouvez utiliser l'indicateur sql_no_cache. Celui-ci empêche le résultat d'être généré par le cache de requête, même si la même requête a été exécutée précédemment. Cet indicateur vient juste après l'instruction SELECT dans une requête. De nombreux exemples de requêtes parallèles fournis dans cette rubrique utilisent cet indicateur afin de pouvoir comparer les délais entre les versions d'une requête pour laquelle la fonction de requête parallèle est activée ou désactivée.

Veillez à supprimer cet indicateur du code source lorsque vous utiliserez la fonction de requête parallèle en environnement de production.

Indicateurs de l'optimiseur

Une autre façon de contrôler l'optimiseur consiste à utiliser des indices d'optimiseur, qui peuvent être spécifiés dans des instructions individuelles. Par exemple, vous pouvez activer une optimisation pour une table dans une instruction, puis désactiver l'optimisation pour une autre table. Pour plus d'informations sur ces conseils, consultez la section Conseils d'optimisation dans le manuel My SQL Reference.

Vous pouvez utiliser SQL des indices avec les SQL requêtes Aurora My pour affiner les performances. Vous pouvez également utiliser des indicateurs pour empêcher que les plans d'exécution des requêtes importantes ne changent en fonction de conditions imprévisibles.

Nous avons étendu la fonctionnalité d'SQLastuces pour vous aider à contrôler les choix d'optimiseur pour vos plans de requêtes. Ces indicateurs s'appliquent aux requêtes qui utilisent l'optimisation via les requêtes parallèles. Pour de plus amples informations, veuillez consulter Aurora Mes SQL conseils.

Mes tables ISAM temporaires

L'optimisation via les requêtes parallèles s'appliquent uniquement aux tables InnoDB. Comme Aurora My SQL utilise My ISAM en arrière-plan pour les tables temporaires, les phases de requête internes impliquant des tables temporaires n'utilisent jamais de requête parallèle. Ces phases de requête sont indiquées par Using temporary dans la sortie EXPLAIN.