Индексы
Индексом называется объект базы данных, позволяющий значительно повысить скорость обращения к базе за счет ускоренной обработки команд, содержащих сравнительные критерии. Хранимая в индексах информация о размещении данных по одному или нескольким полям таблицы повышает эффективность поиска записей при условной выборке (например, с использованием секции 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
Установка некоторых типов ограничений (прежде всего, ограничения PRIMARY KEY и UNIQUE) также приводит к автоматическому построению индекса. В листинге 7.2 при создании таблицы authors для поля id устанавливается ограничение первичного ключа (PRIMARY KEY). В результате автоматически строится индекс authors_pkey.
Листинг 7.2. Автоматическое построение индекса
booktown=# CREATE TABLE authors (id integer PRIMARY KEY,
booktown(# last_name text,
booktown(# first_name text);
NOTICE: CREATE TABtE/PRIMARY KEY will create implicit index 'authors_pkey' for
table 'authors'
CREATE
example=# \d authors
Table "authors"
Attribute | Type Modifier
id integer | not null
lastjiame | text
firstjiame text |
Index: authors_pkey
Индекс, построенный командой из листинга 7.2, позволяет PostgreSQL быстро проверять уникальность первичного ключа для всех новых записей, заносимых в таблицу. Кроме того, индекс повышает скорость выполнения запросов, у которых поле i d указано в условии поиска.
Уникальные индексы
Создание индекса с ключевым словом UNIQUE говорит о том, что индекс является уникальным, то есть индексируемое поле (или поля) не может содержать повторяющихся значений. Фактически создание уникального индекса эквивалентно созданию таблицы с ограничением уникальности (см. ниже подраздел «Ограничения в таблицах» в разделе «Нетривиальное использование таблиц»).
В листинге 7.3 для поля name таблицы publ ishers создается уникальный индекс unique_publ IsheMdx. Это означает, что в таблице не могут присутствовать два издательства с одинаковыми названиями.
Листинг 7.3. Создание уникального индекса
booktown-* CREATE UNIQUE INDEX unique_publisherjdx
booktown-# ON publishers (name):
CREATE
booktown=# \d publishers
Table "publishers" Attribute | Type | Modifier
id integer | not null
name | text
address j text j Indices: publishers_pkey.
urn que_publ i sher_i dx
Поскольку псевдозначение NULL формально не совпадает ни с одним реальным значением, в поле с уникальным индексом допускаются многократные вхождения NULL. В этом и заключается главное практическое различие между уникальным индексом и индексом, автоматически создаваемым при установке ограничения PRIMARY KEY, которое вообще запрещает присутствие значений NULL.
ВНИМАНИЕ
Ключевое слово UNIQUE в сочетании с секцией USING может использоваться только для индексов, реализованных в виде В-дерева.
Типы индексов
Необязательная секция USING задает реализуемый тип индекса. В PostgreSQL 7.1.x поддерживаются три типа индексов:
- В-дерево;
- R-дерево;
- кэш.
В первом варианте с высокой степенью параллельности используются алгоритмы В-деревьев Лемана-Яо (Lehman-Yao). Это самый распространенный способ индексации, обладающий наибольшими возможностями. По этой причине он используется по умолчанию.
Реализация R-дерева, основанная на квадратичном разбиении по алгоритму Гуттмана (Guttman), применяется главным образом при операциях с геометрическими типами данных. Реализация хэша основана на алгоритмах линейного хэширования Литвина (Litwin), которые традиционно используются для индексов с частой проверкой равенства (то есть ориентированы на оператор =).
На момент написания книги в PostgreSQL версии 7.1.x реализация индексов на основе В-дерева значительно превосходила остальные типы но универсальности и широте возможностей. В-дерево рекомендуется использовать вместо хэша даже при прямых сравнениях оператором =. Хэш поддерживается в первую очередь по соображениям совместимости, хотя ничто не мешает вам выбрать эту реализацию, если вы твердо уверены в выигрыше от перехода на нее от В-дерева.
Как сказано выше, реализацию на основе R-дерева рекомендуется использовать для индексации геометрических типов данных, но при этом необходимо помнить о специфике этого типа. Например, для R-дерева нельзя построить уникальный индекс или провести индексацию по нескольким полям. В таких случаях лучше положиться на реализацию В-дерева, обладающую более широкими возможностями.
Тип индекса задается в секции USING при помощи ключевых слов BTREE, RTREE и HASH. По умолчанию используется тип BTREE.
В листинге 7.4 создается таблица с именем polygons, предназначенная для хранения геометрических данных типа polygon. Затем для поля shape создается индекс spaci a I _1 ndex типа RTREE.
Листинг 7.4. Выбор типа индекса
booktown=# CREATE TABLE polygons (shape polygon):
CREATE
booktown=f CREATE INDEX spadaljdx ON polygons USING RTREE (shape);
CREATE
ВНИМАНИЕ
Если у вас нет твердой, обоснованной уверенности в том, что для конкретной ситуации лучше подойдет другой тип индекса, мы рекомендуем использовать стандартный тип BTREE.
Функциональные индексы
В слегка измененном виде команда CREATE INDEX позволяет индексировать данные не по значениям поля, а по некоторой функции этих значений. Такая форма индекса называется функциональной.
Команда создания функционального индекса имеет следующий синтаксис:
CREATE [ UNIQUE ] INDEX индекс ON таблица
[ USING тип ] ( функция ( поле [. ...])[ класс ] )
Единственное отличие этой команды от описанной выше заключается в том, что индекс строится по результатам применения функции к каждому значению поля. Остальные элементы те же.
Функциональные индексы часто строятся для полей, значения которых проходят предварительную обработку перед сравнением в команде SQL. Например, при сравнении строковых данных без учета регистра символов часто используется функция иррегО. Создание функционального индекса с функцией иррегО улучшает эффективность таких сравнений.
В листинге 7.5 приведен пример построения индекса upper_title для таблицы books. Данные индексируются по результатам применения функции upper () к полю title. Затем выполняется запрос SQL, который благодаря наличию функционального индекса выполняется более эффективно.
Листинг 7.5. Построение функционального индекса
booktown=# CREATE INDEX upperjtitlejdx ON books
booktown-f (upper(title));
CREATE
booktown=# SELECT title FROM books WHERE upper(title) = 'DUNE';
title
Dune
(1 row)
Удаление индекса
Для удаления индексов из таблицы используется команда DROP INDEX. Синтаксис команды DROP INDEX: DROP INDEX индекс [. ...]
В качестве параметра команде передается имя удаляемого индекса. Допускается одновременное удаление нескольких индексов, перечисленных через запятую. В листинге 7.6 удаляется индекс upper_title_idx, созданный в листинге 7.5.
Листинг 7.6. Удаление индекса
booktown=# DROP INDEX upper_title_idx:
DROP
