Индексы
В данной главе рассматриваются нетривиальные возможности PostgreSQL, в том числе оптимизация доступа к таблицам с использованием индексов, наследование и установка ограничений для таблиц, практическое применение массивов в значениях полей, работа с транзакциями и курсорами. Все перечисленные возможности выгодно отличают PostgreSQL от других реляционных СУБД.
Кроме того, в этой главе описаны такие концепции из области программирования, как триггеры и последовательности. Наконец, для программистов, занимающихся разработкой специализированных процедур для работы с базой данных, описано, как расширить возможности PostgreSQL путем определения пользовательских функций и операторов.
Индексом называется объект базы данных, позволяющий значительно повысить скорость обращения к базе за счет ускоренной обработки команд, содержащих сравнительные критерии. Хранимая в индексах информация о размещении данных по одному или нескольким полям таблицы повышает эффективность поиска записей при условной выборке (например, с использованием секции WHERE).
Существует несколько разновидностей внутреннего устройства индексов. В этом разделе описаны разные типы индексов, а также объясняется, в каких ситуациях следует использовать тот или иной тип.
Индексы ускоряют доступ к базе, но они также сопряжены с дополнительными затратами ресурсов. При изменении данных в поле индекс приходится обновлять, поэтому поддержание редко используемых индексов отрицательно сказывается на быстродействии системы (затраты времени на поддержание индексов превышают экономию от их использования). Как правило, индексы определяются только для полей, часто указываемых в условиях поиска.
Создание индекса
Индексы создаются командой SQL CREATE INDEX. Синтаксис команды:
CREATE [ UNIQUE ] INDEX индекс ON таблица [ USING тип ] (поле [ класс ] [….])
Здесь индекс – имя создаваемого индекса, таблица – имя таблицы, для которой строится индекс, а поле – имя индексируемого поля. Необязательный параметр тип позволяет выбрать нужную реализацию индекса, а параметр класс описывает операторный класс, используемый для сортировки входных данных.
Внимание
В PostgreSQL операторные классы хранятся в поле pg_opclass. Используйте этот параметр лишь в том случае, если вы досконально разбираетесь во всех тонкостях операторных классов PostgreSQL.
Команда может содержать список из нескольких индексируемых полей, разделенных запятыми; в этом случае индекс строится для всех перечисленных полей. Составные индексы используются в PostgreSQL только при выполнении команд SQL, осуществляющих поиск по всем индексированным полям с объединением условий ключевым словом AND. В стандартной установке PostgreSQL составной индекс содержит не более 16 полей и реализуется только в виде В-дерева.
Перед построением индекса следует решить, какие поля чаще всего требуются при поиске. Например, хотя таблица books проиндексирована по полю id (первичный ключ), поле title также часто проверяется в условиях WHERE. Включение вторичного индекса по полю title заметно ускорит работу команд SQL, в которых значение этого поля сравнивается с некоторой величиной.
В листинге 7.1 приведен пример построения индекса и просмотра таблицы books при помощи управляющей команды \d psql. Помимо типов полей команда также выводит имена индексов таблицы.
Листинг 7.1. Построение индекса.
booktown=# CREATE INDEX books_title_idx booktown-# ON books (title); CREATE booktown=# \d books Table "books" Attribute | Type | Modifier id | integer | not null title | text | not null authorjd |integer | subjectjd I integer Indices: books_id_pkey .books_titlejdx