Введение в оптимизацию запросов

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

Компонента SQL СУБД, которая определяет, как осуществлять навигацию по физическим структурам данных для доступа к требуемым данным, называется оптимизатором запросов (query optimizer).

Навигационная логика (вариант алгоритма) для доступа к требуемым данным называется путем или методом доступа (access path).

Последовательность выполняемых оптимизатором действий, которые обеспечивают выбранные пути доступа, называется планом выполнения (execution plan).

Процесс, используемый оптимизатором запросов для определения пути доступа, называется оптимизацией запросов (query optimization).

Во время процесса оптимизации запросов определяются пути доступа для всех типов команд SQL DML. Однако команда SQL SELECT представляет наибольшую сложность в решении задачи выбора пути доступа. Поэтому этот процесс обычно называют оптимизацией запроса, а не оптимизацией путей доступа к данным. Далее следует отметить, что термин оптимизация запросов является не совсем точным в том смысле, что нет гарантии, что в процессе оптимизации запроса будет действительно получен оптимальный путь доступа. Более подходящим термином мог бы быть термин "улучшение запроса" (query improvement). Например, наилучший возможный путь доступа, имеющий заданную стоимость (в смысле вычислительной сложности). Далее всюду используется стандартный общепринятый термин "оптимизация запросов" во избежание недоразумений.

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

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

Синтаксическая оптимизация

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

Пример. Рассмотрим следующий запрос, который делает выборку данных из таблиц PRODUCT (ПРОДУКЦИЯ) и VENDOR (ПРОИЗВОДИТЕЛЬ):

SELECT VENDOR_CODE, PRODUCT_CODE, PRODUCT_DESC
FROM VENDOR, PRODUCT
WHERE VENDOR.VENDOR_CODE = PRODUCT.VENDOR_CODE
AND VENDOR.VENDOR_CODE = "100";

Наиболее очевидный путь обработки этого запроса состоит в следующем:

1. Формируем декартово произведение таблиц PRODUCT и VENDOR.
2. Ограничиваемся в результирующей таблице строками, которые удовлетворяют условию поиска в предложении WHERE.
3. Выполняем проекцию результирующей таблицы на список колонок, указанный в предложении SELECT.

Оценим стоимость процесса обработки этого запроса в терминах операций ввода/вывода. Пусть для определенности таблица VENDOR содержит 50 строк, а таблица PRODUCT - 1000 строк. Тогда формирование декартова произведения потребует 50050 операций чтения и операций записи (в результирующую таблицу). Для ограничения результирующей таблицы потребуется более 50000 операций чтения и, если 20 строк удовлетворяют условиям поиска, то 20 операций записи. Выполнения операции проекции вызовет еще 20 операций чтения и 20 операций записи. Таким образом, обработка этого запроса обойдется системе в 100090 операций чтения и записи.

Основная идея синтаксической оптимизации лежит в использовании эквивалентных алгебраических преобразований. SQL является алгебраическим языком манипулирования множествами (представленными таблицами). Каждый оператор SELECT эквивалентен некоторой формуле этого языка. Существует набор алгебраических правил для тождественных преобразований формул над множествами. Для данного примера запроса можно использовать следующую эквивалентность:

(A JOIN B) WHERE restriction on A v (A WHERE restriction on A) JOIN B.

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

1. Ограничение по условию поиска во второй таблице ( VENDOR_CODE = "100") приведет к 1000 операций чтения и 20 операциям записи.
2. Выполнение соединения полученной на 1-м шаге результирующей таблицы с таблицей VENDOR потребует 20 операций чтения результирующей таблицы, 100 операций чтения из таблицы VENDOR и 20 операций записи в новую результирующую таблицу.

Обработка запроса в этом случае потребует 1120 операций чтения и 40 операций записи для получения того же самого результата, что и в первом случае. Преобразование, описанное в данном примере, называется синтаксической оптимизацией (syntax optimization).

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