$ \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)

Запрос

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

Планы запросов

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

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

  • План 5. Students(GId)
    • $π_{Name}(S ⋈ σ_{Name=M3438}(G))$
    • $10^3 + (3 + 20) + 20 ≈ 10^3$
  • План 6. Groups(Name), Students(GId)
    • $π_{Name}(S ⋈ σ_{Name=M3438}(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=M3438}(σ_{S.GId=G.GId}(S × G)))$$π_{FirstName}(σ_{Name=M3438 ∧ 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=M3439}(π_{FirstName, Name}(S × G)))$$π_{FirstName}(π_{FirstName, Name}(σ_{Name=M3439}(S × G)))$$π_{FirstName}(σ_{Name=M3439}(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}(Students)$, если $Students.GId ⊂ Groups.GId$

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

  • Ограничение
    • У всех, кто получает стипендию все оценки $≥ 60$
    • check not HasScolarship or 60 <=
        all (select Points from Points where Points.SId = Id)
      
  • Запрос
    • Оценки стипендиатов группы M3438 по СУБД
    • select Points from Students natural join Points
      where HasScolarship and CId = 10 and GId = M3438
      
    • $σ_{HasScolarship ∧ CId = 10}(Students ⋈ Points)$
  • Оптимизированный запрос
    • $σ_{GId=M3438 ∧ 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 Student 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
    • Зависит от вида формулы трудозатрат
    • + поиск по хешу
    • Трудоёмкость $O(n^2)$
  • Алгоритм AB
    • Смягчает ограничения на вид формулы
    • Построен на базе KBZ

Оценка размера и распределения

Содержание

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

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

Статистические характеристики

Содержание

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

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

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

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

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

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

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

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

Оценка операций

Содержание

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

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

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

  • Оценочная функция
    • $|Out| = Sel(expr)|In|$
  • Сравнения
    • Селективность из распределения
    • Интервальные оценки
    • Сравнение с другим атрибутом
  • Составные выражения
    • $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|)$

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

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

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

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

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

Содержание

Постановка задачи

Содержание

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

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

Полный просмотр 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$
    • Стоимость – $3 + 90 = 96$

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

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

Поиск по (Groups.GId)

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

Поиск по (Groups.Department)

  • Статистика индекса
    • Записей – $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 Поиск по (Department)$5$

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

Содержание

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

Прав \ лев S.GIdS.APG.GIdG.Dept
Просмотр S.GId merge loops
Поиск по S.GId index index
Поиск по S.AP loops hash
Просмотр G.GIdmerge loops
Поиск по G.GId index index
Поиск по G.Dept 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.D)

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

(G.D) 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.D)$111$
(G.D) index (S.GId)$97$

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

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

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

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

Литература

Содержание

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

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

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