Wyjaśnienie architektury SQL Server: potoki nazwane, optymalizator, menedżer buforów

Spisie treści:

Anonim

MS SQL Server jest architekturą typu klient-serwer. Proces MS SQL Server rozpoczyna się wysłaniem przez aplikację kliencką żądania. SQL Server przyjmuje, przetwarza i odpowiada na żądanie z przetworzonymi danymi. Omówmy szczegółowo całą architekturę pokazaną poniżej:

Jak pokazano na poniższym diagramie, w architekturze SQL Server występują trzy główne składniki:

  1. Warstwa protokołu
  2. Silnik relacyjny
  3. Silnik pamięci masowej
Diagram architektury programu SQL Server

Omówmy szczegółowo wszystkie trzy powyższe główne moduły. W tym samouczku dowiesz się.

  • Warstwa protokołu - SNI
    • Pamięć współdzielona
    • TCP / IP
    • Nazwane rury
    • Co to jest TDS?
  • Silnik relacyjny
    • Parser CMD
    • Optimizer
    • Query Executor
  • Silnik pamięci masowej
    • Typy plików
    • Metoda dostępu
    • Menedżer buforów
    • Planowanie pamięci podręcznej
    • Analiza danych: pamięć podręczna bufora i przechowywanie danych
    • Menedżer transakcji

Warstwa protokołu - SNI

WARSTWA PROTOKOŁU MS SQL SERWERA obsługuje 3 typy architektury serwera klienta. Zaczniemy od „ Trzech typów architektury serwera klienckiego”, który obsługuje MS SQL Server.

Pamięć współdzielona

Rozważmy ponownie scenariusz porannej rozmowy.

MAMA i TOM - Tutaj Tomek i jego Mama byli w tym samym logicznym miejscu, czyli w swoim domu. Tom mógł poprosić o kawę, a mama była w stanie podać ją na gorąco.

SERWER MS SQL - tutaj serwer MS SQL udostępnia PROTOKÓŁ PAMIĘCI WSPÓLNEJ . Tutaj KLIENT i serwer MS SQL działają na tej samej maszynie. Oba mogą komunikować się za pośrednictwem protokołu Shared Memory.

Analogia: umożliwia mapowanie obiektów w dwóch powyższych scenariuszach. Możemy łatwo mapować Tom na klienta, mamę na serwer SQL, z domu na maszynę i komunikację werbalną na protokół pamięci współdzielonej.

Z biurka konfiguracji i instalacji:

W przypadku połączenia z lokalną bazą danych - w programie SQL Management Studio może być opcja „Nazwa serwera”

„.”

"Lokalny Gospodarz"

„127.0.0.1”

„Maszyna \ Instancja”

TCP / IP

A teraz zastanów się, czy Tom jest w nastroju na imprezę. Chce kawę zamówioną w znanej kawiarni. Kawiarnia znajduje się 10 km od jego domu.

Tutaj Tom i Starbuck znajdują się w innej fizycznej lokalizacji. Tom w domu i Starbucks na ruchliwym rynku. Komunikują się przez sieć komórkową. Podobnie MS SQL SERVER zapewnia możliwość interakcji poprzez protokół TCP / IP, w którym KLIENT i MS SQL Server są od siebie zdalne i zainstalowane na oddzielnej maszynie.

Analogia: umożliwia mapowanie obiektów w dwóch powyższych scenariuszach. Możemy łatwo zmapować Tom do klienta, Starbuck do serwera SQL, miejsce Home / Market do lokalizacji zdalnej i wreszcie sieć komórkową do protokołu TCP / IP.

Uwagi z działu konfiguracji / instalacji:

  • W programie SQL Management Studio - w przypadku połączenia za pośrednictwem protokołu TCP \ IP opcją „Nazwa serwera” musi być „Komputer \ Wystąpienie serwera”.
  • Serwer SQL używa portu 1433 w protokole TCP / IP.

Nazwane rury

Teraz wreszcie w nocy Tom zapragnął napić się jasnozielonej herbaty, którą jej sąsiadka, Sierra, bardzo dobrze przygotowała.

Tutaj Tom i jego sąsiadka Sierra znajdują się w tej samej fizycznej lokalizacji, będąc sąsiadami siebie nawzajem. Komunikują się przez sieć Intra. Podobnie MS SQL SERVER zapewnia możliwość interakcji za pośrednictwem protokołu nazwanego potoku . Tutaj KLIENT i SERWER MS SQL są połączone za pośrednictwem sieci LAN .

Analogia: umożliwia mapowanie obiektów w dwóch powyższych scenariuszach. Możemy łatwo zmapować Tom na klienta, Sierra na serwer SQL, sąsiada na LAN i wreszcie sieć wewnętrzną na protokół Named Pipe.

Uwagi z działu konfiguracji / instalacji:

  • Do połączenia przez nazwaną rurę. Ta opcja jest domyślnie wyłączona i musi zostać włączona przez Menedżera konfiguracji SQL.

Co to jest TDS?

Teraz, gdy wiemy, że istnieją trzy typy architektury klient-serwer, rzućmy okiem na TDS:

  • TDS to skrót od Tabular Data Stream.
  • Wszystkie 3 protokoły używają pakietów TDS. TDS jest hermetyzowany w pakietach sieciowych. Umożliwia to przesyłanie danych z komputera klienta do serwera.
  • TDS został po raz pierwszy opracowany przez Sybase, a obecnie jest własnością firmy Microsoft

Silnik relacyjny

Silnik relacyjny jest również znany jako procesor zapytań. Zawiera komponenty SQL Server, które określają, co dokładnie ma zrobić zapytanie i jak można to zrobić najlepiej. Odpowiada za wykonywanie zapytań użytkowników poprzez pobieranie danych z silnika pamięci masowej i przetwarzanie zwracanych wyników.

Jak pokazano na diagramie architektonicznym, istnieją 3 główne komponenty silnika relacyjnego. Przeanalizujmy szczegółowo komponenty:

Parser CMD

Dane otrzymane z warstwy protokołu są następnie przekazywane do silnika relacyjnego. „Parser CMD” jest pierwszym komponentem silnika relacyjnego, który odbiera dane zapytania. Głównym zadaniem CMD Parser jest sprawdzenie zapytania pod kątem błędów syntaktycznych i semantycznych. Na koniec generuje drzewo zapytań . Omówmy szczegółowo.

Sprawdzenie składniowe:

  • Jak każdy inny język programowania, MS SQL ma również predefiniowany zestaw słów kluczowych. Ponadto SQL Server ma własną gramatykę, którą SQL Server rozumie.
  • SELECT, INSERT, UPDATE i wiele innych należą do predefiniowanych list słów kluczowych MS SQL.
  • Parser CMD sprawdza składnię. Jeśli dane wejściowe użytkownika nie są zgodne ze składnią lub regułami gramatycznymi języka, zwraca błąd.

Przykład: Powiedzmy, że Rosjanin poszedł do japońskiej restauracji. Zamawia fast foody w języku rosyjskim. Niestety kelner rozumie tylko język japoński. Jaki byłby najbardziej oczywisty wynik?

Odpowiedź brzmi - kelner nie może dalej realizować zamówienia.

Nie powinno być żadnych odchyleń w gramatyce lub języku akceptowanym przez serwer SQL. Jeśli tak, serwer SQL nie może go przetworzyć i dlatego zwróci komunikat o błędzie.

Więcej o zapytaniach MS SQL dowiemy się w nadchodzących tutorialach. Jednak rozważ poniżej najbardziej podstawową składnię zapytania jako 

SELECT * from ;

Teraz, aby uzyskać wyobrażenie o tym, co robi składnia, powiedzmy, czy użytkownik uruchomi podstawowe zapytanie, jak poniżej:

SELECR * from

Zauważ, że zamiast „SELECT” użytkownik wpisał „SELECR”.

Wynik: Parser CMD przeanalizuje tę instrukcję i wyśle ​​komunikat o błędzie. Ponieważ „SELECR” nie następuje po wstępnie zdefiniowanej nazwie słowa kluczowego i gramatyce. Tutaj CMD Parser oczekiwał „SELECT”.

Sprawdzenie semantyczne:

  • Jest to wykonywane przez Normalizer .
  • W najprostszej formie sprawdza, czy nazwa kolumny, nazwa tabeli, której dotyczy zapytanie, istnieją w schemacie. A jeśli istnieje, powiąż go z Query. Jest to również znane jako wiązanie .
  • Złożoność wzrasta, gdy zapytania użytkowników zawierają VIEW. Normalizer wykonuje zamianę z wewnętrznie przechowywaną definicją widoku i nie tylko.

Zrozummy to za pomocą poniższego przykładu -

SELECT * from USER_ID

Wynik: Parser CMD przeanalizuje tę instrukcję w celu sprawdzenia semantycznego. Parser wyśle ​​komunikat o błędzie, ponieważ Normalizer nie znajdzie żądanej tabeli (USER_ID), ponieważ nie istnieje.

Utwórz drzewo zapytań:

  • Ten krok generuje różne drzewo wykonywania, w którym można uruchomić zapytanie.
  • Zauważ, że wszystkie różne drzewa mają ten sam pożądany wynik.

Optimizer

Praca optymalizatora polega na stworzeniu planu wykonania zapytania użytkownika. To jest plan, który określi sposób wykonania zapytania użytkownika.

Zwróć uwagę, że nie wszystkie zapytania są zoptymalizowane. Optymalizacja jest wykonywana dla poleceń DML (Data Modification Language), takich jak SELECT, INSERT, DELETE i UPDATE. Takie zapytania są najpierw zaznaczane, a następnie wysyłane do optymalizatora. Polecenia DDL, takie jak CREATE i ALTER, nie są zoptymalizowane, ale zamiast tego są kompilowane do postaci wewnętrznej. Koszt zapytania jest obliczany na podstawie takich czynników, jak użycie procesora, użycie pamięci i potrzeby wejścia / wyjścia.

Rolą Optymalizatora jest znalezienie najtańszego, a nie najlepszego, opłacalnego planu wykonania.

Zanim przejdziemy do bardziej technicznych szczegółów Optymalizatora, rozważmy poniższy przykład z życia wzięty:

Przykład:

Powiedzmy, że chcesz otworzyć konto bankowe online. Wiesz już o jednym banku, którego otwarcie konta zajmuje maksymalnie 2 dni. Ale masz również listę 20 innych banków, co może zająć mniej niż 2 dni. Możesz zacząć kontaktować się z tymi bankami, aby określić, którym bankom zajmuje mniej niż 2 dni. Teraz możesz nie znaleźć banku, który zajmuje mniej niż 2 dni, a dodatkowo tracisz czas z powodu samej działalności wyszukiwania. Lepiej byłoby otworzyć konto w pierwszym banku.

Wniosek: ważniejszy jest mądry wybór. Aby być precyzyjnym, wybierz, która opcja jest najlepsza, a nie najtańsza.

Podobnie MS SQL Optimizer działa na wbudowanych wyczerpujących / heurystycznych algorytmach. Celem jest zminimalizowanie czasu wykonywania zapytań. Wszystkie algorytmy Optymalizatora są własnością firmy Microsoft i są tajemnicą. Chociaż , poniżej etapy wysokiego poziomu wykonany przez MS SQL Optimizer. Wyszukiwanie optymalizacji przebiega w trzech fazach, jak pokazano na poniższym diagramie:

Faza 0: Poszukiwanie planu trywialnego:

  • Jest to również znane jako etap wstępnej optymalizacji .
  • W niektórych przypadkach może istnieć tylko jeden praktyczny, wykonalny plan, zwany planem trywialnym. Nie ma potrzeby tworzenia zoptymalizowanego planu. Przyczyną jest to, że dalsze wyszukiwanie skutkowałoby znalezieniem tego samego planu wykonania w czasie wykonywania. To także z dodatkowym kosztem wyszukiwania zoptymalizowanego planu, który wcale nie był wymagany.
  • Jeśli nie znaleziono planu Trivial, rozpoczyna się pierwsza faza.

Faza 1: Poszukiwanie planów przetwarzania transakcji

  • Obejmuje to wyszukiwanie prostego i złożonego planu .
  • Proste wyszukiwanie planu: W analizie statystycznej zostaną użyte poprzednie dane kolumny i indeksu zaangażowanych w zapytanie. Zwykle obejmuje to, ale nie ogranicza się do jednego indeksu na tabelę.
  • Jeśli jednak prosty plan nie zostanie znaleziony, przeszukiwany jest plan bardziej złożony. Obejmuje wiele indeksów na tabelę.

Faza 2: przetwarzanie równoległe i optymalizacja.

  • Jeśli żadna z powyższych strategii nie działa, Optimizer szuka możliwości przetwarzania równoległego. Zależy to od możliwości przetwarzania i konfiguracji Maszyny.
  • Jeśli nadal nie jest to możliwe, rozpoczyna się końcowa faza optymalizacji. Teraz ostatecznym celem optymalizacji jest znalezienie wszystkich innych możliwych opcji wykonania zapytania w najlepszy sposób. Ostateczna faza optymalizacji Algorytmy są własnością firmy Microsoft.

Query Executor

Moduł wykonawczy kwerendy wywołuje metodę dostępu. Zapewnia plan wykonania logiki pobierania danych wymaganej do wykonania. Po odebraniu danych z Storage Engine wynik jest publikowany w warstwie protokołu. Wreszcie dane są wysyłane do użytkownika końcowego.

Silnik pamięci masowej

Zadaniem silnika pamięci masowej jest przechowywanie danych w systemie pamięci masowej, takim jak dysk lub sieć SAN, i pobieranie danych w razie potrzeby. Zanim zagłębimy się w silnik pamięci masowej, przyjrzyjmy się, jak dane są przechowywane w bazie danych i jakie są dostępne pliki.

Plik danych i zakres:

Plik danych fizycznie przechowuje dane w postaci stron danych, przy czym każda strona danych ma rozmiar 8 KB, tworząc najmniejszą jednostkę pamięci w programie SQL Server. Te strony danych są logicznie pogrupowane w celu utworzenia zakresów. Żaden obiekt nie jest przypisany do strony w SQL Server.

Utrzymanie obiektu odbywa się za pośrednictwem ekstensji. Strona zawiera sekcję o nazwie Nagłówek strony o rozmiarze 96 bajtów, przenoszącą metadane dotyczące strony, takie jak typ strony, numer strony, rozmiar używanego miejsca, rozmiar wolnego miejsca i wskaźnik na następną i poprzednią stronę. itp.

Typy plików

  1. Plik podstawowy
  • Każda baza danych zawiera jeden plik podstawowy.
  • To przechowuje wszystkie ważne dane związane z tabelami, widokami, wyzwalaczami itp.
  • Rozszerzenie jest. mdf zwykle, ale może mieć dowolne rozszerzenie.
  1. Plik dodatkowy
  • Baza danych może zawierać wiele plików pomocniczych lub nie.
  • Jest to opcjonalne i zawiera dane specyficzne dla użytkownika.
  • Rozszerzenie jest. ndf zwykle, ale może mieć dowolne rozszerzenie.
  1. Plik dziennika
  • Znany również jako dzienniki zapisu z wyprzedzeniem.
  • Rozszerzenie jest. ldf
  • Używany do zarządzania transakcjami.
  • Służy do odzyskiwania po niechcianych wystąpieniach. Wykonaj ważne zadanie wycofania zmian do niezatwierdzonych transakcji.

Storage Engine składa się z 3 elementów; przyjrzyjmy się im szczegółowo.

Metoda dostępu

Działa jako interfejs między wykonawcą zapytań a menedżerem buforów / dziennikami transakcji.

Sama metoda dostępu nie wykonuje żadnego wykonania.

Pierwszą czynnością jest ustalenie, czy zapytanie to:

  1. Instrukcja Select (DDL)
  2. Instrukcja Non-Select (DDL i DML)

W zależności od wyniku Metoda dostępu wykonuje następujące kroki:

  1. Jeśli zapytanie jest instrukcją DDL , SELECT, zapytanie jest przekazywane do menedżera buforów w celu dalszego przetwarzania.
  2. A jeśli zapytanie dotyczy instrukcji DDL, NON-SELECT , zapytanie jest przekazywane do Menedżera transakcji. Dotyczy to głównie instrukcji UPDATE.

Menedżer buforów

Menedżer buforów zarządza podstawowymi funkcjami poniższych modułów:

  • Planowanie pamięci podręcznej
  • Analiza danych: pamięć podręczna bufora i przechowywanie danych
  • Brudna strona

W tej sekcji nauczymy się planu, bufora i pamięci podręcznej danych. Brudne strony omówimy w sekcji Transakcja.

Planowanie pamięci podręcznej

  • Istniejący plan zapytania: Menedżer buforów sprawdza, czy plan wykonania znajduje się w przechowywanej pamięci podręcznej planu. Jeśli tak, używana jest pamięć podręczna planu zapytania i skojarzona z nią pamięć podręczna danych.
  • Plan pierwszej pamięci podręcznej: skąd pochodzi istniejąca pamięć podręczna planu?

    Jeśli plan wykonania zapytania po raz pierwszy jest uruchamiany i jest złożony, warto przechowywać go w pamięci podręcznej Plane. Zapewni to szybszą dostępność, gdy następnym razem serwer SQL otrzyma to samo zapytanie. Tak więc to nic innego, jak samo zapytanie, które wykonanie planu jest przechowywane, jeśli jest uruchamiane po raz pierwszy.

Analiza danych: pamięć podręczna bufora i przechowywanie danych

Menedżer buforów zapewnia dostęp do wymaganych danych. Poniższe dwa podejścia są możliwe w zależności od tego, czy dane istnieją w pamięci podręcznej danych, czy nie:

Buffer Cache - Soft Parsing:

Menedżer buforów szuka danych w buforze w pamięci podręcznej danych. Jeśli są obecne, wówczas te dane są używane przez moduł wykonawczy Query. Zwiększa to wydajność, ponieważ liczba operacji we / wy jest zmniejszona podczas pobierania danych z pamięci podręcznej w porównaniu z pobieraniem danych z magazynu danych.

Przechowywanie danych - twarde analizowanie:

Jeśli danych nie ma w Menedżerze buforów, to jest wymagane. Dane są wyszukiwane w Magazyn danych. Jeśli również przechowuje dane w pamięci podręcznej danych do wykorzystania w przyszłości.

Brudna strona

Jest przechowywany jako logika przetwarzania Menedżera transakcji. O szczegółach dowiemy się w dziale Menedżer transakcji.

Menedżer transakcji

Menedżer transakcji jest wywoływany, gdy metoda dostępu ustali, że zapytanie jest instrukcją Non-Select.

Menedżer dziennika

  • Log Manager śledzi wszystkie aktualizacje wykonane w systemie za pośrednictwem dzienników w dziennikach transakcji.
  • Dzienniki mają numer sekwencji dzienników z identyfikatorem transakcji i rekordem modyfikacji danych .
  • Służy do śledzenia transakcji zatwierdzonych i wycofanych z transakcji .

Menedżer zamków

  • Podczas transakcji skojarzone dane w magazynie danych są w stanie blokady. Ten proces jest obsługiwany przez Lock Manager.
  • Ten proces zapewnia spójność i izolację danych . Znany również jako właściwości ACID.

Proces realizacji

  • Log Manager rozpoczyna rejestrowanie, a Lock Manager blokuje powiązane dane.
  • Kopia danych jest przechowywana w buforze bufora.
  • Kopia danych, które mają być aktualizowane, jest przechowywana w buforze dziennika, a wszystkie zdarzenia aktualizują dane w buforze danych.
  • Strony, które przechowują dane, są również znane jako brudne strony .
  • Rejestrowanie punktów kontrolnych i zapisu z wyprzedzeniem: ten proces uruchamia się i zaznacza wszystkie strony od brudnych stron na dysk, ale strona pozostaje w pamięci podręcznej. Częstotliwość wynosi około 1 uruchomienia na minutę, ale strona jest najpierw wypychana na stronę danych pliku dziennika z dziennika bufora. Jest to znane jako rejestrowanie z wyprzedzeniem.
  • Leniwy pisarz: Brudna strona może pozostać w pamięci. Gdy serwer SQL obserwuje ogromne obciążenie i do nowej transakcji potrzebna jest pamięć buforowa, zwalnia on brudne strony z pamięci podręcznej. Działa na LRU - najmniej ostatnio używany algorytm czyszczenia strony z puli buforów na dysk.

Podsumowanie:

  • Istnieją trzy typy architektury serwera klienckiego: 1) Pamięć współdzielona 2) TCP / IP 3) Potoki nazwane
  • TDS, opracowany przez Sybase, a obecnie należący do Microsoft, to pakiet, który jest hermetyzowany w pakietach sieciowych w celu przesyłania danych z komputera klienckiego do serwera.
  • Silnik relacyjny zawiera trzy główne komponenty:

    Parser CMD: jest odpowiedzialny za błąd syntaktyczny i semantyczny i ostatecznie generuje drzewo zapytań.

    Optymalizator: rolą optymalizatora jest znalezienie najtańszego, a nie najlepszego, opłacalnego planu wykonania.

    Query Executor: moduł wykonujący zapytania wywołuje metodę dostępu i dostarcza plan wykonania dla logiki pobierania danych wymaganej do wykonania.

  • Istnieją trzy typy plików Plik podstawowy, plik pomocniczy i pliki dziennika.
  • Silnik pamięci masowej: Zawiera następujące ważne elementy

    Metoda dostępu: ten składnik określa, czy kwerenda jest instrukcją Select, czy Non-Select. Wywołuje odpowiednio Buffer i Transfer Manager.

    Menedżer buforów: Menedżer buforów zarządza podstawowymi funkcjami pamięci podręcznej planu, analizowania danych i brudnej strony.

    Menedżer transakcji: zarządza transakcją non-Select za pomocą menedżerów logów i blokad. Ułatwia również ważną implementację rejestrowania z wyprzedzeniem i leniwych pisarzy.