Systemy zarządzania bazami danych

Systemy zarządzania bazami danych

16. Strojenie zapytań

Oryginał: Shasha & Bonnet 216. Strojenie zapytań

Strojenie zapytaniaSELECT 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 to_number(to_char(t.DATE_TIME, 'SSSSS')) between 39600 and 82800 and t.OFFER_TYPE = 'Discount'

ORDER BY AMOUNTVALUE DESC, t.TABLE_SEATING ASC, upper(s.RESTAURANT_NAME) ASC,SORTTIME ASC, t.DATE_TIME ASC

Wykonanie jest zbyt wolne …

1) Jak wykonywane jest to zapytanie?2) Jak je przyśpieszyć?


Plan wykonania zapytaniaWynik 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)


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)


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

Ri2

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 T2O

dcz

yt p

ełn

y:

Za

pis

z w

ynik

do

ty

mcz

aso

wej

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

• Korzystaj z gwiazdowości zapytania (lub płatkośniegości)


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



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)



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 SUBSTRSELECT * 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 ssnumFROM employeeWHERE 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 ssnumFROM employee, techWHERE 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.ssnumFROM employee E, tech TWHERE E.manager = T.manager



• 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.deptFROM employee E, tech TWHERE E.manager = T.manager



• 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.ssnumFROM student S, employee E, tech TWHERE 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 employeeWHERE dept in (select dept from tech)

• Skorelowane podzapytanie z agregatem

SELECT ssnum FROM employee e1WHERE 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ów1. Połącz zawartość obu

klauzul FROM2. 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 ssnumFROM employee, techWHERE employee.dept = tech.dept


Przeformułowanie podzapytań nieskorelowanych bez agregatów

• Możliwe problemy z duplikatami– SELECT avg(salary)

FROM employeeWHERE manager in (select manager from tech)

– SELECT avg(salary)FROM employee, techWHERE 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 ssnumFROM employee e1WHERE salary = (SELECT avg(e2.salary) FROM employee e2, tech WHERE e2.dept = e1.dept AND e2.dept = tech.dept);



INSERT INTO tempSELECT avg(salary) as avsalary, employee.deptFROM employee, techWHERE employee.dept = tech.deptGROUP BY employee.dept;

SELECT ssnumFROM employee, tempWHERE salary = avsalary AND employee.dept = temp.dept



• Zapytanie: Znajdź pracowników działów tech, w których numfriends jest równe licznie pracowników ich działu

SELECT ssnumFROM employee e1WHERE numfriends = (SELECT COUNT(e2.ssnum)

FROM employee e2, techWHERE e2.dept = tech.dept AND e2.dept = e1.dept);



INSERT INTO tempSELECT COUNT(ssnum) as numcolleagues, employee.deptFROM employee, techWHERE employee.dept = tech.deptGROUP BY employee.dept;

SELECT ssnumFROM employee, tempWHERE 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 tempSELECT COUNT(ssnum) as numcolleagues, employee.deptFROM employee, techWHERE employee.dept = tech.deptGROUP BY employee.dept;

SELECT ssnumFROM 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 employeeWHERE salary >= 40000

SELECT ssnum, locationFROM tempWHERE 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’;


FROM employee

WHERE dept= ‘information systems’

GROUP BY dept;

• HAVING należy zarezerwować wyłącznie do selekcji po zagregowanych właściwościach grup


FROM employee

GROUP BY dept

HAVING count(ssnum) > 100;


Użycie perspektyw

CREATE VIEW techlocation ASSELECT ssnum, tech.dept, locationFROM employee, techWHERE employee.dept = tech.dept;

SELECT location

FROM techlocation

WHERE ssnum = 43253265;

• Optymalizator stara się rozwinąć definicję perspektywy w miejscu przed optymalizacją.

• Selekcja z techlocation będzie prawdopodobnie wykonana jako złączenie:

SELECT location

FROM employee, tech

WHERE employee.dept = tech.deptAND ssnum = 43253265;


Wpływ przeformułowania zapytania na wydajność

>10000

-10

0

10

20

30

40

50

60

70

80

Th

rou

gh

pu

t rat

io

SQLServer 2000

Oracle 8i

DB2 V7.1

Przykład wiodący100000 employee100000 students10 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?



• 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

zmaterializowaneCREATE MATERIALIZED VIEW

VendorOutstanding BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE

AS SELECT orders.vendor, sum(orders.quantity*item.price)FROM orders,itemWHERE

orders.itemnum = item.itemnumGROUP 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



• 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 tableWHERE 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 tableFOR insert ASSELECT *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 nrON accountFOR updateAS INSERT INTO RichdepositorFROM insertedWHERE inserted.balance > 50000;

• Bardziej wydajna implementacja

CREATE TRIGGER nr ON accountFOR updateAS if update(balance)

BEGININSERT INTO RichDepositorFROM inserted, deletedWHERE inserted.id = deleted.idAND inserted.balance > 500000AND deleted.balance < 500000

END

Systemy zarządzania bazami danych

Documents

Transcript of Systemy zarządzania bazami danych