Op kosten gebaseerde optimaliseerder

Spark SQL gebruikt een kostengebaseerde optimizer (CBO) om efficiënte queryplannen te kiezen. Het CBO gebruikt tabel- en kolomstatistieken, zoals tabelgrootte en gegevensverdeling, om de kosten van alternatieve plannen te schatten en het plan met de laagste geschatte kosten te kiezen. Dit is het belangrijkst voor queries met meerdere joins, waarbij een slecht plan misschien vele malen langzamer is dan een goed.

De CBO is standaard ingeschakeld, maar het hangt af van statistieken. Als statistieken ontbreken of verouderd zijn, kan de CBO kiezen voor een inefficiënte join-order of join-strategie. Verzamel tabel- en kolomstatistieken en houd ze up-to-date. Zie Statistieken verzamelen.

Aanbevolen procedures

Om het CBO te helpen efficiënte queryplannen te kiezen, volg deze praktijken:

• Verzamel statistieken voor tabellen die worden gebruikt in multi-join queries. Als je predictive optimization gebruikt op door Unity Catalog beheerde tabellen, worden statistieken voor je verzameld. Zie Statistieken verzamelen.
• Controleer queryplannen wanneer een multi-join query langzamer is dan verwacht. Ontbrekende of onnauwkeurige statistieken zijn een veelvoorkomende oorzaak. Zie Verifieer queryplannen.
• Verwijder statistieken of schakel de CBO alleen uit om een specifiek planningsprobleem te beperken dat wordt veroorzaakt door statistieken. Zie De kostengebaseerde optimizer uitschakelen.

Statistieken verzamelen

Om het volledige voordeel van de CBO te krijgen, is het belangrijk om zowel kolomstatistieken als tabelstatistiekente verzamelen. U kunt de ANALYZE TABLE opdracht gebruiken om handmatig statistieken te verzamelen.

ANALYZE gebruiken

Voorspellende optimalisatie voert automatisch ANALYZEuit, een opdracht om statistieken te verzamelen, in de door Unity Catalog beheerde tabellen. Databricks raadt aan voorspellende optimalisatie in te schakelen voor alle beheerde tabellen in Unity Catalog om het onderhoud van gegevens te vereenvoudigen en de opslagkosten te verlagen. Zie ANALYZE TABLE ... REKENSTATISTIEKEN.

U kunt optimalisatiestatistieken verwijderen, zodat de optimizer er niet langer van afhankelijk is, bijvoorbeeld om een planningsprobleem te verhelpen dat wordt veroorzaakt door de statistieken. Zie ANALYZE TABLE ... DROP STATISTICS.

Queryplannen controleren

Er zijn verschillende manieren om het queryplan te controleren.

EXPLAIN bevelen

Gebruik de SQL-opdracht EXPLAINom te controleren of het plan statistieken gebruikt.

Als er statistieken ontbreken, is het queryplan mogelijk niet optimaal.

== 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)

In Databricks Runtime 16.0 en hoger vermeldt de uitvoer van het EXPLAIN commando de gerefereerde tabellen met ontbrekende, gedeeltelijke en volledige statistieken, bijvoorbeeld:

== 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

Spark SQL-gebruikersinterface

Gebruik de pagina Spark SQL UI om het uitgevoerde plan en de nauwkeurigheid van de statistieken te bekijken.

Een lijn zoals rows output: 2,451,005 est: N/A betekent dat deze operator ongeveer 2 miljoen rijen produceert en dat er geen statistieken beschikbaar waren.

Een lijn zoals rows output: 2,451,005 est: 1616404 (1X) betekent dat deze operator ongeveer 2 miljoen rijen produceert, terwijl de schatting ongeveer 1,6 miljoen was en de schattingsfoutfactor 1 was.

Een lijn zoals rows output: 2,451,005 est: 2626656323 betekent dat deze operator ongeveer 2 miljoen rijen produceert terwijl de schatting 2B-rijen was, dus de schattingsfoutfactor was 1000.

Schakel de kostengebaseerde optimizer uit

De CBO is standaard ingeschakeld. U schakelt de CBO uit door de spark.sql.cbo.enabled vlag te wijzigen.

spark.conf.set("spark.sql.cbo.enabled", false)