[Explained] Cum se creează un index de bază de date în SQL

de
Tweet
Share
Share
Pin

Îți dorești să optimizezi timpul de răspuns al interogărilor tale în baza de date? Descoperă cum să creezi un index al bazei de date folosind SQL, îmbunătățind astfel eficiența interogărilor și accelerând accesul la date.

În procesul de extragere a datelor dintr-un tabel al bazei de date, adesea este necesar să aplici filtre pe anumite coloane.

Să presupunem că elaborezi o interogare SQL pentru a selecta date în funcție de anumite criterii. În mod implicit, executarea interogării implică o scanare completă a tabelului până când sunt identificate toate înregistrările care îndeplinesc condiția, și abia apoi sunt returnate rezultatele.

Această metodă poate fi extrem de ineficientă, în special când trebuie să interoghezi un tabel vast, conținând milioane de înregistrări. Crearea unui index al bazei de date poate accelera semnificativ aceste interogări.

Ce reprezintă un index al bazei de date?

Când vrei să găsești un termen specific într-o carte, vei citi fiecare pagină în parte? Evident, nu.

În schimb, vei consulta indexul pentru a vedea la ce pagini este menționat acel termen, și vei merge direct la acele pagini. Un index al bazei de date funcționează similar cu indexul unei cărți.

Un index al bazei de date constă dintr-un set de indicatori sau referințe către datele efective, sortate într-o manieră care facilitează accesul rapid la informații. Din punct de vedere tehnic, un index al bazei de date poate fi implementat folosind structuri de date precum arbori B+ și tabele hash. Astfel, un index al bazei de date crește viteza și eficiența operațiunilor de preluare a datelor.

Cum se creează un index de bază de date în SQL

Acum că am înțeles ce este un index de bază de date și cum poate accelera procesul de recuperare a datelor, să învățăm cum să creăm un index de bază de date în SQL.

Atunci când efectuezi operații de filtrare — specificând condiții de recuperare folosind clauza WHERE — este foarte probabil să interoghezi o anumită coloană mai des decât altele.

CREATE INDEX nume_index ON tabel (coloana)

Unde:

  • nume_index reprezintă numele indexului care urmează să fie creat
  • tabel se referă la tabelul din baza de date relațională
  • coloana indică numele coloanei din tabelul bazei de date pe care dorim să creăm indexul.

De asemenea, poți crea indecși pe mai multe coloane — un index compus — în funcție de necesități. Iată sintaxa corespunzătoare:

CREATE INDEX nume_index ON tabel (coloana_1, coloana_2,...,coloana_k)

Să analizăm un exemplu practic.

Cum îmbunătățește performanța un index al bazei de date

Pentru a înțelege avantajele creării unui index, vom crea un tabel de bază de date cu un număr mare de înregistrări. Exemplele de cod sunt date pentru SQLite. Dar poți utiliza și alte sisteme RDBMS, cum ar fi PostgreSQL și MySQL.

Crearea unui tabel de bază de date cu înregistrări

Poți utiliza modulul random integrat în Python pentru a crea și a insera înregistrări în baza de date. Însă, pentru acest exemplu, vom folosi Faker, pentru a popula tabelul bazei de date cu un milion de rânduri.

Următorul script Python:

  • Creează o conexiune la baza de date customer_db.
  • Creează un tabel de clienți cu câmpurile: prenume, nume, oraș și număr_comenzi.
  • Generează date fictive și inserează date – un milion de înregistrări – în tabelul clienților.

Poți consulta codul și 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 testarea interogărilor.

Crearea unui index pe coloana orașului

Să presupunem că vrei să obții informații despre clienți, filtrând după coloana orașului. Interogarea ta SELECT va arăta astfel:

SELECT coloana(e) FROM customers
WHERE condiție;

Prin urmare, să creăm un index city_idx pe coloana oraș din tabelul clienți:

CREATE INDEX city_idx ON customers (city);

⚠ Crearea unui index necesită un anumit timp și este o operațiune care se realizează o singură dată. Dar avantajele în termeni de performanță, când ai nevoie de multe interogări, filtrând după coloana orașului, vor fi considerabile.

Ștergerea unui index de bază de date

Pentru a șterge un index, poți folosi instrucțiunea DROP INDEX astfel:

DROP INDEX nume_index;

Compararea timpilor de interogare cu și fără index

Dacă vrei să rulezi interogări într-un script Python, poți utiliza temporizatorul implicit pentru a obține timpii de execuție.

Alternativ, poți rula interogările folosind linia de comandă a clientului sqlite3. Pentru a lucra cu customer_db.db folosind linia de comandă, rulează următoarea comandă în terminal:

$ sqlite3 customer_db.db;

Pentru a obține timpii aproximativi de execuție, poți utiliza funcționalitatea .timer încorporată în sqlite3 astfel:

sqlite3 > .timer on
        > <interogarea ta aici>

Deoarece am creat un index pe coloana oraș, interogările care implică filtrarea pe baza acestei coloane din clauza WHERE vor fi mult mai rapide.

Mai întâi, execută interogările. Apoi, creează indexul și rulează din nou interogările. Compară timpii de execuție în ambele cazuri. Iată câteva exemple:

Interogare Timp fără index Timp cu index
SELECT * FROM clienți
WHERE oraș LIKE „New%”
LIMIT 10;		 0.100 s 0.001 s
SELECT * FROM clienți
WHERE city=”New Wesley”;		 0,148 s 0,001 s
SELECT * FROM clienți
WHERE city IN („New Wesley”, „New Steven”, „New Carmenmouth”);		 0,247 s 0,003 s

Se observă că timpii de răspuns cu index sunt mult mai mici decât timpii fără index pe coloana orașului.

Cele mai bune practici pentru crearea și utilizarea indicilor de baze de date

Întotdeauna verifică dacă îmbunătățirile de performanță sunt mai importante decât costul creării unui index de bază de date. Iată câteva dintre cele mai bune practici pe care le poți reține:

  • Alege cu atenție coloanele pentru care creezi index. Evită să creezi prea mulți indici, pentru că generează costuri suplimentare.
  • De fiecare dată când o coloană indexată este actualizată, trebuie actualizat și indexul aferent. Așadar, crearea unui index de bază de date (deși accelerează recuperarea) încetinește semnificativ operațiunile de inserare și de actualizare. Prin urmare, este recomandat să creezi indici pe coloanele care sunt frecvent interogate, dar rar actualizate.

Când nu ar trebui să creezi un index?

Acum ar trebui să ai o idee clară despre când și cum să creezi un index. Dar să vedem și când un index de bază de date ar putea să nu fie necesar:

  • Atunci când tabelul bazei de date este mic și nu conține multe rânduri, scanarea completă a tabelului pentru a prelua date nu este la fel de costisitoare.
  • Nu crea indecși pe coloanele care sunt rar utilizate pentru interogare. Atunci când creezi indici pe coloane care nu sunt interogate frecvent, costul creării și menținerii unui index depășește beneficiile aduse de creșterea performanței.

În concluzie

Să recapitulăm ceea ce am învățat:

  • Când interoghezi o bază de date pentru a prelua date, uneori este necesar să filtrezi după anumite coloane. Un index de bază de date pe astfel de coloane interogate frecvent poate îmbunătăți performanța.
  • Pentru a crea un index pe o singură coloană, utilizează sintaxa: CREATE INDEX nume_index ON tabel (coloană). Dacă vrei să creezi un index pe mai multe coloane, utilizează: CREATE INDEX nume_index ON tabel (coloana_1, coloana_2,…,coloana_k)
  • De fiecare dată când o coloană indexată este modificată, ar trebui actualizat și indexul aferent. Prin urmare, alege cu grijă coloanele — cele interogate frecvent și actualizate rar — pentru a crea un index.
  • Dacă tabelul bazei de date este relativ mic, costul creării, întreținerii și actualizării unui index va fi mai mare decât îmbunătățirile de performanță obținute.

Î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 accelera interogarea. În continuare, să analizăm cele mai bune practici pentru proiectarea bazelor de date.