[Explained] Come creare un indice di database in SQL ⋆ winadmin.it

Vuoi velocizzare le query del database? Scopri come creare un indice di database utilizzando SQL e ottimizzare le prestazioni delle query e velocizzare il recupero dei dati.

Quando recuperi dati da una tabella di database, dovrai filtrare più spesso in base a colonne specifiche.

Supponiamo di scrivere una query SQL per recuperare i dati in base a condizioni specifiche. Per impostazione predefinita, l’esecuzione della query esegue una scansione completa della tabella fino a quando non sono stati trovati tutti i record che soddisfano la condizione, quindi restituisce i risultati.

Questo può essere estremamente inefficiente quando devi interrogare una tabella di database di grandi dimensioni con diversi milioni di righe. È possibile velocizzare tali query creando un indice del database.

Sommario:

Che cos’è un indice di database?

Quando vuoi trovare un termine specifico in un libro, eseguirai una scansione dell’intero libro, una pagina dopo l’altra, alla ricerca del termine specifico? Beh, non lo fai.

Cercherai invece l’indice per scoprire quali pagine fanno riferimento al termine e salterai direttamente a quelle pagine. Un indice in un database funziona in modo molto simile agli indici in un libro.

Un indice di database è un insieme di puntatori o riferimenti ai dati effettivi, ma ordinati in modo da velocizzare il recupero dei dati. Internamente, un indice di database può essere implementato utilizzando strutture di dati come alberi B+ e tabelle hash. Pertanto, un indice del database migliora la velocità e l’efficienza delle operazioni di recupero dei dati.

Creazione di un indice di database in SQL

Ora che sappiamo cos’è un indice di database e come può velocizzare il recupero dei dati, impariamo come creare un indice di database in SQL.

Quando si eseguono operazioni di filtraggio, specificando la condizione di recupero mediante una clausola WHERE, è possibile che si desideri interrogare una determinata colonna più frequentemente rispetto ad altre.

CREATE INDEX index_name ON table (column)

Qui,

index_name è il nome dell’indice da creare
table si riferisce alla tabella nel database relazionale
column si riferisce al nome della colonna nella tabella del database su cui dobbiamo creare l’indice.

È inoltre possibile creare indici su più colonne, un indice a più colonne, a seconda dei requisiti. Ecco la sintassi per farlo:

CREATE INDEX index_name ON table (column_1, column_2,...,column_k)

Passiamo ora a un esempio pratico.

Comprensione dei guadagni in termini di prestazioni dell’indice del database

Per comprendere il vantaggio 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 puoi anche usare altri RDBMS di tua scelta, come PostgreSQL e MySQL.

Popolamento di una tabella di database con i record

Puoi anche utilizzare il modulo casuale integrato di Python per creare e inserire record nel database. Tuttavia, useremo Falsario 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 clienti con i campi: first_name, last_name, city e num_orders.
Genera dati sintetici e inserisce i dati (un milione di record) nella tabella dei clienti.

Trovi anche il codice 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 interrogare.

Creazione di un indice sulla colonna della città

Supponiamo di voler ottenere le informazioni sui clienti filtrando in base alla colonna della città. La tua query SELECT sarà simile a questa:

SELECT column(s) FROM customers
WHERE condition;

Quindi creiamo il city_idx nella colonna della città nella tabella dei clienti:

CREATE INDEX city_idx ON customers (city);

⚠ La creazione di un indice richiede una quantità di tempo non trascurabile ed è un’operazione una tantum. Ma i vantaggi in termini di prestazioni quando è necessario eseguire un numero elevato di query, filtrando sulla colonna della città, saranno significativi.

Eliminazione di un indice del database

Per eliminare un indice, puoi utilizzare l’istruzione DROP INDEX in questo modo:

DROP INDEX index_name;

Confronto dei tempi delle query con e senza indice

Se desideri eseguire query all’interno di uno script Python, puoi utilizzare il timer predefinito per ottenere i tempi di esecuzione delle query.

In alternativa, puoi eseguire le query utilizzando il client della riga di comando sqlite3. Per lavorare con customer_db.db utilizzando il client della riga di comando, eseguire il comando seguente nel terminale:

$ sqlite3 customer_db.db;

Per ottenere i tempi di esecuzione approssimativi, puoi utilizzare la funzionalità .timer incorporata in sqlite3 in questo modo:

sqlite3 > .timer on
        > <query here>

Poiché abbiamo creato un indice sulla colonna della città, le query che implicano il filtraggio in base alla colonna della città nella clausola WHERE saranno molto più veloci.

Innanzitutto, esegui le query. Quindi, crea l’indice ed esegui nuovamente le query. Annotare i tempi di esecuzione in entrambi i casi. Ecco alcuni esempi:

QueryTime senza IndexTime con IndexSELECT * FROM clienti
WHERE città MI PIACE ‘Nuovo%’
LIMITE 10;0.100 s0.001 sSELEZIONA * DAI clienti
WHERE città=’New Wesley’;0.148 s0.001 sSELECT * FROM clienti
WHERE città IN (‘New Wesley’, ‘New Steven’, ‘New Carmenmouth’);0,247 s0,003 s

Vediamo che i tempi di recupero con l’indice sono diversi ordini più veloci di quelli senza indice sulla colonna della città.

Best practice per creare e utilizzare indici di database

Dovresti sempre controllare se i miglioramenti delle prestazioni sono maggiori del sovraccarico della creazione di un indice del database. Ecco alcune best practice da tenere a mente:

Scegli le colonne giuste per creare un indice. Evitare di creare troppi indici a causa del notevole sovraccarico.
Ogni volta che viene aggiornata una colonna indicizzata, dovrebbe essere aggiornato anche l’indice corrispondente. Pertanto, la creazione di un indice del database (sebbene acceleri il recupero) rallenta notevolmente gli inserimenti e le operazioni di aggiornamento. Pertanto, è necessario creare indici su colonne che vengono interrogate frequentemente ma aggiornate raramente.

Quando non dovresti creare un indice?

A questo punto dovresti avere un’idea di quando e come creare un file index. Ma diciamo anche quando l’indice del database potrebbe non essere necessario:

Quando la tabella del database è piccola e non contiene un numero elevato di righe, la scansione dell’intera tabella per recuperare i dati non è così costosa.
Non creare indici su colonne utilizzate raramente per il recupero. Quando si creano indici su colonne che non vengono interrogate di frequente, il costo di creazione e gestione di un indice supera i guadagni in termini di prestazioni.

Riassumendo

Rivediamo ciò che abbiamo imparato:

Quando si esegue una query su un database per recuperare i dati, potrebbe essere necessario filtrare in base a determinate colonne con maggiore frequenza. Un indice di database su tali 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). Se vuoi creare un indice a più colonne, usa: CREATE INDEX index_name ON table (column_1, column_2,…,column_k)
Ogni volta che una colonna indicizzata viene modificata, anche l’indice corrispondente dovrebbe essere aggiornato. Pertanto, scegli le colonne giuste, interrogate frequentemente e aggiornate molto meno frequentemente, per creare un indice.
Se la tabella del database è relativamente più piccola, il costo di creazione, gestione e aggiornamento di un indice sarà maggiore dei miglioramenti delle prestazioni.

Nella maggior parte dei moderni sistemi di gestione dei database, esiste un ottimizzatore di query che verifica se un indice su una colonna specifica renderà più veloce l’esecuzione della query. Successivamente, impariamo le migliori pratiche per la progettazione di database.