Использование оптимизатора для оптимизации выполнения запросов

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

Существует много вариантов увеличения производительности запросов. Основной идеей для выбора самого подходящего является построение запроса таким образом, чтобы оптимизатор запросов СУБД вычислил оптимальные по стоимости пути доступа для его выполнения.

Так, например, при работе с оптимизатором СУБД SQLBase для увеличения производительности конкретной команды SELECT, проектировщик базы данных или администратор баз данных выполняет следующие действия:

Обновление статистики.
Определение оптимального набора множества индексов.
Переписывание плана выполнения запроса, выбранного оптимизатором.

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

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

Общая стратегия индексирования состоит в создании индексов для всех первичных и внешних ключей. Это так, потому что в большинстве систем существуют колонки, которые извлекаются гораздо чаще в предикатах предложений WHERE, GROUP BY, ORDER BY.
Этот первоначальный набор индексов базы данных обеспечивает индексацию для выполнения селекции и исключений сортировок первичных и внешних ключей. Следовательно, часто соединения являются наиболее критическим временным аспектом конкретного запроса. Самый быстрый алгоритм соединения для больших таблиц есть объединение индексов. Первоначальный набор индексов гарантирует, что путь доступа с объединением индексов доступен для оптимизатора запросов, когда колонка соединения является первичным или внешним ключом. Для всех других соединений оптимизатор ограничивается более медленными алгоритмами соединения, хэш-соединением или одним из алгоритмов соединения в цикле. Однако для большинства утверждений SELECT пути доступа, допустимые для оптимизатора на первоначальном наборе индексов, обеспечивают адекватную производительность.

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

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

Следовательно, общая цель проектирования состоит в том, чтобы создать набор индексов, который отвечает требованиям производительности для всех транзакций к базе данных. Это и есть оптимальный набор индексов.

Переписывание плана выполнения запроса, выбранного оптимизатором. В СУБД SQLBase такая процедура выполняется с помощью настройки команды SELECT. Она может быть применена в при использовании других реляционных СУБД.

Однако в некоторых СУБД, например СУБД Oracle, проектировщик базы данных может явно влиять на выбор тех или иных вариантов улучшения скорости выполнения запросов.

На уровне экземпляра режим работы оптимизатора задается администратором базы данных. На уровне сессии используется команда вида

ALTER SESSION SET OPTIMIZER_GOAL = <режим>;

В качестве режима может быть задано одно из следующих значений:

CHOOSE - это значение задает использование оптимизации, основанной на вычислении стоимости, в противном случае будет использоваться оптимизация, основанная на правилах;

RULE - это значение задает использование оптимизации, основанной на правилах. Такой режим оптимизации будет применен и при использовании подсказок (см. таблицу 16.2); Таблица 16.2. ПодсказкаОписание

ROWID	Использование идентификатора
CLUSTER	Сканирование ключа кластера
HASH	Сканирование хэш-индекса
INDEX	Сканирование индекса
INDEX_ASC	Сканирование индекса в порядке возрастания
INDEX_DECS	Сканирование индекса в порядке убывания
AND_FFS	Быстрое полное сканирование индекса
AND_EQUAL	Использование нескольких индексов со слиянием результатов
FULL	Полное сканирование таблицы

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

Более подробно об использовании оптимизатора запросов СУБД Oracle можно прочитать в рекомендованной к лекции литературе.

Для управления оптимизатором СУБД Oracle используются специальные подсказки, которые записываются в SQL-командах. Такие подсказки влияют на выбор конкретного способа обращения к данным. Пример ниже содержит подсказку оптимизатора для использования индекса (в предположении, что таблица имеет один индекс):

Пример

SELECT /* + index */ EMPLOYEE_NO, DEPT, SALARY FROM EMPLOYEE WHERE EMPLOYEE_NO = 65;

Подсказка является частью комментария, следующего за ключевым словом команды, и отмечается символом "+".

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

При необходимости доступа к значительной части строк какой-либо таблицы полное сканирование таблицы предпочтительнее, чем использование индексов. Это связано с дополнительным чтением записей индекса. Индексы, как правило, эффективны, если запрос захватывает до 10% строк таблицы. Для небольших таблиц использование индексов практически неэффективно.
На различных этапах выполнения запросов следует максимально использовать результаты предыдущих этапов, чтобы не переписывать лишний раз результирующий набор как результат выполнения команды. Например, если результирующий набор данных требуется отсортировать по значениям некоторой колонки, то при выполнении соединения таблиц можно указать способ выполнения этой операции, при котором будет проведена сортировка этих значений. Полученные результаты будут использованы при окончательной сортировке.
При использовании различных видов подзапросов на основе знаний о данных следует учитывать особенности выполнения предикатов и операторов теоретико-множественных операций.

Одним из важных вопросов при работе с оптимизатором запросов является выбор режима его работы (если такая возможность предоставляется СУБД). Так, для оптимизатора СУБД Oracle режим оптимизации может быть задан на уровне экземпляра базы данных, на уровне сессии или SQL-команды.

Содержание раздела