Meniul
AcasăYandex

Optimizarea interogărilor SQL. Motive pentru care interogările SQL sunt ineficiente în Oracle. Optimizarea performanței interogărilor SQL

Voi împărtăși experiența pe care am acumulat-o de-a lungul mai multor ani de optimizare a interogărilor SQL. Majoritatea sfaturilor se referă la bazele de date ORACLE.
Dacă articolul pare prea evident pentru cineva, atunci consideră-l ca o notă exclusiv pentru tine, pentru a nu uita.

1. Fără subinterogări, doar JOIN
După cum am scris mai devreme, dacă eșantionul este 1 la 1 sau trebuie să însumați ceva, atunci nu există subinterogări, doar alăturați-vă.
Este de remarcat faptul că, în majoritatea cazurilor, optimizatorul va putea extinde subinterogarea într-o alăturare, dar acest lucru s-ar putea să nu se întâmple întotdeauna.

2. Selectați ÎN sau EXISTĂ?
De fapt este Alegere dificilăȘi solutie corecta poate fi obținut doar prin experiență.
Îți voi da doar câteva sfaturi:
* Dacă există multe rânduri în selecția principală și puține în subinterogare, atunci alegerea dvs ÎN. Deoarece în acest caz, interogarea în in va fi executată o dată și va constrânge imediat tabelul principal mare.
* Dacă într-o subinterogare interogare complexă, și există relativ puține rânduri în eșantionul principal, apoi alegerea dvs EXISTĂ. În acest caz, o interogare complexă nu va fi executată la fel de des.
* Dacă ambele sunt dificile, atunci acesta este un motiv pentru a schimba logica la alăturari.

3. Nu uitați de indici
Sfat pentru începători completi: atașați indecși la coloanele pe care vă alăturați tabele.

4. Dacă este posibil, evitați utilizarea SAU.
Efectuați teste, poate UNION nu arată atât de elegant, dar solicitarea poate fi executată mult mai rapid. Motivul este că, în cazul OR, indecșii sunt foarte greu utilizați în join.

5. Dacă este posibil, nu utilizați CUîn oracol.
Face viața mult mai ușoară dacă interogarea cu trebuie utilizată de mai multe ori (cu indicația materialize) în selecția principală sau dacă numărul de rânduri din subinterogare nu este semnificativ.
În toate celelalte cazuri, este necesar să se utilizeze subinterogări directe din sau un tabel pre-preparat cu indecșii și datele necesare de la WITH.
Cauză munca proasta WITH este că atunci când vă alăturați, nu sunt utilizați indici și dacă există o mulțime de date în el, atunci totul va funcționa. Al doilea motiv este că este dificil pentru optimizator să determine câte date vor reveni la noi, iar optimizatorul nu poate construi planul de interogare corect.
În cele mai multe cazuri, WITH fără +materialize va fi extins în continuare în interogarea principală.

6. Nu faceți solicitări de kilometri
Adesea, pe web, problema opusă este că există multe cereri mici într-o buclă și se recomandă să fie combinate într-una mare. Dar există limitări aici: dacă aveți o interogare încapsulată de mai multe ori de la, atunci părțile interne trebuie plasate într-o selecție separată, completate într-un tabel temporar, indecși alocați și apoi utilizate în selecția principală. Viteza de lucru va fi semnificativ mai mare (în primul rând datorită complexității construirii unui plan optim pe un număr mare de combinații de mese)

7. Utilizați KEEP în loc de subinterogări de corelare.
ORACLE are funcții analitice foarte utile care vă vor simplifica interogările. Una dintre ele este KEEP.
KEEP vă va permite să sortați sau să grupați selecția principală fără o solicitare suplimentară.
Exemplu: selectați o contraparte pentru un articol care i-a fost asociat mai devreme decât altele. Un articol poate avea mai mulți furnizori.
SELECT n.ID, MIN(c.ID) PĂSTRAȚI (DENSE_RANK FIRST ORDER BY c.date ASC) ca cnt_id FROM nmcl n, cnt c WHERE n.cnt_id = c.id GROUP BY n.ID Cu abordarea obișnuită am avea pentru a face subinterogare de corelare pentru fiecare articol cu ​​selectarea datei minime.
Dar nu exagerați un numar mare funcții analitice, mai ales dacă au diferite sortări. Fiecare fel diferit este o nouă scanare a ferestrei.

8. Mergând în sus și în jos pe eșantion
O caracteristică mai puțin populară, dar nu mai puțin utilă. Vă permite să schimbați linia curentă selecții prin N elemente în sus sau în jos. Poate fi util dacă trebuie să comparați indicatorii rândurilor adiacente.
Următorul exemplu selectează vânzările departamentale sortate după dată. Coloanele cu valorile următoare și anterioare ale veniturilor sunt adăugate la selecția principală. Al doilea parametru este câte linii trebuie mutate, al treilea este parametrul implicit dacă datele vecinului nu sunt găsite. SELECT deptno, empno, sal, LEAD(sal, 1, 0) OVER (PARTIȚIE BY dept ORDER BY date) NEXT_LOWER_SAL, LAG(sal, 1, 0) OVER (PARTIȚIE BY dept ORDER BY date) PREV_HIGHER_SAL FROM emp; COMANDA PENTRU deptno, data DESC; Cu abordarea obișnuită, acest lucru ar trebui făcut prin logica aplicației.

9. Citire directă a căii
Setarea acestei setari (fie prin reglare, fie prin interogare paralelă) este să citiți datele direct în PGA, ocolind memoria cache a tamponului. Ceea ce încetinește etapele ulterioare ale cererii, deoarece zăvoarele UNDO și de partajare nu sunt utilizate.

10. IO direct
Folosind scrierea/citirea directă de pe disc fără a utiliza un buffer de sistem de fișiere ( Sistemul de fișiere special pentru DBMS).
* În cazul citirii, avantajul este utilizarea cache-ului buffer al bazei de date, înlocuind cache-ul FS (cache-ul bazei de date este mai bine proiectat să funcționeze cu sql)
* În cazul înregistrării, înregistrarea directă asigură că datele nu se pierd în bufferul FS în cazul unei întreruperi de curent (pentru redolog folosește întotdeauna fsync, indiferent de tipul FS)

După scrierea programului și apariția datelor „în direct”, se dovedește că răspunsul programului la seturile de testare este uneori foarte diferit de lucrul cu date reale. Programatorii acordă de obicei puțină atenție formării truse de testare date, ceea ce este o eroare gravă. Pariul este pe utilizarea DBMS-urilor moderne „cool” care se configurează singure. Din păcate, acest lucru nu este în întregime adevărat și trebuie acordată o atenție deosebită lucrului cu baza de date. În mod ideal, specialiștii ar trebui să fie responsabili pentru procesarea logicii de afaceri. Dar este, de asemenea, util pentru programatorii obișnuiți să aibă abilități și cunoștințe despre arhitectura DBMS și să scrie interogări SQL.

Există adesea cazuri când generatoarele de script și codul programului pentru a accesa date. Autorii programelor speră că inovațiile tehnologice moderne vor face toată munca pentru tine. Ca urmare, există adesea cazuri în care, la câteva luni după implementarea programului, utilizatorii încep să se plângă că programul „abia se mișcă”. O panică generală începe cu implicarea unor specialiști scumpi care pot găsi „gâtul de sticlă” care încetinește programul și pot salva proiectul.

Practica arată că prin analiza și reconstruirea interogărilor SQL, puteți reduce timpul de execuție a acestora de zeci și uneori de sute de ori. După dezvoltarea mai multor proiecte, programatorii își dezvoltă abilitățile de a scrie interogări mai rapide. Dar este totuși util să analizați periodic costurile cu resursele serverului atunci când rulați creația dvs. Și deși în general analiza folosirii resurselor serverului este treaba unui administrator de baze de date avand abilitati in optimizarea programelor nu va strica nimanui; În plus, nu este atât de dificil pe cât pare la prima vedere.

Există o serie de programe care vă permit să automatizați și să simplificați această sarcină. Acest material este axat pe lucrul cu serverul Oracle, dar pentru alte baze de date există instrumente similare de analiză și optimizare a reglajului. Primul nostru asistent va fi un program de monitorizare pentru serverul Oracle numit „Spotlight on Oracle” din software-ul Quest (http://www.quest.com). Aceasta este foarte Unealtă puternică, conceput pentru a controla funcționarea serverului dvs. Acest program realizat într-un mod neobișnuit paleta de culori, ceea ce îl face să iasă în evidență față de alte produse. După rularea acestui program, trebuie să creați un cont de utilizator, care va necesita un cont SYS sau un cont cu privilegii de sistem DBA. Asistent noua creație cont apelat din meniul „Fișier > Expert utilizator”.

După crearea unui cont de utilizator și conectarea la serverul Oracle, ni se prezintă o imagine vizuală care afișează componentele și procesele serverului. Dacă una sau mai multe componente ale serverului nu funcționează optim sau sunt supraîncărcate, atunci culoarea acestuia se schimbă de la verde la roșu, în funcție de gradul de supraîncărcare. Este posibil să monitorizați mai multe servere simultan, a căror listă este afișată în panoul din stânga și, de asemenea, își schimbă culoarea. Pictograma din bara de activități își schimbă, de asemenea, culoarea sincron cu programul, ceea ce vă permite să răspundeți rapid atunci când este „minimizat” în aplicație. Un exemplu de monitorizare este prezentat în Figura 1.

O caracteristică foarte utilă a acestui program este sistemul de auto-recomandări pentru rezolvarea problemelor. Doar faceți clic pe zona roșie a imaginii pentru a obține o descriere detaliată a problemei și metode posibile eliminarea acestuia. Dacă totul este în regulă, atunci datorită acestui program puteți ajusta parametrii de pornire a serverului pentru a reduce cantitatea de date pe care o utilizează. resurse de sistem. De exemplu, din Figura 1 putem concluziona că dimensiunea spațiului tabelă al fișierului bazei de date poate fi redusă în siguranță la jumătate și este recomandabil să se aloce memorie suplimentară sub „Piscina comună”.

Dar aceasta este toată problema administratorului bazei de date. Dezvoltatorii sunt mai preocupați de modul în care funcționează creațiile lor și de câte resurse consumă interogările bazei de date. Pentru a face acest lucru, apelați elementul de meniu „Navigator > Top Sessions”. După completarea parametrilor filtrului de selecție a datelor, ni se va afișa o listă de solicitări curente către serverul bazei de date. După ce ați sortat anterior cererile în funcție de cerințele de resurse, le puteți selecta pe cele mai „lacom”. În aceeași fereastră puteți vedea planul de execuție a interogării, al cărui exemplu este prezentat în Figura 2. Mai mult, planul de interogare poate fi prezentat sub forma unui grafic, arbore sau descriere verbală. Se foloseste si aici cod de culoare zonele cu probleme.

După identificarea interogărilor SQL problematice, era timpul să le optimizăm. Pentru a automatiza acest proces vom folosi program SQL Expert de la LECCO (http://www.leccotech.com). Apelați fereastra editorului SQL și adăugați-i scriptul de interogare. Aici puteți vedea și planul de execuție a interogării. Dar ne interesează cel mai mult funcția de meniu „SQL-> Optimize”, care generează o listă opțiuni alternative construirea unui script SQL dat. Iar funcția „SQL-> Butch Run” vă permite să analizați timpul de execuție al tuturor interogărilor privind datele „în direct” și să afișați tabelul rezultat, care poate fi sortat după parametrul necesar. După ce ați selectat cea mai optimă cerere, o puteți compara cu originalul și puteți decide cu privire la posibilitatea de a o utiliza în continuare în aplicația dvs. Un exemplu de lucru de optimizare a interogărilor este prezentat în Figura 3.

Astfel, folosind tehnica propusă, fără a afecta codul programului principal, uneori este posibilă creșterea semnificativă a performanței aplicației și salvarea proiectului. Procedând astfel, veți dobândi o experiență neprețuită în scrierea de interogări de înaltă performanță. Analizând opțiunile de script SQL oferite de program, de-a lungul timpului vă dezvoltați imediat obiceiul de a scrie „optim”, ceea ce vă crește și imaginea de bun specialist.

Când un utilizator începe o operațiune de regăsire a datelor, instrucțiunea SQL a acelui utilizator parcurge mai mulți pași secvențiali, care sunt numiți în mod colectiv procesarea interogărilor. Unul dintre principalele avantaje Limbajul SQL este că nu este procedural și, prin urmare, nu trebuie să enumerați pașii care trebuie parcurși pentru a atinge scopul operatorului. Cu alte cuvinte, SQL nu trebuie să descrie cum ar trebui făcut ceva, trebuie doar să descrie ceea ce dorește să obțină din baza de date.

Procesarea interogărilor înseamnă convertirea instrucțiunii SQL într-un plan de execuție eficient pentru a returna datele solicitate din baza de date. Optimizarea interogărilor se referă la procesul de selectare a celui mai eficient plan de execuție pentru a obține un rezultat cu cel mai mic cost în ceea ce privește consumul de resurse, cum ar fi resursele I/O și CPU, pe serverul pe care rulează baza de date, precum și reducerea totală. timpul de execuție a interogării, care este pur și simplu suma indicatorilor de timp de execuție ai tuturor componentelor a acestei cereri operațiuni. Este posibil ca această optimizare a performanței să nu arate ca minimizarea timpului de răspuns. Dacă trebuie să minimizeze timpul petrecut pentru preluarea primelor n rânduri, mai degrabă decât întreaga ieșire a unei interogări, optimizatorul poate alege un plan diferit și, dacă trebuie să minimizeze timpul de răspuns pentru toate datele de interogare, poate alege, de asemenea, un plan paralel. modul de executare a operației.

În general, fiecare instrucțiune SQL executată de un utilizator trece printr-o etapă analizare, faza de optimizare și faza de execuție. Dacă instrucțiunea SQL este o interogare, aceasta implică preluarea datelor și, prin urmare, în acest caz, înainte de finalizarea procesului de procesare, există și o etapă suplimentară de recuperare. Următoarele subsecțiuni intră în mai multe detalii despre ceea ce face Oracle în fiecare dintre aceste faze.

Analizarea interogărilor SQL

Faza de analiză constă în principal în verificarea sintaxei și semanticii instrucțiunilor SQL. La sfârșitul acestei etape, este creat un arbore de analiză care reflectă structura cererii.

Mai exact, în această etapă instrucțiunea SQL este convertită într-o interogare algebră relațională, care este analizat pentru a determina dacă este corect din punct de vedere sintactic. Interogarea este apoi supusă unei verificări de validare semantică, care utilizează un dicționar de date pentru a verifica dacă toate tabelele și coloanele individuale la care se face referire în interogare, precum și orice privilegii necesare pentru obiecte, există efectiv. În plus, tipurile de coloane sunt verificate pentru a se asigura că datele se potrivesc cu definițiile coloanei. Declarația este apoi normalizată, astfel încât să poată fi procesată într-un mod mai eficient. Dacă cererea este formulată greșit, aceasta este respinsă. Odată ce arborele de analiză trece toate verificările sintactice și semantice, acesta este considerat valid și trimis la etapa de generare a planului de interogare logic. Toate aceste operațiuni au loc în SGA, care reprezintă memoria cache a bibliotecii părții.

Optimizarea interogărilor SQL

În timpul fazei de optimizare, Oracle folosește optimizatorul său, numit Cost-Base Optimizer (CBO), pentru a selecta cea mai buna metoda acces pentru a prelua date din tabelele și indecșii prezenți în cerere. Utilizând statisticile furnizate și orice indicii specificate în interogările SQL, CBO generează planul optim de execuție pentru instrucțiunea SQL.

ÎN caz general Etapa de optimizare poate fi împărțită în două sub-etape separate: rescrierea interogării și generarea unui plan fizic de execuție a interogării. Să ne uităm mai detaliat la acești doi subpași separati de optimizare.

Faza de rescriere a solicitării

În această etapă, arborele de analiză este convertit într-un plan logic abstract pentru executarea interogării. Reprezintă versiunea originală plan real execuția interogării și conține doar o reformulare algebrică generală a interogării originale. Adică, în această etapă, diferite noduri și ramuri ale arborelui de analiză sunt înlocuite cu operații de algebră relațională. Rețineți că rescrierea interogărilor aici înseamnă ceva foarte diferit de rescrierea interogărilor efectuată atunci când se utilizează vizualizări materializate.

Etapa de generare a planului de execuție

În acest moment, Oracle convertește planul logic într-un plan fizic de interogare. Pentru a procesa o interogare, optimizatorul poate avea mai mulți algoritmi din care să aleagă. Selectează cel mai eficient dintre acești algoritmi pentru a răspunde la interogare și determină cel mai mult metoda eficienta pentru a implementa operațiuni. Pe lângă faptul că decide ce pași operaționali sunt cei mai buni de efectuat, el determină și ordinea în care acești pași trebuie să fie efectuati. De exemplu, după ce a decis că o operație de îmbinare ar trebui efectuată între tabelul A și tabelul B, optimizatorul va determina apoi ce tip de îmbinare ar trebui să fie și în ce ordine este cel mai bine să o efectueze.

În general, atunci când se generează un plan fizic sau un plan de execuție a interogării, optimizatorul ia în considerare următorii factori:

  • diverse operațiuni (cum ar fi operațiuni de alăturare) care trebuie efectuate în timpul unei solicitări;
  • ordinea în care trebuie efectuate aceste operațiuni;
  • algoritmul care ar trebui utilizat pentru a efectua fiecare dintre ele;
  • cea mai buna cale pentru a prelua date de pe disc sau memorie;
  • cel mai bun mod de a transmite date în timpul unei solicitări de la o operațiune la alta.

Optimizatorul poate genera mai multe planuri de interogare fizice reale, care sunt planuri potențiale de execuție. Optimizatorul face apoi o alegere între ele, estimând costul fiecărui plan fizic posibil pe baza statisticilor de tabel și index disponibile și selectând planul al cărui cost calculat este cel mai mic. Acest proces de estimare a costului posibilelor planuri fizice de interogare se numește optimizare bazată pe cost. Costul executării unui plan este direct legat de câte resurse (I/O, memorie și CPU) necesită. Optimizatorul transmite apoi planul de interogare fizic cu cel mai mic cost selectat motorului de execuție Interogări Oracle. Următoarea secțiune analizează un exemplu simplu pentru a vă ajuta să înțelegeți mai bine despre ce este vorba despre optimizarea procesului de cost.

Exemplu de optimizare a interogărilor în funcție de cost

Să presupunem că vrem să rulăm următoarea interogare pentru a găsi informații despre toți supraveghetorii care lucrează în Dallas:

SQL> SELECT * FROM angajat e, dept d WHERE e.dept_no = d.dept_no AND(e.job = "SUPERVISOR" AND d.city = "DALLAS"); SQL>

Există trei moduri de a obține o listă de manageri. Să ne uităm la aceste trei metode și să calculăm costul obținerii rezultatelor dacă fiecare dintre ele este utilizată.

Pentru a calcula costul, să începem cu următoarele ipoteze:

  • Puteți citi și scrie date doar câte un rând (în realitate, operațiunile I/O sunt de obicei efectuate la nivel de bloc, nu la nivel de rând);
  • baza de date scrie fiecare pas intermediar pe disc (din nou, în lumea reala acesta poate să nu fie cazul);
  • nu sunt asociați indici cu tabelele;
  • in masa angajat conține 2000 de linii;
  • in masa dept conține 40 de linii și, de asemenea, 40 de manageri (câte unul pentru fiecare departament);
  • Există zece filiale în total în Dallas.

Următoarele secțiuni arată trei interogări diferite care preiau aceleași date, dar utilizând metode diferite acces. Pentru fiecare cerere se calculează un cost brut, astfel încât acestea să poată fi comparate în ceea ce privește consumul de resurse. Prima interogare implică efectuarea unei îmbinări carteziane.

Interogarea 1: îmbinare carteziană

Când se utilizează această interogare, se obține mai întâi produsul cartezian al tabelelor angajatȘi dept, apoi verificați care dintre liniile din acesta satisface cerința:

WHERE e.job=supervizor AND d.dept=operations AND e.dept_no=d.dept_no

  • Produsul cartezian al tabelelor angajatȘi dept va necesita citirea ambelor tabele, de ex. 2000 + 40 = 2040 operații de citire;
  • crearea unui produs cartezian - 2000 * 40 = 80000 scrieri;
  • citirea rezultatului produsului cartezian pentru a-l compara cu condiția de selecție a rândului - 2000 * 40 = 80000 operații de citire;
  • în total, costul total al I/O va fi: 2040 + 80000 + 80000 = 162040.

Interogarea 2: Unirea a două tabele

A doua interogare implică efectuarea unei îmbinări la tabel angajatȘi dept. Dacă se utilizează această interogare, tabelele vor fi mai întâi unite angajatȘi dept după coloană dept_nrși apoi selectând din rezultatele acestei uni toate rândurile care îndeplinesc condiția:

UNDE e.job=supervizor și oraș=Dallas

Costul total al executării acestei interogări ar arăta astfel:

  • unirea meselor angajatȘi dep va necesita mai întâi citirea tuturor rândurilor din ambele tabele, de exemplu. 2000 + 40 = 2.040 operații de citire;
  • crearea unei uniri la tabel angajatȘi dep- 2000 operatii de scriere;
  • citirea rezultatelor conexiunii va costa 2000 de citiri;
  • în total, costul total al I/O va fi: 2040 + 2000 + 2000 = 6040.

Interogarea 3: Conectarea comenzilor rapide

A treia interogare implică, de asemenea, efectuarea unei îmbinări la tabel angajatȘi dept, dar cu legătura nu a tuturor, ci numai rânduri de probă din aceste două tabele. Dacă sunt utilizate, datele necesare vor fi preluate așa cum este descris mai jos. Mai întâi va citi tabelul angajaților pentru a obține toate rândurile cu valoarea supervizor. O citire din tabelul departamentului va fi apoi efectuată pentru a prelua toate rândurile cu valoarea DALLAS. Și în final, conexiunea se va realiza pe acele rânduri care au fost extrase din tabele angajatȘi dept.

Costul total al executării acestei interogări ar arăta astfel:

  • citiți tabelul angajat pentru a prelua rândurile cu valoare supervizor va costa 2000 de operatii de citire;
  • scrierea șirurilor cu valoare supervizor, care au fost extrase la pasul anterior- 40 operatii de scriere;
  • citiți tabelul dept pentru a prelua toate rândurile cu o valoare DALLAS- 40 de operatii de citire;
  • scrierea șirurilor cu valoare DALLAS, extras in pasul anterior - 10 operatii de scriere;
  • concatenarea șirurilor de caractere cu valoare supervizorși cu sens DALLAS, extras la etapele anterioare de executare a acestei cereri - un total de 40 + 10 = 50 operatii de scriere;
  • citirea rezultatului conexiunii obținut la pasul anterior - 50 de operații de citire;
  • costul total al I/O va fi: 2000 + 2 (40) + 10 + 2 (50) = 2190.

Acest exemplu, oricât de simplu este, arată că produsele carteziene sunt mai scumpe decât îmbinările cu condiții mai restrictive. Chiar și o operație de îmbinare selectivă se dovedește a fi mai costisitoare decât o operație de selecție. Deși operația de îmbinare din Interogarea 3 este o îmbinare a două relații reduse, dimensiunea de îmbinare pare a fi mult mai mică decât unirea din Interogarea 2. Optimizarea interogării implică adesea efectuarea de selectare timpurie (selectarea doar a unor rânduri) și proiecție (selectarea doar a unor coloane). ) operațiuni ) pentru a reduce dimensiunea surselor de ieșire sau rând rezultate.

Strategii euristice pentru procesarea interogărilor

Utilizarea tehnicilor de optimizare a costurilor nu este singura modalitate de a realiza optimizarea interogărilor. Tehnicile mai puțin sistematice cunoscute sub denumirea de strategii euristice pot fi, de asemenea, utilizate pentru a procesa interogări de baze de date. O operație de îmbinare este binară, dar o operație precum select este unară. O strategie de succes, în general, este efectuarea unei operații unare asupra stadiu timpuriu astfel încât în ​​mai complex și consumator de timp operatii binare apoi s-au folosit operanzi mai mici. Fă-o mai întâi pe cât posibil Mai mult operațiunile unare reduc sursele de rând în operațiunile de îmbinare. Mai jos sunt enumerate unele dintre cele mai comune strategii euristice pentru procesarea interogărilor.

  • Operațiunile de selecție ar trebui efectuate devreme pentru a elimina rândurile candidate la începutul procesării interogărilor. Lăsând majoritatea șirurilor până la sfârșit, se vor realiza comparații inutile cu șiruri care oricum nu vor fi utile la sfârșit.
  • Operațiunile de proiecție ar trebui efectuate din timp pentru a limita numărul de coloane care trebuie procesate.
  • Dacă trebuie să efectuați operații de îmbinare secvențială, ar trebui să efectuați mai întâi cea care produce cea mai mică îmbinare.
  • Expresiile cele mai frecvent utilizate ar trebui evaluate o dată și rezultatele salvate.

Executarea interogărilor

Pe ultima etapăÎn timpul procesului de procesare a interogării, interogarea optimizată (planul fizic de interogare care a fost selectat) este executată. Dacă este un operator SELECTAȚI, atunci rândurile corespunzătoare sunt returnate utilizatorului, iar dacă operatorul INSERT, UPDATE sau ȘTERGE, apoi faceți modificările corespunzătoare în linii. Motorul de execuție SQL preia planul de execuție obținut în faza de optimizare și îl execută.

Dintre cele trei etape ale procesării instrucțiunilor SQL, etapa de optimizare este cea mai importantă deoarece determină cât de repede pot fi recuperate datele necesare. Înțelegerea modului în care funcționează optimizatorul joacă Rol cheieîn înţelegerea procesului de optimizare. Pentru a scrie cod SQL eficient, este important să știți cum arată metode tipice acces, metode de conectare și comenzi de conectare. Prin urmare, secțiunea următoare prevede descriere detaliata Optimizatorul CBO atotputernic al Oracle.

Acum este momentul să optimizăm declarații condiționale SQL. Majoritatea interogărilor folosesc clauza SQL WHERE, astfel încât, prin optimizarea condițiilor, puteți obține performanțe semnificative de interogare. În același timp, din anumite motive Mică parte Aplicațiile de baze de date folosesc optimizarea condițiilor.

ȘI

Evident, într-o serie de mai multe declarații ȘI condițiile ar trebui aranjate în ordinea creșterii probabilității adevărului această condiție. Acest lucru se face astfel încât la verificarea condițiilor, baza de date să nu verifice restul condiției. Aceste recomandări nu se aplică bazelor de date Oracle, în care condițiile sunt verificate de la sfârșit. În consecință, ordinea lor ar trebui inversată - în ordinea descrescătoare a probabilității adevărului.

SAU

Situația cu acest operator este exact opusă situației cu AND. Condițiile trebuie aranjate în ordinea descrescătoare a probabilității de a fi adevărate. Compania Microsoft recomandă insistent utilizarea aceasta metoda atunci când construiesc interogări, deși mulți nici măcar nu știu despre asta sau cel puțin nu îi acordă atenție. Dar din nou, acest lucru nu se aplică bazei de date Oracle, unde condițiile ar trebui aranjate în ordinea creșterii probabilității adevărului.

O altă condiţie pentru optimizare poate fi considerată faptul că dacă coloane identice sunt situate în apropiere, cererea este finalizată mai rapid. De exemplu, cerere „.. WHERE coloana1 = 1 SAU coloana2 = 3 SAU coloana1 = 2” se va executa mai lent decât cererea „UNDE coloana1 = 1 SAU coloana1 = 2 SAU coloana2 = 3” . Chiar dacă probabilitatea ca condiția coloana2 = 3 să fie adevărată este mai mare decât coloana 1 = 2.

Întors la școală, mi s-a spus despre legea distributivă. Se spune că A ȘI (B SAU C)- la fel ca (A ȘI B) SAU (A ȘI C). S-a constatat experimental că o cerere a formularului „...UNDE coloana1 = 1 ȘI (coloana2 = „A” SAU coloana2 = „B”)” rulează puțin mai repede decât „...UNDE (coloana1 = 1 ŞI coloana2 = „A”) SAU (coloana1 = 1 ŞI coloana2 = „B”)” . Unele baze de date pot optimiza interogări de acest tip, dar este mai bine să fii în siguranță.

NU

Această operațiune ar trebui întotdeauna redusă la o formă mai „lizibilă” (în limite rezonabile, desigur). Astfel, interogarea „...UNDE NU (coloana1 > 5)” este convertită în „...UNDE coloana1<= 5" . Condițiile mai complexe pot fi transformate folosind regula lui De Morgan, pe care ar fi trebuit să o înveți și la școală. Conform acestei reguli NU(A ȘI B) = (NU A) SAU (NU B) și NU(A SAU B) = (NU A) ȘI (NU B) . De exemplu, starea „...UNDE NU (coloana1 > 5 SAU coloana2 = 7)” transformat în mai mult formă simplă: ...UNDE coloana1<= 5 AND column2 <> 7 .

ÎN

Mulți cred naiv că cererea „... WHERE coloana1 = 5 SAU coloana1 = 6” echivalent cu o cerere „...UNDE coloana1 ÎN (5, 6)”. De fapt, acest lucru nu este adevărat. Operația IN este mult mai rapidă decât seria OR. Prin urmare, ar trebui să înlocuiți întotdeauna OR cu IN ori de câte ori este posibil, deși unele baze de date realizează singure această optimizare. În cazul în care se utilizează o serie de numere secvențiale, IN ar trebui schimbată în BETWEEN. De exemplu, „...UNDE coloana1 ÎN (1, 3, 4, 5)” optimizat pentru a arăta astfel: …UNDE coloana1 INTRE 1 SI 5 SI coloana1<> 2 . Și această interogare este cu adevărat mai rapidă.

CA

Această operațiune ar trebui folosită numai atunci când este absolut necesar, deoarece este mai bine și mai rapid să folosiți căutări bazate pe indexuri full-text. Din păcate, trebuie să vă direcționez către World Wide Web pentru informații despre căutare.

CAZ

Această funcție în sine poate fi utilizată pentru a îmbunătăți viteza unei interogări atunci când are mai multe apeluri la o funcție lentă într-o condiție. De exemplu, pentru a evita apelarea slow_function() din nou într-o interogare „...UNDE slow_function(coloana1) = 3 SAU slow_function(coloana1) = 5” , trebuie să utilizați CASE:

... WHERE 1 = CASE slow_function(coloana1)

CÂND 3 APOI 1

CÂND 5 APOI 1

Maurice Lewis

Ce diferență uriașă poate face un index în ceea ce privește viteza de execuție a interogărilor! Recent, autorul articolului a primit o altă confirmare vizuală a acestui adevăr. El a introdus un index suplimentar, iar timpul de procesare a interogărilor a scăzut de la 40 de minute la 12 secunde. Dar indexurile sunt doar unul dintre instrumentele pe care administratorii de baze de date le folosesc pentru a îmbunătăți performanța sistemului. Puteți îmbunătăți performanța ajustând o varietate de parametri - de la configurarea hardware-ului până la utilizarea utilităților bazei de date. Mai jos sunt cele 9 cele mai eficiente moduri de a îmbunătăți performanța SQL Server 6.5.

1.Alocați serverului câtă memorie RAM poate gestiona.

Cu cât mai multă baza de date poate încadra în cache, cu atât interogările vor fi procesate mai rapid. Prin urmare, este recomandabil să măriți dimensiunea RAM proporțional cu dimensiunea bazei de date. De exemplu, dacă baza de date ocupă 1 GB, atunci 1 GB de RAM vă va permite să încadrați aproape întreaga bază de date în memorie. Ar trebui să rămână puțin RAM pentru Windows NT. Autorul preferă să lase de la 64 MB la 128 MB pentru sistemul de operare și să aloce restul memoriei pentru SQL Server. Și cu siguranță trebuie să păstrați cantitatea de memorie fizică disponibilă NT de cel puțin 4 MB. Dacă scade sub limita specificată, NT va începe imediat să creeze pagini de memorie virtuală pe disc.

2. Utilizați matrice RAID de nivel 0 sau 5 pentru a paraleliza regăsirea informațiilor din baza de date.

Nivelurile RAID 0 și 5 distribuie cererile de citire pe mai multe discuri fizice. Probabil știți ce se întâmplă la abordările de poduri în orele de vârf, când mii de mașini încearcă simultan să treacă printr-un spațiu de lățime limitată. Același blocaj are loc pentru solicitările de citire a fișierelor de pe dispozitivele tale baze de date. Dacă puteți ruta date pe mai multe canale, serverul poate citi blocuri de date de pe fiecare hard disk în paralel. În acest caz, se observă o îmbunătățire aproape liniară a performanței. Această creștere a ratei de citire se datorează nivelurilor RAID 0 și 5. De exemplu, să ne uităm la cifrele din cartea lui Ron Saukup Inside SQL Server 6.5, publicată de Microsoft Press în 1997. El scrie că un singur hard disk de 4 GB poate gestiona 80 până la 90 de operațiuni I/O pe secundă. În același timp, o matrice RAID de nivel 0 de 8 hard disk-uri de 500 MB fiecare (adică cu aceeași capacitate totală) trece de 400 de operațiuni I/O pe secundă. Desigur, problema creșterii costurilor rămâne deschisă. Dar, în general, cu cât sunt mai multe hard disk-uri în matrice, cu atât este mai mare debitul bazei de date pentru operațiunile de citire.

3. Lăsați Max Async I/O să profite din plin de computerul dvs.

Subsistemul dvs. de disc poate fi capabil să gestioneze mai mult de opt operațiuni I/O asincrone pe secundă, care este mai mult decât valoarea implicită adoptată cu mai mult de trei ani în urmă, când a fost lansat SQL Server 6.5. Pentru a optimiza această setare, creșteți-o în trepte mici în timp ce monitorizați contorul de lungime medie a cozii de disc, AvgDiskQueueLength, în NT Performance Monitor. Atâta timp cât coada medie pentru un subsistem de discuri nu depășește de două ori numărul de discuri din acesta, putem presupune că nu îl supraîncărcați.

4. Setați praguri de extindere a blocării pentru întregul tabel.

Trei parametri de escaladare a blocării la nivel de tabel (LE): LE Threshold Maximum, LE Threshold Minimum și LE Threshold Procent, determină câte pagini trebuie să blocheze SQL Server înainte ca întregul tabel să fie blocat. Valorile implicite pentru acești parametri sunt 200, 20 și, respectiv, 0. Pentru tabelele foarte mari, blocarea întregului tabel evită supraîncărcarea a mii de blocări. Dacă baza de date conține sute de tabele, eliminarea acestei suprasarcini poate avea un impact semnificativ asupra performanței.

5. Creați indecși grupați pentru interogări care citesc intervale de valori.

Deoarece indicii grupați organizează fizic datele plasându-le în ordinea valorilor din index, astfel de indici oferă o bază excelentă pentru interogările care caută intervale de valori. De exemplu, dacă un tabel care conține înregistrări de cont individuale (rânduri) are un index grupat pe coloana ID cont, atunci toate rândurile legate de contul 0001 vor fi chiar la începutul tabelului și toate rândurile pentru contul 9999 vor fi localizate la chiar sfârșitul. Această ordine înseamnă că, dacă acest tabel se potrivește cu partea „mulți” a unei relații unu-la-mulți atunci când este alăturat unui alt tabel, SQL Server poate găsi cu ușurință primul rând cu valoarea dorită a ID-ului contului și apoi se poate deplasa prin tabel secvenţial până când acestea până când valoarea identificatorului se schimbă.

6. Creați indecși non-cluster pentru interogări care caută valori unice.

Spre deosebire de indecșii grupați, care includ întregul conținut al unei pagini, indecșii la nivel de frunză negrupați stochează o singură înregistrare mică pentru un întreg rând de tabel. Intrarea de index include numărul paginii și numărul rândului din cadrul acesteia, indicând astfel coordonatele rândului din tabel. Dacă interogarea dvs. caută informații dintr-o coloană care nu face parte dintr-un index, atunci SQL Server trebuie să memoreze în cache întreaga pagină care conține acel rând și să caute informațiile stocate în rândul de acolo. Principalul avantaj al indecșilor non-cluster este că permit SQL Server să găsească valori unice foarte rapid. De obicei, indecșii non-cluster sunt construiți pe chei primare, deoarece valorile lor sunt unice în definiția cheii primare și, prin urmare, indecșii non-cluster sunt grozavi pentru ei. Cu toate acestea, SQL Server nu folosește întotdeauna indecși non-cluster, chiar dacă i-ați creat. Prin urmare, construiți numai acei indecși care acceptă multe interogări și returnează un procent foarte mic de rânduri de tabel.

7. Creați indecși compuși pentru a accepta mai multe interogări.

În cazurile în care tabelele dvs. efectuează în principal ACTUALIZĂRI, INSERE și citiri, reducerea numărului de indici va reduce costul general de întreținere a indexului. O operație INSERT face ca SQL Server să adauge noi înregistrări la index, iar o operațiune UPDATE poate determina mutarea unui rând într-o nouă locație din index sau chiar într-o nouă pagină din tabel. Mai mult decât atât, SQL Server efectuează adesea o operație de ștergere ca o secvență de două operații: mai întâi, rândul vechi este șters, iar apoi este inserat unul nou. În ceea ce privește cheltuielile generale de gestionare a indexului, aceasta este cea mai proastă opțiune. Soluția pentru această situație este de a crea indecși compoziți pe care SQL Server să îi poată folosi pentru o varietate de interogări.

8. Indexați coloanele unite.

Când uniți două tabele, SQL Server caută în tabelul interior toate rândurile ale căror valori satisfac o condiție calculată pe baza valorii curente din tabelul exterior. Și SQL Server repetă această căutare pentru fiecare rând din tabelul extern. Dacă există un index, atunci SQL Server poate selecta mai întâi numai acele rânduri care îndeplinesc condiția de alăturare. Când dimensiunea tabelului intern este de câteva ori mai mare decât dimensiunea tabelului extern, câștigul în timpul de execuție al unirii poate fi de câteva ordine de mărime. (Pentru mai multe informații despre îmbinările tabelelor, consultați articolul „Advanced JOIN Techniques” de Itzik Ben-Gan și Kaylen Delaney, publicat în numărul din decembrie 1999 al revistei.) Ce index să alegeți - grupat sau negrupat - depinde în principal dacă alte coloane sunt prezente în lista SELECT. Dacă lista conține numai acele coloane care sunt unite, cel mai bine este să utilizați un index necluster.

9. Profită de acoperirea indicilor.

Prin definiție, un index de acoperire este un index care conține toate coloanele la care se face referire într-o instrucțiune SELECT, UPDATE sau DELETE. Solicitarea se numește apoi cerere acoperită. Deoarece un index non-cluster conține o intrare la nivel de frunză pentru fiecare rând din tabel, toate informațiile necesare pentru a rula interogarea se află în index. Din acest motiv, procesorul de interogări nu poate scana un tabel uriaș, ci doar un index mic. În general, dacă puteți construi un index de acoperire, veți experimenta imediat o îmbunătățire semnificativă a performanței interogărilor. Acest lucru se datorează faptului că indexul nu conține întregul rând al tabelului, ci doar un subset al acestuia. Cu toate acestea, reversul monedei este că adăugarea de coloane suplimentare la index are ca rezultat mai puține înregistrări care se potrivesc pe pagina de index. Aceasta, la rândul său, determină creșterea spațiului de index și creșterea numărului de operațiuni I/O necesare pentru a citi indexul în cache. Construcția indicilor de acoperire este justificată atâta timp cât lungimea totală a tuturor coloanelor incluse în index rămâne semnificativ mai mică decât lungimea rândului tabelului.

Maurice Lewis este președintele Holitech, o companie specializată în consultanță și instruire în tehnologiile Internet și dezvoltarea bazelor de date Microsoft.