Systemy zarządzania bazami danych
16. Strojenie zapytań
Strojenie zapytania
SELECT s.RESTAURANT_NAME, t.TABLE_SEATING, to_char(t.DATE_TIME,'Dy, Mon FMDD') AS THEDATE, to_char(t.DATE_TIME,'HH:MI PM') AS THETIME,to_char(t.DISCOUNT,'99') || '%' AS AMOUNTVALUE,t.TABLE_ID, s.SUPPLIER_ID, t.DATE_TIME,
to_number(to_char(t.DATE_TIME,'SSSSS')) AS SORTTIME FROM TABLES_AVAILABLE t, SUPPLIER_INFO s,
(SELECT s.SUPPLIER_ID, t.TABLE_SEATING, t.DATE_TIME, max(t.DISCOUNT) AMOUNT, t.OFFER_TYPE FROM TABLES_AVAILABLE t, SUPPLIER_INFO
WHERE t.SUPPLIER_ID = s.SUPPLIER_ID and (TO_CHAR(t.DATE_TIME, 'MM/DD/YYYY') !=
TO_CHAR(sysdate, 'MM/DD/YYYY') OR TO_NUMBER(TO_CHAR(sysdate, 'SSSSS')) < s.NOTIFICATION_TIME - s.TZ_OFFSET)
and t.NUM_OFFERS > 0 and t.DATE_TIME > SYSDATE and s.CITY = 'SF'
and t.TABLE_SEATING = '2'
and t.DATE_TIME between sysdate and (sysdate + 7)
and to_number(to_char(t.DATE_TIME, 'SSSSS')) between 39600 and 82800 and t.OFFER_TYPE = 'Discount‘
GROUP BY
s.SUPPLIER_ID, t.TABLE_SEATING, t.DATE_TIME, t.OFFER_TYP ) u
WHERE
t.SUPPLIER_ID = s.SUPPLIER_ID and u.SUPPLIER_ID = s.SUPPLIER_ID and t.SUPPLIER_ID = u.SUPPLIER_ID and t.TABLE_SEATING = u.TABLE_SEATING and t.DATE_TIME = u.DATE_TIME and t.DISCOUNT = u.AMOUNT and t.OFFER_TYPE = u.OFFER_TYPE
and (TO_CHAR(t.DATE_TIME, 'MM/DD/YYYY') !=
TO_CHAR(sysdate, 'MM/DD/YYYY') OR
TO_NUMBER(TO_CHAR(sysdate, 'SSSSS')) < s.NOTIFICATION_TIME - s.TZ_OFFSET) and t.NUM_OFFERS >
and t.DATE_TIME > SYSDATE and s.CITY = 'SF' and t.TABLE_SEATING = '2' and t.DATE_TIME between sysdate and (sysdate + 7) and
Wykonanie jest zbyt wolne …
1) Jak wykonywane jest to zapytanie?
2) Jak je przyśpieszyć?
Plan wykonania zapytania
Wynik działania EXPLAIN w Oracle
Operatory fizyczne
Metoda dostępu Szacunek kosztów
Execution Plan
---
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=165 Card=1 Bytes=106) 1 0 SORT (ORDER BY) (Cost=165 Card=1 Bytes=106)
2 1 NESTED LOOPS (Cost=164 Card=1 Bytes=106) 3 2 NESTED LOOPS (Cost=155 Card=1 Bytes=83)
4 3 TABLE ACCESS (FULL) OF 'TABLES_AVAILABLE' (Cost=72 Card=1 Bytes=28) 5 3 VIEW
6 5 SORT (GROUP BY) (Cost=83 Card=1 Bytes=34) 7 6 NESTED LOOPS (Cost=81 Card=1 Bytes=34)
8 7 TABLE ACCESS (FULL) OF 'TABLES_AVAILABLE' (Cost=72 Card=1 Bytes=24) 9 7 TABLE ACCESS (FULL) OF 'SUPPLIER_INFO' (Cost=9 Card=20 Bytes=200) 10 2 TABLE ACCESS (FULL) OF 'SUPPLIER_INFO' (Cost=9 Card=20 Bytes=460)
Operatory fizyczne
• Bloki zapytań
– Jeden blok dla każdego SELECT-FROM-WHERE- GROUPBY-ORDERBY – VIEW izoluje bloki
optymalizowane oddzielnie
• Kształt drzewa złączeń (skierowane w prawo, krzaczaste, …)
• Kolejność złączeń
• Algorytmy
– Sortowanie – Agregacja – Selekcja – Rzutowanie – Złączenie
• Nested Loops (NL)
• Sort-Merge (SMJ)
• Hash-Join (HJ)
Metoda dostępu
• Odczyt pełny
• Wyszukiwanie w indeksie
– Znajdź indeksy pasujące do wyrażeń w zapytaniu
– Wyizoluj stałe lub zakresy w zapytaniu – Wyszukaj w indeksie
Model kosztów
• Funkcja kosztu
– Koszt = w1 * koszt_io + w2 * koszt_cpu
– Przyjmujemy w2 = 0
• Każdy operator ma swoją formułę kosztu
– Zależy ona od algorytmu – Zależy od wielkości wejścia
(liczba krotek, liczba stron)
– Operatory są kompozycjonalne, więc potrzebujemy oszacowania wielkości wyniku każdego z nich
Ri
R Ri1
Ro
Ro
Reprezentacja zapytań
• Drzewo zapytania • Graf zapytania (QBE)
Reserves Sailors
sid=sid
bid=100
sname(w locie)
rating > 5
(Sort-Merge Join)
Reserves
Sailors
rating > 5 bid=100
sid=sid
Odczyt pełny:
Zapisz wynik do tymczasowej T2 Odczyt pełny: Zapisz wynik do tymczasowej T1
Reprezentacja zapytania
• Zapytanie jest dzielone na bloki
– Agregacja – Sortowanie
– SPJ (Select-Project-Join) – Dostęp do relacji
• Każdy blok jest reprezentowany i
optymalizowany oddzielnie
Schemat optymalizacji zapytań
• Idealnie: chcemy znaleźć najlepszy plan
• Praktycznie: unikamy złych planów
• Dwa podstawowe zagadnienia:
– Jaka jest przestrzeń przeszukiwania dla danego zapytania?
– Jak to przeszukiwanie zaimplementować?
• Algorytm przeszukiwania przestrzeni planów w celu znalezienia najtańszego planu (wg oszacowań)
• Jak szacować koszty planów?
Algorytmy przeszukiwania
Naiwny 1
– Wylicz wszystkie możliwe plany (>n!) – Wybierz najlepszy plan
– Nierealne
Naiwny 2
– Zamróź porządek relacji w zapytaniu – Przesuń selekcje przed złączenia
• Wiele więcej nie da się zrobić
– Prosta zagnieżdżona pętla dla każdego bloku
• Użyj indeksu, gdy masz
Algorytmy przeszukiwania, cd.
Półnaiwny
– Zamróź porządek relacji w zapytaniu – Przesuń selekcje przed złączenia
• Wiele więcej nie da się zrobić
– Zagnieżdżone pętle lub sortowanie i scalanie (SMJ)
– Drzewo skierowane w lewo lub w prawo
Algorytmy przeszukiwania, cd.
Zachłanny
– Z modelem kosztów
• Opartym na statystykach (rozmiary relacji, rozkłady wartości w kolumnach)
• Szacowanie kosztu operacji I/O dla każdej operacji
– Wybór kolejności złączeń metodą zachłanną
• Dla każdej tabeli zewnętrznej
– Znajdź najtańsze jej złączenie z pozostałymi tabelami – Powtarzaj dopóki nie dodasz wszystkich tabel
• Pozostaw najlepszy plan (drzewo skierowane w lewo)
Algorytmy przeszukiwania, cd.
Programowanie dynamiczne (System R)
• Wyliczamy w N krokach (złączamy N relacji):
– Krok 1: Znajdź najlepszy plan dostępu dla każdej relacji.
– Krok 2: Znajdź najlepszy sposób złączenia planów dla 1 relacji z innymi relacjami (otrzymujemy wszystkie plany dla 2 relacji)
– Krok N: Znajdź najlepszy plan złączenia wyniku planów dla (N–1) relacji z relacją N-tą (otrzymujemy wszystkie plany dla N relacji)
• Dla każdego zbioru relacji mamy:
– Najtańszy plan w ogóle
– Najtańszy plan dla każdego interesującego porządku krotek
Monitorowanie zapytań
• Dwa sposoby identyfikacji długich zapytań:
– Długie zapytanie żądające zbyt wiele dostępów do dysku (np. zapytanie punktowe robiące
odczyt pełny)
– Plan wybrany przez optymalizator nie korzysta najbardziej obiecujących indeksów
Przeformułowanie zapytania
• Ta metodą strojenia zapytania ma efekty całkowicie lokalne
– Dodanie indeksu, zmiana schematu,
modyfikacja parametrów transakcji mają efekty globalne i są potencjalnie szkodliwe
– Przeformułowanie zapytania ma wpływ tylko na to zapytanie
Wiodący przykład
• Employee(ssnum, name, manager, dept, salary, numfriends)
– Indeks pogrupowany na ssnum
– Niepogrupowane indeksy na (1) name i (2) dept – ssnum jest kluczem
• Student(ssnum, name, degree_sought, year)
– Indeks pogrupowany na ssnum
– Niepogrupowany indeks na (1) name – ssnum jest kluczem
• Tech(dept, manager, location)
– Indeks pogrupowany na dept – dept jest kluczem
Techniki przepisywania zapytań
• Użycie indeksu
• Eliminacja DISTINCTs
• Poprawa i/lub eliminacja (skorelowanych) podzapytań
• Użycie tabel tymczasowych
• Warunki złączenia
• Użycie HAVING
• Użycie zwykłych perspektyw
• Użycie perspektyw zmaterializowanych
Użycie indeksu
• Wiele optymalizatorów nie użyje indeksów w otoczeniu
– Operatorów arytmetycznych
WHERE salary/12 >= 4000;
– Wyrażeń z SUBSTR
SELECT * FROM employee
WHERE SUBSTR(name, 1, 1) = ‘G’;
– Porównań numerycznych pól różnych typów – Porównań z NULL
Usuń zbędne DISTINCT
• Zapytanie: Znajdź pracowników departamentu information systems. Nie może być w wyniku duplikatów:
SELECT DISTINCT ssnum FROM employee
WHERE dept = ‘information systems’
• DISTINCT jest zbędny, ponieważ ssnum jest kluczem w employee, więc też i kluczem w podzbiorze relacji employee
Eliminacja DISTINCT
• Zapytanie: Znajdź pracowników
departamentów technicznych. Nie może być w wyniku duplikatów:
SELECT DISTINCT ssnum FROM employee, tech
WHERE employee.dept = tech.dept
• Czy DISTINCT jest potrzebny?
Także i tu zbędny
• Skoro dept jest kluczem tabeli tech, każdy rekord employee zostanie złączony z co
najwyżej jednym rekordem tabeli tech
• Skoro ssnum jest kluczem employee,
DISTINCT i tu jest zbędny.
Osiąganie
• Zależność między DISTINCT, kluczami i złączeniami można uogólnić
• Tabela T jest uprzywilejowana, wtw. gdy kolumny wypisywane przez SELECT zawierają klucz T
• Niech R będzie tabelą niekoniecznie
uprzywilejowaną. Jeśli R jest złączana równościowo po swoim kluczu z tabelą S, to powiemy, że R
osiąga S.
• Domknijmy przechodnio relację osiągania: jeśli R1 osiąga R2 i R2 osiąga R3, to R1 osiąga R3
Osiąganie – główne twierdzenie
• Zapytanie nie zwróci żadnych duplikatów (nawet bez DISTINCT), jeśli każda tabela z klauzuli FROM
– jest uprzywilejowana lub
– osiąga co najmniej jedną tabelę uprzywilejowaną
Osiąganie – szkic dowodu
• Jeśli każda relacja jest uprzywilejowana, to na pewno nie ma duplikatów
– Klucze tych relacji są wypisywane przez klauzulę SELECT
• Przypuśćmy, że pewne relacja T nie jest uprzywilejowana, ale osiąga pewną relację
uprzywilejowaną R. Wtedy warunki łączące T z R zapewniają, że każda kombinacja rekordów
uprzywilejowanych jest łączona z co najwyżej jednym rekordem T
Osiąganie – przykład 1
• Ten wiersz tabeli E może pasować do kilku
rekordów tabeli T (manager nie jest kluczem T), więc ssnum tego rekordu E może pojawić się kilka razy
• Tabela T nie osiąga uprzywilejowanej relacji
employee (nie ma złączenia po kluczu głównym E)
SELECT E.ssnum
FROM employee E, tech T
WHERE E.manager = T.manager
Osiąganie – przykład 2
• Każde wystąpienie wartości ssnum znajdzie się w towarzystwie innego T.dept, ponieważ dept jest kluczem T
• Obie relacje są uprzywilejowane, więc duplikatów nie będzie
SELECT E.ssnum, T.dept FROM employee E, tech T
WHERE E.manager = T.manager
Osiąganie – przykład 3
• Relacja S jest uprzywilejowana
• Relacja E nie osiąga S (name nie jest kluczem E)
• DISTINCT jest niezbędny, jeśli chcemy uniknąć duplikatów
SELECT S.ssnum
FROM student S, employee E, tech T WHERE S.name = E.name
AND E.dept = T.dept;
Klasyfikacja podzapytań
• Nieskorelowane
podzapytanie z agregatem
SELECT ssnum FROM employee WHERE salary >
(select avg(salary) from employee)
• Nieskorelowane
podzapytanie bez agregatu
SELECT ssnum FROM employee WHERE dept in
(select dept from tech)
• Skorelowane podzapytanie z agregatem
SELECT ssnum FROM employee e1 WHERE salary =
(SELECT avg(e2.salary) FROM employee e2, tech WHERE e2.dept = e1.dept AND e2.dept = tech.dept)
• Skorelowane podzapytanie bez agregatu (rzadki przypadek)
Przeformułowanie podzapytań nieskorelowanych bez agregatów
1. Połącz zawartość obu klauzul FROM
2. Połącz spójnikiem AND klauzule
WHERE zastępując operator IN
równością
3. Pozostaw klauzulę SELECT bloku
zewnętrznego
SELECT ssnum FROM employee WHERE dept in
(select dept from tech)
SELECT ssnum
FROM employee, tech
WHERE employee.dept = tech.dept
Przeformułowanie podzapytań nieskorelowanych bez agregatów
• Możliwe problemy z duplikatami
– SELECT avg(salary) FROM employee
WHERE manager in (select manager from tech) – SELECT avg(salary)
FROM employee, tech
WHERE employee.manager = tech.manager
• Drugie zapytanie może uwzględniać rekord employee kilka razy, jeśli jest managerem kilku działów
• Rozwiązaniem może być stworzenie tabeli
tymczasowej (lub inline view), które ma DISTINCT i eliminuje duplikaty
Przeformułowanie podzapytań skorelowanych
• Zapytanie: znajdź pracowników działów tech,
którzy zarabiają dokładnie tyle co średnie zarobki swoich działów
SELECT ssnum
FROM employee e1 WHERE salary =
(SELECT avg(e2.salary) FROM employee e2, tech
WHERE e2.dept = e1.dept AND e2.dept = tech.dept);
Przeformułowanie podzapytań skorelowanych
INSERT INTO temp
SELECT avg(salary) as avsalary, employee.dept FROM employee, tech
WHERE employee.dept = tech.dept GROUP BY employee.dept;
SELECT ssnum
FROM employee, temp WHERE salary = avsalary
AND employee.dept = temp.dept
Przeformułowanie podzapytań skorelowanych
• Zapytanie: Znajdź pracowników działów tech, w których numfriends jest równe licznie
pracowników ich działu
SELECT ssnum
FROM employee e1 WHERE numfriends =
(SELECT COUNT(e2.ssnum) FROM employee e2, tech
WHERE e2.dept = tech.dept AND e2.dept = e1.dept);
Przeformułowanie podzapytań skorelowanych
INSERT INTO temp
SELECT COUNT(ssnum) as numcolleagues, employee.dept FROM employee, tech
WHERE employee.dept = tech.dept GROUP BY employee.dept;
SELECT ssnum
FROM employee, temp
WHERE numfriends = numcolleagues AND employee.dept = temp.dept;
• Czy widzisz już sławną pluskwę COUNT?
Pluskwa COUNT
• Helena nie jest z działu technicznego
• W oryginalnym zapytaniu liczba przyjaciół
Heleny zostanie porównana z licznością zbioru pustego (zero). Jeśli nie ma ona przyjaciół,
znajdzie się w wyniku selekcji.
• W nowym zapytaniu Helena się nie znajdzie w tabeli tymczasowej, bo nie pracuje w dziale
technicznym
• To jest ograniczenie przeformułowania podzapytań skorelowanych z COUNT
• Czy na pewno? Jakieś lekarstwa?
Przeformułowanie podzapytań skorelowanych – lekarstwo
• Złączenie zewnętrzne...
INSERT INTO temp
SELECT COUNT(ssnum) as numcolleagues, employee.dept FROM employee, tech
WHERE employee.dept = tech.dept GROUP BY employee.dept;
SELECT ssnum
FROM employee LEFT JOIN temp ON (employee.dept = temp.dept) WHERE numfriends = NVL(numcolleagues, 0);
Nadużywanie tabel tymczasowych
• Zapytanie: Znajdź pracowników działu information systems z ich lokalizacją, którzy zarabiają więcej niż 40000$.
INSERT INTO temp SELECT *
FROM employee
WHERE salary >= 40000 SELECT ssnum, location
FROM temp
WHERE temp.dept = ‘information systems’
• Może selekcje lepiej zrobić w odwrotnej kolejności?
• Tabela tymczasowa ogłupia/oślepia optymalizator
Warunki złączenia
• Dobrze, żeby warunki złączenia dotyczyły indeksów pogrupowanych
– Nie trzeba sortować przed złączaniu przez scalanie (SMJ)
– Przyspieszenie dostępu wielopunktowego przy złączeniu iteracyjnym z indeksem (INLJ)
• Lepiej złączenia robić po atrybutach
liczbowych niż napisowych
Użycie HAVING
• Nie używaj HAVING, gdy można WHERE.
SELECT avg(salary) as avgsalary, dept FROM employee
GROUP BY dept
HAVING dept = ‘information systems’;
SELECT avg(salary) as avgsalary, dept FROM employee
WHERE dept= ‘information systems’
GROUP BY dept;
• HAVING należy
zarezerwować wyłącznie do selekcji po zagregowanych właściwościach grup
SELECT avg(salary) as avgsalary, dept FROM employee
GROUP BY dept
HAVING count(ssnum) > 100;
Użycie perspektyw
CREATE VIEW techlocation AS
SELECT ssnum, tech.dept, location FROM employee, tech
WHERE employee.dept = tech.dept;
SELECT location FROM techlocation
WHERE ssnum = 43253265;
• Optymalizator stara się rozwinąć definicję perspektywy w miejscu
• Selekcja z techlocation będzie prawdopodobnie wykonana jako
złączenie:
SELECT location
FROM employee, tech
WHERE employee.dept = tech.dept AND ssnum = 43253265;
Wpływ przeformułowania zapytania na wydajność
>10000
-10 0 10 20 30 40 50 60 70 80
Throughput ratio
SQLServer 2000 Oracle 8i
DB2 V7.1
Przykład wiodący 100000 employee 100000 students 10 tech
Materializacja agregatów
• Księgowość co 20 minut wysyła
zapytania o:
– Łączną wartość
zamówionych towarów każdego dostawcy
– Łączną wartość
towarów zamówionych dla każdego sklepu
• Schemat oryginalny:
Ordernum(ordernum, itemnum, quantity, purchaser, vendor) Item(itemnum, price)
• Ordernum i Item mają indeks pogrupowany na itemnum
• Te zapytania o łączne wartości są kosztowne.
Dlaczego?
Materializacja agregatów
• Dodaj tabele:
– VendorOutstanding(vendor, amount), z łączną wartością zamówień towarów
dostawcy. Dodaj indeks pogrupowany na vendor
– StoreOutstanding(purchaser, amount) z łączną wartością towarów zamówionych w poszczególnych sklepach.
Dodaj indeks pogrupowany na purchaser
• Każda modyfikacja zamówienia powoduje modyfikację tych dwóch
nadmiarowych tabel (można użyć wyzwalaczy, by
zaimplementować to jawnie, lub perspektywy
zmaterializowanej by
modyfikacje były niejawne
• Kompromis między czasem modyfikacji i czasem
zapytania
Perspektywy zmaterializowane
• Oracle ma perspektywy zmaterializowane
CREATE MATERIALIZED VIEW VendorOutstanding
BUILD IMMEDIATE REFRESH COMPLETE
ENABLE QUERY REWRITE AS
SELECT orders.vendor,
sum(orders.quantity*item.price) FROM orders,item
WHERE
orders.itemnum = item.itemnum
GROUP BY by orders.vendor;
• Niektóre opcje:
– BUILD immediate/deferred – REFRESH complete/fast
– ENABLE QUERY REWRITE
• Główne właściwości:
– Przezroczyste utrzymywanie agregatów
– Przezroczyste użycie przez optymalizator kosztowy
• To optymalizator a nie programista dokonuje przepisania
Materializacja agregatów
• SQLServer na Windows2000
• Zapytanie i dane działu księgowości
• 1000000 zamówień, 1000 produktów
• Utrzymywanie agregatu za pomocą wyzwalaczy
• Ten eksperyment wyraźnie na korzyść
utrzymywania agregatów
pect. of gain with aggregate maintenance
21900
31900
- 62.2 -5000
0 5000 10000 15000 20000 25000 30000 35000
insert vendor total store total
Wyzwalacze
• Wyzwalacz to składowana procedura (ciąg
poleceń SQL przechowywanych na serwerze bazy danych), który jest wykonywany w wyniku
zdarzenia
• Zdarzenia są dwojakiego rodzaju
– Czasowe
– Modyfikacje: wstawienia, zmiany, usunięcia
• Wyzwalacz wykonuje się w ramach transakcji zawierającej zdarzenie odpalające
Dlaczego używać wyzwalaczy?
• Wyzwalacz wykona się niezależnie od aplikacji, która go odpala
– To sprawia, że wyzwalacze są wygodne do kontroli lub odwracania efektów podejrzanych akcji, np.
modyfikacja wynagrodzenia w sobotę
• Wyzwalacze mogą utrzymywać więzy
integralności, np. integralność referencyjną lub materializować agregaty
• Wyzwalacze znajdują się na serwerze bazy danych, więc są niezależne od aplikacji
Życie bez wyzwalaczy
• Aplikacja musi za każdym razem wyliczać agregaty
• Aktywne czekanie na zdarzenie:
– SELECT * FROM table
WHERE inserttime >= lasttimelooked + 1;
– Zmieniaj lasttimelooked na podstawie czasu lokalnego.
• Bardzo intensywne aktywne czekanie powoduje rywalizację o zamki
• Mało intensywne czekanie może spowodować przeoczenie zdarzenia.
Wyzwalacze są pomocne
• Implementują czekanie „na przerwaniach”
– CREATE TRIGGER todisplay ON table
FOR insert AS SELECT *
FROM inserted
• Unikamy konfliktów współbieżności i
dostarczamy dane natychmiast, gdy się
pojawią
Problemy z wyzwalaczami
• W obecności wyzwalaczy, żadnej
modyfikacji nie można rozważać w izolacji, bo założone na nią wyzwalacze mogą
spowodować dalsze modyfikacje
• Interakcja między wyzwalaczami może być trudna do uchwycenia, gdy ich liczba rośnie
– Modyfikacja danych, może odpalić kilka
wyzwalaczy: które to są? w jakiej kolejności?
Strojenie wyzwalaczy
• Wyzwalacz wykonuje się tylko wtedy, gdy zaszła dana operacja. Programista może
wykorzystać tę strategię by zasugerować specyficzną kolejność ich wykonania
– Indeksy niepogrupowane przyspieszają
sprawdzenie integralności referencyjnej, gdy w czasie wykonania rekordy są wkładane
pojedynczo
Strojenie wyzwalaczy
• Łatwo napisać wyzwalacze, które wykonuje się za często albo zwracają one zbyt wiele wierszy: zapisz wiersz w
tabeli Richdepositor za każdym razem, gdy saldo
konta rośnie powyżej 50000$.
CREATE TRIGGER nr ON account
FOR update AS
INSERT INTO Richdepositor FROM inserted
WHERE inserted.balance > 50000;
• Bardziej wydajna implementacja
CREATE TRIGGER nr ON account FOR update
AS if update(balance) BEGIN
INSERT INTO RichDepositor FROM inserted, deleted
WHERE inserted.id = deleted.id AND inserted.balance > 500000 AND deleted.balance < 500000 END
