11.1. Введение #
Предположим, что у нас есть такая таблица:
CREATE TABLE test1 ( id integer, content varchar );
и приложение выполняет много подобных запросов:
SELECT content FROM test1 WHERE id = константа
;
Если система не будет заранее подготовлена, ей придётся сканировать всю таблицу test1
, строку за строкой, чтобы найти все подходящие записи. Когда таблица test1
содержит большое количество записей, а этот запрос должен вернуть всего несколько (возможно, одну или ноль), такое сканирование, очевидно, неэффективно. Но если создать в системе индекс по полю id
, она сможет находить строки гораздо быстрее. Возможно, для этого ей понадобится опуститься всего на несколько уровней в дереве поиска.
Подобный подход часто используется в технической литературе: термины и понятия, которые могут представлять интерес, собираются в алфавитном указателе в конце книги. Читатель может просмотреть этот указатель довольно быстро и затем перейти сразу к соответствующей странице, вместо того, чтобы пролистывать всю книгу в поисках нужного материала. Так же, как задача автора предугадать, что именно будут искать в книге читатели, задача программиста баз данных — заранее определить, какие индексы будут полезны.
Создать индекс для столбца id
рассмотренной ранее таблицы можно с помощью следующей команды:
CREATE INDEX test1_id_index ON test1 (id);
Имя индекса test1_id_index
может быть произвольным, главное, чтобы оно позволяло понять, для чего этот индекс.
Для удаления индекса используется команда DROP INDEX
. Добавлять и удалять индексы можно в любое время.
Когда индекс создан, никакие дополнительные действия не требуются: система сама будет обновлять его при изменении данных в таблице и сама будет использовать его в запросах, где, по её мнению, это будет эффективнее, чем сканирование всей таблицы. Вам, возможно, придётся только периодически запускать команду ANALYZE
для обновления статистических данных, на основе которых планировщик запросов принимает решения. В Главе 14 вы можете узнать, как определить, используется ли определённый индекс и при каких условиях планировщик может решить не использовать его.
Индексы могут быть полезны также при выполнении команд UPDATE
и DELETE
с условиями поиска. Кроме того, они могут применяться в поиске с соединением. То есть, индекс, определённый для столбца, участвующего в условии соединения, может значительно ускорить запросы с JOIN.
В целом индексы Postgres Pro можно использовать для оптимизации запросов, содержащих одно или несколько предложений WHERE
или JOIN
в таком виде:
индексированный-столбец
индексируемый-оператор
значение-сравнения
Здесь индексированный-столбец
— это любой столбец или выражение, для которого был определён индекс, индексируемый-оператор
— это оператор, который является членом класса операторов индекса для индексированного столбца (более подробную информацию об этом см. ниже), а значение-сравнения
может быть любым выражением, которое не является изменчивым и не ссылается на таблицу с этим индексом.
В некоторых случаях планировщик запросов может извлечь индексируемое предложение этого вида из другой конструкции SQL. Например, если исходное предложение выглядело так:
значение сравнения
оператор
индексированный столбец
, то его можно преобразовать в индексируемую форму, если исходный оператор
имеет коммутирующий оператор, который является членом класса операторов индекса.
Создание индекса для большой таблицы может занимать много времени. По умолчанию Postgres Pro позволяет параллельно с созданием индекса выполнять чтение (операторы SELECT
) таблицы, но операции записи (INSERT
, UPDATE
и DELETE
) блокируются до окончания построения индекса. Для производственной среды это ограничение часто бывает неприемлемым. Хотя есть возможность разрешить запись параллельно с созданием индексов, при этом нужно учитывать ряд оговорок — они описаны в подразделе Неблокирующее построение индексов.
После создания индекса система должна поддерживать его в состоянии, соответствующем данным таблицы. С этим связаны неизбежные накладные расходы при изменении данных. Наличие индексов также может препятствовать созданию кортежей только в куче. Таким образом, индексы, которые используются в запросах редко или вообще никогда, должны быть удалены.