Po testování byl proveden podmíněný dotaz na tabulce obsahující více než 4 miliony záznamů, přičemž doba dotazu byla až 40 sekund. Proto je velmi důležité, jak zlepšit efektivitu dotazů SQL příkazů. Následuje několik metod optimalizace dotazových příkazů, které jsou široce rozšířené na internetu:
Za prvé, když je objem dat velký, měli byste se snažit vyhnout prohledávání celé tabulky a zvážit vytváření indexů na sloupcích, které jsou zapojeny do pořadí a pořadí po, což může výrazně urychlit vyhledávání dat. Nicméně existují situace, kdy indexování nefunguje:
1. Snažte se vyhnout použití != nebo <> operátorů v klauzuli where, jinak engine opustí indexy a provede kompletní tabulkové skenování.
2. Snažte se vyhnout hodnotovému soudu na polí v klauzuli where, jinak engine opustí použití indexů a provede kompletní tabulkové skenování, například:
Vyberte id z T, kde num je nul
Můžete nastavit výchozí hodnotu 0 na num, ujistit se, že v tabulce num není žádná null, a pak dotazovat takto:
Vyberte id z T, kde num=0
3. Snažte se vyhnout použití OR v klauzuli where pro spojení podmínek, jinak engine přestane používat index a provede kompletní tabulkový sken, například:
Vyberte id z T, kde num=10 nebo num=20
Můžete se ptát takto:
Vyberte id z T, kde num=10
Sjednocení všech
Vyberte ID z T, kde num=20
4. Následující dotaz také povede k úplnému skenování tabulky:
Vyberte id od T, kde se jmenuje například '%abc%'
Pro zvýšení efektivity zvažte vyhledávání v plném textu.
5. In a not in by se měly používat opatrně, jinak povedou k úplnému skenování tabulky, například:
Vyberte id z T, kde num in(1,2,3)
Pro spojité hodnoty, pokud můžete použít mezi, nepoužívejte v:
Vyberte id z T, kde num mezi 1 a 3
6. Pokud použijete parametr v klauzuli kde, způsobí to také prohledání celé tabulky. Protože SQL řeší pouze lokální proměnné za běhu, ale optimalizátor nemůže odložit výběr přístupových plánů na runtime; Musí být vybrán při kompilaci. Pokud je však při kompilaci vytvořen přístupový plán, hodnota proměnné je stále neznámá a proto ji nelze použít jako vstupní položku pro výběr indexu. Následující prohlášení budou načtena v plném rozsahu:
Vyberte id z T, kde num=@num
Můžete donutit dotaz, aby místo toho použil index:
Vyberte id z t with(index(index(index name)) kde num=@num
7. Snažte se vyhnout vyjádření polí v klauzuli where, což způsobí, že engine přestane používat index a provede kompletní skenování tabulek. Například:
Vyberte id z T, kde num/2=100
Mělo by se změnit na:
Vyberte id z T, kde num=100*2
8. Snažte se vyhnout provádění funkčních operací na polích v klauzuli where, což způsobí, že engine přestane používat indexy a provede kompletní skenování tabulek. Například:
Vyberte id z T, kde podstring(název,1,3)='ABC' – ID jména začínající na abc
Vyberte ID z T, kde datediff(day,createdate,'2005-11-30′)=0–'2005-11-30′ generované ID
Mělo by se změnit na:
Vyberte id od T, kde se jmenuje například 'abc%'
Vyberte ID z T, kde je vytvořeno>='2005-11-30′ a vytvořeno<'2005-12-1′
9. Neprovádějte funkce, aritmetické operace ani jiné výrazové operace nalevo od "=" v klauzuli where, jinak systém nemusí být schopen správně použít index.
10. Při použití indexového pole jako podmínky, pokud je index složený index, musí být první pole v indexu použito jako podmínka, aby systém index používal, jinak index nebude použit a pořadí polí by mělo být co nejvíce konzistentní s pořadím indexů.
11. Nepište nějaké bezvýznamné dotazy, například generujte prázdnou tabulkovou strukturu:
Vyberte sloupec1,sloupec2 do #t z T, kde 1=0
Tento typ kódu nevrací žádnou sadu výsledků, ale spotřebovává systémové zdroje, takže by měl být změněn na něco takto:
Create table #t(...)
12. Často je dobré použít exists místo v:
Vyberte num z A, kde num v (vyberte num z b)
Nahraďte následujícím tvrzením:
Vyberte num z A, kde existuje (vyberte 1 z B, kde num=a.num)
Na co si dát pozor při tvorbě indexu:
1. Ne všechny indexy jsou platné pro dotazy, SQL je založeno na datech v tabulce pro optimalizaci dotazu, pokud má sloupec indexu velké množství duplicit dat, SQL dotazy nemusí index využívat, například tabulka má pole pohlaví, samec a žena téměř polovinu každé, a i když je index postaven na pohlaví, nebude hrát roli v efektivitě dotazů.
2. Čím více indexů, tím lépe, index může určitě zvýšit efektivitu odpovídajícího výběru, ale zároveň snižuje efektivitu vkládání a aktualizace, protože index může být při vkládání nebo aktualizaci znovu sestaven, takže je třeba pečlivě zvážit, jak index vytvořit, v závislosti na konkrétní situaci. Nejlepší je nemít v tabulce více než 6 indexů, a pokud jich je příliš mnoho, zvažte, zda je nutné vytvářet indexy na některých zřídka používaných sloupcích.
3. Vyhněte se aktualizaci sloupců dat shlukovaných indexů co nejvíce, protože pořadí shlukovaných indexovaných datových sloupců je fyzické pořadí záznamů v tabulce a jakmile se změní hodnota sloupce, povede to k úpravě pořadí celých záznamů tabulky, což spotřebuje značné zdroje. Pokud aplikační systém potřebuje často aktualizovat shlukované indexové sloupce, musí zvážit, zda by měl být index vytvořen jako shlukovaný index.
Další body, které stojí za zmínku:
1. Snažte se používat číselná pole a nenavrhovat pole, která obsahují pouze číselné informace jako znaky, což sníží výkon dotazů a spojení a zvýší režijní náklady na úložiště. Je to proto, že engine porovnává každý znak v řetězci jeden po druhém při zpracování dotazů a spojení, zatímco u číselných typů je potřeba porovnávat pouze jednou.
2. Nepoužívejte výběr * z t nikde inde, nahraďte "*" konkrétním seznamem polí a nevracejte žádná pole, která nejsou použita.
3. Snažte se používat tabulkové proměnné místo dočasných tabulek. Pokud tabulková proměnná obsahuje velké množství dat, všimněte si, že index je velmi omezený (pouze primární klíčový index).
4. Vyhněte se častému vytváření a mazání dočasných tabulek, abyste snížili spotřebu systémových tabulek.
5. Dočasné tabulky nejsou nepoužitelné a jejich správné použití může některé rutiny učinit efektivnějšími, například když je potřeba opakovaně odkazovat na velkou tabulku nebo datovou sadu v běžně používané tabulce. Pro jednorázové události je však nejlepší použít exportní tabulku.
6. Při vytváření dočasné tabulky, pokud je množství dat vložených najednou velké, můžete místo vytváření tabulky použít select in, abyste se vyhnuli zrychlení velkého počtu logů; Pokud množství dat není velké, abyste usnadnili zdroje systémové tabulky, měli byste nejprve vytvořit tabulku a pak ji vložit.
7. Pokud je použita dočasná tabulka, ujistěte se, že explicitně smažete všechny dočasné tabulky na konci uložené procedury, nejprve tabulku zkrácejte a poté tabulku zrušte, abyste se vyhnuli dlouhému uzamčení systémové tabulky.
8. Snažte se kurzoru vyhnout, protože jeho účinnost je špatná; pokud data s kurzorem přesahují 10 000 řádků, měli byste zvážit přepisování.
9. Před použitím metody založené na kurzoru nebo metodě dočasné tabulky byste měli nejprve hledat řešení založená na množinách, která problém vyřeší, a metoda založená na množinách je obvykle účinnější.
10. Stejně jako u dočasných tabulek není kurzor nepoužitelný. Používání kurzorů FAST_FORWARD pro malé datové sady je často lepší než jiné metody zpracování řádek po řádku, zvlášť pokud musíte odkazovat na několik tabulek, abyste získali potřebná data. Rutiny, které obsahují "total" ve výsledné sadě, jsou obvykle rychlejší než ty, které se vykonávají kurzorem. Pokud to dovolí čas na vývoj, lze vyzkoušet jak metody založené na kurzoru, tak na množině, aby se zjistilo, která funguje lépe.
11. Nastavte NASTAVIT NOCOUNT ON na začátku všech uložených procedur a spouštěčů a na konci NASTAVIT NOCOUNT vypnuté. Není třeba klientovi posílat DONE_IN_PROC zprávy po vykonání každého příkazu uložené procedury a spouštěče.
12. Snažte se vyhnout vracení velkých dat klientovi; pokud je objem dat příliš velký, měli byste zvážit, zda je odpovídající poptávka rozumná.
13. Snažte se vyhnout velkým transakčním operacím a zlepšit schopnost systému v souběžnosti.
|
Zveřejněno 11.05.2018 13:57:06
|
|
|
Zveřejněno 17.05.2018 10:12:27