Po testavimo sąlyginė užklausa buvo atlikta lentelėje, kurioje yra daugiau nei 4 milijonai įrašų, o užklausos laikas buvo net 40 sekundžių. Todėl labai svarbu, kaip pagerinti SQL sakinio užklausos efektyvumą. Toliau pateikiami keli užklausų teiginių optimizavimo metodai, plačiai paplitę internete:
Visų pirma, kai duomenų kiekis yra didelis, turėtumėte stengtis vengti nuskaityti visą lentelę ir apsvarstyti galimybę kurti indeksus stulpeliuose, kuriuose nurodoma, kur ir pagal kuriuos galima sutvarkyti, o tai gali labai pagreitinti duomenų gavimą. Tačiau yra situacijų, kai indeksavimas neveikia:
1. Stenkitės nenaudoti != arba <> operatorių where sąlygoje, kitaip variklis atsisakys indeksų naudojimo ir atliks visos lentelės nuskaitymą.
2. Stenkitės išvengti nulinės vertės vertinimo laukuose where sąlyga, kitaip variklis atsisakys indeksų naudojimo ir atliks visą lentelės nuskaitymą, pvz.:
Pasirinkite ID iš T, kur Num yra neapibrėžtas
Galite nustatyti numatytąją skaičių reikšmę 0, įsitikinti, kad lentelės stulpelyje nr nėra nulinės reikšmės, ir tada užklausti taip:
Pasirinkite ID iš t, kur num=0
3. Stenkitės nenaudoti OR kur sąlygoje sujungti sąlygas, kitaip variklis nustos naudoti indeksą ir atliks visą lentelės nuskaitymą, pvz.:
Pasirinkite ID iš t, kur num=10 arba num=20
Galite pateikti tokią užklausą:
Pasirinkite ID iš t, kur num=10
Sąjunga visi
Pasirinkite ID iš t, kur num=20
4. Atlikus šią užklausą taip pat bus nuskaityta visa lentelė:
Pasirinkite ID iš t, kur pavadinimas pvz., '%abc%'
Norėdami pagerinti efektyvumą, apsvarstykite galimybę ieškoti viso teksto.
5. In ir not in taip pat turėtų būti naudojamas atsargiai, kitaip bus nuskaityta visa lentelė, pvz.:
Pasirinkite ID iš T, kur Num In(1,2,3)
Jei galite naudoti nepertraukiamas reikšmes, nenaudokite:
Pasirinkite ID iš t, kur skaičius nuo 1 iki 3
6. Jei naudosite parametrą sąlygoje where, taip pat bus nuskaityta visa lentelė. Kadangi SQL išsprendžia tik vietinius kintamuosius vykdymo metu, tačiau optimizatorius negali atidėti prieigos planų pasirinkimo vykdymo laikui; Jis turi būti pasirinktas kompiliavimo metu. Tačiau, jei kompiliavimo metu nustatomas prieigos planas, kintamojo reikšmė vis dar nežinoma, todėl negali būti naudojama kaip įvesties elementas indekso pasirinkimui. Bus nuskaityti visi šie teiginiai:
Pasirinkite ID iš t, kur num=@num
Galite priversti užklausą naudoti indeksą:
Pasirinkite ID iš t su(index(index(index pavadinimas)) kur num=@num
7. Stenkitės vengti išreikšti laukus kur sąlygoje, dėl kurios variklis nustos naudoti indeksą ir atliks visą lentelės nuskaitymą. Pavyzdžiui:
Pasirinkite ID iš t, kur num/2=100
turėtų būti pakeistas į:
Pasirinkite ID iš t, kur num=100*2
8. Stenkitės vengti funkcijų operacijų laukuose, esančiuose kur sąlygoje, nes variklis atsisakys indeksų naudojimo ir atliks visą lentelės nuskaitymą. Pavyzdžiui:
Pasirinkite ID iš T, kur substring(name,1,3)='abc' – pavadinimo ID, prasidedantis ABC
Pasirinkite ID iš t, kur datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ sugeneruotas ID
turėtų būti pakeistas į:
Pasirinkite ID iš t, kur pavadinimas, pvz., 'abc%'
Pasirinkite ID iš T, kur sukurta>='2005-11-30′ ir sukurta<'2005-12-1′
9. Neatlikite funkcijų, aritmetinių operacijų ar kitų išraiškos operacijų kairėje nuo "=" sakinyje where, kitaip sistema gali nesugebėti tinkamai naudoti indekso.
10. Naudojant indekso lauką kaip sąlygą, jei indeksas yra sudėtinis indeksas, tada pirmasis indekso laukas turi būti naudojamas kaip sąlyga, užtikrinanti, kad sistema naudoja indeksą, kitaip indeksas nebus naudojamas, o laukų tvarka turi kiek įmanoma atitikti indekso tvarką.
11. Nerašykite beprasmių užklausų, pvz., sukurkite tuščios lentelės struktūrą:
Pasirinkite col1,col2 į #t iš t, kur 1=0
Šio tipo kodas nepateikia jokio rezultatų rinkinio, tačiau sunaudoja sistemos išteklius, todėl jį reikia pakeisti į kažką panašaus:
Kurti lentelę #t(...)
12. Daug kartų tai yra geras pasirinkimas naudoti, o ne:
Pasirinkite NUM iš kur NUM IN(Pasirinkite NUM iš b)
Pakeisti šiuo teiginiu:
Pasirinkite num iš a, kur yra(pasirinkite 1 iš b, kur num=a.num)
Į ką reikia atkreipti dėmesį kuriant indeksą:
1. Ne visi indeksai galioja užklausoms, SQL yra pagrįstas lentelės duomenimis, kad būtų optimizuota užklausa, kai indekso stulpelyje yra daug duomenų dubliavimosi, SQL užklausos gali nenaudoti indekso, pvz., lentelėje yra laukai lytis, vyras, moteris beveik po pusę, tada net jei indeksas yra sukurtas pagal lytį, jis neturės įtakos užklausos efektyvumui.
2. Kuo daugiau indeksų nėra geriau, indeksas tikrai gali pagerinti atitinkamo pasirinkimo efektyvumą, tačiau taip pat sumažina įterpimo ir atnaujinimo efektyvumą, nes indeksas gali būti atstatytas įterpiant ar atnaujinant, todėl reikia atidžiai apsvarstyti, kaip sudaryti indeksą, atsižvelgiant į konkrečią situaciją. Geriausia lentelėje neturėti daugiau nei 6 indeksų, o jei jų yra per daug, apsvarstykite, ar reikia kurti indeksus kai kuriuose retai naudojamuose stulpeliuose.
3. Kiek įmanoma venkite atnaujinti sugrupuotus indekso duomenų stulpelius, nes sugrupuotų indeksuotų duomenų stulpelių tvarka yra fizinė lentelės įrašų saugojimo tvarka, o pasikeitus stulpelio reikšmei, reikės pakoreguoti visų lentelės įrašų tvarką, o tai sunaudos daug išteklių. Jei taikomajai sistemai reikia dažnai atnaujinti sugrupuotus indekso stulpelius, ji turi apsvarstyti, ar indeksas turėtų būti sukurtas kaip sugrupuotas indeksas.
Kiti dalykai, į kuriuos reikia atkreipti dėmesį:
1. Stenkitės naudoti skaitinius laukus ir stenkitės nekurti laukų, kuriuose yra tik skaitinė informacija kaip simboliai, nes tai sumažins užklausų ir ryšių našumą bei padidins saugyklos išlaidas. Taip yra todėl, kad variklis lygina kiekvieną eilutės simbolį po vieną, apdorodamas užklausas ir sujungimus, o skaitiniams tipams jį reikia palyginti tik vieną kartą.
2. Niekur nenaudokite select * from t, pakeiskite "*" konkrečiu laukų sąrašu ir negrąžinkite nenaudojamų laukų.
3. Pabandykite naudoti lentelės kintamuosius, o ne laikinas lenteles. Jei lentelės kintamajame yra didelis duomenų kiekis, atkreipkite dėmesį, kad indeksas yra labai ribotas (tik pirminio rakto indeksas).
4. Venkite dažnai kurti ir naikinti laikinas lenteles, kad sumažintumėte sistemos lentelės išteklių sunaudojimą.
5. Laikinos lentelės nėra netinkamos naudoti, o tinkamai jas naudojant tam tikros procedūros gali būti efektyvesnės, pavyzdžiui, kai reikia pakartotinai nurodyti didelę lentelę arba duomenų rinkinį dažniausiai naudojamoje lentelėje. Tačiau vienkartiniams įvykiams geriausia naudoti eksportavimo lentelę.
6. Kurdami laikiną lentelę, jei vienu metu įterptų duomenų kiekis yra didelis, galite naudoti pasirinkti į, o ne sukurti lentelę, kad išvengtumėte didelio žurnalų skaičiaus, kad pagerintumėte greitį; Jei duomenų kiekis nėra didelis, norėdami palengvinti sistemos lentelės išteklius, pirmiausia turėtumėte sukurti lentelę, o tada įterpti.
7. Jei naudojama laikina lentelė, būtinai ištrinkite visas laikinas lenteles saugomos procedūros pabaigoje, pirmiausia sutrumpinkite lentelę, o tada numeskite lentelę, kad išvengtumėte ilgo sistemos lentelės užrakinimo.
8. Stenkitės vengti žymeklio naudojimo, nes žymeklio efektyvumas yra prastas, jei žymeklio valdomi duomenys viršija 10 000 eilučių, turėtumėte apsvarstyti galimybę perrašyti.
9. Prieš naudodami žymekliu pagrįstą metodą arba laikinos lentelės metodą, pirmiausia turėtumėte ieškoti rinkiniu pagrįstų sprendimų, kad išspręstumėte problemą, o rinkiniu pagrįstas metodas paprastai yra efektyvesnis.
10. Kaip ir laikinos lentelės, žymeklis nėra netinkamas naudoti. Naudoti FAST_FORWARD žymeklius mažiems duomenų rinkiniams dažnai yra geriau nei kitus eilutės apdorojimo metodus, ypač jei turite nurodyti kelias lenteles, kad gautumėte reikiamus duomenis. Procedūros, kuriose rezultatų rinkinyje yra "total", paprastai yra greitesnės nei tos, kurios vykdomos žymekliu. Jei kūrimo laikas leidžia, galima pabandyti ir žymekliu, ir rinkiniu pagrįstus metodus, kad pamatytumėte, kuris veikia geriau.
11. Visų saugomų procedūrų ir paleidiklių pradžioje nustatykite SET NOCOUNT ON, o pabaigoje nustatykite SET NOCOUNT OFF. Įvykdžius kiekvieną saugomos procedūros ir paleidiklio teiginį, klientui nereikia siųsti DONE_IN_PROC pranešimų.
12. Stenkitės vengti didelių duomenų grąžinimo klientui, jei duomenų kiekis yra per didelis, turėtumėte apsvarstyti, ar atitinkamas reikalavimas yra pagrįstas.
13. Stenkitės vengti didelių operacijų operacijų ir pagerinti sistemos sutapimo galimybes.
|
Paskelbta 2018-05-11 13:57:06
|
|
|
Paskelbta 2018-05-17 10:12:27