Odpowiedź

Indeksy — specjalne struktury danych (najczęściej oparte na drzewach B), służące do szybkiego wyszukiwania danych na podstawie określonej kolumny lub zestawu kolumn tabeli. Indeksy przyspieszają wybieranie, sortowanie i filtrowanie, zmniejszając liczbę przeglądanych wierszy.

Typy indeksów:

• Zwykłe (B-tree, hash)
• Złożone (wielokolumnowe)
• Unikalne (zapewniające unikalność wartości)
• Przykrywające (covering)
• Pełnotekstowe (full-text)

Indeksy przyspieszają:

• WHERE ... = ...
• JOIN na indeksowanej kolumnie
• ORDER BY i GROUP BY na indeksowanej kolumnie

Mogą spowolnić:

• Wstawianie, aktualizowanie, usuwanie — indeksy wymagają dodatkowych operacji dla utrzymania swojej struktury.
• Jeśli zapytania często używają kolumn, na których nie ma indeksów, indeksy będą bezużyteczne, a przy masowych wstawieniach — nawet szkodliwe.

Przykład tworzenia indeksu:

CREATE INDEX idx_user_email ON users (email);

Przykład, gdzie indeks nie pomaga:

SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- jeśli indeks jest zbudowany na email, ale zapytanie używa funkcji lower(email), indeks nie jest używany!

Pytanie z podstępem

Czy dodanie indeksu do wszystkich kolumn tabeli zawsze przyspieszy wykonanie wszystkich zapytań SELECT?

Odpowiedź:
Nie. Indeksy przyspieszają tylko te zapytania, gdzie filtrowanie lub sortowanie odbywa się ściśle na indeksowanej kolumnie bez stosowania funkcji lub operacji, które uniemożliwiają wykorzystanie indeksu. Nadmiar indeksów nie tylko spowalnia INSERT/UPDATE/DELETE, ale również zajmuje dużo miejsca, a niektóre złożone zapytania mogą całkowicie omijać indeksy (na przykład podczas skanowania po zakresie z wyrażeniem).

Przykład:

SELECT * FROM orders WHERE year(order_date) = 2023;
-- jeśli indeks jest tylko na order_date, indeks nie działa z powodu funkcji year()

---

Historia

W systemie pocztowym utworzono wyzwalacz, który tworzył indeks dla każdego często używanego pola. Po pół roku wydajność systemu spadła — każdy wstawiony lub zmieniony wiersz zajmował 4-5 razy więcej czasu. Po audycie liczba indeksów została zmniejszona, a system przyspieszył.

---

Historia

W platformie reklamowej często występowały zapytania SELECT z filtrem według substring(url, 1, 10). Mimo indeksu na url, SQL nie wykorzystał indeksu z powodu funkcji substring. Rozwiązaniem było wprowadzenie osobnego pola do takiego wyboru i indeksu na nim.

---

Historia

W programie lojalnościowym utworzono złożony indeks na pola (customer_id, shop_id). Przy zapytaniach tylko według shop_id indeks nie był używany, co prowadziło do pełnego skanowania tabeli. To spowodowało utratę korzyści z indeksu przy obliczeniach bonusów. Pomogła optymalizacja: osobny indeks na shop_id.