Принципы построения и работы баз данных Тема 7

Принципы построения и работы баз данных Тема 7: Оптимизация запросов Принципы построения БД Тема 7 1

Оптимизация запроса Генерация и сравнение планов Запрос Генерация Отсечение Планы x x Оценка стоим. Стоимость Выбор минимума Принципы построения БД Тема 7 2

Для генерации плана рассмотрим: • Преобразование выражений реляционной алгебры (для получения различного порядка соединения) • Использование существующих индексов • Динамическое построение индексов и сортировка • Детали реализации: например - Алгоритм соединения - Управление памятью - Параллельная обработка Принципы построения БД Тема 7 3

Оценивание операций IO: • Подсчет числа дисковых блоков, которые должны быть прочитаны (или записаны) для выполнения плана запроса • Для оценки стоимости мы можем использовать дополнительные параметры B(R) = число блоков, содержащих кортежи R Т(R) = число кортежей в R S(R) = часть блока, занимаемая 1 кортежем M = число доступных блоков опер. памяти HT(i) = число уровней в индексе i LB(i) = число листовых блоков индекса i Принципы построения БД Тема 7 4

Кластеризованный индекс Индекс, позволяющий читать кортежи в порядке, соответствующем физическому порядку A 10 15 17 Индекс по A Принципы построения БД 19 35 37 Тема 7 5

Различные понятия кластеризации • Кластеризованные файлы …. . • Кластеризованное отношение R 1 R 2 R 3 R 4 R 5 R 7 R 8 …. . • Кластеризованный индекс R 1 R 2 S 1 S 2 Принципы построения БД R 3 R 4 S 3 S 4 Тема 7 6

Пример R 1 R 2 по общему атрибуту C T(R 1) = 10, 000 T(R 2) = 5, 000 S(R 1) = S(R 2) = 1/10 блока Доступная память = 101 блок Стоимость: число операций IO (без записи результата) Это может быть не лучший способ для определения стоимости • Игнорирование затрат CPU • Игнорирование времени • Игнорирование требования двойной буферизации Принципы построения БД Тема 7 7

Альтернативы • преобразования: R 1 R 2, R 2 R 1 • Алгоритмы соединения: – Итеративное соединение (вложенные циклы) – Соединение-слияние – Индекс-соединение – Хэш-соединение Итеративное соединение (концептуально) для каждого r R 1 выполнить для каждого s R 2 выполнить если r. C = s. C вывести пару r, s Принципы построения БД Тема 7 8

Соединение-слияние (концептуально) (1) если R 1 и R 2 не отсортированы, сортировать (2) i 1; j 1; While (i T(R 1)) & (j T(R 2)) do if R 1[ i ]. C = R 2[ j ]. C then output. Tuples else if R 1[ i ]. C > R 2[ j ]. C then j j+1 else if R 1[ i ]. C < R 2[ j ]. C then i i+1 Процедура Output. Tuples While (R 1[ i ]. C = R 2[ j ]. C) & (i T(R 1)) do { jj j; while (R 1[ i ]. C = R 2[ jj ]. C) & (jj T(R 2)) do { output pair R 1[ i ], R 2[ jj ]; jj jj+1 } i i+1 } Принципы построения БД Тема 7 9

Пример i 1 2 3 4 5 R 1[ I ]. C 10 20 20 30 40 j 1 2 3 4 5 6 52 Принципы построения БД R 2[ j ]. C 5 20 20 30 30 50 7 Тема 7 10

Индекс-соединение (концептуально) для каждого r R 1 выполнить { X index (R 2, C, r. C) Пусть имеется индекс по R 2. C для каждого s X выполнить вывести пару r, s } Замечание: после X index(rel, attr, value) X = множество кортежей отношения rel с attr = value Принципы построения БД Тема 7 11

Хэш-соединение (концептуально) – Хэш-функция h, диапазон значений 0 k – Ячейки для R 1: G 0, G 1, . . . Gk – Ячейки для R 2: H 0, H 1, . . . Hk Алгоритм (1) Хэшировать кортежи R 1 в ячейки G (2) Хэшировать кортежи R 2 в ячейки H (3) For i = 0 to k do соединить кортежи из ячеек Gi, Hi Принципы построения БД Тема 7 12

Простой пример R 1 R 2 2 5 4 4 3 12 5 3 8 13 9 8 11 14 Принципы построения БД хэш: чет/нечет ячейки чет: 248 R 1 нечет: 3 5 9 Тема 7 4 12 8 14 R 2 5 3 13 11 13

Факторы, влияющие на производительность (1) Кортежи отношения хранятся физически рядом? (2) Отношения отсортированы по атрибуту соединения? (3) Существуют индексы по атрибуту соединения? Принципы построения БД Тема 7 14

Пример 1(a) Итеративное соединение R 1 R 2 • Отношения не кластеризованы • Вспомним T(R 1) = 10, 000 T(R 2) = 5, 000 S(R 1) = S(R 2) =1/10 block MEM=101 blocks Стоимость: для каждого кортежа из R 1: { Читать блок с кортежем + читать R 2 } Cтоимость =10, 000 [1+5000]=50, 010, 000 операций IO Принципы построения БД Тема 7 15

• Можно ли сделать лучше? Используем нашу оперативную память (1) Читать порцию из 100 блоков R 1 (2) Читать все отношение R 2 (используя один оставшийся блок) + соединить всю порцию R 1 c блоком R 2 (3) Повторить для остальных 99 порций R 1 Стоимость: для каждой порции R 1: Читать порцию: 100 операций IO Читать R 2: 5000 операций IO Итого на порцию: 5100 Итого на все 100 порций 5100*100 =510000 IO Принципы построения БД Тема 7 16

• Можно ли сделать еще лучше? Попробуем другой порядок (с памятью): R 2 R 1 Стоимость: для каждой порции R 2: Читать порцию: 100 операций IO Читать R 1: 10000 операций IO Итого на порцию: 10100 Итого на все 50 порций 10100*50 =505000 IO Принципы построения БД Тема 7 17

Пример 1(б) Итеративное соединение R 2 R 1 • Отношения кластеризованы • B(R 1) = 1000, B(R 2) = 500 Стоимость: для каждой порции R 2: Читать порцию: 100 операций IO Читать R 1: 1000 операций IO Итого на порцию: 1100 Итого на все 5 порций 1100*50 =5500 IO Стоимость итеративного соединения (с использованием памяти) R 1 R 2 = 6000 IO Принципы построения БД Тема 7 18

Пример 1(в) Соединение-слияние • Оба отношения R 1, R 2 упорядочены по C; отношения кластеризованы Память R 1 …. . R 1 R 2 …. . R 2 Общая стоимость: стоимость чтения R 1 + стоимость чтения R 2 = 1000 + 500 = 1, 500 операций IO Принципы построения БД Тема 7 19

Пример 1(д) Соединение-слияние • R 1, R 2 не упорядочены по C, но кластеризованы --> Нужно сначала отсортировать R 1, R 2 …. Как? Принципы построения БД Тема 7 20

Один способ сортировки: Сортировка-слияние (i) Для каждой порции из 100 блоков R: - Прочитать порцию - Сортировать в памяти - Записать на диск отсорт. R 2 Принципы построения БД . . . R 1 Память Тема 7 порции 21

(ii) Читать все порции + слить + записать на диск Отсорт. файл Память Принципы построения БД . . . отсорт. Тема 7 порции 22

Стоимость: Сортировка Каждый блок (кортеж) читается, записывается, следовательно so. . . Стоимость сортировки R 1: 4 x 1, 000 = 4, 000 Стоимость сортировки R 2: 4 x 500 = 2, 000 Общая стоимость соединения-слияния = стоимость сортировки + стоимость соединения = 6, 000 + 1, 500 = 7, 500 IO Однако: Стоимость итеративного соединения = 5, 500, т. е. соединение-слияние не оправдано! Принципы построения БД Тема 7 23

Предположим, число блоков увеличено в 10 раз: R 1 = 10, 000 блоков, R 2 = 5, 000 блоков, кластеризованы, но не упорядочены по C Стоимость итеративного соединения (с памятью): 50 x (100+10, 000) = 50 x 10, 100 = 505, 000 операций IO Стоимость соединения-слияния (с сортировкой): 5*(10, 000+5, 000) = 75, 000 операций IO Соединение-слияние почти в 7 раз лучше! Принципы построения БД Тема 7 24

Сколько памяти нужно для сортировки-слияния? Например: Имеется отношение с 1000 блоками и 10 блоков памяти 10 . . . R 1 Принципы построения БД 100 порций для слияния потребуется 100 блоков! 10 блоков недостаточно Тема 7 25

В общем случае: Пусть имеется k блоков памяти и x блоков, которые необходимо отсортировать. Число порций = (x/k), размер порции = k. Число порций должно быть <= число буферов для слияния (точнее число буферов – 1, 1 буфер необходим для вывода, но нам нужна примерная оценка) Следовательно, (x/k) k или k 2 x, k sqrt(x) В нашем примере, R 1 имеет 1000 blocks, k 31. 62 R 2 имеет 500 blocks, k 22. 36 Нужно, по крайней мере, 32 блока Принципы построения БД Тема 7 26

Можно ли улучшить соединение-слияние? Подсказка: действительно ли нам нужны полностью отсортированные файлы? R 1 соединение? R 2 отсортированные порции Принципы построения БД Тема 7 27

Стоимость улучшенного соединения-слияния: Cтоимость = чтение R 1 + запись R 1 в отсорт. порции + чтение R 2 + запись R 2 отсорт. порции + чтение R 1, R 2 для соединения = 2000 + 1500 = 4500 --> Какие требования к памяти? Принципы построения БД Тема 7 28

Пример 1(д) Индекс-соединение Предположим, что R 2 кластеризовано, не упорядочено Предположим, что имеется индекс по R 1. C с 2 уровн. Предположим, что индекс по R 1. C умещается в памяти Стоимость: Чтение R 2: 500 IO Для каждого кортежа из R 2: - поиск в индексе по R 1. C – нет IO - если значение найдено, читать кортеж R 1: 1 IO Принципы построения БД Тема 7 29

Каково ожидаемое число соответствующих кортежей в R 1? (а) если R 1. C – первичный ключ, R 2. C – внешний ключ, то ожидаемое число = 1 (б) если V(R 1, C) = 5000, T(R 1) = 10000 с равномерным распределением, то ожидаемое число = 10, 000/5, 000 = 2 (в) если DOM(R 1, C)=1000000, T(R 1) = 10, 000 с альтернативным предположением, то ожидаемое число = 10000 =_1 1000000 100 Принципы построения БД Тема 7 30

Общая стоимость индекс-соединения (a) Общая стоимость = 500+5000*(1)*1 = 5, 500 (б) Общая стоимость = 500+5000*(2)*1 = 10, 500 (в) Общая стоимость = 500+5000*(1/100)*1=550 Что, если индекс не умещается в памяти? Например: пусть размер индекса R 1. C - 200 блоков • В памяти хранится корень + 99 листовых узлов • Ожидаемая цена каждого поиска увеличится на E = (0)*99 + (1)*100 = 0. 5 200 Принципы построения БД Тема 7 31

Общая стоимость (включая поиск по индексу) = 500+5000*(поиск + получить запись) = 500+5000*(0. 5+2) [равномерное распределение] = 500+12, 500 = 13, 000 (случай б) Для случая (в): = 500+5000*(0. 5*1 + (1/100)*1) = 500+2500+50 = 3050 IO Принципы построения БД Тема 7 32

не кластеризов. итер. соед. R 2 соед. -слияние сорт. +соед. -сл. R 1. C индекс R 2. C индекс R 1 55, 000 (лучшее) _______ кластеризов. До сих пор итер. соед. R 2 соед. -слияние сорт. +соед. -сл. R 1. C индекс R 2. C индекс R 1 5500 1500 7500 4500 5500 3050 550 ____ Принципы построения БД Тема 7 33

Пример 1(е) хэш-соединение • R 1, R 2 кластеризованы (неупорядочены) использовать 100 ячеек Читать R 1, хэшировать, + запись ячеек . . . R 1 100 10 blocks Принципы построения БД Тема 7 34

-> То же для R 2 -> Читать одну R 1 ячейку; построить хэш-таблицу в памяти -> Читать соответствующую ячейку R 2 + провести поиск R 2. . . R 1 память Повторить для всех ячеек Принципы построения БД Тема 7 35

Стоимость: “генерация ячеек : ” чтение R 1 + запись чтение R 2 + запись соединение: чтение R 1, R 2 стоимость = 3 x [1000+500] = 4500 Общая Это приблизительная оценка потому, что ячейки будут разного размера, их надо будет округлять до блоков Минимальные требования к памяти Размер ячейки для R 1 = (x/k), где k = число буферов памяти x = число блоков R 1 Следовательно. . . (x/k) < k, k > sqrt(x), Нужно: k+1 буферов памяти Принципы построения БД Тема 7 36

Всп. прием: хранить некоторые ячейки в памяти Например, k=33 R 1 ячеек размером 31 блок, 2 ячейки хранятся в памяти полностью, остальные - по 1 блоку память ввод G 0 G 1 31 . . . R 1 Используемая память: G 0 31 блок G 1 31 блок Вывод 33 -2 блока R 1 ввод 1 Итого 94 блока 6 блоков остается!! 33 -2=31 Гибридное хэш-соединение Принципы построения БД Тема 7 37

Далее: создать ячейки для R 2 – R 2 ячейки = 500/33 = 16 блоков – 2 ячейки R 2 соединяются с G 0, G 1 память G 0 16 . . . G 1 R 2 ячейки Принципы построения БД R 1 ячейки 31 33 -2=31 Тема 7 . . . R 2 ввод 33 -2=31 38

В заключение: соединение остальных ячеек – Для каждой пары соответствующих ячеек: • Читать одну из них в память • Соединить с другой ячейкой пары память one R 1 buffer Принципы построения БД 16 R 1 ячейки 31 33 -2=31 Тема 7 . . . Gi R 2 ячейки . . . ответ вывод 1 полная ячейка R 2 33 -2=31 39

Стоимость • Создание ячеек R 1 = 1000+31 31 = 1961 • Для создания ячеек R 2, записать только 31 ячейку: поэтому цена = 500+31 16 = 996 • Для соединения записанных ячеек (2 из 33 уже обработаны) читать 31 31+31 16 = 1457 блоков Общая стоимость = 1961+996+1457 = 4414 Принципы построения БД Тема 7 40

Сколько ячеек держать в памяти? память R 1 ввод память G 0 R 1 G 1 или. . . ввод G 0 ? Ответ – в книге Принципы построения БД Тема 7 41

Другой вспом. прием для хэш-соединения: • Записывать в ячейки только пары <значение, указатель> • Когда находится соответствие на фазе соединения, необходимо получить кортеж, используя указатель Принципы построения БД Тема 7 42

• Для иллюстрации стоимости предположим: – 100 <val, ptr> пар/блок – Ожидаемое число кортежей в результате - 100 • Построить хэш-таблицу для R 2 в памяти 5000 кортежей 5000/100 = 50 блоков • Читать R 1 искать соответствие • Читать ~ 100 кортежей R 2 Общая стоимость читать R 2: читать R 1: получить кортежи R 2 Принципы построения БД Тема 7 500 100 1600 43

Кластеризованные R 1, R 2 До сих пор: итер. соед. -слияние сорт. +соед. -сл. R 1. C индекс R 2. C индекс построить R. C индекс построить S. C индекс хэш-соедин. + гибр. R 1 -первая + гибр. , R 2 - первая хэш-соедин. указат. Принципы построения БД Тема 7 5500 1500 7500 5500 550 _____ 4500+ 4414 _____ 1600 44

Итоги • Итеративное соединение подходит для “небольших” таблиц (относительно размера памяти) • Для соединений с условием равенства, где таблицы не упорядочены и не имеют индексов по атрибутам соединения, обычно лучший результат дает хэшсоединение • Соединение-слияние с сортировкой подходит при условии неравенстве (например, R 1. C > R 2. C) • Если таблица уже упорядочена, используется соединение-слияние • Если имеется индекс, может оказаться полезным (зависит от ожидаемого размера результата) Принципы построения БД Тема 7 45