Ottime ottimizzazioni SQL che non dipendono dal modello di costo. Parte 2

Ottime ottimizzazioni SQL che non dipendono dal modello di costo. Parte 1

4. Eliminazione dei predicati “privi di significato”.

Altrettanto privi di significato sono i predicati che sono (quasi) sempre veri. Come puoi immaginare, se stai chiedendo:

SELECT * FROM actor WHERE 1 = 1;

...allora i database non lo eseguiranno effettivamente, ma semplicemente lo ignoreranno. Una volta ho risposto a una domanda al riguardo su Stack Overflow ed è per questo che ho deciso di scrivere questo articolo. Lascerò il test come esercizio al lettore, ma cosa succede se il predicato è un po' meno "privo di significato"? Per esempio:

SELECT * FROM film WHERE release_year = release_year;

Hai davvero bisogno di confrontare il valore con se stesso per ogni riga? No, non esiste alcun valore per il quale questo predicato sarebbe FALSE , giusto? Ma dobbiamo ancora verificarlo. Sebbene il predicato non possa essere uguale a FALSE , potrebbe essere uguale a NULL ovunque , sempre a causa della logica a tre valori. La colonna RELEASE_YEAR è nullable e se una qualsiasi delle righe ha RELEASE_YEAR IS NULL , allora NULL = NULL restituisce NULL e la riga deve essere eliminata. Quindi la richiesta diventa la seguente:

SELECT * FROM film WHERE release_year IS NOT NULL;

Quali database lo fanno?

DB2

SÌ!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

MySQL

È un peccato, ma MySQL, ancora una volta, non mappa i predicati nei piani di esecuzione, quindi capire se MySQL implementa questa particolare ottimizzazione è un po' complicato. È possibile eseguire una valutazione delle prestazioni e vedere se vengono effettuati confronti su larga scala. Oppure puoi aggiungere un indice:

CREATE INDEX i_release_year ON film (release_year);

E ottieni in cambio piani per le seguenti richieste:

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

Se l'ottimizzazione funziona, i piani di entrambe le query dovrebbero essere approssimativamente gli stessi. Ma in questo caso non è così:

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

Come puoi vedere, le nostre due query differiscono in modo significativo nei valori delle colonne POSSIBLE_KEYS e FILTERED . Quindi azzarderei un'ipotesi ragionevole che MySQL non lo ottimizzi.

Oracolo

SÌ!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

PostgreSQL

Sfortunatamente no!

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

I piani e i costi variano. Osserviamo cioè la valutazione della cardinalità, che non va assolutamente bene, mentre questo predicato:

SELECT * FROM film WHERE release_year IS NOT NULL;

dà risultati molto migliori:

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

Peccato!

server SQL

Stranamente, neanche SQL Server sembra fare questo:

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

Tuttavia, a giudicare dall’aspetto del piano, la valutazione della cardinalità è corretta, così come lo è il costo. Ma nella mia esperienza con SQL Server, direi che in questo caso non si verifica alcuna ottimizzazione, poiché SQL Server visualizzerebbe il predicato effettivamente eseguito nel piano (per capire perché, dai un'occhiata agli esempi di vincolo CHECK di seguito). Che dire dei predicati "privi di significato" su colonne NOT NULL ? La conversione di cui sopra era necessaria solo perché RELEASE_YEAR può essere indefinito. Cosa succede se esegui la stessa query priva di significato, ad esempio, sulla colonna FILM_ID ?

SELECT * FROM film WHERE film_id = film_id

Ora non corrisponde più ad alcun predicato? O almeno così dovrebbe essere. Ma lo è?

DB2

SÌ!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

Non viene applicato alcun predicato e selezioniamo tutti i film.

MySQL

SÌ! (Ancora una volta, ipotesi plausibile)

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

Nota che la colonna EXTRA ora è vuota, come se non avessimo alcuna clausola WHERE!

Oracolo

SÌ!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

Anche in questo caso non si applicano predicati.

PostgreSQL

Wow, no di nuovo!

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

Il filtro viene applicato e il punteggio di cardinalità è ancora 5. Che peccato!

server SQL

E anche qui no!

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

Riepilogo

Sembra una semplice ottimizzazione, ma non è utilizzata in tutti i DBMS; in particolare, stranamente, non viene utilizzata in SQL Server!

5. Proiezioni nelle sottoquery EXISTS

È interessante notare che mi viene chiesto continuamente di loro durante la mia master class, dove difendo il punto di vista secondo cui SELECT * di solito non porta a nulla di buono. La domanda è: è possibile utilizzare SELECT * in una sottoquery EXISTS ? Ad esempio, se dobbiamo trovare attori che hanno recitato in film...

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

E la risposta è si. Potere. L'asterisco non influisce sulla richiesta. Come puoi esserne sicuro? Considera la seguente query:

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

Tutti questi database riportano una divisione per zero errori. Nota una cosa interessante: in MySQL, quando dividiamo per zero, otteniamo NULL invece di un errore, quindi dobbiamo fare qualcos'altro che non è consentito. Ora, cosa succede se eseguiamo, invece di quanto sopra, le seguenti query?

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

Ora nessuno dei database restituisce un errore. Restituiscono tutti TRUE o 1 . Ciò significa che nessuno dei nostri database valuta effettivamente la proiezione (ovvero la clausola SELECT ) della sottoquery EXISTS . SQL Server, ad esempio, mostra il seguente piano:

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

Come puoi vedere, l' espressione CASE è stata convertita in una costante e la sottoquery è stata eliminata. Altri database memorizzano la sottoquery nel piano e non menzionano nulla sulla proiezione, quindi diamo un'altra occhiata al piano di query originale in Oracle:

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Il piano di query sopra è simile al seguente:

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

Osserviamo le informazioni sulla proiezione a Id=3 . In effetti, non accediamo nemmeno alla tabella FILM_ACTOR perché non ne abbiamo bisogno. Il predicato EXISTS può essere eseguito utilizzando un indice di chiave esterna su una singola colonna ACTOR_ID - tutto ciò che è necessario per questa query - anche se abbiamo scritto SELECT * .

Riepilogo

Fortunatamente, tutti i nostri database rimuovono la proiezione dalle sottoquery EXISTS : Resta sintonizzato per la Parte 3 , dove discuteremo di altre interessanti ottimizzazioni SQL.