Pärast testimist viidi läbi tingimuslik päring tabelil, mis sisaldas üle 4 miljoni kirje, ning päringu kestus ulatus kuni 40 sekundini. Seetõttu on väga oluline, kuidas parandada SQL-lause päringute efektiivsust. Järgnevalt on toodud mitmed päringulausete optimeerimise meetodid, mis on internetis laialdaselt levinud:
Esiteks, kui andmemaht on suur, tuleks vältida kogu tabeli skaneerimist ning kaaluda indeksite loomist veergudele, kus ja järjekorras, mis võib andmete leidmist oluliselt kiirendada. Siiski on mõned olukorrad, kus indekseerimine ei tööta:
1. Püüa vältida != või <> operaatorite kasutamist where-klauslis, vastasel juhul loobub mootor indeksite kasutamisest ja teostab täieliku tabeli skaneerimise.
2. Püüa vältida nullväärtuse hindamist väljadele kus-klauslis, vastasel juhul loobub mootor indeksite kasutamisest ja teostab täieliku tabeli skaneerimise, näiteks:
Vali ID T-st, kus num on null
Võid määrata num-i vaikimisi väärtuseks 0, veenduda, et num-veerus pole nullväärtust tabelis, ja seejärel pärida nii:
Vali id t-st, kus num=0
3. Püüa vältida OR kasutamist where-klauslis tingimuste liitmiseks, vastasel juhul loobub mootor indeksi kasutamisest ja teeb täieliku tabeli skaneerimise, näiteks:
Vali id t-st, kus num=10 või num=20
Võite päringuid esitada nii:
Vali ID t-st, kus num=10
Liit kõik
Vali id t-st, kus num=20
4. Järgmine päring toob kaasa ka täieliku tabeli skaneerimise:
Vali ID T-st, kus nimi on näiteks '%ABC%'
Tõhususe parandamiseks kaalu täisteksti otsingut.
5. Sisse ja mitte sisse tuleks kasutada ka ettevaatlikult, vastasel juhul võib see viia täieliku tabeli skaneerimiseni, näiteks:
Vali ID T-st, kus num in(1,2,3)
Pidevate väärtuste puhul, kui saad kasutada between'i, ära kasuta in:
Vali ID T-st, kus number jääb vahemikku 1 kuni 3
6. Kui kasutad parameetrit kus klauslis, skaneeritakse ka kogu tabel. Kuna SQL lahendab ainult lokaalseid muutujaid käitusajal, kuid optimeerija ei saa juurdepääsuplaanide valikut edasi lükata käitusajale; See tuleb valida kompileerimise ajal. Kui aga kompileerimise ajal luuakse ligipääsuplaan, on muutuja väärtus endiselt teadmata ja seetõttu ei saa seda kasutada indeksi valiku sisendina. Järgmised avaldused skaneeritakse täielikult:
Vali id t-st, kus num=@num
Saad sundida päringut kasutama indeksit:
Vali id t-st (indeksi(indeks(indeksi nimi)), kus num=@num
7. Püüa vältida välju väljendamist kus klauslis, mis põhjustab mootori loobuma indeksi kasutamisest ja täieliku tabeli skaneerimise. Näiteks:
Vali ID T-st, kus num/2=100
tuleks muuta järgmiselt:
Vali id t-st, kus num=100*2
8. Püüa vältida funktsioonioperatsioonide tegemist väljadele where-klauslis, mis põhjustab mootori indeksite kasutamise lõpetamise ja täieliku tabeli skaneerimise. Näiteks:
Vali id t-st, kus substring(name,1,3)='abc' – nime ID, mis algab abc-ga
Vali ID hulgast T, kus datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ genereeritud ID
tuleks muuta järgmiselt:
Vali ID T-st, kus nimi on näiteks 'ABC%'
Vali id t-st, kus createdate>='2005-11-30′ ja createdate<'2005-12-1′
9. Ärge tehke funktsioone, aritmeetilisi operatsioone ega muid avaldisoperatsioone "=" vasakul lõigus kus klauslis, vastasel juhul ei pruugi süsteem indeksit õigesti kasutada.
10. Kui indeksvälja kasutatakse tingimusena, kui indeks on liitindeks, tuleb indeksi esimene välja kasutada tingimusena, et süsteem kasutab indeksit, vastasel juhul indeksit ei kasutata ning väljade järjekord peaks olema võimalikult kooskõlas indeksi järjekorraga.
11. Ära kirjuta mõttetuid päringuid, näiteks genereeri tühja tabelistruktuuri:
vali col1,col2 #t t-st, kus 1=0
See kood ei tagasta tulemuste hulka, kuid tarbib süsteemi ressursse, seega tuleks see muuta selliseks:
loo tabel #t(...)
12. Sageli on see hea valik kasutada exists, mitte järgmistes kohtades:
vali num a-st, kus num sisse (vali num b-st)
Asenda järgmise väitega:
vali num a-st, kus eksisteerib (vali 1 b-st, kus num=a.num)
Asjad, millele indeksit koostades tähelepanu pöörata:
1. Kõik indeksid ei kehti päringute jaoks, SQL põhineb tabelis olevatel andmetel päringu optimeerimiseks; kui indeksiveerus on suur hulk andmete dubleerimist, ei pruugi SQL-päringud indeksit kasutada, näiteks tabelis on väljad sugu, mees, naine, peaaegu pool igaühest, ja isegi kui indeks põhineb sool, ei mängi see päringute efektiivsuses rolli.
2. Mida rohkem indekseid ei ole, seda parem – indeks võib kindlasti parandada vastava valiku efektiivsust, kuid vähendab ka lisamise ja uuendamise efektiivsust, sest indeksit võib lisamisel või uuendamisel uuesti üles ehitada, seega tuleb indeksi loomise viis hoolikalt läbi mõelda, sõltuvalt konkreetsest olukorrast. Parim on mitte hoida tabelis rohkem kui 6 indeksit, ja kui neid on liiga palju, kaalu, kas on vaja indekseid ehitada mõnele harva kasutatavale veerule.
3. Väldi klasterdatud indeksiandmete veergude uuendamist nii palju kui võimalik, sest klasterdatud indekseeritud andmeveergude järjekord on tabelikirjete füüsiline salvestusjärjekord ning kui veeru väärtus muutub, viib see kogu tabelikirjete järjekorra kohandamiseni, mis kulutab märkimisväärseid ressursse. Kui rakendussüsteem peab klasterdatud indeksiveerge sageli uuendama, peab ta kaaluma, kas indeks tuleks ehitada klasterdatud indeksina.
Muud tähelepanekud:
1. Proovi kasutada numbrilisi välju ja mitte kujundada välju, mis sisaldavad ainult numbrilist infot tähemärkidena, mis vähendab päringute ja ühenduste jõudlust ning suurendab salvestuskoormust. See on tingitud sellest, et mootor võrdleb iga stringi märki ükshaaval, kui töödeldakse päringuid ja liitumisi, samas kui numbriliste tüüpide puhul tuleb seda võrrelda vaid üks kord.
2. Ära kasuta kuskil valikut *, asenda "*" konkreetse väljade nimekirjaga ja ära tagasta ühtegi välja, mida ei kasutata.
3. Proovi kasutada tabelimuutujaid ajutiste tabelite asemel. Kui tabelimuutuja sisaldab suurt hulka andmeid, pane tähele, et indeks on väga piiratud (ainult peamine võtmeindeks).
4. Vältige ajutiste tabelite sagedast loomist ja kustutamist, et vähendada süsteemitabeli ressursside tarbimist.
5. Ajutised tabelid ei ole kasutuskõlbmatud ning nende õige kasutamine võib muuta teatud rutiinid tõhusamaks, näiteks kui pead korduvalt viitama suurele tabelile või andmekogule tavapärases tabelis. Kuid ühekordsete sündmuste puhul on kõige parem kasutada eksporditabelit.
6. Ajutise tabeli loomisel, kui korraga sisestatud andmemaht on suur, siis saad kasutada valikut sisse loomise tabeli asemel, et vältida suurt logide arvu kiiruse paranemist; Kui andmemaht pole suur, siis süsteemitabeli ressursside lihtsustamiseks tuleks esmalt luua tabel ja seejärel lisada.
7. Kui kasutatakse ajutist tabelit, kustuta kindlasti kõik ajutised tabelid salvestatud protseduuri lõpus, lõika esmalt tabel välja ja seejärel eemalda tabel, et vältida süsteemitabeli pikka lukustust.
8. Püüa vältida kursori kasutamist, sest kursori efektiivsus on kehv; kui kursori poolt juhitav andmemaht ületab 10 000 rida, peaksid kaaluma ümberkirjutamist.
9. Enne kursoripõhise või ajutise tabeli meetodi kasutamist tuleks esmalt otsida hulgapõhiseid lahendusi probleemi lahendamiseks, mis tavaliselt on hulgapõhine meetod tõhusam.
10. Nagu ajutiste tabelite puhul, ei ole kursor kasutuskõlbmatu. FAST_FORWARD kursorite kasutamine väikeste andmekogumite jaoks on sageli parem kui teised ridapõhised töötlemismeetodid, eriti kui pead viitama mitmele tabelile, et saada vajalikke andmeid. Rutiinid, mis sisaldavad tulemuste hulka "total", on tavaliselt kiiremad kui need, mis täidetakse kursoriga. Kui arendusaeg lubab, saab proovida nii kursoripõhiseid kui ka komplektipõhiseid meetodeid, et näha, kumb töötab paremini.
11. Sea SET NOCOUNT kõigi salvestatud protseduuride ja päästikute alguses ning seadista SET NOCOUNT OFF lõpus. Pärast iga salvestatud protseduuri ja käivitaja käivitamist ei ole vaja saata kliendile DONE_IN_PROC sõnumeid.
12. Püüa vältida suurte andmete tagastamist kliendile, kui andmemaht on liiga suur, peaksid kaaluma, kas vastav nõudlus on mõistlik.
13. Püüa vältida suuri tehinguid ja parandada süsteemi paralleelsuse võimekust.
|
Postitatud 11.05.2018 13:57:06
|
|
|
Postitatud 17.05.2018 10:12:27