Po testiranju je bila izvedena pogojna poizvedba na tabeli, ki je vsebovala več kot 4 milijone zapisov, čas poizvedbe pa je bil do 40 sekund. Zato je zelo pomembno, kako izboljšati učinkovitost poizvedb po SQL izjavah. Spodaj je več metod optimizacije poizvedbenih stavkov, ki so široko razširjene na internetu:
Najprej, ko je podatkovni volumen velik, se izogibajte pregledovanju celotne tabele in razmislite o ustvarjanju indeksov na stolpce, ki so vključeni v kje, in po vrstnem redu, kar lahko močno pospeši pridobivanje podatkov. Vendar pa obstajajo situacije, kjer indeksiranje ne deluje:
1. Poskusite se izogibati uporabi != ali <> operatorjev v klavzuli kje, sicer bo pogon opustil uporabo indeksov in izvedel popolno skeniranje tabel.
2. Poskušajte se izogniti ničelni vrednostni presoji na poljih v klavzuli kje, sicer bo pogon opustil uporabo indeksov in izvedel popolno skeniranje tabel, na primer:
Izberite ID iz T, kjer je num ničelna
Lahko nastavite privzeto vrednost 0 na num, preverite, da v stolpcu num v tabeli ni ničelne vrednosti, nato pa poizvedujete takole:
Izberi ID iz T, kjer je num=0
3. Poskusite se izogibati uporabi OR v klavzuli where za spajanje, sicer bo pogon opustil uporabo indeksa in izvedel popoln pregled tabele, na primer:
Izberite ID iz T, kjer je num=10 ali num=20
Lahko povprašate takole:
Izberi ID iz T, kjer je nuM=10
Združitev vseh
Izberi ID iz T, kjer je nuM=20
4. Naslednja poizvedba bo prav tako privedla do popolnega pregleda tabele:
Izberite id iz T, kjer je ime kot '%abc%'
Za večjo učinkovitost razmislite o iskanju po celotnem besedilu.
5. In in not in je treba uporabljati previdno, sicer bo to povzročilo popolno skeniranje tabele, kot je:
Izberi ID iz T, kjer Num in(1,2,3)
Za zvezne vrednosti, če lahko uporabite med, ne uporabljajte v:
Izberite ID iz T, kjer je num med 1 in 3
6. Če uporabite parameter v klavzuli kje, bo to povzročilo tudi pregled celotne tabele. Ker SQL med izvajanjem rešuje le lokalne spremenljivke, optimizator pa ne more prestaviti izbire načrtov dostopa na izvajanje časa; Izbrati ga je treba ob prevajanju. Če pa je načrt dostopa vzpostavljen ob prevajanju, je vrednost spremenljivke še vedno neznana in je zato ni mogoče uporabiti kot vhodni element za izbiro indeksa. Naslednje izjave bodo skenirane v celoti:
Izberi ID iz T, kjer je NuM=@num
Poizvedbo lahko prisilite, da namesto tega uporabi indeks:
Izberite ID iz t z(index(index(index name)), kjer num=@num
7. Poskušajte se izogniti izražanju polj v klavzuli kje, kar bo povzročilo, da pogon opusti uporabo indeksa in izvede popolno pregledovanje tabele. Na primer:
Izberi ID iz T, kjer num/2=100
naj se spremeni v:
Izberi ID iz T, kjer je num=100*2
8. Poskušajte se izogibati izvajanju funkcijskih operacij na poljih v klavzuli kje, kar bo povzročilo, da pogon opusti uporabo indeksov in izvede popolno pregledovanje tabele. Na primer:
Izberite ID iz T, kjer je podstring(ime,1,3)='ABC' – ID imena, ki se začne z ABC
Izberite ID iz T, kjer je datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ generirani ID
naj se spremeni v:
Izberi ID iz T, kjer je ime kot 'abc%'
Izberite ID iz T, kjer je ustvarjeno>='2005-11-30′ in ustvarjeno<'2005-12-1′
9. Ne izvajajte funkcij, aritmetičnih operacij ali drugih izraznih operacij levo od "=" v klavzuli kjer, sicer sistem morda ne bo mogel pravilno uporabiti indeksa.
10. Pri uporabi indeksnega polja kot pogoja, če je indeks sestavljen, mora biti prvo polje v indeksu uporabljeno kot pogoj, da sistem uporablja indeks, sicer indeks ne bo uporabljen, in vrstni red polj mora biti čim bolj skladen z vrstnim redom indeksov.
11. Ne pišite nesmiselnih poizvedb, kot je generiranje strukture prazne tabele:
Izberi stolpec 1,stolpec 2 v #t iz T, kjer je 1=0
Ta vrsta kode ne vrača nobenega nabora rezultatov, vendar porablja sistemske vire, zato jo je treba spremeniti v nekaj takega:
Ustvari tabelo #t(...)
12. Velikokrat je dobra izbira uporabiti obstaja namesto v:
Izberi num iz A, kjer num v (izberi num iz b)
Zamenjajte z naslednjo izjavo:
Izberi num iz A, kjer obstaja (izberi 1 iz b, kjer num=a.num)
Stvari, na katere je treba biti pozoren pri sestavljanju indeksa:
1. Niso vsi indeksi veljavni za poizvedbe, SQL temelji na podatkih v tabeli za optimizacijo poizvedbe; ko ima stolpec indeksa veliko podvajanja podatkov, SQL poizvedbe morda ne uporabljajo indeksa, na primer tabela ima polja spol, moški, ženska skoraj po polovici vsakega, tudi če je indeks zgrajen na podlagi spola, to ne bo vplivalo na učinkovitost poizvedb.
2. Več kot je indeksov, tem bolje, indeks lahko zagotovo izboljša učinkovitost ustreznega selekcije, vendar tudi zmanjša učinkovitost vstavljanja in posodabljanja, saj se indeks lahko ob vstavljanju ali posodabljanju ponovno zgradi, zato je treba gradnjo indeksa skrbno premisliti, odvisno od specifične situacije. Najbolje je, da v tabeli ni več kot 6 indeksov, in če jih je preveč, razmislite, ali je potrebno indekse graditi na redko uporabljenih stolpcih.
3. Izogibajte se posodabljanju stolpcov podatkov v gručenih indeksih čim več, saj je vrstni red gručenih indeksiranih podatkovnih stolpcev fizični vrstni red tabel, in ko se vrednost stolpca spremeni, bo to povzročilo prilagoditev vrstnega reda celotnih zapisov tabel, kar bo zahtevalo znatne vire. Če mora aplikacijski sistem pogosto posodabljati stolpce združenega indeksa, mora upoštevati, ali naj bo indeks zgrajen kot gručen indeks.
Druge točke, ki jih je treba omeniti:
1. Poskušajte uporabljati številčna polja in ne oblikovati polj, ki vsebujejo le številčne informacije kot znake, kar bo zmanjšalo zmogljivost poizvedb in povezav ter povečalo stroške shranjevanja. To je zato, ker pogon primerja vsak znak v nizu enega za drugim pri obdelavi poizvedb in združevanj, medtem ko je za numerične tipe potrebno primerjati le enkrat.
2. Nikjer ne uporabljajte izbire * iz t, zamenjajte "*" s specifičnim seznamom polj in ne vračajte nobenih polj, ki niso uporabljena.
3. Poskusite uporabljati spremenljivke tabel namesto začasnih tabel. Če tabelična spremenljivka vsebuje veliko podatkov, upoštevajte, da je indeks zelo omejen (le indeks primarnega ključa).
4. Izogibajte se pogostemu ustvarjanju in brisanju začasnih tabel, da zmanjšate porabo virov sistemskih tabel.
5. Začasne tabele niso neuporabne, njihova ustrezna uporaba pa lahko določene rutine naredi bolj učinkovite, na primer, ko morate večkrat referencirati veliko tabelo ali podatkovni nabor v pogosto uporabljeni tabeli. Vendar pa je za enkratne dogodke najbolje uporabiti izvozno tabelo.
6. Pri ustvarjanju začasne tabele, če je količina podatkov vstavljenih naenkrat velika, lahko namesto ustvarjanja tabele uporabite select in, da se izognete povzročanju večjega števila dnevnikov; Če količina podatkov ni velika, bi morali najprej ustvariti tabelo in jo nato vstaviti, da bi olajšali vire sistemske tabele.
7. Če se uporablja začasna tabela, poskrbite, da boste na koncu shranjenega postopka eksplicitno izbrisali vse začasne tabele, najprej skrajšali tabelo in nato opustili tabelo, da se izognete dolgotrajnemu zaklepanju sistemske tabele.
8. Poskusite se izogibati uporabi kazaleca, ker je njegova učinkovitost slaba; če podatki, ki jih kazalec obdeluje, presegajo 10.000 vrstic, potem razmislite o prepisovanju.
9. Pred uporabo metode s kazalcem ali metode začasne tabele najprej poiščite rešitve na osnovi množic za reševanje problema, pri čemer je metoda na osnovi množic običajno učinkovitejša.
10. Tako kot pri začasnih tabelah kazalec ni neuporaben. Uporaba FAST_FORWARD kazalca za majhne podatkovne nize je pogosto boljša kot druge metode obdelave vrstica za vrstico, še posebej, če morate za pridobitev potrebnih podatkov uporabiti več tabel. Rutine, ki vključujejo "total" v naboru rezultatov, so običajno hitrejše od tistih, ki se izvajajo s kazalcem. Če razvojni čas dopušča, lahko preizkusimo tako metode, ki temeljijo na kazalcu kot na množicah, da ugotovimo, katera deluje bolje.
11. Nastavi NASTAVI NOCOUNT ON na začetku vseh shranjenih procedur in sprožilcev ter nastavi NASTAVI NOCOUNT OFF na koncu. Po izvajanju vsakega izreka shranjene procedure in sprožilca ni treba pošiljati DONE_IN_PROC sporočil.
12. Poskušajte se izogibati vračanju velikih podatkov odjemalcu; če je podatkovni volumen prevelik, morate upoštevati, ali je ustrezna povpraševanja razumna.
13. Poskušajte se izogniti velikim transakcijam in izboljšati sposobnost sočasnosti sistema.
|
Objavljeno na 11. 05. 2018 13:57:06
|
|
|
Objavljeno na 17. 05. 2018 10:12:27