Lekcje modułu (1/4)
Indeksy i drzewa B-Tree
W jaki sposób baza danych może znaleźć pojedynczy rekord wśród milionów wierszy w ułamku sekundy?
Domyślnie, gdy pytamy SELECT * FROM customers WHERE city = 'Milano', Postgres musi wykonać Skanowanie sekwencyjne: fizycznie odczytuje całą tabelę z dysku wiersz po wierszu, porównując każde miasto z „Mediolanem”. Gdy tabela zawiera miliony rekordów, proces ten zmniejsza wydajność.
Główną odpowiedzią strukturalną na te wąskie gardła są Indeksy.
Indeksy (drzewa B)
Indeksy działają dokładnie tak, jak indeks analityczny na końcu dużej książki. Zamiast czytać całą książkę, aby znaleźć miejsce, w którym jest mowa o „Milano”, przeglądamy ostatnie strony, gdzie słowo „Milano” przekierowuje nas do [p. 45, 114, 230].
Bazy danych w tle tworzą oddzielną, bardzo zwartą strukturę danych (często „drzewo B”) uporządkowaną według danej kolumny lub kolumn, zawierającą bezpośrednie wskaźniki do fizycznego przechowywania pasujących wierszy.
-- Creare un indice semplice
CREATE INDEX idx_customers_city ON customers(city);Ta pojedyncza operacja uruchamia silnik Postgres, który wyodrębnia i indeksuje w tle wszystkie wartości city. Po ukończeniu każdy kolejny SELECT ... WHERE city = '...' będzie latał!
Klucze podstawowe i niepowtarzalność
Czy wiesz, że reguły klucza podstawowego (PRIMARY KEY) i UNIQUE potajemnie korzystają z indeksów? Ponieważ baza danych musi zapewnić, że adres e-mail lub identyfikator nigdy nie zostanie zduplikowany podczas wstawiania lub modyfikowania wierszy, PostgreSQL automatycznie tworzy na nich ukryty indeks B-Tree: w ten sposób sprawdzenie unikalności zajmuje mikrosekundy.
Dlatego złączenia w polu ID są niezwykle wydajne.
Upuszczenie indeksu
Jeśli zdasz sobie sprawę, że zmieniły się ciężkie zapytania i nigdy nie korzysta się z indeksu, mądrze jest go zniszczyć i odzyskać niezbędną przestrzeń:
DROP INDEX idx_customers_city;Twoja kolej
W naszym e-commerce zauważamy poprzez dashboardy, że bardzo często filtrujemy klientów według narodowości (kolumna Kraj w Klienci). Utwórz nowy indeks bazowy w kolumnie kraju i nazwij go „kraj_klienta_idx”.
Pokaż wskazówkę
Użyj CREATE INDEX nazwa_indeksu ON nazwa_tabeli(nazwa_kolumny);
Rozwiązanie dostępne po 3 próbach
Eksplorowanie wtórnych unikalnych indeksów
Indeks B-Tree może być również użyty do symulacji ograniczenia UNIQUE w czasie tworzenia i przyspieszenia wyszukiwania, jednocześnie czyniąc intencję logiczną jawną i egzekwowaną. Utwórz nowy indeks, w którym zadeklarujesz słowo kluczowe UNIQUE. Zdefiniuj go tak, aby blokował wszelkie niechciane zduplikowane rejestracje od innych użytkowników, wymuszając unikalność w kolumnie „e-mail” „klientów” i nadaj jej nazwę „idx_unique_email_customer”.
Pokaż wskazówkę
Napisz CREATE UNIQUE INDEX nazwa_indeksu NA tabeli(kolumnie);
Rozwiązanie dostępne po 3 próbach