Linee guida SQL

L

Introduzione

Le operazioni di persistenza dati su database spesso tendono ad essere il collo di bottiglia principale per la maggior parte delle applicazioni web. Non solo i DBA (amministratori di database) devono preoccuparsi di questi problemi prestazionali: noi come programmatori dobbiamo fare la nostra parte strutturando le tabelle in modo corretto, scrivendo query ottimizzate e un codice migliore. In questo articolo elencherò alcune tecniche di ottimizzazione valide per i principali database sul mercato: oracle, mysql, postgree e mss.

non usare SELECT *

Nel linguaggio SQL l’asterisco ( * ) comanda al database di ritornare il valore di tutte le colonne dell’insieme di record. Questo comporta molti rischi:

  • Sicurezza: se un hacker riesce a trovare una falla con sql injection è in grado di conoscere il valore di tutte le colonne; ad esempio la password o altri dati sensibili del tuo cliente.
  • Performance: se hai un milione di record di ritorno dalla tua query questo puo avere grossi impatti sulla performance.
  • Coerenza del codice: se ritorni esattamente le colonne di cui hai bisogno leggendo il codice del Data Access Layer del tuo software sai esattamente che info utilizza, ad esempio il tuo frontend.

Morale della favola: specifica sempre la lista di campi di cui hai realmente bisogno nella select!

Usa la clausola standard join

Con la clausola JOIN dello standard ANS la clausola WHERE viene utilizzata solo per i filtri. In passato la clausola JOIN veniva utilizzata sia per condizioni di join che per filtri. Questo non ha impatti sulle performance ma peggiora la leggibilità della query.
Riportiamo un esempio:

-- old style join

SELECT a.Au_id,

       t.Title

FROM   TITLES t,

       AUTHORS a,

       TITLEAUTHOR ta

WHERE  a.Au_id = ta.Au_id

       AND ta.Title_id = t.Title_id

       AND t.Title LIKE ‘%Computer%’

 

--nuova sintassi con JOIN

SELECT a.Au_id,

       t.Title

FROM   AUTHORS a

       INNER JOIN TITLEAUTHOR ta

         ON a.Au_id = ta.Au_id

       INNER JOIN TITLES t

         ON ta.Title_id = t.Title_id

WHERE  t.Title LIKE ‘%Computer%’

 

specifica il nome della colonna nell’order by

Usa sempre il nome delle colonne nell’order by ed evita riferimenti posizionali:

-- DA EVITARE
SELECT OrderID, OrderDate

FROM Orders

ORDER BY 2
 -- soluzione corretta

SELECT OrderID, OrderDate

FROM Orders

ORDER BY OrderDate

INCLUDI LA LISTA DELLE COLONNE NEL TUO INSERT STATEMENT

Specifica sempre le colonne che stai riferendo nel tuo insert statement. Questo evita problemi quando la struttura cambia (ad esempio viene aggiunta o rimossa una colonna).
Considera la seguente tabella:
CREATE TABLE ORDERS

  (

     ID INT PRIMARY KEY,

     DESCRIPTION VARCHAR(25)

  )

Ecco uno statement di insert valido ma che non rispetta la linea guida descritta da questo paragrafo:

INSERT INTO ORDERS

VALUES (1, ‘ORDINE NUMERO 1’)

Se viene aggiunta una colonna alla tabella Orders lo statement di insert precedente non risulta essere piu’ valido:

--ESEMPIO ERRORE ORACLE
SQL Error: ORA-00947: not enough values

Per evitare questo problema sarebbe più opportuno scrivere il seguente statement di insert:

INSERT INTO ORDERS ( id, description)

VALUES (1, ‘ORDINE NUMERO 1’)

USA SEMPRE ALIAS DI TABELLE IN PRESENZA DI STATEMENT CHE RIFERISCONO DIVERSE TABELLE

Se più tabelle sono in gioco nel tuo statement sarebbe opportuno sempre specificare l’alias della tabella davanti al nome del campo.

performance

Il tema performance è molto complesso e si potrebbero scrivere centinaia di pagine su tecniche di tuning e best practices. In questo articolo verranno riportati solo alcuni accorgimenti, andremo in dettaglio sul tema performance in successivi articoli:

  • Controlla sempre il piano di accesso della tua query per controllare che non si creino dei FULL TABLE ACCESS che degradano drasticamente le performance del tuo sistema.
  • Crea indici sui campi utilizzati dai tuoi filtri.
  • Evita l’utilizzo di funzioni sui campi utilizzati nei filtri poiché invalidano gli indici.

 

ottimizza le tue query per utilizzare la cache

I database sql più famosi hanno a disposizione una cache: quando una query è eseguita più volte nel brevissimo range di tempo, il risultato viene recuperato dalla cache anziché accedere al disco. Il database riesce ad utilizzare la cache per la medesima query quando la query non fa uso di funzioni non deterministiche come now() (mysql) o sysdate (oracle).

// query che non utilizza cache
$r = mysql_query("SELECT username FROM user WHERE creation_date>= CURDATE()");
 
// query che utilizza la cache
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE creation_date>= '$today'");

PREDILIGI ID PRIMARY KEY INT

Quando è possibile prediligi chiave primarie composte da un solo campo intero unsigned (AUTO_INCREMENT in msql e generato da sequence in oracle), perché l’engine del DB lavora meglio sotto queste condizioni.

conclusioni

Ho elencato solo una parte delle best practices da seguire quando si lavora con database relazionali. L’articolo verrà aggiornato constantemente per includere altre importanti consigli da seguire quando si lavora con SQL.

A proposito di me

Dario Frongillo

Software architect con un forte background in Java, Architettura REST, framework Spring , Database Design,progettazione e sviluppo di SPA e RIA Web application con framework Javascript. Attualmente mi occupo di sviluppo soluzioni software in ambito Banking e Finance: in particolare progettazione e sviluppo di applicativi web based per la realizzazione di sistemi di trading, interfacce con i mercati finanziari e di servizi real time.

Gli articoli più letti

Articoli recenti

Commenti recenti