Opérateurs d'exécution de requêtes

Présentation

Cette page décrit en détail les opérateurs utilisés dans Spanner. Plans d'exécution de requêtes Pour apprendre à récupérer un plan d'exécution pour une spécifique à l'aide de la console Google Cloud, consultez la section Comprendre comment Spanner exécute des requêtes.

Remarque sur l'interface PostgreSQL : Les exemples de cette rubrique sont destinés aux bases de données utilisant le dialecte GoogleSQL. Cette fonctionnalité n'est pas compatible avec l'interface PostgreSQL.

Les requêtes et les plans d'exécution de cette page sont basés sur le schéma de base de données suivant :

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
  BirthDate  DATE
) PRIMARY KEY(SingerId);

CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);

CREATE TABLE Albums (
  SingerId        INT64 NOT NULL,
  AlbumId         INT64 NOT NULL,
  AlbumTitle      STRING(MAX),
  MarketingBudget INT64
) PRIMARY KEY(SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget);

CREATE TABLE Songs (
  SingerId  INT64 NOT NULL,
  AlbumId   INT64 NOT NULL,
  TrackId   INT64 NOT NULL,
  SongName  STRING(MAX),
  Duration  INT64,
  SongGenre STRING(25)
) PRIMARY KEY(SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE INDEX SongsBySingerAlbumSongNameDesc ON Songs(SingerId, AlbumId, SongName DESC), INTERLEAVE IN Albums;

CREATE INDEX SongsBySongName ON Songs(SongName);

CREATE TABLE Concerts (
  VenueId      INT64 NOT NULL,
  SingerId     INT64 NOT NULL,
  ConcertDate  DATE NOT NULL,
  BeginTime    TIMESTAMP,
  EndTime      TIMESTAMP,
  TicketPrices ARRAY<INT64>
) PRIMARY KEY(VenueId, SingerId, ConcertDate);

Vous pouvez utiliser les instructions LMD (langage de manipulation de données) suivantes pour ajouter des données à ces tables :

INSERT INTO Singers (SingerId, FirstName, LastName, BirthDate)
VALUES (1, "Marc", "Richards", "1970-09-03"),
       (2, "Catalina", "Smith", "1990-08-17"),
       (3, "Alice", "Trentor", "1991-10-02"),
       (4, "Lea", "Martin", "1991-11-09"),
       (5, "David", "Lomond", "1977-01-29");

INSERT INTO Albums (SingerId, AlbumId, AlbumTitle)
VALUES (1, 1, "Total Junk"),
       (1, 2, "Go, Go, Go"),
       (2, 1, "Green"),
       (2, 2, "Forever Hold Your Peace"),
       (2, 3, "Terrified"),
       (3, 1, "Nothing To Do With Me"),
       (4, 1, "Play");

INSERT INTO Songs (SingerId, AlbumId, TrackId, SongName, Duration, SongGenre)
VALUES (2, 1, 1, "Let's Get Back Together", 182, "COUNTRY"),
       (2, 1, 2, "Starting Again", 156, "ROCK"),
       (2, 1, 3, "I Knew You Were Magic", 294, "BLUES"),
       (2, 1, 4, "42", 185, "CLASSICAL"),
       (2, 1, 5, "Blue", 238, "BLUES"),
       (2, 1, 6, "Nothing Is The Same", 303, "BLUES"),
       (2, 1, 7, "The Second Time", 255, "ROCK"),
       (2, 3, 1, "Fight Story", 194, "ROCK"),
       (3, 1, 1, "Not About The Guitar", 278, "BLUES");

Remarque : Vous pouvez exécuter des requêtes et récupérer des plans d'exécution même si les tables ne contiennent aucune donnée.

Opérateurs feuille

L'opérateur feuille est un opérateur qui n'a pas d'enfants. Les types d'opérateurs feuille sont les suivants :

• Array unnest
• Generate relation
• Unit relation
• Empty relation
• Scan

Array unnest

L'opérateur array unnest regroupe un tableau d'entrée en lignes d'éléments. Chaque ligne en résultant contient jusqu'à deux colonnes : la valeur réelle du tableau et éventuellement la position basée sur zéro dans le tableau.

Par exemple, si vous utilisez cette requête :

SELECT a, b FROM UNNEST([1,2,3]) a WITH OFFSET b;

La requête regroupe le tableau [1,2,3] dans la colonne a et affiche la position du tableau dans la colonne b.

Vous obtenez les résultats suivants :

a	b
1	0
2	1
3	2

Voici le plan d'exécution :

Generate relation

L'opérateur generate relation renvoie zéro ou plusieurs lignes.

Unit relation

L'opérateur unit relation renvoie une ligne. Il s'agit d'un cas particulier de l'opérateur generate relation.

Par exemple, si vous utilisez cette requête :

SELECT 1 + 2 AS Result;

Vous obtenez le résultat suivant :

Résultat
3

Voici le plan d'exécution :

Empty relation

L'opérateur empty relation ne renvoie aucune ligne. Il s'agit d'un cas particulier de l'opérateur generate relation.

Par exemple, si vous utilisez cette requête :

SELECT * FROM Albums LIMIT 0

Vous obtenez le résultat suivant :

No results

Voici le plan d'exécution :

Scan

L'opérateur scan renvoie des lignes en analysant une source de lignes. Les types d'opérateurs d'analyse sont les suivants :

• Table scan : l'analyse porte sur une table.
• Index scan : l'analyse porte sur un index.
• Batch scan : l'analyse porte sur des tables intermédiaires créées par d'autres opérateurs relationnels (par exemple, une table créée par un opérateur distributed cross apply).

Dans la mesure du possible, Spanner applique des prédicats simples sur des clés au cours d'une analyse. L'exécution des analyses est plus efficace lorsque des prédicats sont appliqués, car l'analyse n'a pas besoin de lire l'intégralité de la table ou de l'index. Les prédicats apparaissent dans le plan d'exécution sous la forme KeyPredicate: column=value.

Dans le pire des cas, une requête peut avoir besoin de consulter toutes les lignes d'une table. Cette situation conduit à une analyse complète et apparaît dans le plan d'exécution au format full scan: true.

Par exemple, si vous utilisez cette requête :

SELECT s.LastName
FROM singers@{FORCE_INDEX=SingersByFirstLastName} AS s
WHERE s.FirstName = 'Catalina';

Vous obtenez les résultats suivants :

Nom
Smith

Voici le plan d'exécution :

Dans le plan d'exécution, l'opérateur distributed union de premier niveau envoie des sous-plans aux serveurs distants. Chaque sous-plan comporte un opérateur serialize result et un opérateur index scan. Le prédicat Key Predicate: FirstName = 'Catalina' limite l'analyse aux lignes de l'index SingersByFirstLastname dont le prénom (FirstName) est Catalina. Le résultat de l'analyse de l'index est renvoyé à l'opérateur serialize result.

Opérateurs unaires

L'opérateur unaire est un opérateur ayant un seul enfant relationnel.

Les opérateurs suivants sont des opérateurs unaires :

• Aggregate
• Apply mutations
• Create batch
• Calcul
• Compute struct
• Filter
• Filter scan
• Limite
• Attribution d'ID aléatoire
• Serialize result
• Trier
• TVF
• Union input

Aggregate

L'opérateur aggregate met en œuvre les instructions SQL GROUP BY et les fonctions d'agrégation (telles que COUNT). L'entrée d'un opérateur aggregate est partitionnée de manière logique en groupes organisés en colonnes de clé (ou en un seul groupe en l'absence de l'instruction GROUP BY). Pour chacun des groupes, zéro ou plusieurs agrégats sont calculés.

Par exemple, si vous utilisez cette requête :

SELECT s.SingerId, AVG(s.duration) AS average, COUNT(*) AS count
FROM Songs AS s
GROUP BY SingerId;

La requête regroupe par ID de chanteur (SingerId), et effectue une agrégation AVG et une agrégation COUNT.

Vous obtenez les résultats suivants :

ID du chanteur	Moyenne	Nombre
3	278	1
2	225.875	8

Voici le plan d'exécution :

Les opérateurs agregate peuvent être basés sur les flux ou basés sur le hachage. Le plan d'exécution ci-dessus montre une agrégation basée sur les flux. Les agrégations basées sur les flux lisent des entrées déjà triées (si l'instruction GROUP BY est spécifiée) et calculent les groupes sans interruption. Les agrégations basées sur le hachage créent des tables de hachage pour gérer simultanément les agrégations incrémentielles de plusieurs lignes d'entrée. Les agrégations basées sur les flux sont plus rapides et consomment moins de mémoire que les agrégations basées sur le hachage, à condition que les entrées soient triées (par colonnes de clé ou par index secondaires).

Dans les scénarios distribués, un opérateur agregate peut être séparé en une paire locale/globale. Chaque serveur distant effectue l'agrégation locale sur ses lignes d'entrée, puis renvoie ses résultats au serveur racine. Le serveur racine se charge de l'agrégation globale.

Apply mutations

L'opérateur apply mutations applique à la table les mutations d'une instruction de manipulation de données (LMD). Il s'agit de l'opérateur principal du plan de requête pour une instruction LMD.

Par exemple, si vous utilisez cette requête :

DELETE FROM Singers
WHERE FirstName = 'Alice';

Vous obtenez les résultats suivants :

4 rows deleted
This statement deleted 4 rows and did not return any rows.

Voici le plan d'exécution :

Create batch

L'opérateur create batch regroupe ses lignes d'entrée dans une séquence. L'opération de création de lot a généralement lieu dans le cadre d'une opération d'application croisée distribuée. Les lignes d'entrée peuvent être réorganisées pendant le traitement par lot. Le nombre de lignes d'entrée qui sont regroupées à chaque exécution de l'opérateur de lot est variable.

Pour obtenir un exemple d'opérateur de création de lot dans un plan d'exécution, reportez-vous à l'opérateur Distributed cross apply.

Compute

L'opérateur compute génère un résultat en lisant ses lignes d'entrée et en ajoutant une ou plusieurs colonnes calculées à l'aide d'expressions scalaires. Pour obtenir un exemple d'opérateur compute dans un plan d'exécution, reportez-vous à l'opérateur union all.

Compute struct

L'opérateur compute struct crée une variable pour une structure contenant des champs pour chacune des colonnes d'entrée.

Par exemple, si vous utilisez cette requête :

SELECT FirstName,
       ARRAY(SELECT AS STRUCT song.SongName, song.SongGenre
             FROM Songs AS song
             WHERE song.SingerId = singer.SingerId)
FROM singers AS singer
WHERE singer.SingerId = 3;

Vous obtenez les résultats suivants :

Nom	Non spécifié
Alice	[["Not About The Guitar","BLUES"]]

Voici le plan d'exécution :

Dans le plan d'exécution, l'opérateur array subquery reçoit les entrées d'un opérateur distributed union, qui reçoit les entrées d'un opérateur compute struct. L'opérateur compute struct crée une structure à partir des colonnes SongName et SongGenre de la table Songs.

Filter

L'opérateur filter lit toutes les lignes à partir de ses entrées, applique un prédicat scalaire sur chaque ligne, puis ne renvoie que les lignes qui correspondent au prédicat.

Par exemple, si vous utilisez cette requête :

SELECT s.LastName FROM (SELECT s.LastName
FROM Singers AS s LIMIT 3) s
WHERE s.LastName LIKE 'Rich%';

Vous obtenez les résultats suivants :

Nom
Richards

Voici le plan d'exécution :

Le prédicat des chanteurs dont le nom de famille commence par Rich est implémenté en tant que filtre. L'entrée du filtre est le résultat d'une analyse d'index et le résultat du filtre correspond aux lignes où le nom de famille (LastName) commence par Rich.

Du point de vue des performances, lorsqu'un filtre est placé directement au-dessus d'une analyse, il a une incidence sur la lecture des données. Prenons l'exemple d'une table avec la clé k. Un filtre avec le prédicat k = 5 qui est placé directement au-dessus d'une analyse de la table recherchera les lignes correspondant à k = 5, sans lire l'intégralité de l'entrée. L'exécution de la requête sera donc plus efficace. Dans l'exemple ci-dessus, l'opérateur filter ne lit que les lignes concordant avec le prédicat WHERE s.LastName LIKE 'Rich%'.

Filter scan

L'opérateur filter scan se trouve toujours au-dessus d'une analyse de table ou d'index. Il se sert de l'analyse pour réduire le nombre de lignes lues dans la base de données. L'analyse qui en résulte est généralement plus rapide qu'avec un filtre. Spanner applique l'analyse du filtre dans certaines conditions :

• Condition de recherche: la condition de recherche s'applique si Spanner le peut déterminer une ligne spécifique à laquelle accéder dans la table. En règle générale, cette condition a lieu lorsque le filtre porte sur un préfixe de la clé primaire. Par exemple, si la clé primaire est constituée de Col1 et Col2, il est possible de rechercher une clause WHERE incluant des valeurs explicites pour Col1 ou pour Col1 et Col2. Dans ce cas, Spanner ne lit que les données qui se trouvent dans la plage de clés.
• Condition résiduelle : toute autre condition dans laquelle Spanner peut évaluer l'analyse afin de limiter la quantité de données lues.

Par exemple, si vous utilisez cette requête :

SELECT LastName
FROM Singers
WHERE SingerId = 1

Vous obtenez les résultats suivants :

Nom
Richards

Voici le plan d'exécution :

Limit

L'opérateur limit restreint le nombre de lignes renvoyées. Le paramètre OFFSET facultatif spécifie la ligne de départ à renvoyer. Dans les scénarios distribués, l'opérateur limit peut être séparé en une paire locale/globale. Chaque serveur distant applique la limite locale pour ses lignes de sortie, puis renvoie ses résultats au serveur racine. Le serveur racine regroupe les lignes envoyées par les serveurs distants, puis applique la limite globale.

Par exemple, si vous utilisez cette requête :

SELECT s.SongName
FROM Songs AS s
LIMIT 3;

Vous obtenez les résultats suivants :

Titre de la chanson
Not About The Guitar
The Second Time
Starting Again

Voici le plan d'exécution :

La limite locale est la limite de chaque serveur distant. Le serveur racine regroupe les lignes des serveurs distants, puis applique la limite globale.

Attribution d'ID aléatoire

Un opérateur d'attribution d'ID aléatoire génère un résultat en lisant ses lignes d'entrée et en ajoutant un nombre aléatoire à chaque ligne. Il utilise un opérateur Filter ou Sort pour réaliser des méthodes d'échantillonnage. Les méthodes d'échantillonnage compatibles sont les méthodes de Bernoulli et de Reservoir.

Par exemple, la requête suivante utilise l'échantillonnage de Bernoulli avec un taux d'échantillonnage de 10 %.

SELECT s.SongName
FROM Songs AS s TABLESAMPLE BERNOULLI (10 PERCENT);

Vous obtenez les résultats suivants :

Titre de la chanson
Starting Again
Nothing Is The Same

Notez que comme il s'agit d'un échantillon, le résultat peut varier à chaque nouvelle exécution de la requête même si la requête est identique.

Voici le plan d'exécution :

Dans ce plan d'exécution, l'opérateur Random Id Assign reçoit les entrées d'un opérateur distributed union, qui reçoit les entrées d'un opérateur index scan. L'opérateur renvoie les lignes avec des identifiants aléatoires, et l'opérateur Filter applique ensuite un prédicat scalaire sur les ID aléatoires et renvoie environ 10 % des lignes.

L'exemple suivant utilise l'échantillonnage Reservoir avec un taux d'échantillonnage de deux lignes.

SELECT s.SongName
FROM Songs AS s TABLESAMPLE RESERVOIR (2 ROWS);

Vous obtenez les résultats suivants :

Titre de la chanson
I Knew You Were Magic
The Second Time

Notez que comme il s'agit d'un échantillon, le résultat peut varier à chaque nouvelle exécution de la requête même si la requête est identique.

Voici le plan d'exécution :

Dans ce plan d'exécution, l'opérateur Random Id Assign reçoit les entrées d'un opérateur distributed union, qui reçoit les entrées d'un opérateur index scan. L'opérateur renvoie les lignes avec des identifiants aléatoires, et l'opérateur Sort applique ensuite l'ordre de tri des identifiants aléatoires et applique LIMIT avec deux lignes.

Serialize result

L'opérateur serialize result est un cas particulier de l'opérateur compute struct qui sérialise chaque ligne du résultat final de la requête pour le renvoyer au client.

Par exemple, si vous utilisez cette requête :

SELECT ARRAY(SELECT AS STRUCT so.SongName, so.SongGenre
             FROM Songs AS so
             WHERE so.SingerId = s.SingerId)
FROM Singers AS s;

La requête demande un tableau de titre de chanson (SongName) et de genre de chanson (SongGenre) basé sur l'ID du chanteur (SingerId).

Vous obtenez les résultats suivants :

Non spécifié
[]
[[Let's Get Back Together, COUNTRY], [Starting Again, ROCK]]
[[Not About The Guitar, BLUES]]
[]
[]

Voici le plan d'exécution :

L'opérateur serialize result crée un résultat qui contient, pour chaque ligne de la table Singers, un tableau de paires SongName et SongGenre pour les titres du chanteur.

Sort

L'opérateur sort lit ses lignes d'entrée, les classe par colonne, puis renvoie les résultats triés.

Par exemple, si vous utilisez cette requête :

SELECT s.SongGenre
FROM Songs AS s
ORDER By SongGenre;

Vous obtenez les résultats suivants :

SongGenre
BLUES
BLUES
BLUES
BLUES
MUSIQUE CLASSIQUE
COUNTRY
ROCK
ROCK
ROCK

Voici le plan d'exécution :

Dans ce plan d'exécution, l'opérateur sort reçoit ses lignes d'entrée d'un opérateur distributed union. Il trie ensuite les lignes d'entrée et les renvoie à un opérateur serialize result.

Pour restreindre le nombre de lignes renvoyées, l'opérateur sort peut éventuellement avoir les paramètres LIMIT et OFFSET. Dans les scénarios distribués, l'opérateur sort ayant un opérateur LIMIT et/ou OFFSET est séparé en une paire locale/globale. Chaque serveur distant applique l'ordre de tri et la limite locale/le décalage local pour ses lignes d'entrée, puis renvoie ses résultats au serveur racine. Le serveur racine regroupe les lignes envoyées par les serveurs distants, les trie, puis applique la limite/le décalage global.

Par exemple, si vous utilisez cette requête :

SELECT s.SongGenre
FROM Songs AS s
ORDER By SongGenre
LIMIT 3;

Vous obtenez les résultats suivants :

SongGenre
BLUES
BLUES
BLUES

Voici le plan d'exécution :

Le plan d'exécution montre la limite locale pour les serveurs distants et la limite globale pour le serveur racine.

TVF

L'opérateur table valued function produit un résultat en lisant ses lignes d'entrée et en appliquant la fonction spécifiée. La fonction peut implémenter un mappage et renvoyer le même nombre de lignes que l'entrée. Il peut également s'agir d'un générateur qui renvoie plus de lignes ou d'un filtre qui renvoie moins de lignes.

Par exemple, si vous utilisez cette requête :

SELECT Genre, SongName
FROM ML.PREDICT(MODEL GenreClassifier, Table Songs)

Vous obtenez les résultats suivants :

Genre	Titre de la chanson
Pays	Not About The Guitar
Rock	The Second Time
Pop	Starting Again
Pop	Nothing Is The Same
Pays	Let's Get Back Together
Pop	I Knew You Were Magic
Électro	Bleu
Rock	42
Rock	Fight Story

Voici le plan d'exécution :

Union input

L'opérateur union input renvoie les résultats à un opérateur union all. Pour obtenir un exemple d'opérateur union input dans un plan d'exécution, reportez-vous à l'opérateur union all.

Opérateurs binaires

L'opérateur binaire est un opérateur ayant deux enfants relationnels. Les opérateurs suivants sont des opérateurs binaires :

• Cross apply
• Hash join
• Merge join
• Push broadcast hash join
• Outer apply

Cross apply

L'opérateur cross apply exécute une requête de table sur chaque ligne extraite par une requête d'une autre table et renvoie l'union de toutes les exécutions de requêtes de la table. Les opérateurs cross apply et outer apply exécutent un traitement basé sur les lignes, contrairement aux opérateurs exécutant un traitement basé sur des ensembles tel que l'opérateur hash join. L'opérateur cross apply possède deux entrées : entrée et carte. L'opérateur cross apply applique chaque ligne du côté entrée au côté carte. Le résultat de l'application croisée contient des colonnes des deux côtés, entrée et carte.

Par exemple, si vous utilisez cette requête :

SELECT si.FirstName,
  (SELECT so.SongName
   FROM Songs AS so
   WHERE so.SingerId=si.SingerId
   LIMIT 1)
FROM Singers AS si;

La requête demande le prénom de chaque chanteur, ainsi que le titre d'un seul de leurs titres.

Vous obtenez les résultats suivants :

Nom	Non spécifié
Alice	Not About The Guitar
Catalina	Let's Get Back Together
David	NULL
Lea	NULL
Marc	NULL

La première colonne est renseignée à partir de la table Singers, et la deuxième colonne à partir de la table Songs. Si un ID de chanteur (SingerId) existe dans la table Singers, mais qu'il n'y a pas de SingerId correspondant dans la table Songs, la deuxième colonne contient la valeur NULL.

Voici le plan d'exécution :

Le nœud de premier niveau est un opérateur distributed union. L'opérateur distributed union envoie des sous-plans aux serveurs distants. Le sous-plan contient un opérateur serialize result qui calcule le prénom du chanteur et l'un de ses titres, puis sérialise chaque ligne du résultat.

L'opérateur serialize result reçoit ses entrées d'un opérateur cross apply. Le côté entrée de l'opérateur cross apply est une analyse de table sur la table Singers.

Le côté carte de l'opération d'application croisée contient les éléments suivants (de haut en bas) :

• Un opérateur aggregate qui renvoie Songs.SongName.
• Un opérateur limit qui limite le nombre de titres renvoyés à un par chanteur.
• Une analyse d'index sur l'index SongsBySingerAlbumSongNameDesc.

L'opérateur cross apply mappe chaque ligne du côté entrée à une ligne du côté carte ayant le même ID de chanteur (SingerId). La sortie de l'opérateur cross apply correspond à la valeur FirstName de la ligne d'entrée et à la valeur SongName de la ligne de carte. (La valeur SongName deviendra NULL si aucune ligne de mappage ne correspond à SingerId.) L'opérateur distributed union en haut du plan d'exécution combine ensuite toutes les lignes de sortie des serveurs distants et les renvoie en tant que résultats de la requête.

Hash join

L'opérateur hash join est une implémentation de jointures SQL basée sur le hachage. Les jointures de hachage exécutent un traitement basé sur des ensembles. L'opérateur hash join lit les lignes de l'entrée signalée comme build et les insère dans une table de hachage en fonction d'une condition de jointure. Il lit ensuite les lignes de l'entrée signalée comme probe. Pour chaque ligne lue à partir de l'entrée de vérification (probe), l'opérateur hash join recherche les lignes correspondantes dans la table de hachage. Il renvoie les lignes correspondantes en tant que résultat.

Par exemple, si vous utilisez cette requête :

SELECT a.AlbumTitle, s.SongName
FROM Albums AS a JOIN@{join_method=hash_join} Songs AS s
ON a.SingerId = s.SingerId AND a.AlbumId = s.AlbumId;

Vous obtenez les résultats suivants :

Titre de l'album	Titre de la chanson
Nothing To Do With Me	Not About The Guitar
Green	The Second Time
Green	Starting Again
Green	Nothing Is The Same
Green	Let's Get Back Together
Green	I Knew You Were Magic
Green	Blue
Green	42
Terrified	Fight Story

Voici le plan d'exécution :

Dans le plan d'exécution, build est une union distribuée qui envoie des analyses sur la table Albums. Probe est un opérateur distributed union qui envoie des analyses sur l'index SongsBySingerAlbumSongNameDesc. L'opérateur hash join lit toutes les lignes du côté build. Chaque ligne de build est placée dans une table de hachage en fonction des colonnes de la condition a.SingerId = s.SingerId AND a.AlbumId = s.AlbumId. L'opérateur hash join lit ensuite toutes les lignes du côté vérification. Pour chaque ligne de vérification, l'opérateur hash join recherche des correspondances dans la table de hachage. Les correspondances en résultant sont renvoyées par l'opérateur hash join.

Les correspondances de la table de hachage peuvent également être filtrées par une condition résiduelle avant d'être renvoyées. (Les conditions résiduelles apparaissent par exemple dans les jointures sans égalité.) Les plans d'exécution de jointure de hachage peuvent s'avérer complexes de par la gestion de la mémoire et les variantes de jointure. L'algorithme principal de jointure de hachage est adapté à la gestion des variantes de jointure, à savoir inner, semi, anti et outer.

Remarque : Un plan de requête visualisé affiche build et probe comme enfants gauche et droit, respectivement, d'une jointure de hachage.

Merge join

L'opérateur merge join (jointure par fusion) est une implémentation de jointure SQL basée sur la fusion. Les deux côtés de la jointure produisent des lignes triées en fonction des colonnes utilisées dans la condition de jointure. La jointure par fusion consomme simultanément les flux d'entrée et génère des lignes lorsque la condition de jointure est remplie. Si les entrées ne sont pas initialement triées comme requis, l'optimiseur ajoute des opérateurs Sort explicites au plan.

L'opérateur Merge join n'est pas automatiquement sélectionné par l'optimiseur. Pour utiliser cet opérateur, définissez la méthode de jointure sur MERGE_JOIN dans l'optimisation de requête, comme indiqué dans l'exemple suivant :

SELECT a.AlbumTitle, s.SongName
FROM Albums AS a JOIN@{join_method=merge_join} Songs AS s
ON a.SingerId = s.SingerId AND a.AlbumId = s.AlbumId;

Vous obtenez les résultats suivants :

Titre de l'album	Titre de la chanson
Vert	The Second Time
Green	Starting Again
Green	Nothing Is The Same
Green	Let's Get Back Together
Green	I Knew You Were Magic
Green	Blue
Green	42
Terrified	Fight Story
Nothing To Do With Me	Not About The Guitar

Voici le plan d'exécution :

Dans ce plan d'exécution, la jointure par fusion est distribuée afin de s'exécuter à l'emplacement des données. Cela permet également de procéder à la jointure par fusion sans ajouter d'opérateurs de tri supplémentaires, car les deux analyses de table sont déjà triées par SingerId et AlbumId, ce qui correspond à la condition de jointure. Dans ce plan, l'analyse du côté gauche de la table Albums avance lorsque son SingerId, AlbumId est relativement inférieur à la paire SingerId_1, AlbumId_1 de l'analyse d'index de SongsBySingerAlbumSongNameDesc du côté droit. De même, le côté droit avance chaque fois qu'il est inférieur au côté gauche. Cette avancée par fusion continue de rechercher des équivalences afin que les correspondances résultantes puissent être renvoyées.

Prenons un autre exemple de merge join à l'aide de la requête suivante :

SELECT a.AlbumTitle, s.SongName
FROM Albums AS a JOIN@{join_method=merge_join} Songs AS s
ON a.AlbumId = s.AlbumId;

Cet opérateur permet d'obtenir les résultats suivants :

Titre de l'album	Titre de la chanson
Total Junk	The Second Time
Total Junk	Starting Again
Total Junk	Nothing Is The Same
Total Junk	Let\'s Get Back Together
Total Junk	I Knew You Were Magic
Total Junk	Bleu
Total Junk	42
Total Junk	Not About The Guitar
Green	The Second Time
Green	Starting Again
Green	Nothing Is The Same
Vert	Let\'s Get Back Together
Vert	I Knew You Were Magic
Green	Blue
Green	42
Vert	Not About The Guitar
Nothing To Do With Me	The Second Time
Nothing To Do With Me	Starting Again
Nothing To Do With Me	Nothing Is The Same
Nothing To Do With Me	Let\'s Get Back Together
Nothing To Do With Me	I Knew You Were Magic
Nothing To Do With Me	Bleu
Nothing To Do With Me	42
Nothing To Do With Me	Not About The Guitar
Play	The Second Time
Play	Starting Again
Play	Nothing Is The Same
Play	Let\'s Get Back Together
Play	I Knew You Were Magic
Play	Bleu
Play	42
Play	Not About The Guitar
Terrified	Fight Story

Voici le plan d'exécution :

Dans le plan d'exécution précédent, l'optimiseur de requêtes a introduit d'autres opérateurs Sort pour obtenir les propriétés nécessaires à l'exécution de la jointure par fusion. La condition JOIN dans cet exemple de requête n'est appliquée qu'à AlbumId, qui n'est pas le mode de stockage des données. Par conséquent, un tri doit être ajouté. Le moteur de requête est compatible avec un algorithme de fusion distribuée, ce qui permet d'effectuer un tri local plutôt que global afin de distribuer et paralléliser les coûts de processeur.

Les correspondances obtenues peuvent également être filtrées sur la base d'une condition résiduelle avant d'être renvoyées. (Les conditions résiduelles apparaissent par exemple dans les jointures sans égalité) Les plans d'exécution de jointure par fusion peuvent être complexes en raison d'exigences de tri supplémentaires. L'algorithme principal de jointure par fusion est adapté à la gestion des variantes de jointure, à savoir inner, semi, anti et outer.

Push broadcast hash join

L'opérateur push broadcast hash join est une mise en œuvre des jointures SQL basée sur la jointure de hachage. L'opérateur "push broadcast hash join" lit les lignes du côté entrée afin de construire un lot de données. Ce lot est ensuite diffusé à tous les serveurs contenant des données côté carte. Sur les serveurs de destination où le lot de données est reçu, une jointure de hachage est créée en utilisant le lot comme données de côté compilation. Les données locales sont ensuite analysées en tant que côté vérification de la jointure de hachage.

L'optimiseur ne sélectionne pas automatiquement l'opérateur push broadcast hash join. Pour utiliser cet opérateur, définissez la méthode de jointure sur PUSH_BROADCAST_HASH_JOIN au niveau de l'optimisation de requête, comme indiqué dans l'exemple suivant :

SELECT a.AlbumTitle, s.SongName
FROM Albums AS a JOIN@{join_method=push_broadcast_hash_join} Songs AS s
ON a.SingerId = s.SingerId AND a.AlbumId = s.AlbumId;

Vous obtenez les résultats suivants :

Titre de l'album	Titre de la chanson
Vert	The Second Time
Green	Starting Again
Green	Nothing Is The Same
Vert	Lets Get Back Together
Vert	I Knew You Were Magic
Green	Blue
Green	42
Terrified	Fight Story
Nothing To Do With Me	Not About The Guitar

Voici le plan d'exécution :

L'entrée de la jointure de hachage de diffusion push est l'index AlbumsByAlbumTitle. Cette entrée est sérialisée en un lot de données. Ce lot est ensuite envoyé à toutes les divisions locales de l'index SongsBySingerAlbumSongNameDesc, où le lot est ensuite désérialisé et intégré à une table de hachage. La table de hachage utilise ensuite les données d'index local en tant que vérification renvoyant les correspondances obtenues.

Les correspondances de résultats peuvent également être filtrées sur la base d'une condition résiduelle avant d'être renvoyées. (Les conditions résiduelles apparaissent par exemple dans les jointures sans égalité.)

Outer apply

L'opérateur outer apply est semblable à un opérateur cross apply, à la différence qu'un opérateur outer apply vérifie que chaque exécution du côté carte renvoie au moins une ligne en créant, si nécessaire, une ligne de remplissage NULL. (En d'autres termes, il fournit une sémantique de jointure externe gauche.)

Opérateurs n-aires

L'opérateur N-aire est un opérateur comptant plus de deux enfants relationnels. Les opérateurs suivants sont des opérateurs N-aires :

Union all

L'opérateur union all combine tous les ensembles de lignes de ses enfants sans supprimer les doublons. Les opérateurs union all reçoivent leur entrée des opérateurs union input répartis sur plusieurs serveurs. L'opérateur union all requiert que ses entrées aient le même schéma, c'est-à-dire le même ensemble de types de données pour chaque colonne.

Par exemple, si vous utilisez cette requête :

SELECT 1 a, 2 b
UNION ALL
SELECT 3 a, 4 b
UNION ALL
SELECT 5 a, 6 b;

Le type de ligne des enfants est constitué de deux entiers.

Vous obtenez les résultats suivants :

a	b
1	2
3	4
5	6

Voici le plan d'exécution :

L'opérateur union all combine ses lignes d'entrée et, dans cet exemple, envoie les résultats à un opérateur serialize result.

Une requête comme la suivante aboutirait, car le même ensemble de types de données est utilisé pour chaque colonne, même si les enfants utilisent des variables différentes pour les noms de colonne :

SELECT 1 a, 2 b
UNION ALL
SELECT 3 c, 4 e;

Une requête comme celle ci-dessous n'aboutirait pas, car les enfants utilisent différents types de données pour chaque colonne :

SELECT 1 a, 2 b
UNION ALL
SELECT 3 a, 'This is a string' b;

Sous-requêtes scalaires

Une sous-requête scalaire est une sous-expression SQL faisant partie d'une expression scalaire. Spanner tente de supprimer les sous-requêtes scalaires chaque fois que possible. Cependant, dans certains scénarios, les plans peuvent explicitement contenir des sous-requêtes scalaires.

Par exemple, si vous utilisez cette requête :

SELECT FirstName,
IF(FirstName='Alice',
   (SELECT COUNT(*)
    FROM Songs
    WHERE Duration > 300),
   0)
FROM Singers;

Voici la sous-expression SQL :

SELECT COUNT(*)
FROM Songs
WHERE Duration > 300;

Vous obtenez les résultats suivants (de la requête complète) :

Nom
Alice	1
Catalina	0
David	0
Lea	0
Marc	0

Voici le plan d'exécution :

Le plan d'exécution contient une sous-requête scalaire, indiquée par Sous-requête scalaire, au-dessus d'un opérateur aggregate.

Spanner convertit parfois les sous-requêtes scalaires en un autre opérateur, tel qu'une jointure ou une application croisée, pour éventuellement améliorer les performances.

Par exemple, si vous utilisez cette requête :

SELECT *
FROM Songs
WHERE Duration = (SELECT MAX(Duration) FROM Songs);

Voici la sous-expression SQL :

SELECT MAX(Duration) FROM Songs;

Vous obtenez les résultats suivants (de la requête complète) :

ID de chanteur	ID de l'album	ID de la piste	Titre de la chanson	Durée	Genre de la chanson
2	1	6	Nothing Is The Same	303	BLUES

Voici le plan d'exécution :

Le plan d'exécution ne contient pas de sous-requête scalaire, car Spanner a converti la sous-requête scalaire en application croisée.

Sous-requêtes de tableau

La sous-requête de tableau est semblable à une sous-requête scalaire, mais elle est autorisée à utiliser plusieurs lignes d'entrée. Les lignes utilisées sont converties en un tableau de sortie scalaire unique contenant un élément par ligne d'entrée utilisée.

Par exemple, si vous utilisez cette requête :

SELECT a.AlbumId,
ARRAY(SELECT ConcertDate
      FROM Concerts
      WHERE Concerts.SingerId = a.SingerId)
FROM Albums AS a;

Voici la sous-requête :

SELECT ConcertDate
FROM Concerts
WHERE Concerts.SingerId = a.SingerId;

Les résultats de la sous-requête pour chaque ID d'album (AlbumId) sont convertis en un tableau de lignes ConcertDate par rapport à cet AlbumId. Le plan d'exécution contient une sous-requête de tableau, indiquée par Sous-requête de tableau, au-dessus d'un opérateur d'union distribuée :

Opérateurs distribués

Les opérateurs décrits plus haut s'exécutent dans les limites d'une seule machine. Les opérateurs distribués s'exécutent sur plusieurs serveurs.

Les opérateurs suivants sont des opérateurs distribués :

• Distributed union
• Union de fusion distribuée
• Distributed cross apply
• Distributed outer apply
• Apply mutations

L'opérateur distributed union est l'opérateur primitif d'où proviennent l'application croisée distribuée et l'application externe distribuée.

Les opérateurs distribués apparaissent dans les plans d'exécution avec une variante d'union distribuée en plus d'une ou plusieurs variantes d'union distribuée locale. Une variante d'union distribuée effectue la distribution à distance des sous-plans. Une variante d'union distribuée locale se trouve au-dessus de chacune des analyses effectuées pour la requête, comme indiqué dans le plan d'exécution suivant :

Les variantes d'union distribuée locale garantissent une exécution stable des requêtes lors de redémarrages visant à modifier de façon dynamique des limites de partition.

Chaque fois que cela est possible, une variante d'union distribuée comporte un prédicat de partition qui entraîne l'élimination de la partition, ce qui signifie que les serveurs distants n'exécutent des sous-plans que sur les partitions correspondant au prédicat. Cela permet d'améliorer la latence et les performances générales des requêtes.

Distributed union

L'opérateur distributed union divise de manière conceptuelle une ou plusieurs tables en différentes partitions, évalue à distance et de manière indépendante une sous-requête sur chaque partition, puis réunit tous les résultats.

Par exemple, si vous utilisez cette requête :

SELECT s.SongName, s.SongGenre
FROM Songs AS s
WHERE s.SingerId = 2 AND s.SongGenre = 'ROCK';

Vous obtenez les résultats suivants :

Titre de la chanson	Genre de la chanson
Starting Again	ROCK
The Second Time	ROCK
Fight Story	ROCK

Voici le plan d'exécution :

L'opérateur distributed union envoie des sous-plans aux serveurs distants, qui effectuent une analyse de table sur des partitions concordant avec le prédicat WHERE s.SingerId = 2 AND s.SongGenre = 'ROCK'. Un opérateur serialize result calcule les valeurs SongName et SongGenre à partir des lignes renvoyées par les analyses de table. L'opérateur distributed union renvoie ensuite les résultats combinés des serveurs distants en tant que résultats de la requête SQL.

Union de fusion distribuée

L'opérateur distributed merge union distribue une requête sur plusieurs serveurs distants. Il combine ensuite les résultats de la requête pour produire un résultat trié, appelé tri par fusion distribué.

Une union de fusion distribuée exécute les étapes suivantes :

1. Le serveur racine envoie une sous-requête à chaque serveur distant qui héberge une partition des données interrogées. La sous-requête inclut des instructions qui renvoient sont triés dans un ordre spécifique.

2. Chaque serveur distant exécute la sous-requête sur sa division, puis renvoie les résultats dans l'ordre demandé.

3. Le serveur racine fusionne la sous-requête triée pour produire un résultat entièrement trié résultat.

L'union fusionnée distribuée est activée par défaut pour Spanner version 3 et plus tard.

Distributed cross apply

L'opérateur distributed cross apply (DCA) étend l'opérateur cross apply en s'exécutant sur plusieurs serveurs. Le côté entrée de l'application croisée distribuée regroupe des lots de lignes (contrairement à un opérateur cross apply classique, qui agit sur une seule ligne d'entrée à la fois). Le côté carte de l'application croisée distribuée est un ensemble d'opérateurs cross apply qui s'exécutent sur des serveurs distants.

Par exemple, si vous utilisez cette requête :

SELECT AlbumTitle FROM Songs
JOIN Albums ON Albums.AlbumId=Songs.AlbumId;

Les résultats sont au format suivant :

Titre de l'album
Green
Nothing To Do With Me
Play
Total Junk
Green

Voici le plan d'exécution :

L'entrée de l'application croisée contient une analyse d'index sur SongsBySingerAlbumSongNameDesc qui regroupe les lignes de AlbumId. Le côté carte de cet opérateur cross apply est une analyse d'index sur l'index AlbumsByAlbumTitle, à condition que le prédicat de AlbumId dans la ligne d'entrée concorde avec la clé AlbumId dans l'index AlbumsByAlbumTitle. Le mappage renvoie la valeur SongName pour les valeurs SingerId dans les lignes d'entrée regroupées.

Récapitulons le processus d'application croisée distribuée utilisé dans cet exemple. L'entrée de l'application croisée distribuée correspond aux lignes regroupées de la table Albums. La sortie de l'application croisée distribuée correspond quant à elle à l'application de ces lignes à la carte de l'analyse d'index.

Distributed outer apply

L'opérateur distributed outer apply étend l'opérateur outer apply en s'exécutant sur plusieurs serveurs, de la même façon que l'opérateur distributed cross apply étend un opérateur cross apply.

Par exemple, si vous utilisez cette requête :

SELECT LastName, ConcertDate FROM Singers
LEFT OUTER JOIN@{JOIN_TYPE=APPLY_JOIN} Concerts
ON Singers.SingerId=Concerts.SingerId;

Les résultats sont au format suivant :

Nom	ConcertDate
Trentor	2014-02-18
Smith	2011-09-03
Smith	2010-06-06
Lomond	2005-04-30
Martin	2015-11-04
Richards

Voici le plan d'exécution :

Apply mutations

L'opérateur apply mutations applique à la table les mutations d'une instruction de manipulation de données (LMD). Il s'agit de l'opérateur principal du plan de requête pour une instruction LMD.

Par exemple, si vous utilisez cette requête :

DELETE FROM Singers
WHERE FirstName = 'Alice';

Vous obtenez les résultats suivants :

4 rows deleted
This statement deleted 4 rows and did not return any rows.

Voici le plan d'exécution :

Informations supplémentaires

Cette section décrit les éléments qui ne sont pas des opérateurs autonomes, mais qui exécutent des tâches pour accepter un ou plusieurs des opérateurs mentionnés ci-dessus. D'un point de vue technique, les éléments décrits ici sont des opérateurs, mais il ne s'agit pas d'opérateurs distincts dans votre plan de requête.

Constructeur de structure

Un constructeur de structure crée une structure, soit une collection de champs. Il crée généralement une structure pour les lignes résultant d'une opération de calcul. Un constructeur de structure n'est pas un opérateur autonome. Il fait plutôt partie des opérateurs compute struct ou serialize result.

Pour une opération de structure de calcul, le constructeur de structure crée une structure afin que les colonnes des lignes calculées puissent utiliser une référence de variable unique à la structure.

Pour une opération de sérialisation de résultat, le constructeur de structure crée une structure pour sérialiser les résultats.

Par exemple, si vous utilisez cette requête :

SELECT IF(TRUE, struct(1 AS A, 1 AS B), struct(2 AS A , 2 AS B)).A;

Vous obtenez les résultats suivants :

A
1

Voici le plan d'exécution :

Dans le plan d'exécution, les constructeurs de structure apparaissent au sein d'un opérateur serialize result.