Optimiseur de requêtes

Qu'est-ce qu'un optimiseur de requêtes ?

L'optimiseur de requête SQL de Cloud Spanner convertit une instruction SQL déclarative, qui décrit les données interrogées par la requête, en un plan d'exécution impératif, qui décrit une manière d'obtenir précisément ces données. Le processus de transformation d'une instruction déclarative en un plan d'exécution de requête implique la transformation des structures d'arbres utilisées pour représenter la requête. L'optimiseur, au cours de la création d'un plan d'exécution, conserve la signification logique de la requête SQL d'origine afin de renvoyer les lignes correctes.

L'un des rôles importants de l'optimiseur est de concevoir un plan d'exécution efficace.

Comment l'optimiseur Cloud Spanner génère-t-il des plans d'exécution efficaces ?

L'optimiseur de Cloud Spanner utilise une combinaison d'heuristiques bien établies et d'optimisation des coûts pour produire des plans efficaces. Certaines heuristiques sont très simples, comme "appliquer les filtres sur les machines distantes plutôt que d'extraire des données sur la machine locale". D'autres heuristiques sont plus complexes, mais incarnent tout de même le principe de logique de déplacement visant à réduire le volume des données à proximité des données. Ce principe est particulièrement important dans un système qui répartit les données sur plusieurs machines.

Toutes les décisions d'exécution ne pouvant pas être prises efficacement en utilisant ces règles fixes, l'optimiseur de Cloud Spanner prend également des décisions basées sur le coût estimé des alternatives. Ces estimations de coûts sont calculées en utilisant la structure de la requête, le schéma de base de données et les estimations de volume de données produit par des fragments de la requête. Par exemple, Cloud Spanner estime le nombre de lignes de la table "Songs" qui correspondent au filtre (SongGenre = "Pays"), si ce filtre apparaît dans une requête. Pour faciliter le calcul de ces estimations, Cloud Spanner recueille régulièrement des statistiques sur les données utilisateur.

Pour en savoir plus sur les plans d'exécution de requêtes et leur utilisation par Cloud Spanner afin d'exécuter des requêtes dans un environnement distribué, consultez la page Plans d'exécution de requêtes.

Gestion des versions de l'optimiseur de requêtes

Au fil du temps, l'optimiseur de requêtes évoluera, élargissant la gamme de choix dans le plan d'exécution de requêtes et améliorant la précision des estimations qui informent ces choix, ce qui améliorera l'efficacité des plans d'exécution.

Cloud Spanner déploie les mises à jour de l'optimiseur en tant que nouvelles versions de l'optimiseur de requêtes. Par défaut, chaque base de données commence à utiliser la dernière version de l'optimiseur au plus tard 30 jours après sa publication.

Pour vous permettre de mieux maîtriser le scaling, nous vous proposons de gérer la version de l'optimiseur de requêtes utilisée par vos requêtes. Avant de procéder au commit vers la dernière version, vous pouvez comparer les profils de performances des requêtes entre les anciennes versions et la dernière version. Pour en savoir plus, consultez la section Gérer l'optimiseur de requêtes.

Historique des versions de l'optimiseur de requête

Vous trouverez ci-dessous un récapitulatif des mises à jour apportées à l'optimiseur de requêtes dans chaque version.

Version 3 : 1er Août 2021 (dernière version)

  • Ajoute un nouvel algorithme de jointure (jointure par fusion) activé à l'aide d'une nouvelle valeur d'optimisation de requête JOIN METHOD.

    Optimisation de déclaration : SQL @{join_method=merge_join} SELECT ...

    Optimisation de jointure : SQL SELECT ... FROM (...) JOIN@{join_method=merge_join} (...)

  • Ajoute un nouvel algorithme de jointure (jointure de hachage push) activé à l'aide d'une nouvelle valeur d'optimisation de requête JOIN METHOD.

    Optimisation de jointure : SQL SELECT ... FROM (...) JOIN@{join_method=push_broadcast_hash_join} (...)

  • Introduit l'union de fusion distribuée, qui est activée par défaut le cas échéant.

  • Légère amélioration des performances d'une analyse sous un opérateur GROUP BY lorsqu'il n'y a pas d'agrégation MAX ou MIN (ou HAVING MAX/MAX) dans la liste SELECT. Avant cette modification, Spanner charge la colonne supplémentaire non regroupée même si elle n'est pas requise par la requête.

    Exemple :

    Prenons l'exemple de table suivant.

    CREATE TABLE myTable(
      a INT64,
      b INT64,
      c INT64,
      d INT64)
    PRIMARY KEY (a, b, c);
    

    Avant cette modification, la requête suivante aurait chargé la colonne c, même si elle n'est pas requise par la requête.

    SELECT a, b
    FROM myTable
    GROUP BY a, b
    
  • Améliore les performances de certaines requêtes avec LIMIT lorsqu'un opérateur CrossApply est introduit par des jointures et que la requête demande des résultats triés avec LIMIT. Après cette modification, l'optimiseur applique d'abord le tri avec la limite du côté d'entrée de CrossApply.

    Exemple :

    SELECT a2.*
    FROM Albums@{FORCE_INDEX=_BASE_TABLE} a1
    JOIN Albums@{FORCE_INDEX=_BASE_TABLE} a2 USING(SingerId)
    ORDER BY a1.AlbumId
    LIMIT 2;
    
  • Améliore les performances des requêtes en envoyant davantage de calculs via JOIN.

    Transmet plus de calculs pouvant inclure une sous-requête ou une construction de structure via une jointure. Cela améliore les performances des requêtes de plusieurs manières : plus de calculs peuvent être effectués de manière distribuée et plus d'opérations dépendantes des calculs push peuvent être transférées. Par exemple, la requête a une limite et l'ordre de tri dépend de ces calculs. Dans ce cas, la limite peut également être transmise via une jointure.

    Exemple :

    SELECT
      t.ConcertDate,
      (
        SELECT COUNT(*) FROM UNNEST(t.TicketPrices) p WHERE p > 10
      ) AS expensive_tickets,
      u.VenueName
    FROM Concerts t
    JOIN Venues u ON t.VenueId = u.VenueId
    ORDER BY expensive_tickets
    LIMIT 2;
    

Version 2 : 1er Mars 2020 (par défaut)

  • Ajoute des optimisations dans la sélection d'index.
  • Améliore les performances des prédicats REGEXP_CONTAINS et LIKE dans certaines circonstances.
  • Améliore les performances d'une analyse sous un prédicat GROUP BY dans certains cas.

Version 1 : 18 juin 2019

  • Inclut de nombreuses optimisations basées sur des règles, telles que le pushdown de prédicat, le pushdown de limite, la jointure redondante et la suppression d'expressions redondantes.

  • Utilise les statistiques de données utilisateur pour sélectionner l'index à utiliser pour accéder à chaque table.

Packages de statistiques de l'optimiseur de requêtes

Cloud Spanner conserve des statistiques sur la distribution des données des colonnes de table afin d'estimer le nombre de lignes générées par une requête. L'optimiseur de requêtes utilise ces estimations pour vous aider à choisir le meilleur plan d'exécution de requêtes. Ces statistiques sont régulièrement mises à jour par Cloud Spanner. Étant donné que les statistiques sont utilisées pour choisir les plans d'exécution de requêtes, il est possible que Cloud Spanner modifie le plan de requête utilisé pour une requête lorsque les statistiques sont mises à jour.

Par défaut, les bases de données utilisent automatiquement le dernier package de statistiques généré. Vous pouvez épingler une version de package de statistiques antérieure pour forcer votre base de données à l'utiliser. Vous avez également la possibilité d'exécuter des requêtes individuelles avec un package de statistiques autre que le plus récent.

Récupération de mémoire dans les packages de statistiques

Cloud Spanner met à jour les packages de statistiques tous les trois jours. Les anciens packages sont conservés pendant 30 jours après leur création, après quoi ils sont soumis à la récupération de mémoire.

La table intégrée INFORMATION_SCHEMA.SPANNER_STATISTICS de Cloud Spanner contient une liste des packages de statistiques disponibles. Chaque ligne de ce tableau répertorie un package de statistiques par nom, et le nom contient l'horodatage de création du package. Chaque entrée contient également un champ appelé ALLOW_GC qui indique si un package peut faire l'objet d'une récupération de mémoire ou non.

Vous pouvez épingler l'un des packages répertoriés dans cette table pour forcer l'ensemble de votre base de données à l'utiliser. Le package de statistiques choisi ne fait pas l'objet d'une récupération de mémoire et la valeur de ALLOW_GC est ignorée tant que la base de données utilise ce package épinglé. Afin d'utiliser un package de statistiques particulier pour une requête individuelle, le package en question doit être répertorié avec ALLOW_GC=FALSE ou épinglé. Vous éviterez ainsi l'échec des requêtes après la récupération de mémoire du package de statistiques. La valeur de ALLOW_GC peut être modifiée à l'aide de l'instruction LDD ALTER STATISTICS.

Conservation des packages et informations personnelles

Un package de statistiques contient des histogrammes des données de colonne, conformément aux pratiques standards de l'industrie. Cela permet à l'optimiseur de requêtes de sélectionner les plans de requête optimaux. L'histogramme est construit à l'aide d'un petit échantillon de valeurs. Ce petit ensemble de données peut contenir des informations personnelles.

Cloud Spanner crée régulièrement un nouveau package de statistiques et le conserve pendant 30 jours par défaut. Ainsi, un petit échantillon des valeurs supprimées de la base de données peut être conservé pendant 30 jours supplémentaires dans les histogrammes de statistiques. Les packages de statistiques épinglés avec l'option de base de données optimizer_statistics_package ou les packages avec l'option ALLOW_GC=FALSE ne font pas l'objet d'une récupération de mémoire. Les histogrammes de ces packages peuvent contenir des valeurs supprimées de la base de données pendant une période plus longue. De plus, le contenu des packages de statistiques est inclus dans les sauvegardes de base de données.

Les statistiques de l'optimiseur sont stockées de la même manière que les données utilisateur.

La quantité totale d'espace de stockage requise pour ces packages est généralement inférieure à 100 Mo et est comptabilisée dans votre coût total de stockage.

Étape suivante