mechanizmy_wewnetrzn..
advertisement
Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy” Na przykładzie SQL Server 2008 informatyka + 2 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 3 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 4 Więzy integralności referencyjnej • W bazach danych często występuje konieczność zdefiniowana relacji pomiędzy dwoma tabelami np.: – klient jest właścicielem rachunku bankowego – rachunek jest określonego typu (oszczędnościowy, rozliczeniowy…) • Do tego celu służą więzy integralności referencyjnej • Chcemy powiązać klienta z rachunkiem bankowym: JAK ? • Wymaganie: Rachunek ma dokładnie jednego właściciela. informatyka + 5 Więzy integralności referencyjnej • Dodajmy kilku klientów i zobaczmy ich dane: • Rezultat: • Mamy troje klientów o identyfikatorach 1, 2 i 3. informatyka + 6 Więzy integralności referencyjnej • Dodajmy teraz kilka rachunków i zobaczmy ich dane: • Rezultat: Nie ma takiego klienta! • Rachunki zostały utworzone (automatycznie nadane numery i daty utworzenia) • Mamy cztery rachunki. Trzy należą do naszych klientów. • Czwarty rachunek – nie wiadomo do kogo! Utraciliśmy właśnie spójność danych :-( informatyka + 7 Więzy integralności referencyjnej • Jak zabezpieczyć się przed tego typu błędami? • Klucz obcy – kolumna lub kombinacja kolumn, która jest używana do określenia i wymuszenia relacji pomiędzy danymi z dwóch tabel Kolumna z kluczem podstawowym lub unikalnym Stworzone ograniczenie – klucz obcy informatyka + Kolumna tego samego typu! 8 Więzy integralności referencyjnej • Spróbujmy więc utworzyć klucz obcy na naszej tabeli rachunki: • Nic z tego! Nie udało się utworzyć ograniczenia ze względu na istniejące dane (feralny rachunek z błędnym identyfikatorem właściciela) • Rezultat: Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Rachunki_Klienci". The conflict occurred in database "Bank", table "dbo.Klienci", column 'KlientID'. • Jak to naprawić? Opcja WITH NOCHECK lub poprawienie błędnych danych. informatyka + 9 Więzy integralności referencyjnej • Naprawiamy błędne dane: • Ponowne wykonanie polecenia tworzącego klucz obcy kończy się sukcesem! Od tej pory baza nie pozwoli na utworzenie rachunku dla nieistniejącego klienta: • Rezultat: Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Rachunki_Klienci". The conflict occurred in database "Bank", table "dbo.Klienci", column 'KlientID'. informatyka + 10 Więzy integralności referencyjnej • Nie ma natomiast problemu z dodaniem rachunku dla istniejącego klienta: Luka w numeracji – ślad po nieudanej próbie dodania rachunku • Rezultat: informatyka + 11 Więzy integralności referencyjnej • Przy tworzeniu klucza obcego można korzystać z opcji ON DELETE i ON UPDATE. • Służą one do określenia reakcji na usunięcie lub zmodyfikowanie wiersza (z tabeli z kluczem podstawowym) do którego odnosi się klucz obcy. Isnieją cztery warianty dla każdej : UWAGA! Bardzo – No action (domyślna) • Nie podejmuje żadnej akcji. wygodne i bardzo niebezpiecznie!!! – Cascade • Usuwa/modyfikuje wiersz z kluczem obcym – Set null • Ustawia wartość null jako wartość kolumn klucza obcego (działa jeżeli te kolumny dopuszczają wartość null!) – Set default • Ustawia wartość domyślną dla kolumn klucza obcego (działa jeśli te kolumny maja określona wartość domyślną i spełnia ona regułę klucza lub dopuszcza wartość null. informatyka + 12 Więzy integralności referencyjnej • Usuńmy nasz klucz obcy i stwórzmy go na nowo z opcją ON DELETE CASCADE: • Usuńmy teraz jednego klienta (posiadającego dwa rachunki): • Pobranie listy wszystkich rachunków daje teraz rezultat: • Usunęliśmy jeden wiersz z tabeli Klienci, a automatycznie zostały usunięte dwa rachunki należące do usuniętego klienta • Ta opcja jest bardzo niebezpieczna!!! informatyka + 13 Więzy integralności referencyjnej Garść faktów na temat kluczy obcych: • Klucz obcy może zawierać więcej niż jedna kolumnę – Uwaga! Jeśli w takim przypadku choć jedna z kolumn ma wartość null, to pozostałe nie są sprawdzane pod kątem zgodności z regułą klucza! • Klucz obcy może odwoływać się do tej samej tabeli (samozłączenie) – Stosowane do budowania hierarchii – Łatwe w implementacji, trudne w obsłudze – Alternatywą jest typ danych HierarchyID lub XML. • Klucz obcy (podobnie jak ograniczenia typu CHECK) można włączać i wyłączać za pomocą polecenia ALTER TABLE z opcją CHECK lub NOCHECK CONSTRAINT. [nie mylić z WITH CHECK/ WITH NOCHECK !] informatyka + 14 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 15 Transakcje • Dane w bazie reprezentują aktualną sytuację biznesową – Mogą zawierać dane o zamówieniach, informacje o procesie produkcyjnym, o alokacji określonych zasobów i ich statusie itd., itp.. • Zmiany sytuacji biznesowej (stanu) powodują zmiany w danych w bazie – Pojawiają się nowe wiersze, modyfikowane są istniejące, zdarzają się też usunięcia wierszy. • Zmiana stanu powinna prowadzić od jednego stabilnego stanu do drugiego • Wszelkie stany „przejściowe” spowodowane dowolnym czynnikiem są niedopuszczalne! – Zapisanie tylko części zamówienia??? – Przelew bankowy wykonany połowicznie (środki pobrane, ale nie umieszczone na docelowym rachunku) ??? informatyka + 16 Transakcje • Transakcja to sekwencja logicznie powiązanych operacji na danych, których celem jest przejście bazy danych z jednego stanu spójnego do drugiego • Właściwości transakcji – akronim ACID – Atomicity (atomowość) • Operacje w ramach transakcji są niepodzielne. Albo wykonają się w całości, albo wcale – Consistency (spójność) • Baza danych jest w stanie spójnym zarówno przed rozpoczęciem transakcji jak i po jej zakończeniu (nieważne czy transakcja zakończyła się sukcesem czy porażką) – Isolation (odizolowanie) • Transakcje są od siebie logicznie odseparowane. Z punktu widzenia transakcji – wykonywane sekwencyjnie – Durability (trwałośc) • Jeżeli transakcja została zatwierdzona, to niezależnie od awarii systemu nie może zostać cofnięta bądź utracona informatyka + 17 Transakcje • Skoro to takie ważne, to czy nie wystarczy kolejkowanie transakcji i sekwencyjnie ich wykonywanie? • Nie. To kwestia wydajności! Takie podejście powodowałoby drastyczny spadek wydajności wraz ze wzrostem liczby transakcji (użytkowników) • Zależnie od specyfiki operacji wykonywanych w ramach transakcji można starać się zrównoleglać wykonywanie innych transakcji i operacji odczytu danych. • Możliwości „zrównoleglania” operacji sterowane są poprzez mechanizm blokad (locks). • Pojęcie poziomu izolacji odnosi się właśnie do tego zagadnienia - jakie blokady i na jaki czas są konieczne, żeby zapewnić odpowiedni poziom bezpieczeństwa dla transakcji. informatyka + 18 Transakcje • SQL Server obsługuje dwa tryby rozpoczynania transakcji: – Jawny (explicit) • Transakcja rozpoczyna się poleceniem BEGIN TRANSACTION – Niejawny (implicit) • Każde pierwsze polecenie modyfikujące dane( m.in. INSERT, UPDATE, DELETE) powoduje rozpoczęcie transakcji • Transakcję należy zakończyć jawnie (COMMIT lub ROLLBACK) • Wyłącza tryb autocommit! • Domyślnie SQL Server działa w trybie autocommit – Każde polecenie modyfikujące dane (m.in. INSERT, UPDATE, DELETE) powoduje rozpoczęcie transakcji. – Poprawne wykonanie polecenia powoduje automatyczne zatwierdzenie (COMMIT) transakcji. – Błąd w trakcie wykonania polecenia powoduje automatyczne wycofanie (ROLLBACK) transakcji informatyka + 19 Transakcje • Transakcje mogą być zagnieżdżane: • UWAGA! – COMMIT dla transakcji zagnieżdżonej tak naprawdę nie ma żadnego efektu… jedynie zmniejsza poziom zagnieżdżenia. – ROLLBACK powoduje wycofanie wszystkich transakcji łącznie z główną (zawierającą zagnieżdżone pozostałe). Ustawia poziom zagnieżdżenia na 0 – ROLLBACK z parametrem (nazwa punktu zapisu) wycofuje transakcje do tego punktu. Nie powoduje zmiany poziomu zagnieżdżenia. informatyka + 20 Transakcje • Aktualny poziom zagnieżdżenia transakcji można odczytać ze zmiennej @@TRANCOUNT • Rozpoczynając transakcje można nadać jej nazwę. • W trakcie transakcji można tworzyć za pomocą polecenia SAVE dodatkowe punkty zapisu (savepoint), do których będzie można wycofywać częściowo transakcję przez wywołanie polecenia ROLLBACK z parametrem – nazwą punktu zapisu. informatyka + 21 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 22 Transakcje • Konflikty i problemy występujące przy dostępie do danych poziomu więcej niż jednej transakcji (w przykładach są to transakcje T1 i T2): • Lost update (zgubiona modyfikacja) – T1 i T2 modyfikują wartość kolumny jedna po drugiej. Tylko ostatnia modyfikacja (zatwierdzona transakcja) będzie widoczna w bazie. • Dirty read (brudny odczyt) – T1 modyfikuje dane. Przed jej zatwierdzeniem, T2 odczytuje zmodyfikowane dane i wykorzystuje je. Jeśli T1 zostanie wycofana to T2 pracuje na niepoprawnych lub nieistniejących danych – niespójność! informatyka + 23 Transakcje • Konflikty i problemy występujące przy dostępie do danych poziomu więcej niż jednej transakcji ( w przykładach są to transakcje T1 i T2): • Nonrepeatable Read (niepowtarzalny odczyt) – T1 odczytuje te same dane dwukrotnie w trakcie działania. Pomiędzy jednym a drugim odczytem T2 modyfikuje te dane i zostaje zatwierdzona. W związku z tym drugi odczyt danych z poziomu T1 pobiera inne wartości niż pierwszy! Może to prowadzić do niespójności. • Phantom reads (odczyt – widmo) – T1 modyfikuje dane z określonego zakresu i następnie pobiera je do dalszej analizy. Pomiędzy modyfikacją a odczytem, T2 dodaje nowe wiersze do modyfikowanego przez T1 zakresu. T1 odczytuje dane i uzyskuje wiersze, których nie było przy modyfikacji. informatyka + 24 Transakcje • Standard ANSI definiuje cztery poziomy izolacji dla transakcji. Każdy z nich cechuje się eliminowaniem szans na wystąpienie kolejnego rodzaju konfliktu: [poziom domyślny został wyróżniony] Poziom izolacji Dirty read Nonrepeatable read Phantom read READ UNCOMMITED TAK TAK TAK READ COMMITED NIE TAK TAK REPEATABLE READ NIE NIE TAK SERIALIZABLE NIE NIE NIE • SQL Server posiada dwa dodatkowe poziomy izolacji (bazujące na wersjonowaniu wierszy): jeden jest implementacją poziomu READ COMMITED, drugi to poziom SNAPSHOT (funkcjonalnie zbliżony do SERIALIZABLE) informatyka + 25 Transakcje • Przy transakcjach warto wspomnieć o jeszcze jednym negatywnym zjawisku – zakleszczeniu. Rysowanie wykresu: - linijka - kreda • Dwie osoby chcą narysować wykres. Potrzebne do tego są: linijka i kreda. • Pierwsza osoba sięga po kredę, druga po linijkę… • W efekcie pierwsza zaczyna czekać na linijkę, druga na kredę… • Rozwiązanie – wylosować osobę (deadlock victim), zabrać jej linijkę lub kredę i oddać drugiej. informatyka + 26 Transakcje • Minimalizowanie szansy na wystąpienie zakleszczenia Czekam na linijkę Rysowanie wykresu: - linijka - kreda Chwyciłem linijkę Teraz tylko kreda… • Sięganie do zasobów wg tej samej kolejności! informatyka + 27 Transakcje • Kilka dobrych rad dotyczących transakcji • Starajmy się budować transakcje tak krótkie jak się da! Pozwala to skrócić czas aktywności blokad i poprawić wydajność • Planujmy kolejność uzyskiwania dostępu do zasobów w ramach transakcji aby unikać zakleszczeń • Mimo, iż SQL Server daje nam możliwości sterowania mechanizmem blokad – jeśli nie wiemy na 100% co robimy – lepiej nie ingerować w tę dziedzinę. Mechanizm ten sam z siebie działa bardzo dobrze. • Dobierajmy właściwy poziom izolacji transakcji dla konkretnych operacji. Korzystanie ze zbyt wysokiego powoduje spadek wydajności informatyka + 28 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 29 Wyzwalacze • Wyzwalacz to specjalny rodzaj procedury składowanej, która jest wywoływana automatycznie w reakcji na zajście określonego zdarzenia. • Wyzwalacze to sztandarowy mechanizm pozwalający na implementowanie w bazie reguł biznesowych i zapewnienie spójności danych w zakresie szerszym niż ograniczenia (constraints) • SQL Server posiada mechanizm wyzwalaczy dla DML (Data Manipulation Language) oraz DDL (Data Definition Language) • Korzystanie z wyzwalaczy jest przyjemne, ale muszą być one dokładnie udokumentowane! W przeciwnym razie w przypadku wystąpienia problemów z logiką bazy bardzo trudno będzie dociec źródła problemu. informatyka + 30 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 31 Wyzwalacze DML • Wyzwalacze mogą reagować na zdarzenia: INSERT, UPDATE i DELETE • Dwa rodzaje wyzwalaczy: AFTER i INSTEAD OF • Wyzwalacze AFTER wykonują się po operacji, która spowodowała ich uruchomienie i wchodzą w skład realizowanej transakcji • Wyzwalacze INSTEAD OF wykonują się zamiast wywołującej je operacji • Można deklarować wiele wyzwalaczy na tej samej tabeli, dla tego samego zdarzenia. UWAGA! W takiej sytuacji nie mamy zbyt dużego wpływu na kolejność wykonania wyzwalaczy. Można jedynie określić, który wykona się jako pierwszy i jako ostatni. informatyka + 32 Wyzwalacze DML • Dodajmy do naszej bazy jeszcze jedną tabelę • Będzie ona przechowywać informacje o operacjach wykonywanych na rachunku • Wykorzystamy wyzwalacze do zaimplementowania reguł biznesowych: – Nie można usunąć ani zmodyfikować raz wykonanej operacji – Minimalna kwota wypłaty z rachunku musi być większa lub równa 10 zł informatyka + 33 Wyzwalacze DML • Na pierwszy ogień weźmy blokadę modyfikacji i usuwania wpisów w tabeli Operacje. • Zrealizujemy to za pomocą wyzwalacza INSTEAD OF: • Dodajmy parę wpisów: • Spróbujmy teraz usunąć operację: • Rezultat: informatyka + 34 Wyzwalacze DML • Następny krok to implementacja drugiej reguły biznesowej – minimalna kwota wypłaty musi być większa lub równa 10 zł • Zrealizujemy to za pomocą wyzwalacza AFTER: • Spróbujmy wykonać wypłatę zbyt małej kwoty: Transakcja została wycofana • Rezultat: informatyka + 35 Wyzwalacze DML • W kodzie wyzwalacza mamy dostęp do dwóch specjalnych tabel : inserted i deleted • Tabela inserted zawiera listę dodawanych wierszy w ramach wykonywanego polecenia INSERT • Tabela deleted zawiera listę wierszy usuwanych w ramach wykonywanego polecenia DELETE • W przypadku wykonywania modyfikacji danych,(UPDATE) tabela inserted zawiera nowe wartości wierszy, a deleted stare. • Z tych tabel korzysta się przy tworzeniu kodu wyzwalaczy odwołującego się do modyfikowanych danych. informatyka + 36 Wyzwalacze DML • Ważne! Nie należy zakładać, że wyzwalacz będzie wywoływany zawsze dla modyfikacji pojedynczego wiersza! TAK NIE informatyka + 37 Wyzwalacze DDL • Wraz z pojawieniem się SQL Servera 2005 pojawił się nowy rodzaj wyzwalacza – wyzwalacz DDL • Wyzwalacze DDL mogą reagować na zdarzenia wywołania poleceń DDL (CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS ) • Przeznaczone do wspomagania audytu zmian w strukturze bazy danych i śledzenia jej zmian • Pozwalają też ograniczać swobodę modyfikowania struktury bazy danych lub tworzyć mechanizmy zabezpieczające przed przypadkową modyfikacją • W kodzie wyzwalacza dostępna jest funkcja EVENTDATA() zwracająca szczegółowe informacje o zdarzeniu w formie XML informatyka + 38 Wyzwalacze DDL • Stwórzmy wyzwalacz DDL, który zablokuje wszelkie modyfikacje tabel oraz próby ich usunięcia: Tabela testowa Wyzwalacz DDL Próba usunięcia tabeli Rezultat usuwania informatyka + 39 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 40 Procedury składowane i funkcje użytkownika • Procedura składowana przypomina funkcję (metodę) znaną z języków programowania: – Zawiera blok kodu, który jest wykonywany po jej wywołaniu\ – Może przyjmować parametry wywołania (wejściowe oraz wyjściowe) a także zwracać wartość (kod powrotu – wartość całkowita) – W jej kodzie można stosować instrukcje warunkowe i pętle • Pozwala na odcięcie aplikacji od szczegółów implementacyjnych bazy danych – tworzy warstwę abstrakcji danych • Można nadawać uprawnienia do jej wykonania informatyka + 41 Procedury składowane i funkcje użytkownika • W naszej bankowej bazie danych możemy zastosować procedurę składowaną do utworzenia rachunku dla nowego klienta. • Wymagania biznesowe: – Klient podaje swój adres email oraz imię i nazwisko – Zostaje dla niego utworzone konto. Otrzymuje jego numer. – Klient dostaje na dzień dobry 100 zł na swoje nowe konto • Zrealizujemy te wymagania za pomocą procedury, która przyjmie na wejściu dane klienta. • Numer nowootwartego rachunku zostanie zwrócony jako parametr wyjściowy informatyka + 42 Procedury składowane i funkcje użytkownika • Postać procedury zakładania promocyjnego konta: informatyka + 43 Procedury składowane i funkcje użytkownika • Wywołanie procedury: • Rezultat: • Sukces! Widać trzy udane wykonania polecenia insert oraz wygenerowany numer rachunku informatyka + 44 Procedury składowane i funkcje użytkownika • Funkcje użytkownika są podobne do procedur składowanych • Różnią się tym, że ich wywołania mogą być wykorzystane w charakterze wartości w wyrażeniach i zapytaniach. • Funkcje występują w dwóch wariantach (zależnie od typu zwracanej wartości): – Skalarne (scalar functions) – Tabelaryczne (mogą składać się z jednego zapytania SELECT lub z wielu wyrażeń) • Korzystanie z funkcji skalarnej : SELECT funkcja(parametr) • Korzystanie z funkcji tabelarycznej: SELECT * FROM funkcja(parametr) informatyka + 45 Procedury składowane i funkcje użytkownika • Funkcja obliczająca saldo wskazanego rachunku: • Wywołanie: • Rezultat: informatyka + 46 Procedury składowane i funkcje użytkownika • Funkcja tabelaryczna (inline) zwracająca n ostatnich operacji wykonanych na rachunkach: • Wywołanie: • Rezultat: informatyka + 47 Procedury składowane i funkcje użytkownika • Ta sama funkcja zrealizowana jako „multistatement” • Wywołanie: • Rezultat: informatyka + 48 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 49 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 50 Fizyczna organizacja danych w SQL Server 2008 • Logicznie tabela składa się z wierszy, które składają się z kolumn. • Jak te dane przechowywane są na dysku? • Jakie są ograniczenia przy definiowaniu tabel? • Jaki ma to wpływ na wydajność? informatyka + 51 Fizyczna organizacja danych w SQL Server 2008 • Podstawowa jednostka – strona (page) – Rozmiar: 8 KB (dokładnie 8060 bajtów na dane) – Jest to jednocześnie maksymalna długość wiersza (nie licząc kolumn przechowywanych na osobnych stronach) – Wiersz nie może być podzielony pomiędzy strony. Nagłówek Wiersz 1 Wiersz 2 Wiersz 3 … • Rodzaje stron – data (wszystkie dane z wyjątkiem kolumn typów: text, ntext, image, nvarchar(max), varchar(max), varbinary(max), xml ) – index (wpisy indeksów) – text/image (text, ntext, image, nvarchar(max), varchar(max), varbinary(max), xml oraz niemieszczące się w wierszu: varchar, nvarchar, varbinary) – GAM, (Global Allocation Map) SGAM (Shared GAM), IAM (Index Allocation Map) – wrócimy do nich! informatyka + 52 Fizyczna organizacja danych w SQL Server 2008 • • • • 8 KB (strona) to trochę mało… 8 stron – 64 KB to w sam raz na jednostkę alokacji! Jednostka ta zwana jest obszarem (extent). Rodzaje obszarów – Jednolite (uniform extent) • Zawierają strony należące do jednego obiektu ( tabeli /indeksu ) • Nagłówek Nagłówek – Mieszane (mixed extent) Nagłówek Wiersz 1 Nagłówek • Zawierają strony należące do więcej niż jednego obiektu Wiersz 1 Nagłówek Wiersz 1 Nagłówek Wiersz Wiersz 1 22 Nagłówek Wiersz Alokowane i odczytywane są zawsze Wiersz 1 2 Nagłówek Wiersz Wiersz 1 Wiersz Wiersz 2 33 Wiersz 1 Wiersz całe obszary a nie pojedyncze strony Wiersz 2 3 Wiersz 1Wiersz Wiersz 2 …3 Wiersz Wiersz 2 … Wiersz Wiersz 2… 3 3 Wiersz …3 Wiersz … Wiersz … 3 … … informatyka + 53 Fizyczna organizacja danych w SQL Server 2008 • Sterta (heap) – zbiór obszarów zawierających dane z jednej tabeli (lub partycji w przypadku tabel partycjonowanych) • Dane nie są ze sobą powiązane w żaden sposób • Wyszukiwanie wymaga przejrzenia wszystkich stron Nagłówek Nagłówek Nagłówek Nagłówek Nagłówek Nagłówek Nagłówek Nagłówek Nagłówek Nagłówek Nagłówek Nagłówek Wiersz 1 Wiersz 1 Wiersz 1 Nagłówek Nagłówek Nagłówek Wiersz 1 Nagłówek Wiersz 1 Wiersz 1 Wiersz 1 Nagłówek Nagłówek Nagłówek Wiersz 1 Nagłówek Wiersz 1 2 Wiersz 1 2 Wiersz 1 2 Nagłówek Nagłówek Nagłówek Wiersz 1 Wiersz Wiersz Wiersz Nagłówek Wiersz 1 2 Wiersz 1 2 Wiersz 1 2 Wiersz Nagłówek Nagłówek Nagłówek Wiersz 1 22 Wiersz Wiersz Wiersz Nagłówek Wiersz 1 Wiersz 1 Wiersz 1 Wiersz Nagłówek Wiersz 1 2 Wiersz 2 3 Nagłówek Wiersz 2 3 Nagłówek Wiersz 2 3 Nagłówek Wiersz 1Wiersz Wiersz 1Wiersz Wiersz 1Wiersz Wiersz Wiersz 1 Wiersz 2 Wiersz 2 Wiersz 2 Wiersz Wiersz 1Wiersz Wiersz 1Wiersz Wiersz 1Wiersz Wiersz 2 33 3 Wiersz 3 Wiersz 3 Wiersz 1 … Wiersz 2 Wiersz 2 Wiersz 2 Wiersz Wiersz 1 31 51 Wiersz 2 3 Wiersz Wiersz Wiersz Wiersz 4Wiersz Wiersz 2… 3 3 Wiersz 2… 3 3 Wiersz 2… 3 3 Wiersz 2 Wiersz Wiersz Wiersz … Wiersz 2… 3 Wiersz 2… 3 Wiersz 2… 3 Wiersz Wiersz 2… 3 3 Wiersz Wiersz Wiersz Wiersz 6 Wiersz 72 Wiersz 32 Wiersz … … … Wiersz 98 Wiersz Wiersz Wiersz …3 Wiersz … 33 … 33 … 33 Wiersz Wiersz Wiersz … Wiersz … … … Wiersz Wiersz Wiersz … 623 Wiersz … 3 … 13 … 93 … … … … … … … … … informatyka + … 54 Fizyczna organizacja danych w SQL Server 2008 • Tabela może składać się z jednej lub więcej partycji • Sterta jest tworzona osobno dla każdej partycji Tabela Partycja 1 Partycja 3 Partycja 2 informatyka + 55 Fizyczna organizacja danych w SQL Server 2008 • Skąd wiadomo które obszary są wolne, które są zajęte, do których obiektów należą obszary czy strony? • Ze stron GAM, SGAM i IAM ;-) – GAM (Global Allocation Map) – informacje o zajętych obszarach jednolitych (uniform) – SGAM (Shared GAM) - informacje o zajętych obszarach mieszanych (mixed) – IAM (Index Allocation Map) – informacje o przynależności obszarów do obiektów IAM informatyka + 56 Fizyczna organizacja danych w SQL Server 2008 • No dobrze, ale jak trafić do odpowiedniej strony IAM? • Każdy obiekt (tabela / indeks) ma wpisy w tabelach systemowych dotyczące alokacji jego danych • Dostęp do tych informacji – widok sys.partitions • Każda sterta, indeks, obszar LOB mają odpowiadający im wpis. Wpis ten zawiera wskaźnik do IAM • Wartośc kolumny index_id: – – – – 0 – sterta 1 – indeks zgrupowany 2..250 – indeksy niezgrupowane 255 – dane LOB sys.partitions id Index_id=0 IAM informatyka + 57 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 58 Indeks zgrupowany sys.partitions id Index_id=1 Root page korzeń gałęzie liście informatyka + 59 Indeks zgrupowany • • • • • • Struktura drzewiasta (B-tree) – drzewo zrównoważone Na poziomie korzenia i gałęzi – strony indeksu Na poziomie liści – właściwe strony z danymi z tabeli Dane fizycznie uporządkowane rosnąco wg klucza indeksu Tylko jeden indeks zgrupowany dla tabeli! Unikalność kluczy zapewniona wewnętrznie – • Kiedy stosowanie jest szczególnie uzasadnione – – – – • Jeśli w tabeli występują dwie takie same wartości klucza, dodawana do nich jest losowa liczba i taki klucz staje się wewnętrznie rozpoznawany jako unikalny Operowanie na zakresach danych i danych grupowanych Pobieranie danych w określonym porządku Zapytania korzystające z wielu kolumn tabeli Lepsza wydajność przy dodawaniu nowych wierszy Na jakich kolumnach tworzyć indeks zgrupowany? – – – – Mała długość Wysoka selektywność (mało powtarzających się wartości klucza indeksu) Rzadko bądź wcale nie zmieniane wartości Wartości klucza dla kolejno dodawanych wierszy są rosnące informatyka + 60 Indeks niezgrupowany (budowany na stercie) sys.partitions id Index_id=2 Root page korzeń gałęzie liście sterta informatyka + 61 Indeks niezgrupowany (budowany na stercie) • Struktura drzewiasta (B-tree) – drzewo zrównoważone • Na poziomie korzenia, gałęzi i liści – strony indeksu • Liście zawierają wskaźniki do właściwych stron na stercie • Można tworzyć do 248 indeksów niezgrupowanych na tabeli • Stosowane są gdy dane wyszukiwane są według wielu kryteriów (różne zapytania) • Maksymalna długość klucza – 900 bajtów • Maksymalnie 16 kolumn w kluczu informatyka + 62 Indeks niezgrupowany (budowany na zgrupowanym) sys.partitions id Index_id=2 Root page korzeń gałęzie liście Indeks zgrupowany informatyka + 63 Indeks niezgrupowany (budowany na zgrupowanym) • Praktycznie wszystko tak samo jak w budowanym na stercie. • Z wyjątkiem dwóch rzeczy: – Liście zawierają wartości klucza z indeksu zgrupowanego – Wskaźnik zawsze ustawiony jest na korzeń indeksu zgrupowanego • Jeśli indeks zgrupowany zostanie usunięty – niezgrupowany zostanie przebudowany (na wariant oparty o stertę) • Jeśli indeks zgrupowany zostanie utworzony – indeksy niezgrupowane zostaną także przebudowane (ze sterty na zgrupowany) informatyka + 64 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 65 Wykonywanie zapytań • Zapytanie zostało przekazane do wykonania …co dzieje się dalej? • Całość procesu można opisać kilkoma etapami: – Parsowanie zapytania (błędy składniowe). Efektem jest drzewo zapytania. – Standaryzacja zapytania (drzewa). Usuwanie nadmiarowości, standaryzowanie podzapytań itp.. – Optymalizacja zapytania .Wieloetapowy proces prowadzący do wyboru sposobu realizacji zapytania – Kompilacja wygenerowanego planu (zapisanie w cache) – Określenie metod fizycznego dostępu do danych – Wykonanie zapytania zgodnie ze stworzonym planem informatyka + 66 Wykonywanie zapytań – optymalizacja zapytania • Optymalizacja zapytania polega na: – Dokonaniu analizy zapytania (pod kątem kryteriów wyszukiwania oraz złączeń) – Dobraniu indeksów, które mogą okazać się pomocne przy realizacji zapytania (kryteria wyszukiwania, kolumny wyjściowe) – Określeniu strategii realizacji złączeń (selektywność, potrzebna pamięć) • Generowanych jest kilka wariantów, dla każdego szacowany jest koszt wyrażony w operacjach wejścia/wyjścia (I/O) i czasie rocesora (CPU). • Wybierany jest najtańszy wariant i przekazywany do kompilacji • Plan wykonania można podejrzeć za pomocą włączenia jednej z opcji: – SET SHOWPLAN_TEXT ON, SET SHOWPLAN_XML ON , SET SHOWPLAN_ALL ON informatyka + 67 Optymalizacja zapytań - wykorzystanie indeksów • Zakładamy, że zapytania będą tworzone w oparciu o tabelę: W celu zwiększenia rozmiaru wiersza i liczby stron:) • Nie ma żadnych indeksów na tabeli Klienci • Zapytanie, którym się zajmiemy jest proste: informatyka + 68 Wykorzystanie indeksów • Pierwsze wykonanie zapytania – plan wykonania Brak indeksów – skanowanie sterty Pierwsze wykonanie: strony pobierane z dysku Kolejne wykonania: strony znajdują się w cache • Koszt zapytania (estimated subtree cost) : 2,1385 informatyka + 69 Wykorzystanie indeksów • Stwórzmy najpierw indeks zgrupowany na kolumnie ID. • Zrealizujemy to przez utworzenie klucza podstawowego na tej kolumnie (prowadzi to do utworzenia indeksu) • Wykonanie naszego zapytania po utworzeniu indeksu przebiega według planu: Stworzyliśmy indeks zgrupowany, więc nie ma już sterty. • Koszt zapytania pozostał bez zmian : 2,1385 informatyka + 70 Wykorzystanie indeksów • Spróbujmy teraz popracować nad wydajnością • Stwórzmy indeks niezgrupowany na kolumnie, której używamy jako kryterium wyszukiwania • Skoro istnieje indeks na kolumnie Nazwisko, to powinien zostać użyty do wyszukiwania? Sprawdźmy… Nic z tego! Nasz indeks nie został wykorzystany • Dlaczego? • Bo na wyjściu zapytania mamy jeszcze kolumnę Imie! • Optymalizator stwierdził, iż nie warto korzystać z indeksu niezgrupowanego, skoro i tak trzeba pobrać strony danych, żeby uzyskać wartości z tej kolumny • Koszt zapytania ciągle bez zmian : 2,1385 informatyka + 71 Wykorzystanie indeksów • Zróbmy w końcu coś co przyniesie efekt! • Wiemy dlaczego nasz indeks był nieprzydatny • Uczyńmy go przydatnym! Dodajmy kolumnę Imie do indeksu • Wykonajmy kolejny raz nasze zapytanie Sukces :-) • Koszt wykonania: 0,0453 • Wcześniej było: 2,1385 informatyka + Wcześniej było 2862 ! 72 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 73 Kopie bezpieczeństwa i odtwarzanie danych • Baza danych może być skonfigurowana do pracy w trzech trybach: – Simple Recovery – Bulk logged Recovery (nieomawiany w ramach wykładu) – Full Recovery • Zależnie od wybranego trybu mamy różne możliwości wykonywania kopii zapasowych. • W trybie Simple Recovery można wykonywać jedynie pełne i różnicowe kopie zapasowe. Pozwala to w razie awarii odtworzyć stan bazy do stanu na chwilę wykonania ostatniej kopii zapasowej. • W trybie Full Recovery można dodatkowo wykonywać kopie logu transakcji. Pozwala to praktycznie na odtworzenie stanu bazy bezpośrednio sprzed awarii informatyka + 74 Kopie bezpieczeństwa i odtwarzanie danych • Wykonanie kopii zapasowej odbywa się za pomocą polecenia BACKUP: – BACKUP DATABASE – kopia zapasowa całej bazy danych – BACKUP LOG – kopia zapasowa logu transakcji – BACKUP FILE – kopia zapasowa pliku wchodzącego w skład bazy danych • Polecenie BACKUP DATABASE wykonuje domyślnie pełną kopię bazy danych • Wywołane z opcją WITH DIFFERENTIAL – wykonuje kopię różnicową (zmiany danych od ostatniej kopii pełnej) • Opcja ta zadziała pod warunkiem, że wcześniej wykonaliśmy kopię pełną! informatyka + 75 Kopie bezpieczeństwa i odtwarzanie danych • Odtwarzanie bazy z kopii zapasowej realizowane jest za pomocą polecenia RESTORE • Posiada ono takie same warianty jak polecenie BACKUP (DATABASE, LOG, FILE) • W przypadku konieczności odtworzenia stanu z kilku kolejnych kopii (kopia pełna, kopia różnicowa oraz log transakcji) można wykorzystać opcję NORECOVERY, która powoduje , że baza utrzymywana jest w stanie niespójności i pozwala na odtwarzanie kolejnych kopii. • Ostatnie polecenie odtworzenia bazy powinno być wywołane z opcją RECOVERY (domyślna), żeby baza wróciła do stanu stabilnego (wycofanie niezatwierdzonych transakcji sprzed awarii) informatyka + 76 Kopie bezpieczeństwa i odtwarzanie danych • Wykonywanie kopii zapasowych i ich odtwarzanie można wykonywać także z poziomu narzędzia SQL Server Management Studio. • Dodatkowo tworząc tzw. Maintenance Plan można stworzyć harmonogram wykonywania kopii zapasowych, który będzie realizowany automatycznie. • Istnieją także narzędzia produkowane przez inne firmy, które pozwalają planować i realizować strategie wykonywania kopii zapasowych baz danych. • Najważniejsze jednak jest sensowne zaplanowanie strategii wykonywania kopii zapasowych. – Powinna zapewnić możliwość odtworzenia danych z założoną dokładnością – Powinna zapewnić akceptowalny czas odtworzenia bazy i przywrócenia gotowości do pracy – Powinna być skrupulatnie realizowana – Powinna zawierać dokładnie opisane procedury odtwarzania danych po awarii! informatyka + 77 Agenda • Więzy integralności referencyjnej • Transakcje – Poziomy izolacji transakcji • Wyzwalacze – Rodzaje wyzwalaczy • Procedury składowane i funkcje użytkownika • Indeksy – Fizyczna organizacja danych w SQL Server 2008 – Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania • Kopie bezpieczeństwa i odtwarzanie danych • Podsumowanie informatyka + 78 Podsumowanie • Baza danych to nie tylko zbiór tabel • Istnieje wiele mechanizmów wewnątrz bazy danych, które służą zapewnieniu spójności danych, definiowaniu różnego rodzaju ograniczeń, implementowaniu złożonej logiki aplikacji itp. • Warto te mechanizmy stosować, gdyż takie podejście skutkuje zwykle wyższą wydajnością aplikacji oraz wyższym poziomem bezpieczeństwa danych. • Możliwości drzemiące w mechanizmach bazy danych są wystarczające, żeby projektować bazy „hermetyczne” i „idiotoodporne” w postaci czarnej skrzynki, która udostępnia na zewnątrz tylko listę operacji (procedur składowanych) • Warto zapoznać się z tymi mechanizmami praktycznie! informatyka + 79 KONIEC … czy są jakieś pytania? informatyka + 80
