Želite da ubrzate pretrage u vašoj bazi podataka? Otkrijte kako se kreira indeks baze podataka pomoću SQL-a i kako optimizovati performanse upita – i ubrzati proces dobijanja podataka.
Kada preuzimate informacije iz tabele baze podataka, često je neophodno filtrirati podatke na osnovu određenih kolona.
Zamislite da pišete SQL upit da biste dobili podatke na osnovu specifičnih kriterijuma. Uobičajeno, izvršavanje upita pokreće skeniranje cele tabele dok se ne pronađu svi zapisi koji ispunjavaju postavljene uslove, a zatim se rezultati prikazuju.
Ovo može biti izuzetno neefikasno, posebno kada pretražujete velike tabele koje sadrže milione redova. Takve upite možete ubrzati stvaranjem indeksa baze podataka.
Šta je indeks baze podataka?
Kada u knjizi tražite određeni pojam, da li pretražujete celu knjigu od stranice do stranice, dok ne pronađete taj pojam? Verovatno ne.
Umesto toga, koristićete indeks da biste saznali na kojim se stranicama pominje taj pojam i direktno ćete preći na te stranice. Indeks baze podataka funkcioniše na sličan način kao indeks u knjizi.
Indeks baze podataka je skup pokazivača ili referenci na stvarne podatke, ali sortiran na način koji olakšava brzo pronalaženje podataka. Interno, indeks baze podataka može se implementirati koristeći strukture podataka kao što su B+ stabla i heš tabele. Zahvaljujući tome, indeks baze podataka poboljšava brzinu i efikasnost operacija preuzimanja podataka.
Kreiranje indeksa baze podataka u SQL-u
Sada kada smo upoznati sa tim šta je indeks baze podataka i kako može ubrzati preuzimanje informacija, hajde da naučimo kako da kreiramo indeks baze podataka u SQL-u.
Kada obavljate operacije filtriranja — navodeći uslove preuzimanja pomoću klauzule WHERE — moguće je da ćete češće postavljati upite za određenu kolonu.
CREATE INDEX naziv_indeksa ON tabela (kolona)
Gde:
- `naziv_indeksa` predstavlja naziv indeksa koji se kreira
- `tabela` se odnosi na tabelu u relacionoj bazi podataka
- `kolona` predstavlja naziv kolone u tabeli baze podataka na kojoj želimo da kreiramo indeks.
Takođe, možete kreirati indekse na više kolona – indeks sa više kolona – u zavisnosti od potreba. Evo sintakse za to:
CREATE INDEX naziv_indeksa ON tabela (kolona_1, kolona_2,...,kolona_k)
Sada pređimo na praktičan primer.
Razumevanje poboljšanja performansi indeksa baze podataka
Da bismo shvatili prednost kreiranja indeksa, potrebno je da napravimo tabelu baze podataka sa velikim brojem zapisa. Primeri koda su za SQLite, ali možete koristiti i druge RDBMS po vašem izboru, kao što su PostgreSQL i MySQL.
Popunjavanje tabele baze podataka zapisima
Takođe, možete koristiti ugrađeni modul random u Pythonu za kreiranje i umetanje zapisa u bazu podataka. Međutim, koristićemo Faker da popunimo tabelu baze podataka sa milion redova.
Sledeća Python skripta:
- Kreira i povezuje se sa bazom podataka `customer_db`.
- Pravi tabelu `customers` sa poljima: `ime`, `prezime`, `grad` i `broj_narudžbi`.
- Generiše sintetičke podatke i ubacuje podatke – milion zapisa – u tabelu `customers`.
Kod takođe možete pronaći na GitHubu.
# 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()
Sada možemo početi sa upitima.
Kreiranje indeksa na koloni grad
Pretpostavimo da želite da dobijete informacije o klijentima filtriranjem na osnovu kolone grad. Vaš SELECT upit će izgledati ovako:
SELECT kolona(e) FROM customers WHERE uslov;
Dakle, hajde da kreiramo `city_idx` na koloni `grad` u tabeli `customers`:
CREATE INDEX city_idx ON customers (city);
⚠ Kreiranje indeksa zahteva određeno vreme i predstavlja jednokratnu operaciju. Međutim, prednosti u performansama kada vam je potreban veliki broj upita – filtriranjem po koloni grad – biće značajne.
Brisanje indeksa baze podataka
Da biste obrisali indeks, možete koristiti naredbu DROP INDEX na sledeći način:
DROP INDEX naziv_indeksa;
Poređenje vremena izvršavanja upita sa i bez indeksa
Ako želite da pokrenete upite u okviru Python skripte, možete koristiti podrazumevani tajmer da biste dobili vreme izvršavanja za upite.
Alternativno, možete pokrenuti upite koristeći sqlite3 klijent komandne linije. Da biste radili sa `customer_db.db` koristeći klijent komandne linije, pokrenite sledeću komandu na terminalu:
$ sqlite3 customer_db.db;
Da biste dobili približna vremena izvršavanja, možete koristiti `.timer` funkcionalnost ugrađenu u sqlite3 na sledeći način:
sqlite3 > .timer on
> <upit ovde>
Pošto smo napravili indeks na koloni `grad`, upiti koji uključuju filtriranje na osnovu kolone `grad` u klauzuli WHERE biće mnogo brži.
Prvo, pokrenite upite. Zatim kreirajte indeks i ponovo pokrenite upite. Zabeležite vreme izvršavanja u oba slučaja. Evo nekoliko primera:
| Upit | Vreme bez indeksa | Vreme sa indeksom |
| SELECT * FROM customers WHERE city LIKE ‘Novi%’ 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 |
Vidimo da su vremena preuzimanja sa indeksom znatno brža od onih bez indeksa na koloni `grad`.
Najbolje prakse za kreiranje i korišćenje indeksa baza podataka
Uvek treba proveriti da li je dobit u performansama veća od troškova kreiranja indeksa baze podataka. Evo nekoliko najboljih praksi koje treba imati na umu:
- Odaberite prave kolone za kreiranje indeksa. Izbegavajte preveliki broj indeksa zbog visokih troškova.
- Svaki put kada se indeksirana kolona ažurira, odgovarajući indeks takođe treba da se ažurira. Dakle, kreiranje indeksa baze podataka (iako ubrzava preuzimanje) znatno usporava umetanje i operacije ažuriranja. Zbog toga treba kreirati indekse za kolone koje se često pretražuju, ali se retko ažuriraju.
Kada ne bi trebalo da pravite indeks?
Do sada biste trebali imati ideju kada i kako da kreirate indeks. Ali, hajde da navedemo i kada indeks baze podataka možda neće biti neophodan:
- Kada je tabela baze podataka mala i ne sadrži veliki broj redova, skeniranje cele tabele za preuzimanje podataka nije tako skupo.
- Nemojte kreirati indekse na kolonama koje se retko koriste za pretraživanje. Kada kreirate indekse na kolonama koje se ne pretražuju često, troškovi kreiranja i održavanja indeksa su veći od poboljšanja performansi.
Sumiranje
Hajde da pregledamo šta smo naučili:
- Kada upitujete bazu podataka da biste preuzeli podatke, možda ćete morati češće da filtrirate na osnovu određenih kolona. Indeks baze podataka na tako često pretraživanim kolonama može poboljšati performanse.
- Da biste kreirali indeks na jednoj koloni, koristite sintaksu: `CREATE INDEX naziv_indeksa ON tabela (kolona)`. Ako želite da kreirate indeks sa više kolona, koristite: `CREATE INDEX naziv_indeksa ON tabela (kolona_1, kolona_2,…,kolona_k)`
- Kada god se indeksirana kolona modifikuje, odgovarajući indeks takođe treba da se ažurira. Stoga, odaberite prave kolone – one koje se često pretražuju, ali se mnogo ređe ažuriraju – da biste kreirali indeks.
- Ako je tabela baze podataka relativno manja, troškovi kreiranja, održavanja i ažuriranja indeksa biće veći od poboljšanja performansi.
U većini modernih sistema za upravljanje bazama podataka postoji optimizator upita koji proverava da li će indeks na određenoj koloni ubrzati pokretanje upita. Zato, hajde da naučimo najbolje prakse za dizajn baze podataka.