Раздел 6. Индексы

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

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

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

В системе Oracle существуют различные виды индексов, к наиболее распространенными по употреблению и универсальности являются B-индексы. При создании индекса указываются индексируемая таблица и группа полей, по которым строится индекс. Если индекс построен по группе полей таблицы, он называется составным.

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

Для создания индекса используется команда CREATE INDEX:

CREATE INDEX <имя индекса> ON <имя таблицы>

( <список полей таблицы или выражений> )

Приведем несколько примеров команд по созданию индексов; таблицами для индексирования являются таблицы из примера раздела 2.4.

CREATE INDEX "Клиент_ДР"

ON "Клиент" ("Дата Рождения")

CREATE INDEX "Код заказа_СКК"

ON "СоставЗак"

( "Код заказа", "Код товара", "Количество" )

Здесь индекс "Клиент_ДР" является простым, а "Код заказа_СКК" – составным, поскольку создается по трем полям. Отметим, что составной индекс совершенно не эквивалентен группе простых индексов, созданных по каждому полю. Кроме того, при создании составных индексов важен порядок перечисления полей; индексы, построенные по одному набору полей, но в различных сочетаниях, являются различными.

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

Следует иметь в виду, что для полей и групп полей, на которые наложены ограничения PRIMARY KEY и UINQUE, индексы создаются автоматически, и, следовательно, для них создавать индексы явным образом не следует. Более того, запрещается создавать индексы по одинаковым группам полей (с учетом порядка полей в списке). Имя индекса совпадает с именем ограничения, что является еще одним аргументом присваивать ограничениям явные явным образом.

Проверить, какие индексы существуют для некоторой таблицы, можно при помощи следующего запроса, обращающегося к представлений системного каталога ALL_INDEXES и USER_INDEXES:

SELECT INDEX_NAME, UNIQUENESS

FROM user_indexes

WHERE TABLE_NAME = 'Клиент'

INDEX_NAME	UNIQUENESS
Клиент_ДР	NONUNIQUE
Клиент_Ф_С	UNIQUE
SYS_C009987	UNIQUE

SELECT INDEX_NAME, UNIQUENESS

FROM user_indexes

WHERE TABLE_NAME = 'СоставЗак'

INDEX_NAME	UNIQUENESS
Код заказа_СКК	NONUNIQUE
СоставЗак_КК_U	UNIQUE

Здесь имя SYS_C009987 соответствует индексу, автоматически созданному по полю "Код Клиента", поскольку на это поле было наложено ограничение уникальности; это имя было сгенерировано системой автоматически. Поле UNIQUENESS обозначает, является ли индекс уникальными или нет; это определяется наличием ограничения уникальности на поля. Уникальные индексы более эффективны.

Проиллюстрирует ожидаемый рост производительности системы при выполнении следующего запроса:

SELECT * FROM “Клиент”

WHERE "Дом Телефон" = ‘4953453214’

Если таблицы "Дом Телефон" содержит 1000000 записей, то поиск записей, соответствующих этому условию селекции, потребует просмотр всех записей таблицы. Этот способ вычисления запроса называется полным перебором (FULL ACCESS), априорная оценка стоимости вычисления данного запроса будет равной 1000000 в общем случае, или 1000000/2 = 500000 в случае, если на поле "Дом Телефон" наложено ограничений уникальности.

Если по этому полю создан индекс (и он действительно применяется системой при вычислении запроса), то вместо просмотра записей таблицы происходит сканирование индекса от вершины к листу, в котором должно находиться значение ‘4953453214’, если оно действительно существует в индексе. Если такой узел в индексе найдет, по адресу записи в этом узле происходит обращение непосредственно к записи в таблице. Учитывая структуру индекса, скорость поиска в индексе очень высокая (логарифмическая), и априорная оценка для запроса будет пропорциональна ln 1000000 ~~ 14. Скорость в индексе будет так же выше при ограничении уникальности.

Впрочем, подобные априорные оценки лишь частично отражают реальный процесс вычисления запросов.

Способ вычисления, состоящий в сканировании индекса, называется RANGE SCAN и UNIQUE SCAN для неуникального и уникального индексов соответственно.

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

Следует иметь в виду, что индексы можно строить не только по полям таблицы, но по производным от них выражениям и функциям. В функциях можно обращаться, в том числе, к длинным полям (LOB); строить индексы непосредственно по полям длинных типов нельзя. Например:

CREATE INDEX "Товар_Ф"

ON "Товар"( LENGTH("Фото" ))

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

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

В частности, одним из важных факторов, характеризующих применяемость индекса, является его селективность, которую можно определить как отношение записей, удовлетворяющих условию селекции, к общему количеству записей в таблице. Например, если поиск выполняется по значению уникального поля, построенный по этому полю индекс будет обладать самой высокой селективность, а селективность поиска по полю, например, "Пол" таблицы “Клиент”, будет в среднем 50%, и такой индекс, очевидно, бесполезен.

Для проверки, каким образом вычисляется SELECT-запрос и какие индексы используются, а какие – нет, следует использовать средства Oracle для просмотра плана выполнения запросов. Этот план описывает, в том числе, какие действия выполняются при вычислении конкретного запроса, используется ли полный перебор, доступ при помощи индекса и т.д. Существуют средства, визуализирующие план исполнения средствами графического интерфейса, а так же базовое средство, встроенное в Oracle SQL – команда EXPLAIN PLAN. Ниже приведена стандартный сценарий ее использования (за детальным описанием этой команды и интерпретаций ее результатов следует обращаться к документации к системе Oracle и специализированной литературе).