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

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 Ottime ottimizzazioni SQL che non dipendono dal modello di costo. Parte 4

10. Spingere i predicati

Questa ottimizzazione non è del tutto appropriata in questo caso, perché non si può dire che non si basi affatto sul modello dei costi. Ma poiché non riesco a pensare a un solo motivo per cui l'ottimizzatore non dovrebbe inserire i predicati nelle tabelle derivate, lo elencherò qui, insieme al resto delle ottimizzazioni senza costi. Considera la richiesta:

SELECT *
FROM (
  SELECT *
  FROM actor
) a
WHERE a.actor_id = 1;

La tabella derivata in questa query non ha alcun senso e dovrebbe essere eliminata riducendo il numero di livelli di nidificazione delle query. Ma ignoriamolo per ora. Puoi aspettarti che il database esegua la seguente query invece di quella precedente:

SELECT *
FROM (
  SELECT *
  FROM actor
  WHERE actor_id = 1
) a;

E poi, ancora, magari eliminare la richiesta esterna. Un esempio più complesso si ottiene utilizzando UNION :

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
) people
WHERE people.last_name = 'DAVIS';

Il risultato di questa query:

FIRST_NAME  LAST_NAME  TYPE
----------------------------
JENNIFER    DAVIS      actor
SUSAN       DAVIS      actor
SUSAN       DAVIS      actor
JENNIFER    DAVIS      customer

Ora, sarebbe fantastico se l'ottimizzatore del database eseguisse invece una query come questa:

SELECT *
FROM (
  SELECT first_name, last_name, 'actor' type
  FROM actor
  WHERE last_name = 'DAVIS'
  UNION ALL
  SELECT first_name, last_name, 'customer' type
  FROM customer
  WHERE last_name = 'DAVIS'
) people;

Cioè, in modo da inserire il predicato nella tabella derivata e da lì in due sottoquery UNION ALL , poiché, dopo tutto, abbiamo un indice sia sulla colonna ACTOR.LAST_NAME che sulla colonna CUSTOMER.LAST_NAME . Ancora una volta, questa conversione è probabilmente basata sulle stime dei costi nella maggior parte dei database, ma continuo a pensare che sia un gioco da ragazzi poiché, con qualsiasi algoritmo, è quasi sempre meglio ridurre il numero di tuple elaborate il prima possibile. Se conosci un caso in cui una tale trasformazione si rivela una cattiva idea, sarò felice di sentire i tuoi commenti! sarei molto interessato Quindi, quale dei nostri database può farlo? (E per favore, è così semplice e così importante, lascia che la risposta sia: tutto)

DB2

Tabella derivata semplice Sì

Explain Plan
--------------------------------------------------
ID | Operation         |               Rows | Cost
 1 | RETURN            |                    |    6
 2 |  FETCH ACTOR      |   1 of 1 (100.00%) |    6
 3 |   IXSCAN PK_ACTOR | 1 of 200 (   .50%) |    0
Predicate Information
 3 - START (Q1.ACTOR_ID = 1)
      STOP (Q1.ACTOR_ID = 1)

Tabella derivata con UNION Inoltre sì:

Explain Plan
-----------------------------------------------------------------
ID | Operation                        |               Rows | Cost
 1 | RETURN                           |                    |   20
 2 |  UNION                           |             2 of 1 |   20
 3 |   FETCH CUSTOMER                 |   1 of 1 (100.00%) |   13
 4 |    IXSCAN IDX_CUSTOMER_LAST_NAME | 1 of 599 (   .17%) |    6
 5 |   FETCH ACTOR                    |   1 of 1 (100.00%) |    6
 6 |    IXSCAN IDX_ACTOR_LAST_NAME    | 1 of 200 (   .50%) |    0
Predicate Information
 4 - START (Q1.LAST_NAME = 'DAVIS')
      STOP (Q1.LAST_NAME = 'DAVIS')
 6 - START (Q3.LAST_NAME = 'DAVIS')
      STOP (Q3.LAST_NAME = 'DAVIS')

Inoltre, in entrambi i casi, la tabella derivata (vista) è stata esclusa dal piano in quanto non effettivamente necessaria.

MySQL

Tabella derivata semplice Sì

ID  TABLE  TYPE   KEY      REF    EXTRA
---------------------------------------
1   actor  const  PRIMARY  const

Viene utilizzato il consueto accesso con chiave primaria tramite valore costante. Tabella derivata con UNION Ops, no.

ID  SELECT_TYPE  TABLE       TYPE  KEY          REF    ROWS  EXTRA
------------------------------------------------------------------
1   PRIMARY        ref   	const  10
2   DERIVED      actor       ALL                       200
3   UNION        customer    ALL                       599

Risultati della conversione manuale nel piano:

ID  SELECT_TYPE  TABLE       TYPE  KEY                  REF    ROWS  EXTRA
--------------------------------------------------------------------------
1   PRIMARY        ALL                               5
2   DERIVED      actor       ref   idx_actor_last_name  const  3
3   UNION        customer    ref   idx_last_name        const  1

Questo è un problema serio quando si utilizzano query nidificate complesse in MySQL!

Oracolo

Tabella derivata semplice Sì, funziona.

---------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR    |      1 |      1 |      1 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ACTOR |      1 |      1 |      1 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ACTOR"."ACTOR_ID"=1)

E il numero di livelli di nidificazione è stato ridotto. Anche la tabella derivata con UNION funziona:

---------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | E-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |        |
|   1 |  VIEW                                 |                        |      4 |
|   2 |   UNION-ALL                           |                        |        |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR                  |      3 |
|*  4 |     INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME    |      3 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER               |      1 |
|*  6 |     INDEX RANGE SCAN                  | IDX_CUSTOMER_LAST_NAME |      1 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("LAST_NAME"='DAVIS')
   6 - access("LAST_NAME"='DAVIS')

Tuttavia, senza ridurre il numero di livelli di nidificazione. Id=1 "Visualizza" mostra che la tabella derivata è ancora presente. In questo caso non si tratta di un grosso problema, solo forse un piccolo costo aggiuntivo.

PostgreSQL

Tabella derivata semplice Sì, funziona:

QUERY PLAN
----------------------------------------------------
Seq Scan on actor  (cost=0.00..4.50 rows=1 width=25)
  Filter: (actor_id = 1)

Tieni presente, tuttavia, che PostgreSQL a volte non utilizza nemmeno la chiave primaria per cercare una singola riga, ma esegue invece la scansione dell'intera tabella. In questo caso, 200 righe × 25 byte per riga ("larghezza") rientrano in un blocco, quindi che senso ha preoccuparsi delle letture degli indici se non generare operazioni I/O non necessarie per accedere a una tabella così piccola? Tabella derivata con UNION Sì, funziona anche:

QUERY PLAN
-----------------------------------------------------------------------------------
Append  (cost=0.00..12.83 rows=4 width=45)
  ->  Seq Scan on actor  (cost=0.00..4.50 rows=3 width=45)
        Filter: ((last_name)::text = 'DAVIS'::text)
  ->  Index Scan using idx_last_name on customer  (cost=0.28..8.29 rows=1 width=45)
        Index Cond: ((last_name)::text = 'DAVIS'::text)

Anche in questo caso, non viene utilizzato un indice nella colonna ACTOR.LAST_NAME , ma viene utilizzato un indice nella colonna CUSTOMER.LAST_NAME poiché la tabella CLIENTI è molto più grande.

server SQL

Tabella derivata semplice Sì, funziona

|--Nested Loops(Inner Join)
     |--Index Seek(SEEK:([actor_id]=(1)))
     |--RID Lookup(OBJECT:([actor]))

Funziona anche la tabella derivata con UNION .

|--Concatenation
     |--Compute Scalar(DEFINE:([Expr1003]='actor'))
     |    |--Nested Loops(Inner Join)
     |         |--Index Seek(SEEK:([actor].[last_name]='DAVIS'))
     |         |--RID Lookup(OBJECT:([actor]))
     |--Compute Scalar(DEFINE:([Expr1007]='customer'))
          |--Nested Loops(Inner Join)
               |--Index Seek(SEEK:([customer].[last_name]='DAVIS'))
               |--RID Lookup(OBJECT:([customer]))

Riepilogo

Le mie speranze non si sono avverate. MySQL 8.0.2 non supporta ancora completamente questa semplice ottimizzazione. Tutti gli altri, invece, sono di supporto.

Conclusione

L’elenco qui presentato è lungi dall’essere completo. Esistono molte altre semplici trasformazioni SQL che non sono (o non dovrebbero essere) difficili da implementare per i database, anche prima che venga coinvolto un ottimizzatore dei costi. Eliminano il lavoro extra non necessario [per il database] ( al contrario del lavoro necessario e non necessario , di cui ho già scritto ). Questi sono strumenti importanti per:

1. Gli stupidi errori [dello sviluppatore] non hanno avuto alcun impatto sulle prestazioni. Gli errori sono inevitabili e man mano che il progetto cresce e le query SQL diventano più complesse, questi errori possono accumularsi, si spera senza alcun effetto.


2. Fornire la possibilità di riutilizzare blocchi complessi, come visualizzazioni e funzioni di tabella, che possono essere incorporati nelle query SQL principali, trasformati o parzialmente eliminati o riscritti.

Queste capacità sono fondamentali per il punto 2. Senza di essi, sarebbe molto difficile creare query SQL di 4000 righe con prestazioni normali basate su una libreria di componenti SQL riutilizzabili. Con grande disappunto degli utenti di PostgreSQL e MySQL, questi due popolari database open source hanno ancora molta strada da fare rispetto ai loro concorrenti commerciali DB2, Oracle e SQL Server, tra i quali DB2 ha ottenuto i migliori risultati, con Oracle e SQL Server all'incirca alla pari. collo, un po' indietro.