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

Статистика индексов

Статистика, связанная с индексами таблицы, также поддерживается в двух отдельных таблицах системного каталога. Все колонки (за исключением одной - HEIGTH - в таблице SYSINDEXES ) поддерживаются статически и заполняются, когда выполняется команда UPDATE STАTISTICS или создается индекс.

В таблице SYSADM.SYSINDEXES статистическая информация поддерживается в следующих колонках:

В таблице SYSADM.SYSKEYS поддерживается следующая статистическая информация.

Простейший пример есть колонка Пол (SEX_CODE), которая имеет кардинальность 2. Рассмотрим следующий индекс:

CREATE INDEX XNKSALS ON EMPLOYEE
(DEPT, SALARY, YEARS_SERVICE);

Таблица EMPLOYEE содержит 250 строк, по одному для каждого служащего компании. Кардинальность множества значений колонок есть:

При этих заданных условиях строки таблицы, которые описывают эти индексируемые колонки, имеют следующие значения DISTICTCOUNT:

Фактор селективности

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

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

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

Численно фактор селективности представляет вероятность, которая изменяется от 0 до 1. Умножение числа строк в таблице на фактор селективности для связанного с ним предиката будет давать ожидаемое число строк для операции выборки, при предположении, что значения колонок таблицы равномерно распределены по строкам.

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

Определение фактора селективности для одного предиката, такого как EMPLOYEE_NO=65, зависит от того, какой оператор используется в предикате. Оператор влияет на фактор селективности, так как он определяет взаимосвязь между строками, которые удовлетворяют предикату и другим операндам.

Чем большее число строк отбрасывает оператор предиката при выборке (ограничивает), тем меньше фактор селективности предиката. Наиболее трудоемким для выполнения оператором (по числу операций ввода-вывода) является оператор равенства, поскольку только одно значение колонки может удовлетворить предикату. В этом случае фактор селективности просто обратно пропорционален кардинальности колонки, которая сохраняется в колонке DISTINCTCOUNT таблицы SYSADM.SYSINDEXES для этой колонки (как индексируемого ключа).

Однако непросто вычислить фактор селективности для операторов неравенства. Рассмотрим следующие два предиката:

ORDER_DATE > JAN-01-1900
ORDER_DATE > SYSDATE -1 DAY

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

Как оптимизатор может определить это? Оказывается, что оптимизатор вычисляет фактор селективности посредством доступа к верхнему уровню индекса той индексной структуры, которая содержит колонку запроса ( ORDER_DATE ). Затем он оценивает число строк, которое удовлетворяет предикату, экстраполируя пропорцию индексных ключей на этом уровне индекса. Этот метод, называемый сканирование B-дерева (B-tree scan), позволяет оптимизатору сделать достаточно точную оценку результатов применения предикатов неравенства.

Другой фактор, который играет роль в определении фактора селективности одного предиката неравенства, есть ответ на вопрос, будет ли сравниваться колонка с константой или связанной переменной? Это некритично для предиката равенства потому, что оптимизатор может вычислить фактор селективности исходя из значения DISTINCTCOUNT в предположении равновероятного распределения значений колонки по строкам таблицы. Такое предположение позволяет оптимизатору быть нечувствительным к действительным значениям операнда с другой стороны знака равенства. Однако для использования метода сканирования В-дерева оптимизатор должен быть способным найти значение другого операнда. Когда операнд является связанной переменной, оптимизатор должен опуститься назад для проверки предположения, что в действительности трудно закодировать.

Нижеследующая таблица 16.1 суммирует определения фактора селективности для одного предиката, в зависимости от операции и типа второго операнда.

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

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

Пример. Пусть задан предикат

EMPLOYEE_NO = 45 AND
DEPT = 50 AND
SALARY > 25000.

Пример. Пусть задан предикат

SALARY > 25000 AND
DEPT = 50 AND
YEARS_SERVICE > 3.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример

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

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

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

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

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

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

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

Анализ запросов с целью повышения скорости их выполнения

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

Шаг 1. Обновить статистику. До того как добавить индексы, необходимо убедиться, что статистика базы данных в системном каталоге является корректной. Если вы выполняете запрос без учета действительной производительности базы данных, вам следовало бы обновить статистику для всех таблиц, указанных в предложении FROM, используя команду UPDATE STSTISTICS (для SQLBаse) или другую специальную команду СУБД. С другой стороны, если вы используете небольшую тестовую базу данных, то можно вручную вычислить необходимые статистические показатели и внести их в системный каталог.

Когда вы обновляете статистику, вам следовало бы скомпилировать команду SQL, установив параметр PLANONLY в положение ON. Сравните новый план запроса со старым до обновления статистики, для того чтобы определить изменения в нем (иногда требуется довольно длительное время для построения плана). Сравнивая планы, можно избежать повторного выполнения запроса только для того, чтобы убедиться, что производительность его выполнения идентична предыдущему выполнению этого запроса. Если статистика изменилась, то выполните запрос, чтобы определить, увеличилась ли производительность и насколько.

Шаг 2. Упростить команду SELECT. Перед добавлением индексов или переписыванием плана выполнения следует попытаться упростить запрос. Задача состоит в том, чтобы сделать выражение SELECT как можно проще, сократив по мере возможности число переменных в нем. Упростив запрос, скомпилируйте команду, чтобы посмотреть план запроса. Сравните новый план запроса со старым. Определите, увеличилась ли производительность запроса, выполнив его.

Для того чтобы упростить SELECT, необходимо:

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

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

WHERE DEPT_NO = 10 AND DEPT_NAME = 'PERATIONS'.

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

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

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

При использовании связанных переменных в предикате селекции оптимизатор запросов вынужден использовать умалчиваемый фактор селективности 1/3.

Рассмотрим следующий пример. Предикаты: Amount > :BindVar и Amount > 1000. Преимущество использования первого предиката состоит в том, что команда может быть откомпилирована один раз и затем много раз использована с различными значениями. Недостаток состоит в том, что оптимизатор запросов имеет меньше информации для его оценки во время компиляции. Он не знает, будет ли 10 или 10000000 стоять вместо связанной переменной. Следовательно, он неспособен корректно вычислить фактор селективности. В этой ситуации будет установлено значение по умолчанию, которое не обязательно будет отражать истинную ситуацию в данных. Поскольку значение фактора селективности, равное 1/3, относительно высокое (при отсутствии других предикатов), то оптимизатор не может использовать какой-либо индекс, связанный с колонкой в этом предикате.

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

Связанные переменные также приводят к проблеме при использовании предиката оператора LIKE. Этот оператор может включать символ подстановки.

Пример. Рассмотрим запрос на поиск всех продавцов, 
  чье имя начинается с буквы "А": 

SELECT * FROM CUSTOMER WHERE NAME LIKE 'A%';

Символ подстановки может стоять и в начале, и в середине, и в конце строки шаблона. Природа индексной структуры на основе В-дерева такова, что она может работать с символом подстановки, если он не стоит в начальной позиции строки. Ясно, что оптимизатор не будет использовать индекс, если символ постановки будет стоять на первой позиции (как при использовании связанной переменной в предикате LIKE ). В этих случаях будет применено сканирование таблицы.

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

Шаг 3. Пересмотреть план запроса. Выполните запрос так, чтобы посмотреть его план. Вы должны хорошо понимать план запроса, чтобы использовать его. Несколько элементов этого плана требуют особого внимания.

Шаг 4. Локализовать узкие места. Запрос, который выполняется медленно, может содержать много предложений и предикатов. Если это так, нужно определить, какие предложения или предикаты приводят к плохой производительности. Если удалить одно или два предложения или предиката, производительность выполнения запроса возрастает значительно. Эти предложения являются критическими параметрами выполнения запроса.

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

Исключите любые АТ-функции (@), выполняющиеся в WHERE, и посмотрите, не выросла ли производительность. Может быть, индекс не используется из-за применения функции. В этом случае можно построить индекс с использованием этой функции.

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

Локализуйте следующие колонки таблицы из запроса, которые не имеют индекса:

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

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

Шаг 6. Создать индексы для нескольких колонок. Процедура идентификации колонок для создания составных индексов состоит в следующем:

Типы составных индексов приведены ниже.

Шаг 7. Удалить все индексы, которые не используются в плане запроса. Как уже указывалось выше, индексы замедляют выполнение команд DML, а их сопровождение требует времени и увеличивает стоимость обработки. Следовательно, вам следует проследить использование всех созданных индексов и удалить те, которые не используются запросом.

Литература: [7], [23], [38], [47].