Mechanizmy wewn trzne baz danych czyli co w bazach piszczy
This presentation is the property of its rightful owner.
Sponsored Links
1 / 81

Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy” PowerPoint PPT Presentation


  • 67 Views
  • Uploaded on
  • Presentation posted in: General

Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy”. Na przykładzie SQL Server 2008. Agenda. Więzy integralności referencyjnej Transakcje Poziomy izolacji transakcji Wyzwalacze Rodzaje wyzwalaczy Procedury składowane i funkcje użytkownika Indeksy

Download Presentation

Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy”

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Mechanizmy wewn trzne baz danych czyli co w bazach piszczy

Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy”

Na przykładzie SQL Server 2008

informatyka +


Agenda

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


Agenda1

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

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:

  • Wymaganie: Rachunek ma dokładnie jednego właściciela.

JAK?

informatyka +

5


Wi zy integralno ci referencyjnej1

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 referencyjnej2

Więzy integralności referencyjnej

  • Dodajmy teraz kilka rachunków i zobaczmy ich dane:

  • Rezultat:

  • 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 :-(

Nie ma takiego klienta!

informatyka +

7


Wi zy integralno ci referencyjnej3

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

Stworzone ograniczenie – klucz obcy

Kolumna tego samego typu!

Kolumna z kluczem podstawowym lub unikalnym

informatyka +

8


Wi zy integralno ci referencyjnej4

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 referencyjnej5

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 referencyjnej6

Więzy integralności referencyjnej

Luka w numeracji – ślad po nieudanej próbie dodania rachunku

  • Nie ma natomiast problemu z dodaniem rachunku dla istniejącego klienta:

  • Rezultat:

informatyka +

11


Wi zy integralno ci referencyjnej7

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 :

    • No action (domyślna)

      • Nie podejmuje żadnej akcji.

    • Cascade

      • Usuwa/modyfikuje wiersz z kluczem obcym

    • Setnull

      • Ustawia wartość null jako wartość kolumn klucza obcego (działa jeżeli te kolumny dopuszczają wartość null!)

    • Setdefault

      • 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.

UWAGA! Bardzo wygodne i bardzo niebezpiecznie!!!

informatyka +

12


Wi zy integralno ci referencyjnej8

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 referencyjnej9

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


Agenda2

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

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


Transakcje1

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


Transakcje2

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


Transakcje3

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


Transakcje4

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


Transakcje5

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


Agenda3

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


Transakcje6

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


Transakcje7

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


Transakcje8

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]

  • 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


Transakcje9

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


Transakcje10

Transakcje

Chwyciłem linijkę 

Teraz tylko kreda…

  • Minimalizowanie szansy na wystąpienie zakleszczenia

  • Sięganie do zasobów wg tej samej kolejności!

Czekam na linijkę

Rysowanie wykresu:

- linijka

- kreda

informatyka +

27


Transakcje11

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


Agenda4

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

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


Agenda5

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 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 dml1

Wyzwalacze DML

  • Dodajmy do naszej bazy jeszcze jedną tabelę

  • Będzie ona przechowywać informacjeo operacjach wykonywanych na rachunku

  • Wykorzystamy wyzwalacze dozaimplementowania reguł biznesowych:

    • Nie można usunąć ani zmodyfikowaćraz wykonanej operacji

    • Minimalna kwota wypłaty z rachunku musibyć większa lub równa 10 zł

informatyka +

33


Wyzwalacze dml2

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 dml3

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:

  • Rezultat:

Transakcja została wycofana

informatyka +

35


Wyzwalacze dml4

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 dml5

Wyzwalacze DML

TAK

  • Ważne! Nie należy zakładać, że wyzwalacz będzie wywoływany zawsze dla modyfikacji pojedynczego wiersza!

NIE

informatyka +

37


Wyzwalacze ddl

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 ddl1

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


Agenda6

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

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 ytkownika1

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 ytkownika2

Procedury składowane i funkcje użytkownika

  • Postać procedury zakładania promocyjnego konta:

informatyka +

43


Procedury sk adowane i funkcje u ytkownika3

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 ytkownika4

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 ytkownika5

Procedury składowane i funkcje użytkownika

  • Funkcja obliczająca saldo wskazanego rachunku:

  • Wywołanie:

  • Rezultat:

informatyka +

46


Procedury sk adowane i funkcje u ytkownika6

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 ytkownika7

Procedury składowane i funkcje użytkownika

  • Ta sama funkcja zrealizowana jako „multistatement”

  • Wywołanie:

  • Rezultat:

informatyka +

48


Agenda7

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


Agenda8

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

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 20081

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.

  • 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 20082

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 )

    • Mieszane (mixed extent)

      • Zawierają strony należące do więcej niż jednego obiektu

  • Alokowane i odczytywane są zawsze całe obszary a nie pojedyncze strony

informatyka +

53


Fizyczna organizacja danych w sql server 20083

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

informatyka +

54


Fizyczna organizacja danych w sql server 20084

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 20085

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 20086

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

informatyka +

57


Agenda9

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

Indeks zgrupowany

korzeń

gałęzie

liście

informatyka +

59


Indeks zgrupowany1

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

    • 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

  • Kiedy stosowanie jest szczególnie uzasadnione

    • 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

Indeks niezgrupowany (budowany na stercie)

korzeń

gałęzie

liście

sterta

informatyka +

61


Indeks niezgrupowany budowany na stercie1

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

Indeks niezgrupowany (budowany na zgrupowanym)

korzeń

gałęzie

liście

Indeks zgrupowany

informatyka +

63


Indeks niezgrupowany budowany na zgrupowanym1

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


Agenda10

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

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

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

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

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 (estimatedsubtreecost) : 2,1385

informatyka +

69


Wykorzystanie indeks w1

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 w2

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 w3

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 :-)

Wcześniej było 2862 !

  • Koszt wykonania: 0,0453

  • Wcześniej było: 2,1385

informatyka +

72


Agenda11

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

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 danych1

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 danych2

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 danych3

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


Agenda12

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

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


Mechanizmy wewn trzne baz danych czyli co w bazach piszczy

KONIEC

… czy są jakieś pytania?

informatyka +

80


  • Login