Lecții din modul (3/4)
Indici compuși și parțiali
În lecțiile anterioare am abordat utilizarea indicilor pentru interogări DBMS liniare (WHERE country = 'Italy'). Dar când căutăm pe două fronturi combinate?
De exemplu, într-un comerț electronic real am putea dori să găsim comenzi care aparțin unui anumit ID de client ȘI se încadrează într-un interval de timp dat:
WHERE customer_id = 90 AND placed_at >= '2025-01-01'.
Dacă aveți doar un index de bază pe customer_id, Postgres va găsi cele 50 de comenzi ale clientului în cel mai scurt timp folosind arborele binar, dar apoi va trebui să le itereze prin toate cele 50 manual și secvenţial, filtrând leneș datele după ochi.
Indici compoziți
Putem spune SGBD să comande fișierele „dicționar analitic” nu pe o singură dimensiune cheie, ci pe mai multe.
CREATE INDEX idx_orders_customer_date ON orders(customer_id, placed_at);Un index „compozit” tratează ordinea parametrilor săi ca fiind foarte importantă: aici nodurile B-Tree grupează și categorizează rigid customer_id într-un bloc (cel mai discriminant pentru separare); în interiorul lor trăiește sub-arborele care se ramifică cu diferitele valori de timp placed_at în ordine perfect sortată.
Această structură anihilează brutal clauzele WHERE cu mai multe predicate imbricate.
Indici parțiali (Indici filtrați)
Fiecare index costă spațiu pe disc și CPU pe fluxurile de intrare de utilizator. Ce se întâmplă dacă comerțul electronic ar necesita sortarea și preluarea constantă a unui filtru, dar numai pentru o minoritate de înregistrări? (De exemplu: interogarea rapidă a verificării notificărilor necitite.) Crearea unui index „is_read” peste toate cele 10.000.000 de notificări este incredibil de plictisitoare dacă „Necitit” reprezintă doar 5 dintre ele.
CREATE INDEX idx_unread_notifs ON notifications(user_id)
WHERE is_read = false; -- Questa aggiuntiva è l'"Indice Parziale"Baza de date va construi un B-Tree fulgerător care stochează cheile de acces rapid la DB DOAR dacă înregistrarea este Necitită! Discul se va îndrăgosti de un index de 5 rânduri (3 octeți) în loc de unul cu 10 milioane. Fast SELECT beneficiază pe deplin:
SELECT * FROM notifications WHERE user_id = 2 AND is_read = false;
Rândul tău
Dorim să optimizăm căutarea parolelor temporare / a jetoanelor de resetare. Efectuăm foarte frecvent apeluri SELECT pentru toți clienții înscriși dintr-o anumită țară, combinați cu un oraș cunoscut.\nCreați un index compus numit „idx_cust_loc” pe „clienți”. Treceți mai întâi câmpul „țară” (care este mai exclusiv) și apoi câmpul „oraș”.
Afișează indiciu
Sintaxă: CREATE INDEX nume ON table(col1, col2);
Soluție disponibilă după 3 încercări
Explorarea indicilor condiționali (parțiali).
Optimizați o căutare a utilizatorilor de cumpărături: utilizatorii se înregistrează în prealabil, dar trebuie doar să-i listăm în ordinea lor cronologică de înscriere (creați un index pe „signed_up_on” în „clienți”), dar LIMITAT NUMAI la clienții din șirul exact „oraș”: „Milano”.
Afișează indiciu
Este declarat în mod normal pe proprietatea țintă; la sfârșit adăugați cuvântul cheie magic WHERE oraș = „Milano”.
Soluție disponibilă după 3 încercări