L'optimiseur de requêtes Spanner détermine le moyen le plus efficace d'exécuter une requête SQL. Toutefois, le plan de requête déterminé par l'optimiseur peut changent légèrement lorsque l'optimiseur de requêtes lui-même évolue ou lorsque la base de données les statistiques sont mises à jour. Minimiser le risque de régression des performances lorsque l'optimiseur de requêtes ou les statistiques changent, Spanner fournit les options de requête suivantes.
optimizer_version: les modifications apportées à l'optimiseur de requête sont groupées et publiées sous forme de versions d'optimiseur. Spanner commence à utiliser la dernière version de l'optimiseur par défaut au moins 30 jours après sa publication. Vous pouvez utiliser l'option de version de l'optimiseur de requêtes pour exécuter des requêtes sur une ancienne version de l'optimiseur.
optimizer_statistics_package : Spanner met à jour régulièrement les statistiques de l'optimiseur. Les nouvelles statistiques sont disponibles sous la forme d'un package. Cette option de requête spécifie un package de statistiques que l'optimiseur de requêtes doit utiliser. lors de la compilation d'une requête SQL. La récupération de mémoire doit être désactivée pour le package spécifié :
GoogleSQL
ALTER STATISTICS <package_name> SET OPTIONS (allow_gc=false)
PostgreSQL
ALTER STATISTICS spanner."" SET OPTIONS (allow_gc = true)
Ce guide explique comment définir ces options individuelles à différents niveaux d'étendue dans Spanner.
Répertorier les options de l'optimiseur de requêtes
Spanner stocke des informations sur les versions d'optimiseur disponibles et les packages de statistiques que vous pouvez sélectionner.
Versions de l'optimiseur
La version de l'optimiseur de requêtes est une valeur entière, incrémentée de 1 à chaque mise à jour. La dernière version de l'optimiseur de requêtes est 7.
Exécutez l'instruction SQL suivante pour renvoyer la liste de toutes les versions de l'optimiseur prises en charge, ainsi que les dates de disponibilité correspondantes et l'indication de si cette version est la version par défaut. Le numéro de version le plus élevé renvoyé est la dernière version prise en charge de l'optimiseur.
SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;
Version par défaut
Par défaut, Spanner commence à utiliser la dernière version de l'optimiseur au moins 30 jours après sa publication. Au cours de la période de 30 jours minimum entre la publication d'une nouvelle version et son adoption comme version par défaut, nous vous encourageons à tester les requêtes sur la nouvelle version afin de détecter toute régression.
Pour trouver la version par défaut, exécutez l'instruction SQL suivante :
SELECT * FROM SPANNER_SYS.SUPPORTED_OPTIMIZER_VERSIONS;
La requête renvoie une liste de toutes les versions d'optimiseur prises en charge. La colonne IS_DEFAULT
indique la version par défaut actuelle.
Pour en savoir plus sur chaque version, consultez l'article Historique des versions de l'optimiseur de requêtes.
Packages de statistiques de l'optimiseur
Chaque nouveau package de statistiques d'optimiseur créé par Spanner se voit attribuer un nom de package qui est garanti d'être unique dans la base de données donnée.
Le format du nom du package est auto_{PACKAGE_TIMESTAMP}UTC
.
Dans GoogleSQL, l'instruction ANALYZE
déclenche la création du nom du package de statistiques. Dans PostgreSQL, l'instruction ANALYZE
effectue cette tâche. Le format du nom du package de statistiques est analyze_{PACKAGE_TIMESTAMP}UTC
, où {PACKAGE_TIMESTAMP}
correspond à l'horodatage, au fuseau horaire UTC, du début de la création des statistiques. Exécutez l'instruction SQL suivante pour renvoyer la liste de tous les packages de statistiques de l'optimiseur disponibles.
SELECT * FROM INFORMATION_SCHEMA.SPANNER_STATISTICS;
Par défaut, Spanner utilise le dernier package de statistiques d'optimiseur sauf si la base de données ou la requête est épinglée sur un package plus ancien à l'aide de l'une des décrites sur cette page.
Priorité du forçage d'option
Si vous utilisez une base de données en dialecte GoogleSQL, Spanner propose plusieurs méthodes pour modifier les options de l'optimiseur. Par exemple, vous pouvez définir la ou les options d'une requête spécifique ou configurer l'option dans la bibliothèque cliente au moment du processus au niveau de la requête. Lorsqu'une option est définie de plusieurs manières, la priorité suivante s'applique. (Sélectionnez un lien pour accéder à cette section dans ce document.)
Valeur par défaut Spanner ← option de base de données ← application cliente ← variable d'environnement ← requête client ← optimisation de déclaration
Par exemple, voici comment interpréter l'ordre de priorité lors de la définition de la version de l'optimiseur de requêtes :
Lorsque vous créez une base de données, celle-ci utilise la version de l'optimiseur par défaut de Spanner. La définition de la version de l'optimiseur à l'aide de l'une des méthodes répertoriées ci-dessus prévaut sur tout élément situé à gauche de celle-ci. Par exemple, la définition de l'optimiseur pour une application à l'aide d'une variable d'environnement prévaut sur toute valeur définie pour la base de données à l'aide de l'option de base de données. La définition de la version de l'optimiseur via une optimisation d'instruction a la priorité la plus élevée pour la requête donnée, l'mportant sur la valeur définie à l'aide de toute autre méthode.
Étudions maintenant chaque méthode plus en détail.
Définir des options d'optimiseur au niveau de la base de données
Vous pouvez définir la version de l'optimiseur par défaut sur une base de données à l'aide de la commande LDD ALTER DATABASE
suivante.
GoogleSQL
ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version = 7);
PostgreSQL
ALTER DATABASE MyDatabase SET spanner.optimizer_version = 5;
Vous pouvez définir le package de statistiques de la même manière, comme illustré ci-dessous à titre d'exemple.
GoogleSQL
ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_statistics_package = "auto_20191128_14_47_22UTC");
PostgreSQL
ALTER DATABASE MyDatabase
SET spanner.optimizer_statistics_package = "auto_20191128_14_47_22UTC";
Vous pouvez également définir plusieurs options à la fois, comme indiqué dans la la commande LDD suivante.
GoogleSQL
ALTER DATABASE MyDatabase
SET OPTIONS (optimizer_version = 7,
optimizer_statistics_package = "auto_20191128_14_47_22UTC");
Vous pouvez exécuter ALTER DATABASE
dans gcloud CLI à l'aide de la commande
gcloud CLI databases ddl update
comme suit.
GoogleSQL
gcloud spanner databases ddl update MyDatabase --instance=test-instance \
--ddl='ALTER DATABASE MyDatabase SET OPTIONS ( optimizer_version = 7 )'
PostgreSQL
gcloud spanner databases ddl update MyDatabase --instance=test-instance \
--ddl='ALTER DATABASE MyDatabase SET spanner.optimizer_version = 7'
Définir une option de base de données sur NULL
(dans GoogleSQL) ou DEFAULT
(dans
PostgreSQL) l'efface afin que la valeur par défaut soit utilisée.
Pour connaître la valeur actuelle de ces options pour une base de données, interrogez la
Vue INFORMATION_SCHEMA.DATABASE_OPTIONS
pour GoogleSQL, ou la
information_schema database_options
pour PostgreSQL, comme indiqué ci-dessous.
GoogleSQL
SELECT
s.OPTION_NAME,
s.OPTION_VALUE
FROM
INFORMATION_SCHEMA.DATABASE_OPTIONS s
WHERE
s.SCHEMA_NAME=""
AND s.OPTION_NAME IN ('optimizer_version', 'optimizer_statistics_package')
PostgreSQL
SELECT
s.option_name,
s.option_value
FROM
information_schema.database_options s
WHERE
s.schema_name='public'
AND s.option_name IN ('optimizer_version',
'optimizer_statistics_package')
Définir des options d'optimiseur avec des bibliothèques clientes
Lorsque vous interagissez de manière automatisée avec Spanner via des bibliothèques clientes, il existe plusieurs façons de modifier les options de requête pour votre application cliente.
Vous devez utiliser les dernières versions des bibliothèques clientes pour définir les options d'optimiseur.
Définir les options de l'optimiseur pour un client de base de données
Une application peut définir des options d'optimiseur de manière globale sur la bibliothèque cliente en configurant la propriété des options de requête, comme indiqué dans les extraits de code suivants. Les paramètres de l'optimiseur sont stockés dans l'instance cliente et sont appliqués à toutes les requêtes exécutées tout au long de la durée de vie du client. Même si les options s'appliquent au niveau de la base de données dans le backend, elles s'appliquent à toutes les bases de données connectées via ce client lorsqu'elles sont définies au niveau du client.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Définir des options d'optimiseur avec des variables d'environnement
Pour vous aider à tester les différents paramètres de l'optimiseur sans avoir à recompiler votre application, vous pouvez définir les variables d'environnement SPANNER_OPTIMIZER_VERSION
et SPANNER_OPTIMIZER_STATISTICS_PACKAGE
et exécuter votre application, comme indiqué dans l'extrait suivant.
Linux/macOS
export SPANNER_OPTIMIZER_VERSION="7"
export SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"
Windows
set SPANNER_OPTIMIZER_VERSION="7"
set SPANNER_OPTIMIZER_STATISTICS_PACKAGE="auto_20191128_14_47_22UTC"
Les valeurs des options de l'optimiseur de requêtes spécifiées sont lues et stockées dans l'instance cliente au moment de l'initialisation du client. Elles s'appliquent à toutes les requêtes exécutées tout au long de la durée de vie du client.
Définir les options de l'optimiseur pour une requête cliente
Vous pouvez spécifier une valeur pour la version de l'optimiseur ou du package de statistiques à l'adresse au niveau de la requête dans votre application cliente en spécifiant des options de requête lors de la création de votre requête.
C++
C#
Go
Java
Node.js
PHP
Python
Ruby
Définir les options de l'optimiseur pour une requête à l'aide d'une optimisation d'instruction
Une optimisation d'instruction est une optimisation sur une instruction de requête qui modifie le comportement par défaut de l'exécution de la requête. La définition de l'optimisation OPTIMIZER_VERSION
sur une instruction force l'exécution de cette requête à l'aide de la version de l'optimiseur de requêtes spécifiée.
L'optimisation OPTIMIZER_VERSION
a la priorité de version d'optimiseur la plus élevée. Si l'optimisation de l'instruction est spécifiée, elle sera utilisée quels que soient les autres paramètres de version de l'optimiseur.
GoogleSQL
@{OPTIMIZER_VERSION=7} SELECT * FROM MyTable;
PostgreSQL
/*@OPTIMIZER_VERSION=7*/ SELECT * FROM MyTable;
Vous pouvez également utiliser le littéral latest_version pour définir la version de l'optimiseur pour une requête sur la dernière version, comme indiqué ici.
GoogleSQL
@{OPTIMIZER_VERSION=latest_version} SELECT * FROM MyTable;
PostgreSQL
/*@OPTIMIZER_VERSION=latest_version*/ SELECT * FROM MyTable;
Activer l'indice OPTIMIZER_STATISTICS_PACKAGE
Une instruction force l'exécution de cette requête à l'aide de l'optimiseur de requête spécifié.
version du package de statistiques. Le package spécifié
la récupération de mémoire doit être désactivée:
GoogleSQL
ALTER STATISTICS <package_name> SET OPTIONS (allow_gc=false)
PostgreSQL
ALTER STATISTICS spanner."package_name" SET OPTIONS (allow_gc=false)
L'optimisation OPTIMIZER_STATISTICS_PACKAGE
a la priorité de configuration du package d'optimiseur la plus élevée. Si l'indice d'instruction est spécifié, il sera utilisé.
quels que soient les autres paramètres
de version du package d'optimiseur.
@{OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC} SELECT * FROM MyTable;
Vous pouvez également utiliser le littéral latest pour utiliser le dernier package de statistiques.
@{OPTIMIZER_STATISTICS_PACKAGE=latest} SELECT * FROM MyTable;
Les deux suggestions peuvent être définies dans une seule instruction, comme illustré dans l'exemple suivant.
Le littéral default_version définit la version de l'optimiseur pour une requête sur la version par défaut, qui peut être différente de la dernière version. Voir Version par défaut pour plus de détails.
GoogleSQL
@{OPTIMIZER_VERSION=default_version, OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC} SELECT * FROM MyTable;
PostgreSQL
/*@OPTIMIZER_VERSION=default_version, OPTIMIZER_STATISTICS_PACKAGE=auto_20191128_14_47_22UTC*/ SELECT * FROM KeyValue;
Définir les options de l'optimiseur lorsque vous utilisez le pilote JDBC Spanner
Vous pouvez remplacer la valeur par défaut de la version et des statistiques de l'optimiseur en spécifiant des options dans la chaîne de connexion JDBC, comme indiqué dans le dans l'exemple suivant.
Ces options ne sont compatibles qu'avec les dernières versions du pilote JDBC Spanner.
Vous pouvez également définir la version de l'optimiseur de requêtes à l'aide de l'instruction SET OPTIMIZER_VERSION
, comme indiqué dans l'exemple suivant.
Pour en savoir plus sur l'utilisation du pilote Open Source, consultez la section Utiliser le pilote JDBC Open Source.
Comment les versions de l'optimiseur non valides sont gérées
Spanner est compatible avec plusieurs versions d'optimiseur.
Cette plage change au fil du temps lorsque l'optimiseur de requête est mis à jour. Si la version que vous spécifiez est en dehors de la plage, la requête échoue. Par exemple, si vous tentez d'exécuter une requête avec l'indice d'instruction @{OPTIMIZER_VERSION=8}
, mais que la version de l'optimiseur la plus récente n'est que 7
, Spanner renvoie le message d'erreur suivant :
Query optimizer version: 8 is not
supported
Gérer un paramètre de package de statistiques de l'optimiseur non valide
Vous pouvez épingler votre base de données ou votre requête à n'importe quel package de statistiques disponible à l'aide de l'une des méthodes décrites plus haut sur cette page. Une requête échoue si un nom de package de statistiques non valide est fourni. Un package de statistiques spécifié par une requête doit être :
Déterminer la version de l'optimiseur de requêtes utilisée pour exécuter une requête
La version de l'optimiseur utilisée pour une requête est visible dans la console Google Cloud et dans la Google Cloud CLI.
console Google Cloud
Pour afficher la version de l'optimiseur utilisée pour une requête, exécutez votre requête dans la section Spanner Studio de la console Google Cloud, puis sélectionnez Onglet Explication. Un message semblable au suivant doit s'afficher :
Version de l'optimiseur de requêtes: 7
CLI gcloud
Pour afficher la version utilisée lors de l'exécution d'une requête dans gcloud CLI, définissez
l'indicateur --query-mode
sur PROFILE
, comme illustré dans l'extrait suivant.
gcloud spanner databases execute-sql MyDatabase --instance=test-instance \
--query-mode=PROFILE --sql='SELECT * FROM MyTable'
Visualiser la version de l'optimiseur de requêtes dans l'explorateur de métriques
Cloud Monitoring collecte des mesures pour vous aider à comprendre les performances de vos applications et de vos services système. L'une des métriques collectées pour Spanner est le nombre de requêtes, qui mesure le nombre de requêtes d'une instance, échantillonnées au fil du temps. Bien que cette métrique soit très utile pour afficher les requêtes regroupées par code d'erreur, nous pouvons également l'exploiter pour déterminer la version de l'optimiseur utilisée pour exécuter chaque requête.
Vous pouvez utiliser l'explorateur de métriques dans la console Google Cloud pour visualiser le nombre de requêtes de votre instance de base de données. La figure 1 indique le nombre de requêtes pour trois bases de données. Vous pouvez voir quelle version de l'optimiseur est utilisée dans chaque base de données.
Le tableau situé sous le graphique de cette figure montre que my-db-1
a tenté de s'exécuter
une requête avec une version d'optimiseur non valide, renvoyant l'état Utilisation incorrecte
ce qui donne un nombre de requêtes de 0. Les autres bases de données ont exécuté des requêtes à l'aide des versions 1 et 2 de l'optimiseur.
Figure 1 : Nombre de requêtes affichées dans l'explorateur de métriques avec les requêtes regroupées par version de l'optimiseur.
Pour configurer un graphique similaire pour votre instance, procédez comme suit :
- Accédez à l'Explorateur de métriques dans la console Google Cloud.
- Dans le champ Resource type (Type de ressource), sélectionnez
Cloud Spanner Instance
. - Dans le champ Metric (Métrique), sélectionnez
Count of queries
. - Sélectionnez
database
,optimizer_version
etstatus
dans le champ Group By (Grouper par).
Cet exemple n'aborde pas le cas où une autre version de l'optimiseur est utilisée pour différentes requêtes dans la même base de données. Dans ce cas, le graphique affiche un segment à barres pour chaque combinaison de la base de données et de la version de l'optimiseur.
Pour apprendre à utiliser Cloud Monitoring pour surveiller vos instances Spanner, consultez la page Surveiller avec Cloud Monitoring.