#291 Rozdziaｳ 13. Wydajno懈 i integralno懈 bazy danych Projekt dobrej aplikacji bazy danych jest nierozerwalnie zwiｹzany z zagadnieniami wydajno彡i i integralno彡i. W chwili przekazania aplikacji do uｿytku, stopie・jej wydajno彡i nabiera duｿego znaczenia. Jeｿeli aplikacja dziaｳa w spos maｳo efektywny, szans・na to, ｿe zostanie zaakceptowana przez uｿytkownik sｹ niewielkie. Podobnie rzecz ma si・w przypadku integralno彡i danych. Jeｿeli nie mamy pewno彡i co do poprawno彡i danych, na ktych pracujemy, nie moｿemy polega・na bazie danych pod ｿadnym istotnym wzgl鹽em. Niestety, te dwie istotne cechy kaｿdej aplikacji bazy danych sｹ, pod pewnymi wzgl鹽ami, sobie przeciwstawne. Wiele krok podejmowanych w celu zapewnienia integralno彡i bazy danych powoduje obniｿenie jej wydajno彡i. Dla wi麑szo彡i aplikacji koszt ten jest nieistotny, poniewaｿ dodatkowe obciｹｿenie naｳoｿone na baz・danych nie jest na tyle duｿe, aby mogｳo wpｳynｹ・na jej ognｹ wydajno懈. Mimo to, warto jednak zna・techniki umoｿliwiajｹce zwi麑szanie wydajno彡i bazy danych oraz ponoszone przy tym koszty. Dzi麑i tej wiedzy programista moｿe podejmowa・忤iadome decyzje co do projektu przyszｳej bazy danych, zapewniajｹc jej odpowiedniｹ wydajno懈 bez utraty integralno彡i. Zwi麑szanie wydajno彡i bazy danych Pierwszｹ rzeczｹ dotyczｹcｹ wydajno彡i bazy danych, o ktej trzeba wiedzie・ jest to, ｿe moｿe ona by・mierzona na wiele rnych sposob. Niekte metody optymalizacji zwi麑szajｹ ognｹ wydajno懈 bazy danych, podczas gdy inne poprawiajｹ tylko niekte jej aspekty, ale jednocze從ie obniｿajｹ inne. Wydajno懈 bazy danych moｿe by・mierzona w oparciu o nast麪ujｹce parametry: * Czas odpowiedzi. * Liczba obsｳugiwanych jednocze從ie uｿytkownik. * Liczba transakcji na dzie・ #292 Aplikacje majｹ minimalne wymagania w odniesieniu do kaｿdego z tych parametr, ale w niektych przypadkach moｿna postara・si・o lepsze wyniki na tle jednego z nich kosztem pozostaｳych. Projekt bazy danych a jej wydajno懈 Zanim zaczniesz zastanawia・si・nad tym, jak konstruowa・zapytania, aby wykonywaｳy si・one w najbardziej wydajny spos, powiniene・upewni・si・ czy projekt bazy danych zapewnia optymalne warunki jej dziaｳania. Jak mili徇y w rozdziale 2., istnieje kilka filozofii projektowania baz danych. Konstruujｹc baz・danych, naleｿy kierowa・si・schematem, w oparciu o kty powstaje aplikacja (DES[DES (ang. decision support) - systemy wspomagania decyzji (przyp. tｳum).] lub OLTP[OLTP (ang. online transaction proccessing) - przetwarzanie transakcji w trybie rzeczywistym (przyp. tｳum.).]). Wi鹹ej informacji na temat obu schemat aplikacji znajdziesz w rozdziale drugim (podrozdziaｳ Ц praktyce"). Podstawowa zasada zwi麑szania wydajno彡i: im mniejszy typ danych uｿyty do realizacji zadania, tym lepiej. Wytｳumaczenie tej zasady jest bardzo proste: im mniej miejsca zajmujｹ dane, tym mniej czasu trzeba na odnalezienie ich w pami鹹i lub odczytanie z dysku. Trzeba pami黎a・o tym, ｿe z rnymi typami danych oraz ustawieniami kolumn zwiｹzana jest pewna nadmiarowa porcja danych. Na przykｳad pola znakowe o zmiennej dｳugo彡i wymagajｹ kilku dodatkowych bajt do zapisania rozmiaru danych przechowywanych w kaｿdym polu. Niemniej jednak, gdy dｳugo彡i danych przechowywanych w polach tego typu sｹ zrnicowane w znacznym stopniu, zysk w pornaniu z uｿyciem p o staｳej dｳugo彡i rekompensuje koszt informacji nadmiarowej. Pewne oszcz鹽no彡i wiｹｿｹ si・rnieｿ z uｿyciem p typu NOT NULL zamiast NULL. Pola mogｹce przechowywa・warto彡i NULL zuｿywajｹ kilka bajt pami鹹i do zapisania informacji o tym, czy warto懈 w polu jest pusta, czy teｿ nie. Niekte bazy danych posiadaj ｹ typ danych BIT, wymagajｹcy jednego bitu pami鹹i, do przechowywania warto彡i typu logicznego (Boolean). Uｿycie tego typu niesie ze sobｹ dwie korzy彡i. Pierwsza to minimalna ilo懈 zajmowanej pami鹹i. Druga korzy懈, to eliminacja konieczno彡i pami黎ania o tym, jakie warto彡i reprezentujｹ prawd・i faｳsz w polu danych. Na przykｳad, jeｿeli do zapisywania warto彡i typu logicznego uｿyjemy pola CHAR (1), naｳoｿymy na siebie przykry obowiｹzek pami黎ania, jaka warto懈 w tym polu reprezentuje prawd・(np. T, Y lub 1), a jaka faｳsz (np. F, N lub 0). Narz鹽zia pomiaru wydajno彡i Za kaｿdym razem, kiedy uruchamiamy zapytanie, jest dla niego przygotowywany plan wykonania (ang. execution plan). Polecenia zawarte w tym planie sｹ nast麪nie kolejno wykonywane przez baz・danych. #293 W pewnym sensie, zapytania moｿna przyrna・do problem matematycznych. Proste problemy matematyczne wymagajｹ wykonania pojedynczej operacji, np. dodajｹc dwa do dwh, wykonujemy jednｹ dodatkowｹ operacj・ w wyniku ktej otrzymujemy wynik cztery. Jeｿeli mnoｿymy przez siebie dwie wielocyfrowe liczby w spos r鹹zny, najpierw wykonujemy pojedyncze mnoｿenie dla kaｿdej z cyfr jednej liczby, a nast麪nie dodajemy do siebie otrzymane wyniki. Liczba operacji ro從ie wraz ze stopniem skomplikowania wyraｿe・ Podobnie wyglｹda plan wykonania - jest to lista wszystkich pojedynczych operacji wykonywanych w celu otrzymania rezultat wi麑szego zapytania. W matematyce oraz w zapytaniach baz danych istnieje na og wiele metod pozwalajｹcych na rozwiｹzanie zｳoｿonego problemu. Czｳowiek rozwiｹzujｹcy zadanie wybiera metod・najｳatwiejszｹ z jego punktu widzenia. W bazie danych za dobranie najbardziej efektywnego planu wykonania zapytania odpowiedzialny jest optymali-zator zapyta・(ang. ｹuery optimizer). Uｿywajｹc narz鹽zi do pomiaru wydajno彡i bazy danych moｿna obserwowa・operacje naleｿｹce do 彡ieｿki wykonania wybranej przez optymalizator dla zapytania. ...... -<封 Wi麑szo懈 baz danych posiada narz鹽zia pozwalajｹce na wglｹd w plan wykonania zapyta・oraz okre徑enie, czy zastosowanie podpowiedzi dla optymalizatora przynosi popraw・wydajno彡i zapyta・ Moｿna rnieｿ napisa・kilka rnych zapyta・i sprawdzi・ kte z nich cechuje si・najlepszｹ wydajno彡iｹ. Bazy danych wykorzystujｹce Transact-SQL (Microsoft SQL Server oraz wszystkie produkty Sybase) posiadajｹ ustawienie SHOWPLAN udost麪niajｹce uｿytkownikom analizy wykonania ich zapyta・ W Oracle'u do tego samego celu sｳuｿy ustawienie auto-trace. Narz鹽zia te rozbijajｹ zapytania na indywidualne operacje, kte skｳadajｹ si・na ich wykonanie. Obu tym narz鹽ziom przyjrzymy si・za chwil・z osobna. AUTOTRACE (Oracle) Istniejｹ dwie metody pozwalajｹce na obejrzenie planu wykonania okre徑onego zapytania. Jeｿeli chcemy zobaczy・plan wykonania zapytania przed jego faktycznym uruchomieniem, moｿemy do tego celu uｿy・polecenia EXPLAIN PLAN. Je徑i interesuje nas wyｳｹczenie planu wykonania zapyta・po ich uruchomieniu, moｿemy uｿy・polecenia SET AUTOTRACE ON. Wyｳｹczenie podglｹdu planu wykonania nast麪uje po ustawieniu AUTORACE na warto懈 0FF (SET AUTOTRACE 0FF). ================== Rada Ustawienie AUTOTRACE w zupeｳno彡i wystarcza do przeglｹdania plan wykonania wyraｿe・typu SELECT. Jeｿeli jednak uｿywasz wyraｿe・manipulujｹcych danymi, do ich przeglｹdania lepiej skorzysta・z polecenia EKPLAIN PLAN, kte wy忤ietla plany wykona・zapyta・bez faktycznej modyfikacji danych w tablicach. ================== Wybiegnijmy na chwil・w prz ｿeby pokaza・ jak dziaｳa polecenie EXPLAIN PLAN. Aby uｿy・polecenia EXPLAIN PLAN, naleｿy wpisa・EKPLAIN PLAN FOR, a nast麪nie zapytanie. Plan wykonania przykｳadowego zapytania pokazuje listing 13.1. #294 -------------------------------- Listing 13.1. Plan wykonania prostego zapytania EXPLAIN PLAN FOR SELECT * FROM Movies Explained. -------------------------------- Aby polecenie EXPLAIN PLAN mogｳo dziaｳa・ musi istnie・specjalna tablica, przechowujｹca plany zapyta・ Tablic・takｹ (pod nazwｹ PLAN_TABLE) tworzy specjalny skrypt, o nazwie utlxplan.sql, dostarczany razem z systemem Oracle. Jeｿeli utworzymy wｳasnｹ tablic・dla plan (o strukturze identycznej ze strukturｹ tablicy PLAN_TABLE), jej nazw・musimy poda・w wyraｿeniu EXPLAIN PLAN. Do identyfikacji odr鹵nych plan w tablicy sｳuｿy pole STATMENT_ID, ktemu trzeba nada・odpowiedniｹ warto懈 1 w wyraｿeniu EXPLAIN PLAN, tak jak wida・to na przykｳadzie z listingu 13.2. -------------------------------- Listing 13.2. Zapisywanie planu zapytania EKPLAIN PLAN SET STATEMENT_ID = 'foo' FOR SELECT movie_title FROM Movies Explained. -------------------------------- Do wydobycia danych z tablicy plan, po wykonaniu polecenia EKPLAIN PLAN, wystarczy uｿy・zwykｳej instrukcji SELECT, jak ta w listingu 13.3. W tym przypadku wynikiem zapytania sｹ dwie kolumny, ale oprz nich istnieje jeszcze wiele innych danych zapisanych w tablicy plan. -------------------------------- Listing 13.3. Wydobywanie wynik polecenia EKPLAIN PLAN z tablicy plan: SELECT operation, object_name FROM PlanJTable WHERE statment id = 'foo' OPERATION OBJECT_NAME -------------------------------- SELECT STATMENT TABLE ACCESS MOVIES -------------------------------- AUTOTRACE jest troch・ｳatwiejszy w uｿyciu niｿ EXPLAIN PLAN. Wyniki zapytania generowane przy wｳｹczonej opcji AUTOTRACE przedstawia listing 13.4. -------------------------------- Listing 13.4. Plan wykonania tworzony przy wｳｹczonej opcji AUTOTRACE SET AUTOTRACE ON; SELECT stuio_name FROM Studios; STUIO NAME ----------- Giant MPM FKG #295 Delighted Artists Metaversal Studios Some Studio 6 rows selected. Execution Plan -------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) 0F 'STUDIOS' Statistics ---------------- 219 recursive calls 4 db bｳock gets 44 consistent gets 3 physical reads 0 redo size 662 bytes sent via SQL*Net to client 659 bytes receiyed via SQL*Net from client 4 SQL*Net roundtrips to/from client 4 sorts (memory) 6 rows processed -------------------------------- Jak wida・ ilo懈 danych dost麪nych po wｳｹczeniu opcji AUTOTRACE jest spora. Wygenerowane tabele zawierajｹ informacje dotyczｹce uｿycia bazy danych i zasob systemowych przez zapytanie. Nas interesuje bardziej cze懈 dotyczｹca planu wykonania (tabela...
sliwak