Основы проектирования реляционных баз данных

         

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


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

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

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

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

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

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


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

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

  • Предложение ORDER BY. Часто это предложение включается, даже если определенный порядок в результирующем множестве не требуется приложением или конечным пользователем.
  • Предикаты предложения WHERE. Часто это предложение содержит избыточное множество предикатов ограничения. Например, предикаты в следующем предложении WHERE являются избыточными, так как DEPT_NO есть первичный ключ и, следовательно, будет уникально идентифицировать только одну строку:

    WHERE DEPT_NO = 10 AND DEPT_NAME = 'PERATIONS'.


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

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


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

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

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

  • Преобразование подзапроса в соединение. Оптимизатор преобразует большинство подзапросов в соединения. Нужно знать, на каких этапах выполнения запроса это преобразование происходит.
  • Когда будут создаваться временные таблицы. Если временные таблицы создаются, это может указывать, что оптимизатор сортирует промежуточные результаты. Если это происходит, можно попробовать добавить индекс на одном из следующих шагов настройки для того, чтобы избежать сортировки.
  • Медленные методы соединения. Хэш-соединение и методы вложенного соединения не являются быстрыми, как метод слияния индексов для больших таблиц. Если эти методы используются, можно попробовать добавить индекс в шагах 5 и 6 настройки команды SELECT, так, чтобы соединения использовали метод слияния индексов. Иногда хэш-соединение может представлять более лучший метод соединения, когда большое количество данных обрабатывается.


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

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

Примеры:

  • Если запрос содержит предложение ORDER BY, закомментируйте его и посмотрите, изменится ли план этого запроса. Если план изменился, выполните запрос для того, чтобы определить, увеличилась ли производительность.
  • Если запрос содержит несколько соединений, локализуйте то, которое замедляет выполнение. Комментируйте последовательно все соединения, кроме одного, и выполняйте запрос. Определите, какое соединение самое критичное.



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