ITA-101 Bazy Danych
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 7 Wersja 1.0
Indeksy i transakcje
Spis treści
Indeksy i transakcje ... 1
Informacje o module ... 2
Przygotowanie teoretyczne ... 3
Przykładowy problem ... 3
Podstawy teoretyczne... 3
Przykładowe rozwiązanie ... 9
Porady praktyczne ... 12
Uwagi dla studenta ... 13
Dodatkowe źródła informacji... 14
Laboratorium podstawowe ... 15
Problem 1 (czas realizacji 30 min) ... 15
Problem 2 (czas realizacji 15 min) ... 18
Laboratorium rozszerzone ... 20
Zadanie 1 (czas realizacji 90 min) ... 20
Informacje o module
Opis modułu
W module tym znajdziesz informacje na temat dostępu fizycznego do danych oraz optymalizacji dostępu. Poznasz indeksy i ich rodzaje, a następnie dowiesz się, jakie operacje wykonywane są na indeksach. W drugiej części poznasz transakcje, które służą do zapewnienia spójności bazy danych i mają wpływ na wydajność bazy danych. Dowiesz się, że obsługa transakcji nie jest rzeczą łatwą i wymaga rozwiązywania wielu trudnych problemów.
Cel modułu
Celem modułu jest zapoznanie czytelnika z podstawowymi mechanizmami indeksowania oraz struktury pisania transakcji. Zostaną przedstawione proste przykłady, które mają pokazać mechanizmy obowiązujące w metodach indeksowania oraz w transakcjach.
Uzyskane kompetencje
Po zrealizowaniu modułu będziesz:
• wiedział, na czym polega zasada działania indeksów
• potrafił we właściwy sposób dobierać politykę indeksowania
• znał zasadę funkcjonowania transakcji
• potrafił we właściwy sposób pisać proste transakcje Wymagania wstępne
Przed przystąpieniem do pracy z tym modułem powinieneś:
• umieć stworzyć bazę danych wraz z jej podstawowymi obiektami (patrz: moduł 3)
• znać zaawansowane mechanizmy programowania w języku T-SQL (patrz: moduł 9)
• znać podstawowe mechanizmy bezpieczeństwa (patrz: moduł 11) Mapa zależności modułu
Zgodnie z mapą zależności przedstawioną na rys. 1, przed przystąpieniem do realizacji tego modułu należy zapoznać się z materiałem zawartym w modułach 3, 8 i 11.
Rys. 1 Mapa zależności modułu
Przygotowanie teoretyczne
Przykładowy problem
Tak jak już Wiesz z bazy danych może korzystać wielu użytkowników, dysponujących różnorakimi uprawnieniami i mających dostęp do różnych obiektów w bazie danych. W związku z tym jednym z podstawowych zadań, jakie stoi przed osobami odpowiedzialnymi za prawidłowe funkcjonowanie bazy danych, jest dobór odpowiedniej polityki indeksowania, która pozwoli na szybsze przeszukiwanie bazy danych. Bezpośrednio z polityką indeksowania bazy danych związana jest optymalizacja i strojenie bazy danych, między innymi poprzez przebudowę indeksów.
Kolejnym mechanizmem, z jakim bardzo często możemy się spotkać, są transakcje. Według definicji są to operacje, które są niepodzielne i muszą być wykonane w całości. Innym ważnym zadaniem transakcji jest zapewnienie spójności bazy danych oraz umożliwienie równoległych operacji na niej.
W celu dobrego pisania transakcji potrzebna jest znajomość zaawansowanego programowania w języku T-SQL. Transakcje stanowią bardzo ważny element programowania baz danych i powinny być pisane profesjonalnie. Przykładem transakcji może być wypłata pieniędzy z bankomatu, płatność kartą debetową itp.
Podstawy teoretyczne Dostęp fizyczny do danych
Zrozumienie mechanizmu dostępu do danych zapisanych w bazie danych jest bardzo istotne dla zrozumienia zasad działania indeksów.
Jak wiadomo, dane w bazach danych w sposób trwały są zapisywane na dyskach optycznych, magnetycznych lub rodzinach nośników o dostępie bezpośrednim, takich jak macierze RAID (ang.
Redundant Array of Independent Disks). Zasady działania tego typu nośników oraz pojęcia głowicy, cylindrów, strony danych itp. powinny być Ci znane z przedmiotu „Podstawy Informatyki” lub podobnego.
System zarządzania bazami danych najczęściej nie zajmuje się fizyczną obsługą dysków. W procesie dostępu do danych biorą udział:
• Menedżer plików – posiada wiedzę o strukturze systemu plików i jest odpowiedzialny za odszukanie odpowiedniego pliku.
• Menedżer dysku – posiada wiedzę na temat fizycznej organizacji dysku i jest odpowiedzialny za odnalezienie odpowiedniej strony danych.
Schemat łańcucha dostępu do danych pokazany jest na rys. 2.
Optymalizacja dostępu
Zasadniczy czas dostępu do danych bazy to czas odczytu danych z dysków. W celu optymalizacji tego dostępu stosuje się metodę zwaną klastrowaniem.
Klastrowanie polega na dążeniu do utrzymania logicznie powiązanych rekordów blisko siebie na dysku. Taka organizacja danych znacznie przyspiesza dostęp do nich. Aby odczytać dane powiązane, głowica nie musi wykonywać dużych ruchów, a tym samym maleją czasy wyszukiwania.
Rozróżniamy dwa rodzaje klastrowania:
• Klastrowanie wewnątrzplikowe – polega na grupowaniu rekordów obok siebie wewnątrz jednego pliku.
• Klastrowanie międzyplikowe – polega na umieszczaniu na stronie obok siebie rekordów pochodzących z więcej niż jednego pliku (tabeli).
Optymalizacja dostępu do danych sprowadza się w zasadzie do odpowiedniego zarządzania stronami i decydowania, w jaki sposób dane mają być klastrowane.
Więcej na temat fizycznej struktury danych możesz znaleźć w module 4 Indeksy i ich zastosowanie
Zastanowimy się teraz nad problemem wyszukiwania danych w tabeli. Na przykład załóżmy, że w tabeli Studenci chcemy znaleźć studenta o nazwisku Nowak.
Tab. 1 Przykładowa tabela bazy studentów
ID Nazwisko Imie Wydzial
1 Olacki Jan Elektryczny
2 Babicki Adam Mechaniczny
3 Nowak Jerzy Elektryczny
1 Adamski Adam Elektronika
Wiersze zapisane są w bazie w kolejności ich wpisywania i nie są w szczególny sposób sortowane.
Co robi wobec tego system, kiedy wydajemy polecenie odnalezienia rekordu zawierającego informacje o Nowaku, np.:
SELECT imie, nazwisko FROM Studenci WHERE Nazwisko = 'Nowak'
System musi przeszukać całą tabelę (skanowanie wszystkich stron danych zawierających dane z tabeli) i przejrzeć wszystkie rekordy tej tabeli, aby mieć pewność, że odnalazł rekordy zawierające nazwisko Nowak. Operacja taka jest oczywiście czasochłonna.
Podobnie jest, gdy w książce poszukujemy jakiegoś hasła (na przykład w podręczniku do baz danych szukamy informacji o indeksach). Aby znaleźć szukaną informację, powinniśmy przeczytać całą książkę. Na szczęście niektóre książki są wyposażone na końcu w specjalne zestawienie haseł – czyli w indeks.
Nasze postępowanie przebiega wówczas następująco:
1. Odszukujemy poszukiwane hasło w indeksie, który jest uporządkowany alfabetycznie (co znacznie ułatwia nam odnalezienie hasła).
2. Odczytujemy w indeksie numer strony, na której hasło to występuje w książce.
3. Otwieramy książkę na odpowiedniej stronie.
4. Przeglądamy stronę w poszukiwaniu naszego hasła.
5. Odczytujemy informacje związane z szukanym hasłem.
Idea działania indeksów w bazie danych jest dokładnie taka sama.
Indeks określony na atrybucie A relacji jest mechanizmem, który pozwala na efektywne wyszukiwanie krotek o ustalonej wartości składowej atrybutu A. Jak więc wyglądałby indeks dla tabeli Studenci?
Indeks określany jest dla konkretnego pola. Mówimy wówczas, że pole to jest polem indeksowanym. W wypadku tabeli Studenci możemy jako pole indeksowane wybrać pole Nazwisko, wówczas założenie indeksu na tym polu będzie oznaczało założenie przez system dodatkowej tabeli, w której umieszczone zostaną nazwiska studentów z tabeli Studenci oraz przesłanki, gdzie należy szukać (na której stronie danych) pełnej informacji o wybranych studentach. Dodatkowo rekordy w tabeli indeksu zostaną posortowane w kolejności alfabetycznej nazwisk. Poszukiwanie naszego studenta Nowaka będzie teraz przebiegać znacznie szybciej.
Działanie i rola indeksów polega głównie na przyspieszeniu wyszukiwania rekordów w bazie danych. Niestety obciążają one dodatkowo system w czasie aktualizacji lub wstawiania danych, bowiem oprócz umieszczenia rekordu w bazie musi on również dokonać wpisu w tabeli indeksu oraz ponownie posortować trzymane w niej rekordy.
Zalety i wady stosowania indeksów zebrano w tab. 2.
Tab. 2 Zalety i wady stosowania indeksów
Zalety Wady
Przyspieszają dostęp do danych Zajmują miejsce na dysku Wymuszają unikatowość wierszy Zwiększają obciążenie systemu
Niektóre z pól warto indeksować, inne natomiast nie powinny być nigdy indeksowane. Do pierwszej grupy można zaliczyć:
• klucze podstawowe i obce (często są indeksowane automatycznie)
• pola, po których często następuje wyszukiwanie
• pola, do których dostęp następuje w ustalonej, uporządkowanej kolejności Nie należy indeksować:
• pól, do których rzadko odwołują się zapytania
• pól, które zawierają tylko kilka wartości unikatowych
• pól zawierających dane typu image, bit czy obiekt OLE
Indeksy mogą być zakładane na jednym polu lub na kilku jednocześnie.
Rodzaje indeksów
Indeksy można klasyfikować w różny sposób. My podzielimy indeksy na dwie grupy:
• indeksy grupowane (klastrowe)
• indeksy niegrupowane (nieklastrowe) Indeksy grupowane
Indeks grupowany działa na podobnej zasadzie, co książka telefoniczna. Zawiera strony szybkiego dostępu do danych (w książce telefonicznej na początku znajduje się alfabetyczny spis mówiący o tym, na której stronie szukać nazwisk, firm czy instytucji zaczynających się na daną literę alfabetu).
Strony te są ułożone w odwrócone drzewo i przechowują tylko ułożone alfabetycznie wartości indeksowanego pola oraz wskaźniki do stron znajdujących się w niższej warstwie drzewa.
Na samym dole drzewa znajdują się strony zawierające posortowane wg indeksowanego pola dane (w książce telefonicznej zawartość też jest posortowana – wg nazwisk lub nazw firm czy instytucji).
Przeszukiwanie indeksu odbywa się z góry na dół na następującej zasadzie: porównywana jest szukana wartość pola indeksowanego z wartościami zapisanymi (i posortowanymi) na stronach indeksu – jeśli znajdzie się wartość „większa” od wartości szukanej, wtedy następuje skok jeden poziom niżej do strony wskazywanej przez ostatnią pozycję (na ostatnio sprawdzanej stronie), która nie była „większa” od szukanej wartości.
Każda pozycja na stronach zawiera wskaźnik do strony danych jeden poziom niżej w hierarchii indeksu. Dzięki takiej strukturze przeszukiwane są tylko wybrane strony z danymi, nie zaś wszystkie strony zawierające dane z wybranej tabeli.
Rys. 3 Indeks grupowany
Indeksy niegrupowane
Indeks niegrupowany działa na podobnej zasadzie, co indeks w typowej książce (ale nie w telefonicznej). Indeks niegrupowany budowany jest na stronach danych, które nie są sortowane.
Składa się z co najmniej dwóch poziomów: poziomu niepomocniczego i poziomów pomocniczych.
Na stronach poziomu niepomocniczego, podobnie jak w przypadku indeksu grupowanego, przechowywane są wartości indeksowanego pola ze wskaźnikami do poziomu niżej w drzewie indeksu.
Strony poziomu pomocniczego zwane też liśćmi i zawierają wskaźniki do konkretnych stron danych (które nie są posortowane). Wskaźnik taki zawiera następujące dane: ID pliku, numer strony, numer wiersza na stronie.
Przeszukiwanie indeksu niegrupowanego odbywa się na podobnej zasadzie, jak w indeksie grupowanym. Po dojściu do poziomu pomocniczego następują skoki do stron danych (do konkretnych wierszy).
Rys. 4 Indeks niegrupowany
Operacje na indeksach
Indeksy są najczęściej tworzone automatycznie dla kluczy głównych oraz pól, dla których ma być wymuszona unikatowość. Można też tworzyć je dla innych pól, korzystając z polecenia języka SQL CREATE INDEX.
Poniżej podano przykład utworzenia indeksu grupowanego dla dla pola Nazwisko w tabeli Recenzent:
USE PraceDyplomowe
CREATE CLUSTERED INDEX cl_nazwisko ON Recenzent (Nazwisko)
Indeks może też być zakładany na kilku polach jednocześnie. Poniższy przykład pokazuje sposób definiowania unikalnego indeksu na polach Nazwisko i Imie tabeli Osoba:
CREATE UNIQUE INDEX Indeks_Osoba ON Osoba (Nazwisko, Imie)
Założenie takiego indeksu spowoduje, że będzie możliwe wprowadzanie jedynie unikatowych par imię-nazwisko.
Transakcje
Transakcja jest ciągiem operacji wykonywanych na bazie danych, które są niepodzielne i muszą być wykonane w całości. Jest więc jednostką logiczną operowania na bazie. Podlega ona kontroli i sterowaniu.
Każda transakcja może składać się z następujących operacji:
• czytanie danej x przez transakcję T
• zapisanie danej x przez transakcję T
• wycofanie transakcji T
• zatwierdzenie transakcji T
Transakcje służą do zapewnienia spójności bazy danych oraz umożliwiają równoległe wykonywanie operacji na niej. Jeśli wiele procesów działa jednocześnie na bazie danych, to może łatwo dojść do utraty spójności danych, a co za tym idzie do błędów i otrzymywania niewłaściwych rezultatów operacji.
Transakcja zabezpiecza również przed częściowym wykonaniem zbioru operacji, które mogą być przerwane, na przykład w wyniku awarii. Klasycznym przykładem jest operacja polegająca na wypłacaniu pieniędzy z bankomatu. W uproszczeniu operacje realizowane w czasie wypłaty pieniędzy z bankomatu są następujące:
1. Klient wczytuje kartę magnetyczną i jest rozpoznawany.
2. Klient określa sumę wypłaty.
3. Konto klienta jest sprawdzane.
4. Konto jest zmniejszane o sumę wypłaty.
5. Wysyłane jest zlecenie do kasy.
6. Bankomat odlicza sumę i koryguje stan gotówki w bankomacie.
7. Bankomat wypłaca klientowi pieniądze.
Zaistnienie awarii w kroku 5 lub 6 może prowadzić do niezbyt przyjemnej sytuacji dla posiadacza konta. Transakcje pozwalają na uniknięcie tego typu niespodzianek.
Postulaty AICD
Każda transakcja powinna spełniać cztery postulaty. Są to tak zwane postulaty AICD.
1. Atomowość (ang. atomicity) – w ramach jednej transakcji wykonują się albo wszystkie operacje, albo żadna.
2. Spójność (ang. consistency) – o ile transakcja zastała bazę danych w spójnym stanie, po jej zakończeniu stan jest również spójny (w międzyczasie stan może być chwilowo niespójny).
3. Izolacja (ang. isolation) – transakcja nie wie nic o innych transakcjach i nie musi uwzględniać ich działania. Czynności wykonane przez daną transakcję są niewidoczne dla innych transakcji aż do jej zakończenia.
4. Trwałość (ang. durability) – po zakończeniu transakcji jej skutki są na trwale zapamiętane (na dysku) i nie mogą być odwrócone przez zdarzenia losowe (np. wyłączenie prądu).
Transakcje w SQL
W języku SQL każda transakcja rozpoczyna się słowem BEGIN TRANSACTION, a kończy operacją COMMIT oznaczającą pomyślne zakończenie transakcji lub operacją ROLLBACK, oznaczającą wycofanie transakcji.
Dowolne polecenie SELECT, INSERT, UPDATE, DELETE czy CREATE rozpoczyna transakcję.
Transakcja trwa aż do wydania komendy COMMIT (potwierdzającej) lub ROLLBACK (zrywającej, cofającej). Transakcja może objąć dowolną liczbę poleceń SELECT, INSERT, UPDATE, DELETE, CREATE i innych.
Stosowanie zamków oraz problem z zakleszczaniem
Zarządzanie transakcjami wymaga zastosowania specjalnego modułu (zwanego modułem planisty) oraz protokołu zarządzania transakcjami. W celu uniknięcia problemów ze współbieżnym dostępem stosuje się mechanizmy blokady dostępu do danych, tzw. zamki.
Wyróżniamy dwa typy zamków:
• Zamek typu X – założenie zamka tego typu całkowicie blokuje dostęp do obiektu dla innych transakcji.
• Zamek typu S – założenie zamka tego typu powoduje, że inne transakcje mogą czytać dane, ale nie mogą ich modyfikować.
Najczęściej stosowanym w bazach relacyjnych protokołem zarządzania transakcjami jest protokół blokowania dwufazowego 2PL (ang. two-phase locking). Reguły pracy protokołu 2PL są następujące:
1. Jeśli dana operacja pi(x) może być wykonana, to planista zakłada blokadę na daną x dla transakcji Ti i operację przekazuje do wykonania. Jeśli natomiast operacja ta nie może być wykonana, to umieszcza ją w kolejce.
2. Zdjęcie założonej blokady może nastąpić dopiero wtedy, gdy operacja zostanie wykonana.
3. Jeśli nastąpiło zdjęcie jakiejkolwiek blokady założonej dla transakcji T, to dla tej transakcji nie można założyć żadnej innej blokady.
Postępując zgodnie z tymi zasadami przebieg wykonania transakcji wymusza dwie fazy obsługi transakcji. W pierwszej fazie są zakładane blokady, a w drugiej następuje ich zdejmowanie. Faza druga jest znacznie szybsza od pierwszej.
Rys. 5 Protokół blokowania dwufazowego 2PL
Stosowanie zamków może prowadzić do zjawiska zwanego zakleszczaniem. Zakleszczanie polega na tym, że transakcje blokują się, oczekując siebie nawzajem. Powodem powstania zakleszczeń jest konieczność oczekiwania na zwolnienie zasobu, do którego dostęp potrzebują.
Weźmy pod uwagę dwie transakcje A i B. Transakcja A blokuje zasób X i żąda dostępu do zasobu Y.
Transakcja B blokuje zasób Y i żąda dostępu do zasobu X. W wyniku tego dochodzi do zakleszczenia i żadna z transakcji nie może wykonywać swojej akcji.
Inne przykłady zakleszczeń są pokazane na rys. 6.
Rys. 6 Zakleszczenie transakcji
Stemple czasowe oraz ziarnistość transakcji
Każda transakcja w momencie startu dostaje unikalny stempel czasowy. Stempel ten jest na tyle precyzyjny, aby móc po nim rozróżniać transakcje. Żadna zmiana nie jest nanoszona do bazy danych, transakcja działa na swoich własnych kopiach aż do potwierdzenia.
Każdy obiekt bazy danych przechowuje dwa stemple czasowe: transakcji, która ostatnio brała obiekt do czytania i transakcji, która ostatnio brała obiekt do modyfikacji. W momencie potwierdzenia sprawdza się stemple transakcji oraz wszystkich pobranych przez nią obiektów.
Można dość łatwo wyprowadzić reguły zgodności, np. jeżeli obiekt był aktualizowany i stempel na obiekcie do aktualizacji jest taki sam, jak stempel transakcji, to transakcja może zostać wykonana, w przeciwnym razie należy ją zerwać i uruchomić od nowa. Jeżeli obiekt był tylko czytany i stempel na obiekcie do aktualizacji jest starszy niż stempel transakcji, to transakcja może zostać wykonana, w przeciwnym razie należy ją zerwać i uruchomić od nowa.
Z problemem zakładania zamków wiąże się również problem ziarnistości. Ziarnistość oznacza decyzję, co będzie niepodzielną jednostką, na którą zakłada się blokady. Poziomy ziarnistości mogą dotyczyć: bazy danych, relacji, rekordu, elementu rekordu, pojedynczego atrybutu, fizycznej strony pamięci.
Grube ziarna mogą zapewnić znaczny poziom bezpieczeństwa, jednak dają niewielki stopień współbieżności procesów. Z kolei małe ziarna wiążą się z dużymi nakładami na zakładanie zamków oraz ich obsługę.
Przykładowe rozwiązanie Optymalizacja bazy danych
Bardzo często działająca od jakiegoś czasu baza danych, na której wykonuje się bardzo dużo operacji, zaczyna działać woniej niż podczas pierwszego uruchomienia. Wówczas do głównego zadania administratora bazy danych należy zoptymalizowanie jej oraz przebudowa indeksów. Do tego celu może posłużyć nam narzędzie SQL Server Profiler.
SQL Server Profiler to potężne choć rzadko wykorzystywane narzędzie do analizy problemów z wydajnością bazy danych. Wykorzystując profiler do przechwytywania śladów (ang. traces) aktywności bazy danych można analizować wzorce zapytań, aby wykryć problemy z wydajnością nawet przed ich uwidocznieniem w aplikacjach.
W pierwszym kroku należy zdefiniować ślad. Wewnątrz silnika bazy danych SQL Server udostępnia podsystem zdarzeń nazwany SQL Trace, który opiera się na zewnętrznych interfejsie. Interfejs ten pozwala na wywoływanie SQL Trace z wykorzystaniem różnorodnych parametrów.
SQL Server Profiler uruchamia się z menu SQL Server 2008 Performance Tools. Po jego otworzeniu należy wybrać File, a następnie New Trace, co pokazano na rys. 7.
Rys. 7 Definicja śladu
Każdy definiowany ślad musi zostać nazwany (w naszym przykładzie użyliśmy nazwy Test).
Następnie następuje wybór wzorca. Istnieje kilka wzorców, które najczęściej są monitorowane.
Oczywiście możemy ustawić pusty wzorzec i sami zdefiniować opcje. Na rys. 7 użyliśmy predefiniowanego wzorca Tuning. Następnie ustawiamy sposób zapisu. Mamy możliwość zapisu do pliku lub do tabeli. Zapis do tabeli nie jest polecany, gdyż SQL Trace może generować setki tysięcy wierszy danych śladu na minutę na obciążonym serwerze.
Gdy ślad jest w pełni zdefiniowany, należy go tylko uruchomić i zbierać dane. W tym celu należy kliknąć Run. Wynik działania śladu pokazano na rys. 8.
Rys. 8 Uruchomienie śladu
Po zakończeniu zbierania śladu kolejnym etapem jest użycie narzędzia Database Engine Tuning Advisor (DTA). Odgrywa ono ważną rolę w rozwiązywaniu problemów związanych z wydajnością, pozwalając wpływać na optymizator zapytań w celu otrzymania zaleceń dla indeksów, indeksowanych widoków lub partycji, które mogą zwiększyć wydajność.
W celu uruchomienia DTA należy z zakładki Tools wybrać Database Engine Tuning Advisor.
Następnie należy połączyć się z serwerem, wybrać plik obciążający do analizy – ten, do którego zapisaliśmy zbierany ślad (rys. 9) – oraz określić opcje strojenia (rys. 10).
Rys. 9 Definiowanie ogólnych opcji strojenia bazy danych
Rys. 10 Definiowanie strojenia bazy danych
Po skonfigurowaniu opcji strojenia DTA można zacząć analizę klikając Start Analysis, co pokazano na rys. 11.
Rys. 11 Strojenie bazy danych
Po wykonaniu analizy otrzymujemy zalecenia na przykład dotyczące przebudowy indeksów, które możemy zastosować względem naszej bazy danych.
Porady praktyczne Indeksowanie kolumn
• Indeksy powinny być planowane z myślą o najczęściej wykonywanych w Twojej bazie danych zapytaniach SELECT z klauzulą WHERE. Pierwszym krokiem powinno być zatem wypisanie najczęściej wykonywanych zapytań (koniecznie z klauzulą WHERE)
• Microsoft SQL Server umożliwia utworzenie w tabeli jednego indeksu grupowanego oraz 249 indeksów niegrupowanych. Pojedynczy indeks można założyć na maksymalnie 16 polach. To
jednak tylko maksymalne możliwości serwera. W praktyce w bazach danych OLTP (ang.
online transaction processing) nie tworzy się więcej niż 8 do 10 indeksów związanych z pojedynczą tabelą.
• Należy pamiętać, iż nie wszystkie zapytania wykorzystują indeksy. Np. w Microsoft SQL Server zapytanie:
SELECT imie, nazwisko FROM osoby WHERE nazwisko LIKE '%mar%'
nie wykorzysta indeksu grupowanego na polu nazwisko, ponieważ niemożliwe będzie porównanie wartości wzorca z wartościami na stronach indeksu.
• Indeksy mają kluczowe znaczenie dla optymalizacji wydajności baz danych. Dobrze zaprojektowane indeksy mogą znacząco poprawić szybkość operacji przeszukiwania bazy danych (najczęściej wykonywana operacja i dotyczy największych ilości danych), ale źle zaplanowane mogą spowodować efekt odwrotny do pożądanego. Z tego powodu należy starannie zaplanować ich strukturę.
• Database Engine Tuning Advisor (DTA) to znacznie udoskonalony następca narzędzia Index Tuning Wizard dostarczanego wraz z SQL Server 2000.
• Żeby Database Engine Tuning Advisor (DTA) mógł przeprowadzić optymalizację bazy danych w oparciu o plik zawierający ślad, musi on mieć co najmniej 2 MB zebranych danych.
Transakcje
• Zakleszczenia są poważnym problemem i mają istotny wpływ na wydajność systemu. Walka z zakleszczeniami może przebiegać według dwu metod:
a) Wykrywanie zakleszczeń i rozrywanie pętli zakleszczenia – detekcja zakleszczenia wymaga skonstruowania grafu czekania (ang. wait-for graph) i tranzytywnego domknięcia tego grafu (złożoność gorsza niż n * n). Rozrywanie pętli zakleszczenia polega na wybraniu transakcji „ofiary” uczestniczącej w zakleszczeniu, a następnie jej zerwaniu i uruchomieniu od nowa. Wybór „ofiary” podlega różnym kryteriom – może nią być transakcja najmłodsza, najmniej pracochłonna, o niskim priorytecie itp.
b) Niedopuszczanie do zakleszczeń – istnieje wiele tego rodzaju metod, np.:
• Wstępne żądanie zasobów – przed uruchomieniem każda transakcja określa potrzebne jej zasoby. Nie może później nic więcej żądać. Wada: zgrubne oszacowanie żądanych zasobów prowadzi do zmniejszenia stopnia współbieżności.
• Czekasz-umieraj (ang. wait-die) – jeżeli transakcja próbuje dostać się do zasobu, który jest zablokowany, to natychmiast jest zrywana i powtarzana od nowa. Metoda może być nieskuteczna dla systemów interakcyjnych (użytkownik może się denerwować koniecznością dwukrotnego wprowadzania tych samych danych) oraz prowadzi do spadku efektywności (sporo pracy idzie na marne).
• Postulat możliwości odtworzenia stanu systemu sprzed wywołani transakcji w wypadku jej awarii (wycofania) możliwy jest dzięki m.in. zastosowaniu osobnego dziennika transakcji.
Mogą być w nim zapisywane wszystkie dane, na których pracuje transakcja lub też tylko dane różnicowe. Należy jednak pamiętać, że obsługa transakcji wiąże się z dodatkowymi nakładami i obciążeniem dla systemu.
Uwagi dla studenta
Jesteś przygotowany do realizacji laboratorium jeśli:
• rozumiesz mechanizm działania i polityki indeksowania
• rozumiesz zasadę działania transakcji
• znasz składnię nakładania indeksów na poszczególne pola
• rozumiesz sposób działania transakcji
• rozumiesz mechanizm zakleszczania transakcji
Pamiętaj o zapoznaniu się z uwagami i poradami zawartymi w tym module. Upewnij się, że rozumiesz omawiane w nich zagadnienia. Jeśli masz trudności ze zrozumieniem tematu zawartego w uwagach, przeczytaj ponownie informacje z tego rozdziału i zajrzyj do notatek z wykładów.
Dodatkowe źródła informacji
1. Kalen Delaney, Microsoft SQL Server 2005: Rozwiązania praktyczne krok po kroku, Microsoft Press, 2006
W książce autor przedstawia między innymi w jaki sposób należy stosować transakcje w celu zapewnienia bezpiecznej współbieżności bazy danych. Pokazuje jak należy definiować transakcje w SQL Server oraz jak zarządzać odseparowanymi transakcjami. W pozycji tej znajdziesz dużo rozwiązań praktycznych.
2. Deren Bieniek, Randy Dyess, Mike Hotek, Javier Loria, Adam Machanic, Antonio Soto, Adolfo Wiernik, SQL Server 2005. Implementacja i obsługa, APN Promise, 2006
W książce obszernie przedstawiono zagadnienia związane z tworzeniem indeksów oraz transakcjami. Znajdziesz tu potrzebne informacje na temat struktury indeksu, tworzenia indeksów klastrowych i nieklastrowych oraz na temat tworzenia transakcji. Znajdziesz tutaj również szczegółowe informacje na temat optymalizacji bazy danych oraz przebudowy indeksów. Książka szczególnie polecana ze względu na dużą zawartość ćwiczeń laboratoryjnych.
3. Ramez Elmasri, Shamkant B. Navathe, Wprowadzenie do systemów baz danych, Helion, 2005 W książce tej znajdziemy dużo szczegółowych informacji na temat indeksowania oraz zagadnień związanych z przetwarzaniem transakcji. Pozycja szczególnie polecana dla osób pragnących poszerzyć swoją wiedzę z tej tematyki.
Laboratorium podstawowe
Problem 1 (czas realizacji 30 min)
Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że firma planuje rozszerzenie na skalę uczelnianą systemu prac dyplomowych, którym zarządzała dotąd na Twoim wydziale. Zadanie, jakie przed Tobą stoi, to zaplanowanie struktury indeksów.
Zadanie Tok postępowania
1. Stwórz listę najczęściej wykonywanych zapytań
• Wypisz najczęściej wykonywane zapytania na bazie danych PraceDyplomowe, służące do wyszukiwania prac:
— według tytułów
— według autorów
— według promotorów
— według recenzentów
Zastanów się, jakie inne zapytania z klauzulą WHERE można utworzyć dla bazy danych PraceDyplomowe.
2. Wybierz pola
do indeksowania • Mając przed sobą listę najczęściej wykonywanych zapytań oraz strukturę tabel zaplanuj strukturę indeksów. Kandydatami do indeksowania są kolumny lub kombinacje kolumn, które:
— narzucają porządek sortowania (indeks grupowy)
— przechowują wartości częściej odczytywane niż modyfikowane
— są wykorzystywane do łączenia (JOIN) lub wyszukiwania (WHERE) danych
— przechowują różnorodne wartości
Zastanów się, jakie inne kolumny są dobrymi kandydatami na to, żeby założyć na nie indeks.
3. Nawiąż połączenie z SQL Server 2008
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku D:\VirtualPC\Dydaktyka\BD2008.vhd.
• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom SQL Server Management Studio.
• W oknie logowania kliknij Connect.
4. Przygotuj
tabelę • Z menu głównego wybierz File -> Open -> File.
• Odszukaj plik C:\Lab08\Indeksy.sql i kliknij Open.
• Kliknij w menu głównym programu SQL Server Management Studio na Tools -> Customize.
• W oknie Customize kliknij przycisk Keybord.
• W pozycji odpowiadającej kombinacji klawiszy Ctrl+F1 wpisz sp_helpindex i wciśnij OK. Wynik powyższych operacji pokazano na rys.
12.
Rys. 12 Definicja klawiszy skrótu
Dzięki zdefiniowaniu klawiszy skrótu w prosty sposób będziesz mógł wywoływać z klawiatury procedurę składowaną sp_helpindex, która wyświetla informacje o indeksach w wybranej tabeli.
• Stwórz kopie tabeli Osoba:
-- (2) Tworzymy kopie tabeli Osoba IF EXISTS (
SELECT name FROM sysobjects
WHERE name='Osoba_kopia') DROP TABLE Osoba_kopia GO
SELECT Imie, Nazwisko, Nr_Indeksu INTO Osoba_kopia
FROM Osoba GO
• Uruchom zapytanie do tabeli Osoba_kopia:
-- (3) Wyswietl zawartość tabeli Osoba_kopia SELECT * FROM Osoba_kopia
GO
• Podkreśl w edytorze słowo Osoba (nazwa twojej tabeli) i wciśnij CTRL+F1 lub wykonaj poniższy fragment skryptu. Powinieneś zobaczyć informację pokazaną na rys. 13.
-- (4) zobaczmy indeksy w tabeli EXEC sp_helpindex Osoba_kopia GO
Rys. 13 Informacja o indeksach w tabeli Osoba
Zastanów się, dlaczego w wyniku wywołania procedury składowanej odpowiedzialnej za wyświetlanie informacji o indeksach pojawia się wpis z indeksem PK_Osoba, skoro żaden indeks nie został założony?
• Włącz pomiar statystyk wejścia/wyjścia ilości skanowanych stron wykonując poniższe polecenie:
-- (5) Wlacz statystyki wejscia/wyjscia ilosci skanowanych stron SET statistic io on
GO
• Zaznacz i uruchom (F5) poniższy fragment kodu:
-- (6) wykonajmy proste wyszukiwanie po Nr_Indeksu -- oraz po nazwisku
SELECT * FROM Osoba
WHERE Nr_Indeksu > 1000 AND Nazwisko = 'K%' GO
5. Utwórz indeks • Zaznacz i uruchom (F5) poniższy fragment kodu:
-- (7) utworzmy indeks niegrupowany
CREATE INDEX index_Osoba ON Osoba(Nazwisko, Nr_Indeksu) GO
• Ponownie podkreśl w edytorze słowo Osoba (nazwa twojej tabeli) i wciśnij CTRL+F1. Powinieneś zobaczyć informację pokazaną na rys. 14.
Rys. 14 Informacja o indeksach w tabeli Osoba
6. Przetestuj
indeks • Zaznacz i uruchom (F5) poniższy fragment kodu:
-- (6) wykonajmy proste wyszukiwanie po Nr_Indeksu -- oraz po nazwisku
SELECT * FROM Osoba
WHERE Nr_Indeksu > 1000 AND Nazwisko = 'K%' GO
Zauważ, iż wynik będzie oczywiście taki sam, ale pojawi się różnica w statystykach.
Problem 2 (czas realizacji 15 min)
Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że firma planuje rozszerzenie na skalę uczelnianą systemu prac dyplomowych, którym zarządzała dotąd na Twoim wydziale. Zadanie jakie przed Tobą stoi to stworzenie transakcji dla rozwijanej bazy danych.
Zadanie Tok postępowania
1. Nawiąż połączenie z SQL Server 2008
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku D:\VirtualPC\Dydaktyka\BD2008.vhd.
• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom SQL Server Management Studio.
• W oknie logowania kliknij Connect.
2. Przygotuj
tabelę • Z menu głównego wybierz File -> Open -> File.
• Odszukaj plik C:\Lab08\Transakcje.sql i kliknij Open.
• Zaznacz kod, który wyświetla fragment tabeli, oznaczony w komentarzu (1) i (2).
-- (1) Ustaw sie na bazie danych PraceDyplomowe USE PraceDyplomowe
GO
-- (2) Wyswietlamy dwa pierwsze rekordy z tabeli Osoba SELECT *
FROM Osoba
WHERE ID_Osoby IN (1,2)
• Zaznacz kod, który próbuje wykonać transakcje:
-- (3) Przykladowa transakcja BEGIN TRANSACTION
BEGIN TRY UPDATE Osoba
SET Nazwisko = 'Kochan' WHERE ID_Osoby = 2 UPDATE Osoba
SET Nazwisko = 'Kowalski' WHERE ID_Osoby = 6
END TRY BEGIN CATCH SELECT
ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION GO
Powyższy kod podejmuje próbę wykonania transakcji złożonej z dwóch operacji UPDATE. Próba wykonania pierwszej z nich spowoduje błąd, ponieważ wartości w kolumnie ID_Osoby w tabeli Osoby muszą być unikalne, a polecenie próbuje wstawić duplikat wartości 2, która już istnieje w rekordzie drugim tabeli. Przechwytywanie błędów odbywa się przy użyciu bloków TRY...CATCH, które poznałeś w module 9.
Zastanów się, jak powinieneś zmodyfikować powyższą transakcję, żeby zakończyła się powodzeniem?
Laboratorium rozszerzone
Zadanie 1 (czas realizacji 90 min)
Jesteś administratorem w firmie National Insurance. Właśnie dowiedziałeś się od swojego szefa, że firma zarządzająca bazą AdventureWorks planuje rozszerzenie i modernizację systemu w celu spełnienia pewnych standardów. W związku z modernizacją systemu bazodanowego, a zatem zmianą struktury fizycznej bazy danych, najprawdopodobniej będzie musiała nastąpić przebudowa struktury indeksów. Poza przebudową indeksów będzie musiała zapewne nastąpić modernizacja istniejących transakcji tak, żeby odpowiadały one nowej strukturze fizycznej przebudowanej bazy danych.
Zadania jakie przed Tobą stoją to:
1. Podjęcie decyzji, które obiekty w bazie danych pozostaną bez zmian, a które zostaną zmodyfikowane lub usunięte.
2. Podjęcie decyzji, na które pola opłaca się nałożyć indeks i jakiego typu te indeksy powinny być.
3. Podjęcie decyzji, które transakcje należy zmodernizować i czy nie trzeba będzie dodać nowych transakcji.
