Doriți să accelerați interogările bazei de date? Aflați cum să creați un index al bazei de date folosind SQL și să optimizați performanța interogărilor și să accelerați recuperarea datelor.
Când preluați date dintr-un tabel de bază de date, va trebui să filtrați mai des pe anumite coloane.
Să presupunem că scrieți o interogare SQL pentru a prelua datele pe baza unor condiții specifice. În mod implicit, rularea interogării rulează o scanare a tabelului complet până când toate înregistrările care satisfac condiția au fost găsite și apoi returnează rezultatele.
Acest lucru poate fi extrem de ineficient atunci când trebuie să interogați un tabel mare de bază de date cu câteva milioane de rânduri. Puteți accelera astfel de interogări prin crearea unui index al bazei de date.
Cuprins
Ce este un index de bază de date?
Când doriți să găsiți un anumit termen într-o carte, veți face o scanare completă a cărții – o pagină după alta – căutând termenul anume? Ei bine, nu.
Veți căuta în schimb indexul pentru a afla ce pagini fac referire la termen și veți sări direct la acele pagini. Un index dintr-o bază de date funcționează mult ca indexurile dintr-o carte.
Un index al bazei de date este un set de indicatori sau referințe la datele reale, dar sortate într-un mod care face recuperarea datelor mai rapidă. Intern, un index al bazei de date poate fi implementat folosind structuri de date precum arbori B+ și tabele hash. Prin urmare, un index al bazei de date îmbunătățește viteza și eficiența operațiunilor de recuperare a datelor.
Crearea unui index de bază de date în SQL
Acum că știm ce este un index al bazei de date și cum poate accelera recuperarea datelor, să învățăm cum să creăm un index al bazei de date în SQL.
Când efectuați operațiuni de filtrare — prin specificarea condiției de recuperare folosind o clauză WHERE — este posibil să doriți să interogați o anumită coloană mai des decât altele.
CREATE INDEX index_name ON table (column)
Aici,
- index_name este numele indexului care urmează să fie creat
- tabelul se referă la tabelul din baza de date relațională
- coloana se referă la numele coloanei din tabelul bazei de date pe care trebuie să creăm indexul.
De asemenea, puteți crea indecși pe mai multe coloane — un index pe mai multe coloane — în funcție de cerințe. Iată sintaxa pentru a face acest lucru:
CREATE INDEX index_name ON table (column_1, column_2,...,column_k)
Acum să trecem la un exemplu practic.
Înțelegerea câștigurilor de performanță ale indexului bazei de date
Pentru a înțelege avantajul creării unui index, trebuie să creăm un tabel de bază de date cu un număr mare de înregistrări. Exemplele de cod sunt pentru SQLite. Dar puteți utiliza și alte RDBMS la alegere, cum ar fi PostgreSQL și MySQL.
Popularea unui tabel de bază de date cu înregistrări
De asemenea, puteți utiliza modulul aleatoriu încorporat al lui Python pentru a crea și a insera înregistrări în baza de date. Cu toate acestea, vom folosi Faker pentru a popula tabelul bazei de date cu un milion de rânduri.
Următorul script Python:
- Creează și se conectează la baza de date customer_db.
- Creați un tabel de clienți cu câmpurile: prenume, prenume, oraș și număr_comenzi.
- Generează date sintetice și inserează date – un milion de înregistrări – în tabelul clienților.
Puteți găsi și codul pe 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()
Acum putem începe să interogăm.
Crearea unui index pe coloana orașului
Să presupunem că doriți să obțineți informațiile despre clienți prin filtrarea în funcție de coloana orașului. Interogarea dvs. SELECT va arăta astfel:
SELECT column(s) FROM customers WHERE condition;
Deci, să creăm city_idx pe coloana oraș din tabelul clienți:
CREATE INDEX city_idx ON customers (city);
⚠ Crearea unui index necesită o perioadă de timp deloc neglijabilă și este o operațiune unică. Dar beneficiile de performanță atunci când aveți nevoie de un număr mare de interogări, prin filtrarea pe coloana orașului, vor fi semnificative.
Ștergerea unui index de bază de date
Pentru a șterge un index, puteți utiliza instrucțiunea DROP INDEX astfel:
DROP INDEX index_name;
Compararea timpilor de interogare cu și fără index
Dacă doriți să rulați interogări într-un script Python, puteți utiliza temporizatorul implicit pentru a obține timpii de execuție pentru interogări.
Alternativ, puteți rula interogările folosind clientul de linie de comandă sqlite3. Pentru a lucra cu customer_db.db folosind clientul de linie de comandă, rulați următoarea comandă la terminal:
$ sqlite3 customer_db.db;
Pentru a obține timpii aproximativi de execuție, puteți utiliza funcționalitatea .timer încorporată în sqlite3 astfel:
sqlite3 > .timer on
> <query here>
Deoarece am creat un index pe coloana oraș, interogările care implică filtrarea pe baza coloanei oraș din clauza WHERE vor fi mult mai rapide.
Mai întâi, executați interogările. Apoi, creați indexul și rulați din nou interogările. Notați timpii de execuție în ambele cazuri. Aici sunt cateva exemple:
QueryTime fără IndexTime cu IndexSELECT * FROM clienți
UNDE oraș ca „New%”
LIMIT 10;0.100 s0.001 sSELECT * FROM clienți
WHERE city=’New Wesley’;0,148 s0,001 sSELECT * FROM clienți
WHERE city IN („New Wesley”, „New Steven”, „New Carmenmouth”);0,247 s0,003 s
Vedem că timpii de regăsire cu index sunt cu câteva ordine mai rapid decât cei fără index pe coloana orașului.
Cele mai bune practici pentru crearea și utilizarea indicilor de baze de date
Ar trebui să verificați întotdeauna dacă câștigurile de performanță sunt mai mari decât costul general al creării unui index al bazei de date. Iată câteva dintre cele mai bune practici de reținut:
- Alegeți coloanele potrivite pentru a crea un index. Evitați să creați prea mulți indici din cauza supraîncărcării substanțiale.
- De fiecare dată când o coloană indexată este actualizată, ar trebui să fie actualizat și indexul corespunzător. Deci, crearea unui index al bazei de date (deși accelerează recuperarea) încetinește semnificativ inserările și operațiunile de actualizare. Prin urmare, ar trebui să creați indecși pe coloanele care sunt interogate frecvent, dar rar actualizate.
Când nu ar trebui să creați un index?
Până acum ar trebui să aveți o idee despre când și cum să creați un index. Dar să precizăm și când indexul bazei de date ar putea să nu fie necesar:
- Când tabelul bazei de date este mic și nu conține un număr mare de rânduri, scanarea tabelului complet pentru a prelua date nu este la fel de costisitoare.
- Nu creați indecși pe coloanele care sunt rareori utilizate pentru recuperare. Când creați indici pe coloane care nu sunt interogate frecvent, costul creării și întreținerii unui index depășește câștigurile de performanță.
Rezumând
Să revizuim ceea ce am învățat:
- Când interogați o bază de date pentru a prelua date, poate fi necesar să filtrați mai des pe baza anumitor coloane. Un index al bazei de date pe astfel de coloane interogate frecvent poate îmbunătăți performanța.
- Pentru a crea un index pe o singură coloană, utilizați sintaxa: CREATE INDEX nume_index ON tabel (coloană). Dacă doriți să creați indexul cu mai multe coloane, utilizați: CREATE INDEX index_name ON tabel (coloana_1, coloana_2,…,coloana_k)
- Ori de câte ori o coloană indexată este modificată, ar trebui să fie actualizat și indexul corespunzător. Prin urmare, alegeți coloanele potrivite – interogate frecvent și mult mai puțin actualizate – pentru a crea un index.
- Dacă tabelul bazei de date este relativ mai mic, costul creării, întreținerii și actualizării unui index va fi mai mare decât câștigurile de performanță.
În majoritatea sistemelor moderne de gestionare a bazelor de date, există un optimizator de interogări care verifică dacă un index pe o anumită coloană va face interogarea să ruleze mai rapid. În continuare, să învățăm cele mai bune practici pentru proiectarea bazelor de date.