Indeksy w bazach danych. Rodzaje indeksów.
.png?locale=pl)
Indeksy w bazach danych są używane do przyspieszenia wyszukiwania i pobierania danych. Działają podobnie do spisów treści w książce, umożliwiając szybki dostęp do potrzebnych rekordów bez skanowania całej tabeli. Główne cele stosowania indeksów:
1. Przyspieszenie wyszukiwania. Indeksy pozwalają szybko znajdować wiersze spełniające określone warunki zapytania (np. `WHERE`).
2. Optymalizacja sortowania i grupowania. Jeśli zapytanie zawiera operacje `ORDER BY` lub `GROUP BY`, indeksy pomagają przyspieszyć ich wykonanie.
3. Przyspieszenie łączenia (JOIN). Podczas łączenia tabel indeksy pomagają szybko znajdować pasujące wiersze w powiązanych tabelach.
4. Zapewnienie unikalności. Unikalne indeksy gwarantują brak zduplikowanych wartości w kolumnie lub grupie kolumn.
5. Zwiększenie wydajności. Indeksy zmniejszają liczbę operacji wejścia/wyjścia (I/O), co jest szczególnie istotne dla dużych tabel.
Jednak indeksy mają także wady:
- Dodatkowe zużycie pamięci. Indeksy zajmują dodatkowe miejsce na dysku.
- Obciążenie operacji modyfikacji. Podczas wstawiania, aktualizacji lub usuwania danych indeksy muszą być aktualizowane, co może spowolnić te operacje.
Dlatego indeksy należy tworzyć, uwzględniając konkretne zapytania i obciążenie bazy danych, aby osiągnąć optymalną równowagę między wydajnością a kosztami utrzymania.
Rodzaje indeksów.
W bazach danych istnieje kilka rodzajów indeksów, z których każdy jest przeznaczony do określonych zastosowań. Podstawowe typy indeksów:
1. Indeksy jednokolumnowe
- Tworzone dla jednej kolumny tabeli.
- Odpowiednie do przyspieszenia wyszukiwania po jednej kolumnie.
Przykład: `CREATE INDEX idx_name ON users (name);`
2. Indeksy złożone
- Tworzone dla kilku kolumn.
- Przydatne w zapytaniach filtrujących lub sortujących dane według wielu kolumn.
- Kolejność kolumn w indeksie ma znaczenie: zapytania powinny korzystać z prefiksu indeksu (np. jeśli indeks jest utworzony na `(col1, col2)`, zapytania filtrujące tylko po `col1` będą używać indeksu, ale zapytania tylko po `col2` już nie).
Przykład: `CREATE INDEX idx_name_age ON users (name, age);`
3. Indeksy unikalne
- Gwarantują, że wszystkie wartości w indeksowanej kolumnie (lub grupie kolumn) są unikalne.
- Używane do zapewnienia integralności danych.
Przykład: `CREATE UNIQUE INDEX idx_email ON users (email);`
4. Indeksy pełnotekstowe
- Przeznaczone do wyszukiwania danych tekstowych (np. słów kluczowych lub fraz).
- Obsługują zaawansowane zapytania wyszukiwania, takie jak wyszukiwanie według synonimów lub morfologii.
Przykład: `CREATE FULLTEXT INDEX idx_content ON articles (content);`
5. Indeksy przestrzenne
- Używane do pracy z danymi geograficznymi (np. współrzędnymi, geometrią).
- Optymalizują wyszukiwanie obiektów geograficznych.
Przykład: `CREATE SPATIAL INDEX idx_location ON places (coordinates);`
6. Indeksy haszujące
- Wykorzystują funkcje haszujące do szybkiego wyszukiwania na podstawie dokładnych dopasowań.
- Efektywne dla operacji `=`, ale nie nadają się do zapytań zakresowych (np. `BETWEEN`, `>`).
Przykład: `CREATE INDEX idx_hash ON users USING HASH (email);`
7. Indeksy B-drzewiaste
- Najczęściej spotykany typ indeksów.
- Obsługują zapytania zakresowe (np. `>`, `<`, `BETWEEN`), a także dokładne dopasowania.
Przykład: `CREATE INDEX idx_btree ON users (age);`
8. Indeksy bitowe
- Stosowane dla kolumn z niewielką liczbą unikalnych wartości (np. płeć, status).
- Efektywne dla operacji `AND`, `OR` i `NOT`.
Przykład: `CREATE BITMAP INDEX idx_status ON orders (status);`
9. Indeksy klastrowane
- Określają fizyczny porządek danych w tabeli.
- Każda tabela może mieć tylko jeden indeks klastrowany.
Przykład: W MySQL klucz główny (`PRIMARY KEY`) automatycznie tworzy indeks klastrowany.
10. Indeksy nieklastrowane
- Tworzą oddzielną strukturę danych, która wskazuje na wiersze w tabeli.
- Tabela może mieć wiele indeksów nieklastrowanych.
Przykład:`CREATE INDEX idx_nonclustered ON users (name);`
11. Indeksy częściowe
- Indeksują tylko podzbiór danych spełniający określony warunek.
- Przydatne w przypadku dużych tabel, gdzie indeksowanie wszystkich wierszy jest nieefektywne.
Przykład: `CREATE INDEX idx_active_users ON users (name) WHERE status = 'active';`
12. Indeksy funkcjonalne
- Indeksują wynik funkcji zastosowanej do kolumny.
- Przydatne w zapytaniach, które filtrują lub sortują dane na podstawie wyników funkcji.
Przykład: `CREATE INDEX idx_lower_name ON users (LOWER(name));`
13. Indeksy pokrywające
- Zawierają wszystkie niezbędne dane do wykonania zapytania, co pozwala uniknąć odwołań do samej tabeli.
Przykład: `CREATE INDEX idx_covering ON users (id, name, age);`(jeśli zapytanie wybiera tylko te kolumny).
14. Indeksy na wyrażeniach
- Podobne do indeksów funkcjonalnych, ale mogą zawierać bardziej złożone wyrażenia.
Przykład: `CREATE INDEX idx_expr ON orders (total_price * quantity);`
15. Indeksy dla tablic (GIN, GiST, BRIN)
- Stosowane w PostgreSQL do obsługi tablic, JSON, wyszukiwania pełnotekstowego i innych złożonych typów danych.
Przykład: `CREATE INDEX idx_gin ON documents USING GIN (tags);`
Wybór odpowiedniego typu indeksu zależy od struktury danych, typów zapytań i wymagań dotyczących wydajności. Niewłaściwe użycie indeksów może obniżyć wydajność, dlatego ważne jest analizowanie zapytań i testowanie ich wpływu na bazę danych.
Назад