Индекс PostgreSQL
Индекс — это специальный табличный запрос, который ускоряет извлечение данных поисковым движком. Проще говоря, индекс — это указатель на данные в таблице. Индекс в базе данных очень похож на индекс книги.
Возьмём в качестве аналогии страницы с индексом китайского словаря. Мы можем быстро находить нужные иероглифы с помощью указателей (индексов), отсортированных по пиньиню, количеству черт, радикалам и т. д.
Индексы помогают ускорить SELECT-запросы и условия WHERE, но замедляют ввод данных при использовании операторов UPDATE и INSERT. Индексы можно создавать или удалять, но они не влияют на данные.
Оператор CREATE INDEX используется для создания индекса. Он позволяет задать имя индекса, указать таблицу и столбец или столбцы для индексирования, а также указать, сортируется ли индекс по возрастанию или по убыванию.
Индексы также могут быть уникальными, подобно ограничению UNIQUE, предотвращая дублирование записей в столбце или комбинации столбцов.
Команда CREATE INDEX
Синтаксис CREATE INDEX (создание индекса) выглядит следующим образом:
CREATE INDEX index_name ON table_name;
Типы индексов
Одностолбцовый индекс
Одностолбцовый индекс — это индекс, созданный только по одному столбцу таблицы. Базовый синтаксис выглядит следующим образом:
CREATE INDEX index_name ON table_name (column_name);
Составной индекс
Составной индекс — это индекс, созданный по нескольким столбцам таблицы. Базовый синтаксис выглядит следующим образом:
CREATE INDEX index_name ON table_name (column1_name, column2_name);
Независимо от того, является ли индекс одностолбцовым или составным, индекс должен быть создан по столбцам, которые очень часто используются в условиях фильтрации предложения WHERE.
Если используется только один столбец, выбирайте одностолбцовый индекс; если используется несколько столбцов, используйте составной индекс.
Уникальный индекс
Использование уникального индекса служит не только для производительности, но и для целостности данных. Уникальный индекс не допускает вставки в таблицу дублирующихся значений. Базовый синтаксис выглядит следующим образом:
CREATE UNIQUE INDEX index_name on table_name (column_name);
Частичный индекс
Частичный индекс — это индекс, построенный на подмножестве таблицы; подмножество определяется условным выражением. Индекс содержит только строки, удовлетворяющие условию. Базовый синтаксис выглядит следующим образом:
CREATE INDEX index_name ON table_name(column_list) WHERE condition;
Здесь index_name — имя индекса, который вы хотите создать, table_name — имя таблицы, содержащей столбцы, для которых вы хотите создать индекс, column_list — список столбцов, которые вы хотите индексировать, а condition — логическое выражение, определяющее, какие строки будут включены в индекс.
Неявный индекс
В PostgreSQL неявный индекс — это индекс, автоматически создаваемый сервером базы данных при создании объекта. Такие индексы обычно автоматически создаются для ограничений первичного ключа и ограничений уникальности. Когда вы объявляете столбец первичным ключом, ограничением уникальности или внешним ключом при создании таблицы, PostgreSQL автоматически создаёт неявный индекс для этого столбца. Преимущество этого в том, что это упрощает управление индексами и повышает производительность базы данных.
Например, если вы объявите столбец с именем "userid" как первичный ключ при создании таблицы с именем "users", PostgreSQL автоматически создаст неявный индекс для столбца "userid". Это означает, что при вставке новых записей база данных автоматически сгенерирует уникальное значение индекса для столбца "userid".
Создание и управление неявными индексами автоматически выполняются PostgreSQL, и пользователям не нужно вмешиваться вручную, что делает управление базой данных более простым и эффективным.
Пример
Следующий пример создаст индекс для столбца SALARY таблицы COMPANY:
# CREATE INDEX salary_index ON COMPANY (salary);
Теперь используйте команду\d company, чтобы вывести список всех индексов таблицы COMPANY:
# \d company
Результаты следующие. company_pkey — это неявный индекс, созданный при создании таблицы:
exampledb=# \d company
Table "public.company"
Column | Type | Collation | Nullable | Default
---------+---------------+-----------+----------+---------
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, чтобы вывести список всех индексов в базе данных:
exampledb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+------------
public | company_pkey | index | postgres | company
public | department_pkey | index | postgres | department
public | salary_index | index | postgres | company
(3 rows)
DROP INDEX (Удаление индекса)
Индекс можно удалить с помощью команды DROP в PostgreSQL.
DROP INDEX index_name;
Вы можете использовать следующую инструкцию, чтобы удалить индекс, созданный ранее:
# DROP INDEX salary_index;
После удаления вы увидите, что salary_index был удалён из списка индексов:
exampledb=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------+-------+----------+------------
public | company_pkey | index | postgres | company
public | department_pkey | index | postgres | department
(2 rows)
Когда следует избегать использования индексов?
Хотя цель индексов — повысить производительность базы данных, есть несколько ситуаций, в которых следует избегать использования индексов.
При использовании индексов следует учитывать следующие рекомендации:
- Индексы не следует использовать в небольших таблицах.
- Индексы не следует использовать в таблицах с частыми крупномасштабными операциями обновления или вставки.
- Индексы не следует использовать для столбцов, содержащих большое количество значений NULL.
- Индексы не следует использовать для столбцов, с которыми часто выполняются операции.