Улучшение производительности работы индекса

Операторы CREATE INDEX и DROP INDEX.

Большинство диалектов поддерживает как минимум следующий оператор:

CREATE [UNIQUE] [ASQ[ENDING] | DESC[ENDING]] INDEX ИмяИндекса

ON ИмяТаблицы (столбец 1 [,столбец2...]);

· UNIQUE – требует создания уникального индекса, не допускающего одинаковых значений индексных полей для разных записей таблицы;

· ASC[ENDING] – указывает на необходимость сортировки значений индексных полей по возрастанию (режим принят по умолчанию);

· DESC[ENDING] – указывает на необходимость сортировки значений индексных полей по убыванию;

· ИмяИндекса - имя создаваемого индекса;

· ИмяТаблицы - имя таблицы, для которой создается индекс;

· столбецМ - имена столбцов, по которым создается индекс.

Указанные в операторе столбцы составляют ключ индекса и должны быть перечислены в возрастающем или убывающем порядке. Индексы могут создаваться только для таблиц базы данных, но не для представлений. Если в операторе указано ключевое слово UNIQUE, уникальность значений ключа индекса будет автоматически поддерживаться системой. Требование уникальности значений обязательно для первичных ключей, а также, возможно, и для других столбцов таблицы (например, для альтернативных ключей). Создание индексов возможно в любой момент, даже для уже заполненной данными таблицы, могут возникнуть проблемы, связанные с дублированием данных в различных строках. Однако, имеет смысл создавать уникальные индексы (по крайней мере, для первичного ключа) непосредственно при создании таблицы. В результате система сразу же возьмет на себя контроль над уникальностью значений данных в соответствующих столбцах. Для каждого из ключевых столбцов может быть указан порядок следования значений – по возрастанию (ASC) или по убыванию (DESC), причем значение ASC используется по умолчанию.

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

DROP INDEX <имя индекса>;

Нельзя удалить индекс, созданный в результате определения первичного и внешнего ключей, а также уникальности значений столбца или группы столбцов (PRIMARY KEY, FOREIGN KEY, UNIQUE). Для этой цели следует применять оператор ALTER TABLE. Нельзя также удалить используемый индекс, что может иметь место при выполнении другими пользователями запросов к базе данных. Кроме этого, для удаления индекса нужно иметь соответствующие привилегии доступа к базе данных.

После многократного внесения изменений в таблицу базы данных индексы этой таблицы могут быть разбалансированы. Разбалансировка приводит к тому, что "глубина" индекса (depth) возрастает сверх критического значения (2). "Глубина" индекса - параметр, показывающий максимальное число операций, необходимых для нахождения искомого значения в таблице базы данных с использованием данного индекса. В случае разбалансировки индекса его ценность при выполнении запросов снижается из-за увеличения времени выполнения запроса. Поэтому время от времени необходимо производить одно из перечисленных ниже действий:

· выполнять перестройку индекса оператором ALTER INDEX;

· пересчитать показатель "выбираемости" индекса оператором SET STATISTICS;

· уничтожить индекс оператором DROP INDEX и заново создать его оператором CREATE INDEX.

Перестройка индекса. Перестройка индекса заключается в пересоздании и балансировке индекса, что наступает после деактивизации индекса оператором

ALTER INDEX <имя индекса> INACTIVATE;

и последующей его активизации оператором

ALTER INDEX <имя индекса> ACTIVATE;

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

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

· нельзя перестроить индекс, созданный в результате определения первичного и внешнего ключей, а также уникальности значений столбца или группы столбцов (PRIMARY KEY, FOREIGN KEY, UNIQUE). Для этой цели следует применять оператор ALTER TABLE;

· для выполнения оператора ALTER INDEX нужно иметь соответствующие привилегии доступа к БД.

Повторное вычисление показателя "полезности" индекса. Показатель "полезности" индекса основан на сведениях о числе повторяющихся строк индекса. Он используется InterBase при доступе к таблице для выработки оптимального плана удовлетворения запроса. Пересчет показателя "полезности" производится оператором

SET STATISTICS INDEX <имя индекса>;

КОНТРОЛЬНЫЕ ВОПРОСЫ:

1. Что такое индекс в СУБД?

2. В каких случаях необходимо строить индексы?

3. В каких случаях не рекомендуется строить индексы?

4. При помощи какого оператора создаются индексы?

5. Какой оператор используется для удаления индекса?

6. В каких случаях нельзя удалить индекс?

7. В чем заключается перестройка индекса?

8. На чем основан показатель «полезности» индекса? При помощи какого оператора производится его пересчет?

Дата добавления: 2014-01-07