Desideri rendere le tue interrogazioni al database più rapide? Scopri come realizzare un indice di database utilizzando SQL e come ottimizzare l’efficienza delle tue query, incrementando la velocità di recupero dei dati.
Quando effettui il recupero di dati da una tabella di un database, spesso dovrai filtrare basandoti su determinate colonne.
Immagina di dover scrivere una query SQL per recuperare dati secondo specifiche condizioni. Di base, l’esecuzione della query avvia una scansione completa della tabella, fino a quando non vengono trovati tutti i record che soddisfano la condizione, e solo allora vengono restituiti i risultati.
Questo processo può risultare incredibilmente inefficiente quando devi interrogare tabelle di database molto grandi, contenenti svariati milioni di righe. Per velocizzare queste query, puoi creare un indice di database.
Cos’è un indice di database?
Se volessi trovare una parola precisa in un libro, ti metteresti a sfogliare l’intero volume pagina per pagina, alla ricerca del termine desiderato? Ovviamente no.
Cercheresti invece nell’indice le pagine in cui compare quel termine e andresti direttamente a quelle pagine. Un indice in un database funziona in maniera molto simile a quello di un libro.
Un indice di database è un insieme di puntatori o riferimenti ai dati effettivi, disposti in maniera tale da accelerare il recupero delle informazioni. A livello interno, un indice di database può essere realizzato usando strutture dati come alberi B+ e tabelle hash. Pertanto, un indice di database aumenta la velocità e l’efficienza delle operazioni di recupero dati.
Creazione di un indice di database in SQL
Ora che abbiamo compreso cos’è un indice di database e come può accelerare il recupero dati, impariamo come crearne uno in SQL.
Quando si eseguono operazioni di filtraggio, definendo le condizioni di recupero mediante una clausola WHERE, può verificarsi la necessità di interrogare una determinata colonna più spesso di altre.
CREATE INDEX index_name ON table (column)
Dove:
index_nameè il nome che si vuole dare all’indice da crearetablesi riferisce alla tabella nel database relazionalecolumnsi riferisce al nome della colonna all’interno della tabella di database su cui si vuole creare l’indice.
È anche possibile creare indici su più colonne, definiti indici a più colonne, in base alle necessità. Ecco la sintassi per farlo:
CREATE INDEX index_name ON table (column_1, column_2,...,column_k)
Ora passiamo a un esempio pratico.
Comprensione dei benefici di performance dell’indice di database
Per capire i vantaggi della creazione di un indice, è necessario creare una tabella di database con un numero elevato di record. Gli esempi di codice sono per SQLite, ma si possono utilizzare anche altri RDBMS a scelta, come PostgreSQL e MySQL.
Popolamento di una tabella di database con i record
Si può anche usare il modulo random di Python per creare e inserire record nel database. Tuttavia, qui useremo Faker per popolare la tabella del database con un milione di righe.
Il seguente script Python:
- Crea e si connette al database customer_db.
- Crea una tabella customers con i campi: first_name, last_name, city e num_orders.
- Genera dati fittizi e li inserisce (un milione di record) nella tabella customers.
Il codice è disponibile anche su GitHub.
# main.py
# imports
import sqlite3
from faker import Faker
import random
# connect to the db
db_conn = sqlite3.connect('customer_db.db')
db_cursor = db_conn.cursor()
# create table
db_cursor.execute('''CREATE TABLE customers (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
city TEXT,
num_orders INTEGER)''')
# create a Faker object
fake = Faker()
Faker.seed(27)
# create and insert 1 million records
num_records = 1_000_000
for _ in range(num_records):
first_name = fake.first_name()
last_name = fake.last_name()
city = fake.city()
num_orders = random.randint(0,100)
db_cursor.execute('INSERT INTO customers (first_name, last_name, city, num_orders) VALUES (?,?,?,?)', (first_name, last_name, city, num_orders))
# commit the transaction and close the cursor and connection
db_conn.commit()
db_cursor.close()
db_conn.close()
Ora possiamo iniziare a eseguire le interrogazioni.
Creazione di un indice sulla colonna della città
Supponiamo di voler ottenere informazioni sui clienti filtrando in base alla colonna della città. La query SELECT sarà simile a questa:
SELECT column(s) FROM customers WHERE condition;
Creiamo quindi l’indice city_idx sulla colonna city della tabella customers:
CREATE INDEX city_idx ON customers (city);
⚠ La creazione di un indice richiede una certa quantità di tempo e si tratta di un’operazione da eseguire una sola volta. Tuttavia, i benefici in termini di prestazioni quando è necessario eseguire un grande numero di query, filtrando sulla colonna city, saranno notevoli.
Eliminazione di un indice del database
Per eliminare un indice, è possibile utilizzare l’istruzione DROP INDEX in questo modo:
DROP INDEX index_name;
Confronto dei tempi di query con e senza indice
Se si desidera eseguire query all’interno di uno script Python, è possibile utilizzare il timer incorporato per ottenere i tempi di esecuzione delle query.
In alternativa, è possibile eseguire le query usando il client della riga di comando sqlite3. Per lavorare con customer_db.db utilizzando il client della riga di comando, eseguire il seguente comando nel terminale:
$ sqlite3 customer_db.db;
Per ottenere i tempi di esecuzione approssimativi, è possibile utilizzare la funzionalità .timer inclusa in sqlite3 in questo modo:
sqlite3 > .timer on
> <query here>
Dato che abbiamo creato un indice sulla colonna city, le query che filtrano in base alla colonna city nella clausola WHERE saranno molto più rapide.
Per prima cosa, esegui le query. Poi crea l’indice e riesegui le query. Annota i tempi di esecuzione in entrambi i casi. Ecco alcuni esempi:
| Query | Tempo senza indice | Tempo con indice |
SELECT * FROM customers WHERE city LIKE 'New%' LIMIT 10; |
0.100 s | 0.001 s |
SELECT * FROM customers WHERE city='New Wesley'; |
0.148 s | 0.001 s |
SELECT * FROM customers WHERE city IN ('New Wesley', 'New Steven', 'New Carmenmouth'); |
0.247 s | 0.003 s |
Si nota che i tempi di recupero con l’indice sono nettamente più rapidi rispetto a quelli senza indice sulla colonna city.
Best practice per creare e utilizzare indici di database
È sempre consigliabile verificare se i miglioramenti delle prestazioni superino il carico aggiuntivo dovuto alla creazione di un indice di database. Ecco alcune best practice da tenere a mente:
- Scegliere le colonne giuste per creare un indice. Evitare di creare troppi indici a causa del notevole sovraccarico.
- Ogni volta che una colonna indicizzata viene aggiornata, anche l’indice corrispondente dovrebbe essere aggiornato. Pertanto, la creazione di un indice di database (sebbene acceleri il recupero) rallenta in maniera significativa le operazioni di inserimento e aggiornamento. Di conseguenza, è necessario creare indici su colonne che vengono interrogate frequentemente ma aggiornate raramente.
Quando non è necessario creare un indice?
A questo punto dovresti avere un’idea di quando e come creare un indice. Vediamo anche quando un indice di database potrebbe non essere necessario:
- Quando la tabella del database è piccola e non contiene un gran numero di righe, la scansione dell’intera tabella per il recupero dei dati non è particolarmente dispendiosa.
- Non creare indici su colonne usate di rado per il recupero. Quando si creano indici su colonne non interrogate frequentemente, il costo della creazione e della manutenzione di un indice supera i benefici in termini di prestazioni.
Riassumendo
Rivediamo i punti chiave:
- Quando si interroga un database per recuperare dati, potrebbe essere necessario filtrare in base a determinate colonne più spesso di altre. Un indice di database su queste colonne frequentemente interrogate può migliorare le prestazioni.
- Per creare un indice su una singola colonna, utilizzare la sintassi:
CREATE INDEX nome_indice ON tabella (colonna). Per creare un indice su più colonne, usare:CREATE INDEX index_name ON table (column_1, column_2,…,column_k) - Ogni volta che una colonna indicizzata viene modificata, anche l’indice corrispondente deve essere aggiornato. Scegliere perciò le colonne giuste, interrogate frequentemente e aggiornate raramente, per la creazione di un indice.
- Se la tabella del database è relativamente piccola, il costo della creazione, della gestione e dell’aggiornamento di un indice sarà superiore ai miglioramenti delle prestazioni.
Nella maggior parte dei moderni sistemi di gestione di database, esiste un ottimizzatore di query che verifica se un indice su una specifica colonna velocizzerà l’esecuzione della query. In seguito, approfondiremo le best practice per la progettazione di database.