Come velocizzare le query SQL utilizzando gli indici [edizione Python]

Velocizzare le query SQL con indici [Python]

 

Supponiamo che stiate sfogliando le pagine di un libro. E volete trovare le informazioni che state cercando molto più velocemente. Come lo fareste? Beh, probabilmente guardereste l’indice dei termini e poi saltate alle pagine che fanno riferimento a un determinato termine. Gli indici in SQL funzionano in modo simile agli indici dei libri.

Nella maggior parte dei sistemi reali, eseguirai query su una tabella di database con un grande numero di righe (pensa a milioni di righe). Le query che richiedono una scansione completa della tabella per recuperare i risultati saranno piuttosto lente. Se sai che dovrai interrogare le informazioni in base ad alcune colonne spesso, puoi creare indici di database su tali colonne. Questo velocizzerà significativamente la query.

Quindi cosa impareremo oggi? Impareremo a connetterci e interrogare un database SQLite in Python, utilizzando il modulo sqlite3. Impareremo anche come aggiungere indici e vedere come migliora le prestazioni.

Per codificare insieme a questo tutorial, dovresti avere Python 3.7+ e SQLite installati nel tuo ambiente di lavoro.

Nota: Gli esempi e l’output di esempio in questo tutorial sono per Python 3.10 e SQLite3 (versione 3.37.2) su Ubuntu LTS 22.04.

 

Connessione a un database in Python

 

Utilizzeremo il modulo sqlite3 integrato. Prima di iniziare a eseguire le query, dobbiamo:

  • connetterci al database
  • creare un cursore del database per eseguire le query

Per connettersi al database, utilizzeremo la funzione

connect() dal modulo sqlite3. Una volta stabilita una connessione, possiamo chiamare cursor() sull’oggetto di connessione per creare un cursore del database come mostrato:

import sqlite3

# connettersi al db
db_conn = sqlite3.connect('people_db.db')
db_cursor = db_conn.cursor()

 

Qui cerchiamo di connetterci al database

people_db. Se il database non esiste, l’esecuzione del frammento di codice sopra creerà il database sqlite per noi.

 

Creazione di una tabella e inserimento dei record

 

Ora, creeremo una tabella nel database e la popoleremo con i record.

Creiamo una tabella chiamata people nel database people_db con i seguenti campi:

  • name
  • email
  • job
# main.py
...
# creazione tabella
db_cursor.execute('''CREATE TABLE people (
                  id INTEGER PRIMARY KEY,
                  name TEXT,
                  email TEXT,
                  job TEXT)''')


...

# confermare la transazione e chiudere il cursore e la connessione al db
db_conn.commit()
db_cursor.close()
db_conn.close()

 

Generazione di dati sintetici con Faker

 

Ora dobbiamo inserire i record nella tabella. Per fare ciò, utilizzeremo il Faker – un pacchetto Python per la generazione di dati sintetici – installabile tramite pip:

$ pip install faker

 

Dopo aver installato faker, puoi importare la classe Faker nello script Python:

# main.py
...
from faker import Faker
...

 

Il passo successivo è generare e inserire i record nella tabella delle persone. Solo per sapere come gli indici possono velocizzare le query, inseriamo un gran numero di record. Qui, inseriremo 100.000 record; impostare la variabile num_records su 100000.

Quindi, facciamo quanto segue:

  • Istanzia un oggetto Faker fake e imposta il seed in modo da ottenere la riproducibilità.
  • Ottieni una stringa di nome utilizzando nome e cognome – chiamando first_name() e last_name() sull’oggetto fake.
  • Genera un falso dominio chiamando domain_name().
  • Utilizza il nome e il cognome e il dominio per generare il campo email.
  • Ottieni un lavoro per ogni record individuale utilizzando job().

Generiamo e inseriamo record nella tabella people:

# crea e inserisci record
fake = Faker() # assicurati di importare: from faker import Faker
Faker.seed(42)

num_records = 100000

for _ in range(num_records):
    first = fake.first_name()
    last = fake.last_name()
    name = f"{first} {last}"
    domain = fake.domain_name()
    email = f"{first}.{last}@{domain}"
    job = fake.job()
    db_cursor.execute('INSERT INTO people (name, email, job) VALUES (?,?,?)', (name,email,job))

# esegui la transazione, chiudi il cursore e la connessione al database
db_conn.commit()
db_cursor.close()
db_conn.close()

 

Ora il file main.py ha il seguente codice:

# main.py
# importazioni
import sqlite3
from faker import Faker

# connetti al database
db_conn = sqlite3.connect('people_db.db')
db_cursor = db_conn.cursor()

# crea tabella
db_cursor.execute('''CREATE TABLE people (
                  id INTEGER PRIMARY KEY,
                  name TEXT,
                  email TEXT,
                  job TEXT)''')

# crea e inserisci record
fake = Faker()
Faker.seed(42)

num_records = 100000

for _ in range(num_records):
    first = fake.first_name()
    last = fake.last_name()
    name = f"{first} {last}"
    domain = fake.domain_name()
    email = f"{first}.{last}@{domain}"
    job = fake.job()
    db_cursor.execute('INSERT INTO people (name, email, job) VALUES (?,?,?)', (name,email,job))

# esegui la transazione, chiudi il cursore e la connessione al database
db_conn.commit()
db_cursor.close()
db_conn.close()

 

Esegui questo script – una volta – per popolare la tabella con un numero di record pari a num_records.

 

Interrogare il Database

 

Ora che abbiamo la tabella con 100.000 record, eseguiamo una query di esempio sulla tabella people.

Eseguiamo una query per:

  • ottenere i nomi e le email dei record con il titolo di lavoro “Product manager”, e
  • limitare i risultati della query a 10 record.

Useremo il timer predefinito dal modulo time per ottenere il tempo di esecuzione approssimativo per la query.

# sample_query.py

import sqlite3
import time

db_conn = sqlite3.connect("people_db.db")
db_cursor = db_conn.cursor()

t1 = time.perf_counter_ns()

db_cursor.execute("SELECT name, email FROM people WHERE job='Product manager' LIMIT 10;")

res = db_cursor.fetchall()
t2 = time.perf_counter_ns()

print(res)
print(f"Tempo di query senza indice: {(t2-t1)/1000} us")

 

Ecco l’output:

Output >>
[
    ("Tina Woods", "[email protected]"),
    ("Toni Jackson", "[email protected]"),
    ("Lisa Miller", "[email protected]"),
    ("Katherine Guerrero", "[email protected]"),
    ("Michelle Lane", "[email protected]"),
    ("Jane Johnson", "[email protected]"),
    ("Matthew Odom", "[email protected]"),
    ("Isaac Daniel", "[email protected]"),
    ("Jay Byrd", "[email protected]"),
    ("Thomas Kirby", "[email protected]"),
]

Tempo di query senza indice: 448.275 us

 

Puoi anche invocare il client della linea di comando di SQLite eseguendo sqlite3 nome_db da riga di comando:

$ sqlite3 people_db.db
Versione SQLite 3.37.2 2022-01-06 13:25:41
Digita ".help" per i suggerimenti sull'utilizzo.

 

Per ottenere l’elenco degli indici, puoi eseguire .index:

sqlite> .index

 

Poiché attualmente non ci sono indici, non verrà elencato alcun indice.

Puoi anche controllare il piano di query in questo modo:

sqlite> EXPLAIN QUERY PLAN SELECT name, email FROM people WHERE job='Product Manager' LIMIT 10;
QUERY PLAN
`--SCAN people

 

Qui il piano di query prevede la scansione di tutte le righe, il che è inefficiente.

 

Creazione di un indice su una colonna specifica

 

Per creare un indice di database su una colonna specifica, puoi utilizzare la sintassi:

CREATE INDEX nome-indice su tabella (colonna(e))

 

Supponiamo che abbiamo bisogno di cercare frequentemente i record di individui con un determinato titolo di lavoro. Sarebbe utile creare un indice people_job_index sulla colonna del lavoro:

# create_index.py

import time
import sqlite3

db_conn = sqlite3.connect('people_db.db')

db_cursor =db_conn.cursor()

t1 = time.perf_counter_ns()

db_cursor.execute("CREATE INDEX people_job_index ON people (job)")

t2 = time.perf_counter_ns()

db_conn.commit()

print(f"Tempo per creare l'indice: {(t2 - t1)/1000} us")


Output >>
Tempo per creare l'indice: 338298.6 us

 

Anche se la creazione dell’indice richiede tanto tempo, è un’operazione che viene eseguita una sola volta. Otterrai comunque un notevole aumento di velocità quando esegui più query.

Ora, se esegui .index nel client della linea di comando di SQLite, otterrai:

sqlite> .index
people_job_index

 

Interrogare il database con l’indice

 

Se ora guardi il piano di query, dovresti poter vedere che cerchiamo la tabella people utilizzando l’indice people_job_index sulla colonna job:

sqlite> EXPLAIN QUERY PLAN SELECT name, email FROM people WHERE job='Product manager' LIMIT 10;
QUERY PLAN
`--SEARCH people USING INDEX people_job_index (job=?)

 

Puoi eseguire nuovamente sample_query.py. Modifica solo l’istruzione print() e vedi quanto tempo impiega ora per eseguire la query:

# sample_query.py

import sqlite3
import time

db_conn = sqlite3.connect("people_db.db")
db_cursor = db_conn.cursor()

t1 = time.perf_counter_ns()

db_cursor.execute("SELECT name, email FROM people WHERE job='Product manager' LIMIT 10;")

res = db_cursor.fetchall()
t2 = time.perf_counter_ns()

print(res)
print(f"Tempo di query con indice: {(t2-t1)/1000} us")

 

Ecco l’output:

Output >>
[
    ("Tina Woods", "[email protected]"),
    ("Toni Jackson", "[email protected]"),
    ("Lisa Miller", "[email protected]"),
    ("Katherine Guerrero", "[email protected]"),
    ("Michelle Lane", "[email protected]"),
    ("Jane Johnson", "[email protected]"),
    ("Matthew Odom", "[email protected]"),
    ("Isaac Daniel", "[email protected]"),
    ("Jay Byrd", "[email protected]"),
    ("Thomas Kirby", "[email protected]"),
]

Tempo di query con indice: 167.179 us

 

Vediamo che la query ora impiega circa 167.179 microsecondi per essere eseguita.

 

Miglioramento delle prestazioni

 

Per la nostra query di esempio, la ricerca con l’indice è circa 2,68 volte più veloce. E otteniamo un miglioramento percentuale delle prestazioni del 62,71% nei tempi di esecuzione.

Puoi anche provare a eseguire altre query: query che coinvolgono il filtraggio sulla colonna del lavoro e vedere il miglioramento delle prestazioni.

Nota anche: poiché abbiamo creato un indice solo sulla colonna del lavoro, se esegui query che coinvolgono altre colonne, le query non verranno eseguite più velocemente rispetto a quelle senza indice.

 

Conclusione e Prossimi Passi

 

Spero che questa guida ti abbia aiutato a capire come creare gli indici del database – sulle colonne frequentemente interrogate – può velocizzare significativamente le interrogazioni. Questa è un’introduzione agli indici del database. Puoi anche creare indici multi-colonna, indici multipli per la stessa colonna e molto altro ancora. 

Puoi trovare tutto il codice utilizzato in questo tutorial in questo repository GitHub. Buon coding!     Bala Priya C è una sviluppatrice e scrittrice tecnica dall’India. Le piace lavorare all’incrocio tra matematica, programmazione, data science e creazione di contenuti. Le sue aree di interesse e competenza includono DevOps, data science e natural language processing. Ama leggere, scrivere, programmare e bere caffè! Al momento, sta lavorando per imparare e condividere le sue conoscenze con la comunità degli sviluppatori scrivendo tutorial, guide pratiche, articoli di opinione e altro ancora.