După testare, se efectua o interogare condiționată pe un tabel care conținea peste 4 milioane de înregistrări, iar timpul de interogare era de până la 40 de secunde. Prin urmare, modul de a îmbunătăți eficiența interogării instrucțiunilor SQL este foarte important. Următoarele sunt câteva metode de optimizare a instrucțiunilor de interogare care sunt larg răspândite pe Internet:
În primul rând, când volumul de date este mare, ar trebui să încerci să eviți scanarea tabelului complet și să iei în considerare construirea unor indici pe coloanele implicate în unde și ordine, ceea ce poate accelera considerabil recuperarea datelor. Totuși, există unele situații în care indexarea nu funcționează:
1. Încercați să evitați folosirea operatorilor != sau <> în clauza where, altfel motorul va abandona utilizarea indexurilor și va efectua scanarea completă a tabelelor.
2. Încercați să evitați judecata de valoare nulă asupra câmpurilor din clauza where, altfel motorul va abandona utilizarea indicilor și va efectua scanarea completă a tabelelor, cum ar fi:
Selectează ID din T, unde num este nul
Poți seta valoarea implicită de 0 pe num, să te asiguri că nu există nicio valoare nulă în coloana num din tabel și apoi să interogezi astfel:
selectează ID din t, unde num=0
3. Încercați să evitați folosirea OR în condițiile clauzei where to join, altfel motorul va renunța la utilizarea indexului și va efectua o scanare completă a tabelului, cum ar fi:
selectează ID din t, unde num=10 sau num=20
Poți interoga astfel:
selectează ID din t, unde num=10
Uniunea tuturor
selectează ID din t unde num=20
4. Următoarea interogare va duce, de asemenea, la o scanare completă a tabelului:
Selectează ID de la T, unde numele este '%abc%'
Pentru a îmbunătăți eficiența, luați în considerare căutarea în text integral.
5. In și not in ar trebui folosite cu prudență, altfel va duce la scanarea completă a tabelului, cum ar fi:
Selectează ID din t unde num în(1,2,3)
Pentru valori continue, dacă poți folosi între ele, nu folosi în:
selectează ID de la t unde num între 1 și 3
6. Dacă folosești parametrul din clauza where, va face ca tabelul complet să fie scanat. Pentru că SQL rezolvă doar variabilele locale la rulare, dar optimizatorul nu poate amâna selecția planurilor de acces către timpul de execuție; Trebuie selectat în timpul compilației. Totuși, dacă un plan de acces este stabilit la compilare, valoarea variabilei rămâne necunoscută și, prin urmare, nu poate fi folosită ca element de intrare pentru selecția indicelui. Următoarele declarații vor fi scanate integral:
selectează ID de la t, unde num=@num
Poți forța interogarea să folosească un index în schimb:
selectează ID din t cu(index(index(index name)) unde num=@num
7. Încercați să evitați exprimarea câmpurilor în clauza where, ceea ce va determina motorul să abandoneze utilizarea indexului și să efectueze scanarea completă a tabelului. De exemplu:
selectează ID de la t, unde num/2=100
Ar trebui să fie schimbat în:
selectează ID din t unde num=100*2
8. Încearcă să eviți efectuarea operațiunilor de funcție pe câmpurile din clauza where, ceea ce va determina motorul să renunțe la utilizarea indexurilor și să efectueze scanarea completă a tabelului. De exemplu:
Selectează ID-ul din T, unde substring(name,1,3)='abc' – ID nume care începe cu abc
Selectează ID-ul de la T, unde datediff(Day,createdate,'2005-11-30′)=0–'2005-11-30′ a generat ID-ul
Ar trebui să fie schimbat în:
Selectează ID-ul de la T unde numele este "ABC%"
selectează ID-ul de la T unde se creează>='2005-11-30′ și se creează<'2005-12-1′
9. Nu efectuați funcții, operații aritmetice sau alte operații de expresie în stânga "=" din clauza where, altfel sistemul s-ar putea să nu poată folosi corect indexul.
10. Când se folosește un câmp index ca condiție, dacă indicele este un indice compozit, atunci primul câmp din index trebuie folosit ca condiție pentru a asigura că sistemul folosește indicele, altfel indexul nu va fi folosit, iar ordinea câmpurilor ar trebui să fie cât mai consistentă cu ordinea indicilor.
11. Nu scrie unele interogări fără sens, cum ar fi generarea unei structuri de tabel goale:
Selectați col1,col2 în #t de la t unde 1=0
Acest tip de cod nu returnează niciun set de rezultate, dar consumă resurse de sistem, deci ar trebui schimbat în ceva de genul:
creează tabel #t(...)
12. De multe ori este o alegere bună să folosești exists în loc de în:
selectează num din a unde num în (selectează num din b)
Înlocuiește cu următoarea afirmație:
Selectează num din a unde există (selectează 1 din b unde num=a.num)
Lucruri la care trebuie să fii atent atunci când construiești un indice:
1. Nu toate indexurile sunt valide pentru interogări, SQL se bazează pe datele din tabel pentru a optimiza interogarea, iar când coloana indexului are o cantitate mare de duplicaturi de date, interogările SQL pot să nu folosească indexul, de exemplu un tabel are câmpuri sex, masculin, feminin aproape jumătate fiecare, iar chiar dacă indexul este construit pe sex, acesta nu va juca un rol în eficiența interogării.
2. Cu cât nu sunt mai mulți indici, cu atât mai bine, indicele poate îmbunătăți cu siguranță eficiența selectării corespunzătoare, dar reduce și eficiența inserării și actualizării, deoarece indexul poate fi reconstruit la inserarea sau actualizarea, astfel că modul de construire a unui index trebuie analizat cu atenție, în funcție de situația specifică. Este mai bine să nu ai mai mult de 6 indici într-un tabel și, dacă sunt prea mulți, să te gândești dacă este necesar să construiești indici pe unele coloane folosite rar.
3. Evitați actualizarea coloanelor de date indexate clusterizate cât mai mult posibil, deoarece ordinea coloanelor indexate clusterizate este ordinea fizică a stocării înregistrărilor tabelului, iar odată ce valoarea coloanei se schimbă, va duce la ajustarea ordinii întregii înregistrări de tabel, ceea ce va consuma resurse considerabile. Dacă sistemul de aplicații trebuie să actualizeze frecvent coloanele de index clusterizate, trebuie să ia în considerare dacă indexul ar trebui construit ca un index clusterizat.
Alte puncte de remarcat:
1. Încearcă să folosești câmpuri numerice și să nu proiectezi câmpuri care conțin doar informații numerice ca caractere, ceea ce va reduce performanța interogărilor și conexiunilor și va crește overhead de stocare. Acest lucru se datorează faptului că motorul compară fiecare caracter din șir, unul câte unul, când procesează interogările și join-urile, în timp ce pentru tipurile numerice trebuie comparat o singură dată.
2. Nu folosiți select * din t nicăieri, înlocuiți "*" cu o listă specifică de câmpuri și nu returnați niciun câmp care nu este folosit.
3. Încearcă să folosești variabile de tabel în loc de tabele temporare. Dacă variabila tabelului conține o cantitate mare de date, rețineți că indexul este foarte limitat (doar indexul cheii primare).
4. Evitați crearea și ștergerea frecventă a tabelelor temporare pentru a reduce consumul resurselor de tabele de sistem.
5. Tabelele temporare nu sunt inutilizabile, iar utilizarea lor corespunzătoare poate face anumite rutine mai eficiente, de exemplu, atunci când trebuie să consultați în mod repetat un tabel mare sau un set de date într-un tabel folosit frecvent. Totuși, pentru evenimente unice, cel mai bine este să folosești un tabel de export.
6. Când creezi un tabel temporar, dacă cantitatea de date introduse simultan este mare, poți folosi select into în loc de create table pentru a evita creșterea vitezei unui număr mare de loguri; Dacă cantitatea de date nu este mare, pentru a ușura resursele tabelului de sistem, ar trebui să creezi mai întâi tabelul și apoi să introduci.
7. Dacă se folosește un tabel temporar, asigurați-vă că ștergeți explicit toate tabelele temporare la sfârșitul procedurii stocate, truncați tabelul, apoi eliminați tabelul, pentru a evita blocarea lungă a tabelului sistemului.
8. Încearcă să eviți folosirea cursorului, deoarece eficiența cursorului este slabă; dacă datele operate de cursor depășesc 10.000 de linii, ar trebui să iei în considerare rescrierea.
9. Înainte de a folosi metoda bazată pe cursor sau metoda tabelului temporar, ar trebui mai întâi să căutați soluții bazate pe mulțimi pentru a rezolva problema, iar metoda bazată pe mulțimi este de obicei mai eficientă.
10. La fel ca tabelele temporare, cursorul nu este inutilizabil. Folosirea cursoarelor FAST_FORWARD pentru seturi mici de date este adesea mai bună decât alte metode de procesare rând cu rând, mai ales dacă trebuie să consulți mai multe tabele pentru a obține datele de care ai nevoie. Rutinele care includ "total" în setul de rezultate sunt de obicei mai rapide decât cele executate cu cursorul. Dacă timpul de dezvoltare permite, pot fi încercate atât metode bazate pe cursor, cât și pe seturi pentru a vedea care funcționează mai bine.
11. Setează SET NOCOUNT ON la începutul tuturor procedurilor și trigger-urilor stocate și setează SET NOCOUNT OFF la final. Nu este nevoie să se trimită DONE_IN_PROC mesaje clientului după executarea fiecărei instrucțiuni a procedurii stocate și a declanșării.
12. Încercați să evitați să returnați date mari către client; dacă volumul de date este prea mare, ar trebui să luați în considerare dacă cererea corespunzătoare este rezonabilă.
13. Încercați să evitați operațiunile mari de tranzacții și să îmbunătățiți capacitatea de concurență a sistemului.
|
Postat pe 11.05.2018 13:57:06
|
|
|
Postat pe 17.05.2018 10:12:27