Heading

In questo articolo, ci concentreremo sui consigli più semplici e migliori che gli sviluppatori possono implementare per i loro sforzi quotidiani di ottimizzazione delle prestazioni SQL. Questo articolo si concentrerà sul tuning delle prestazioni delle query MySQL, ma gli stessi concetti possono essere applicati a molti altri database relazionali.

Ora più che mai, gli ingegneri del software devono avere una vasta conoscenza del tuning delle prestazioni SQL.
Il cambiamento sta avvenendo sia nelle piccole startup che nelle grandi imprese. Al giorno d’oggi, gli sviluppatori sono quelli che scrivono le query SQL e lo strato di accesso al database.

Non importa se state usando uno strato di astrazione del database (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django, o altri) o se state scrivendo query SQL native, alla fine sarete chiamati a mettere a punto le query che state inviando al vostro database.

Quindi cosa si può fare per ottimizzare le query SQL?

Creare indici, ma farlo con saggezza

L’indicizzazione è probabilmente la parte più importante del processo di ottimizzazione delle query. Quindi, per prima cosa, assicuratevi di avere familiarità con i diversi aspetti che dovreste considerare quando scegliete gli indici ottimali per il vostro database.

Quando pensate a quali indici creare, dovreste prestare molta attenzione alla clausola WHERE della query e alle table JOIN, poiché queste dichiarazioni includono le parti critiche indicizzabili della query.

Inoltre, i maggiori colli di bottiglia possono avere origine nelle parti GROUP BY e ORDER BY. Detto questo, un potenziale intoppo sarà che potreste non essere in grado di indicizzarle in alcuni casi, come abbiamo spiegato qui. Pertanto, potrebbe essere necessario ripensare il design della query prima di creare gli indici, per essere sicuri di scrivere grandi query, ma anche di scrivere query indicizzabili.

Una volta che avete capito l’indicizzazione per una query, non fermatevi lì. Allarga la tua visione e guarda ad altre query importanti nella tua applicazione. Assicuratevi di combinare gli indici quando possibile, e rimuovete gli indici che non sono utilizzati. Guardare l’intero ambito dell’applicazione sarà sempre meglio che guardare l’ambito di una singola query.

Si dovrebbe anche tenere a mente che la creazione di più indici di quelli necessari può anche ritorcersi contro di voi, in quanto possono rallentare le operazioni di scrittura (come le istruzioni INSERT / UPDATE). Quindi crea indici per ottimizzare le prestazioni delle tue query SQL, ma fallo con saggezza.

Non ostacolare gli indici

Siamo stati contattati spesso da clienti che ci chiedono “perché il database non usa il mio indice? Bene, questa è una grande domanda, con infinite risposte possibili. Ma, in questo articolo, cercheremo di coprire diversi scenari comuni che vediamo spesso, così speriamo che li troverete utili per il vostro caso d’uso.

Esempio #1 – Evitare di avvolgere le colonne indicizzate con funzioni

Considerate questa query, che conta il numero di hot dog acquistati negli USA nel 2018. Nel caso siate curiosi, 18.000.000.000.000 di hot dog sono stati venduti negli Stati Uniti nel 2018.

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE YEAR(purchase_time) = ‘2018’

Come potete vedere, stiamo usando la funzione YEAR per prendere la parte dell’anno dalla colonna purchase_time. Questa chiamata di funzione impedirà al database di poter utilizzare un indice per la ricerca nella colonna purchase_time, perché abbiamo indicizzato il valore di purchase_time, ma non il valore di ritorno di YEAR(purchase_time).

Per superare questa sfida e mettere a punto questa query SQL, è possibile indicizzare il risultato della funzione, utilizzando Generated Columns, che sono disponibili a partire da MySQL 5.7.5.

Un’altra soluzione può essere trovare un modo alternativo per scrivere la stessa query, senza utilizzare la chiamata di funzione. In questo esempio, possiamo trasformare questa condizione in una condizione di range a 2 vie, che restituirà gli stessi risultati:

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE purchased_at >= '2018-01-01' AND purchased_at < '2019-01-01'

Esempio #2 – evitare condizioni OR

Considerate questa query, che seleziona la quantità di post su Facebook pubblicati dopo la vigilia di Capodanno, o pubblicati da un utente chiamato Mark.

SELECT COUNT(*)FROM fb_postsWHERE username = ‘Mark’ OR post_time > ‘2018-01-01’

Avere un indice su entrambe le colonne username e post_time potrebbe sembrare utile, ma nella maggior parte dei casi, il database non lo userà, almeno non completamente. La ragione sarà la connessione tra le due condizioni – l’operatore OR, che fa sì che il database recuperi i risultati di ogni parte della condizione separatamente.

Un modo alternativo di guardare a questa query può essere quello di ‘dividere’ la condizione OR e ‘combinarla’ usando una clausola UNION. Questa alternativa vi permetterà di indicizzare ogni condizione separatamente, così il database userà gli indici per cercare i risultati e poi combinerà i risultati con la clausola UNION.

SELECT …FROM …WHERE username = ‘Mark’ UNIONSELECT …FROM …WHERE post_time > ‘2018-01-01’

Si prega di notare che se non vi dispiace avere record duplicati nel vostro set di risultati, potete anche usare UNION ALL (che funzionerà meglio di UNION DISTINCT).

Esempio #3 – Evitare l’ordinamento con un ordine misto

Considera questa query, che seleziona tutti i post da Facebook e li ordina per nome utente in ordine crescente, e poi per data del post in ordine decrescente.

SELECT username, post_typeFROM fb_postsORDER BY username ASC , post_type DESC

MySQL (e molti altri database relazionali), non può usare gli indici quando si ordina con un ordine misto (sia ASC che DESC nella stessa clausola ORDER BY). Questo è cambiato con il rilascio della funzionalità degli indici invertiti e MySQL 8.x.

Quindi cosa puoi fare se non hai ancora aggiornato all’ultima versione di MySQL? Per prima cosa, vi consigliamo di riconsiderare l’ordinamento misto. Ne hai davvero bisogno? Se no, evitatelo.

Allora avete deciso che ne avete bisogno, o il vostro product manager ha detto: “Non c’è modo di gestire senza di esso”? Un’altra opzione sarà quella di utilizzare le colonne generate (disponibili su MySQL 5.7.5+) per creare una colonna invertita e ordinare su quella colonna invece che sull’originale. Per esempio, supponiamo che stiate ordinando su una colonna numerica, potete creare una colonna generata con il valore numerico negativo che è correlato al numero originale e ordinare su quella nuova colonna nell’ordine opposto. In questo modo, tutte le colonne avranno lo stesso ordine nella clausola ORDER BY, ma l’ordinamento avverrà come definito originariamente dai requisiti del vostro prodotto.

L’ultima soluzione potenziale non sarà sempre un’opzione, quindi la vostra ultima risorsa sarà l’aggiornamento all’ultima versione di MySQL che supporta l’ordinamento di ordine misto usando gli indici.

Esempio #4 – Evitare condizioni con diversi tipi di colonna

Considera questa query, che seleziona il numero di frutti rossi in una foresta.

SELECT COUNT(*)FROM forestWHERE fruit_color = 5; /* 5 = red */

Assumendo che il tipo della colonna fruit_color sia VARCHAR, o qualsiasi cosa non numerica, indicizzare questa colonna non sarà molto utile, poiché il cast implicito richiesto impedirà al database di utilizzare l’indice per il processo di filtraggio.

Come si può quindi ottimizzare questa query SQL? Avete due opzioni per ottimizzare questa query. La prima sarebbe quella di confrontare la colonna con un valore costante che corrisponda al tipo di colonna, quindi se si tratta di una colonna VARCHAR, confrontatela con ‘5’ (con le virgolette singole) e non con 5 (che è un confronto numerico che risulterà in un cast implicito).

Un’opzione migliore sarà quella di adattare il tipo di colonna al tipo più adatto ai valori che la colonna contiene. In questo esempio, la colonna dovrebbe essere modificata al tipo INT. Si prega di notare che modificare il tipo di una colonna può essere un compito complicato, quindi leggete le sfide di questo compito prima di dirigervi verso di esso.

Evitare le ricerche LIKE con prefissi jolly

Considerate questa query, che cerca tutti i post di Facebook da un nome utente che include la stringa ‘Mar’, quindi stiamo cercando tutti i post scritti da utenti di nome Mark, Marcus, Almar, ecc.

SELECT *FROM fb_postsWHERE username LIKE '%Mar%'

Avere un carattere jolly ‘%’ all’inizio dello schema impedirà al database di usare un indice per la ricerca di questa colonna. Tali ricerche possono richiedere molto tempo.

In questo caso, ci sono due opzioni per migliorare le prestazioni di questa query. La prima è banale – considera se il prefisso jolly è abbastanza importante. Se puoi farne a meno, sbarazzatene.

Un’altra opzione sarà quella di usare gli indici full-text. Notate però che questi indici e la sintassi MATCH … AGAINST non sono esenti da sfide e hanno alcune differenze rispetto alle familiari espressioni LIKE in MySQL.

Conclusione

In questa prima parte della nostra serie sull’ottimizzazione delle query SQL, abbiamo parlato dell’importanza di una saggia indicizzazione, siamo passati attraverso diversi esempi di possibili ostacoli durante l’utilizzo di colonne indicizzate nelle query, e abbiamo anche dettagliato diversi altri suggerimenti e trucchi che possono essere utili per migliorare le prestazioni delle query. Ci vediamo nel prossimo post.