$ \newcommand{\gdiv}{\mathbin{⋇}} \newcommand\joineq{\raise{-0.05ex}{\unicode{xa78a}}} \newcommand{\rjoin}{\mathbin{⋉\mkern-6mu\joineq}} \newcommand{\ljoin}{\mathbin{\joineq\mkern-6mu⋊}} \newcommand{\ojoin}{\mathbin{\joineq\mkern-10mu×\mkern-10mu\joineq}}$
Базы данных

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

Пример базы данных

  • Students(SId, FirstName, LastName, GId, Year)
    • $10^4$ записей
    • Индексы: (SId) (кластеризованный), (GId)
  • Groups(GId, Name)
    • $10^3$ записей
    • Индексы: (GId) (кластеризованный), (Name)

Запрос

  • Фамилии студентов группы M3439
    • select LastName
      from Students natural join Groups
      where Name = 'M34391'
      

Планы запросов без индексов

  • План 1
    • $π_{LastName}(σ_{Name=M34391}(σ_{S.GId=G.GId}(S × G)))$
    • $10^4·10^3 + 10^4·10^3 + 10^4 + 20 ≈ 2·10^7$
  • План 2
    • $π_{LastName}(σ_{Name=M34391}(S ⋈ G))$
    • $10^4·10^3 + 10^4 + 20 ≈ 10^7$
  • План 3
    • $π_{LastName}(S ⋈ σ_{Name=M34391}(G))$
    • $10^3 + 10^4 + 20 ≈ 10^4$

Планы запросов с индексами

  • План 3. Students(GId)
    • $π_{LastName}(S ⋈ σ_{Name=M34391}(G))$
    • $10^3 + (3 + 20) + 20 ≈ 10^3$
  • План 4. Groups(Name), Students(GId)
    • $π_{LastName}(S ⋈ σ_{Name=M34391}(G))$
    • $2 + (3 + 20) + 20 = 45$

Результаты

  • Наиболее медленный план
    • $2·10^7$
  • Наиболее быстрый план
    • $45$
  • ⇒ От выбора плана сильно зависит производительность

Обработка запроса

Планировщик запроса

Перезапись и планировщик

  • Перезапись запроса
    • Статические правила оптимизации запроса
    • Считается, что полезны всегда
  • Планировщик запроса
    • Оптимизация в зависимости от данных
    • Перебор и оценка вариантов

Выбор структуры и метода

  • Выбор структуры
    • Преобразование запроса
    • Порядок выполнения операций
    • Порядок соединений
  • Выбор метода исполнения
    • Как исполняется каждая операция

Оценка плана

  • Модель стоимости
    • Стоимость операции
    • Размер операндов
    • Размер результата
  • Оценка размера и распределения
    • Статистика по данным
    • Статистика предыдущих запросов

Преобразование подзапросов

  • Преобразуются в реляционную алгебру
    • Запись в реляционном исчислении
    • Вынос кванторов
    • Преобразование в алгебру

Преобразование соединений

  • Внешние соединения
    • $R_1 \ojoin_θ R_2 ⇒ (R_1 \ljoin_θ R_2) ∪ (R_1 \rjoin_θ R_2)$
    • $R_1 \ljoin_θ R_2 ⇒ σ_θ(R_1 × R_2) ∪ (R_1 - π_{R_1}(σ_θ(R_1 × R_2)))$
    • $R_1 \rjoin_θ R_2 ⇒ σ_θ(R_1 × R_2) ∪ (R_2 - π_{R_2}(σ_θ(R_1 × R_2)))$
  • Декартово соединение
    • $R_1 × R_2 ⇒ R_1 ⋈ R_2$

Повторная фильтрация

  • Правило
    • Повторное применение фильтрации заменяется одинарным
    • $σ_{cond_1}(σ_{cond_2}(R)) ⇒ σ_{cond_1 ∧ cond_2}(R)$
  • Пример
    • $π_{FirstName}(σ_{Name=M34391}(σ_{S.GId=G.GId}(S × G)))$$π_{FirstName}(σ_{Name=M34391 ∧ S.GId=G.GId}(S × G))$

Повторная проекция

  • Правило
    • Повторное применение проекции заменяется внешней
    • $π_{A}(π_{B}(R)) ⇒ π_{A}(R)$
  • Пример
    • $π_{FirstName}(π_{FirstName, Name}(S × G))$$π_{FirstName}(S × G)$

Проекция и фильтрация

  • Правило
    • Фильтрация осуществляется до проекции
    • $σ_{cond}(π_{A}(R)) ⇒ π_{A}(σ_{cond}(R))$
  • Пример
    • $π_{FirstName}(σ_{Name=M34391}(π_{FirstName, Name}(S × G)))$$π_{FirstName}(π_{FirstName, Name}(σ_{Name=M34391}(S × G)))$$π_{FirstName}(σ_{Name=M34391}(S × G))$
  • Всегда ли так можно сделать?
    • Да
  • А наоборот?
    • Нет

Дистрибутивность операций

  • Фильтрация
    • $σ_{cond}(R_1 ∪ R_2) ⇒ σ_{cond}(R_1) ∪ σ_{cond}(R_2)$
    • $σ_{cond}(R_1 \cap R_2) ⇒ σ_{cond}(R_1) \cap σ_{cond}(R_2)$
    • $σ_{cond}(R_1 - R_2) ⇒ σ_{cond}(R_1) - σ_{cond}(R_2)$
    • $σ_{cond_1 ∧ cond_2}(R_1 ⋈ R_2) ⇒ σ_{cond_1}(R_1) ⋈ σ_{cond_2}(R_2)$
  • Проекция
    • $π_A(R_1 ∪ R_2) ⇒ π_A(R_1) ∪ π_A(R_2)$
    • $π_A(R_1 ∩ R_2) ⇏ π_A(R_1) ∩ π_A(R_2)$
    • $π_A(R_1 - R_2) ⇏ π_A(R_1) - π_A(R_2)$
    • $π_{A}(R_1 ⋈ R_2) ⇒ π_A(π_{(A ∪ R_2) ∩ R_1}(R_1) ⋈ π_{(A ∪ R_1) ∩ R_2}(R_2))$

Коммутативность операций

  • Коммутативные операции
    • $⋈$
    • $∪$
    • $∩$
  • Некоммутативные операции
    • $-$
    • $\div$, $\gdiv$
  • Применение коммутативности
    • Выбор левой и правой стороны для несимметричных методов исполнения

Ассоциативность операций

  • Ассоциативные операции
    • $⋈$
    • $∪$
    • $∩$
  • Неассоциативные операции
    • $-$
    • $\div$, $\gdiv$
  • Применение ассоциативности
    • Выбор порядка выполнения операций

Замыкание предикатов

  • Примеры правил
    • $a = b ∧ b = c$$a = b ∧ b = c ∧ a = c$
    • $a > b ∧ b = c$$a > b ∧ b = c ∧ a > c$
    • $a > b ∧ b > c$$a > b ∧ b > c ∧ a > c$
  • Пример
    • $σ_{P_1.p > P_2.p ∧ P_2.p ≥ 60}(P_1 ⋈_{P_1.SId = P_2.SId} P_2)$$σ_{P_1.p > P_2.p ∧ P_2.p ≥ 60 ∧ P_1.p > 60}(P_1 ⋈_{P_1.SId = P_2.SId} P_2)$$σ_{P_1.p > P_2.p}(σ_{p > 60}(P_1) ⋈_{P_1.SId = P_2.SId} σ_{p ≥ 60}(P_2))$

КНФ и ДНФ

  • Преобразование предикатов
    • Конъюнктивная нормальная форма
    • Дизъюнктивная нормальная форма
  • Вычисление КНФ
    • Слева направо, до первой лжи
  • Вычисление ДНФ
    • Слева направо, до первой истины

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

  • Применение знания об ограничениях
    • Неэквивалентные запросы
    • Тот же результат
  • Пример
    • $π_{FirstName}(Students ⋈ Groups)$$π_{FirstName}(σ_{GId ≠ null}(Students))$,
      если $Students.GId ⊆ Groups.GId$

Пример оптимизации

  • Ограничение
    • У всех, кто получает стипендию, все оценки $≥ 60$
    • check not HasScolarship or 60 <=
        all (select Points from Points where Points.SId = Id)
      
  • Запрос
    • Оценки стипендиатов группы M34391 по СУБД
    • select Points from Students natural join Points
      where HasScolarship and CId = 10 and GId = M34391
      
    • $σ_{GId=M34391 ∧ HasScolarship ∧ CId = 10}(Students ⋈ Points)$
  • Оптимизированный запрос
    • $σ_{GId=M34391 ∧ HasScolarship}(Students) ⋈$
      $⋈ σ_{60 ≥ Points ∧ CId = 10}(Points)$

Сеть операций

  • Аргументы операции
    • Базовые таблицы
    • Результаты других операций
  • Результат операций
    • Аргумент другой операции
    • Окончательный результат

Типы операций

  • Преобразователи
    • Просматривают аргументы в процессе работы
  • Контейнеры
    • Загружают аргументы
    • Сохраняют результаты
    • При необходимости – на диске

Курсоры

  • Выдают записи последовательно
  • Операции
    • open() – открыть курсор
    • fetch() – следующая строка
    • reset() – начать с начала
    • close() – закрыть курсор

Фильтрация

  • Преобразователь
    • Выкидывает ненужные строки
  • Трудоёмкость
    • $O(n)$, где $n$ – размер входа
  • Свойства
    • Может сильно уменьшать количество строк (селективность)

Проекция

  • Преобразователь
    • Выкидывает ненужные атрибуты
    • Сравнивает соседние элементы
  • Трудоёмкость
    • $O(n)$, где $n$ – размер входа
  • Свойства
    • Вход должен быть упорядочен
    • Сохраняет порядок

Сортировка

  • Контейнер
    • Сортировка в памяти
    • Внешняя сортировка
  • Трудоёмкость
    • $O(n \log n)$, где $n$ – размер входа
  • Свойства
    • Выход упорядочен
    • Время исполнения скачкообразно зависит от объема

Просмотр таблиц

  • Преобразователь и контейнер
    • Полный проход по таблице
    • Внешняя сортировка
  • Трудоёмкость
    • $O(n)$, где $n$ – размер входа
  • Методы исполнения
    • Полный просмотр
    • Индекс
    • Кластеризованный индекс

Объединение

  • Преобразователь
    • Слияние входов
  • Трудоёмкость
    • $O(n)$, где $n$ – размер входа
  • Свойства
    • Упорядоченные входы
    • Упорядоченный результат

Полное объединение

  • Преобразователь
    • Чтение первого входа
    • Чтение второго входа
    • ...
  • Трудоёмкость
    • $O(n)$, где $n$ – размер входа

Пересечение

  • Преобразователь
    • Слияние входов
  • Трудоёмкость
    • $O(n)$, где $n$ – размер входа
  • Свойства
    • Упорядоченные входы
    • Упорядоченный результат

Разность

  • Преобразователь
    • Слияние входов
  • Трудоёмкость
    • $O(n)$, где $n$ – размер входа
  • Свойства
    • Упорядоченные входы
    • Упорядоченный результат

Вложенный перебор

  • Преобразователь
    • Перебор левого входа
    • Перебор правого входа
  • Трудоёмкость
    • $O(l·r)$, где $l$, $r$ – размеры входов
  • Свойства
    • Сохраняет упорядоченность

Поиск по дереву

  • Преобразователь
    • Перебор левого входа
    • Доступ к правому входу по индексу
  • Трудоёмкость
    • $O(\max(l·\log r, n))$, где $l$, $r$ – размеры входов,
      $n$ – выхода
  • Свойства
    • Сохраняет упорядоченность

Поиск по хешу

  • Преобразователь+контейнер
    • Захешировать правый вход
    • Перебор левого входа
    • Доступ по хешу к правому входу
  • Трудоёмкость
    • $O(\max(l + r, n))$, где $l$, $r$ – размеры входов, $n$ – выхода

Слияние

  • Преобразователь
    • Слияние входов
    • Перебор левого входа пока равны
    • Перебор правого входа пока равны
  • Трудоёмкость
    • $O(\max(l + r, n))$, где $l$, $r$ – размеры входов, $n$ – выхода
  • Свойства
    • Упорядоченные входы
    • Упорядоченный результат

Операции

  • Унарные
    • Фильтрация – первый приоритет
    • Проекция – второй приоритет
  • Над множествами
    • Не влияют
  • Соединения
    • Порядок важен
    • Желательно сокращение результата
    • Число вариантов $n!C_n ≈ {n! 4^n \over n^{3/2} \sqrt π}$

Эвристика 1

  • Совершаются только запрошенные соединения
  • Пример
    • select * from Students natural join Points
      natural join Courses
      
  • Планы исполнения
    • $(Students ⋈ Points) ⋈ Courses$
    • $(Points ⋈ Courses) ⋈ Students$
    • $(Students ⋈ Courses) ⋈ Points$

Эвристика 2

  • Правый вход каждого соединения – таблица
    • Запрещены ветвящиеся деревья
  • Пример
    • select * from Students natural join Points
      natural join Courses natural join Lecturers
      
  • Планы исполнения
    • $((Students ⋈ Points) ⋈ Courses) ⋈ Lecturers$
    • $((Points ⋈ Courses) ⋈ Students) ⋈ Lecturers$
    • $(Students ⋈ Points) ⋈ (Courses ⋈ Lecturers)$

Общая идея

  • Динамическое программирование
    • На подмножествах
  • Оптимизируемая функция
    • Множество соединённых отношений
    • Виды упорядочения
    • ⇒ минимальные трудозатраты

Этапы

  • База
    • Полный просмотр
    • Частичный просмотр по кластеризованному индексу
    • Поиск по индексу
  • Шаг
    • Добавляем ко множеству все возможные отношения
    • Перебираем все методы реализации
    • Сохраняем наилучшие результаты
  • Трудозатраты
    • В большинстве случаев $O(2^n)$

Рандомизированные алгоритмы

  • Итеративное улучшение
    • Со случайным началом
  • Эмуляция отжига
  • Двухфазная оптимизация
    • Итеративное улучшение
    • Эмуляция отжига
  • Генетические алгоритмы

Точные алгоритмы

  • Алгоритм Ибараки-Камеда
    • Для случая вложенного перебора
    • Трудоёмкость $O(n^2 \log n)$
  • Алгоритм KBZ (Krishnamurthy-Boral-Zaniolo)
    • Зависит от вида формулы трудозатрат
    • + поиск по хешу
    • Трудоёмкость $O(n^2)$
  • Алгоритм AB
    • Смягчает ограничения на вид формулы
    • Построен на базе KBZ

Интересующие оценки

  • Получение всех результатов
    • Обычный запрос
  • Получение первого результата
    • exists
  • Получение части результатов
    • offset + limit

Размер и распределение

  • Оценка размера
    • Какого размера результат
    • Поместится ли он в памяти
  • Оценка распределения
    • Позволяет оценить размер результатов соединения

Характеристики распределения

  • Основные тенденции
    • Среднее, медиана, мода
  • Распределение
    • Минимум, максимум, дисперсия
  • Размеры
    • Число значений
    • Число различных значений
  • Распределение по частотам
    • Тип распределения, его параметры

Типы распределений

  • Распределение отношения
    • Строки
    • Страницы
  • Распределение атрибутов
    • Значения
    • Размер
    • Диапазон
    • Распределение
  • Распределение индексов
    • Листы, узлы, высота дерева
    • Уникальные значения, корзины

Упрощающие предположения

  • Равномерность распределения
    • Равное количество кортежей с равными значениями
  • Независимость атрибутов
    • Распределения атрибутов независимы
  • Равномерность запросов
    • Запросы ко всем значениям равновероятны
  • Равномерность заполнения
    • Страницы содержат равное число кортежей
  • Случайность расположения
    • Кортежи распределены по страницам случайным образом

Оцениваемые значения

  • Мат. ожидание числа чтений страниц
    • Запрос с одним атрибутом
    • Запрос с несколькими атрибутами
  • Мат. ожидание количества различных значений
    • В результате фильтрации
  • Теорема Христодоулакиса
    • Применение предположений даёт оценки для худшего случая

Общий механизм

  • Базовые отношения
    • Статистика
  • Результаты операций
    • Распределение аргументов
    • Свойства операции

Оценка фильтрации

  • Оценочная функция
    • $|Out| = Sel(expr)|In|$
  • Сравнения
    • Селективность из распределения
    • Интервальные оценки
    • Сравнение с другим атрибутом
  • Составные выражения
    • $Sel(\neg A) = 1 - Sel(A)$
    • $Sel(A ∧ B) = Sel(A) Sel(B|A)$
    • $Sel(A \vee B) = Sel(A) + Sel(B) - Sel(A ∧ B)$

Оценка соединения

  • Оценочная функция
    • $|Out| = Sel(expr)|R_1||R_2|$
  • Сравнения
    • Различные распределения
  • Уникальность ключей
    • $|Out| = min(|R_1|, |R_2|)$

Операции над множествами

  • Пересечение
    • $0 ≤ |Out| ≤ \min(|R_1|, |R_2|)$
  • Объединение
    • $\max(|R_1|, |R_2|) ≤ |Out| ≤ |R_1| + |R_2|$
  • Разность
    • $\max(|R_1| - |R_2|, 0) ≤ |Out| ≤ |R_1|$

Сбор статистики

  • Гистограммы распределений
    • Индексы
    • Явный пересчёт статистики
  • Результаты работы
    • Статистика по исполненным запросам
  • Предположение о распределениях
    • Нормальное распределение
    • Равномерное распределение

Описание примера

  • Таблицы
    • Students(SId, GId, FirstName, AveragePoints)
    • Groups(GId, Name, Program)
  • Индексы
    • Students(GId) – B-дерево
    • Students(AveragePoints) – B-дерево
    • Groups(Program) – хеш-индекс
  • Запрос
    • select FirstName, Name
      from Students natural join Groups
      where AveragePoints > 4.8 and Program = 5
      

Интересные порядки

  • Students.GId = Groups.GId
    • Отсутствует
    • Students.GId
    • Groups.GId
    • Students.GId, Groups.GId
    • Groups.GId, Students.GId

Пути доступа

Таблица Порядок Описание
Students Полный просмотр
StudentsGId Просмотр GId
StudentsGId Поиск по GId
Students Поиск по APoints
GroupsGId Полный просмотр
GroupsGId Поиск по GId
Groups Поиск по Program

Полный просмотр Students

  • Статистика таблицы
    • Записей – $10^4$
    • Страниц – $10^3$
  • Оценка
    • Размер результата – $10^4$
    • Стоимость – $10^3$

Просмотр Students.GId

  • Статистика индекса
    • Записей – $10^4$
    • Значений – $500$
    • Распределение – нормальное $(20, 10)$
    • Уровней – $3$
    • Страниц – $100$
  • Оценка
    • Размер результата – $10^4$
    • Стоимость – $100 + 10^4 ≈ 10^4$

Поиск по Students.GId

  • Статистика индекса
    • Записей – $10^4$
    • Значений – $500$
    • Распределение – нормальное $(20, 10)$
    • Уровней – $3$
    • Страниц – $100$
  • Оценка
    • Размер результата – $20$ / GId
    • Стоимость – $3 + 20 = 23$ / GId

Поиск по Students.AveragePoints

  • Статистика индекса
    • Записей – $10^4$
    • Значений – $300$
    • Распределение – нормальное $(4, 1/3)$
    • Страниц – $100$
    • Уровней – $3$
  • Оценка
    • Размер результата – $90$
    • Стоимость – $2 + 4 + 90 = 96$

Полный просмотр Groups

  • Статистика таблицы
    • Записей – $500$
    • Страниц – $20$
  • Оценка
    • Размер результата – $500$
    • Стоимость – $20$

Поиск по Groups.GId

  • Статистика таблицы
    • Записей – $500$
    • Страниц – $20$
  • Оценка
    • Размер результата – $1$ / GId
    • Стоимость – $1 + 1 = 2$ / GId

Поиск по Groups.Program

  • Статистика индекса
    • Записей – $500$
    • Значений – $125$
    • Страниц – $5$
    • Распределение – нормальное $(4, 1)$
  • Оценка
    • Размер результата – $4$
    • Стоимость – $1 + 4 = 5$

Оценка путей доступа

Таблица Порядок Описание Стоимость
Students Полный просмотр $1000$
StudentsGId Просмотр GId$10^4$
StudentsGId Поиск по GId$23$ / GId
Students Поиск по APoints$96$
GroupsGId Полный просмотр $20$
GroupsGId Поиск по GId$2$ / GId
Groups Поиск по Program$5$

Варианты соединения

Прав \ лев S.GIdS.APG.GIdG.Prog
Просмотр S.GId merge loops
Поиск по S.GId index index
Поиск по S.AP loops hash
Просмотр G.GIdmerge loops
Поиск по G.GId index index
Поиск по G.Prog loops hash

S.GId merge G.GId

  • Левая часть
    • Записей – $10^4$
    • Стоимость – $10^4$
  • Правая часть
    • Записей – $200$
    • Стоимость – $20$
  • Оценка
    • Стоимость – $10^4 + 20 + (10^4 + 20) / 10 ≈ 10^4$

S.AP index G.GId

  • Левая часть
    • Записей – $90$
    • Стоимость – $96$
  • Правая часть
    • Записей – $1$ / GId
    • Стоимость – $2$ / GId
  • Оценка
    • Стоимость – $96 + 90·2 = 276$

S.AP hash G.Prog

  • Левая часть
    • Записей – $90$
    • Стоимость – $96$
  • Правая часть
    • Записей – $4$
    • Стоимость – $5$
  • Оценка
    • Стоимость – $96 + 5 + (90 + 4) / 10 ≈ 111$

G.Prog index S.GId

  • Левая часть
    • Записей – $4$
    • Стоимость – $5$
  • Правая часть
    • Записей – $20$ / GId
    • Стоимость – $23$ / GId
  • Оценка
    • Стоимость – $5 + 4·23 = 97$

Оценка вариантов соединения

Метод Стоимость
S.GId merge G.GId$10^4$
S.AP index G.GId$276$
S.AP hash G.Prog$111$
G.Prog index S.GId$97$

Модификация статистики

  • Students.AveragePoints
    • Распределение – нормальное (4, 1/4)
  • Что изменится?
    • Размер результата – $7$
    • Стоимость – $3 + 7 = 10$
  • Какие стоимости изменятся?
    • S.AP index G.GId: $10 + 7·2 = 24$
    • S.AP hash G.Prog: $10 + 5 + (7 + 5) / 10 ≈ 16$

Модифицированный запрос

  • Запрос
    • select FirstName, Name
      from Students natural join Groups
      where AveragePoints > 4.8 and Program = 5
      order by Students.GId
      
  • Что изменилось?
    • Требуется упорядочение результата
  • Как добиться?
    • sort(S.AP hash G.Prog)
    • sort(G.Prog index S.GId)
    • sort(G.Prog) index S.GId

Основная литература

  • Дейт К. Введение в системы баз данных (глава 18)

Дополнительная литература