Optymalizacja zapytań sql



Pobieranie 2,87 Mb.
Strona1/19
Data05.03.2018
Rozmiar2,87 Mb.
  1   2   3   4   5   6   7   8   9   ...   19

Optymalizacja zapytań SQL

Andrzej Ptasznik

Warszawska Wyższa Szkoła Informatyki

aptaszni@wwsi.edu.pl

Kurs: Optymalizacja zapytań SQL

Streszczenie

Kurs zapoznaje słuchaczy z problematyką wydajności i optymalizacji zapytań SQL. Omówiona zostanie fizyczna organizacja przechowywania danych i wprowadzone zostaną pojęcia indeksów zgrupowanych i niezgrupowanych. Zaprezentowane zostaną przykłady planów wykonania zapytań generowane przez optymalizator SQL. Na bazie przykładu omówione zostaną problemy wyboru strategii wykonania zapytania w zależności od zawartości tabel i zdefiniowanych indeksów. Wprowadzone zostanie pojecie statystyk indeksów i omówione będzie ich znaczenie przy wyborze strategii realizacji zapytania.

.

Spis treści


  1. Wprowadzenie

    1. Wstęp

    2. Podstawowe pojęcia związane z bazami danych

  2. Projektowanie bazy danych

    1. Podstawy projektowania baz danych

    2. Definiowanie bazy danych i tabel w języku SQL

    3. Ćwiczenie 1 - Zdefiniowanie bazy danych i przykładowej tabeli

  3. Technologia MS SQL Server 2008

    1. Elementy technologii MS SQL Server 2008

    2. Ćwiczenie 2 - Instalacja instancji MS SQL Server 2008 Express

  4. Środowisko SQL Server Menagement Studio

    1. Ćwiczenie 3 – Zapoznanie ze środowiskiem SQL Menagement Studio

  5. Metody optymalizacji wydajności bazy danych

  6. Fizyczna organizacja danych w SQL Server 2008

    1. Organizacja przechowywania danych

    2. Indeksy zgrupowane

    3. Indeksy niezgrupowane




  1. Plany wykonania zapytania

  2. Statystyki

  3. Optymalizacja przykładowego zapytania

  4. Narzędzia wspomagające optymalizację




  1. Podsumowanie

  2. Literatura

  3. Adresy w Internecie



  1. Wprowadzenie

1.1.Wstęp

Historia relacyjnego modelu danych rozpoczęła się w roku 1970 wraz z publikacją E.F.Codda pt. A Relational Model of Data for Large Shared Data Banks. ( pol. Relacyjny model danych dla dużych współdzielonych banków danych). Ten artykuł wzbudził duże zainteresowanie, ponieważ przedstawiał możliwości realizacji i praktyczne zastosowania komercyjnego systemu baz danych Praca ta stworzyła teoretyczne podstawy budowania baz danych w oparciu o relacyjne podejście do jej modelowania. Opublikowana teoria bardzo szybko zainteresowała potencjalnych twórców i producentów systemów zarządzania bazami danych. Droga od teorii do praktyki bywa często długa i wyboista ale w tym przypadku, ze względu na pilne potrzeby rynku, przebiegała dość szybko i sprawnie. W ramach naszego kursu zapoznamy się z Systemem Zarządzania Relacyjnymi Bazami Danych MS SQL Server 2008.



1.2.Podstawowe definicje związane z bazami danych.
Wprowadzimy na początku kilka definicji wyjaśniających podstawowe pojęcia, którymi będziemy się posługiwali w dalszej części.

  • Dane to liczby, znaki, symbole (i cokolwiek innego) zapisane w celu ich przetwarzania.

  • Informacja to taki czynnik, któremu człowiek może przypisać określony sens (znaczenie), aby móc ją wykorzystywać do różnych celów.

  • Wiedza to, zgodnie z prastarą definicją Platona, ogół wiarygodnych informacji o rzeczywistości wraz z umiejętnością ich wykorzystywania.

Można teraz uporządkować te pojęcia w kontekście baz danych i sposobu i wykorzystania. Dane zbieramy i zapisujemy, by na ich podstawie uzyskiwać informacje, które będą stawały się wiedzą, gdy uzupełnimy je o sposoby i możliwości ich praktycznego wykorzystania. Nie trzeba chyba udowadniać tezy, że współczesne społeczeństwo, społeczeństwo informacyjne, opiera swoje działania i podstawy rozwoju na wiedzy, która jest między innymi pozyskiwana z baz danych.

Zgodnie z powyższymi definicjami dane to prawie wszystko co może być zapisane i dlatego jednym z podstawowych zadań, żeby zbiór danych mógł stać się bazą danych, jest zapewnienie odpowiedniego sposobu uporządkowania. Bazy danych mogą gromadzić gigantyczne ilości danych zapewniając właściwy sposób ich uporządkowania. Teraz możemy spróbować zdefiniować pojęcie bazy danych :



Baza danych to zbiór danych zapisanych w ściśle określony sposób w strukturach odpowiadających założonemu modelowi danych.



  1. Projektowanie bazy danych

2.1.Podstawy projektowania baz danych

Jedyną strukturą danych w modelu relacyjnym jest tabela ale jedna tabela może zawierać dane tylko na określony temat (dane o uczniu, dane o planowanych wizytach lekarskich itp.). Nie można utożsamiać tabeli z bazą danych ponieważ baza danych jest pojęciem szerszym a tabele są elementami składowymi bazy danych. To jakie tabele będzie zawierała baza danych określane jest na etapie jej projektowania. W trakcie projektowania baz danych za pomocą dwuwymiarowych tabel opisujemy wybrany fragment rzeczywistości (bank, szkoła, kolekcja płyt). Teraz spróbujemy omówić niektóre aspekty i zasady projektowania baz danych, zdając sobie sprawę, że proces projektowania baz jest daleko bardziej złożony.



Zanim przystąpimy do projektowania musi zostać określona dziedzina, dla której tworzymy bazę danych. Zakładamy, że chcemy zaprojektować bazę danych umożliwiającą rejestrowanie ocen wystawianych uczniom, czyli pewien fragment szkolnej rzeczywistości. Jednym z podstawowych zadań, na etapie projektowania bazy danych, jest określenie podstawowych obiektów występujących w dziedzinie, dla której te bazę projektujemy. W naszym przypadku dość szybko dojdziemy do wniosku, że nasza baza powinna opisywać uczniów oraz podstawową jednostkę organizacyjną szkoły czyli klasy. Poniżej przedstawiamy propozycję tabel opisujących te dwa podstawowe elementy.

Rysunek 1.Przykładowe tabele



Wyjaśnimy teraz niektóre elementy zaproponowanego rozwiązania.

  • Każda tabela musi mieć przypisaną nazwę i nazwa ta powinna określać rodzaj danych, jaki planujemy w tej tabeli przechowywać.

  • Zgodnie z cechami modelu relacyjnego, każda tabela musi zawierać klucz podstawowy. Dla zaproponowanych tabel kluczami podstawowymi są kolumny o nazwach iducznia i idklasy – na pierwszy rzut oka nazwy te wydaja się dziwne ale odpowiada to pewnej przyjętej praktyce polegającej na tym, że projektując tabele ustala się tzw. sztuczny klucz podstawowy. W naszym przypadku kolumna iducznia (identyfikator ucznia) będzie zawierała unikatowe numery przypisywane każdemu uczniowi, który zostanie w tabeli zapisany. W bazach danych istnieją mechanizmy, które automatycznie generują unikatowe numery dla tak zdefiniowanych kluczy.

  • W tabeli Uczniowie znajduje się kolumna o nazwie idklasy (na rysunku nazwana kluczem obcym). Wymaga to wyjaśnienia tym bardziej, że dotykamy istoty projektowania relacyjnych baz danych. Sprawą oczywistą jest to, że każdy uczeń jest przypisany do określonej klasy. Klasy, jako takie, są zapisywane w oddzielnej tabeli o nazwie Klasy w której kluczem podstawowym jest kolumna idklasy. Fakt umieszczenia w tabeli Uczniowie klucza obcego (czyli kolumny idklasy, która w innej tabeli pełni role klucza podstawowego) tworzy związek (powiązanie) pomiędzy tabelami Uczniowie i Klasy. Spróbujmy wyjaśnić dlaczego w ten sposób projektuje się elementy relacyjnych baz danych.

    • Faktem jest, że każdy uczeń jest przypisany do określonej klasy i teoretycznie moglibyśmy umieścić w tabeli Uczniowie dodatkowe kolumny opisujące tę klasę (nazwę klasy i rok szkolny), ale w tej sytuacji dla uczniów tej samej klasy dane takie musiałyby być powtórzone, czyli to samo byłoby zapisywane w tabeli wielokrotnie. Sytuacja taka sprzyja powstawaniu błędów i niejednoznaczności, których przyczyną mogą być zwykłe błędy (literówki) na etapie zapisywania danych do tabeli.

    • Zapisując dane o klasach w osobnej tabeli zapewniamy, że dana klasa opisana jest tylko jeden raz.

    • Umieszczenie w tabeli Uczniowie klucza obcego idklasy zapewnia powiązanie danych o uczniu z danymi o klasie do której został dowiązany.

    • Jeżeli w pewnym wierszu tabeli Uczniowie mamy zapisane dane ucznia i przykładowo w kolumnie idklasy zapisana jest liczba 5, to taki zapis interpretujemy w ten sposób: uczeń związany jest z klasą (zapisaną w tabeli Klasy) o wartości klucza 5. Ponieważ idklasy w tabeli Klasy jest kluczem podstawowym to mamy gwarancję, że nasza przykładowa wartość 5, odpowiada dokładnie jednemu wierszowi tabeli Klasy zawierającemu interesujący nas opis.

Kontynuujemy nasz projekt i kolejnym elementem może być tabela opisująca nauczycieli, ponieważ nie można wyobrazić sobie procesu wystawiania ocen bez wiedzy o nauczycielu, który taką ocenę wystawił. Proponowana tabela nauczyciele nie wprowadza żadnych nowych elementów do rozważań.



Rysunek 2.Schematy tabel

Kilka słów wyjaśnienia przy propozycji kolejnych tabel w naszym projekcie. Zaproponowane tabele o nazwach przedmioty i rodzaje_ocen są tak zwanymi tabelami słownikowymi, czyli takimi, które będą przechowywać zbiory pewnych pojęć. Cel dla którego projektujemy tego typu tabele wydaje się oczywisty – będziemy wykorzystywać klucze podstawowe z tych tabel jako klucze obce w innych tabelach zawierających informacje o przedmiocie lub rodzaju wystawionej oceny i. podobnie jak we wcześniej opisywanym przypadku (uczniowie i klasy), dzięki takiemu podejściu zapewnimy jednoznaczność zapisywanych danych.

Na koniec tabela, która jest najważniejsza z punktu widzenia zaplanowanej bazy danych, czyli tabela w której będziemy przechowywali dane opisujące wystawione oceny.



Rysunek 3.Tabela Oceny

W tabeli Oceny są przechowywane dane o pewnych zdarzeniach – wystawionych ocenach. Należy się spodziewać, że ta tabela będzie centralnym punktem naszej bazy danych. Zawartość tej tabeli można opisać następująco:

Pewien uczeń (iducznia) od jakiegoś nauczyciela (idnauczyciela) otrzymał pewien rodzaj oceny (idrodzaju_oceny) z pewnego przedmiotu (idprzedmiotu) o wartości oceny (ocena) wystawionej pewnego dnia (data). Dodatkowo, w tabeli Oceny jest kolumna idoceny, czyli sztuczny klucz podstawowy, który już poznaliśmy.



Na zakończenie tej części naszych rozważań przedstawmy w całości nasz projekt bazy danych.

Baza danych „Elektroniczny dziennik ocen”

Rysunek 4.Przykładowy schemat bazy danych.

Możemy uznać, że tak zaprojektowana baza danych będzie spełniać rolę miejsca, w którym gromadzone będą dane o wystawianych ocenach. Nic nie stoi na przeszkodzie, aby gromadzić w niej dane o wszystkich ocenach wystawianych w danej szkole. Rodzi się jednak pytanie – a jeśli chcielibyśmy zapisywać w takiej bazie danych oceny wystawiane w różnych szkołach. Czy taki projekt bazy byłby wystarczający? Jak należałoby zmodyfikować ten projekt, aby umożliwić zapisywanie ocen wystawianych w różnych szkołach?

Realizacja bazy ocen nie jest wystarczającym doświadczeniem, by móc uznać, że jesteśmy już projektantami baz danych. Problemy związane z projektowaniem baz są dalece bardziej złożone i wymagają oprócz wiedzy bardzo dużego doświadczenia. Zrobiliśmy tylko pierwszy krok.



2.2.Ćwiczenie 1 - Wykonanie własnego projektu bazy danych
W ramach ćwiczenia każdy uczestnik przygotuje projekt bazy danych składający się z około 5 tabel. Dziedzina problemu dowolna. Po wykonaniu projektu należy skonsultować go z prowadzącym ćwiczenia. Projekt powinien zawierać :

  • Nazwy tabel

  • Nazwy kolumn i określenie typu wartości

  • Zaznaczenie kluczy podstawowych o obcych

W dalszej części kursu każdy uczestnik będzie implementował bazę danych na podstawie swojego projektu.


  1. Technologia MS SQL Server 2008

3.1.Elementy technologii MS SQL Server 2008

Dotychczasowe rozważania prowadziliśmy w oderwaniu od technologii, czyli od sposób realizacji. Koncentrowaliśmy się na teorii – a teraz przyszła pora na praktykę. W tym celu zdefiniujemy nowe pojecie:



Systemem Zarządzania Bazami Danych(SZBD) nazywamy specjalistyczne oprogramowanie umożliwiające tworzenie baz danych oraz ich eksploatację.

Wydaje się oczywiste, że tworzenie i działanie baz danych musi być wspierane przez specjalistyczne oprogramowanie, które powinno umożliwiać realizacje pewnych zadań:



  • definiowanie obiektów bazy danych,

  • manipulowanie danymi,

  • generowanie zapytań,

  • zapewnienie spójności i integralności danych.

Zadania te brzmią bardzo ogólnie, obejmują jednak większość potrzeb w zakresie tworzenia i eksploatacji baz danych. Dla przybliżenia pojęcia SZBD można podać kilka nazw handlowych, pod jakimi te produkty można spotkać na rynku i w zastosowaniach: MS SQL Server 2008, Oracle, MySQL, Access, DB2 i wiele, wiele innych mniej lub bardziej popularnych.

Jednym z najważniejszych zadań stojących przed SZBD jest zapewnienie spójności i integralności danych, czyli dostarczenie mechanizmów zapewniających przestrzeganie określonych reguł przez dane. SZBD dostarczają mechanizmy służące do zapewnienia spójności i integralności danych, czyli mówiąc innymi słowami, zapewnienia logicznej poprawności danych zapisanych w bazie. Podstawowe mechanizmy, realizujące te zadania to :



  • deklaracja typu,

  • definicje kluczy,

  • reguły poprawności dla kolumny,

  • reguły poprawności dla wiersza,

  • reguły integralności referencyjnej

W ramach kursu będziemy wykorzystywać technologię MS SQL Server 2008 Express. Jest to jeden z najpopularniejszych serwerów baz danych. Edycja SQL Server Express, z której będziemy korzystać, jest wersją darmową z możliwością wykorzystania jej w celach komercyjnych. Technologia SQL Server 2008 zawiera następujące podsystemy:

  • Serwer bazy danych (Database Engine) – podsystem odpowiedzialny za zarządzanie bazami danych (definiowanie, eksploatacja i administracja baz danych)

  • Serwer raportowania (Reporting Services) – podsystem umożliwiający zarządzanie procesem tworzenia i dystrybucji raportów generowanych na podstawie danych z różnych źródeł (bazy danych, pliki Excel, pliki tekstowe, dokumenty XML)

  • Serwer usług analitycznych (Analysis Services) – podsystem wspomagający organizację hurtowni danych, wielowymiarowych kostek analitycznych, tworzenie pulpitów menadżerskich oraz realizację algorytmów wyszukiwania złożonych zależności (Data Mining)

  • Serwer usług integracyjnych (Integration Services) – podsystem realizujący zadania integracji danych, polegające, w dużym uproszczeniu, na pobieraniu danych z pewnych źródeł danych, poddanie ich procesowi przetwarzania( sprawdzanie poprawności, eliminowanie błędów itp.) a następnie zapisanie przetworzonych danych w docelowej lokalizacji. Zadania te są określane w teorii jako platforma ET&L (Extract, Transform and Load).

W ramach kursu będziemy wykorzystywać jedynie serwer baz danych (Database Engine), który również zawiera wiele różnych dodatkowych technologii :

  • Usługi asynchronicznego przetwarzania (Service Broker ) – umożliwiają realizację asynchronicznego przetwarzania z wykorzystanie kolejek

  • Usługi replikacji danych – umożliwiają konfigurowanie zadań związanych z odtwarzaniem części zasobów bazy danych w innych lokalizacjach

  • Usługi wyszukiwania pełno tekstowego - umożliwiają wyszukiwanie fragmentów tekstu niezależnie od ich lokalizacji w bazie danych (klasyczne zapytanie SQL wymagają określenie tabel z których dane są pobierane)

Wymienione zostały niektóre elementy technologii MS SQL Server 2008 co i tak pokazuje, że jest to bardzo rozległy i złożony system umożliwiający realizacje bardzo różnych zadań związanych z bazami danych. Nasz kurs należy traktować jako pierwszy krok w złożony i bardzo ciekawy świat technologii MS SQL Server 2008.

3.2.Ćwiczenie 2 - Instalacja instancji MS SQL Server 2008 Express

W ramach ćwiczenia zostanie omówiony proces instalacji SQL Server 2008 oraz każdy uczestnik kursu wykona instalacje serwera na swoim komputerze .

Obok komercyjnych wersji, SQL Server występuje także w darmowym wariancie zwanym SQL Server 2008 Express. Jego instalacja jest jednak, podobnie jak pozostałych wersji, dość złożona i można popełnić przy niej wiele błędów. Aby się tego ustrzec i móc jak najszybciej zacząć wykorzystywać możliwości oferowane przez SQL Server 2008, podamy w miarę prosty i łatwy sposób zainstalowania tego systemu.



  1   2   3   4   5   6   7   8   9   ...   19


©operacji.org 2017
wyślij wiadomość

    Strona główna