PostgreSQL - Индексы

Индексы — это специальные таблицы поиска, которые поисковая система базы данных может использовать для ускорения поиска данных. Проще говоря, индекс – это указатель данных в таблице. Индекс в базе данных очень похож на Содержание в конце таблицы.

Например, если Вы хотите обратиться к страницам книги, посвященным какой-то определенной теме, в первую очередь Вы должны обратиться к Содержанию, перечисляемому все темы в алфавитном порядке, и только затем перейти на искомые страницы.

Индексы помогают ускорить запросы SELECT и условия WHERE; однако, они замедляют ввод данных для UPDATE и INSERT операций. Индексы создаются и удаляются без какого-либо воздействия на данные.

Создание индексов подразумевает команду CREATE INDEX, которая позволяет Вам назвать индекс, указать таблицу и столбец (цы), которые нужно проиндексировать, и указать, в каком порядке находятся индексы – нарастающем или убывающем.

Индексы также могут быть уникальными, аналогичными ограничению UNIQUE , где индекс предотвращает дублирование записей в столбце или столбцах, для которых указан индекс.

Команда CREATE INDEX

Базовый синтаксис CREATE INDEX выглядит следующим образом:

CREATE INDEX index_name ON table_name;

Типы индексов

PostgreSQL поддерживает несколько типов индексов: B-дерево, хеш, GiST, SP-GiST, GIN и BRIN. Для разных типов индексов применяются разные алгоритмы, ориентированные на определённые типы запросов. По умолчанию команда CREATE INDEX создаёт индексы типа B-дерево, эффективные в большинстве случаев.

Индексы одного столбца

Подобные индексы определены только для одного столба таблицы. Базовый синтаксис выглядит следующим образом:

CREATE INDEX index_name
ON table_name (column_name);

Индексы нескольких столбцов

Подобные индексы определяются для нескольких столбцов. Базовый синтаксис выглядит следующим образом:

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Независимо от того, хотите ли Вы создать индекс с одним столбцом или индекс с несколькими столбцами, примите во внимание столбцы, которые Вы можете часто использовать в предложении WHERE запроса в качестве условий фильтрации.

Если используется только один столбец, следует выбрать одностолбцовый индекс. Если есть два или более столбца, которые часто используются в предложении WHERE в качестве фильтров, лучшим выбором будет индекс с несколькими столбцами.

Уникальные индексы

Уникальные индексы используются не только для производительности, но и для целостности данных. Уникальный индекс не позволяет вставлять в таблицу повторяющиеся значения. Базовый синтаксис выглядит следующим образом:

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Частичные индексы

Частичный индекс — это индекс, который строится по подмножеству строк таблицы, определяемому условным выражением (оно называется предикатом частичного индекса). Такой индекс содержит записи только для строк, удовлетворяющих предикату. Базовый синтаксис выглядит так:

CREATE INDEX index_name
on table_name (conditional_expression);

Неявные индексы

Неявные индексы — это индексы, которые автоматически создаются сервером базы данных при создании объекта. Индексы создаются автоматически для ограничений первичного ключа и ограничений уникальности.

Пример

Создадим индекс в таблице COMPANY для столбца «зарплата» (salary):

# CREATE INDEX salary_index ON COMPANY (salary);

Пролистаем все индексы таблицы COMPANY, использую команду \d company .

# \d company

Результат будет следующим (где company_pkey – неявный индекс, созданный одновременно с созданием таблицы):

       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)

Все индексы базы данных можно просмотреть при помощи команды \di.

Команда DROP INDEX

Индекс можно удалить при помощи команды DROP. При удалении индекса стоит соблюдать осторожность, поскольку это может как замедлить, так и ускорить производительность.

Базовый синтаксис выглядит следующим образом:

DROP INDEX index_name;

Для того, чтобы удалить ранее созданный индекс, необходимо использовать следующий индекс:

# DROP INDEX salary_index;

Когда стоит избегать использования индексов?

Несмотря на то, что индексы созданы для улучшения производительности базы данных, есть случаи, когда их все-таки лучше избегать:

Не нужно использовать индексы для маленьких таблиц;
Таблицы с частыми крупными пакетными операциями обновления или вставки;
Не следует использовать индексы для столбцов, содержащих большое количество значений NULL;
Столбцы, которые часто используются, не должны индексироваться.

← Предыдущая статья

PostgreSQL - Триггеры

Следующая статья →

PostgreSQL – команда ALTER TABLE