Bonnes pratiques SQL

décembre 26, 2021Articles

Comme décrit dans les plans d’exécution de requête, le compilateur SSQL de Cloud Spanner transforme une instruction SQL en un plan d’exécution de requête, qui est utilisé pour obtenir les résultats de la requête. Cette page décrit les meilleures pratiques pour la construction d’instructions SQL pour aider Cloud Spanner à trouver des plans d’exécution efficaces.

Les exemples d’instructions SQL présentés dans cette page utilisent 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 la référence SQL complète, reportez-vous àtaxes de déclaration, Fonctions et opérateurs, structure lexicale et syntaxe.

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

Les requêtes paramétrées sont une technique d’exécution de requête qui sépare une chaîne de requête des valeurs de paramètres de requête. Par exemple, supposons que votre application doive récupérer des chanteurs qui ont publié des albums avec certains titres au cours d’une année donnée. Vous pouvez écrire une instruction SQL comme l’exemple suivant pour récupérer tous les albums intitulés « Love » qui ont été publiés en 2017:

SELECT a.SingerIdFROM Albums AS aWHERE a.AlbumTitle = 'Love' AND a.ReleaseDate >= '2017-01-01'

Dans une autre requête, vous pouvez changer la valeur du titre de l’album en « Peace »:

SELECT a.SingerIdFROM Albums AS aWHERE a.AlbumTitle = 'Peace' AND a.ReleaseDate >= '2017-01-01'

Si votre application doit exécuter de nombreuses requêtes similaires à celle-ci, dans lesquelles seule une valeur littérale change dans les requêtes suivantes, vous devez utiliser l’espace réservé aparameter pour cette valeur. La requête paramétrique résultante peut êtrecachée 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.SingerIdFROM Albums AS aWHERE a.AlbumTitle = @title AND a.ReleaseDate >= '2017-01-01'

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

Une référence de paramètre dans la requête utilise le caractère @ suivi du nom du paramètre, qui peut contenir n’importe quelle combinaison de lettres, de chiffres et de sous-scores.Les paramètres
peuvent apparaître partout où une valeur littérale est attendue.
Le même nom de paramètre peut être utilisé plus d’une fois dans un seul SQLstatement.
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.
En savoir plus sur la structure lexicale et la syntaxe du paramètre de requête syntaxinSQL.

En résumé, les paramètres de requête bénéficient de l’exécution de la requête de la manière suivante:

Plans pré-optimisés : Les requêtes qui utilisent 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 clé Cloud.
Composition de requête simplifiée : vous n’avez pas besoin d’échapper les valeurs de chaîne lors de leur fourniture dans les paramètres de requête. Les paramètres de requête réduisent également le risque d’erreurs de synchronisation.
Sécurité: les paramètres de requête rendent vos requêtes plus sécurisées en vous protégeant contre diverses attaques par injection SQL. Cette protection est particulièrement importantepour les requêtes que vous construisez à partir d’une entrée utilisateur.

Comprendre comment Cloud Spanner exécute les requêtes

Cloud Spanner vous permet d’interroger des bases de données à l’aide de déclarations SQL déclaratives spécifiant les données que vous souhaitez récupérer. Si vous voulez également comprendre Commentcloud Spanner obtient les résultats, vous devez utiliser des plans d’exécution de requête. Le plan d’exécution d’Aquery affiche le coût de calcul associé à chaque étape de la requête. En utilisant ces coûts, vous pouvez déboguer les problèmes de performances des requêtes et optimiser votre requête.

Vous pouvez récupérer les plans d’exécution des requêtes via la console Cloud ou les bibliothèques clientes.

Pour obtenir un plan de requête à l’aide de la console Cloud:

Ouvrez la page Instances de clé de cloud.

Accéder aux instances Cloud Spanner
Cliquez sur le nom de l’instance et de la base de données Cloud Spanner que vous souhaitez interroger.
Cliquez sur Requête.
Tapez la requête dans le champ de texte, puis cliquez sur Exécuter la requête.
Cliquez sur Explication.
La console Cloud affiche un plan d’exécution visuelle pour votre requête.

Pour plus d’informations sur les plans visuels, consultez Réglage d’une requête à l’aide du visualiseur de plan de requête.

Pour la référence complète du plan de requête, reportez-vous à Plans d’exécution de requête.

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

Comme 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 à l’aide de l’interface de lecture de Cloud Spanner. La façon la plus courante d’extraire des données d’anindex 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 de la requête.

Par exemple, supposons que vous vouliez récupérer les identifiants de tous les chanteurs avec un nom de famille aparticulaire. Une façon d’écrire une telle requête SQL est:

SELECT s.SingerIdFROM Singers AS sWHERE s.LastName = 'Smith';

Cette requête renverrait les résultats que vous attendez, mais cela pourrait prendre du temps pour renvoyer les résultats. Le calendrier dépendrait du nombre de lignes dans la table Singers et du nombre de lignes qui satisfont le prédicat WHERE s.LastName = 'Smith'. S’il n’y a pas d’index secondaire qui contienne la colonne LastName à lire, le plan de requête lirait la table Singers pour trouver les lignes qui correspondent au prédicat. La lecture de l’entiretable s’appelle une analyse de table complète, et une analyse de table complète est un moyen coûteux d’obtenir les résultats si la table ne contient qu’un faible pourcentage de Singers avec ce nom de famille.

Vous pouvez améliorer les performances de cette requête en définissant un index secondaire sur la colonne nom de famille:

CREATE INDEX SingersByLastName on Singers (LastName);

Étant donné que l’index secondaire SingersByLastName contient la colonne de table indexée LastName et la colonne de clé primaire SingerId, Cloud Spanner peut extraire toutes les données de la table d’index beaucoup plus petite au lieu d’analyser la table complète Singers.

Dans ce scénario, Cloud Spanner utiliserait probablement automatiquement l’index secondaire SingersByLastName lors de l’exécution de la requête. Cependant, il est préférable de dire explicitement à Cloud Spanner d’utiliser cet index en spécifiant une directive index dans la clause FROM:

SELECT s.SingerIdFROM [email protected]{FORCE_INDEX=SingersByLastName} AS sWHERE s.LastName = 'Smith';

Supposons maintenant que vous vouliez également récupérer le prénom du chanteur en plus de theID. Même si la colonne FirstName n’est pas contenue dans l’index, vous devez toujours spécifier la directive index comme précédemment:

SELECT s.SingerId, s.FirstNameFROM [email protected]{FORCE_INDEX=SingersByLastName} AS sWHERE s.LastName = 'Smith';

Vous bénéficiez toujours d’un avantage en termes de performances grâce à l’utilisation de l’index, car les opérateurs de cloud n’ont 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 satisfont le prédicat de l’index SingersByLastName, puis effectue une recherche à partir de la table de base Singers pour récupérer le premier nom uniquement pour ce sous-ensemble de lignes.

Si vous souhaitez éviter que Cloud Spanner n’ait à récupérer aucune ligne de la table de base, vous pouvez éventuellement stocker une copie de la colonne FirstName dans l’index lui-même:

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

L’utilisation d’une clause STORING comme celle-ci coûte un stockage supplémentaire, mais elle offre les avantages suivants pour les requêtes et les appels de lecture à l’aide de l’index:

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

Les exemples précédents illustrent comment les index secondaires peuvent accélérer les requêtes lorsque les lignes choisies par la clause WHERE d’une requête peuvent être rapidement identifiées à l’aide de l’index secondaire. Un autre scénario dans lequel les index secondaires peuvent offrir des avantages de performance concerne certaines requêtes qui renvoient des résultats ordonnés. Par exemple, supposons que vous vouliez récupérer tous les titres d’album et leurs dates de sortie et les renvoyer par ordre croissant de date de sortie et par ordre décroissant par titre d’album. Vous pouvez écrire une requête SQL comme celle-ci:

SELECT a.AlbumTitle, a.ReleaseDateFROM Albums AS aORDER 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 cet index secondaire:

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

Réécrivez ensuite la requête pour utiliser l’index secondaire:

SELECT a.AlbumTitle, a.ReleaseDateFROM [email protected]{FORCE_INDEX=AlbumsByReleaseDateTitleDesc} AS aORDER 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 de 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.

Étant donné que 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 l’ajout d’index secondaires peut ajouter de la latence à 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, avoir quelques index secondaires est très bien. Cependant, vous devriez considérer si vous vous souciez davantage de la latence en lecture ou en écriture, et considérer les opérations les plus critiques pour votre charge de travail. Vous devez également comparer votre charge de travail pour vous assurer qu’elle fonctionne comme prévu.

Pour la référence complète sur les index secondaires, reportez-vous aux index secondaires.

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

Une utilisation courante de la requête SQL consiste à lire plusieurs lignes à partir d’une clé Cloud basée sur une liste de clés connues.

Ce sont les meilleures pratiques pour écrire des requêtes efficaces lors de la récupération de données par une plage de clés:

Si la liste des clés est clairsemée et non adjacente, utilisez les paramètres de requête et UNNEST pour construire votre requête.

Par exemple, si votre liste de clés est {1, 5, 1000}, écrivez la requête comme ceci:
```
SELECT *FROM Table AS tWHERE t.Key IN UNNEST (@KeyList)
```
Notes:
- L’opérateur array UNNEST aplatit un tableau d’entrée en lignes d’éléments.
- @KeyList est un paramètre de requête, qui peut accélérer votre requête comme discuté dans la meilleure pratique précédente.
Si la liste des clés est adjacente et 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 est {1,2,3,4,5}, construisez la requête comme ceci:
```
SELECT *FROM Table AS tWHERE t.Key BETWEEN @min AND @max
```
Où @min et @max sont des paramètres de requête liés aux valeurs 1 et 5, respectivement.

Notez que cette requête n’est plus efficace que si les clés de la plage de clés sont adjacentes. En d’autres termes, si votre liste de clés est {1, 5, 1000}, vous ne devez pas spécifier les bornes inférieure et supérieure comme dans la requête précédente car la requête de recherche parcourrait chaque valeur comprise entre 1 et 1000.

Écrire des requêtes efficaces pour les jointures

Les opérations de jointure peuvent être coûteuses. En effet, JOIN s peut augmenter considérablement le nombre de lignes que votre requête doit analyser, ce qui entraîne des requêtes plus lentes. En plus des techniques que vous avez l’habitude d’utiliser dans d’autres bases de données relationnelles pour optimiser les requêtes de jointure, voici quelques bonnes pratiques pour une JOINTURE plus efficace lors de l’utilisation de Cloud Spanner SQL:

Si possible, joignez les données dans des tables entrelacées par clé primaire. Exemple:
```
SELECT s.FirstName, a.ReleaseDateFROM Singers AS s JOIN Albums AS a ON s.SingerId = a.SingerId;
```
Les lignes de la table entrelacée Albums sont garanties d’être physiquement stockées dans les mêmes divisions que la ligne parent dans Singers, comme discuté dans le schéma et le modèle de données. Par conséquent, JOIN s peut être implémenté localement sans envoyer beaucoup de données sur le réseau.
Utilisez la directive join si vous souhaitez forcer l’ordre du JOIN. Par exemple:
```
SELECT *FROM Singers AS s [email protected]{FORCE_JOIN_ORDER=TRUE} Albums AS aON s.SingerId = a.SingeridWHERE s.LastName LIKE '%x%' AND a.AlbumTitle LIKE '%love%';
```
La directive join @{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 et non Albums JOIN Singers). Les résultats renvoyés sont les mêmes quel que soit le fournisseur choisi par Cloud Spanner. Cependant, vous voudrez peut-être utiliser cette directive de jointure si vous remarquez dans le plan de requête que Cloud Spanner a modifié l’ordre de jointure et provoqué des résultats indésirables tels que des résultats intermédiaires plus importants ou a manqué des opportunités de recherche de lignes.
Utilisez une directive join pour choisir une implémentation de join. Choisir le bon algorithme commun pour votre requête peut améliorer la latence, la consommation de mémoire, oucette fois. Cette requête montre la syntaxe d’utilisationune directive JOIN avec l’indice JOIN_METHOD pour choisir un HASH JOIN:
```
SELECT *FROM Singers s [email protected]{JOIN_METHOD=HASH_JOIN} Albums AS aON a.SingerId = a.SingerId
```
Si vous utilisez un HASH JOIN ou APPLY JOIN et si 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 dans HASH JOIN, Cloud Spanner sélectionne toujours la table latérale gauche comme build et la table latérale droite comme probe. De même, pour APPLY JOIN, Cloud Spanner choisit le côté gauche comme extérieur et la table latérale droite comme intérieur. Voir plus d’informations sur ces types de jointures: Hash join et Apply join.

Évitez les grandes lectures dans les transactions en lecture-écriture

Les transactions en lecture-écriture permettent une séquence de zéro ou plus de requêtes SQL ou SQL, et peuvent inclure un ensemble de mutations, avant un appel à commit. Afin de maintenir la cohérence de vos données, Cloud Spanneracquiert des verrous lors de la lecture et de l’écriture de lignes dans vos tables et index (plus de détails sur le verrouillage de la durée de lecture et d’écriture).

En raison du fonctionnement du verrouillage dans Cloud Spanner, l’exécution d’une lecture ou d’une requête SQLquery qui lit un grand nombre de lignes (par exemple SELECT * FROM Singers) signifie qu’aucune autre transaction ne peut écrire sur les lignes que vous avez lues jusqu’à ce que votre transaction soit validée ou abandonnée. De plus, comme yourtransaction traite un grand nombre de lignes, il est susceptible de prendre plus de temps qu’une transaction qui lit une plage de lignes beaucoup plus petite (par exemple SELECTLastName FROM Singers WHERE SingerId = 7), ce qui aggrave encore le problème et réduit le débit du système.

Par conséquent, vous devriez essayer d’éviter les grandes lectures (par exemple: analyse de table complète ou opérations de jointure massives) à l’intérieur de 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:

Faites votre grande lecture dans une transaction en lecture seule. (Notez que les transactions en lecture seule n’utilisent pas de verrous et permettent donc un débit d’agrégat plus élevé.)
Si vous devez effectuer un traitement sur les données que vous venez de lire, il faut.
Démarrez une transaction en lecture-écriture.
Vérifiez que les lignes critiques qui vous intéressent n’ont pas changé de valeurs 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 semble correct, commettez vos mutations.

Une façon de vous assurer que vous évitez les grandes lectures à l’intérieur des transactions en lecture-écriture consiste à examiner les plans d’exécution générés par yourqueries.

Utilisez ORDER BY pour assurer l’ordre de vos résultats SQL

Si vous attendez un certain ordre pour les résultats d’une requête SELECT, vous devez explicitement inclure la clause ORDER BY. Par exemple : Si vous souhaitez répertorier tous les chanteurs dans l’ordre des clés primaires, utilisez cette requête:

SELECT * FROM SingersORDER BY SingerId;

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

SELECT * FROM Singers;

Cloud Spanner ne garantit pas que les résultats de cette requête seront dansordre de clé primaire. De plus, l’ordre des résultats peut changer à tout moment et n’est pas garanti d’être cohérent d’une invocation à l’autre.

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

Étant donné que Cloud Spanner n’évalue pas les modèles paramétrés LIKE jusqu’au moment de l’exécution, Cloud Spanner doit lire toutes les lignes et les évaluer par rapport à l’expression LIKE pour filtrer les lignes qui ne correspondent pas.

Dans les cas où un motif LIKE recherche des correspondances au début d’avalue et que la colonne est indexée, utilisez STARTS_WITH au lieu de LIKE. Cela permet à Cloud Spanner d’optimiser plus efficacement le plan d’exécution des requêtes.

Non recommandé:

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

Recommandé:

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