Problemi di prestazioni SQL dovuti a "lavoro non necessario ma richiesto"

Livello di conoscenza richiesto per comprendere l'articolo: una conoscenza generale dei database e di SQL, qualche esperienza pratica con DBMS. Probabilmente la cosa più importante che puoi imparare per scrivere query SQL efficaci è l'indicizzazione. Tuttavia, al secondo posto, subito dietro, c'è la consapevolezza che molti client SQL richiedono che il database svolga molto "lavoro non necessario ma necessario" . Ripeti dopo di me: Cos’è il “lavoro non necessario ma obbligatorio”? Come ci dice Captain Obvious, lei:

Non necessario

Lascia che la nostra applicazione client abbia bisogno dei seguenti dati: Niente di insolito. Stiamo lavorando con un database di film (come il database Sakila ) e desideriamo visualizzare il titolo e la valutazione di tutti i film agli utenti. La seguente query può fornire il risultato di cui abbiamo bisogno:

SELECT title, rating
FROM film

Tuttavia, la nostra applicazione (o il nostro ORM) esegue invece questa query:

SELECT *
FROM film

Cosa otteniamo di conseguenza? Indovinare. Riceviamo molte informazioni inutili: Sulla destra puoi anche vedere il caricamento di alcuni JSON complessi:

• dal disco
• memorizzare nella cache
• tramite filo
• in memoria del cliente
• e infine buttato via [in quanto non necessario]

Sì, buttiamo via la maggior parte di queste informazioni. Tutte le azioni intraprese per estrarre queste informazioni si sono rivelate completamente inutili. È vero? È vero.

Obbligatorio

E ora... la parte peggiore. Sebbene ora gli ottimizzatori possano fare molto, queste azioni sono obbligatorie per il database. Il database non ha modo di sapere se l'applicazione client non necessita del 95% di questi dati. E questo è solo l'esempio più semplice. Immagina di connettere più tabelle... E allora, dici, ma i database sono veloci? Lascia che ti illumini su alcune cose a cui probabilmente non hai pensato. Naturalmente il tempo di esecuzione di una singola richiesta non influisce su nulla. Ok, è andato una volta e mezza più lentamente, ma ce la faremo, giusto? Per comodità? A volte questo è vero. Ma se sacrifichi sempre le prestazioni per la comodità , queste piccole cose inizieranno a sommarsi. Non parleremo più di prestazioni (la velocità di esecuzione delle singole richieste), ma di throughput (tempo di risposta del sistema), e poi inizieranno problemi seri, che non sono così facili da risolvere. Questo è quando perdi la scalabilità. Diamo un'occhiata ai piani di esecuzione, in questo caso Oracle DBMS:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   166K|
|   1 |  TABLE ACCESS FULL| FILM |  1000 |   166K|
--------------------------------------------------

paragonato a:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 | 20000 |
|   1 |  TABLE ACCESS FULL| FILM |  1000 | 20000 |
--------------------------------------------------

Eseguendo una query SELECT * invece del titolo SELECT, la valutazione utilizza 8 volte più memoria nel database. Niente di inaspettato, vero? Sapevamo che sarebbe successo. Ma siamo comunque d'accordo per molte delle nostre richieste in cui semplicemente non abbiamo bisogno di tutti questi dati. Creiamo lavoro non necessario ma obbligatorio per il database , che continua ad accumularsi e ad accumularsi. Usiamo 8 volte più memoria del necessario (il moltiplicatore cambierà, ovviamente). Nel frattempo, in tutte le altre fasi (I/O del disco, trasferimento dei dati in rete, consumo di memoria da parte del client) i problemi sono esattamente gli stessi, ma li salterò e guarderò invece a...

Utilizzo degli indici

La maggior parte dei database oggi ha già apprezzato il concetto di copertura degli indici . Un indice di copertura non è di per sé un tipo speciale di indice. Ma potrebbe rivelarsi un "indice speciale" per una query particolare, "per sbaglio" o perché era previsto che fosse così. Considera la seguente query:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

Non c'è nulla di inaspettato in termini di implementazione. Questa è una semplice richiesta. Visualizza l'intervallo per indice, accedi alla tabella e il gioco è fatto:

-------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR               |     8 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACTOR_LAST_NAME |     8 |
-------------------------------------------------------------------

Buon piano, vero? Bene, se ne avessimo davvero bisogno, allora no: Ovviamente stiamo sprecando memoria, ecc. Consideriamo questa query come alternativa:

SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'

Il suo piano è questo:

----------------------------------------------------
| Id  | Operation        | Name            | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT |                 |     8 |
|*  1 |  INDEX RANGE SCAN| IDX_ACTOR_NAMES |     8 |
----------------------------------------------------

Siamo riusciti ad eliminare completamente l'accesso alla tabella, grazie alla presenza di un indice che soddisfa tutte le esigenze della nostra query... un indice di copertura. È importante? E come! Questo approccio consente di accelerare alcune query di un ordine di grandezza (o di rallentarle di un ordine di grandezza quando l'indice non copre più dopo alcune modifiche). Non sempre è possibile utilizzare gli indici di copertura. Devi pagare per gli indici e non dovresti aggiungerne troppi. Ma in questo caso tutto è ovvio. Valutiamo la prestazione:

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 100000;
BEGIN
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Наихудший вариант requestа: перерасход памяти ПЛЮС доступ к таблице
      SELECT *
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Улучшенный request, но все равно с доступом к таблице
      SELECT /*+INDEX(actor(last_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Оптимальный request: покрывающий индекс
      SELECT /*+INDEX(actor(last_name, first_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/

Tieni presente che la tabella degli attori ha solo 4 colonne, quindi la differenza di prestazioni tra le istruzioni 1 e 2 non è così grande, ma è comunque significativa. Noterò anche che ho utilizzato i suggerimenti dell'ottimizzatore Oracle per fare in modo che l'ottimizzatore selezionasse uno o un altro indice specifico per la query. L'operatore 3 è il vincitore indiscusso della nostra gara. Le sue prestazioni sono molto migliori e stiamo parlando di una query estremamente semplice. Ancora una volta, quando scriviamo SELECT *, creiamo lavoro non necessario ma obbligatorio per il database che non può ottimizzare. Non selezionerà l'indice di copertura perché ha un sovraccarico leggermente superiore rispetto all'indice LAST_NAME che ha scelto e, tra le altre cose, deve ancora accedere alla tabella per recuperare una colonna LAST_UPDATE inutile, ad esempio. Ma più a fondo analizziamo SELECT *, peggiori risultano essere le cose. Parliamone...

Conversioni SQL

Gli ottimizzatori funzionano così bene perché trasformano le query SQL ( ho parlato di come funziona nel mio recente intervento al Voxxed Days di Zurigo ). Ad esempio, esiste una trasformazione "eccezione JOIN" estremamente potente. Considera la seguente vista helper che abbiamo dovuto creare per evitare di unire manualmente tutte queste tabelle ogni volta:

CREATE VIEW v_customer AS
SELECT
  c.first_name, c.last_name,
  a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)

Questa visualizzazione esegue semplicemente tutti i join "...-to-one" tra la tabella clienti CUSTOMER e varie tabelle per parti del loro indirizzo. Grazie, normalizzazione. Immagina che, dopo aver lavorato un po' con questa visualizzazione, ci siamo abituati e ci siamo dimenticati delle tabelle sottostanti. E ora eseguiamo la seguente query:

SELECT *
FROM v_customer

Di conseguenza, otteniamo un piano davvero impressionante:

----------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost |
----------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   599 | 47920 |   14 |
|*  1 |  HASH JOIN           |          |   599 | 47920 |   14 |
|   2 |   TABLE ACCESS FULL  | COUNTRY  |   109 |  1526 |    2 |
|*  3 |   HASH JOIN          |          |   599 | 39534 |   11 |
|   4 |    TABLE ACCESS FULL | CITY     |   600 | 10800 |    3 |
|*  5 |    HASH JOIN         |          |   599 | 28752 |    8 |
|   6 |     TABLE ACCESS FULL| CUSTOMER |   599 | 11381 |    4 |
|   7 |     TABLE ACCESS FULL| ADDRESS  |   603 | 17487 |    3 |
----------------------------------------------------------------

Beh, certo. Il database sta eseguendo tutte queste unioni e scansioni complete delle tabelle perché è ciò che gli abbiamo detto di fare: recuperare tutti questi dati. Ora, ancora una volta, immagina che tutto ciò di cui avevamo veramente bisogno fosse questo: Cosa, sul serio, vero? Ora stai iniziando a capire di cosa sto parlando. Ma immagina di aver imparato qualcosa dagli errori passati ed esegui questa query più ottimale:

SELECT first_name, last_name
FROM v_customer

Ora controlliamo cosa è successo!

------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   599 | 16173 |     4 |
|   1 |  NESTED LOOPS      |             |   599 | 16173 |     4 |
|   2 |   TABLE ACCESS FULL| CUSTOMER    |   599 | 11381 |     4 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C007120 |     1 |     8 |     0 |
------------------------------------------------------------------

Cambiamenti drastici in meglio in termini di esecuzione. I join sono stati eliminati perché l'ottimizzatore ora può vedere che sono inutili e se riesce a vederlo (e non hai reso questo lavoro obbligatorio selezionando *), allora semplicemente non può fare tutto quel lavoro. Perché è così in questo caso? La chiave esterna CUSTOMER.ADDRESS_ID alla chiave primaria ADDRESS.ADDRESS_ID garantisce esattamente un valore di quest'ultima, il che significa che l'operazione JOIN sarà un join "...-to-one" che non aumenta né diminuisce il numero di righe . E poiché non selezioniamo né richiediamo alcuna riga, non ha senso caricarle. La rimozione del JOIN probabilmente non influirà affatto sul risultato della query. I database lo fanno continuamente. È possibile eseguire la seguente query su quasi tutti i database:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)

In questo caso, potresti aspettarti che venga lanciata un'eccezione aritmetica, come quando si esegue la seguente query:

SELECT 1 / 0 FROM dual

Ma questo non accade. L'ottimizzatore (o anche il parser) può garantire che nessun elemento dell'elenco di selezione nel predicato EXISTS (SELECT ..) modificherà il risultato della query, quindi non è necessario eseguirlo. Come questo!

Nel frattempo...

Uno dei problemi più fastidiosi con gli ORM è che è così facile scrivere query SELECT *. Infatti, ad esempio, in HQL/JPQL vengono generalmente utilizzati di default. Possiamo omettere del tutto la clausola SELECT, perché recupereremo l'intera entità, giusto? Per esempio:

FROM v_customer

Ad esempio, Vlad Mihalcea, un esperto e sostenitore dello sviluppo con Hibernate , consiglia di utilizzare query [qualificate] quasi sempre quando sei sicuro di non voler salvare alcuna modifica dopo il checkout. Gli ORM facilitano notevolmente la soluzione del problema della persistenza degli oggetti grafici. Nota: persistenza. Le attività di modifica effettiva dei grafici degli oggetti e di salvataggio delle modifiche sono inestricabilmente legate. Ma se non hai intenzione di farlo, allora perché preoccuparti di estrarne l'essenza? Perché non scrivere una richiesta [perfezionata]? Cerchiamo di essere chiari: dal punto di vista delle prestazioni, scrivere una query su misura per il tuo caso d'uso specifico è ovviamente migliore di qualsiasi altra opzione. Potrebbe non interessarti poiché il tuo set di dati è piccolo e non ha importanza. Grande. Ma quando alla fine avrai bisogno di scalabilità, riprogettare le tue applicazioni per utilizzare query invece dell'attraversamento imperativo del grafico delle entità sarà piuttosto impegnativo. E avrai qualcosa da fare senza di esso.

Contare le righe per scoprire se è presente qualcosa

Uno dei peggiori sprechi di risorse è l'esecuzione di query COUNT(*) solo per vedere se c'è qualcosa nel database. Ad esempio, dobbiamo scoprire se un determinato utente ha degli ordini. Ed eseguiamo la richiesta:

SELECT count(*)
FROM orders
WHERE user_id = :user_id

Elementare. Se COUNT = 0, non ci sono ordini. Altrimenti sì. Le prestazioni non saranno poi così male poiché probabilmente abbiamo un indice nella colonna ORDERS.USER_ID. Ma cosa pensi che saranno le prestazioni della query di cui sopra rispetto alla seguente opzione:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
)

Non ci vuole uno scienziato per capire che un predicato di esistenza vera smetterà di cercare stringhe aggiuntive non appena trova la prima . Quindi, se il risultato è “nessun ordine”, la velocità sarà comparabile. Se invece il risultato è "sì, ci sono ordini", nel caso in cui non sia necessario contare la quantità esatta, la risposta verrà ricevuta molto più velocemente. Dopotutto, non siamo interessati al numero esatto. Tuttavia, abbiamo detto al database di calcolarlo ( lavoro non necessario ) e il database non sa che stiamo ignorando tutti i risultati maggiori di 1 ( lavoro richiesto ). Naturalmente, sarebbe molto peggio se chiamassimo list.size() su una raccolta supportata da JPA per ottenere gli stessi risultati. Ne ho già parlato in precedenza sul mio blog e ho condotto test comparativi di entrambe le opzioni su...

Conclusione

Questo articolo afferma l'ovvio. Non forzare il database a svolgere attività non necessarie ma necessarie . Non è necessario perché, dati i requisiti, sai che non è necessario svolgere un lavoro specifico. Tuttavia, dici al database di farlo. È necessario perché il database non può garantire che questo lavoro non sia necessario . Queste informazioni sono disponibili solo per il client e non sono disponibili per il server. Quindi il database deve eseguirlo. L'articolo si concentra su SELECT *, principalmente perché è un oggetto molto comodo da guardare. Ma questo vale non solo per i database. Questo vale per tutti gli algoritmi distribuiti in cui il client dice al server di svolgere un lavoro non necessario ma richiesto . Quante attività N+1 sono presenti nella tua applicazione AngularJS media in cui l'interfaccia utente esegue il loop del risultato del servizio A, chiamando il servizio B più volte, anziché raggruppare tutte le chiamate a B in un'unica chiamata? Questo è un evento molto comune. La soluzione è sempre la stessa. Più informazioni fornisci all'entità che esegue i tuoi comandi, più velocemente (in teoria) eseguirà tali comandi. Scrivi query ottimali. Sempre. Il tuo intero sistema ti ringrazierà per questo. Articolo originale