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

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

8. VERIFICA le restrizioni

Oh, è roba fantastica! Il nostro database Sakila ha un vincolo CHECK sulla colonna FILM.RATING :

CREATE TABLE film (
  ..
  RATING varchar(10) DEFAULT 'G',
  ..
  CONSTRAINT check_special_rating
    CHECK (rating IN ('G','PG','PG-13','R','NC-17')),
  ..
);

Seriamente, utilizza i vincoli CHECK per garantire l'integrità dei dati. Il costo per aggiungerle è estremamente basso, molto inferiore rispetto ad altre restrizioni, ad esempio PRIMARY , UNIQUE o FOREIGN KEY , perché non richiedono un indice per funzionare, quindi le ottieni praticamente "gratuitamente". Ma c'è una sfumatura interessante legata all'ottimizzazione! Considera le seguenti query:

Predicati impossibili

Abbiamo già incontrato predicati impossibili , anche vincoli NOT NULL (che in realtà sono un tipo speciale di vincolo CHECK ), ma questo è ancora più interessante:

SELECT *
FROM film
WHERE rating = 'N/A';

Non esiste un film del genere, e non può esserci, poiché il vincolo CHECK ne impedisce l'inserimento (o l'aggiornamento). Ancora una volta, questo dovrebbe tradursi in un comando di non fare nulla. Che ne dici di questa richiesta?

CREATE INDEX idx_film_rating ON film (rating);

SELECT count(*)
FROM film
WHERE rating NOT IN ('G','PG','PG-13','R');

Grazie all'indice sopra, probabilmente è sufficiente fare una rapida scansione dell'indice e contare tutti i film con valutazione = 'NC-17' , poiché è l'unica valutazione rimasta. Quindi la query dovrebbe essere riscritta in questo modo:

SELECT count(*)
FROM film
WHERE rating = 'NC-17';

Questo dovrebbe essere il caso indipendentemente dall'indice, perché il confronto di una colonna con un valore è più veloce del confronto con 4. Quindi, quali database possono farlo?

DB2

Predicato impossibile (voto = 'N/A') Fantastico!

Explain Plan
-----------------------------------
ID | Operation      |   Rows | Cost
 1 | RETURN         |        |    0
 2 |  TBSCAN GENROW | 0 of 0 |    0

Predicate Information
 2 - RESID (1 = 0)

Predicato inverso (voto = 'NC-17') No...

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |   34
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |   34
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |   34

Predicate Information
 3 - SARG  NOT(Q1.RATING IN ('G', 'PG', 'PG-13', 'R'))

Sebbene il passaggio ID=3 utilizzi un indice e sebbene le cardinalità siano corrette, viene eseguita una scansione completa poiché il piano non dispone di un predicato di intervallo, ma solo del predicato "SARG". Vedi la recensione di Marcus Wynand per i dettagli . Puoi anche dimostrarlo invertendo manualmente il predicato e ottenendo:

Explain Plan
------------------------------------------------------------
ID | Operation                |                  Rows | Cost
 1 | RETURN                   |                       |    7
 2 |  GRPBY (COMPLETE)        |    1 of 210 (   .48%) |    7
 3 |   IXSCAN IDX_FILM_RATING | 210 of 1000 ( 21.00%) |    7

Predicate Information
 3 - START (Q1.RATING = 'NC-17')
      STOP (Q1.RATING = 'NC-17')

Ora abbiamo il predicato dell'intervallo desiderato.

MySQL

MySQL supporta la sintassi del vincolo CHECK , ma per qualche motivo non la applica. Prova questo:

CREATE TABLE x (a INT CHECK (a != 0));
INSERT INTO x VALUES (0);
SELECT * FROM x;

e otterrai:

A
-
0

Zero punti per MySQL (davvero, perché non supportare solo i vincoli CHECK ?)

Oracolo

Predicato impossibile (voto = 'N/A')

--------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |
|*  1 |  FILTER            |      |      1 |        |      0 |
|*  2 |   TABLE ACCESS FULL| FILM |      0 |     89 |      0 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)
   2 - filter("RATING"='N/A')

Ancora una volta, un filtro molto strano NULL IS NOT NULL , che elimina FULL TABLE SCAN , che potrebbe essere facilmente rimosso del tutto dal piano. Ma almeno funziona! Predicato inverso (voto = 'NC-17') Spiacenti:

----------------------------------------------------------------------------
| Id  | Operation             | Name            | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE       |                 |      1 |      1 |      1 |
|*  2 |   INDEX FAST FULL SCAN| IDX_FILM_RATING |      1 |    415 |    210 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter((RATING'PG-13' AND RATING'R' AND RATING'PG' AND RATING'G'))

Il predicato non può essere invertito, la valutazione della cardinalità è molto debole, inoltre otteniamo INDEX FAST FULL SCAN invece di INDEX RANGE SCAN e il predicato di filtro invece del predicato di accesso . Ma questo è ciò che dovremmo ottenere, ad esempio, invertendo manualmente il predicato:

------------------------------------------------------------------------
| Id  | Operation         | Name            | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |      1 |        |      1 |
|   1 |  SORT AGGREGATE   |                 |      1 |      1 |      1 |
|*  2 |   INDEX RANGE SCAN| IDX_FILM_RATING |      1 |    210 |    210 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RATING"='NC-17')

Peccato!

PostgreSQL

Tieni presente che la versione PostgreSQL del database Sakila utilizza il tipo ENUM invece dei vincoli CHECK nella colonna RATING . Ho invece duplicato la tabella utilizzando un vincolo CHECK . Predicato impossibile (valutazione = 'N/A') Non funziona:

QUERY PLAN
------------------------------------------------------
Seq Scan on film2  (cost=0.00..67.50 rows=1 width=385)
  Filter: ((rating)::text = 'N/A'::text)

Anche il predicato inverso (rating = 'NC-17') non funziona:

QUERY PLAN
------------------------------------------------------------------
Aggregate  (cost=70.53..70.54 rows=1 width=8)
  ->  Seq Scan on film2  (cost=0.00..70.00 rows=210 width=0)
        Filter: ((rating)::text  ALL ('{G,PG,PG-13,R}'::text[]))

Molto dispiaciuto! Nota: come ci ha gentilmente fatto notare David Rowley nei commenti , questa funzionalità può essere abilitata impostando il parametro:

SET constraint_exclusion TO on;
SQL Server
Невозможный предикат (rating = 'N/A')
Да!
|--Constant Scan

Predicato inverso (voto = 'NC-17') Sì anche!

|--Compute Scalar
     |--Stream Aggregate
          |--Index Seek(OBJECT:([idx_film_rating]), SEEK:([rating]='NC-17'))

Riepilogo

9. Connessioni riflessive non necessarie.

Man mano che le tue query diventano più complesse, potresti dover eseguire un join riflessivo su una tabella in base alla sua chiave primaria. Credetemi, questa è una pratica molto comune quando si creano viste complesse e le si collegano tra loro, quindi assicurarsi che il database presti attenzione a questo è una parte fondamentale dell'ottimizzazione del codice SQL complesso. Non dimostrerò un esempio complesso, ne basterà uno semplice, ad esempio:

SELECT a1.first_name, a1.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

Questo può essere visto come un caso speciale di eliminazione di JOIN , poiché in realtà non abbiamo bisogno di un join ad A2 , possiamo fare tutto ciò di cui abbiamo bisogno solo con la tabella A1 . Successivamente, l'eliminazione di INNER JOIN funziona correttamente solo se è presente una FOREIGN KEY , che qui non abbiamo. Ma grazie alla chiave primaria di ACTOR_ID , possiamo dimostrare che in effetti A1 = A2 . In un certo senso, anche questa è una chiusura transitiva . Puoi andare ancora oltre e utilizzare le colonne di entrambe le tabelle A1 e A2 :

SELECT a1.first_name, a2.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;

Nel classico caso di eliminazione JOIN , non sarebbe più possibile eliminarlo poiché vengono proiettate entrambe le tabelle. Ma poiché abbiamo già dimostrato che A1 = A2 , allora sono intercambiabili, quindi possiamo aspettarci che la query venga convertita in:

SELECT first_name, last_name
FROM actor;

Quale DBMS può fare questo?

DB2

Solo proiezione della tabella A1 Sì:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

Proiezione tabelle A1 e A2 ...anche sì:

Explain Plan
------------------------------------------------
ID | Operation     |                 Rows | Cost
 1 | RETURN        |                      |   20
 2 |  TBSCAN ACTOR | 200 of 200 (100.00%) |   20

MySQL

Solo proiezione del tavolo A1 N.

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id  Using index

Proiezione tavole A1 e A2 ... anche n

ID  TABLE  REF          EXTRA
-----------------------------------
1   a1
1   a2     a1.actor_id

Delusione totale...

Oracolo

Solo proiezione del tavolo A1 Sì

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

Proiezione tavoli A1 e A2 Ancora sì

--------------------------------------------
| Id  | Operation         | Name  | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT  |       |        |
|   1 |  TABLE ACCESS FULL| ACTOR |    200 |
--------------------------------------------

PostgreSQL

Solo proiezione della tabella A1 No:

QUERY PLAN
--------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=17)
  ->  Hash  (cost=4.00..4.00 rows=200 width=4)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=4)

Proiezione tabelle A1 e A2 E ancora no:

QUERY PLAN
---------------------------------------------------------------------
Hash Join  (cost=6.50..13.25 rows=200 width=13)
  Hash Cond: (a1.actor_id = a2.actor_id)
  ->  Seq Scan on actor a1  (cost=0.00..4.00 rows=200 width=10)
  ->  Hash  (cost=4.00..4.00 rows=200 width=11)
        ->  Seq Scan on actor a2  (cost=0.00..4.00 rows=200 width=11)

server SQL

Solo proiezione del tavolo A1 Stranamente no! (Ma tieni presente che sto utilizzando SQL Server 2014, le versioni più recenti potrebbero avere risolto il problema. Potrei sicuramente usare un aggiornamento!)

|--Merge Join(Inner Join, MERGE:([a2].[actor_id])=([a1].[actor_id]))
     |--Index Scan(OBJECT:([a2]))
     |--Sort(ORDER BY:([a1].[actor_id] ASC))
          |--Table Scan(OBJECT:([a1]))

Proiezione tabelle A1 e A2 Ancora no, e il piano è addirittura cambiato in peggio:

|--Hash Match(Inner Join, HASH:([a1].[actor_id])=([a2].[actor_id]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a1]))
     |--Table Scan(OBJECT:([sakila].[dbo].[actor] AS [a2]))

Riepilogo

Francamente mi aspettavo che questa ottimizzazione venisse eseguita su tutti i database, ma purtroppo mi sbagliavo di grosso. Oltre all'eliminazione di JOIN , questa è una delle ottimizzazioni più importanti, che consente di creare enormi query SQL da parti riutilizzabili come visualizzazioni e funzioni di tabella. Sfortunatamente, non è supportato in 3 dei 5 database più comuni.