Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00622 010413 11059654 na godz. na dobę w sumie
SQL. Optymalizacja - książka
SQL. Optymalizacja - książka
Autor: Liczba stron: 384
Wydawca: Helion Język publikacji: polski
ISBN: 83-7361-423-0 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> sql - programowanie
Porównaj ceny (książka, ebook, audiobook).

Instrukcje SQL są kluczowymi elementami typowych aplikacji bazodanowych, a więc efektywność ich wykonywania decyduje w głównym stopniu o wydajności samych aplikacji. Twórcy aplikacji bazodanowych i administratorzy baz danych często spędzają długie godziny w celu upewnienia się, że dostęp do danych istotnie odbywa się po najszybszych ścieżkach, czyli że plany wykonywania wyrażeń SQL są optymalne. Wiąże się z tym między innymi rozważanie wzajemnego związku między strukturą wyrażeń SQL a planami ich wykonywania.

Książka ta poświęcona jest jednemu z kluczowych aspektów tego związku, często niedocenianemu, aczkolwiek niezmiernie istotnemu -- wyborowi odpowiedniego planu wykorzystywanego przez określone zapytanie. Autor prezentuje matematyczną metodą optymalizacji wyrażeń SQL, opierającą się na dobrze zdefiniowanym algorytmie postępowania i prowadzącą do znajdowania optymalnych (lub niemal optymalnych) planów wykonania dla określonych wyrażeń; jest to naprawdę atrakcyjna alternatywa dla poszukiwań metodą prób i błędów, rzadko dającą optymalne rezultaty. Czytelnik znajdzie w niniejszej książce opis wielu szczegółowych zagadnień związanych z optymalizacją wyrażeń SQL i baz danych w ogólności, między innymi takich jak:

Treści poszczególnych rozdziałów towarzyszą ćwiczenia kontrolne, a całość wieńczy prezentacja zastosowania opisywanych koncepcji w (kompletnie opisanym) procesie optymalizowania konkretnej aplikacji.

Przyśpiesz działanie aplikacji -- zoptymalizuj dostęp do danych

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

IDZ DO IDZ DO PRZYK£ADOWY ROZDZIA£ PRZYK£ADOWY ROZDZIA£ SPIS TREĎCI SPIS TREĎCI SQL. Optymalizacja KATALOG KSI¥¯EK KATALOG KSI¥¯EK KATALOG ONLINE KATALOG ONLINE ZAMÓW DRUKOWANY KATALOG ZAMÓW DRUKOWANY KATALOG TWÓJ KOSZYK TWÓJ KOSZYK DODAJ DO KOSZYKA DODAJ DO KOSZYKA CENNIK I INFORMACJE CENNIK I INFORMACJE ZAMÓW INFORMACJE ZAMÓW INFORMACJE O NOWOĎCIACH O NOWOĎCIACH ZAMÓW CENNIK ZAMÓW CENNIK CZYTELNIA CZYTELNIA FRAGMENTY KSI¥¯EK ONLINE FRAGMENTY KSI¥¯EK ONLINE Wydawnictwo Helion ul. Chopina 6 44-100 Gliwice tel. (32)230-98-63 e-mail: helion@helion.pl Autor: Dan Tow T³umaczenie: Marek Pa³czyñski (rozdz. 1 –; 6, dod. C), Tomasz Pêdziwiatr (rozdz. 7 –; 10, dod. A, B) ISBN: 83-7361-423-0 Tytu³ orygina³u: SQL Tuning Format: B5, stron: 348 Instrukcje SQL s¹ kluczowymi elementami typowych aplikacji bazodanowych, a wiêc efektywnoġæ ich wykonywania decyduje w g³ównym stopniu o wydajnoġci samych aplikacji. Twórcy aplikacji bazodanowych i administratorzy baz danych czêsto spêdzaj¹ d³ugie godziny w celu upewnienia siê, ¿e dostêp do danych istotnie odbywa siê po najszybszych ġcie¿kach, czyli ¿e plany wykonywania wyra¿eñ SQL s¹ optymalne. Wi¹¿e siê z tym miêdzy innymi rozwa¿anie wzajemnego zwi¹zku miêdzy struktur¹ wyra¿eñ SQL a planami ich wykonywania. ka ta poġwiêcona jest jednemu z kluczowych aspektów tego zwi¹zku, czêsto niedocenianemu, aczkolwiek niezmiernie istotnemu — wyborowi odpowiedniego planu wykorzystywanego przez okreġlone zapytanie. Autor prezentuje matematyczn¹ metod¹ optymalizacji wyra¿eñ SQL, opieraj¹c¹ siê na dobrze zdefiniowanym algorytmie postêpowania i prowadz¹c¹ do znajdowania optymalnych (lub niemal optymalnych) planów wykonania dla okreġlonych wyra¿eñ; jest to naprawdê atrakcyjna alternatywa dla poszukiwañ metod¹ prób i b³êdów, rzadko daj¹c¹ optymalne rezultaty. Czytelnik znajdzie w niniejszej ksi¹¿ce opis wielu szczegó³owych zagadnieñ zwi¹zanych z optymalizacj¹ wyra¿eñ SQL i baz danych w ogólnoġci, miêdzy innymi takich jak: • Buforowanie danych i zarz¹dzanie tabelami • Indeksowanie, implementowanie indeksów i zwi¹zane z tym koszty • Filtrowanie zawartoġci tabel i jego zwi¹zek z indeksowaniem • Z³¹czenia tabel i metody ich realizacji • Analiza planów wykonywania zapytañ i zarz¹dzanie tymi planami w bazach danych Oracle, MS SQL Server i DB2 • Sporz¹dzanie diagramów zapytañ i wykorzystywanie ich do celów optymalizacji z³o¿onych zapytañ oraz do wyznaczania najlepszych planów wykonywania • Specjalne metody optymalizacji szczególnych przypadków, w których standardowe postêpowanie okazuje siê niewystarczaj¹ce: buforowanie wielokrotnie wydawanych zapytañ, ³¹czenie i upodobnianie zapytañ itp. Treġci poszczególnych rozdzia³ów towarzysz¹ æwiczenia kontrolne, a ca³oġæ wieñczy prezentacja zastosowania opisywanych koncepcji w (kompletnie opisanym) procesie optymalizowania konkretnej aplikacji. Spis treści Przedmowa .........................................................................................................................9 Wstęp .................................................................................................................................11 Rozdział 1. Wprowadzenie ...........................................................................................17 Po co optymalizować zapytania SQL? ...................................................l............................................18 Kto powinien zająć się optymalizacją?...................................................l............................................20 Dlaczego książka ta może być pomocna?...................................................l.......................................21 Dodatek ...................................................l...................................................l.............................................23 Gotowe rozwiązania..................................l...................................................l.........................................24 Rozdział 2. Podstawowe informacje o dostępie do danych ..................................25 Buforowanie danych ...................................................l...................................................l.......................26 Tabele...................................................l...................................................l.................................................29 Indeksy ...................................................l...................................................l..............................................32 Rzadziej wykorzystywane obiekty baz danych...................................................l.............................36 Ścieżki dostępu do pojedynczych tabel ...................................................l..........................................40 Wyznaczanie selektywności...................................................l...................................................l...........48 Złączenia ...................................................l...................................................l...........................................58 Rozdział 3. Przeglądanie i interpretacja planów wykonania zapytań...............67 Analiza planu wykonania zapytania bazy danych Oracle ...................................................l..........68 Analiza planu wykonania zapytania bazy danych DB2 ...................................................l..............79 Analiza planu wykonania zapytania bazy danych SQL Server...................................................l..92 6 Spis treści Rozdział 4. Zarządzanie planami wykonania zapytań .......................................101 Uniwersalne techniki zarządzania planem wykonania zapytania..............................................101 Zarządzanie planami wykonania zapytań w bazach danych Oracle..........................................114 Zarządzanie planami wykonania zapytań w bazach danych DB2..............................................130 Zarządzanie planami wykonywania zapytań w bazach danych SQL Server ...........................136 Rozdział 5. Sporządzanie diagramów prostych zapytań.....................................143 Po co stosować nową metodę? ...................................................l.......................................................143 Pełne diagramy zapytań...................................................l...................................................l...............145 Interpretacja diagramów zapytań ...................................................l..................................................164 Uproszczone diagramy zapytań...........................l...................................................l..........................166 Ćwiczenia...................................................l...................................................l........................................169 Rozdział 6. Wyznaczanie najlepszych planów wykonania zapytań.................173 Efektywne plany wykonania zapytań...................................................l...........................................174 Standardowa heurystyczna kolejność złączania...................................................l..........................176 Proste przykłady...................................................l...................................................l............................177 Szczególny przypadek ...................................................l...................................................l..................187 Skomplikowany przykład ...................................................l...................................................l............190 Specjalne zasady postępowania dla szczególnych przypadków.................................................194 Ćwiczenie...................................................l...................................................l........................................222 Rozdział 7. Tworzenie diagramów i optymalizacja złożonych zapytań SQL... 225 Niestandardowe diagramy złączeń ...................................................l...............................................226 Zapytania z podzapytaniami...................................................l..........................................................254 Zapytania z widokami ...................................................l...................................................l..................267 Zapytania z operacjami na zbiorach...................................................l..............................................277 Ćwiczenie...................................................l...................................................l........................................279 Rozdział 8. Dlaczego metoda diagramów działa? ................................................281 Argumenty przemawiające za zagnieżdżonymi pętlami...................................................l...........281 Wybieranie tabeli źródłowej ...................................................l...........................................................283 Wybieranie kolejnej tabeli złączenia...................................................l..............................................287 Podsumowanie...................................................l...................................................l...............................291 Rozdział 9. Przypadki szczególne .............................................................................293 Złączenia zewnętrzne...................................................l...................................................l....................293 Złączenie scalające i indeksy filtrów...................................................l..............................................299 Brakujące indeksy...................................................l...................................................l..........................302 Spis treści 7 Złączenia bez filtrów...................................................l...................................................l.....................303 Problemy bez rozwiązania ...................................................l...................................................l...........304 Rozdział 10. Rozwiązania dla pozornie nierozwiązywalnych problemów .....307 Gdy bardzo szybko jest zbyt wolno ...................................................l..............................................307 Zapytania zwracające dane ze zbyt wielu wierszy ...................................................l.....................312 Zoptymalizowane zapytanie wolno zwracające jedynie kilka wierszy......................................324 Dodatek A Rozwiązania ćwiczeń..............................................................................329 Rozwiązania do rozdziału 5....................................................l...........................................................329 Rozwiązania do rozdziału 6....................................................l...........................................................333 Rozwiązania do rozdziału 7....................................................l...........................................................339 Dodatek B Pełny proces...............................................................................................343 Uproszczenie zapytania do postaci diagramu...................................................l.............................343 Rozwiązywanie diagramu zapytania ...................................................l............................................347 Sprawdzanie planu wykonania...................................................l......................................................349 Zmiana bazy danych...................................................l...................................................l.....................352 Zmiana reguł optymalizacji ...................................................l............................................................353 Zmiana aplikacji...................................................l...................................................l.............................353 Spojrzenie na przykład z odpowiedniej perspektywy ...................................................l...............354 Dodatek C Słownik ......................................................................................................355 Skorowidz .......................................................................................................................367 Tworzenie diagramów i optymalizacja złożonych zapytań SQL Tworzenie diagramów i optymalizacja złożonych zapytań SQL Jak dotąd, nauczyliśmy się optymalizować zapytania na rzeczywistych tabelach oraz tworzyć dla nich diagramy, które spełniają różne wymagania odnoszące się do normadlnych zapytań biznesowych: • Zapytanie przedstawione jest na jednym drzewie. • Drzewo ma jedno źródło, dokładnie jedną tabelę bez złądczeń z jej kluczami głównymi. Wszystkie węzły, inne niż węzeł źródłowy, mająd pojedyncze, skierowane ku nim połączenia ze znajdującymi się powyżdej węzłami szczegółowymi, ale każdy węzeł może być na szczycie dodwolnej ilości skierowanych ku dołowi połączeń. • Wszystkie złączenia mają skierowane ku dołowi strzałki (złączenia, które są unikalne na jednym z końców). • Złączenia zewnętrzne są niefiltrowane, skierowane ku dodłowi, kolejne złączenia znajdujące się poniżej są także zewnętrzne. • Pytanie, na które zapytanie SQL udziela nam odpowieddzi jest w gruncie rzeczy pytaniem o encję znajdującą się na samej górze (źródło) ddrzewa lub odnośnie agregacji tej encji. • Pozostałe tabele dostarczają jedynie referencyjnych dadnych, umieszczonych w określonej strukturze jedynie przez wzgląd na normaldizację. Nazwałem zapytania spełniające powyższe warunki zapytaniami prostymi, chociaż jak mogliśmy się przekonać w rozdziale 6., mogą one zawierać dowolną ilość złączeń, a ich optymalizacja może być całkiem trudna, zwłaszcza w rzadkich przypadkach węzłów o podobnych współczynnikach filtrowania lub kiedy istnideje ukryte filtrowanie złączeń. 226 Rozdział 7. Tworzenie diagramów i optymalizacja złożonych zapytań SQL Zapytania, które nie mają tak prostej formy będziemy nazywać zapytaniami złożonymi. Jak pokażę w tym rozdziale, niektóre złożone zapytania są wynikiem błędów: w projekcie bazy danych, aplikacji lub implementacji. Błędy tego typu powodują, że bardzo łatwo jest utworzyć nieprawidłowe zapytanie. W tym rozdziale nauczymy się, jak rozpoznawać anomalie występujące w diagramie zapytania, które mogą być ostrzeżeniem o istnieniu błędu w konstrukcji zapytania. Nauczymy się również, jak naprawić te funkcjonalne lub związane z projektem błędy, przy czym niejednokrotnie zwiększenie wydajności będzie efektem ubocznym naszych działań. Poprawki te przekształcają zazwyczaj zapytanie do prostej formy lub na tyle do niej zbliżonej, aby można zastosować metody przedstawio- ne w książce wcześniej. Niektóre złożone zapytania wykraczają poza wszystkie formy, dla których opisywałem tworzenie diagramów, wykorzystując podzapytania, widoki lub klauzule, takie jak UNION i UNION ALL. Takie złożone zapytania mają zazwyczaj dużą funkcjonalność i są często spotykane, musimy więc znaleźć metodę na stworzenie dla nich diagramu oraz na ich optymalizację. Cel ten osiągniemy poprzez rozwinięcie przedstawionych wcześniej me- tod adekwatnych dla prostych zapytań. Niestandardowe diagramy złączeń Jeśli zapytanie zawiera jedynie proste tabele (nie widoki), bez podzapytań, bez klauzul operacji grupowych, takich jak UNION, zawsze można utworzyć jakiś diagram zapytania przez stosowanie metod przedstawionych w rozdziale 5. Jednakże zdarza się, że diagram ma pewne cechy, które nie pozwalają na stosowanie opisanego wcześniej, zwykłego szablonu drzewa złączeń. Opiszę te nieprawidłowości pojedynczo i pokażę jak sobie z nimi radzić. Aby zilustrować anomalie, przedstawię niecałkowite diagramy złączeń, w których części nie mające znaczenia dla dyskusji są ukryte za szarymi obłokami. Skupi to naszą uwagę na tę część, która odgrywa decydującą rolę, pokaże również uniwersalność przykładu. Jako konwencję przyjmę ukrywanie nie mających znaczenia dla dyskusji części szkie- letu połączeń. Liczba szarych połączeń i w ogóle ich istnienie nie ma znaczenia dla przykładu, pokazuje jedynie możliwość wykonania dodatkowych złączeń w realnych przypadkach. Sporadycznie występować będą czarne połączenia z ukrytymi częściami szkieletu zapytania. Połączenia te mają znaczenie dlad dyskusji, ale ukryte części — nie. Cykliczne grafy złączeń Jak poradzić sobie ze złączeniami, które nie mapują się na proste drzewo, ale zawierają połączenia tworzące w pewnym miejscu szkieletu pętlę? Jest kilka okoliczności, w któ- rych można napotkać tego typu diagram. W następnym podrozdziale omówię cztery przypadki z różnymi rozwiązaniami. Niestandardowe diagramy złączeń 227 Teoria grafów jest gałęzią matematyki opisującą abstrakcyjne twory zwane grafami, które składają się z połączeń i węzłów, takich jak diagramy zapytań wykorzy- stywane w tej książce. W teorii grafów, graf cykliczny ma połączenia w formie zamkniętej pętli. W przedstawianych dalej przykładach, aż do rysunku 7.8, na diagramach występują pętle, sprawiając, że są one diagramami cyklicznymi. Przypadek 1. Dwie tabele nadrzędne (złączone jeden- do-jednego) współdzielą tabelę szczegółową Rysunek 7.1 ilustruje pierwszy przypadek, w którym pojedynczy klucz obcy łączy się z kluczami głównymi dwóch różnych tabel nadrzędnych. Rysunek 7.1. Przypadek 1. cyklicznego diagramu zapyótania Na podstawie tego przypadku możemy wnioskować, że SQL może wyglądać w nastę- pujący sposób: SELECT ... FROM ... T1, ... T2, ... T3, ... WHERE ... T1.FKey1=T2.PKey2 AND T1.FKey1=T3.PKey3 AND T2.PKey2=T3.PKey3 ... Nazwałem klucz obcy z tabeli T1, wskazujący na obie tabele przez FKey1, a klucze główne tabeli T2 i T3 odpowiednio przez PKey2 i PKey3. Ponieważ wszystkie trzy złączenia występują jawnie w SQL-u, złączenie cykliczne jest oczywiste, ale należy zwrócić uwagę, że dowolne z tych połączeń mogłoby być nieobecne. W takim przypadku prze- chodniość (jeśli a = b i b = c to a = c) sugerowałaby istnienie tego nieobecnego warun- ku złączenia. To samo zapytanie moglibyśmy wówczas przedstawić w jednej z trzech postaci z rysunku 7.2. Zwróćmy uwagę, że w wersji A i B zapytania możemy wnioskować o istnieniu nieobec- nego połączenia z faktu, że połączenia między T2 a T3 mają strzałki na obu końcach, co oznacza złączenie jeden-do-jednego. Wersja C, dla odmiany wygląda jak zwykłe drzewo złączeń i można się nie zorientować, że ma ono cykliczne złączenia, chyba że zauważy- my, iż T1 wykorzystuje ten sam klucz obcy do połączenia z T2 i T3. 228 Rozdział 7. Tworzenie diagramów i optymalizacja złożonych zapytań SQL Rysunek 7.2. To samo zapytanie cykliczne, w którym óbrakuje jednego z trzech przechodnich warunków złączenia W przypadku występowania tabel jeden-do-jednego, złączenia cykliczne, takie jak na rysunku 7.1, są częste. Nie są one problemami funkcjonalnymi, chociaż ta kwestia po- zostaje do rozważenia przy napotkaniu złączenia jeden-do-jednego. Można je nawet postrzegać jako dodatkowe możliwości. Jeśli osiągnęliśmy węzeł T1, dobrze jest mieć możliwość wyboru następnego węzła, czy to T2, czy T3, ponieważ każdy z nich może mieć lepszy współczynnik filtrowania od drugiego lub może prowadzić do dobrych fil- trów leżących niżej w drzewie. W przypadku dojścia w porządku złączenia do T2 lub T3 przed T1, najbardziej użyteczne byłoby złączenie jeden-do-jednego z pozostałym wę- złem (z T2 do T3 lub z T3 do T2). Pozwala to na uzyskanie dostępu do każdego filtra wykorzystanego w tej drugiej tabeli, przed złączeniem z tabelą T1. Bez poziomego połą- czenia, połączenie T2 z T3 możliwe byłoby jedynie poprzez T1, przy prawdopodobnie większym koszcie. Niektóre optymalizatory są wystarczająco sprytne, aby wykorzystać przechodniość do uzupełnienia brakującego złączenia, a nawet wykorzystać dodatkową swobodę w po- rządku złączenia. Najbezpieczniej jest jednak jawnie uwzględnić wszystkie trzy warunki złączeń, jeśli dwa spośród nich wskazują przez przechodniość na istnienie trzeciego. Z całą pewnością trzeba jawnie uwzględnić w SQL-u wszystkie złączenia potrzebne dla optymalnego planu wykonania. Istnieją szczególne przypadki, w których dwie tabele złączone jeden-do-jednego, tak jak T2 i T3, są tą samą tabelą! W takiej sytuacji, każdy wiersz tabeli T1 łączy się dwukrotnie z tym samym wierszem tabeli T2, co jest bardzo nieefektywne. Najbardziej oczywiste przypadki, w których ta sama tabela wykorzystana jest, poprzez użycie aliasów, dwu- krotnie w klauzuli FROM, są jednak mało prawdopodobne. Dzieje się tak właśnie dlatego, że są one zbyt oczywiste, aby pozostać niezauważonymi. Jednakże dwukrotne złączenie z tą samą tabelą może być bardziej subtelne i można je pominąć w przeglądzie kodu. Może się to zdarzyć, gdy synonim lub widok ukrywa prawdziwą tożsamość tabeli za co najmniej jednym aliasem zapytania. Tak czy inaczej, najlepiej usunąć z zapytania zbyteczną referencję do tabeli i przepisać wszystkie odniesienia kolumn oraz wszystkie złączenia znajdujące się poniżej, tak aby odnosiły się do pozostałego aliasu. Niestandardowe diagramy złączeń 229 Przypadek 2. Każda z nadrzędnych tabel szczegółowych ma kopie klucza obcego wskazującego na klucz główny trzeciej tabeli Rysunek 7.3 przedstawia drugi co do ważności przypadek złączeń cyklicznych, w któ- rym identyczne klucze obce tabel T1 i T2 wskazują na ten sam klucz główny tabeli T3. Rysunek 7.3. Złączenie cykliczne sugerujące denormalizaócję Tym razem SQL ma następującą postać: SELECT ... FROM ... T1, ... T2, ... T3, ... WHERE T1.FKey1=T2.PKey2 AND T1.FKey2=T3.PKey3 AND T2.FKey2=T3.PKey3 ... W tym wyrażeniu oznaczyłem klucze obce wskazujące z T1 do T2 i T3 odpowiednio przez FKey1 i FKey2. Z przechodniości wynika, że kolumna klucza obcego T2.FKey2 ma taką samą wartość, co T1.FKey2, ponieważ obie łączą się z T3.PKey3. Klucze główne tabel T2 i T3 oznaczyłem odpowiednio przez PKey2 i PKey3. Najbardziej prawdopodobnym wy- tłumaczeniem faktu, że T1 i T2 łączą się z tą samą tabelą T3 poprzez jej klucz główny jest to, że klucze obce z tabel T1 i T2 są nadmiarowe. W tym scenariuszu, kolumna FKey2 ta- beli szczegółów T1 denormalizuje dane jej tabeli nadrzędnej. Dane te zawsze pasują do wartości FKey2 w odpowiadającym wierszu T2. Ewentualnie wartości FKey2 powinny pasować, ale czasem niestety tak nie jest, ponieważ zdenormalizowane dane bardzo często nie sąz zsynchronizowane. W rozdziale 10. opisano wszystkie za i przeciw denormalizacji w przypadkach takich jak ten. W skrócie, jeśli denormalizacja jest uzasadniona, można przypuszczać, że dodatko- we połączenie w diagramie zapytania da dostęp do lepszego planu wykonania. Jednak bardziej prawdopodobne jest, że denormalizacja jest błędem, za którym idą większe koszty i ryzyko z nią związane niż spodziewane zyski. Wyeliminowanie denormalizacji usunęłoby klucz obcy FKey2 z tabeli T1, a tym samym usunęłoby także połączenie T1 z T3, sprawiając, że diagram zapytania stałby się drzewem. 230 Rozdział 7. Tworzenie diagramów i optymalizacja złożonych zapytań SQL Przypadek 3. Filtr dwuwęzłowy (nieunikalny na obu końca ch) pomiędzy węzłami, które są już połączone poprzez normal ne złączenia Rysunek 7.4 pokazuje trzeci przypadek złączenia cyklicznego. Tym razem mamy normal- ne połączenia ku dołowi od T1 do T2 i T3, ale mamy także trzeci, niezwykły warunek złączenia pomiędzy T2 i T3, który nie wykorzystuje kluczy głównych żadnej z tabedl. Rysunek 7.4. Złączenie cykliczne z filtrem dwuwęzłowym Ponieważ żaden klucz główny nie jest wykorzystany przy złączeniu T2 z T3, połą- czenie pomiędzy nimi nie ma strzałki na żadnym z kozńców. SQL dla rysunku 7.4 ma postać: SELECT ... FROM ... T1, ... T2, ... T3, ... WHERE ... T1.FKey1=T2.PKey2 AND T1.FKey2=T3.PKey3 AND T2.Kol2 JestJakosPorownywalneZ T3.Kol3 ... Jeśli na przykład T1 byłoby tabelą Orders łączącą się z tabelami Customers — T2 i Salespersons — T3, zapytanie mogłoby zwrócić zamówienia, w których klienci są przypisani do innych regionów niż sprzedawcy odpowieddzialni za zamówienie. SELECT ... FROM Orders T1, Customers T2, Salespersons T3 WHERE T1.Customer_ID=T2.Customer_ID AND T1.Salesperson_ID=T3.Salesperson_ID AND T2.Region_ID!=T3.Region_ID W tym przypadku, warunek T2.Region_ID!=T3.Region_ID jest technicznie złączeniem, ale lepiej postrzegać go jako warunek filtrowania, którego działanie wymaga wierszy z dwóch różnych tabel. Jeśli zignorujemy bezstrzałkowe połączenie pomiędzy T2 i T3, osiągniemy T1 zanim będziemy mogli zastosować dwuwęzłowy filtr na Region_ID. Jedynymi dozwolonymi porządkami złączeń, które unikają bezpośredniego, niestan- dardowego połączenia pomiędzy T2 i T3 są: (T1, T2, T3) (T1, T3, T2) Niestandardowe diagramy złączeń 231 (T2, T1, T3) (T3, T1, T2) Dowolny porządek, inny niż powyższe cztery (taki jak (T2, T3, T1)), spowodowałby katastrofalny nawał danych po złączeniu wiele-do-wielu z drugą tabelą. Byłby to niemal ilo- czyn kartezjański wierszy tabeli T2 i T3. We wszystkich tych bezpiecznych porządkach złączeń tabela T1 znajduje się na pierwszym bądź drugim miejscu, zanim osiągniemy T2 i T3. Te porządki złączeń tworzą zatem dwa zwykłe złączenia wiele-do-jednego pomię- dzy szczegółową tabelą T1 a jej nadrzędnymi tabelami T2 i T3. Rzadki filtr dwuwęzłowy nie ma żądanego działania w momencie osiągnięcia pierwszej z filtrowanych tabel, dopiero potem, po osiągnięciu drugiej tabeli, odrzuca część wierszy, tak jak to czyni zwykły filtr. Z takiej perspektywy, radzenie sobie z tym przypadkiem jest proste — załóżmy, że filtr nie istnieje (lub nie jest bezpośrednio dostępny) aż do mo- mentu złączenia z jedną z tabel, do których filtr się odnosi. Jednak gdy tylko osiągniemy dowolny koniec dwuwęzłowego filtra, koniec przeciwny wchodzi w posiadanie lepszego współczynnika filtrowania i staje się bardziej atrakcyjdny jako następny węzeł do złączenia. Rysunek 7.5 pokazuje specyficzny przykład z filtrem dwuwęzłowym, w którym ułamek wierszy zwykłego złączenia T1 z T2 i T3, które spełniają dodatkowo dwuwęzłowy wa- runek filtrowania, jest równy 0,2. W tym przypadku wybralibyśmy pierwotnie porządek złączeń niezależny od istnienia filtru dwuwęzłowego, uwzględniając jedynie zwykłe złączenia. Jednak gdy tylko osiągniemy złączenie z tabelą T2 lub T3, ta druga jeszcze nie złączona tabela otrzymuje nowy, znacznie bardziej odpowiedni współczynnik złączenia równy współczynnikowi pierwotnemu (1,0 dla T2 i 0,5 dla T3) pomnożonemu przez 0,2. Rysunek 7.5. Filtr dwuwęzłowy z jawnym współczynnikiem ófiltrowania Przy optymalizacji zapytania przedstawionego na rysunku 7.5 należy postępować zgodnie z normalną procedurą, ignorując dwuwęzłowy filtr pomiędzy T2 i T3, oczywiście, aż do momentu osiągnięcia jednej z tych tabel. Tabela wejściowa to T1, po niej należy dodać tabelę T4 ze względu na najlepszy zwykły filtr poniżej tabeli T1. T3 ma następny w kolejności współczynniki filtrowania równy 0,5, tak więc wystąpi ona po T4 w po- rządku złączenia. Mamy teraz wybór pomiędzy T2 i T5. Ale ponieważ T2, ze względu na wcześniejsze osiągnięcie T3, ma aktywowany filtr dwuwęzłowy, jego współczynnik fil- trowania jest równy 0,2, i to jest on korzystniejszy niż T5. Dlatego łączymy go jako następny w kolejności. W efekcie mamy najlepszy porządek złączenia (T1, T4, T3, T2, T5). 232 Rozdział 7. Tworzenie diagramów i optymalizacja złożonych zapytań SQL Złączenie z T2 w przytoczonym przykładzie jest zwykłym złączeniem wykorzy- stującym zagnieżdżone pętle w indeksie na kluczu głównym tabeli T2, wychodząc od klucza obcego z T1. Unikajmy zagnieżdżonych pętli w tabeli z filtrem dwuwę- złowym. Odnosząc się do SQL-a tuż przed rysunkiem 7.5, byłoby znacznie lepiej dosięgnąć tabeli Customers, wykorzystując pętle zagnieżdżone przy złączeniu T1.Customer_ID=T2.Customer_ID niż przy złączeniu dwuwęzłowym T2.Region_ID!=T3.Region_ID. Przypadek 4. Złączenie wieloczęściowe z dwóch kluczy o bcych jest rozłożone na złożone klucze główne dwóch tabel Na zakończenie rozważań na rysunku 7.6 pokazano czwarty przypadek złączeń cyklicz- nych. Występują tu dwa niestandardowe złączenia z T3. Żadne z nich nie wykorzystuje całego klucza głównego tej tabeli ani kluczy głównych tabel na przeciwnych końcach tych złączeń. Jeśli taki przypadek braku połączenia z całym kluczem głównym na co najmniej jednym końcu każdego złączenia jest „zły”, wówczas przypadek 4. jest zazwy- czaj takim, w którym dwukrotne zło staje się dobrem! Rysunek 7.6. Złączenie cykliczne z dwoma niestandardowóymi złączeniami W sytuacji, jaką zaprezentowano na rysunku 7.6, zapytanied SQL ma postać: SELECT ... FROM ... T1, ... T2, ... T3, ... WHERE ... T1.FKey1=T2.PKey2 AND T1.FKey2=T3.PKeyColumn1 AND T2.FKey3=T3.PKeyColumn2 ... Taki SQL pojawia się zazwyczaj, kiedy tabela T3 ma dwuczęściowy klucz główny, a dwuczęściowy klucz obcy jest rozłożony na dwie tabele w zależności tabela nadrzędna- tabela szczegółowa. Konkretny przykład wyjaśni tę kwestię. Rozważmy tabele słownikowe oznaczone Tables, Indexes, Table_Columns i Index_Columns. Moglibyśmy wybrać dwuczęściowy klucz główny (Table_ID, Column_Number) tabeli Table_Columns, gdzie Column_Number oznacza miejsce, które zajmuje kolumna w naturalnym porządku kolumn tabeli — 1 dla pierwszej kolumny, 2 dla drugiej i tak dalej. Tabela Indexes miałaby klucz obcy do tabeli Tables na kolumnie Table_ID, a tabela Index_Columns miałaby dwuczęściowy klucz Niestandardowe diagramy złączeń 233 główny (Index_ID, Column_Number). Wartość Column_Number w tabeli Index_Columns ma to samo znaczenie, co kolumna Column_Number w tabeli Table_Columns — miejsce jakie kolumna zajmuje w naturalnym porządku kolumn tabeli (nie jej miejsce w porządku indeksów, która to wartość znajduje się w Index_Position). Jeśli znalibyśmy nazwę in- deksu i chcielibyśmy znaleźć listę nazw kolumn, które tworzą indeks według porządku z Index_Position, moglibyśmy napisać zapytanie: SELECT TC.Column_Name FROM Indexes Ind, Index_Columns IC, Table_Columns TC WHERE Ind.Index_Name= PRACOWNICY_X1 AND Ind.Index_ID=IC.Index_ID AND Ind.Table_ID=TC.Table_ID AND IC.Column_Number=TC.Column_Number ORDER BY IC.Index_Position ASC W ramach powtórki, spróbujmy utworzyć szkielet diagrazmu dla tego zapytania. Jeśli warunek na Index_Name miałby współczynnik filtrowania 0,0002, diagram zapyta- nia pozbawiony pozostałych współczynników wyglądałby jak na rysunku 7.7. Rysunek 7.7. Konkretny przykład czwartego przypadku ózłączeń cyklicznych W tym przypadku, dwukrotne „zło” (dwa złączenia, które nie łączą się z całym kluczem głównym po żadnej stronie połączenia) łączy się, tworząc w efekcie „dobro”, kiedy rozpatrujemy złączenia z TC razem, ponieważ razem łączą się one z pełnym kluczem głównym tej tabeli. Możemy przetworzyć diagram tego nietypowego przypadku w spo- sób widoczny na rysunku 7.8. Rysunek 7.8. Łączenie wieloczęściowych złączeń z kluczy oóbcych rozdzielonych na dwie tabele 234 Rozdział 7. Tworzenie diagramów i optymalizacja złożonych zapytań SQL Jeśli przestrzegamy reguły, aby łączyć „do” lub „z” pełnych kluczy głównych, najlepszy porządek złączenia dla rysunku 7.7 staje się jasny. Wyjdźmy z filtru na Ind i podążajmy za połączeniem górnym ku IC. Jest to tak naprawdę najlepszy plan wykonania dla tego przykładu. W przypadkach takich jak ten, należy uwzględniać niestandardowe złączenia prowadzące do wieloczęściowych kluczy głównych tylko do momentu, w którym baza danych osiągnie wszystkie węzły górne niezbędne do użycia pełnego klucza głównego. Podsumowanie złączeń cyklicznych Następująca lista podsumowuje metody wykorzystywane przy rozwiązywaniu proble- mów ze złączeniami cyklicznymi: Przypadek 1. Dwie tabele nadrzędne (złączone jeden-rdo-jednego) współdzielą tabelę szczegółową Mamy tu okazję do optymalizacji, zwiększając stopień swobody w porządku złączenia. Powinniśmy jednak rozważyć także inne rozwiązania przedstawione w dalszej części tego rozdziału. Pozwalają nam one dobrze radzić sobie zed złączeniami jeden-do-jednego. Przypadek 2. Każda z nadrzędnych tabel szczegółowych ma kopie klucza obcego wskazującego na klucz główny trzeciej tabeli Tutaj także mamy możliwość zwiększenia swobody w porządku złączenia, ale ten przypadek oznacza denormalizację, która zazwyczaj nie znajduje odpowiedniego uzasadnienia. Jeśli mamy wybór, powinniśmy usunąć denormalizację, chyba że zysk w tym lub innych zapytaniach potwierdza jej trafność. W dotychczas prowadzonych rozważaniach sugerowałem idealne rozwiązania, przy założeniu posiadania całkowitej kontroli nad aplikacją, projektem bazy da- nych oraz SQL-em. Pokazywałem także kompromisowe rozwiązania, które mają zastosowanie w sytuacji, gdy ma się mniejszą kontrolę. Jednak czasem, gdy ma się do czynienia z nieuzasadnioną denormalizacją, w przypadku gotowego systemu, którego nie posiadamy czy na który nie mamy nawet wpływu, jedynym wyjściem kompromisowym jest nic nie robić. Przypadek 3. Filtr dwuwęzłowy (nieunikalny na obu końcarch) pomiędzy węzłami, które są już połączone poprzez normalne złączenia Ten przypadek należy traktować, jak gdyby nie występował w nim żaden filtr aż do momentu osiągnięcia jednego z węzłów. Wówczas w poszukiwaniu pozostałej części porządku złączenia, węzeł ów należy traktować jako mający lepszy współczynnik filtrowania. Przypadek 4. Złączenie wieloczęściowe z dwóch kluczy orbcych jest rozdzielone na złożone klucze główne dwóch tabel Ten przypadek złączenia powinien być wykorzystywany, jedynie gdy posiadamy obie części klucza. Niestandardowe diagramy złączeń 235 Rozłączone diagramy zapytań Rysunek 7.9 pokazuje dwa przypadki rozłączonych diagramów zapytania: szkielet za- pytania, w którym nie udało się połączyć wszystkich tabel zapytania w pojedynczą, połączoną strukturę. W każdym z tych przypadków mamy w pewnym sensie do czy- nienia z dwoma niezależnymi zapytaniami, każde z osobnym diagramem zapytania, który można optymalizować w oderwaniu od pozostałych ddiagramów. Rysunek 7.9. Rozłączone diagramy zapytań W przypadku A przedstawiłem zapytanie, które składa się z dwóch, wyglądających na nie- zależne, zapytań — każde z własnymi złączeniami. W przypadku B pokazałem praktycznie zwykłe zapytanie, którego jedna z tabel (tabela T2) jest odłączona od drzewa złączenia (tzn. nie jest złączona z żadną inną tabelą). Każdy z tych dwóch przypadków mapuje się na dwa oddzielne zapytania, które są wykonywane w obrębie pojedynczego zapytania. Co się stanie, jeśli połączymy dwa niezależne zapytania w jedno? Kiedy dwie tabele są połączone w jedno zapytanie bez jakichkolwiek warunków złączenia, baza danych zwraca iloczyn kartezjański — każdą możliwą kombinację wierszy pierwszej tabeli z wier- szami tabeli drugiej. W przypadku rozłączonych diagramów, należy myśleć o wynikach zapytania reprezentowanego przez każdy niezależny szkielet zapytania (lub izolowany węzeł) jako o wirtualnej tabeli. Z tej perspektywy widać, że baza danych zwróci wszystkie kombinacje wierszy tych dwóch niezależnych zapytań. Tak więc w wyniku otrzymamy iloczyn kartezjański. Kiedy spotkamy się z iloczynem kartezjańskim, tak jak pokazano na rysunku 7.9, po- winniśmy zbadać przyczynę jego zaistnienia. Gdy ją już poznamy, będziemy mogli zadecydować, które z wymienionych poniżej działań podjąć. Będzie to uzależnione od tego, z którym spośród czterech przypadków mamy do cdzynienia: Przypadek 1. W zapytaniu brakuje złączenia, które łącrzyłoby rozdzielone części Dodać brakujące złączenie. Przypadek 2. Zapytanie składa się z dwóch niezależnycrh zapytań, a każde z nich zwraca wiele wierszy Wyeliminować iloczyn kartezjański poprzez osobne wykondywanie oddzielnych zapytań. 236 Rozdział 7. Tworzenie diagramów i optymalizacja złożonych zapytań SQL Przypadek 3. Jedno z niezależnych zapytań jest zapytarniem zwracającym jeden wiersz Rozważyć rozdzielenie zapytań, aby zmniejszyć przepływ danych z bazy danych, szczególnie jeśli jedno z niezależnych zapytań zwraca wiele wierszy. Wykonać naj- pierw zapytanie jednowierszowe. Przypadek 4. Oba niezależne zapytania są zapytaniamir jednowierszowymi Zachować zapytania w formie połączonej, chyba że jest to kłopotliwe lub trudne w konserwacji. Zanim rozdzielimy rozłączone zapytanie na dwa niezależne zapytania, rozważmy, czy programista mógł przez nieuwagę pozostawić zapytanie bez złączenia. Na początku cyklu tworzenia systemu, najczęstszą przyczyną rozłączonych diagramów zapytań jest to, iż programiści zapominają dodać niektóre warunki złączenia, które łączą dwa rozłączone poddrzewa. W takim przypadku, powinniśmy po prostu dołożyć brakujące złączenie, dzięki czemu pozbędziemy się rozłącznych drzew. Za każdym razem, kiedy jedna z ta- bel w drzewie ma klucz obcy wskazujący na klucz główny tabeli źródłowej drugiego drzewa, niemal pewne jest, że brakujące złączenie zostdało opuszczone przez przypadek. Jeśli każde niezależne zapytanie zwraca wiele wierszy, liczba kombinacji przekroczy liczbę wierszy, które otrzymalibyśmy w przypadku wykonania obu zapytań oddziel- nie. Jednakże zbiór kombinacji z dwóch tabel nie zawiera więcej informacji, niż można by uzyskać poprzez wykonanie zapytań oddzielnie. Dlatego generowanie nadmiarowych danych w kombinacji jest po prostu stratą czasu, przynajmniej jeśli zamierza się otrzymać czystą informację. Wobec tego lepsze może być wykonanie tych dwóch za- pytań oddzielnie. Generowanie kombinacji w iloczynie kartezjańskim rzadko bywa w jakiś sposób uzasadnio- ne, z perspektywy wygody programowania. Jednakże zawsze istnieją metody alternatywne, pozwalające uniknąć zbędnych danych, jeśli koszt ich uzydskania jest zbyt wysoki. Jeśli rozpatrywać problem tylko pod kątem fizycznych operacji wejścia-wyjścia, iloczyn kartezjański nie sprawiałby żadnego problemu, ponieważ nadmiarowe odczyty danych z powtórzonych zapytań byłyby najprawdopodobniej w pełni buforowane już po pierwszym odczycie. Słyszałem nawet opinie, które broniły długo wykonujących się zapytań tego typu, opierając się o niewielką ilość fizycznych operacji wejścia-wyjścia. Takie zapytania są dobrym sposobem na spalenie pro- cesora i wygenerowanie ogromnej ilości logicznych operacji wejścia-wyjścia, jeśli kiedykolwiek zaszłaby potrzeba przeprowadzenia jakiegoś testu wytrzymałości czy in- nego doświadczenia. Nie mają one jednak zastosowaniaz w aplikacjach biznesowych. Jeśli jedno z niezależnych zapytań zwraca tylko jeden wiersz, zagwarantowane jest, że przynajmniej iloczyn kartezjański jest bezpieczny i że zwrócona liczba wierszy będzie nie większa niż liczba wierszy zwróconych przez większe z niezależnych zapytań. Jednakże istnieje wciąż niewielki koszt połączenia zapytań. Jest on związany z przesyłem danych, ponieważ lista SELECT połączonego zapytania może zwrócić dane z mniejszego zapytania Niestandardowe diagramy złączeń 237 wielokrotnie, raz dla każdego wiersza zapytania wielowierszowego. Powoduje to prze- sył większej ilości zbędnych danych niż w przypadku rozdzielenia obu zapytań. Koszt przesyłu jest z drugiej strony kontrowany poprzez oszczędności w opóźnieniach wyni- kłych z przesyłu każdego pakietu — zapytanie połączone oszczędza ciągłych odwołań sieciowych do bazy danych, tak więc najlepszy wybór zależy od szczegółów. Jeśli nie roz- dzielimy zapytań, optymalny plan wykonania jest prosty — najpierw należy urucho- mić optymalny plan wykonania dla zapytania zwracającego pojedynczy wiersz. Następ- nie, w pętli zagnieżdżonej, która wykonana będzie tylko raz, trzeba uruchomić optymalny plan wykonania dla zapytania wielowierszowego. Ten połączony plan wykonania ma koszt taki sam, jak wykonanie dwóch zapytań oddzielnie. Jeśli plan zapytania wielo- wierszowego uruchomimy najpierw, plan pętli zagnieżdżonych będzie wymagał powta- rzania dla każdego zapytania jednowierszowego tylekroć razy, ile wierszy zwróciłoby zapytanie wielowierszowe. Połączenie zapytania jednowierszowego z zapytaniem wielowierszowym jest czasem wygodne i usprawiedliwione. Istnieje specjalny przypadek, zobrazowany na prawej połówce rysunku 7.9, w którym zapytanie jednowierszowe jest po prostu odczytem je- dynego wiersza izolowanej tabeli T2 nie mającej żadnych złączeń. Iloczyn kartezjański z izolowaną tabelą jest czasem użyteczny w pobieraniu parametrów przechowywanych w jednowierszowej tabeli parametrów, szczególnie kiedy parametry te występują jedynie w klauzuli WHERE, a nie w liście SELECT. Kiedy zapytanie zwraca dane z tabeli parame- trów, okazuje się, że tańsze jest wykonanie odpowiednio połączonego zapytania niż wykonanie dwóch osobnych zapytań. Z jeszcze rzadszym przypadkiem mamy do czynienia, gdy oba izolowane zapytania zwracają pojedynczy wiersz. Z punktu widzenia wydajności jest całkowicie uzasadnione i bezpieczne połączenie takich dwóch zapytań. Jest ono pozbawione niebezpieczeństw związanych z innymi przypadkami. Jednakże z perspektywy programowania i kon- serwacji oprogramowania, łączenia takich zapytań może być mylące, a oszczędności są raczej niewielkie. Diagram zapytania z wieloma tabelami źródłowymi Rysunek 7.10 pokazuje przykład diagramu zapytania, który nie spełnia oczekiwania odnośnie jednego źródła. Przypadek ten jest spokrewniony z poprzednim (rozłączone diagramy zapytań). Tutaj, dla każdego wiersza tabeli Master spełniającego warunki za- pytania, zapytanie zwróci wszystkie kombinacje odpowiednich szczegółów z Root1 i Root2. Mając dane współczynniki złączeń tabeli szczegółów, możemy spodziewać się wszystkich kombinacji 5 szczegółów tabeli Root1 i 30 szczegółów tabeli Root2, co da nam 150 kombinacji dla każdego wiersza tabeli Master. Te 150 wierszy kombinacji nie zawiera więcej danych niż 5 szczegółów Root1 w połączeniu z 30 szczegółami Root2, zatem szybciej jest odczytać te 5 i 30 wierszy oddzielnie, unikając iloczynu kartezjań- skiego. Podczas gdy rozłączony diagram zapytania tworzy pojedynczy, duży iloczyn kartezjański, liczne węzły-źródła tworzą całą serię mniejszych iloczynów kartezjańskich, po jednym dla każdego odpowiedniego wiersza tabeli gdłównej. 238 Rozdział 7. Tworzenie diagramów i optymalizacja złożonych zapytań SQL Rysunek 7.10. Diagram zapytania o wielu tabelach źródłóowych Istnieją cztery przyczyny wystąpienia diagramu zapytania o wielu źródłach. Następują- ca lista pokazuje te przyczyny i opisuje odpowiadająced im rozwiązania: Przypadek 1. Brakujący warunek W zapytaniu brakuje warunku, który zamieniłby jedną z tabel źródłowych w tabelę główną oraz złączenie jeden-do-wielu na jeden-do-jednedgo. Rozwiązanie: dodać brakujący warunek złączenia. Przypadek 2. Iloczyn kartezjański wiele-do-wielu Zapytanie reprezentuje iloczyn kartezjański wiele-do-wielu na każdy wiersz tabeli głównej, pomiędzy tabelami szczegółów dzielącymi wspólną tabelę główną. Przypadek ten ma miejsce, gdy współczynnik złączenia tabeli szczegółów z pojedynczej współ- dzielonej tabeli głównej do dwóch różnych tabel źródłdowych jest większy niż 1,0. Rozwiązanie: usunąć iloczyn kartezjański poprzez rozdzielenie zapytania na dwa niezależne zapytania czytające niezależnie z tabelid źródłowej. Przypadek 3. Współczynnik złączenia tabeli szczegółówr jest mniejszy niż 1,0 Jedna ze źródłowych tabel szczegółowych łączy się ze współdzieloną tabelą główną, przy współczynniku tabeli szczegółowej złączenia mniejdszym niż 1,0. Rozwiązanie: chociaż nie jest to problem wydajnościowy, należy rozważyć odsepa- rowanie części zapytania lub optymalizację jednej z części zapytania, tak by stała się podzapytaniem. Przypadek 4. Tabela jest używana jedynie dla sprawdzernia obecności Jedna ze źródłowych tabel szczegółów nie dostarcza żadnych danych potrzebnych w liście polecenia SELECT i jest włączona do zapytania jedynie dla sprawdzenia obecności. Rozwiązanie: zamienić sprawdzenie obecności na podzapytdanie. Przypadek 1. Brakujący warunek złączenia Występowanie drugiego węzła źródłowego najczęściej wskazuje na brak warunku złą- czenia, który zamieniłby jeden ze źródłowych węzłów w węzeł główny. Rysunek 7.11 pokazuje transformację, w której złączenie z tabeli Master do Root1 zostało zamienione Niestandardowe diagramy złączeń 239 Rysunek 7.11. Naprawianie zapytania z mnogimi węzłamió źródłowymi na złączenie jeden-do-jednego poprzez dodanie (lub rozpoznanie) dodatkowego warun- ku na tabeli Root1 (przemianowana na R1), co zapewniło, że baza danych znajdzie co najmniej jeden wiersz w R1 dla każdego wiersza tabeli Master. Jest to szczególnie praw- dopodobne, jeśli R1 zawiera szczegółowe dane związane z przedziałami czasowymi (jak np. zmieniający się podatek), które łączą rekord główny (jak encja podatku) i warunek na datę (np. żądanie obecnej stawki podatkowej), tworząc złączenie jeden-do-jednegdo. Niejednokrotnie warunek powodujący, że złączenie staje się jeden-do-jednego, już istnieje. Wówczas powinniśmy odkryć kombinację złączenia wiele-do-jednego i tego warunku, która to kombinacja zmieni współczynnik złączenia szcdzegółów. W przykładzie, w którym współczynnik złączenia tabeli szczegółów Root1 był równy 5, współczynnik filtrowania dla takiego filtra byłby równy 0,2, lub inaczej 1/5. Ewentualnie, warunek, który powoduje, że złączenie jest typu jeden-do-jednego może nie być uwzględniony w zapytaniu, szczególnie jeśli rozwój aplikacji odbywał się w syste- mie testowym, w którym relacja jeden-do-wielu była ukryta ze względu na konkretne dane. Poprzedni przykład dotyczący zmieniających się stóp podatkowych jest tu dobrą ilustracją. W systemie produkcyjnym może się bowiem okazać, że istnieją rekordy jedynie do obecnej stopy podatku, co ukrywa w ten sposób błąd nieuwzględnienia warunku na datę na tabeli stóp. 240 Rozdział 7. Tworzenie diagramów i optymalizacja złożonych zapytań SQL Niezależnie od tego czy brakuje warunku tworzącego złączenie jeden-do-jednego, czy też nie jest on rozpoznany jako połączony z tym złączeniem, powinniśmy włączyć ten warunek i rozpoznać go jako część złączenia, a nie niezależny warunek złączenia. Taki brakujący warunek złączenia jest szczególnie prawdopodobny, gdy jeden z kluczy obcych jednej z tabel źródłowych wskazuje ku dołowi na wspólną tabelę główną i jest jednocze- śnie częścią wieloczęściowego klucza głównego tej tabelid źródłowej. Przypadek 2. Rozdzielanie iloczynu kartezjańskiego na wiele zapytań Rysunek 7.12 wskazuje inne rozwiązanie dla problemu diagramów o wielu węzłach źródłowych. To rozwiązanie jest podobne do tego omówionego wcześniej, dotyczącego jawnie wykonywanych nie połączonych zapytań, i tutaj rozdzielamy iloczyn kartezjań- ski i zastępujemy go przez dwa rozłączne zbiory. W naszym przykładzie, zapytanie, które zwróciłoby 150 wierszy dla każdego wiersza tabeli Master zostało zamienione na dwa zapytania, zwracające w połączeniu po 35 wierszy na każdy wiersz tabeli Master. W każdym przypadku wystąpienia relacji jeden-do-wielu z tabeli głównej do dwóch różnych tabel źródłowych, możemy otrzymać dokładnie te same dane, przy dużo mniejszej liczbie odczytanych wierszy, za pomocą oddzielnych zapytań, tak jak jest to widoczne na ilustracji. Ponieważ rezultat przyjmuje zmienioną formę, potrzeba także zmienić logikę aplikacji, która obsługuje tę nową formę. Rysunek 7.12. Rozwiązywanie problemu iloczynu karteózjańskiego za pomocą oddzielnych zapytań Przypadek 3. Źródłowe tabele szczegółów, które łączą się zazwyczaj w stosunku nie więcej niż jeden-do-jednego Rysunek 7.13 pokazuje przypadek wielokrotnych tabel źródłowych, w którym wydajność zapytania nie jest problemem nawet w niezmienionej postaci. Ponieważ współczynnik złączenia tabeli szczegółów z Master do Root1 jest równy 0,5, nie pojawia się kartezjańska Niestandardowe diagramy złączeń 241 Rysunek 7.13. Iloczyn kartezjański z niskim współczynnóikiem złączenia detali eksplozja wierszy podczas łączenia odpowiednich wierszy tabeli Root1 z Root2, dla przeciętnego rekordu tabeli Master. Można traktować Root1, jak gdyby był złączony ku dołowi, faworyzując go nawet poprzez poprawienie jego współczynnika filtrowania przez owo 0,5 (w myśl specjalnej reguły z rozdziału 6. dla współczynników złączeń tabel szczegółów mniejszych niż 1,0). Chociaż zapytanie to nie stanowi problemu z punktu widzenia optymalizacji, może ono być niepoprawne. Złączenie jeden-do-zera lub jeden-do-wielu z tabeli Master do Root1 ma zazwyczaj typ jeden-do-zera lub jeden-do-jednego, co prowadzi do dobrego zachowa- nia iloczynu kartezjańskiego. Jednakże jeśli złączenie jest zawsze typu jeden-do-wielu, trzeba wziąć pod uwagę, że rezultat może być iloczynem kartezjańskim z powtórzeniami dla danego wiersza tabeli Root2. Ponieważ przypadek ten jest rzadki, można z dużym prawdopodobieństwem powiedzieć, że zapytanie było zaprojektowane i przetestowane tak, by zwracało rezultaty, które mapują jeden-do-jednego z wierszami z Root2, a apli- kacja może nawet nie działać w innych rzadkich przydpadkach. Im rzadszy jest przypadek jeden-do-wielu, tym bardziej prawdopodobne jest, że przypadek taki był zupełnie zaniedbany w projektowazniu aplikacji. Na przykład, jeśli aplikacja wymieni dane w Root2 po odczytaniu ich za pomocą po- wyższego zapytania i będzie próbowała przesłać zmiany z powrotem do bazy danych, musi ona rozważyć, która kopia powtórzonych wierszy Root2 powinna być zapisana ponownie do bazy danych. Czy aplikacja powinna ostrzec użytkownika końcowego, że próbowała wysłać niespójne kopie? Jeśli agreguje ona dane tabeli Root2 z zapytania, czy unika dodawania danych z powtórzonych wierszy tabeli dRoot2? Przypadek 4. Zamiana kontroli istnienia na jawne podz apytanie Jedno z rozwiązań funkcjonalnego problemu z rysunku 7.13 pokazane jest na rysunku 7.12 — rozłączenie zapytania na dwie części. Innym, zadziwiająco częstym rozwiązaniem jest wyizolowanie gałęzi z Root1 w podzapytanie, zazwyczaj za pomocą warunku EXISTS. Rozwiązanie to jest szczególnie łatwe w zastosowaniu, jeśli oryginalne zapytanie nie wybierało kolumn z Root1 (lub dowolnej tabeli połączonej poniżej niej poprzez ukryte, szare połączenia na rysunku 7.13). W tym dość popularnym, szczególnym przypadku, 242 Rozdział 7. Tworzenie diagramów i optymalizacja złożonych zapytań SQL tak naprawdę jesteśmy zainteresowani tylko tym, czy odpowiedni wiersz z Root1 ist- nieje i być może spełnia jakieś warunki filtrowania. Nied interesuje nas jego zawartość czy też liczba pasujących wierszy (poza pierwszym). W dalszej części rozdziału zobaczymy, jak tworzyć diagram i optymalizować zapytania z podzdapytaniami tego typu. Złączenia bez kluczy głównych Połączenia bez strzałek na końcach symbolizują złączenia, które nie zawierają kluczy głównych. Zazwyczaj reprezentują one niezwykłe złączenia wiele-do-wielu, chociaż w niektórych przypadkach mogą się zamieniać w złączenia wiele-do-zera lub wiele-do- jednego. W przypadku gdy nigdy nie mają one postaci wiele-do-wielu, unikalny waru- nek na jednym z końców jest po prostu nierozpoznany, więc trzeba dodać strzałkę na tym unikalnym końcu. Jeśli — chociaż czasami — są one typu wiele-do-wielu, wówczas spotykamy się z tymi samymi problemami (i tymi samymi rozwiązaniami), które są cha- rakterystyczne dla diagramów zapytań o wielu węzłach źródłowych. Rysunek 7.14 przedstawia złączenie wiele-do-wielu pomiędzy T1 a T2, gdzie współczynnik złączenia detali na każdym końcu jest większy niż 1,0. (Współczynniki złączenia tabel głównych istnieją jedynie na unikalnych końcach złączenia, tych ze strzałkami, więc to złączenie ma dwa współczynniki złączenia detali). Rysunek 7.14. Złączenie wiele-do-wielu Okazuje się, że taki przypadek jest znacznie częściej spotykany niż poprzednie przykłady niestandardowych diagramów złączeń. Chociaż współdzieli on te same przyczyny pro- blemów i te same rozwiązania, co problem z wieloma węzłami źródłowymi, znaczna większość złączeń wiele-do-wielu występuje ze względu na brak jakiegoś warunku złączenia. Zacząć należy od sprawdzenia, czy warunki filtrowania, które istnieją już w zapytaniu powinny być traktowane jako część złączenia, ponieważ dopełniają one specyfikacji pełnego klucza głównego na jednym z końców złączenia. Przykład 5.2 z rozdziału 5. mógł być potencjalnie takim przypadkiem, z brakującym warunkiem OT.Code_Type= STATUS_ZAMOWIENIA niezbędnym, aby złączenie z OT stało się unikal- ne. Gdybyśmy traktowali ten warunek jedynie jako warunek filtrowania na aliasie OT, złączenie z OT wyglądałoby jak wiele-do-wielu. Nawet jeśli nie znaleźlibyśmy brakującej części złączenia pośród warunków filtrowania tego zapytania, powinniśmy podejrze- wać, że została ona opuszczona przez pomyłkę. Ten przypadek brakujących warunków złączenia jest szczególnie częsty, kiedy projekt bazy danych pozwala na wiele typów encji lub partycji w obrębie tabeli, a programista zapomniał w zapytaniu uwzględnić warunek na typ lub partycję. Wcześniejszy przykład tabeli Code_Translation ma różne typy encji translacji dla każdego Code_Type i nie Niestandardowe diagramy złączeń 243 uwzględnienie warunku na Code_Type spowodowałoby, że złączenie z Code_Translation byłoby typu wiele-do-wielu. Często zdarza się, że problem tego typu nie jest dość wcześnie zauważony w fazie testów. Dzieje się tak dlatego, że nawet jeśli projekt bazy danych po- zwala na wiele typów partycji, środowisko testowe może mieć dane tylko jednego typu. Na ten stan rzeczy projektanci bardzo często się godzą. Nawet jeśli w prawdziwych danych istnieje wiele typów partycji, inna bardziej wybiórcza część klucza może sama powodować unikalność. Jest to jednocześnie szczęśliwy i nieszczęśliwy zbieg okoliczności — z jednej strony nie pozwala, aby brakujący warunek złączenia sprawił natychmiasto- we problemy, z drugiej powoduje, że problem jest znacznie trudniejszy do znalezienia i naprawienia, oraz daje fałszywe wrażenie, że aplikacja nie zawiera błędów. Odnalezienie i wstawienie brakującego warunku złączenia może zwiększyć wydajność tylko nie- znacznie poprzez uczynienie złączenia bardziej selektywnym, ale może także mieć ogromną wartość, jeśli naprawi niebezpieczny w skutkach, niewidoczny błąd. Poprzez bezpośrednią analogię z diagramem zapytania o wielu węzłach źródłowych, rozwiązania problemu złączenia wiele-do-wielu mapuje się na podobne rozwiązania diagramu wielu źródeł. Złączenia jeden-do-jednego Jest taki dowcip o człowieku, który skarżył się, że musi codziennie chodzić do szkoły 5 mil pod górę w obie strony. W pewnym sensie, złączenia jeden-do-jednego zamieniają ten ob- raz na sytuację przeciwną — ze względu na heurystyczne reguły wybierania następnej tabeli złączenia, złączenia jeden-do-jednego są w obie strony z górki! Jako takie, tego typu złączenia nie powodują żadnych problemów z optymalizacją i są najmniej kłopotliwymi elementami diagramów zapytania. Jednakże wskazują oned czasami na pewne sprzyjające okoliczności do poprawienia projektu bazy danych, jeśli jesteśmy na etapie rozwoju aplikacji, w którym projekt bazy danych nie jest jeszcze zamrożony. Użytecznie jest tak- że mieć standardowe sposoby na reprezentację złączeń jeden-do-jednego na diagramie. Opiszę więc sposoby przedstawiania takich przypadków. Złączenie jeden-do-jednego z tabelą zawierającą pod zbiór danych Rysunek 7.15 pokazuje typowe złączenie jeden-do-jednego osadzone w większym zapyta- niu. Podczas gdy złączenie wiele-do-wielu posiadało współczynniki złączenia szczegółów na obu końcach, złączenie jeden-do-jedengo posiada współczynniki złączenia jedynie z tabelą nadrzędną. Współczynnik ten pokazuje nam, że złączenie pomiędzy T1 a T2 jest rzeczywiście typu jeden-do-zera lub jeden-do-jednego — złączenie zero-do-jednego zda- rza się w 30 wierszy tabeli T1. Rysunek 7.15. Typowe złączenie jeden-do-jednego 244 Rozdział 7. Tworzenie diagramów i optymalizacja złożonych zapytań SQL Ponieważ jest to złączenie wewnętrzne, przypadki jeden-do-zera pomiędzy T1 a T2 two- rzą ukryty warunek złączenia, który powinien być obsłużony tak, jak opisano to pod koniec rozdziału 6. Należy także zwrócić uwagę, że może to być ukryty przypadek złą- czenia cyklicznego, co często się zdarza, gdy tabela główna łączy się jeden-do-jednego z inną tabelą. Jeśli tabela szczegółów znajduje się powyżej T1, jak wskazuje na to szare połączenie i jeśli ta tabela szczegółów łączy się z T1 przez ten sam klucz unikalny, który został użyty przy złączeniu z T2, wówczas przez przechodniość tworzy się złączenie prowadzące od tabeli szczegółów do tabeli T2. Rysunek 7.16 pokazuje to implikowane złączenie poprzez połączenie zaznaczne szarym kolorem. O tym, jak radzić sobie z takimi przypadkami napisano we wcześniejszej części tego rozdziału poruszającej zagadnienie złączeń cyklicznych. Rysunek 7.16. Złączenie implikowane tworzące złączenie cóykliczne Niezależnie od istnienia złączenia cyklicznego, może istnieć okazja do poprawienia projektu bazy danych. Przypadek z rysunku 7.16 sugeruje istnienie zbioru encji, które mapują się jeden-do-jednego z T1 oraz podzbioru tych samych encji, które mapują się jeden-do- jednego z T2, gdzie T2 jest zbudowane z kluczy głównych T1 i kolumn mających zastoso- wanie jedynie dla tego podzbioru. W tym przypadku nie ma istotnych powodów, aby konieczne były dwie tabele — wystarczy po prostu dodać kolumnę do T1 i pozostawić ją pustą dla wszystkich elementów większego zbioru, które nie nalezą do zbioru mniej- szego! Sporadycznie zdarzają się sytuacje, w których ze względu na wygodę, można pozostawić w projekcie dwie tabele. Z perspektywy optymalizacji, połączenie tych dwóch tabel jest niemal zawsze pomocne, tak więc należy się choćby nad nim zastano- wić, jeśli tylko mamy wpływ na projekt bazy danych. Złączenia ściśle typu jeden-do-jednego Rysunek 7.17 pokazuje szczególnie ważny przypadek na połączenie dwóch tabel w jedną. Współczynnik złączenia z tabelą główną jest tu dokładnie równy 1,0, a relacja pomiędzy tabelami jest ściśle typu jeden-do-jednego. W związku z tym, obie tabele mapują się na ten sam zbiór encji, a złączenie jest niepotrzebnym wydatkiem w porównaniu z wyko- rzystaniem tabeli połączonej. Biorąc pod uwagę wydajność, jedynym powodem rozdzielenia tych tabel mogłaby być sytuacja, w której zapytanie niemal zawsze potrzebowałoby danych tylko z jednej spo- śród nich i bardzo rzadko wymagałoby wykonania złączenia. Najczęściej zdarza się, że Niestandardowe diagramy złączeń 245 Rysunek 7.17. Złączenie dokładnie jeden-do-jednego jedna z tabel zawiera dane potrzebne sporadycznie, w porównaniu z drugą tabelą. W tym przypadku — zwłaszcza gdy rzadko wykorzystywane dane zajmują dużo miej- sca na każdy wiersz, a wierszy jest wiele — może się okazać, że większa zwartość czę- ściej przeszukiwanej tabeli, w wyniku której jest ona lepiej buforowana, uzasadni koszt rzadko potrzebnego złączenia. Nawet z funkcjonalnego punktu widzenia lub perspektywy tworzenia oprogramowania jest całkiem prawdopodobne, że koszt kodowania jedno- czesnego dodawania i usuwania wierszy z obu tabel (a czasem i aktualizowania) jest wysoki. Dlatego łatwiejsze może się okazać utrzymanie pojedynczej, połączonej tabeli. Zazwyczaj pojawiające się złączenia dokładnie typu jeden-do-jednego są rezultatem jakiejś dodanej funkcjonalności, która wymaga nowych kolumn w już istniejących encjach, a ograniczenia w projektowaniu lub po prostu czyjaś wizja nie pozwoliły zmienić orygi- nalnej tabeli. Jeśli to tylko możliwe, lepiej jest rozwiązać problem przez usunięcie tego typu ograniczeń. Złączenie jeden-do-jednego ze znacznie mniejszym podz biorem Inny przypadek został pokazany na rysunku 7.18. Jest to złączenie jeden-do-zera lub jeden- do-jednego, które niemal zawsze jest typu jeden-do-zera. W takim przypadku argu- menty za rozdzieleniem tabel są bardzo mocne. Mały zbiór encji reprezentowany przez T2 może mieć zupełnie inne wymogi optymalizacyjne niż nadzbiór reprezentowany przez T1. Najprawdopodobniej tabela T1 jest zazwyczaj przeszukiwana bez złączenia z T2. Wówczas bardzo istotny jest fakt, że nie zawiera ona niepotrzebnych kolumn z T2, a indeksy na niej założone mają zastosowanie przy zapytaniach do często używanych danych. Ukryty warunek złączenia, reprezentowany przez mały współczynnik złączenia z tabelą główną po stronie T2, jest bardzo dobry. W rzeczywistości jest on na tyle dobry, że można zdecydować się na wyjście od pełnego przeszukania tabeli T2 i ponownie
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

SQL. Optymalizacja
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: