Наша команда-партнер Artmisto
> SQL > [Пераклад] Саветы па аптымізацыі індэксаў
Апошні тыдзень на працы займаўся аптымізацыяй працы MS SQL базы дадзеных і наткнуўся на спіс карысных саветаў па аптымізацыі індэксаў у базе, ніжэй прыводжу пераклад гэтага артыкула. Многія з гэтых саветаў відавочныя, але не заўсёды рэальна ўжываюцца на практыцы.
арыгінал: http://www.mssqlcity.com/Tips/tipInd.htm
- Паспрабуйце стварыць індэкс па слупках, якія часта выкарыстоўваюцца ў выразах WHERE, ORDER BY, GROUP BY.
Гэтыя слупкі ідэальныя кандыдаты для стварэння індэксаў. Вам варта вельмі ўважліва прааналізаваць вашыя запыты, каб пазбегнуць стварэння бескарысных індэксаў. - Вашы індэксы павінны быць як мага менш.
З-за таго, што кожны індэкс займае дыскавая прастора, паспрабуйце мінімізаваць памер ключа індэкса, каб пазбегнуць празмернага памеру базы. Такім чынам паменшыцца колькасць аперацый чытання, неабходных для чытання індэкса і падвысіць агульную прадукцыйнасць азначніка. - Выдаляйце індэксы, якія не выкарыстоўваюцца.
Бо кожны індэкс займае дыскавая прастора і запавольвае ўстаўку, выдаленне і абнаўленне радкоў у табліцах, вам варта выдаляць невыкарыстоўваныя індэксы. Вы можаце выкарыстоўваць Index Wizard для вызначэння індэксаў, якія не выкарыстоўваюцца вашымі запытамі. - Паспрабуйце стварыць індэксы па слупках, якія маюць цэлыя, а не знакавыя значэння.
Таму што цэлыя значэння, як правіла, меншага памеру, чым знакавыя (памер тыпу INT - 4 байта, памер тыпу BIGINT - 8 байт), вы можаце паменшыць колькасць старонак індэкса, якія выкарыстоўваюцца для захоўвання ключоў индеса. Гэта паменшыць колькасць чытанняў, патрэбных для чытання індэкса і павялічыць агульную прадукцыйнасць. - Абмяжуйце колькасць індэксаў, калі ваша прыкладанне часта абнаўляе дадзеныя.
Бо кожны індэкс займае дыскавая прастора і запавольвае даданне, выдаленне і абнаўленне радкоў, таму вы павінны ствараць новыя індэксы толькі пасля аналізу выкарыстання дадзеных, тыпу і частоты выконваемых запытаў, і таго, як вашыя запыты будуць выкарыстоўваць новыя індэксы. У большасці выпадкаў перавагі ад стварэння новых індэксаў пераважваюць такія недахопы, як дадатковае якое выкарыстоўваецца прастору і павольныя мадыфікацыі дадзеных. Аднак, пазбягайце выкарыстання залішніх індэксаў, стварайце іх, толькі калі гэта неабходна. Для табліц, якія выкарыстоўваюцца выключна для чытання, лік індэксаў можа быць павялічана. - Пераканайцеся, што індэкс, які вы спрабуеце стварыць яшчэ не існуе.
Майце на ўвазе, што пры стварэнні першаснага ключа або унікальнага ключа SQL Server аўтаматычна стварае індэкс на слупкі, якія ўдзельнічаюць у вашым ключы. Калі вызначыць іншую назву індэкса, то можна ствараць індэксы для таго ж слупка зноў і зноў. - Стварайце кластарны індэкс замест некластерного для павелічэння прадукцыйнасці запытаў, якія вяртаюць дыяпазон значэнняў, і для запытаў, якія змяшчаюць GROUP BY або ORDER BY выказвання і якія вяртаюць адсартаваныя вынікі.
Бо кожная табліца можа ўтрымліваць толькі адзін кластарны індэкс, таму вам варта вельмі старанна выбіраць слупкі для індэкса. Паспрабуйце прааналізаваць усе вашыя запыты, выберыце найбольш часта выкарыстоўваюцца і ўключыце ў кластарны індэкс толькі тыя слупкі, якія забяспечваюць найбольшую перавагу ў прадукцыйнасці. - Стварайце некластерные індэксы для павелічэння прадукцыйнасці запытаў, якія вяртаюць нелсколько радкоў і там дзе індэксы маюць добрую выбіральнасць (selectivity).
Табліцы могуць утрымліваць да 249 некластерных індэксаў, у адрозненне ад кластэрнага індэкса, які можа быць толькі адзін для кожнай табліцы. Аднак, вам варта ствараць некластерные індэксы вельмі старанна, як і ў выпадку кластарнымі індэксамі, таму што кожны індэкс займае дыскавая прастора і ўплывае на хуткасць мадыфікацыі дадзеных. - Стварайце кластарны індэкс для слупкоў, якія не абнаўляецца вельмі часта.
Бо лісце ў дрэве некластеризованного індэкса ўтрымліваюць ключ кластэрнага індэкса, таму калі ў табліцы створаны кластарны індэкс, то кожны раз калі абнаўляюцца слупкі якія ўваходзяць у яго - усё некластерные індэксы таксама будуць абнаўляцца. - Стварайце кластарны індэкс на аснове аднаго слупка або як мага меншага іх колькасці.
Бо некластерные індэксы ўтрымліваюць ключ кластэрнага індэкса ў лісці дрэва і некластерные індэксы выкарыстоўваюць кластарны для пошуку, то стварэнне кластэрнага індэкса па адным слупка паменшыць памер не толькі кластэрнага індэкса, але і ўсіх некластерных. - Варта пазбягаць стварэння кластэрнага індэкса на слупках з автоинкрементом.
Напрыклад, калі ў табліцы автоинкрементальный першасны ключ і створаны кластарны індэкс па гэтым слупка, то кожны раз калі адбываецца ўстаўка ў гэтую табліцу, радкі будуць дадавацца ў канец табліцы. А калі будзе устаўляцца шмат радкоў, то можа адбыцца « hot spot «. «Hot spot» адбываецца калі шмат запытаў спрабуюць прачытаць і запісаць дадзеныя ў адну вобласць у адзін час. «Hot spot» адбываецца з-за таго, што прылады ўводу з'яўляюцца вузкім месцам.
Заўважце, што па-змаўчанню SQL Server стварае кластарны індэкс па слупках, якія ўваходзяць у першасны ключ. У гэтым выпадку вам варта відавочна паказаць, што вы хочаце стварыць некластерный індэкс для гэтых слупкоў. - Стварайце кластарны індэкс для кожнай табліцы.
Калі вы ствараеце табліцу без кластэрнага індэкса, то дадзеныя будуць захоўвацца неўпарадкаваных. Такая струкрута называецца куча. Кожны раз, калі дадзеныя будуць устаўляцца ў такую табліцу, радкі будуць дадавацца ў канец табліцы. У гэтым выпадку зноў можа адбыцца «hot spot». Для паляпшэння работы паралельных запытаў, вам варта ствараць кластарныя індэксы для кожнай табліцы. - Не стварайце індэксы па слупках з нізкай выбіральнасцю (selectivity).
Напрыклад, не стварайце індэкс па слупках, у якіх змяшчаецца шмат дублюючыхся дадзеных, напрыклад для слупок «Пол», які можа ўтрымліваць толькі значэння «Мужчынскі» і «Жаночы», бо у гэтым выпадку недахопы ад дадатковага занятага дыскавай прасторы і павольнай мадыфікацыі пераважваюць перавагі ў хуткасці пошуку па такім індэксе. - Калі вы ствараеце кампазітны індэкс, то паспрабуйце размяшчаць ключы індэкса ў парадку змяншэння выбіральнасці (selectivity), г.зн. ключ з найбольшай выбіральнасцю павінен быць самым левым.
Парадак слупкоў ў кампазітных азначніках вельмі важны, ён можа павялічыць шанец выкарыстання індэкса ў запытах. - Калі вы ствараеце кампазітны індэкс, то размяшчайце на пачатку ключы індэкса, якія часта выкарыстоўваюцца ў WHERE выразах.
Парадак слупкоў ў кампазітных азначніках вельмі важны. Індэкс будзе выкарыстоўвацца ў запыце, толькі калі стобцы, якія знаходзяцца ў WHERE, з'яўляюцца самымі левымі ключамі азначніка. Напрыклад, калі вы ствараеце кампазітны індэкс «Name, Age», то пры пошуку па Name індэкс будзе выкарыстаны, а пры пошуку па Age, ён выкарыстоўвацца не будзе. - Стварайце індэкс для слупкоў, якія часта выкарыстоўваюцца ў JOIN'ах.
Гэта можа істотна павялічыць прадукцыйнасць запытаў з JOIN'ами па гэтых слупкамі. - Падумайце над стварэннем цэлалікавага першаснага сурагатнага ключа.
Кожная табліца павінна ўтрымліваць першасны ключ (унікальны ідэнтыфікатар радкі ў табліцы). Сурагатным першасны ключ гэта поле, якое мае унікальнае значэнне, але не адносіцца ніяк да самай запісу, і карыстальнікі не павінны ніколі бачыць або змяняць такой першасны ключ. Некаторыя распрацоўшчыкі выкарыстоўваюць сурагатныя першасныя ключы, іншыя выкарыстоўваюць дадзеныя саміх палёў, як першасны ключ. Калі першасны ключ утрымлівае шмат палёў і мае вялікі памер - задумайцеся аб стварэнні сурагатнага ключа. Гэта можа павялічыць прадукцыйнасць вашых запытаў. - Разгледзіце магчымасць стварэння індэксаў па ўсіх слупках, якія выкарыстоўваюцца найбольш часта ў выразе WHERE, якое ўтрымлівае аператары АБО.
Калі ў выразе WHERE стрымаецца запыт, які змяшчае аператар АБО і калі з іх ні адзін слупок не будзе пакрыты індэксам, то запыце прыйдзецца цалкам прасканаваць табліцу або кластарны горада. Таму ў такіх выпадках, стварайце індэксы на ўсе такія слупкі, што істотна павялічыць прадукцыйнасць вашых запытаў. - Калі ваша прыкладанне выконвае запыты над табліцай вельмі часта, то разгледзіце пытанне аб стварэнні які пакрывае індэкса, які ўключае ўсе слупкі, патрабаваныя ў запыце.
Які пакрывае індэкс, гэта індэкс, які ўключае ўсе слупкі з запыту. Стварэнне такіх індэксаў можа павялічыць прадукцыйнасць, бо усе дадзеныя для замкні будуць ужо ўтрымлівацца ў індэксе, і не прыйдзецца атрымліваць з з табліцы. Якія пакрываюць індэксы могуць істотна павялічыць прадукцыйнасць запытаў, так як яны эканомяць на вялікай колькасці аперацый уводу / высновы. - Выкарыстоўвайце перыядычна інструкцыю DBCC DBREINDEX для таго, каб перабудаваць усе індэксы ва ўсіх табліцах для памяншэння фрагментацыі.
Фрагментаваныя дадзеныя могуць прымусіць SQL Server вырабляць дадатковыя непатрэбныя чытання, таму запыты на моцна фрагментаваным табліцах будуць выконвацца вельмі дрэнна. Так што варта переодически перабудоўваць індэксы для памяншэння фрагментацыі. Паспрабуйце запускаць DBCC DBREINDEX ў моманты, калі база моцна не нагружаная. - Выкарыстоўвайце інструкцыю DBCC INDEXDERRAG для дэфрагментацыі індэксаў табліц і ўяўленняў.
Гэтая інструкцыя з'явілася ў SQL Server 2000. У адрозненне ад DBCC DBREINDEX гэтая інструкцыя не блакуе дадзеныя надоўга, а таму яны не будзе блакаваць выкананне запытаў. Паспрабуйце выкарыстоўваць DBCC INDEXDERRAG замест DBCC DBREINDEX, калі гэта магчыма. - Паспрабуйце выкарыстоўваць параметр SORT_IN_TEMPDB пры стварэнні індэкса, калі tempdb знаходзіцца не на тым жа дыску, што і ваша база дадзеных
Параметр SORT_IN_TEMPDB быў уведзены ў SQL Server 2000. Калі вы ствараеце індэкс з гэтым параметрам, то SQL Server выкарыстоўвае tempdb для сартавання дадзеных пры стварэнні індэкса, замест бягучай базы дадзеных. Гэты параметр дазваляе хутчэй стварыць індэкс, але павялічвае колькасць занятага дыскавай прасторы пры стварэнні індэкса. - Выкарыстоўвайце Create Trace Wizard ў SQL Server Profile з параметрам «Identity Scans of Large Tables» для вызначэння табліц у базе, якім неабходныя індэксы.
Вынікі пакажуць якія табліцы не выкарыстоўваюць індэксы.
55.755786 37.617633
