PostgreSQL - ИНДЕКСЫ

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

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

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

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

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

Команда CREATE INDEX

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

CREATE INDEX index_name ON table_name;

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

PostgreSQL предоставляет несколько типов индексов: B-tree, 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);

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

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

пример

Ниже приведен пример, в котором мы создадим индекс в таблице КОМПАНИИ для столбца зарплаты.

# 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.

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