След тестване се извършваше условна заявка върху таблица, съдържаща повече от 4 милиона записа, а времето за заявка достигаше до 40 секунди. Затова е много важно как да се подобри ефективността на SQL заявките. По-долу са няколко метода за оптимизация на заявени оператори, които са широко разпространени в интернет:
Първо, когато обемът на данните е голям, трябва да се опитате да избягвате сканиране на цялата таблица и да обмислите изграждането на индекси върху колоните, участващи в where и order by, което може значително да ускори извличането на данни. Въпреки това, има ситуации, в които индексирането не работи:
1. Опитайте се да избягвате използването на оператори != или <> в клаузата where, в противен случай двигателят ще се откаже от използването на индекси и ще извърши пълно сканиране на таблицата.
2. Опитайте се да избегнете нулева оценка за полета в клаузата where, в противен случай двигателят ще се откаже от използването на индекси и ще извърши пълно сканиране на таблицата, като:
Изберете id от T, където num е null
Можете да зададете стойността по подразбиране 0 на num, да се уверите, че няма null стойност в колоната num в таблицата, и след това да направите заявка по следния начин:
Изберете id от T, където num=0
3. Опитайте се да избягвате използването на OR в клаузата where за свързване на условията, в противен случай двигателят ще се откаже от използването на индекса и ще извърши пълно сканиране на таблицата, като:
Изберете ID от T, където num=10 или num=20
Можете да изпращате заявка по следния начин:
Изберете id от T, където num=10
Union All
Изберете id от T, където num=20
4. Следната заявка ще доведе и до пълно сканиране на таблицата:
Изберете id от T, където име като '%abc%'
За да подобрите ефективността, обмислете търсене в пълен текст.
5. В и не в трябва да се използват с внимание, иначе ще доведе до пълно сканиране на масата, като например:
Изберете ID от T, където num in(1,2,3)
За непрекъснати стойности, ако можете да използвате между, не използвайте в:
Изберете ID от T, където NUM е между 1 и 3
6. Ако използвате параметъра в клаузата where, това също ще предизвика сканиране на цялата таблица. Тъй като SQL разрешава локални променливи само по време на изпълнение, но оптимизаторът не може да отлага избора на планове за достъп до runtime; Тя трябва да бъде избрана по време на компилацията. Въпреки това, ако план за достъп бъде установен по време на компилация, стойността на променливата остава неизвестна и следователно не може да се използва като входен елемент за избор на индекс. Следните изявления ще бъдат сканирани изцяло:
Изберете id от T, където num=@num
Можете да принудите заявката да използва индекс вместо това:
Изберете id от t с (index(index(index(index name)), където num=@num
7. Опитайте се да избягвате изразяване на полета в клаузата where, което ще накара двигателя да се откаже от използването на индекса и да извърши пълно сканиране на таблицата. Например:
Изберете id от T, където num/2=100
Трябва да се промени на:
Изберете id от T, където num=100*2
8. Опитайте се да избегнете изпълнението на функционални операции върху полета в клаузата where, което ще накара двигателя да се откаже от използването на индекси и да извърши пълно сканиране на таблицата. Например:
Изберете id от T, където Substring(Name,1,3)='ABC' – идентификатор на име, който започва с ABC
Изберете id от T, където Datediff(Day,Created,'2005-11-30′)=0–'2005-11-30′ генериран ID
Трябва да се промени на:
Изберете ID от T, където име като 'abc%'
Изберете id от t, където създадено>='2005-11-30′ и създадено<'2005-12-1′
9. Не изпълнявайте функции, аритметични операции или други изразни операции вляво от "=" в клаузата where, иначе системата може да не може да използва индекса правилно.
10. При използване на индексно поле като условие, ако индексът е съставен, първото поле в индекса трябва да се използва като условие, за да се гарантира, че системата използва индекса, в противен случай индексът няма да се използва, а редът на полетата трябва да е максимално съвместим с реда на индекса.
11. Не пишете някакви безсмислени заявки, като например генериране на празна таблица:
Изберете COL1,COL2 в #t от T, където 1=0
Този тип код не връща никакъв набор от резултати, но консумира системни ресурси, затова трябва да се промени на нещо подобно:
създаване на таблица #t(...)
12. Много пъти е добър избор да се използва съществува, вместо в:
Изберете num от A where num in (изберете num от b)
Заменете със следното изявление:
Изберете num от a където съществува (изберете 1 от b, където num=a.num)
Неща, на които да обърнете внимание при изграждане на индекс:
1. Не всички индекси са валидни за заявки, SQL се базира на данните в таблицата за оптимизиране на заявката, когато индексната колона има голямо дублиране на данни, SQL заявките може да не използват индекса, например таблица има полета пол, мъжко, женско почти половина, и дори ако индексът е изграден върху пол, той няма да играе роля за ефективността на заявките.
2. Колкото повече индекси не са толкова по-добре, индексът със сигурност може да подобри ефективността на съответния select, но също така намалява ефективността на вмъкване и обновяване, тъй като индексът може да бъде възстановен при вмъкване или обновяване, затова как да се построи индекс трябва да се обмисли внимателно, в зависимост от конкретната ситуация. Най-добре е да не се има повече от 6 индекса в таблица, а ако са твърде много, да се помисли дали е необходимо да се изграждат индекси върху някои рядко използвани колони.
3. Избягвайте актуализирането на клъстерирани индексни колони колкото е възможно повече, защото редът на клъстерираните индексирани колони е физическият ред на съхранение на таблицените записи, и след промяна на стойността на колоната, това ще доведе до корекция на реда на всички записи в таблицата, което ще изразходва значителни ресурси. Ако приложната система трябва често да обновява клъстерираните индексни колони, трябва да обмисли дали индексът трябва да бъде изграден като клъстериран индекс.
Други точки за отбелязване:
1. Опитайте се да използвате числови полета и не проектирайте полета, които съдържат само числова информация като символи, което ще намали производителността на заявки и връзки и ще увеличи натоварването за съхранение. Това е така, защото двигателят сравнява всеки символ в низа един по един при обработка на заявки и съединения, докато при числовите типове е необходимо да се сравни само веднъж.
2. Не използвайте select * от t никъде, заменете "*" с конкретен списък с полета и не връщайте полета, които не са използвани.
3. Опитайте да използвате променливи на таблици вместо временни таблици. Ако променливата в таблицата съдържа голямо количество данни, имайте предвид, че индексът е много ограничен (само основният ключов индекс).
4. Избягвайте честото създаване и изтриване на временни таблици, за да намалите консумацията на ресурси на системните таблици.
5. Временните таблици не са неизползваеми и правилната им употреба може да направи определени рутини по-ефективни, например когато трябва многократно да се позовавате на голяма таблица или набор от данни в често използвана таблица. Въпреки това, за еднократни събития е най-добре да се използва таблица за експортиране.
6. При създаване на временна таблица, ако количеството данни, вмъкнати наведнъж, е голямо, можете да използвате select in вместо create таблица, за да избегнете увеличаване на скоростта на голям брой логове; Ако обемът на данните не е голям, за да улесниш ресурсите на системната таблица, първо трябва да създадеш таблица и след това да я вмъкнеш.
7. Ако се използва временна таблица, уверете се, че изрично изтривате всички временни таблици в края на съхранената процедура, първо отсечете таблицата, а след това премахвате таблицата, за да избегнете дълго заключване на системната таблица.
8. Опитайте се да избягвате използването на курсора, защото ефективността му е слаба, ако данните, обработвани от курсора, надвишават 10 000 реда, тогава трябва да обмислите пренаписване.
9. Преди да използвате метода с курсор или временната таблица, първо трябва да потърсите решения, базирани на множества, за да решите проблема, като методът с множества обикновено е по-ефективен.
10. Както при временните маси, курсорът не е неизползваем. Използването на FAST_FORWARD курсори за малки набори от данни често е по-добро от другите методи за обработка ред по ред, особено ако трябва да се позовавате на няколко таблици, за да получите нужните данни. Рутините, които включват "total" в набора от резултати, обикновено са по-бързи от тези, които се изпълняват с курсора. Ако времето за разработка позволява, могат да се изпробват както курсорни, така и множествени методи, за да се види кой работи по-добре.
11. Задайте SET NOCOUNT ON в началото на всички съхранени процедури и тригери, и SET NOCOUNT OFF в края. Няма нужда да се изпращат DONE_IN_PROC съобщения на клиента след изпълнение на всяко изявление на съхранената процедура и тригър.
12. Опитайте се да избягвате връщането на големи данни към клиента; ако обемът е твърде голям, трябва да прецените дали съответното търсене е разумно.
13. Опитайте се да избегнете големи транзакционни операции и да подобрите способността на системата за конкурентност.
|
Публикувано в 11.05.2018 г. 13:57:06 ч.
|
|
|
Публикувано в 17.05.2018 г. 10:12:27 ч.