Po testovaní bol vykonaný podmienený dotaz na tabuľke obsahujúcej viac ako 4 milióny záznamov, pričom čas dotazu bol až 40 sekúnd. Preto je veľmi dôležité, ako zlepšiť efektivitu SQL dotazu na príkazy. Nasleduje niekoľko metód optimalizácie dotazových príkazov, ktoré sú široko rozšírené na internete:
V prvom rade, keď je objem dát veľký, mali by ste sa snažiť vyhnúť skenovaniu celej tabuľky a zvážiť vytváranie indexov na stĺpcoch, ktoré sú použité v Where a Order po, čo môže výrazne urýchliť vyhľadávanie dát. Existujú však situácie, keď indexovanie nefunguje:
1. Snažte sa vyhnúť používaniu operátorov != alebo <> v klauzule where, inak engine opustí používanie indexov a vykoná kompletné skenovanie tabuliek.
2. Snažte sa vyhnúť hodnotovému súdu na poliach v klauzule where, inak engine opustí používanie indexov a vykoná úplné skenovanie tabuliek, napríklad:
Vyberte id z T, kde num je nul
Môžete nastaviť predvolenú hodnotu 0 na num, uistiť sa, že v stĺpci num v tabuľke nie je žiadna null, a potom sa dotazovať takto:
vyber id z t, kde num=0
3. Snažte sa vyhnúť používaniu OR v klauzule where na spojenie podmienok, inak engine prestane používať index a vykoná kompletné skenovanie tabuľky, napríklad:
Vyberte id z T, kde num=10 alebo num=20
Môžete sa dopytovať takto:
Vyberte id z T, kde num=10
Zjednotenie všetkých
Vyberte id z T, kde num=20
4. Nasledujúci dotaz tiež povedie k úplnému skenovaniu tabuľky:
Vyberte id z T, kde sa volá napríklad '%abc%'
Pre zvýšenie efektivity zvážte fulltextové vyhľadávanie.
5. In a not in by sa tiež mali používať opatrne, inak to povedie k úplnému skenovaniu tabuľky, napríklad:
Vyberte id z t, kde num in(1,2,3)
Pre spojité hodnoty, ak môžete použiť medzi, nepoužívajte v:
Vyberte id z t, kde num medzi 1 a 3
6. Ak použijete parameter v klauzule kde, spôsobí to aj skenovanie celej tabuľky. Pretože SQL rieši lokálne premenné iba za behu, ale optimalizátor nemôže odložiť výber prístupových plánov na runtime; Musí byť vybraný pri kompilácii. Ak je však plán prístupu vytvorený pri kompilácii, hodnota premennej je stále neznáma a preto nemôže byť použitá ako vstupná položka na výber indexu. Nasledujúce vyhlásenia budú naskenované v plnom rozsahu:
Vyberte id z t, kde num=@num
Môžete donútiť dotaz použiť index:
Vyberte id z t with(index(index(index name)) kde num=@num
7. Snažte sa vyhnúť vyjadreniu polí v klauzule where, čo spôsobí, že engine prestane používať index a vykoná úplné skenovanie tabuliek. Napríklad:
Vyberte id z T, kde num/2=100
Mali by sa zmeniť na:
Vyberte id z T, kde num=100*2
8. Snažte sa vyhnúť vykonávaniu funkčných operácií na poliach v klauzule where, čo spôsobí, že engine ukončí používanie indexov a vykoná úplné skenovanie tabuliek. Napríklad:
Vyberte id z T, kde substring(meno,1,3)='abc' – ID mena začínajúce na abc
Vyberte ID z T, kde datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ generované ID
Mali by sa zmeniť na:
Vyberte id od T, kde sa volá napríklad 'abc%'
Vyberte ID z T, kde vytvorené>='2005-11-30′ a vytvorené<'2005-12-1′
9. Nevykonávajte funkcie, aritmetické operácie ani iné výrazové operácie naľavo od "=" v klauzule where, inak systém nemusí byť schopný správne použiť index.
10. Pri použití indexového poľa ako podmienky, ak je index zložený index, potom prvé pole v indexe musí byť použité ako podmienka, aby systém použil daný index, inak sa index nepoužije a poradie polí by malo byť čo najviac v súlade s indexovým poradím.
11. Nepíšte nezmyselné dotazy, napríklad generovanie prázdnej tabuľkovej štruktúry:
Vyberte stĺp1,stĺp2 do #t z T, kde 1=0
Tento typ kódu nevracia žiadnu súbor výsledkov, ale spotrebúva systémové zdroje, preto by sa mal zmeniť na niečo takéto:
Create table #t(...)
12. Mnohokrát je dobré použiť exists namiesto v:
Vyberte num z A, kde num v (vyberte num z b)
Nahradiť to nasledujúcim výrokom:
Vyberte num z A, kde existuje (vyberte 1 z B, kde num=a.num)
Na čo si treba dať pozor pri tvorbe indexu:
1. Nie všetky indexy sú platné pre dotazy, SQL je založené na dátach v tabuľke na optimalizáciu dotazu, ak má indexový stĺpec veľké množstvo duplikácie dát, SQL dotazy nemusia index používať, napríklad tabuľka má polia pohlavie, samec a žena takmer polovicu každé, potom aj keď je index postavený na pohlaví, nebude hrať úlohu v efektivite dotazov.
2. Čím viac indexov nie je, tým lepšie, index môže určite zlepšiť efektivitu príslušného výberu, ale zároveň znižuje efektivitu vkladania a aktualizácie, pretože index môže byť pri vkladaní alebo aktualizácii znovu vytváraný, takže spôsob vytvorenia indexu je potrebné starostlivo zvážiť v závislosti od konkrétnej situácie. Najlepšie je mať v tabuľke viac ako 6 indexov, a ak ich je príliš veľa, zvážte, či je potrebné vytvárať indexy na niektorých zriedkavo používaných stĺpcoch.
3. Vyhýbajte sa aktualizáciám zoskupených indexových dátových stĺpcov čo najviac, pretože poradie zoskupených indexovaných dátových stĺpcov je fyzickým poradím ukladania záznamov v tabuľkách, a keď sa hodnota stĺpca zmení, povedie to k úprave poradia celých záznamov tabuľky, čo spotrebuje značné zdroje. Ak aplikačný systém potrebuje často aktualizovať zhlukované indexové stĺpce, musí zvážiť, či by mal byť index vytvorený ako zhlukovaný index.
Ďalšie body, ktoré treba spomenúť:
1. Snažte sa používať číselné polia a nenavrhovať polia, ktoré obsahujú iba číselné informácie ako znaky, čo zníži výkon dotazov a spojení a zvýši režijné náklady na úložisko. Je to preto, že engine porovnáva každý znak v reťazci jeden po druhom pri spracovaní dotazov a spojení, zatiaľ čo pre číselné typy je potrebné porovnať iba raz.
2. Nepoužívajte výber * z t, nahraďte "*" konkrétnym zoznamom polí a nevracajte žiadne polia, ktoré nie sú použité.
3. Skúste používať tabuľkové premenné namiesto dočasných tabuliek. Ak tabuľková premenná obsahuje veľké množstvo údajov, všimnite si, že index je veľmi obmedzený (iba primárny kľúčový index).
4. Vyhnite sa častému vytváraniu a mazaniu dočasných tabuliek, aby ste znížili spotrebu systémových tabuliek.
5. Dočasné tabuľky nie sú nepoužiteľné a ich správne použitie môže niektoré rutiny zefektívniť, napríklad keď potrebujete opakovane odkazovať na veľkú tabuľku alebo dátovú sadu v bežne používanej tabuľke. Pre jednorazové udalosti je však najlepšie použiť exportnú tabuľku.
6. Pri vytváraní dočasnej tabuľky, ak je množstvo dát vložených naraz veľké, môžete namiesto vytvorenia tabuľky použiť select in, aby ste predišli zvýšeniu rýchlosti veľkého počtu logov; Ak množstvo dát nie je veľké, aby ste uľahčili zdroje systémovej tabuľky, mali by ste najskôr vytvoriť tabuľku a potom ju vložiť.
7. Ak sa používa dočasná tabuľka, uistite sa, že všetky dočasné tabuľky na konci uloženej procedúry explicitne vymažete, najskôr skrátite tabuľku a potom tabuľku zrušte, aby ste predišli dlhému zamknutiu systémovej tabuľky.
8. Snažte sa vyhnúť používaniu kurzora, pretože jeho účinnosť je nízka, ak dáta ovládané kurzorom presahujú 10 000 riadkov, mali by ste zvážiť prepísanie.
9. Pred použitím metódy založenej na kurzore alebo metóde dočasných tabuliek by ste mali najskôr hľadať riešenia založené na množinách na vyriešenie problému, pričom metóda založená na množinách je zvyčajne efektívnejšia.
10. Rovnako ako dočasné tabuľky, kurzor nie je nepoužiteľný. Používanie FAST_FORWARD kurzorov pre malé dátové súbory je často lepšie ako iné metódy spracovania riadok po riadku, najmä ak musíte odkazovať na viacero tabuliek, aby ste získali potrebné dáta. Rutiny, ktoré obsahujú "total" vo výslednej množine, sú zvyčajne rýchlejšie ako tie, ktoré sa vykonávajú kurzorom. Ak to vývojový čas dovolí, možno vyskúšať metódy založené na kurzoroch aj množinách, aby sa zistilo, ktorá funguje lepšie.
11. Nastavte NASTAVIŤ NOCOUNT ON na začiatku všetkých uložených procedúr a spúšťačov a na konci NASTAVIŤ NOCOUNT vypnuté. Nie je potrebné posielať klientovi DONE_IN_PROC správ po vykonaní každého príkazu uloženej procedúry a spúšťača.
12. Snažte sa vyhnúť vráteniu veľkých dát klientovi, ak je objem dát príliš veľký, mali by ste zvážiť, či je zodpovedajúca požiadavka primeraná.
13. Snažiť sa vyhnúť veľkým transakčným operáciám a zlepšiť schopnosť systému prebehnúť súbežnosť.
|
Zverejnené 11. 5. 2018 13:57:06
|
|
|
Zverejnené 17. 5. 2018 10:12:27