- •Оптимизация 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
- •Вывод физического плана
Спецификации процедуры JoinPlan
Вход: список имен таблиц R=(P – Qj) и имя таблицы S=Qj (см. основной алгоритм в п. 1.7.1)
Выход: заполненный экземпляр структуры str[n].
Алгоритм.
Поиск в массиве структур str экземпляров с номерами m1 и m2,
для которых str[m1].W=R и str[m2].W=S .
// Оценка стоимости соединения для различных методов:
// i=1 – NLJ, i=2 – SMJ, i=3 – HJ ;
// выбрать оптимальный метод соединения k(1,2,3).
ДЛЯ i=1,3
Ci = CCPUi + CI/Oi
// CCPUi и CI/Oi вычисляются с помощью формул (5.8) (для i=1)
// или формул (5.9) (для i=2); метод i=3 не рассматривается.
// Необходимая информация для расчета по формулам
// хранится в полях str[m1].V и str[m2].V.
КОНЕЦ ДЛЯ
C = min(C1, C2) //здесь С=Сk; пока это стоимость соединения R и S
// текущая стоимость плана, включающая стоимости чтения
// из исходных таблиц и стоимости промежуточных соединений
С = str[m1].Z + str[m2].Z + C
СI/O = str[m1].ZIO + str[m2].ZIO + CI/O k // дисковая составляющая
Поиск в массиве структур str экземпляра "n", для которого str[n].W=P. Если экземпляр "n" не найден, то заполнить пустой экземпляр "n". Если экземпляр "n" найден и выполняется неравенство str[n].Z > C, то переписать экземпляр "n". Выражения для заполнения экземпляра "n":
str[n] = {P, R, S, C, CI/O , // W, X, Y, Z, ZIO
{T(P), B(P), {I(P, Ai)}i, k} } // V - см. формулы (5.10)
Конец алгоритма.
Спецификации процедуры OptPlanReturn
Вход: список таблиц Q={Qi}.
Выход: вывод оптимального плана.
Алгоритм.
Поиск в массиве структур str экземпляра i, где str[i].W=Q
// Вывод шага оптимизации
Печать (Q, "=", str[i].X, " ", str[i].Y, "метод", str[i].V.k)
Если str[i].X пусто, то выйти из алгоритма
// Вывод оптимального плана для левого аргумента соединения
OptPlanReturn(str[i].X)
// Вывод метода выбора записей для правого аргумента
// соединения, которым является исходная таблица.
OptPlanReturn(str[i].Y)
Конец алгоритма.
Пример построения оптимального физического плана
Логический план
Построим оптимальный физический план для примера, который был рассмотрен ранее в разделе 1.3. В этом примере был построен логический план, представленный на Рис. 1 .24.
Рис. 1.24. Логический план выполнения запроса.
Ниже приведены исходные данные для построения физического плана:
1. Количество записей в исходных таблицах:
T(R1)=10000, T(R2)=100000
2. Количество записей в одном блоке таблицы:
LR1= LR2= 100, LJOIN=1000
3. Индексы по атрибутам и число записей в блоке индекса (L):
таблица R1: индекс по атрибуту "код_пользователя" (L=200),
таблица R2: индекс по атрибуту "номер_счета" (L = 200).
Примечание. Записи исходных таблиц могут читаться в отсортированном виде по своим индексированным атрибутам. Записи в таблице R1 сгруппированы по атрибуту "код_пользователя" (кластеризованный индекс), записи в таблице R2 не сгруппированы по атрибуту "остаток".
4. Мощности атрибутов в исходных таблицах:
I(R1, код_пользователя) = 5000, I(R1, номер_счета) = 10000,
I(R2, номер_счета) = 100000, I(R2, остаток) – неизвестно.
5. Предполагается, что используются левосторонние деревья для поиска оптимального плана и применяются каналы.
6. Некоторые параметры:
b = 10 – число блоков в буфере;
Ccomp = Cmove = Cfilter = 0,01 мс;
CB = 10 мс – время чтения/записи блока на диск.
Для построения оптимального плана воспользуемся алгоритмом динамического программирования (см. п. 1.7.1).