Bonnes pratiques SQL

Comme décrit sur la page Plans d'exécution de requêtes, le compilateur SQL de Cloud Spanner transforme une instruction SQL en un plan d'exécution de requêtes, qui sert à obtenir les résultats d'une requête. Cette page décrit les bonnes pratiques pour la création d'instructions SQL qui aideront Cloud Spanner à trouver des plans d'exécution efficaces.

Les exemples d'instruction SQL présentés dans cette page suivent l'exemple de schéma ci-dessous :

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

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

Pour obtenir une documentation de référence complète sur SQL, consultez les pages Syntaxe des requêtes, Fonctions et opérateurs et Structure lexicale et syntaxe.

Utiliser des paramètres pour accélérer les requêtes fréquemment exécutées

Les requêtes paramétrées sont une technique d'exécution par laquelle une chaîne de requête est séparée des valeurs des paramètres de la requête. Par exemple, supposons que votre application veuille récupérer la liste des chanteurs ayant publié des albums avec un titre spécifique au cours d'une année donnée. Pour récupérer tous les albums intitulés "Love" parus en 2017, vous pouvez écrire une instruction SQL comme celle ci-dessous :

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'

Dans une autre requête, vous souhaitez remplacer la valeur du titre de l'album par "Peace" :

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

Si votre application doit exécuter un grand nombre de requêtes de ce type, dans lesquelles seule une valeur littérale est modifiée dans les requêtes suivantes, utilisez un espace réservé correspondant au paramètre pour cette valeur. La requête paramétrique obtenue peut être mise en cache et réutilisée, ce qui réduit les coûts de compilation.

Par exemple, la requête réécrite ci-dessous remplace Love par un paramètre nommé title :

SELECT a.SingerId
FROM Albums AS a
WHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

Remarques sur l'utilisation des paramètres de requête :

  • Dans une requête, les paramètres sont mentionnés à l'aide du caractère @ suivi du nom du paramètre, qui peut contenir une combinaison de lettres, de chiffres et de traits de soulignement.
  • Les paramètres peuvent apparaître partout où une valeur littérale est attendue.
  • Le même nom de paramètre peut être utilisé plusieurs fois dans une seule instruction SQL.
  • Spécifiez le paramètre de requête et la valeur à laquelle le lier dans le champ params de l'API de requête ExecuteSQL ou ExecuteStreamingSQL.
  • Apprenez-en davantage sur la syntaxe des paramètres de requête dans la page Structure lexicale et syntaxe.

En résumé, les paramètres favorisent l'exécution des requêtes de différentes manières :

  • Plans préoptimisés : les requêtes utilisant des paramètres peuvent être exécutées plus rapidement à chaque appel, car le paramétrage facilite la mise en cache du plan d'exécution par Cloud Spanner.
  • Composition de requête simplifiée : vous n'avez pas besoin d'échapper les valeurs de chaîne lorsque vous les fournissez dans les paramètres de requête. Les paramètres de requête réduisent également le risque d'erreurs de syntaxe.
  • Sécurité : les paramètres de requête renforcent la sécurité de vos requêtes en vous protégeant contre diverses attaques par injection SQL. Cette protection est particulièrement importante pour les requêtes que vous créez à partir d'entrées utilisateur.

Comprendre comment Cloud Spanner exécute les requêtes

Cloud Spanner vous permet d'interroger des bases de données à l'aide d'instructions SQL déclaratives spécifiant les données que vous souhaitez récupérer. Si vous voulez aussi comprendre comment Cloud Spanner obtient les résultats, utilisez un plan d'exécution de requêtes. Un plan d'exécution de requête affiche le coût de calcul associé à chaque étape de la requête. À l'aide de ces coûts, vous pouvez déboguer les problèmes de performance et optimiser votre requête.

Vous pouvez récupérer les plans d'exécution de requêtes via Cloud Console ou en utilisant les bibliothèques clientes.

Pour obtenir un plan de requêtes à l'aide de Cloud Console, procédez comme suit :

  1. Ouvrez la page "Instances" de Cloud Spanner.

    Accéder à la page Instances de Cloud Spanner

  2. Cliquez sur le nom de l'instance Cloud Spanner et de la base de données que vous souhaitez interroger.

  3. Cliquez sur Requête.

  4. Saisissez la requête dans le champ de texte, puis cliquez sur Exécuter la requête.

  5. Cliquez sur Explication.
    Cloud Console affiche un plan d'exécution visuel pour votre requête :

    Capture d'écran de la console

Pour obtenir la documentation de référence complète sur le plan, reportez-vous à la page Plans d'exécution de requêtes.

Utiliser des index secondaires pour accélérer les requêtes courantes

À l'instar d'autres bases de données relationnelles, Cloud Spanner propose des index secondaires que vous pouvez utiliser pour récupérer des données à l'aide d'une instruction SQL ou de l'interface de lecture de Cloud Spanner. Le moyen le plus courant d'extraire des données d'un index consiste à utiliser l'interface de requête SQL. L'utilisation d'un index secondaire dans une requête SQL vous permet de spécifier comment vous souhaitez que Cloud Spanner obtienne les résultats. La spécification d'un index secondaire peut accélérer l'exécution des requêtes.

Par exemple, supposons que vous souhaitiez récupérer les identifiants de tous les chanteurs portant un nom de famille particulier. La requête SQL pourrait être écrite de cette façon (par exemple) :

SELECT s.SingerId
FROM Singers AS s
WHERE s.LastName = 'Smith';

Cette requête renverrait les résultats attendus, mais son traitement pourrait prendre du temps. Le délai dépend du nombre de lignes de la table Singers et du nombre de lignes correspondant au prédicat WHERE s.LastName = 'Smith'. S'il n'y a pas d'index secondaire contenant la colonne LastName à lire, le plan de requête lira l'intégralité de la table Singers pour rechercher les lignes correspondant au prédicat. La lecture de la table entière s'appelle analyse complète de table, un moyen coûteux d'obtenir les résultats si la table ne contient qu'un faible pourcentage de Singers portant ce nom de famille.

Pour améliorer les performances de cette requête, définissez un index secondaire sur la colonne du nom de famille, comme suit :

CREATE INDEX SingersByLastName on Singers (LastName);

Comme l'index secondaire SingersByLastName contient la colonne LastName de la table indexée et la colonne de clé primaire SingerId, Cloud Spanner peut récupérer toutes les données de la table d'index qui est bien plus petite, sans avoir à effectuer une analyse complète de la table Singers.

Dans ce scénario, Cloud Spanner utilisera probablement l'index secondaire SingersByLastName de façon automatique lors de l'exécution de la requête. Cependant, il est préférable de le lui demander explicitement en spécifiant une directive d'index dans la clause FROM, comme suit :

SELECT s.SingerId
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

Supposons maintenant que vous souhaitiez également récupérer le prénom du chanteur en plus de son identifiant. Même si la colonne FirstName ne fait pas partie de l'index, vous devez toujours spécifier la directive d'index comme auparavant, de la manière suivante :

SELECT s.SingerId, s.FirstName
FROM Singers@{FORCE_INDEX=SingersByLastName} AS s
WHERE s.LastName = 'Smith';

Vous bénéficiez toujours d'un avantage en termes de performance grâce à l'utilisation de l'index, car Cloud Spanner n'a pas besoin d'effectuer une analyse complète de la table lors de l'exécution du plan de requête. Au lieu de cela, il sélectionne le sous-ensemble de lignes qui correspond au prédicat à partir de l'index SingersByLastName, puis effectue une recherche dans la table de base Singers pour n'extraire le prénom que pour ce sous-ensemble de lignes.

Si vous ne souhaitez pas que Cloud Spanner ait à récupérer des lignes de la table de base, vous pouvez éventuellement stocker une copie de la colonne FirstName dans l'index même comme suit :

CREATE INDEX SingersByLastName on Singers (LastName) STORING (FirstName);

L'utilisation d'une clause STORING comme celle-ci consomme du stockage supplémentaire, mais elle offre les avantages suivants pour les requêtes et les appels de lecture utilisant l'index :

  • Les requêtes SQL qui utilisent l'index et sélectionnent les colonnes stockées dans la clause STORING ne nécessitent pas de jointure supplémentaire à la table de base.
  • Les appels de lecture utilisant l'index peuvent lire les colonnes stockées dans la clause STORING.

Les exemples ci-dessus illustrent comment les index secondaires peuvent accélérer les requêtes, en particulier lorsque ce type d'index permet d'identifier rapidement les lignes choisies par la clause WHERE d'une requête. Un autre scénario dans lequel les index secondaires peuvent offrir des avantages en termes de performance concerne certaines requêtes qui renvoient des résultats ordonnés. Par exemple, supposons que vous souhaitiez extraire tous les titres d'album et leurs dates de sortie, et les renvoyer dans l'ordre croissant de la date de sortie et dans l'ordre décroissant des titres d'album. Vous pouvez écrire une requête SQL comme celle-ci :

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Sans index secondaire, cette requête nécessite une étape de tri potentiellement coûteuse dans le plan d'exécution. Vous pouvez accélérer l'exécution de la requête en définissant l'index secondaire suivant :

CREATE INDEX AlbumsByReleaseDateTitleDesc on Albums (ReleaseDate, AlbumTitle DESC);

Puis réécrivez la requête de sorte qu'elle utilise l'index secondaire comme suit :

SELECT a.AlbumTitle, a.ReleaseDate
FROM Albums@{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS a
ORDER BY a.ReleaseDate, a.AlbumTitle DESC;

Notez que cette requête et cette définition d'index répondent aux deux critères suivants :

  • La liste des colonnes dans la clause ORDER BY est un préfixe de la liste des clés d'index.
  • Toutes les colonnes de la table utilisée dans la requête sont couvertes par l'index.

Comme ces deux conditions sont remplies, le plan de requête résultant supprime l'étape de tri et s'exécute plus rapidement.

Bien que les index secondaires puissent accélérer les requêtes courantes, sachez que leur ajout peut rallonger la latence de vos opérations de validation, car chaque index secondaire nécessite généralement l'implication d'un nœud supplémentaire dans chaque validation. Pour la plupart des charges de travail, quelques index secondaires sont suffisants. Cependant, vous devez déterminer si vous êtes plus soucieux de la latence en lecture ou en écriture, ainsi que les opérations les plus critiques pour votre charge de travail. Vous devez également analyser votre charge de travail pour vous assurer qu'elle correspond à vos attentes.

Pour obtenir une documentation de référence complète sur les index secondaires, consultez la page Index secondaires.

Écrire des requêtes efficaces pour la recherche par plage de clés

Une requête SQL est couramment utilisée pour lire plusieurs lignes à partir de Cloud Spanner en fonction d'une liste de clés connues.

Voici les meilleures pratiques pour l'écriture de requêtes efficaces lors de l'extraction de données à l'aide d'une plage de clés :

  • Si la liste de clés est fragmentée et non adjacente, utilisez les paramètres de requête et UNNEST pour créer votre requête.

    Par exemple, si votre liste de clés correspond à {1, 5, 1000}, écrivez la requête de la manière suivante :

    SELECT *
    FROM Table AS t
    WHERE t.Key IN UNNEST (@KeyList)
    

    Notes :

    • L'opérateur de tableau UNNEST aplatit un tableau d'entrée sous la forme de lignes d'éléments.

    • @KeyList est un paramètre qui peut accélérer une requête, comme indiqué dans la bonne pratique ci-dessus.

  • Si la liste de clés est adjacente et comprise dans une plage, spécifiez la limite inférieure et la limite supérieure de la plage de clés dans la clause WHERE.

    Par exemple, si votre liste de clés correspond à {1,2,3,4,5}, créez la requête comme ceci :

    SELECT *
    FROM Table AS t
    WHERE t.Key BETWEEN @min AND @max
    

    @min et @max sont des paramètres de requête liés respectivement aux valeurs 1 et 5.

    Notez que cette requête est davantage efficace si les clés de la plage de clés sont adjacentes. En d'autres termes, si votre liste de clés correspond à {1, 5, 1000}, vous ne devez pas spécifier les limites inférieure et supérieure comme dans la requête ci-dessus, car la requête résultante risque d'analyser toutes les valeurs comprises entre 1 et 1 000.

Écrire des requêtes efficaces pour les jointures

Les opérations de jointure peuvent être coûteuses. En effet, JOIN peut augmenter considérablement le nombre de lignes que votre requête doit analyser, ce qui la ralentit. Outre les techniques que vous êtes habitué à utiliser dans d'autres bases de données relationnelles pour optimiser les requêtes de jointure, voici quelques pratiques recommandées pour une opération JOIN plus efficace lors de l'utilisation de Cloud Spanner SQL :

  • Si possible, joignez des données dans des tables entrelacées par clé primaire. Exemple :

    SELECT s.FirstName, a.ReleaseDate
    FROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;
    

    Vous avez la garantie que les lignes de la table entrelacée Albums sont physiquement stockées dans les mêmes divisions que la ligne parente dans Singers, comme indiqué sur la page Schéma et modèle de données. Par conséquent, les jointures (JOIN) peuvent être complétées localement sans envoyer énormément de données sur le réseau.

  • Utilisez la directive de jointure si vous souhaitez définir un certain ordre pour JOIN. Exemple :

    SELECT *
    FROM Singers AS s JOIN@{FORCE_JOIN_ORDER=TRUE} Albums AS a
    ON s.SingerId = a.Singerid
    WHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';
    

    La directive de jointure @{FORCE_JOIN_ORDER=TRUE} indique à Cloud Spanner d'utiliser l'ordre de jointure spécifié dans la requête (c'est-à-dire Singers JOIN Albums, pas Albums JOIN Singers). Les résultats renvoyés sont les mêmes quel que soit l'ordre choisi par Cloud Spanner. Toutefois, vous pouvez utiliser cette directive de jointure si vous remarquez dans le plan de requête que Cloud Spanner a modifié l'ordre de jointure et a entraîné des résultats indésirables, tels que des résultats intermédiaires plus importants, ou des occasions manquées de recherche de lignes.

  • Utilisez une directive de jointure pour choisir une mise en œuvre de jointure. Le choix du bon algorithme de jointure pour votre requête peut améliorer la latence, la consommation de mémoire ou les deux. Cette requête illustre la syntaxe d'une directive JOIN dotée de l'optimisation JOIN_METHOD pour choisir une jointure HASH JOIN :

    SELECT *
    FROM Singers s JOIN@{JOIN_METHOD=HASH_JOIN} Albums AS a
    ON a.SingerId = a.SingerId
    
  • Si vous utilisez une HASH JOIN ou une APPLY JOIN et que vous avez une clause WHERE très sélective d'un côté de votre JOIN, placez la table qui produit le plus petit nombre de lignes comme première table de la clause FROM de la jointure. En effet, actuellement pour HASH JOIN, Cloud Spanner choisit toujours la table du côté gauche pour effectuer des compilations et la table latérale du côté droit pour effectuer des vérifications. De même, pour APPLY JOIN, Cloud Spanner choisit la table du côté gauche comme table externe et celle du côté droit comme table interne. Pour plus d'informations sur ces types de jointure, consultez les sections Jointure de hachage et Jointure d'application.

Éviter les opérations de lecture de grande taille dans les transactions en lecture-écriture

Les transactions en lecture-écriture autorisent une séquence de zéro ou plusieurs lectures ou requêtes SQL et peuvent inclure un ensemble de mutations avant un appel à validation. Afin de maintenir la cohérence de vos données, Cloud Spanner acquiert des verrous lors de la lecture et de l'écriture de lignes dans vos tables et index (pour en savoir plus sur le verrouillage, consultez la page Déroulement des opérations de lecture et d'écriture).

En raison du fonctionnement du verrouillage dans Cloud Spanner, exécuter une requête de lecture ou une requête SQL lisant un grand nombre de lignes (par exemple, SELECT * FROM Singers) signifie qu'aucune autre transaction ne peut écrire sur les lignes lues tant que votre transaction n'est pas validée ou abandonnée. De plus, comme votre transaction traite un grand nombre de lignes, elle risque de prendre plus de temps qu'une transaction qui lit une plus petite plage de lignes (par exemple SELECT LastName FROM Singers WHERE SingerId = 7), ce qui accentue le problème et ralentit le débit du système.

Par conséquent, vous devriez essayer d'éviter les opérations de lecture de grande taille (par exemple, les analyses de table complète ou les très grandes opérations de jointure) dans vos transactions, sauf si vous êtes prêt à accepter un débit d'écriture inférieur. Dans certains cas, le modèle suivant peut donner de meilleurs résultats :

  1. Exécutez votre opération de lecture de grande taille dans une transaction en lecture seule. (Notez que les transactions en lecture seule n'utilisent pas de verrous et permettent donc un débit global plus élevé.)
  2. [Facultatif] Si vous devez effectuer un traitement sur les données que vous venez de lire, faites-le.
  3. Démarrez une transaction en lecture-écriture.
  4. Vérifiez que les lignes critiques qui vous intéressent n'ont pas changé de valeur depuis le moment où vous avez effectué la transaction en lecture seule à l'étape 1.
    1. Si les lignes ont changé, annulez votre transaction et recommencez à l'étape 1.
    2. Si tout se passe bien, validez vos mutations.

Pour éviter les opérations de lecture de grande taille dans les transactions en lecture-écriture, vous pouvez entre autres examiner les plans d'exécution générés par vos requêtes.

Utiliser ORDER BY pour garantir le classement de vos résultats SQL

Si vous souhaitez que les résultats d'une requête SELECT soient classés d'une certaine façon, vous devez explicitement inclure la clause ORDER BY. Par exemple, si vous voulez répertorier tous les chanteurs par ordre de clé primaire, utilisez la requête suivante :

SELECT * FROM Singers
ORDER BY SingerId;

Notez que Cloud Spanner ne garantit le classement des résultats que si la clause ORDER BY est présente dans la requête. En d'autres termes, considérez la requête suivante sans ORDER BY :

SELECT * FROM Singers;

Cloud Spanner ne garantit pas que les résultats de cette requête seront renvoyés par ordre de clé primaire. De plus, l'ordre des résultats peut changer à tout moment et ce classement n'est pas garanti d'un appel à l'autre.

Utiliser STARTS_WITH au lieu de LIKE pour accélérer les requêtes SQL paramétrées

Comme Cloud Spanner n'évalue pas les modèles LIKE paramétrés avant l'exécution, il doit lire toutes les lignes et les évaluer par rapport à l'expression LIKE afin d'exclure celles qui ne correspondent pas.

Dans les cas où un modèle LIKE recherche des correspondances au début d'une valeur et que la colonne est indexée, utilisez STARTS_WITH au lieu de LIKE. Cela permet à Cloud Spanner d'optimiser davantage le plan d'exécution de requêtes.

Option déconseillée :

SELECT a.AlbumTitle FROM Albums a
WHERE a.AlbumTitle LIKE @like_clause;

Option recommandée :

SELECT a.AlbumTitle FROM Albums a
WHERE STARTS_WITH(a.AlbumTitle, @prefix);