Optimiseur basé sur les coûts
Spark SQL utilise un optimiseur de coût (CBO) pour choisir des plans de query efficaces. L’optimiseur de coût utilise des statistiques de table et de colonne, comme la taille de la table et la distribution des données, pour estimer le coût des autres plans et choisir le plan avec le coût estimé le plus bas. C’est particulièrement important pour les queries avec des jointures multiples, où un mauvais plan peut être considérablement plus lent qu’un bon.
Le CBO est activé par default, mais il dépend des statistiques. Si les statistiques sont manquantes ou obsolètes, le CBO pourrait choisir un ordre de jointure ou une stratégie de jointure inefficace. Collecter les statistiques des tables et des colonnes et les tenir à jour. Voir Collecter des statistiques.
Bonnes pratiques
Pour aider le CBO à choisir des plans de query efficaces, suivez les pratiques suivantes :
- Recueillir des statistiques pour les tables utilisées dans les query multi-jointures. Si vous utilisez l’optimisation prédictive sur des tables gérées par Unity Catalog, les statistiques sont collectées pour vous. Voir Recueillir des statistiques.
- Vérifiez les plans de query lorsqu’une query multi-join est plus lente que prévu. Des statistiques manquantes ou inexactes sont une cause fréquente. Voir Vérifier les plans de query.
- Supprimer les statistiques ou désactiver le CBO uniquement pour atténuer un problème de planification spécifique causé par les statistiques. Voir Désactiver l'optimiseur basé sur le coût.
Collecter des statistiques
Pour profiter pleinement du CBO, il est important de collecter à la fois les statistiques de colonne et les statistiques de table . Vous pouvez utiliser la commande ANALYZE TABLE pour collecter manuellement des statistiques.
Pour maintenir les statistiques à jour, exécutez ANALYZE TABLE après avoir écrit dans la table.
Utilisation ANALYZE
L'optimisation prédictive exécute automatiquement ANALYZE, une commande de collecte de statistiques, sur les tables gérées par Unity Catalog. Databricks recommande d'activer l'optimisation prédictive pour toutes les tables gérées par Unity Catalog afin de simplifier la maintenance des données et de réduire les coûts de stockage. Voir ANALYZE TABLE … COMPUTE STATISTICS.
Vous pouvez supprimer les statistiques de l'optimiseur afin que ce dernier ne s'y fie plus, par exemple, pour atténuer un problème de planification causé par les statistiques. Voir ANALYZE TABLE... DROP STATISTICS.
Vérifier les plans de query
Il existe plusieurs façons de vérifier le plan de query.
commande EXPLAIN
Pour vérifier si le plan utilise des statistiques, utilisez la commande SQL EXPLAIN.
Si les statistiques sont manquantes, le plan de query pourrait ne pas être optimal.
== Optimized Logical Plan ==
Aggregate [s_store_sk], [s_store_sk, count(1) AS count(1)L], Statistics(sizeInBytes=20.0 B, rowCount=1, hints=none)
+- Project [s_store_sk], Statistics(sizeInBytes=18.5 MB, rowCount=1.62E+6, hints=none)
+- Join Inner, (d_date_sk = ss_sold_date_sk), Statistics(sizeInBytes=30.8 MB, rowCount=1.62E+6, hints=none)
:- Project [ss_sold_date_sk, s_store_sk], Statistics(sizeInBytes=39.1 GB, rowCount=2.63E+9, hints=none)
: +- Join Inner, (s_store_sk = ss_store_sk), Statistics(sizeInBytes=48.9 GB, rowCount=2.63E+9, hints=none)
: :- Project [ss_store_sk, ss_sold_date_sk], Statistics(sizeInBytes=39.1 GB, rowCount=2.63E+9, hints=none)
: : +- Filter (isnotnull(ss_store_sk) && isnotnull(ss_sold_date_sk)), Statistics(sizeInBytes=39.1 GB, rowCount=2.63E+9, hints=none)
: : +- Relation[ss_store_sk,ss_sold_date_sk] parquet, Statistics(sizeInBytes=134.6 GB, rowCount=2.88E+9, hints=none)
: +- Project [s_store_sk], Statistics(sizeInBytes=11.7 KB, rowCount=1.00E+3, hints=none)
: +- Filter isnotnull(s_store_sk), Statistics(sizeInBytes=11.7 KB, rowCount=1.00E+3, hints=none)
: +- Relation[s_store_sk] parquet, Statistics(sizeInBytes=88.0 KB, rowCount=1.00E+3, hints=none)
+- Project [d_date_sk], Statistics(sizeInBytes=12.0 B, rowCount=1, hints=none)
+- Filter ((((isnotnull(d_year) && isnotnull(d_date)) && (d_year = 2000)) && (d_date = 2000-12-31)) && isnotnull(d_date_sk)), Statistics(sizeInBytes=38.0 B, rowCount=1, hints=none)
+- Relation[d_date_sk,d_date,d_year] parquet, Statistics(sizeInBytes=1786.7 KB, rowCount=7.30E+4, hints=none)
La statistique rowCount est particulièrement importante pour les queries comportant plusieurs jointures. Si rowCount est manquant, cela signifie qu'il n'y a pas suffisamment d'informations pour le calculer (c'est-à-dire que certaines colonnes requises ne contiennent pas de statistiques).
Dans Databricks Runtime 16.0 et versions ultérieures, la sortie de la commande EXPLAIN liste les tables référencées qui ont des statistiques manquantes, partielles et complètes, par exemple :
== Optimizer Statistics (table names per statistics state) ==
missing = date_dim, store
partial =
full = store_sales
Corrective actions: consider running the following command on all tables with missing or partial statistics
ANALYZE TABLE <table-name> COMPUTE STATISTICS FOR ALL COLUMNS
Interface utilisateur Spark SQL
Utilisez la page de l'interface utilisateur Spark SQL pour voir le plan exécuté et la précision des statistiques.
Une ligne telle que rows output: 2,451,005 est: N/A signifie que cet opérateur produit environ 2 millions de lignes et qu'aucune statistique n'était disponible.
Une ligne telle que rows output: 2,451,005 est: 1616404 (1X) signifie que cet opérateur produit environ. 2 millions de lignes, alors que l'estimation était d'environ. 1,6 M et le facteur d'erreur d'estimation était de 1.
Une ligne telle que rows output: 2,451,005 est: 2626656323 signifie que cet opérateur produit environ 2M lignes alors que l'estimation était de 2B lignes, donc le facteur d'erreur d'estimation était de 1000.
Désactiver l'optimiseur basé sur les coûts
Le CBO est activé par default. Vous désactivez le CBO en modifiant l'indicateur spark.sql.cbo.enabled.
spark.conf.set("spark.sql.cbo.enabled", false)