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
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.
