PostgreSQL - INDEKSY

Indeksy to specjalne tabele wyszukiwania, których mechanizm wyszukiwania bazy danych może używać do przyspieszenia pobierania danych. Mówiąc najprościej, indeks jest wskaźnikiem do danych w tabeli. Indeks w bazie danych jest bardzo podobny do indeksu na końcu książki.

Na przykład, jeśli chcesz odwołać się do wszystkich stron w książce, która omawia określony temat, musisz najpierw odwołać się do indeksu, który zawiera listę wszystkich tematów w porządku alfabetycznym, a następnie odnieś się do jednego lub kilku określonych numerów stron.

Indeks pomaga przyspieszyć zapytania SELECT i klauzule WHERE; jednak spowalnia wprowadzanie danych za pomocą instrukcji UPDATE i INSERT. Indeksy można tworzyć lub usuwać bez wpływu na dane.

Tworzenie indeksu obejmuje instrukcję CREATE INDEX, która umożliwia nazwanie indeksu, określenie tabeli i kolumny do indeksowania oraz wskazanie, czy indeks jest w porządku rosnącym, czy malejącym.

Indeksy mogą być również unikalne, podobnie jak ograniczenie UNIQUE, ponieważ indeks zapobiega zduplikowanym wpisom w kolumnie lub kombinacji kolumn, w których znajduje się indeks.

Polecenie CREATE INDEX

Podstawowa składnia CREATE INDEX wygląda następująco -

CREATE INDEX index_name ON table_name;

Typy indeksów

PostgreSQL udostępnia kilka typów indeksów: B-tree, Hash, GiST, SP-GiST i GIN. Każdy typ indeksu używa innego algorytmu, który najlepiej pasuje do różnych typów zapytań. Domyślnie polecenie CREATE INDEX tworzy indeksy B-drzewa, które pasują do większości typowych sytuacji.

Indeksy jednokolumnowe

Indeks jednokolumnowy to taki, który jest tworzony na podstawie tylko jednej kolumny tabeli. Podstawowa składnia jest następująca -

CREATE INDEX index_name
ON table_name (column_name);

Indeksy wielokolumnowe

Indeks wielokolumnowy jest zdefiniowany w więcej niż jednej kolumnie tabeli. Podstawowa składnia jest następująca -

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Niezależnie od tego, czy chcesz utworzyć indeks jednokolumnowy, czy indeks wielokolumnowy, weź pod uwagę kolumny, których możesz często używać w klauzuli WHERE zapytania jako warunki filtru.

Jeśli jest używana tylko jedna kolumna, należy wybrać indeks jednokolumnowy. Jeśli w klauzuli WHERE są często używane dwie lub więcej kolumn jako filtry, najlepszym wyborem będzie indeks wielokolumnowy.

Unikalne indeksy

Unikalne indeksy służą nie tylko wydajności, ale także integralności danych. Unikalny indeks nie pozwala na wstawienie do tabeli zduplikowanych wartości. Podstawowa składnia jest następująca -

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Częściowe indeksy

Indeks częściowy to indeks zbudowany na podzbiorze tabeli; podzbiór jest definiowany przez wyrażenie warunkowe (zwane predykatem częściowego indeksu). Indeks zawiera wpisy tylko dla tych wierszy tabeli, które spełniają predykat. Podstawowa składnia jest następująca -

CREATE INDEX index_name
on table_name (conditional_expression);

Niejawne indeksy

Niejawne indeksy to indeksy, które są automatycznie tworzone przez serwer bazy danych podczas tworzenia obiektu. Indeksy są tworzone automatycznie dla ograniczeń klucza podstawowego i ograniczeń unikalności.

Przykład

Poniżej znajduje się przykład, w którym utworzymy indeks w tabeli COMPANY dla kolumny wynagrodzenia -

# CREATE INDEX salary_index ON COMPANY (salary);

Teraz wypiszmy wszystkie indeksy dostępne w tabeli FIRMA za pomocą \d company Komenda.

# \d company

Daje to następujący wynik, gdzie company_pkey jest niejawnym indeksem, który został utworzony podczas tworzenia tabeli.

Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

Możesz wyświetlić listę całej bazy danych indeksów za pomocą rozszerzenia \di polecenie -

Polecenie DROP INDEX

Indeks można usunąć za pomocą PostgreSQL DROPKomenda. Należy zachować ostrożność podczas usuwania indeksu, ponieważ wydajność może zostać spowolniona lub poprawiona.

Podstawowa składnia jest następująca -

DROP INDEX index_name;

Możesz użyć następującej instrukcji, aby usunąć wcześniej utworzony indeks -

# DROP INDEX salary_index;

Kiedy należy unikać indeksów?

Chociaż indeksy mają na celu zwiększenie wydajności bazy danych, są chwile, kiedy należy ich unikać. Poniższe wytyczne wskazują, kiedy należy ponownie rozważyć użycie indeksu -

Indeksów nie należy używać w przypadku małych tabel.
Tabele, które mają częste, duże operacje aktualizacji lub wstawiania.
Indeksów nie należy używać w kolumnach, które zawierają dużą liczbę wartości NULL.
Kolumny, które są często modyfikowane, nie powinny być indeksowane.