Опубликован: 02.08.2007 | Доступ: свободный | Студентов: 3887 / 751 | Оценка: 4.55 / 4.39 | Длительность: 27:09:00
ISBN: 978-5-9556-0111-3
Лекция 16:

Оптимизация обработки запросов

< Лекция 15 || Лекция 16: 123456

Фактор селективности в случае нескольких предикатов

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

  • Когда для первой по порядку колонки индексного ключа задается предикат равенства, оптимизатор может найти фактор селективности для нескольких колонок, считывая DISTINCTCOUNT до самого последнего ключа строки SYSKEYS. Когда первый предикат неравенства оценивается, то игнорируются все оставшиеся предикаты, содержащие колонки, которые появляются в индексе.
    Пример. Пусть задан предикат
    
    EMPLOYEE_NO = 45 AND
    DEPT = 50 AND
    SALARY > 25000.

    Предположим, что индекс определен для составного ключа, содержащего указанные колонки в следующем порядке: EMPLOYEE_NO, DEPT, SALARY. Комбинация факторов селективности для колонки EMPLOYEE_NOи для колонки DEPT, которые появляются в колонке DISTINCTCOUNT строки DEPT таблицы системного каталога SYSKEYS, используется оптимизатором для оценки числа строк, которые будут возвращены по запросу. Действие предиката неравенства для колонки SALARY игнорируется.

  • Когда для первой по порядку колонки индексного ключа задается предикат неравенства, только фактор селективности этого предиката вычисляется, а все оставшиеся предикаты игнорируются.
    Пример. Пусть задан предикат
    
    SALARY > 25000 AND
    DEPT = 50 AND
    YEARS_SERVICE > 3.

    Предположим, что индекс определен для составного ключа, содержащего указанные колонки в следующем порядке SALARY, DEPT, YEARS_SERVICE. Оптимизатор определяет фактор селективности, просматривая корневую страницу индекса для определения числа строк таблицы, для которых выполняется предикат "Зарплата больше 25000". Это значение и становится фактором селективности для всей группы предикатов. Оставшиеся предикаты игнорируются.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример

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

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

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

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

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

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 можно прочитать в рекомендованной к лекции литературе.

< Лекция 15 || Лекция 16: 123456
Александра Каева
Александра Каева
Михаил Забелкин
Михаил Забелкин