PostgreSQL — ИНДЕКСЫ

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

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

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

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

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

Команда CREATE INDEX

Основной синтаксис CREATE INDEX следующий:

CREATE INDEX index_name ON table_name;

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

PostgreSQL предоставляет несколько типов индексов: B-дерево, Hash, GiST, SP-GiST и GIN. Каждый тип индекса использует свой алгоритм, который лучше всего подходит для разных типов запросов. По умолчанию команда 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 для столбца зарплаты:

# 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

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

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

DROP INDEX index_name;

Вы можете использовать следующую инструкцию для удаления ранее созданного индекса —

# DROP INDEX salary_index;

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

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

Индексы не должны использоваться на маленьких столах.

Таблицы с частыми, крупными пакетными обновлениями или операциями вставки.

Индексы не должны использоваться для столбцов, которые содержат большое количество значений NULL.

Столбцы, которыми часто манипулируют, не должны индексироваться.