Guida dell'analista di dati all'indicizzazione SQL: Risolvere le query lente

Il tempo di risposta delle vostre query SQL lascia a desiderare? O forse non sapete se le vostre query potrebbero essere più veloci. In questo articolo vi spiegheremo come l'indicizzazione SQL può aiutarvi.

Immergersi in SQL è come sbloccare un superpotere. Si tratta di padroneggiare l'arte di interrogare il database in modo efficace, per recuperare le informazioni necessarie in modo rapido e semplice. Ma cosa succede quando si pone una domanda abbastanza complessa da rallentare i tempi di risposta da secondi a minuti, o anche di più? In questo caso si parla di indicizzazione SQL.

In questo articolo condividerò tutto quello che c'è da sapere sull'indicizzazione SQL e perché dovrebbe essere il prossimo passo per ottimizzare le query SQL. Immergiamoci!

SQL e le query di database

SQL è un linguaggio per l'interrogazione e la manutenzione dei database. Consente di memorizzare e visualizzare i dati e di aggiornarli. La gestione dei dati è fondamentale per l'analisi dei dati e la business intelligence. Dobbiamo gestire i dati sottostanti per poterli analizzare e trarne conclusioni: Quali prodotti si vendono bene? Quali clienti acquistano molto? Quali clienti non pagano in tempo?

Una volta che abbiamo i dati, possiamo rispondere a questo tipo di domande. Ma prima dobbiamo "fare domande" o "fare richieste" sui nostri dati. Queste richieste si chiamano query, ad esempio: "Per le vendite del mese scorso, conta il numero di volte in cui ogni prodotto è stato venduto e forniscimi la somma delle vendite".

Naturalmente, man mano che poniamo domande più complesse, le nostre query diventano più complesse. Potrebbe essere necessario combinare (o unire) le informazioni provenienti da più fonti per poter estrarre le informazioni di cui abbiamo bisogno.

Ad esempio, è utile sapere che il prodotto ID 2123876123 ha venduto di più nel trimestre precedente, ma è ancora più utile sapere che quel prodotto è una felpa blu. Con l'aumentare della complessità delle nostre query, la risposta del nostro sistema di database diventa sempre più impegnativa e lunga. Dobbiamo velocizzare le nostre query SQL, ma come?

Qui inizia il divertimento. C'è un'arte sottile nel capire quando una query risponde lentamente e come migliorarne le prestazioni. Potremmo provare a modificare la nostra query e renderla più efficiente, ma la messa a punto delle prestazioni SQL ci porterà solo fino a un certo punto. A un certo punto è necessario eseguire l'ottimizzazione della query. Questo rende più facile per il sistema di database recuperare i risultati che stiamo cercando, migliorando le prestazioni della query.

È qui che entrano in gioco gli indici.

Che cos'è un indice SQL?

Come l'indice di un libro, gli indici SQL preparano il sistema di database a recuperare i dati in modo più efficiente. La creazione degli indici è semplice; abbiamo un corso completo sugli indici che vi insegnerà tutto ciò che dovete sapere. Tra questi, il modo in cui vengono creati all'interno del database, la sintassi SQL per la creazione di un indice e quando crearlo. Ci sono oltre 50 esercizi e una stima di 10 ore di apprendimento.

Se desiderate un'introduzione al lato teorico degli indici, abbiamo anche altri articoli sulle basi dell'indicizzazione SQL e su Cos'è un indice di database?

Per i nostri scopi, non ci preoccuperemo dei dettagli di come il database costruisce un indice o della sua struttura B-tree sottostante. Ci concentreremo invece su come il database utilizza un indice. È sufficiente dire che un indice B-tree (albero bilanciato) ci permette di accedere a qualsiasi riga del database nello stesso tempo.

L'indicizzazione del database accelera il recupero dei dati. Ripensiamo alla nostra analogia: è più facile trovare la pagina che si riferisce ad Abraham Lincoln cercando "Lincoln, Abraham" nell'indice del libro. (Confrontate questa operazione con quella di cercare in ogni singola pagina un riferimento a Mr. Lincoln e avrete capito l'idea). Usando un indice, trovare il riferimento a una persona in particolare nel libro richiederebbe la stessa quantità di tempo.

D'altra parte, immaginate che il libro non abbia un indice e che dobbiate scorrere ogni pagina per trovare un nome. L'individuazione di un riferimento specifico richiederà una quantità di tempo variabile e sconosciuta: le persone che compaiono nelle prime pagine saranno trovate più rapidamente di quelle che compaiono alla fine (a meno che non si effettui una scansione dal retro all'inizio).

L'indicizzazione SQL funziona allo stesso modo. Un indice viene applicato a una colonna che rende più facile per il database restituire le informazioni per una ricerca.

Dovremmo creare indici per ogni colonna di una tabella? Questo non velocizzerebbe il database? No. L'indicizzazione di troppe colonne influisce negativamente sulle prestazioni del database, rendendo molto lenta l'aggiunta e l'aggiornamento delle righe. La pratica migliore è quella di indicizzare solo le colonne utilizzate di frequente per ordinare o classificare i dati.

Esempio di indicizzazione del database #1

Supponiamo di dover memorizzare informazioni sulle persone: nome, cognome, via, numero civico, codice postale/ZIP, città, paese, numero di telefono e data di nascita.

Ora, supponiamo di avere milioni di record memorizzati in questo database. Si potrebbe cercare (cioè interrogare) il database in base al cognome? Sì, probabilmente. Si potrebbe interrogare il database usando solo il nome? No, probabilmente no; è più probabile che si interroghi in base al nome e al cognome. Ma forse si vuole vedere quando un particolare nome è stato il più popolare nel set di dati. In questo caso, si dovrebbe chiedere "in quale anno è nato il maggior numero di persone con questo nome?".

Ognuno di questi casi richiede un approccio diverso all'indicizzazione. Se si cerca solo il cognome, si crea un indice sulla colonna last_name. Se la ricerca viene effettuata per nome e last nome, si indicizzeranno sia la colonna first_name che la colonna last_name. Nel terzo caso, creeremo un indice solo sulla colonna first_name.

In breve, esistono diverse tecniche di indicizzazione SQL. Applicando queste diverse tecniche, è possibile ottenere la regolazione delle prestazioni di SQL. La creazione degli indici dipende dall'uso che si intende fare dei dati, ovvero da come si intende interrogare i dati.

Esempio di indicizzazione del database #2

Immaginiamo di avere una tabella person con quattro colonne: ssn (numero di previdenza sociale, simile a un numero di identificazione nazionale), first_name, last_name e zip_code (codice postale). Con milioni di record, l'esecuzione di una query SQL per trovare una riga in base al cognome richiede quasi un minuto. quasi un minuto - anche per una tabella così semplice.

Riuscite a immaginare di aspettare un minuto perché l'applicazione recuperi le informazioni di cui avete bisogno ogni volta che la interrogate? In questo esempio, la scansione di 40 milioni di record ha richiesto 46 secondi. Non c'era un indice sulla colonna last_name, quindi il sistema di database ha dovuto leggere tutti i record della tabella per verificare quali corrispondessero a un determinato cognome. Abbiamo un disperato bisogno di velocizzare questa query SQL.

In questa situazione, creeremo un indice sulla colonna cognome. Questo velocizzerà la query SQL di tre ordini di grandezza (circa 3.000 volte più veloce), portandola a 15 millisecondi: un enorme guadagno in termini di prestazioni della query SQL.

Per indicizzare un database, è necessario definire:

1. Il nome dell'indice.
2. Quali colonne avranno l'indice.
3. Il nome della tabella che contiene queste colonne.

Non bisogna creare indici per ogni colonna o per ogni combinazione di colonne. Se lo facciamo, rischiamo di uccidere il database e di creare un'applicazione che non risponde quando gli utenti aggiungono o aggiornano i record.

Vediamo come fare.

Creare un indice SQL

La sintassi per la creazione di un indice è semplice. Per creare un indice semplice su una colonna della tabella, si utilizza la seguente istruzione. In questo caso, si indicizzerà la colonna zip_code della tabella person tabella:

CREATE INDEX index_zip
ON person (zip_code);

Non è complicato. Tuttavia, ricordate che ogni indice deve essere aggiornato quando vengono aggiunte nuove righe e quando quelle esistenti vengono modificate o eliminate. Questi aggiornamenti richiedono tempo; se si fa un uso eccessivo degli indici, questi possono rendere il database e la relativa applicazione più lenti o addirittura troppo lenti per gli utenti.

È anche possibile modificare gli indici esistenti o eliminarli, ma ciò esula dallo scopo di questo articolo.

Esistono diversi tipi di indici:

• Gliindici univoci mantengono l'integrità dei dati definendo che nessuna riga della tabella può avere lo stesso valore per l'indice univoco.
• Gliindici primari sono un tipo specifico di indice univoco, ma possono essere solo uno per tabella. L'indice primario viene creato al momento della creazione della tabella. Come dice il nome, viene creato a partire dalla chiave primaria della tabella.
• Gliindici secondari sono indici aggiuntivi che vengono creati su richiesta (utilizzando CREATE INDEX) e possono essere eliminati. Gli indici secondari possono avere valori duplicati e non univoci (cioè possono avere più righe con lo stesso valore).
• Gliindici composti (indici a più colonne) sono indici che includono più colonne.

Gli indici sono importanti, ma l'uso eccessivo è dannoso quanto l'uso insufficiente o il mancato uso. Una strategia di indicizzazione ben implementata è fondamentale. È possibile evitare indici non necessari comprendendo come verranno mantenuti i dati nelle tabelle. Ma assicuratevi di includere indici importanti in base alle modalità di accesso e di utilizzo dei dati.

Migliori pratiche di indicizzazione SQL

Vediamo le migliori pratiche di indicizzazione.

1. Nonindicizzare tutte le tabelle. Le tabelle di piccole dimensioni non necessitano di indici, in quanto una scansione della tabella è più efficiente rispetto alla consultazione dell'indice e al successivo recupero dei dati dalla tabella.
2. Nonindicizzare tutte le colonne. Spero che questo sia ovvio dagli esempi precedenti. L'indicizzazione di ogni colonna comporta un sovraccarico di lavoro per mantenere gli indici aggiornati e rallenta le altre operazioni del database. Indicizzate le colonne su cui filtrate (cioè che usate spesso nelle clausole WHERE).
3. Nonindicizzare colonne di grandi dimensioni. Un campo di grandi dimensioni all'interno della tabella comporta un indice di grandi dimensioni.
4. Indicizzare le chiavi esterne. Questo migliora le prestazioni delle JOIN
5. Usare gli indici a più colonne solo quando è opportuno. Gli indici a più colonne sono ottimi. (Ricordate l'esempio dell'indicizzazione su nome e cognome, in modo da poter eseguire query efficienti su questa combinazione). Tuttavia, gli indici composti sono più impegnativi, in quanto è necessario considerare l'ordine delle colonne all'interno dell'indice. Dobbiamo creare un indice sul nome e sul cognome o sul cognome e sul nome? Si tratta di due indici diversi. Quale dei due funzionerà in modo più efficiente? La risposta dipende dalle query SQL. In genere, un indice su una sola colonna è sufficiente (e fa risparmiare tempo).
6. Usare gli indici per pre-ordinare i dati. L'ordinamento ripetuto dei dati può essere evitato quando viene aggiunto un indice con l'ordine di ordinamento (cioè crescente o decrescente).
7. Verificare che le cose funzionino come previsto. Utilizzare EXPLAIN PLAN per verificare che gli indici siano utili.

Ora che abbiamo una strategia per la creazione degli indici, pensiamo a come mantenerli e metterli a punto. È necessario monitorare gli indici: controllare le statistiche degli indici per comprenderne l'utilizzo e controllare il piano di query per analizzare come le query SQL utilizzano gli indici.

La manutenzione degli indici SQL comporta la riorganizzazione o la ricostruzione degli indici. La riorganizzazione di un indice è meno intensiva di una ricostruzione completa. Sia la riorganizzazione che la ricostruzione di un indice richiedono molte risorse e possono migliorare o meno le prestazioni. Quindi, analizzate i vostri indici prima di mantenerli. Non date per scontato che il mantenimento di un indice migliori le prestazioni delle query SQL.

Cosa c'è da fare con l'indicizzazione SQL?

Ora che abbiamo analizzato cosa sono gli indici e le migliori pratiche per crearli e mantenerli, il passo successivo è quello di approfondire gli indici. Il nostro corso Understanding Indexes spiega come funzionano gli indici e vi guida su come e quando creare un indice. Gli esercizi pratici vi aiuteranno a comprendere rapidamente ciò che vi serve per mantenere le vostre query veloci ed efficienti. Buon apprendimento!