Arhitectura SQL Server explicată: țevi numite, Optimizator, Buffer Manager

Cuprins:

Anonim

MS SQL Server este o arhitectură client-server. Procesul MS SQL Server începe cu aplicația client care trimite o cerere. SQL Server acceptă, procesează și răspunde la cerere cu date procesate. Să discutăm în detaliu întreaga arhitectură prezentată mai jos:

Așa cum arată diagrama de mai jos, există trei componente majore în arhitectura SQL Server:

  1. Strat de protocol
  2. Motor relațional
  3. Motor de stocare
Diagrama arhitecturii SQL Server

Să discutăm în detaliu despre toate cele trei module majore de mai sus. În acest tutorial, veți învăța.

  • Protocol Layer - SNI
    • Memorie partajată
    • TCP / IP
    • Țevi numite
    • Ce este TDS?
  • Motor relațional
    • Analizator CMD
    • Optimizator
    • Executor interogare
  • Motor de stocare
    • Tipuri de fisiere
    • Metoda de acces
    • Manager tampon
    • Planificați memoria cache
    • Analiza datelor: cache tampon și stocare date
    • Manager tranzacții

Protocol Layer - SNI

MS SQL SERVER PROTOCOL LAYER acceptă 3 tipuri de arhitectură client server. Vom începe cu „ Three Type of Client Server Architecture” pe care MS SQL Server îl acceptă.

Memorie partajată

Să reconsiderăm un scenariu de conversație dimineața devreme.

MAMĂ și TOM - Aici Tom și mama lui, erau în același loc logic, adică acasă. Tom a putut să ceară cafea, iar mama a putut să o servească fierbinte.

MS SQL SERVER - Aici serverul MS SQL oferă PROTOCOL DE MEMORIE ÎMPĂRȚITĂ . Aici CLIENTUL și serverul MS SQL rulează pe aceeași mașină. Ambele pot comunica prin protocolul de memorie partajată.

Analogie: permite maparea entităților în cele două scenarii de mai sus. Putem asocia cu ușurință Tom la client, mama la serverul SQL, acasă la mașină și comunicare verbală la protocolul de memorie partajată.

De la biroul de configurare și instalare:

Pentru conexiunea la DB local - În SQL Management Studio, opțiunea „Nume server” ar putea fi

"."

"gazdă locală"

„127.0.0.1”

„Mașină \ Instanță”

TCP / IP

Acum ia în considerare seara, Tom are chef de petrecere. Vrea o cafea comandată de la o cunoscută cafenea. Cafeneaua se află la 10 km de casa sa.

Aici Tom și Starbuck sunt în locație fizică diferită. Tom acasă și Starbucks pe piața aglomerată. Comunică prin rețeaua celulară. În mod similar, MS SQL SERVER oferă capacitatea de a interacționa prin protocolul TCP / IP, unde CLIENTUL și MS SQL Server sunt la distanță între ele și sunt instalate pe o mașină separată.

Analogie: permite maparea entităților în cele două scenarii de mai sus. Putem mapa cu ușurință Tom la client, Starbuck la serverul SQL, locul de acasă / piață la locația la distanță și, în cele din urmă, rețeaua celulară la protocolul TCP / IP.

Note de la biroul de configurare / instalare:

  • În SQL Management Studio - Pentru conexiune prin TCP \ IP, opțiunea „Nume server” trebuie să fie „Mașină \ Instanță a serverului”.
  • Serverul SQL utilizează portul 1433 în TCP / IP.

Țevi numite

Acum, în sfârșit noaptea, Tom a vrut să bea un ceai verde deschis pe care vecinul ei, Sierra îl pregătește foarte bine.

Aici Tom și vecinul său , Sierra, se află în aceeași locație fizică, fiind vecinii unul altuia. Comunică prin rețeaua Intra. În mod similar, MS SQL SERVER oferă capacitatea de a interacționa prin protocolul Named Pipe . Aici CLIENTUL și MS SQL SERVER sunt conectate prin LAN .

Analogie: permite maparea entităților în cele două scenarii de mai sus. Putem mapa cu ușurință Tom la client, Sierra la serverul SQL, Neighbor la LAN și în cele din urmă rețea intra la protocolul cu țeavă numită.

Note de la biroul de configurare / instalare:

  • Pentru conexiune prin țeavă numită. Această opțiune este dezactivată în mod implicit și trebuie activată de SQL Configuration Manager.

Ce este TDS?

Acum, că știm că există trei tipuri de arhitectură client-server, ne permite să aruncăm o privire asupra TDS:

  • TDS înseamnă Tabular Data Stream.
  • Toate cele 3 protocoale folosesc pachete TDS. TDS este încapsulat în pachete de rețea. Aceasta permite transferul de date de la computerul client la computerul server.
  • TDS a fost dezvoltat pentru prima dată de Sybase și este acum deținut de Microsoft

Motor relațional

Motorul relațional este, de asemenea, cunoscut sub numele de procesor de interogare. Are componentele SQL Server care determină ce anume trebuie să facă o interogare și cum se poate face cel mai bine. Este responsabil pentru executarea interogărilor utilizatorilor, solicitând date de la motorul de stocare și prelucrând rezultatele returnate.

După cum este descris în diagrama arhitecturală, există 3 componente majore ale motorului relațional. Să studiem componentele în detaliu:

Analizator CMD

Datele primite odată de la Protocol Layer sunt apoi transmise la Relational Engine. „CMD Parser” este prima componentă a motorului relațional care primește datele interogării. Sarcina principală a CMD Parser este de a verifica interogarea pentru erori sintactice și semantice. În cele din urmă, generează un arbore de interogare . Să discutăm în detaliu.

Verificare sintactică:

  • Ca orice alt limbaj de programare, MS SQL are și setul de cuvinte cheie predefinite. De asemenea, SQL Server are propria gramatică pe care o înțelege serverul SQL.
  • SELECT, INSERT, UPDATE și multe altele aparțin listelor de cuvinte cheie predefinite de MS SQL.
  • CMD Parser efectuează verificarea sintactică. Dacă introducerea utilizatorilor nu respectă aceste sintaxi lingvistice sau reguli gramaticale, returnează o eroare.

Exemplu: Să presupunem că un rus a mers la un restaurant japonez. El comandă mâncare rapidă în limba rusă. Din păcate, chelnerul înțelege doar japoneza. Care ar fi rezultatul cel mai evident?

Răspunsul este - chelnerul nu poate procesa comanda în continuare.

Nu ar trebui să existe nicio abatere în gramatică sau limbaj pe care serverul SQL o acceptă. Dacă există, serverul SQL nu îl poate procesa și, prin urmare, va returna un mesaj de eroare.

Vom afla mai multe despre interogarea MS SQL în tutoriale viitoare. Totuși, luați în considerare mai jos cea mai de bază sintaxă a interogării ca 

SELECT * from ;

Acum, pentru a obține percepția a ceea ce face sintactic, spuneți dacă utilizatorul execută interogarea de bază după cum urmează:

SELECR * from

Rețineți că, în loc de „SELECT”, utilizatorul a tastat „SELECR”.

Rezultat: Analizorul CMD va analiza această declarație și va arunca mesajul de eroare. Deoarece „SELECR” nu urmează numele și gramatica cuvintelor cheie predefinite. Aici CMD Parser aștepta „SELECT”.

Verificare semantică:

  • Aceasta este realizată de Normalizer .
  • În cea mai simplă formă, verifică dacă numele coloanei, numele tabelului solicitat există în schemă. Și dacă există, legați-l de Query. Acest lucru este, de asemenea, cunoscut sub numele de Binding .
  • Complexitatea crește atunci când interogările utilizatorilor conțin VIEW. Normalizer efectuează înlocuirea cu definiția vizualizării stocate intern și multe altele.

Să înțelegem acest lucru cu ajutorul exemplului de mai jos -

SELECT * from USER_ID

Rezultat: Analizorul CMD va analiza această declarație pentru verificarea semantică. Analizorul va arunca un mesaj de eroare deoarece Normalizer nu va găsi tabelul solicitat (USER_ID) deoarece nu există.

Creați arborele de interogare:

  • Acest pas generează arborele de execuție diferit în care interogarea poate fi executată.
  • Rețineți că toți arborii diferiți au aceeași ieșire dorită.

Optimizator

Activitatea optimizatorului este de a crea un plan de execuție pentru interogarea utilizatorului. Acesta este planul care va determina modul în care interogarea utilizatorului va fi executată.

Rețineți că nu toate interogările sunt optimizate. Optimizarea se face pentru comenzile DML (Data Modification Language) precum SELECT, INSERT, DELETE și UPDATE. Astfel de interogări sunt mai întâi marcate, apoi trimise către optimizator. Comenzile DDL precum CREATE și ALTER nu sunt optimizate, dar sunt compilate într-o formă internă. Costul interogării este calculat pe baza unor factori precum utilizarea procesorului, utilizarea memoriei și nevoile de intrare / ieșire.

Rolul optimizatorului este de a găsi cel mai ieftin, nu cel mai bun plan de execuție rentabil.

Înainte de a intra în mai multe detalii tehnice ale Optimizerului, luați în considerare mai jos un exemplu din viața reală:

Exemplu:

Să presupunem că doriți să deschideți un cont bancar online. Știți deja despre o bancă care durează maximum 2 zile pentru a deschide un cont. Dar aveți și o listă cu alte 20 de bănci, care pot dura sau nu mai puțin de 2 zile. Puteți începe să vă angajați cu aceste bănci pentru a determina care bănci durează mai puțin de 2 zile. Acum, este posibil să nu găsiți o bancă care durează mai puțin de 2 zile și este pierdut timp suplimentar din cauza activității de căutare în sine. Ar fi fost mai bine să deschideți un cont la prima bancă în sine.

Concluzie: este mai important să selectați cu înțelepciune. Pentru a fi precis, alegeți ce opțiune este cea mai bună, nu cea mai ieftină.

În mod similar, MS SQL Optimizer funcționează pe algoritmi exhaustivi / euristici încorporați. Scopul este de a minimiza timpul de rulare a interogărilor. Toți algoritmii Optimizer sunt proprietatea Microsoft și un secret. Deși , mai jos sunt pașii la nivel înalt efectuați de MS SQL Optimizer. Căutările de optimizare urmează trei faze așa cum se arată în diagrama de mai jos:

Faza 0: Căutați un plan banal:

  • Acest lucru este, de asemenea, cunoscut sub numele de etapa de pre-optimizare .
  • În unele cazuri, ar putea exista un singur plan practic, realizabil, cunoscut sub numele de plan banal. Nu este nevoie de crearea unui plan optimizat. Motivul este că, căutând mai multe, ar rezulta găsirea aceluiași plan de execuție în timp de rulare. Și asta cu costul suplimentar al căutării unui plan optimizat, care nu era deloc necesar.
  • Dacă nu a fost găsit niciun plan banal, atunci începe prima etapă.

Faza 1: Căutați planuri de procesare a tranzacțiilor

  • Aceasta include căutarea unui plan simplu și complex .
  • Căutare simplă în plan: datele anterioare ale coloanei și indexului implicate în interogare vor fi utilizate pentru analiza statistică. Aceasta constă de obicei, dar nu se limitează la un singur index pe tabel.
  • Totuși, dacă planul simplu nu este găsit, atunci se caută un plan mai complex. Acesta implică Index multiplu pe tabel.

Etapa 2: Procesare și optimizare în paralel.

  • Dacă niciuna dintre strategiile de mai sus nu funcționează, Optimizer caută posibilități de procesare paralelă. Acest lucru depinde de capacitățile și configurația de procesare a mașinii.
  • Dacă acest lucru nu este încă posibil, atunci începe faza finală de optimizare. Acum, scopul final de optimizare este găsirea tuturor celorlalte opțiuni posibile pentru executarea interogării în cel mai bun mod. Faza finală de optimizare Algoritmii sunt Microsoft Propriety.

Executor interogare

Interogator apel apel Metoda de acces. Acesta oferă un plan de execuție pentru logica preluării datelor necesare pentru execuție. Odată ce datele sunt primite de la Storage Engine, rezultatul este publicat în stratul Protocol. În cele din urmă, datele sunt trimise utilizatorului final.

Motor de stocare

Activitatea motorului de stocare este de a stoca date într-un sistem de stocare precum Disk sau SAN și de a prelua datele atunci când este necesar. Înainte de a ne adânci în motorul de stocare, să aruncăm o privire asupra modului în care sunt stocate datele în baza de date și tipul de fișiere disponibile.

Fișier de date și extindere:

Fișier de date, stochează fizic date sub formă de pagini de date, fiecare pagină de date având dimensiunea de 8 KB, formând cea mai mică unitate de stocare din SQL Server. Aceste pagini de date sunt grupate logic pentru a forma extensii. Niciunui obiect nu i se atribuie o pagină în SQL Server.

Întreținerea obiectului se face prin extinderi. Pagina are o secțiune numită Antet de pagină cu o dimensiune de 96 de octeți, care transportă informațiile despre metadate despre pagină, cum ar fi Tipul paginii, Numărul paginii, Dimensiunea spațiului utilizat, Dimensiunea spațiului liber și Pointer la pagina următoare și pagina anterioară , etc.

Tipuri de fisiere

  1. Fișier principal
  • Fiecare bază de date conține un fișier primar.
  • Acesta stochează toate datele importante legate de tabele, vizualizări, declanșatoare etc.
  • Extensia este. MDF de obicei, dar poate fi de orice extensie.
  1. Fișier secundar
  • Baza de date poate conține sau nu mai multe fișiere secundare.
  • Aceasta este opțională și conține date specifice utilizatorului.
  • Extensia este. ndf, de obicei, dar poate fi de orice extensie.
  1. Fișier jurnal
  • Cunoscut și sub denumirea de jurnale Scrieți înainte.
  • Extensia este. ldf
  • Folosit pentru gestionarea tranzacțiilor.
  • Acesta este folosit pentru a vă recupera de la orice instanțe nedorite. Efectuați o sarcină importantă de revenire la tranzacțiile neangajate.

Storage Engine are 3 componente; să le analizăm în detaliu.

Metoda de acces

Acționează ca o interfață între executantul de interogări și jurnalele de tranzacții / Manager tampon.

Metoda de acces în sine nu face nicio execuție.

Prima acțiune este de a determina dacă interogarea este:

  1. Selectați declarația (DDL)
  2. Declarație neselectată (DDL și DML)

În funcție de rezultat, metoda de acces face următorii pași:

  1. Dacă interogarea este DDL , declarația SELECT, interogarea este transmisă către Buffer Manager pentru procesare ulterioară.
  2. Și dacă interogați dacă este DDL, instrucțiune NON-SELECT , interogarea este transmisă către Transaction Manager. Aceasta include în mare parte declarația UPDATE.

Manager tampon

Managerul tampon gestionează funcțiile de bază pentru modulele de mai jos:

  • Planificați memoria cache
  • Analizarea datelor: cache tampon și stocare date
  • Pagina murdară

Vom învăța Planul, memoria tampon și memoria cache în această secțiune. Vom acoperi paginile murdare din secțiunea Tranzacție.

Planificați memoria cache

  • Planul de interogare existent: managerul tampon verifică dacă planul de execuție este acolo în memoria cache a planului stocată. Dacă da, atunci se utilizează memoria cache a planului și memoria cache a datelor asociate.
  • Prima dată planul de cache: De unde provine cache-ul planului existent?

    Dacă planul de execuție a interogării pentru prima dată se execută și este complex, este logic să îl stocați în memoria cache a planului. Acest lucru va asigura o disponibilitate mai rapidă când următoarea dată când serverul SQL primește aceeași interogare. Deci, nu este altceva decât interogarea în sine care este stocarea execuției Planului dacă este rulată pentru prima dată.

Analiza datelor: cache tampon și stocare date

Managerul tampon oferă acces la datele necesare. Mai jos sunt posibile două abordări, în funcție de existența sau nu a datelor în memoria cache:

Memorie cache tampon - Analizare ușoară:

Buffer Manager caută date în buffer în cache de date. Dacă sunt prezente, atunci aceste date sunt utilizate de Query Executor. Acest lucru îmbunătățește performanța, deoarece numărul de operații I / O este redus la preluarea datelor din cache în comparație cu preluarea datelor din stocarea datelor.

Stocare date - Analizare dură:

Dacă datele nu sunt prezente în Buffer Manager decât este necesar Datele sunt căutate în stocarea datelor. Dacă stochează și date în memoria cache pentru utilizare ulterioară.

Pagina murdară

Este stocat ca o logică de procesare a Transaction Manager. Vom afla în detaliu în secțiunea Transaction Manager.

Manager tranzacții

Managerul de tranzacții este invocat atunci când metoda de acces determină că interogarea este o instrucțiune non-selectată.

Manager jurnal

  • Log Manager păstrează o evidență a tuturor actualizărilor efectuate în sistem prin jurnalele din jurnalele de tranzacții.
  • Jurnalele au Număr de secvență a jurnalelor cu ID-ul tranzacției și înregistrarea de modificare a datelor .
  • Aceasta este utilizată pentru urmărirea tranzacției angajate și a retragerii tranzacțiilor .

Manager blocare

  • În timpul tranzacției, datele asociate în stocarea datelor se află în starea Blocare. Acest proces este gestionat de Lock Manager.
  • Acest proces asigură coerența și izolarea datelor . Cunoscut și sub numele de proprietăți ACID.

Procesul de executare

  • Log Manager începe înregistrarea și Lock Manager blochează datele asociate.
  • Copia datelor este păstrată în memoria cache a bufferului.
  • Copia datelor supuse actualizării este păstrată în jurnalul tampon și toate evenimentele actualizează datele în jurnalul tampon de date.
  • Paginile care stochează datele sunt, de asemenea, cunoscute sub numele de Dirty Pages .
  • Punct de verificare și înregistrare în avans: acest proces rulează și marchează toată pagina de la Dirty Pages la Disk, dar pagina rămâne în cache. Frecvența este de aproximativ 1 rulare pe minut, dar pagina este mai întâi împinsă pe pagina de date a fișierului jurnal din jurnalul tampon. Acest lucru este cunoscut sub numele de Scriere înainte de înregistrare.
  • Lazy Writer: pagina Dirty poate rămâne în memorie. Când serverul SQL observă o încărcare uriașă și este necesară memorie tampon pentru o nouă tranzacție, eliberează Dirty Pages din cache. Funcționează pe LRU - Algoritmul cel mai recent utilizat pentru curățarea paginii din pool-ul tampon pe disc.

Rezumat:

  • Există trei tipuri de arhitectură server client: 1) Memorie partajată 2) TCP / IP 3) Pipe denumite
  • TDS, dezvoltat de Sybase și deținut acum de Microsoft, este un pachet care este încapsulat în pachete de rețea pentru transferul de date de la computerul client la computerul server.
  • Motorul relațional conține trei componente majore:

    Analizator CMD: Acesta este responsabil pentru erori sintactice și semantice și, în cele din urmă, generează un arbore de interogare.

    Optimizator: rolul Optimizatorului este de a găsi cel mai ieftin, nu cel mai bun plan de execuție rentabil.

    Executor interogare: Executorul interogării apelează metoda de acces și oferă planul de execuție pentru logica preluării datelor necesare pentru execuție.

  • Există trei tipuri de fișiere Fișier primar, fișier secundar și fișiere jurnal.
  • Motor de stocare: Are următoarele componente importante

    Metodă de acces: această componentă Stabiliți dacă interogarea este Declarație selectată sau neselectată. Invocă Buffer și Transfer Manager în consecință.

    Manager tampon: Managerul tampon gestionează funcțiile de bază pentru Plan Cache, Data Parsing & Dirty Page.

    Manager tranzacții: gestionează tranzacții neselectate cu ajutorul managerilor de jurnal și blocare. De asemenea, facilitează implementarea importantă a jurnalului Write Ahead și a scriitorilor Lazy.