Pēc testēšanas nosacījumvaicājums tika veikts tabulā, kurā bija vairāk nekā 4 miljoni ierakstu, un vaicājuma laiks bija 40 sekundes. Tāpēc ir ļoti svarīgi, kā uzlabot SQL paziņojuma vaicājuma efektivitāti. Tālāk ir norādītas vairākas vaicājumu paziņojumu optimizācijas metodes, kas ir plaši izplatītas internetā:
Pirmkārt, ja datu apjoms ir liels, jums jācenšas izvairīties no pilnas tabulas skenēšanas un jāapsver indeksu veidošana kolonnās, kas saistītas ar to, kur un pēc kārtas, kas var ievērojami paātrināt datu izgūšanu. Tomēr ir dažas situācijas, kad indeksēšana nedarbojas:
1. Mēģiniet izvairīties no != vai <> operatoru izmantošanas kur klauzulā, pretējā gadījumā dzinējs atteiksies no indeksu izmantošanas un veiks pilnu tabulas skenēšanu.
2. Mēģiniet izvairīties no nulles vērtības sprieduma par laukiem where klauzulā, pretējā gadījumā dzinējs atteiksies no indeksu izmantošanas un veiks pilnu tabulas skenēšanu, piemēram:
Atlasiet ID no T, kur Num ir Null
Varat iestatīt noklusējuma vērtību 0 skaitlim, pārliecinieties, vai tabulas kolonnā num nav nulles vērtības, un pēc tam vaicāt šādi:
Izvēlieties ID no T, kur num=0
3. Mēģiniet izvairīties no OR izmantošanas kur klauzulā, lai savienotu nosacījumus, pretējā gadījumā dzinējs atteiksies no indeksa izmantošanas un veiks pilnu tabulas skenēšanu, piemēram:
Izvēlieties ID no T, kur NUM=10 vai NUM=20
Varat vaicāt šādi:
Izvēlieties ID no t, kur num=10
Savienība visi
Izvēlieties ID no t, kur num=20
4. Šāds vaicājums arī novedīs pie pilnas tabulas skenēšanas:
Atlasiet ID no t, kur nosaukums, piemēram, '%abc%'
Lai uzlabotu efektivitāti, apsveriet pilnteksta meklēšanu.
5. In un not in jālieto arī piesardzīgi, pretējā gadījumā tas novedīs pie pilnas tabulas skenēšanas, piemēram:
Atlasiet ID no T, kur Num IN(1,2,3)
Nepārtrauktām vērtībām, ja varat izmantot starp, neizmantojiet:
Izvēlieties ID no T, kur numurs ir no 1 līdz 3
6. Ja izmantojat parametru klauzulā where, tas izraisīs arī pilnas tabulas skenēšanu. Tā kā SQL atrisina vietējos mainīgos tikai izpildlaikā, bet optimizētājs nevar atlikt piekļuves plānu atlasi uz izpildlaiku; Tas jāizvēlas kompilācijas laikā. Tomēr, ja kompilācijas laikā tiek izveidots piekļuves plāns, mainīgā vērtība joprojām nav zināma, un tāpēc to nevar izmantot kā ievades elementu indeksa atlasei. Turpmāk minētie paziņojumi tiks skenēti pilnībā:
Izvēlieties ID no t, kur num=@num
Tā vietā var piespiest vaicājumu izmantot indeksu:
Izvēlieties ID no t ar(indekss(indekss(indeksa nosaukums)), kur num=@num
7. Mēģiniet izvairīties no lauku izteikšanas klauzulā where, kas liks dzinējam atteikties no indeksa izmantošanas un veikt pilnu tabulas skenēšanu. Piemēram:
Izvēlieties ID no T, kur NUM/2=100
jāmaina uz:
Izvēlieties ID no t, kur num=100*2
8. Mēģiniet izvairīties no funkciju operāciju veikšanas laukos where klauzulā, kas liks dzinējam atteikties no indeksu izmantošanas un veikt pilnu tabulas skenēšanu. Piemēram:
Izvēlieties ID no T, kur Substring(Name,1,3)='ABC' – nosaukuma ID, kas sākas ar ABC
Atlasiet ID no T, kur DateDiff(Day,Createdate;'2005-11-30′)=0–'2005-11-30′ ģenerēts ID
jāmaina uz:
Izvēlieties ID no t, kur nosaukums, piemēram, 'abc%'
Izvēlieties ID no T, kur izveidots>='2005-11-30′ un izveidots<'2005-12-1′
9. Neveiciet funkcijas, aritmētiskās operācijas vai citas izteiksmes operācijas pa kreisi no "=" klauzulā where, pretējā gadījumā sistēma var nespēt pareizi izmantot indeksu.
10. Izmantojot indeksa lauku kā nosacījumu, ja indekss ir salikts indekss, tad pirmais indeksa lauks jāizmanto kā nosacījums, lai nodrošinātu, ka sistēma izmanto indeksu, pretējā gadījumā indekss netiks izmantots, un lauku secībai pēc iespējas jāatbilst indeksa secībai.
11. Nerakstiet dažus bezjēdzīgus vaicājumus, piemēram, ģenerējiet tukšu tabulas struktūru:
Izvēlieties col1,col2 #t no t, kur 1=0
Šāda veida kods neatgriež nekādu rezultātu kopu, bet patērē sistēmas resursus, tāpēc tas jāmaina uz kaut ko līdzīgu:
Izveidot tabulu #t(...)
12. Daudzas reizes tā ir laba izvēle izmantot, nevis ir:
Atlasīt num no kur num in(atlasīt num no b)
Aizstāt ar šādu apgalvojumu:
Atlasīt num no a, kur pastāv(atlasiet 1 no b, kur num=a.num)
Lietas, kurām jāpievērš uzmanība, veidojot indeksu:
1. Ne visi indeksi ir derīgi vaicājumiem, SQL ir balstīts uz tabulā esošajiem datiem, lai optimizētu vaicājumu, ja indeksa kolonnā ir liels datu dublēšanas apjoms, SQL vaicājumi var neizmantot indeksu, piemēram, tabulā ir lauki dzimums, vīrietis, sieviete gandrīz puse, tad pat tad, ja indekss ir veidots uz dzimuma, tam nebūs nozīmes vaicājuma efektivitātē.
2. Jo vairāk indeksu nav, jo labāk, indekss noteikti var uzlabot atbilstošās atlases efektivitāti, bet tas arī samazina ievietošanas un atjaunināšanas efektivitāti, jo indekss var tikt pārveidots, ievietojot vai atjauninot, tāpēc ir rūpīgi jāapsver, kā izveidot indeksu, atkarībā no konkrētās situācijas. Vislabāk ir nebūt vairāk par 6 indeksiem tabulā, un, ja to ir pārāk daudz, apsveriet, vai ir nepieciešams veidot indeksus dažās reti izmantotajās kolonnās.
3. Cik vien iespējams, izvairieties no sagrupēto indeksa datu kolonnu atjaunināšanas, jo sagrupēto indeksēto datu kolonnu secība ir tabulas ierakstu fiziskās glabāšanas secība, un, mainoties kolonnas vērtībai, tiks pielāgota visu tabulas ierakstu secība, kas patērēs ievērojamus resursus. Ja lietojumprogrammu sistēmai bieži jāatjaunina sagrupētās indeksa kolonnas, tai jāapsver, vai indekss ir jāveido kā klasterizēts indekss.
Citi punkti, kas jāņem vērā:
1. Mēģiniet izmantot skaitliskus laukus un mēģiniet neveidot laukus, kas satur tikai skaitlisko informāciju kā rakstzīmes, kas samazinās vaicājumu un savienojumu veiktspēju un palielinās krātuves pieskaitāmās izmaksas. Tas ir tāpēc, ka dzinējs salīdzina katru virknes rakstzīmi pa vienai, apstrādājot vaicājumus un savienojumus, savukārt skaitliskajiem tipiem tas ir jāsalīdzina tikai vienu reizi.
2. Neizmantojiet atlasīt * no t nekur, aizstājiet "*" ar konkrētu lauku sarakstu un neatgriežiet neizmantotos laukus.
3. Mēģiniet izmantot tabulas mainīgos, nevis pagaidu tabulas. Ja tabulas mainīgais satur lielu datu apjomu, ņemiet vērā, ka indekss ir ļoti ierobežots (tikai primārās atslēgas indekss).
4. Izvairieties no biežas pagaidu tabulu izveides un dzēšanas, lai samazinātu sistēmas tabulas resursu patēriņu.
5. Pagaidu tabulas nav nelietojamas, un to pareiza izmantošana var padarīt noteiktas rutīnas efektīvākas, piemēram, ja jums ir nepieciešams atkārtoti atsaukties uz lielu tabulu vai datu kopu bieži izmantotā tabulā. Tomēr vienreizējiem notikumiem ieteicams izmantot eksportēšanas tabulu.
6. Veidojot pagaidu tabulu, ja vienā reizē ievietoto datu apjoms ir liels, tad varat izmantot atlasīt, nevis izveidot tabulu, lai izvairītos no liela skaita žurnālu, lai uzlabotu ātrumu; Ja datu apjoms nav liels, lai atvieglotu sistēmas tabulas resursus, vispirms jāizveido tabula un pēc tam jāievieto.
7. Ja tiek izmantota pagaidu tabula, saglabātās procedūras beigās noteikti izdzēsiet visas pagaidu tabulas, vispirms saīsiniet tabulu un pēc tam nometiet tabulu, lai izvairītos no ilgstošas sistēmas tabulas bloķēšanas.
8. Mēģiniet izvairīties no kursora izmantošanas, jo kursora efektivitāte ir slikta, ja kursora darbinātie dati pārsniedz 10 000 rindiņu, tad jums vajadzētu apsvērt pārrakstīšanu.
9. Pirms kursora metodes vai pagaidu tabulas metodes izmantošanas vispirms jāmeklē uz kopām balstīti risinājumi, lai atrisinātu problēmu, un uz kopu balstīta metode parasti ir efektīvāka.
10. Tāpat kā pagaidu tabulas, kursors nav nelietojams. FAST_FORWARD kursoru izmantošana mazām datu kopām bieži vien ir labāka nekā citas rindas apstrādes metodes, it īpaši, ja ir jāatsaucas uz vairākām tabulām, lai iegūtu nepieciešamos datus. Rutīnas, kas rezultātu kopā ietver "kopā", parasti ir ātrākas nekā tās, kas tiek izpildītas ar kursoru. Ja izstrādes laiks atļauj, var izmēģināt gan kursoru, gan kopu metodes, lai redzētu, kas darbojas labāk.
11. Visu saglabāto procedūru un trigeru sākumā iestatiet SET NOCOUNT ON un beigās iestatiet SET NOCOUNT OFF. Pēc katra saglabātās procedūras un trigera paziņojuma izpildes klientam nav nepieciešams nosūtīt DONE_IN_PROC ziņojumus.
12. Centieties izvairīties no lielu datu atgriešanas klientam, ja datu apjoms ir pārāk liels, jums jāapsver, vai atbilstošais pieprasījums ir pamatots.
13. Mēģiniet izvairīties no lielām darījumu operācijām un uzlabot sistēmas konkurētspēju.
|
Publicēts 11.05.2018 13:57:06
|
|
|
Publicēts 17.05.2018 10:12:27