Covering indeksi u MySQL-u: optimizacija koja uklanja čitanje tabele

Covering indeks je indeks koji sadrži sve podatke potrebne za izvršenje upita. Kada je tako, MySQL može da odgovori na upit direktno iz strukture indeksa, bez naknadnog čitanja redova tabele.

Na primer:

SELECT id, status
FROM reservation
WHERE user_id = 100;

Sa ovim indeksom:

INDEX idx_user_status (user_id, status)

Na prvi pogled izgleda da indeks ne sadrži kolonu id. Međutim, u InnoDB-u svaki sekundarni indeks interno automatski sadrži primarni ključ. To znači da MySQL u indeksu već ima user_id, status i id.

Pošto su sve potrebne kolone već tu, MySQL ne mora da poseti red u klasterizovanoj tabeli. To je suština covering indeksa.

Zašto je čitanje tabele skupo

U InnoDB-u indeks primarnog ključa čuva same podatke reda. Sekundarni indeksi čuvaju indeksirane kolone zajedno sa referencom na primarni ključ. Kada upit koristi običan sekundarni indeks, MySQL prvo pretraži sekundarni indeks, pronađe odgovarajuće primarne ključeve, a zatim skače u klasterizovani indeks primarnog ključa da pročita ostatak reda.

Taj drugi korak je često najskuplji. Ako upitu odgovara 50.000 redova, MySQL će možda morati da uradi 50.000 dodatnih pretraga po klasterizovanoj tabeli. Na velikim tabelama to znači nasumičan IO, pritisak na buffer pool, veće opterećenje procesora i sporije odgovore pod opterećenjem.

Covering indeks uklanja tu dodatnu fazu. MySQL sve što mu treba nalazi u sekundarnom indeksu i odmah vraća rezultat.

Primer bez covering indeksa

CREATE TABLE reservation (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  status VARCHAR(20),
  created_at DATETIME,
  total_price DECIMAL(10,2)
);

Zamisli ovaj indeks:

INDEX idx_user (user_id)

I ovaj upit:

SELECT status
FROM reservation
WHERE user_id = 100;

MySQL može da iskoristi idx_user da pronađe odgovarajuće redove, ali indeks ne sadrži status. Zato posle pronalaženja primarnih ključeva i dalje mora da čita prave redove tabele da bi dobio vrednost statusa.

To je bolje od čitanja cele tabele, ali nije idealno. Indeks pomaže da se redovi pronađu, ali ne odgovara u potpunosti na upit.

Primer sa covering indeksom

Sada promeni indeks:

INDEX idx_user_status (user_id, status)

Upit ostaje isti:

SELECT status
FROM reservation
WHERE user_id = 100;

Ovog puta se user_id koristi za filtriranje, a status je već dostupan u indeksu. MySQL ne mora da čita ceo red iz klasterizovane tabele. Sam indeks pokriva upit.

Zato covering indeksi mogu da donesu tako veliko poboljšanje. Oni ne čine samo pretragu reda bržom - oni je potpuno uklanjaju.

Kako prepoznati covering indeks u EXPLAIN-u

Plan izvršenja možeš da pogledaš ovako:

EXPLAIN
SELECT status
FROM reservation
WHERE user_id = 100;

U MySQL-u pokriven upit često prikazuje Using index u koloni Extra.

type: ref
key: idx_user_status
Extra: Using index

Izraz Using index znači da MySQL može da izvrši upit samo iz indeksa. Ne mora da čita ceo red tabele.

Pazi da ne pomešaš Using index sa Using where, Using temporary ili Using filesort. Oni opisuju različite delove plana izvršenja. Upit i dalje može da koristi covering indeks i ima uslov filtriranja, ali privremene tabele i filesort obično ukazuju da je moguća dodatna optimizacija.

Primer kao iz produkcije

Uzmimo sistem za pretragu smeštaja u kom korisnici filtriraju slobodne jedinice i sortiraju ih po ceni:

SELECT unit_id, price
FROM availability
WHERE facility_id = 100
  AND available = 1
ORDER BY price ASC
LIMIT 20;

Osnovni indeks može da izgleda ovako:

INDEX idx_facility (facility_id)

On pomaže MySQL-u da pronađe redove za jedan objekat, ali ne rešava upit u potpunosti. MySQL će možda i dalje morati da proveri dostupnost, pročita redove tabele, sortira po ceni i tek onda primeni limit.

Bolji indeks bi bio:

INDEX idx_search (
  facility_id,
  available,
  price,
  unit_id
)

Ovaj indeks je mnogo jači. MySQL može da filtrira po facility_id, filtrira po available, čita redove već poređane po price, vrati unit_id i price direktno iz indeksa i stane posle 20 rezultata.

Ovo spaja tri optimizacije odjednom: filtriranje, redosled i pokrivanje. U stvarnim sistemima ovakva izmena može da pretvori spor upit za listu u veoma brz.

Redosled kolona u složenom indeksu je važan

Česta greška je dodati prave kolone pogrešnim redom. Složeni indeksi se čitaju sleva nadesno. Redosled kolona nije kozmetika - on određuje koliko je indeks koristan.

Za ovaj upit:

SELECT id, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Dobar indeks je:

INDEX idx_paid_created (
  status,
  created_at,
  id
)

MySQL prvo može da filtrira po status, zatim da čita odgovarajuće redove po redosledu created_at, pa da vrati id direktno iz indeksa, jer je primarni ključ već uključen u InnoDB sekundarne indekse.

Praktično pravilo: prvo uslovi jednakosti, zatim uslovi opsega, pa kolone za sortiranje i na kraju kolone koje su potrebne samo da bi se pokrio izabrani rezultat.

Problem sa SELECT *

Covering indeksi postaju skoro nemogući kada važni upiti koriste SELECT *.

SELECT *
FROM reservation
WHERE user_id = 100;

Ako tabela ima mnogo kolona, indeks bi morao da sadrži skoro celu tabelu da bi pokrio ovaj upit. To je obično loša ideja.

Bolje je birati samo kolone koje su potrebne stranici, API-ju ili pozadinskom procesu:

SELECT id, status, created_at
FROM reservation
WHERE user_id = 100;

Kada se broj izabranih kolona smanji, covering indeks postaje mnogo realniji.

Covering indeksi nisu besplatni

Covering indeksi su moćni, ali imaju cenu. Svaka dodatna kolona povećava veličinu indeksa. Veći indeksi troše više memorije i prostora na disku i usporavaju upis. Insert, update i delete moraju da održavaju indekse.

Zato covering indekse treba projektovati za važne upite, a ne za svaki upit. Upit koji se izvršava hiljadama puta u minutu je dobar kandidat. Retko korišćen izveštaj u admin panelu verovatno nije.

Previše indeksa može tiho da ošteti sistem. Čitanje može da postane brže na jednom mestu, dok se upis, replikacija, korišćenje keša i odluke optimizatora pogoršaju na drugim mestima.

Kada covering indeksi obično pomažu

Covering indeksi su posebno korisni za čitanja sa velikim saobraćajem: stranice sa listama, rezultati pretrage, automatsko dopunjavanje, kontrolne table, feed-ovi, paginacija i API-ji koji vraćaju mali skup kolona.

Manje su korisni kada upit vraća mnogo kolona, dodiruje veliki procenat tabele, retko se izvršava ili pripada toku sa mnogo upisa, gde svaki dodatni indeks ima vidljivu cenu.

Koristi EXPLAIN ANALYZE, ne nagađanje

Najbezbedniji način optimizacije je merenje pre i posle izmene indeksa.

EXPLAIN ANALYZE
SELECT id, status
FROM reservation
WHERE user_id = 100;

Gledaj stvaran broj pročitanih redova, vreme izvršenja, petlje i da li upit koristi očekivani indeks. Ako novi indeks smanjuje čitanje tabele i vreme izvršenja bez neprihvatljivog troška pri upisu, optimizacija je opravdana.

Praktična lista koraka

Počni od sporog upita, ne od indeksa. Prvo pronađi tačan SQL koji je važan. Zatim smanji broj izabranih kolona. Posle toga projektuj složeni indeks oko filtriranja, sortiranja i pokrivanja. Na kraju izmeri rezultat sa EXPLAIN ANALYZE na podacima nalik produkcijskim.

Dobar covering indeks posle dodavanja deluje dosadno. Upit čita manje redova, izbegava nepotreban pristup tabeli i postaje predvidiv pod opterećenjem.

Zaključak

Covering indeksi su jedna od najisplativijih optimizacija baze, jer uklanjaju posao umesto da ga samo ubrzaju.

Ideja je jednostavna: stavi u indeks sve što je upitu potrebno. Ali rezultat može biti dramatičan, naročito na velikim tabelama gde čitanje tabele izaziva nasumičan IO i pritisak na memoriju.

Ako je upit važan, često se izvršava, vraća mali skup kolona i predvidivo filtrira ili sortira, vredi proveriti da li ga covering indeks može ubrzati.

U ozbiljnoj SQL optimizaciji covering indeksi nisu teorijski trik. Oni su praktičan alat koji često razdvaja prihvatljiv sistem od sistema koji se skalira.