- •Оптимизация sql-запросов
- •Последовательность оптимизации запросов и используемые законы реляционной алгебры
- •Основные шаги оптимизации
- •Законы реляционной алгебры
- •Построение логического плана
- •Преобразование запроса в формулу реляционной алгебры
- •Оптимизация формулы реляционной алгебры
- •Иллюстрация логической оптимизации
- •Пример построения логического плана
- •Оптимизация запроса
- •Порядок выполнения запроса на логическом уровне
- •Построение физического плана
- •Шаги построения физического плана
- •Отличие физического плана от логического
- •Методы выбора записей из исходной таблицы
- •1. Чтение всех записей таблицы и их фильтрация.
- •2. Чтение записей с помощью индекса и их фильтрация.
- •Оценка числа кортежей в промежуточной таблице q
- •Оценка числа блоков в промежуточной таблице q
- •Порядок соединения таблиц
- •Левостороннее дерево соединений
- •Кустовое дерево соединений
- •Правостороннее дерево соединений
- •Методы соединения таблиц
- •Метод вложенных циклов (nlj – Nested Loop Join)
- •Метод сортировки-слияния (smj – Sort Merge Join)
- •Метод хешированного соединения (hj – Hash Join)
- •Число кортежей, блоков и мощности атрибутов в соединении
- •Поиск физического плана с минимальной стоимостью
- •Алгоритм поиска для левостороннего дерева соединений
- •Формат экземпляра структуры данных
- •Спецификации процедуры AccessPlan
- •Спецификации процедуры JoinPlan
- •Спецификации процедуры OptPlanReturn
- •Пример построения оптимального физического плана
- •Логический план
- •Алгоритм поиска оптимального физического плана
- •Определение метода выбора записей из исходной таблицы r1
- •Определение метода выбора записей из исходной таблицы r2
- •Оценка соединения q1 и q2 методом nlj
- •Оценка соединения q1 и q2 методом smj
- •Выбор метода соединения q1 и q2 и заполнение структуры
- •Оценка и выбор метода соединения q2 и q1
- •Вывод физического плана
Оптимизация формулы реляционной алгебры
При оптимизации формулы используются следующие правила:
1. Если условие F является конъюнкцией нескольких условий ( ), то переместить каждую селекцию внутрь декартова произведения, используя законы 1, 4, 6, 7, 8.
2. Переместить каждую проекцию внутрь декартова произведения, используя законы 1, 3, 5, 9, 10.
3. После указанных преобразований по возможности скомбинировать каждый каскад селекций в одиночную селекцию и каждый каскад проекций в одиночную проекцию. Это позволяет выполнить все операции селекции и проекции за один проход отношения, полученного после соединения таблиц. Например, .
В результате использования правил 1–3 формула реляционной алгебры (5.1), соответствующая исходному запросу, преобразуется в следующую формулу:
, (5.2)
Q1
Qn
…
где
– условие, сформулированное в исходном запросе SELECT;
f – условие соединения подзапросов {Qi}.
Подчеркнутые в приведённой выше формуле отношения Q1, …, Qn имеют меньшую размерность, чем исходные отношения R1, …, Rn , и потому запрос по формуле (5.2) выполняется быстрее, чем по формуле (5.1).
По формуле (5.2) можно построить логический план, представленный на Рис. 1 .1.
Рис. 1.1. Логический план выполнения запроса, соответствующий формуле (5.2)
Иллюстрация логической оптимизации
1. Выполнение запроса по формуле π A(σF(R1× R2 × … × Rn)), до оптимизации, иллюстрируется рисунком (Рис. 1 .2).
Рис. 1.2. Выполнение запроса по формуле (5.1).
2. Выполнение запроса по формуле
, после оптимизации, иллюстрируется рисунком (Рис. 1 .3).
Рис. 1.3. Выполнение запроса после оптимизации по формуле (5.2).
В случае отсутствия оптимизации запроса вначале нужно определить декартово произведение исходных таблиц R1, …, Rn , затем выполнить селекцию σF и взять проекцию πA. Если таблицы R1, …, Rn имеют большую размерность, то время выполнения запроса будет достаточно большим. Поэтому запрос по формуле (5.2) выполняется быстрее, так как отношения Q1, …, Qn имеют меньшую размерность, чем исходные отношения R1, …, Rn .
Примечание. Если исходные таблицы R1, …, Rn размещаются на разных серверах или на одном суперкомпьютере, то подзапросы Q1, …, Qn могут выполняться параллельно.
Пример построения логического плана
Оптимизация запроса
Предположим, что на сервер СУБД поступает запрос SELECT. На сервере хранятся две таблицы, участвующие в запросе (Рис. 1 .4).
Рис. 1.4. Исходные таблицы на сервере СУБД.
Предположим, что клиент, связанный с сервером СУБД, выдал следующий запрос: "Найти значения остатков, большие 1500, на счетах пользователя с кодом 3". Соответствующий запрос SELECT имеет следующий вид:
SELECT остаток
FROM R2
WHERE остаток > 1500 AND номер_счета IN
(SELECT номер_счета
FROM R1
WHERE код_пользователя = 3);
Этот оператор SELECT преобразуется в формулу реляционной алгебры (см. п. 1.2.1):
Эта формула подвергается оптимизации. Оптимизатор вначале строит логический план выполнения запроса, используя законы реляционной алгебры (см. пункты 1.1.2 и 1.2.2, номера законов показаны над равенствами):
Последняя формула – результат оптимизации, здесь подчёркнуты подзапросы. По этой формуле оптимизатор строит логический план выполнения запроса (Рис. 1 .5).
Рис. 1.5. Логический план выполнения запроса в графическом виде.