Reglarea performanței bazei de date SQL pentru dezvoltatori

Reglarea performanței SQL poate fi o sarcină incredibil de dificilă, în special atunci când lucrați cu date la scară mare, unde chiar și cea mai mică modificare poate avea un impact dramatic (pozitiv sau negativ) asupra performanței.

În companiile mijlocii și mari, cea mai mare parte a reglajului performanței SQL va fi gestionată de un administrator de baze de date (DBA). Dar credeți-mă, există o mulțime de dezvoltatori care trebuie să îndeplinească sarcini asemănătoare DBA. În plus, în multe dintre companiile pe care le-am văzut care au DBA, deseori se luptă să lucreze bine cu dezvoltatorii – pozițiile necesită pur și simplu moduri diferite de rezolvare a problemelor, ceea ce poate duce la dezacorduri între colegi.

În plus, structura corporativă poate juca un rol. Să presupunem că echipa DBA este plasată la etajul 10 cu toate bazele de date, în timp ce dezvoltatorii se află la etajul 15, sau chiar într-o clădire diferită, sub o structură de raportare complet separată - este cu siguranță greu să lucrați împreună fără probleme în aceste condiții.

În acest articol, aș dori să realizez două lucruri:

1. Oferiți dezvoltatorilor câteva tehnici de reglare a performanței SQL pentru dezvoltatori.
2. Explicați modul în care dezvoltatorii și DBA pot lucra împreună eficient.

Ajustarea performanței SQL (în baza de cod): indexuri

Dacă sunteți complet nou venit în bazele de date și chiar vă întrebați „Ce este reglarea performanței SQL?”, ar trebui să știți că indexarea este o modalitate eficientă de reglare a bazei de date SQL, care este adesea neglijată în timpul dezvoltării. În termeni de bază, un index este o structură de date care îmbunătățește viteza operațiunilor de regăsire a datelor pe un tabel de bază de date, oferind căutări aleatorii rapide și acces eficient la înregistrările ordonate. Aceasta înseamnă că, odată ce ați creat un index, puteți selecta sau sorta rândurile mai repede decât înainte.

Indecșii sunt, de asemenea, folosiți pentru a defini o cheie primară sau un index unic care va garanta că nicio altă coloană nu are aceleași valori. Desigur, indexarea bazelor de date este un subiect vast și interesant căruia nu-i pot face dreptate cu această scurtă descriere (dar iată o redacție mai detaliată).

Dacă sunteți nou în indexuri, vă recomand să utilizați această diagramă atunci când vă structurați interogările:

Practic, scopul este de a indexa principalele coloane de căutare și ordonare.

Rețineți că, dacă tabelele sunt ciocănite în mod constant de INSERT , UPDATE și DELETE , ar trebui să fiți atenți atunci când indexați - puteți ajunge să scădeți performanța deoarece toți indecșii trebuie modificați după aceste operațiuni.

În plus, DBA-urile își abandonează adesea indecșii SQL înainte de a efectua inserări în lot de peste milioane de rânduri pentru a accelera procesul de inserare. După ce lotul este inserat, ei recreează apoi indecșii. Amintiți-vă, totuși, că eliminarea indecșilor va afecta fiecare interogare care rulează în acel tabel; deci această abordare este recomandată numai atunci când lucrați cu o singură inserție mare.

SQL Tuning: Planuri de execuție în SQL Server

Apropo: instrumentul Plan de execuție din SQL Server poate fi util pentru crearea de indexuri.

Funcția sa principală este de a afișa grafic metodele de recuperare a datelor alese de optimizatorul de interogări SQL Server. Dacă nu le-ați mai văzut până acum, există o prezentare detaliată.

Pentru a prelua planul de execuție (în SQL Server Management Studio), faceți clic pe „Includeți planul de execuție real” (CTRL + M) înainte de a rula interogarea.

După aceea, va apărea o a treia filă numită „Plan de execuție”. Este posibil să vedeți un index detectat lipsă. Pentru a-l crea, faceți clic dreapta în planul de execuție și alegeți „Detaliile index lipsă...”. Este la fel de simplu!

( Clic pentru a mări )

Reglare SQL: evitați buclele de codare

Imaginați-vă un scenariu în care 1000 de interogări ciocănesc baza de date în succesiune. Ceva asemănător cu:

 for (int i = 0; i < 1000; i++) { SqlCommand cmd = new SqlCommand("INSERT INTO TBL (A,B,C) VALUES..."); cmd.ExecuteNonQuery(); }

Ar trebui să evitați astfel de bucle în codul dvs. De exemplu, am putea transforma fragmentul de mai sus utilizând o instrucțiune unică INSERT sau UPDATE cu mai multe rânduri și valori:

 INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)

Asigurați-vă că clauza dvs. WHERE evită actualizarea valorii stocate dacă se potrivește cu valoarea existentă. O astfel de optimizare banală poate crește dramatic performanța interogărilor SQL prin actualizarea doar a sute de rânduri în loc de mii. De exemplu:

 UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A <> @VALUE -- VALIDATION

Reglare SQL: Evitați subinterogările SQL corelate

O subinterogare corelată este una care utilizează valori din interogarea părinte. Acest tip de interogare SQL tinde să ruleze rând cu rând, o dată pentru fiecare rând returnat de interogarea exterioară, și astfel scade performanța interogării SQL. Noii dezvoltatori SQL sunt adesea prinși structurându-și interogările în acest fel - pentru că de obicei este calea ușoară.

Iată un exemplu de subinterogare corelată:

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

În special, problema este că interogarea interioară ( SELECT CompanyName… ) este rulată pentru fiecare rând returnat de interogarea exterioară ( SELECT c.Name… ). Dar de ce să trecem peste Company din nou și din nou pentru fiecare rând procesat de interogarea externă?

O tehnică mai eficientă de reglare a performanței SQL ar fi refactorizarea subinterogării corelate ca unire:

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

În acest caz, trecem peste tabelul Company o singură dată, la început, și Îl ALĂMĂM cu tabelul Customer . De atunci, putem selecta mai eficient valorile de care avem nevoie ( co.CompanyName ).

Ajustare SQL: Selectați cu moderație

Unul dintre sfaturile mele preferate de optimizare SQL este de a evita SELECT * ! În schimb, ar trebui să includeți individual coloanele specifice de care aveți nevoie. Din nou, acest lucru sună simplu, dar văd această eroare peste tot. Luați în considerare un tabel cu sute de coloane și milioane de rânduri - dacă aplicația dvs. are nevoie doar de câteva coloane, nu are sens să interogați toate datele. Este o risipă masivă de resurse. ( Pentru mai multe probleme, vezi aici. )

De exemplu:

 SELECT * FROM Employees

vs.

 SELECT FirstName, City, Country FROM Employees

Dacă într-adevăr aveți nevoie de fiecare coloană, enumerați în mod explicit fiecare coloană. Aceasta nu este atât de mult o regulă, ci mai degrabă un mijloc de a preveni viitoarele erori de sistem și de reglare suplimentară a performanței SQL. De exemplu, dacă utilizați un INSERT... SELECT... și tabelul sursă s-a schimbat prin adăugarea unei noi coloane, este posibil să întâmpinați probleme, chiar dacă acea coloană nu este necesară pentru tabelul de destinație, de exemplu:

 INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

Pentru a evita acest tip de eroare de la SQL Server, ar trebui să declarați fiecare coloană individual:

 INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees

Rețineți, totuși, că există unele situații în care utilizarea SELECT * ar putea fi adecvată. De exemplu, cu tabele temporare, ceea ce ne conduce la următorul nostru subiect.

Ajustare SQL: Utilizarea înțeleaptă a tabelelor temporare (#Temp)

Tabelele temporare cresc de obicei complexitatea unei interogări. Dacă codul dvs. poate fi scris într-un mod simplu, direct, aș sugera să evitați tabelele temporare.

Dar dacă aveți o procedură stocată cu o anumită manipulare a datelor care nu poate fi gestionată cu o singură interogare, puteți utiliza tabele temporare ca intermediari pentru a vă ajuta să generați un rezultat final.

Când trebuie să vă alăturați unui tabel mare și există condiții pe respectivul tabel, puteți crește performanța bazei de date prin transferul datelor într-un tabel temporar și apoi făcând o alăturare pe acesta . Tabelul tău temporar va avea mai puține rânduri decât tabelul inițial (mare), astfel încât îmbinarea se va termina mai repede!

Decizia nu este întotdeauna simplă, dar acest exemplu vă va oferi o idee pentru situațiile în care ați putea dori să utilizați tabele temporare:

Imaginați-vă o masă de clienți cu milioane de înregistrări. Trebuie să faceți o alăturare într-o anumită regiune. Puteți realiza acest lucru folosind o SELECT INTO și apoi unindu-vă cu tabelul temp:

 SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID

( Notă: unii dezvoltatori SQL evită, de asemenea, să folosească SELECT INTO pentru a crea tabele temporare, spunând că această comandă blochează baza de date tempdb, interzicând altor utilizatori să creeze tabele temporare. Din fericire, acest lucru este remediat în 7.0 și mai târziu .)

Ca alternativă la tabelele temporare, ați putea lua în considerare utilizarea unei subinterogări ca tabel:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Dar asteapta! Există o problemă cu această a doua interogare. După cum este descris mai sus, ar trebui să includem doar coloanele de care avem nevoie în subinterogarea noastră (adică, nu folosim SELECT * ). Ținând cont de asta:

 SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID

Toate aceste fragmente SQL vor returna aceleași date. Dar cu tabelele temporare, am putea, de exemplu, să creăm un index în tabelul temporar pentru a îmbunătăți performanța. Există o discuție bună aici despre diferențele dintre tabelele temporare și subinterogări.

În cele din urmă, când ați terminat cu tabelul temporar, ștergeți-l pentru a șterge resursele tempdb, mai degrabă decât să așteptați ca acesta să fie șters automat (așa cum va fi atunci când conexiunea la baza de date este terminată):

 DROP TABLE #temp

Reglare SQL: „Există înregistrarea mea?”

Această tehnică de optimizare SQL se referă la utilizarea EXISTS() . Dacă doriți să verificați dacă există o înregistrare, utilizați EXISTS() în loc de COUNT() . În timp ce COUNT() scanează întregul tabel, numărând toate intrările care se potrivesc cu condiția dvs., EXISTS() se va închide imediat ce vede rezultatul de care are nevoie. Acest lucru vă va oferi performanțe mai bune și cod mai clar.

 IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'

vs.

 IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'

Reglarea performanței SQL cu SQL Server 2016

După cum probabil că DBA care lucrează cu SQL Server 2016 știu, versiunea a marcat o schimbare importantă în gestionarea setărilor implicite și a compatibilității. Ca versiune majoră, desigur, vine cu noi optimizări ale interogărilor, dar controlul asupra utilizării acestora este acum simplificat prin sys.databases.compatibility_level .

Ajustarea performanței SQL (în Office)

Administratorii de baze de date SQL (DBA) și dezvoltatorii se confruntă adesea cu probleme legate de date și care nu sunt legate de acestea. Din experiența mea, iată câteva sfaturi (pentru ambele părți) despre cum să vă înțelegeți și să lucrați împreună eficient.

Tweet

Optimizarea bazei de date pentru dezvoltatori:

1. Dacă aplicația dvs. încetează să funcționeze brusc, este posibil să nu fie o problemă cu baza de date. De exemplu, poate aveți o problemă de rețea. Investigați puțin înainte de a acuza un DBA!

2. Chiar dacă sunteți un modelator de date SQL ninja, cereți un DBA să vă ajute cu diagrama relațională. Au multe de împărtășit și de oferit.

3. DBA nu le plac schimbările rapide. Acest lucru este firesc: trebuie să analizeze baza de date în ansamblu și să examineze impactul oricăror modificări din toate unghiurile. O simplă modificare într-o coloană poate dura o săptămână pentru a fi implementată, dar asta pentru că o eroare s-ar putea concretiza ca pierderi uriașe pentru companie. Fii răbdător!

4. Nu cereți DBA SQL să facă modificări de date într-un mediu de producție. Dacă doriți acces la baza de date de producție, trebuie să fiți responsabil pentru toate modificările dvs.

Optimizarea bazei de date pentru DBA SQL Server:

1. Dacă nu vă place ca oamenii să vă întrebe despre baza de date, oferiți-le un panou de stare în timp real. Dezvoltatorii sunt întotdeauna suspicioși cu privire la starea unei baze de date, iar un astfel de panou ar putea economisi timp și energie tuturor.

2. Ajută dezvoltatorii într-un mediu de testare/asigurare a calității. Ușurează simularea unui server de producție cu teste simple pe date din lumea reală. Acest lucru va economisi timp semnificativ pentru alții, precum și pentru tine.

3. Dezvoltatorii petrec toată ziua pe sisteme cu logica de afaceri schimbată frecvent. Încercați să înțelegeți că această lume este mai flexibilă și să puteți încălca unele reguli într-un moment critic.

4. Bazele de date SQL evoluează. Va veni ziua în care va trebui să-ți migrați datele la o nouă versiune. Dezvoltatorii se bazează pe noi funcționalități semnificative cu fiecare versiune nouă. În loc să refuzați să acceptați modificările lor, planificați din timp și fiți pregătiți pentru migrare.