Введение в оптимизацию запросов Языки обработки данных и задача оптимизации обработки данных
В настоящее время различают два основных типа языков манипулирования данными : процедурные и декларативные. • Процедурные языки обработки данных требуют от программиста кодирования программной логики, необходимой для навигации по физической структуре данных для идентификации и доступа к требуемым данным. Процедурные языки обработки данных обычно являются контекстно зависимыми в реализации. Следовательно, прикладные программы становятся полностью привязанными к конкретной системе БД, для которой они и были разработаны.
Декларативные языки обработки данных • Декларативные языки обработки данных только специфицируют, какие данные необходимы прикладной программе, оставляя за СУБД привилегию определять, как осуществлять навигацию по физической структуре данных для доступа к требуемым данным. SQL есть пример декларативного языка обработки данных.
Концепция независимости прикладных программ от физической структуры данных дает несколько значительных преимуществ: • Изменение в структурах данных незначительно влияет на существующие прикладные программы. Например, если существующий индекс становится устаревшим, то его можно свободно удалить и создать новый индекс (в том числе и на других атрибутах) без влияния на существующие программы. • Уменьшается сложность прикладной программы. СУБД, а не программист, определяет, как осуществлять навигацию по физической структуре данных. • Снижается число ошибок в прикладных программах.
Оптимизация запросов • Компонента SQL СУБД, которая определяет, как осуществлять навигацию по физическим структурам данных для доступа к требуемым данным, называется оптимизатором запросов (query optimizer). • Навигационная логика (вариант алгоритма) для доступа к требуемым данным называется путем или методом доступа (access path). • Последовательность выполняемых оптимизатором действий, которые обеспечивают выбранные пути доступа, называется планом выполнения (execution plan). • Процесс, используемый оптимизатором запросов для определения пути доступа, называется оптимизацией запросов (query optimization).
• Во время процесса оптимизации запросов определяются пути доступа для всех типов команд SQL DML. Однако команда SQL SELECT представляет наибольшую сложность в решении задачи выбора пути доступа. Поэтому этот процесс обычно называют оптимизацией запроса, а не оптимизацией путей доступа к данным. • Следует отметить, что термин оптимизация запросов является не совсем точным в том смысле, что нет гарантии, что в процессе оптимизации запроса будет действительно получен оптимальный путь доступа. Оптимизация запросов может быть определена как сумма всех технических приемов, которые применяются для повышения эффективности обработки запросов.
Синтаксическая оптимизация Первый успех в оптимизации запросов состоял в нахождении способа переформулирования запроса таким образом, чтобы новое представление запроса обеспечивало тот же результат, но было более эффективно для обработки СУБД
SELECT VENDOR_CODE, PRODUCT_СОDЕ, РRОDUСТ_DESC FRОМ VENDOR, РRОDUСТ WHERE VENDOR _СОDЕ = РRОDUСТ. VENDOR _СООЕ АND VENDOR _СОDЕ = "100"; Наиболее очевидный путь обработки этого запроса состоит в следующем: • Формируем декартово произведение таблиц PRODUCT и VENDOR. • Ограничиваемся в результирующей таблице строками, которые удовлетворяют условию поиска в предложении WHERE. • Выполняем проекцию результирующей таблицы на список колонок, указанный в предложении SELECT. Оценим стоимость процесса обработки этого запроса в терминах операций ввода/вывода. Пусть для определенности таблица VENDOR содержит 50 строк, а таблица PRODUCT — 1000 строк. Тогда формирование декартова произведения потребует 50050 операций чтения и операций записи (в результирующую таблицу). Для ограничения результирующей таблицы потребуется более 50000 операций чтения и, если 20 строк удовлетворяют условиям поиска, то 20 операций записи. Выполнения операции проекции вызовет еще 20 операций чтения и 20 операций записи. Таким образом, обработка этого запроса обойдется системе в 100090 операций чтения и записи.
Основная идея синтаксической оптимизации лежит в использовании эквивалентных алгебраических преобразований. SQL является алгебраическим языком манипулирования множествами (представленными таблицами). Каждый оператор SELECT эквивалентен некоторой формуле этого языка. Существует набор алгебраических правил для тождественных преобразований формул над множествами. Для данного примера запроса можно использовать следующую эквивалентность: (А JOIN В) WHERE restriction оn А<=>(А WHERE restriction оn А) JOIN В. Это означает, что ограничение по условию поиска может быть выполнено как можно раньше для того, чтобы ограничить число строк, которые могут быть обработаны позже. Применяя это правило к запросу, приведенному выше, получаем следующий процесс обработки запроса: Ограничение по условию поиска во второй таблице (VENDOR _СОDЕ = "100") приведет к 1000 операций чтения и 20 операциям записи. Выполнение соединения полученной на 1 м шаге результирующей таблицы с таблицей VENDOR потребует 20 операций чтения результирующей таблицы, 100 операций чтения из таблицы VENDOR и 20 операций записи в новую результирующую таблицу. Обработка запроса в этом случае потребует 1120 операций чтения и 40 операций записи для получения того же самого результата, что и в первом случае. Преобразование, описанное в данном примере, называется синтаксической оптимизацией (syntax optimization).
Оптимизация, основанная на правилах Операции соединения подчиняются как коммутативному, так и ассоциативному закону. Следовательно, теоретически возможно выполнять соединение в любом порядке. Следовательно, когда выполняется соединение нескольких таблиц, каждая из которых имеет несколько индексов, то существует несколько сотен различных комбинаций для выбора порядка выполнения соединений, алгоритмов соединений и путей доступа осуществления выборки. Каждая из этих комбинаций производит один и тот же результат, но с различными характеристиками производительности. Одним из первых подходов на пути борьбы с комбинаторной сложностью выполнения соединений состоит в установлении эвристических правил для выбора между путями доступа и методами соединений, которая называется оптимизацией, основанной на правилах (rule based optimization). В этом подходе веса и предпочтения назначаются альтернативам на основе принципов, которые являются общепризнанными. Используя эти веса и предпочтения, оптимизатор запросов производит возможные планы выполнения до тех пор, пока не будет достигнут лучший план выполнения, удовлетворяющий этим правилам. Оптимизация, основанная на правилах, обеспечивает удовлетворительную производительность системы в тех ситуациях, когда эвристики являются точными. Однако часто общепризнанные правила не являются точными. Для обнаружения таких ситуаций оптимизатор запросов должен рассматривать характеристики данных, такие как: • число строк в таблице; • интервал и распределение значений данной колонки; • длину строки и, соответственно, число строк на физической странице диска; • высоту индекса; • число терминальных (leaf) страниц в индексе.
Оптимизация, основанная на вычислении стоимости запроса (cost based optimization), аналогична оптимизации, основанной на правилах, за исключением того, что оптимизатор на основе вычисления стоимости использует статистическую информацию для выбора наиболее эффективного плана выполнения запроса. Стоимость каждого альтернативного плана выполнения запроса оценивается с помощью статистики, такой как число строк в таблице и числа и распределения значений колонки таблицы. Формулы стоимости обычно учитывают количество ввода/вывода и время СРП, необходимое для выполнения плана запроса. Такая статистика хранится в системном каталоге и поддерживается СУБД.
Последовательность шагов оптимизации запросов Несмотря на то, что оптимизаторы запросов современных реляционных СУБД различаются по сложности и принципам создания, все они следуют одним и тем основным этапам в выполнении оптимизации запроса. • Синтаксический разбор запроса (parsing). Оптимизатор сначала разбивает запрос на его синтаксические компоненты, проверяет ошибки в синтаксисе и затем преобразует запрос в его внутреннее представление для дальнейшей обработки. • Преобразование (соnversion). Далее оптимизатор применяет правила преобразования запроса для преобразования его в формат, оптимальный с точки зрения синтаксиса. • Построение альтернатив (Develop alternatives). Когда запрос проходит синтаксическую оптимизацию, оптимизатор разрабатывает альтернативы для его выполнения. • Создание плана выполнения запроса (Create execution plan). Окончательно оптимизатор выбирает лучший план выполнения запроса, либо следуя набору эвристических правил, либо вычисляя стоимость для каждой альтернативы выполнения.
• • Физические операции Сортировка и агрегация Операции доступа к диску – – • • Сканирование таблицы (Table scan). Сканирование нижнего уровня индекса (Index leaf scan). Сканирование индекса (Matching index scan). Хэширование (Hash acces). Операции соединения Другие физические встроенные операции(выборка и проекция ) При выполнении плана запроса, SQL сервер использует физические операции (physical operation). Эти операции отличаются от логических операций, таких как утверждения SQL, определяющих реляционные операции, которые следует выполнить. Для каждой возможной логической операции существует по крайней мере одна и, возможно, много физических операций, которые позволяют СУБД выполнять операцию эффективным способом. Каждая физическая операция имеет один или два входа, в зависимости от природы операции. Также она имеет одну таблицу на выходе (которая, конечно, может содержать одну единственную строку или вовсе не содержать строк). Эта выходная таблица может быть результирующим множеством, которое представляет окончательный вывод для запроса, или может быть промежуточной таблицей, которая будет использована как вход для некоторой операции согласно плану запроса.
Дерево запроса Одним из способов представления запроса, которое обеспечивает понимание механизма его выполнения, является дерево запроса (query tree). Дерево запроса представляет собой древовидную структуру, в которой окончательный результат запроса находится на вершине дерева (в его корне) и таблицы БД, участвующие в запросе, являются листьями этого дерева. Промежуточные узлы дерева представляют физические операции, которые должны выполняться во время запроса. Когда план запроса выполняется, то последовательность обхода узлов осуществляется снизу вверх и слева направо.
SELECT А. NАМЕ FROM AUTHORS А, BOOCKS В WHERE (В. DАТЕ_PUBLISHED=‘ 1993') АND (А. NАМЕ = В. NАМЕ); Дерево запроса Использование эвристических правил для увеличения эффективности запроса
Преобразование логики предиката Когда несколько предикатов указаны в реляционной операции выборки, важная эвристика состоит в том, чтобы преобразовать их в эквивалентные условия в конъюнктивной форме, которая состоит в перегруппировке логических условий в предикате. Например, WHERE СОL 1 > 5 ОR (С 0 L 2 < 500 АND С 0 L 3 >150) может быть переписано как WHERE (СОL 1 > 5 0 R С 0 L 2 < 500) АND ( СОL 1 >5 ОR С 0 L 3 >150). Конъюнктивная форма является предпочтительнее, так как может быть оценена с помощью метода укороченной цепочки
Фактор селективности является статистическим показателем, который вычисляется оптимизатором для предикатов, используемых реляционной операцией выборки. В дополнение, фактор селективности используется оптимизатором для определения того, является ли путь доступа через индекс более эффективным, чем сканирование таблицы. Следовательно, фактор селективности вычисляется только для тех предикатов, которые содержат индексируемые атрибуты. Фактор селективности оценивает число строк, которые возвращаются применении предикатов выборки на таблицу известного размера. Это является важным моментом, когда оптимизатор оценивает использование индекса при построении плана выполнения. Индекс будет более эффективным, чем сканирование таблицы, только если высота индекса, умноженная на фактор селективности, умноженные на число строк в таблице, будет меньше, чем число физических страниц базы данных, занятых таблицей. Численно фактор селективности представляет вероятность, которая изменяется от 0 до 1. Умножение числа строк в таблице на фактор селек тивности для связанного с ним предиката будет давать ожидаемое число строк для операции выборки, при предположении, что значения колонок таблицы равномерно распределены по строкам.
Тип предиката в порядке приоритета Константа Не константа = 1/саrd != <> 1 -1/card 1/3 > Сканирование индекса 1/3 !> Сканирование индекса 1/3 < Сканирование индекса 1/3 !< Сканирование индекса 1/3 >= Сканирование индекса 1/3 <= Сканирование индекса 1/3 BETWEEN Сканирование индекса 1/3 NULL - Не используется EXIST Преобразование LIKE Сканирование индекса Не определен IN Преобразование
Анализ запросов с целью повышения скорости их выполнения Рассмотрим теперь общую процедуру настройки команды SELECT, результат выполнения которой не удовлетворяет требованиям производительности. Шаг 1. Обновить статистику. До того как добавить индексы, необходимо убедиться, что статистика базы данных в системном каталоге является корректной. Шаг 2. Упростить команду SELECT. Перед добавлением индексов или переписыванием плана выполнения следует попытаться упростить запрос. Для того чтобы упростить SELECT, необходимо: • исключить ненужные предикаты и предложения; • расставить скобки в арифметических и логических выражениях; • преобразовать связанные переменные в константы
Шаг 3. Пересмотреть план запроса. Выполните запрос так, чтобы посмотреть его план. Вы должны хорошо понимать план запроса, чтобы использовать его. Несколько элементов этого плана требуют особого внимания. Преобразование подзапроса в соединение. Оптимизатор преобразует большинство подзапросов в соединения. Нужно знать, на каких этапах выполнения запроса это преобразование происходит. Когда будут создаваться временные таблицы. Если временные таблицы создаются, это может указывать, что оптимизатор сортирует промежуточные результаты. Если это происходит, можно попробовать добавить индекс на одном из следующих шагов настройки для того, чтобы избежать сортировки. Шаг 4. Локализовать узкие места. Запрос, который выполняется медленно, может содержать много предложений и предикатов. Если это так, нужно определить, какие предложения или предикаты приводят к плохой производительности. Если удалить одно или два предложения или предиката, производительность выполнения запроса возрастает значительно. Эти предложения являются критическими параметрами выполнения запроса Может быть, индекс не используется из за применения функции. В этом случае можно построить индекс с использованием этой функции.
Шаг 5. Создать индексы для одной колонки для критических параметров. Если завершены шаги 1 4 и запрос еще не удовлетворяет требованиям производительности, можно попытаться создать индексы специально для этого запроса, чтобы увеличить его производительность. В общем, индексы для одной колонки предпочтительнее, чем составные индексы, так как более вероятно их использование в других запросах. Шаг 6. Создать индексы для нескольких колонок. Процедура идентификации колонок для создания составных индексов состоит в следующем; • Создать составной индекс. Если производительность не увеличилась, создайте другой индекс и повторите процесс. • Несколько колонок в предложении GROUP BY. • Несколько колонок в предложении ORDER BY. • Колонки соединения плюс низкая стоимость ограничений. • Колонки соединения плюс все ограничения. Шаг 7. Удалить все индексы, которые не используются в плане запроса. Как уже указывалось выше, индексы замедляют выполнение команд DML, а их сопровождение требует времени и увеличивает стоимость обработки. Следовательно, вам следует проследить использование всех созданных индексов и удалить те, которые не используются запросом.
Скачать презентацию Введение в оптимизацию запросов Языки обработки данных и
ПрИС Лекция 16.ppt