SQL уяўляе сабой апісальны, дэкларатыўны мова. Гэта значыць, што запыты SQL як бы задаюць пытанні, a SQL Server самастойна вырашае, як на іх адказаць. Зыходзячы з гэтага, ільвіная доля аптымізацыі выконваецца на ўзроўні SQL Server, а не саміх запытаў.
Разглядаючы прыкладную кошт кожнай лагічнай аперацыі на аснове размеркавання дадзеных, даступных індэксаў і магчымасцяў абсталявання, аптымізатар запытаў (Query Optimizer) будуе дрэва лагічных аперацый, якія забяспечваюць найхутчэйшага выкананне пастаўленай задачы, г.зн. агульны план выканання запыту.
Такім чынам, аптымізацыя запытаў большай часткай з'яўляецца пытаннем стварэння правільных індэксаў, такіх, каб аптымізатар запытаў змог адабраць дадзеныя найбольш хуткім спосабам. Толькі ў выключных выпадках змяненне структуры запыту можа паўплываць на прадукцыйнасць. У той жа час большасць вытворчых запытаў, напісаных трыма рознымі спосабамі, вернуць ідэнтычныя (а то і цалкам супадаючыя) планы выканання.
Настройка індэксаў з'яўляецца навыкам, якія патрабуюць глыбокага разумення некалькіх незалежных фактараў. На самай справе наладу запытаў ўскладняе тое, што сам аптымізатар запытаў з'яўляецца чымсьці накшталт чорнай скрыні. І перад тым як вы зможаце прадказаць яго паводзіны, вы павінны зразумець ўзаемасувязь наступных фактараў.
? Старонкавая архітэктура SQL Server, мадэль фізічнай схемы і лагічныя аператары плана выканання запыту.
? Размеркаванне дадзеных, статыстыка індэксаў, выбар індэксаў аптымізатарам запытаў і забеспячэнне абслугоўвання індэксаў.
? Кластеризованная структура індэксаў, каэфіцыент запаўнення, зліццё старонак і абслугоўванне індэксаў.
? Запыты, індэксы, планы выканання запытаў і аптымізатар запытаў.
? Паўторнае выкарыстанне плана запыту і прымяненне параметраў.
Як распрацоўшчык або адміністратар баз дадзеных вы павінны разумець, якія індэксы з'яўляюцца лепшымі сярод масы вытворчых запытаў, а не толькі аднаго запыту.
Самае лепшае, што я магу парэкамендаваць, - гэта вывучыць усе фактары, а затым правесці ўласныя эксперыменты, каб зразумець усе складанасці карэляцыі паміж дадзенымі, схемай, індэксамі і запытамі. Карэкціроўка індэксаў не аказвае ніякага ўплыву на лагічны вынік любога запыту. Адміністратарам баз дадзеных не варта баяцца эксперыментаваць з індэксамі.
індэксацыя
Прадметны паказальнік кнігі дапамагае чытачам хутка знайсці адказ на які цікавіць іх пытанне. Лепшым прадметным паказальнікам лічыцца той, які адкрывае чытачу самы кароткі шлях ад пастаноўкі ім пытання да знаходжання адпаведнага тэксту ў кнізе. Індэксы базы дадзеных гуляюць такую ж ролю.
Нягледзячы на тое што індэксы могуць быць складанымі, мэта індэксацыі простая: скарачэнне колькасці чытэльных фізічных старонак.
асновы індэксацыі
Існуюць два асноўных тыпу індэксаў. Прадметны паказальнік, які фізічна сартуе тэкставыя старонкі, падобна тэлефоннай кнізе, называецца кластеризованным, бо самі дадзеныя выбудаваныя ў спецыфічным парадку. Гэты тып прадметнага паказальніка фізічна знаходзіцца ў тэксце кнігі. Натуральна, тэкст можа быць адсартаваны ў дадзеным выпадку толькі ў адным парадку. Аналагічна, табліцы SQL Server могуць мець толькі адзін фізічны парадак сартавання, і менавіта ён называецца кластеризованным індэксам табліцы.
Некластеризованный індэкс падобны прадметнаму паказальніку ў канцы кнігі - ён адпраўляе чытача на пэўныя старонкі. Любое пытанне можа быць без працы знойдзены ў тэксце, калі спачатку знайсці яго ў прадметным паказальніку, а затым адправіцца па спасылцы на пэўную старонку. SQL Server таксама размяшчае некластеризованными індэксамі, якія выконваюць сартаванне, выкарыстоўваючы слупкі, адрозныя ад кластеризованного азначніка.
Стоўбцы, якія выкарыстоўваюцца ў азначніках, называюць ключавымі.
Так як першасныя ключы рэалізуюць унікальны метад ідэнтыфікацыі любой радкі, індэксы і першасныя ключы ўзаемазвязаны - першасны ключ павінен быць індэксаваць, але ў той жа час ён можа быць кластеризованным або некластеризованным.
Пры планаванні індэксаў існуе некаторы напружанне ў адносінах паміж абслугоўваннем запытаў адбору і запытаў абнаўлення. Нягледзячы на тое што індэксы павышаюць прадукцыйнасць запытаў, за гэта прыходзіцца расплачвацца дадатковымі выдаткамі на абнаўленне індэксаў пры ўстаўцы і абнаўленні радкоў. Аднак некаторыя індэксы неабходныя пры аперацыях запісу. Аперацыі абнаўлення і выдалення перад ажыццяўленнем фізічнай запісы павінны адабраць радкі, а добрыя індэксы могуць паскорыць гэты адбор, адначасова павышаючы эфектыўнасць самой запісу.
Дадатковая Індэксы табліц нельга блытаць з індэксавацца ўяўленнямі. Редак- інфармацыя цыя Enterprise Edition змяшчае функцыю денормализации, якая будуе кластеризованный індэкс, які распаўсюджваецца на мноства табліц. Няправільнае выкарыстанне індэксаваная уяўленняў можа істотна панізіць прадукцыйнасць аперацыйных баз дадзеных. У разьдзеле 53 мы больш падрабязна пагаворым аб індэксаваная уяўленнях.
Кластеризованные індэксы
Кластеризованный індэкс ўяўляе сабой інструмент зліцця збалансаванасць дрэва са старонкамі дадзеных табліц, які падтрымлівае дадзеныя ў тым жа фізічным парадку, што і індэкс (мал. 50.1), Ліставыя вузлы збалансаванага дрэва індэкса фактычна з'яўляюцца дадзенымі старонак дадзеных.
Мал. 50.1. Кластеризованный індэкс звязвае ліставыя вузлы старонак індэксаў са старонкамі дадзеных, забяспечваючы той жа парадак дадзеных, што і ў індэксе
Кластеризованный індэкс можа паўплываць на прадукцыйнасць адным з апісаных ніжэй спосабаў.
? Калі аперацыя пошуку знаходзіць радок, выкарыстоўваючы кластеризованный індэкс, патрэбныя дадзеныя знаходзяцца менавіта ў ёй. Гэта робіць слупок, які выкарыстоўваецца для ідэнтыфікацыі радкоў, магчымым кандыдатам для першаснага ключа і ідэальным - для кластеризованного азначніка.
? Кластеризованные індэксы групуюць радкі з аднолькавымі або падобнымі значэннямі ў найменшую магчымую колькасць старонак, скарачаючы тым самым колькасць радкоў дадзеных, неабходных для здабывання набору радкоў. Такім чынам, кластеризованные індэксы ідэальна падыходзяць для слупкоў, якія найбольш часта выкарыстоўваюць для адбору дыяпазонаў радкоў (у якасці прыкладу можна прывесці слупок OrderDetail. OrderlD).
/ Любая табліца мае некаторы фізічны парадак. Калі табліца не мае
На заметку кластеризованного індэкса, то яна знаходзіцца ў выглядзе кучы, г.зн. у неўпарадкаваных выглядзе. У гэтым выпадку радкі, пры немагчымасці ідэнтыфікацыі па ключавым слупка кластеризованного індэкса, ідэнтыфікуюцца з дапамогай ўнутранага ідэнтыфікатара радка (RowiD) кучы. Гэты ідэнтыфікатар не выкарыстоўваецца ў запытах.
Некластеризованные індэксы
Некластеризованным называецца індэкс ў выглядзе збалансаванага дрэва, што пачынаецца з каранёвага вузла і які расце праз прамежкавыя вузлы да ліставым вузлах. Ліставыя вузлы паказваюць непасрэдна на радкі старонак дадзеных (гл. Мал. 50.1). Табліца SQL Server 2005 можа мець да 249 ^ кластеризованных індэксаў, але мне на практыцы не сустракаліся табліцы, якія патрабуюць больш дзесяці добра прадуманых індэксаў.
Некластеризованный індэкс можа быць створаны па вылічае слупка. Для ўключэння магчымасці стварэння індэкса або індэксаваная прадстаўлення па вылічае слупка варта ўсталяваць для параметру quoted_identif ier значэнне on.
стварэнне індэксаў
У акне Object Explorer ўтыліты Management Studio існуючыя індэксы кожнай табліцы пералічаныя пад вузлом Databases ^ Tables ^ Indexes. Ўласцівасці кожнага новага або існуючага індэкса можна карэктаваць у дыялогавым акне Index Properties (мал. 50.2). Гэта акно адкрываецца для існуючага індэкса пстрычкай правай кнопкі мышы на яго імя і выбарам у кантэкстным меню пункта Properties. Новыя індэксы ствараюцца з дапамогай кантэкстнага меню вузла Indexes канкрэтнай табліцы.
У утыліце Management Studio індэксы адлюстроўваюцца як вузлы на панэлі Object Explorer. З дапамогай выбару ў кантэкстным меню вузла Indexes пункта New Index можна стварыць новы індэкс. У адкрываецца пры гэтым форме ўтрымліваюцца чатыры ўкладкі.
? General. Зьмяшчае назву індэкса, яго тып, ўласцівасць унікальнасці, а таксама ключавыя слупкі.
? Options. Кіруе рэжымам працы азначніка. Тут жа любы індэкс можа быць адключаны і зноў уключаны.
? Included Columns. Змяшчае неключевых слупкі, служачыя абалонкай азначніка.
? Storage. Дазваляе змясціць індэкс ў выбраную файлавую групу.
Puc. 50.2. Параметры любога індэкса можна ўсталяваць у дыялогавым акне Index Properties ўтыліты Management Studio
Пры адкрыцці акна параметраў існуючага індэкса ён ўтрымлівае таксама і дзве дадатковыя ўкладкі.
? Fragmentation. Адлюстроўвае дэталёвую інфармацыю аб стане азначніка.
? Extended Properties. Ёсць дадатковыя параметры, вызначаныя карыстальнікам.
Змены, якія ўносяць у акне параметраў індэкса, можна прымяніць неадкладна з дапамогай пстрычкі на кнопцы Аб Да; іх ужыванне можна адкласці на зададзены час, а таксама ўвасобіць ўсе змены ў сцэнар. Для гэтага можна скарыстацца значкамі ў верхняй частцы акна.
У праграмным кодзе індэксы ствараюцца з дапамогай інструкцыі CREATE INDEX. У наступным прыкладзе ствараецца кластеризованный індэкс IxOrderld, заснаваны на знешнім ключы OrderlD табліцы OrderDetail:
CREATE CLUSTERED INDEX IxOrderlD ON dbo.OrderDetail (OrderlD);
I Для здабывання вычарпальнай інфармацыі пра індэксы з дапамогай пра-
S VS граммного кода выкарыстоўваюць наступныя функцыі і прадстаўлення каталогаў:
I * sysindexes, sysindex_columns, sysstats, sysstats_columns, sysdm_db_
* Index_physical_stats, sysdm_index_operational_stats, sysindexkey_
property і sysindex_col.
Кластеризованный індэкс ствараецца аўтаматычна пры вызначэнні першаснага ключа. Для выдалення індэкса выкарыстоўваецца інструкцыя DROP INDEX, у якой паказваецца імя табліцы і імя індэкса, напрыклад:
DROP INDEX OrderDetail.IxOrderlD
Дополнитешрая Створаныя індэксы не падтрымліваюць эфектыўнае стан аўтаматычна, інфармацыя Аперацыі абнаўлення могуць фрагментаваць індэксы і змяняць коэффици- ент запаўнення іх старонак. У гэтай частцы мы толькі згадваем пра неабходнасць абслугоўвання індэксаў, тогдп як у главы 37 распавядалася пра патрабаванні, неабходных для забеспячэння прадукцыйнасці індэксаў.
складовыя індэксы
Састаўным называюць кластеризованный або некластеризованный індэкс, які ўключае мноства слупкоў. На практыцы большасць індэксаў з'яўляюцца складовымі. Калі вы выкарыстоўваеце акно параметраў індэкса ўтыліты Management Studio, то складовыя індэксы ствараюцца з дапамогай дадання мноства слупкоў ва ўкладцы General. Для стварэння складовага індэкса ў праграмным кодзе ён павінен быць абвешчаны з дапамогай інструкцыі DDL CREATE INDEX пасля стварэння табліцы. У наступным прыкладзе ствараецца складовай кластеризованный індэкс для табліцы GUIDE базы дадзеных СНА2:
CREATE CLUSTERED INDEX IxGuideName ON dbo.Guide (LastName, FirstName);
Парадак слупкоў у складовым індэксе вельмі важны. Каб пры пошуку атрымаць усе перавагі складовага індэкса, апошні павінен змяшчаць найбольш часта выкарыстоўваюцца слупкі ў напрамку злева направа. Калі складовай індэкс выглядае як lastname, firstname, то пошук толькі па firstname не будзе выкарыстоўваць індэкс, а пошук па lastname або сумесна па lastname і firstname - будзе.
Дадатковая SQL Server 2005 можа індэксаваць словы ў слупках з дапамогай функцыі інфармацыя паўнатэкставага пошуку, якую мы абмяркоўвалі ў чале 13.
першасныя ключы
Першасны ключ можа быць першапачаткова вызначаны як кластеризованный або некластеризованный горада. Аднак, каб змяніць тып індэкса, абмежаванне першаснага ключа мусіць быць выдаленае або зноў створана. Гэта досыць складаная задача, калі прысутнічае мноства знешніх ключоў або калі табліца реплицируется.
Дадатковая Аб стварэнні першасных ключоў гл. У частцы 17.
інфармацыя
якія пакрываюць індэксы
Пакрывае называюць любы індэкс, які цалкам задавальняе патрэбам канкрэтнага запыту SELECT. Так як СКБД SQL Server сама выбірае, які індэкс выкарыстоўваць для пошуку дадзеных, існуе верагоднасць, што ў некластеризованном індэксе запыце спатрэбяцца ўсе слупкі. У дадзеным выпадку рэляцыйнае ядро будзе здабываць дадзеныя з індэксных старонак і ніколі не будзе ажыццяўляць чытанне са старонак дадзеных. Гэта істотна скарачае аперацыі ўводу-высновы, так як чытанне ажыццяўляецца з больш вузкіх табліц, якія змяшчаюць на адной старонцы больш дадзеных, а дадатковае чытанне са старонак дадзеных наогул не выконваецца.
Пры праектаванні які пакрывае індэкса вельмі важна ўсведамляць, як кластеризованный індэкс ўплывае на некластеризованный. Паколькі некластеризованный індэкс павінен мець здольнасць звяртацца да старонак дадзеных, на сваіх ліставых вузлах ён павінен змяшчаць ключавой слупок кластеризованного індэкса (калі ў табліцы такі існуе). Пры гэтым слупкі кластеризованного індэкса ўключаюцца ў канец кожнага некластеризованно- га індэкса (нават калі вы іх не бачыце відавочна ў дыялогавым акне уласцівасцяў індэкса). Напрыклад, калі некаторая табліца мае кластеризованный індэкс па слупка ContactID і некластеризованный па слупках LastName і FirstName, то некластеризованный індэкс ўтрымлівае дадзеныя з слупкоў LastName, FirstName (адсартаваныя), а таксама ContactID (неотсортированные). Веданне гэтага факту вельмі важна пры праектаванні якія пакрываюць індэксаў.
Які пакрывае індэкс можа мець неабходнасць ўключаць некаторыя слупкі, якія не патрабуюць рэляцыйнае ядро для ідэнтыфікацыі радкоў, адсяваць прапановай WHERE. Гэтыя дадатковыя, неключевых слупкі не адчуваюць патрэбнасць ў сартаванні у збалансаваным дрэве індэкса - яны ўключаюцца выключна з мэтай вяртання слупкоў запытам SELECT.
Недахопам якія пакрываюць індэксаў у папярэдніх версіях SQL Server было Навінка 4 тое, што пры абнаўленнях павінны былі сартуюцца ўсе слупкі - нават тыя, 2005 якія не выкарыстоўваліся пры адборы радкоў, а былі ўключаныя выключна
для вяртання дадзеных. Здольнасць версіі SQL Server 2005 аддзяляць неключевых слупкі падвысіла прадукцыйнасць аперацый абнаўлення з выкарыстаннем пакрываюць індэксаў, у якіх не сартуюцца неключевых слупкі. Пры гэтым за кошт памяншэння памеру збалансаванага дрэва індэкса палепшылася прадукцыйнасць вымання дадзеных запытам.
Для вызначэння неключевых слупкоў у некластеризованных азначніках выкарыстоўваецца параметр INCLUDE. Гэтыя неключевых слупкі ня сартуюцца як частка структуры збалансаванага дрэва індэкса і ўключаюцца толькі ў яго ліставыя вузлы. У наступным прыкладзе ствараецца індэкс, сартуюцца дадзеныя па слупка OrderNumber і ўключае даныя з слупка OrderDate:
CREATE NONCLUSTERED INDEX IxOrderNumber ON dbo. [Order] (OrderNumber)
INCLUDE (OrderDate);
Дзякуючы ўключаем слупках як вузкай копіі шырокай табліцы, якія SQL Server пастаянна сінхранізуе з зыходнай табліцай, для вымання дадзеных не патрабуецца чытанне дадатковых старонак.
Якiя ўключаюцца слупкі не ўлічаны ў абмежаваннях некластеризованного индекса- 16 ключавых слупкоў і 900 байтаў. На самай справе ў пакрывае індэкс можна ўключыць да 1023 неключевых слупкоў. У якасці ўключаюцца могуць быць таксама і слупкі з асабліва буйнымі тыпамі дадзеных - XML, varchar (max), nvarchar (max) і varbinary (max), - нават нягледзячы на тое, што яны не могуць выступаць у ролі ключавых слупкоў.
Слупок табліцы нельга падзяліць, калі ён удзельнічае ў ролі уключанага слупка ў пакрывае індэксе. Перад тым як падзяліць такі слупок, варта выдаліць які пакрывае горада.
Месцазнаходжанне файлавай групы
Калі база дадзеных выкарыстоўвае мноства найменных файлавых груп, то індэкс можа быць створаны ў пэўнай з іх. Для гэтага ў вызначэнні індэкса выкарыстоўваецца параметр
ON имя_файловой_группьг.
CREATE NONCLUSTERED INDEX имя_индекса ON Table (слупкі)
ON имя_файловой_группы;
Гэты параметр можа апынуцца карысным для размеркавання патокаў ўводу-высновы ў асабліва моцна загружаных базах дадзеных. Напрыклад, калі Web-сайт запытваецца мільённым карыстальнікаў у хвіліну, яго галоўная старонка выкарыстоўвае запыт, які ўключае дзве табліцы і тры індэкса, і пры гэтым даступна некалькі дыскавых падсістэм, то памяшканне кожнай табліцы і яе індэксаў ва ўласную дыскавую падсістэму значна павысіць прадукцыйнасць. Варта заўважыць, што кластеризованный індэкс павінен знаходзіцца ў адной дыскавай падсістэме са звязанай з ім табліцай, так як іх старонкі аб'яднаныя.
Дадатковая Фізічнае размяшчэнне табліц і індэксаў можа быць сканфігуравана і інфармацыя больш глыбока, з выкарыстаннем файлавых груп і раздзелаў. Больш падрабязна _ гэтыя тэмы мы абмяркуем у главе 53.
параметры індэксаў
Індэксы SQL Server 2005 маюць некалькі параметраў, у тым ліку унікальнасць, вылучэнне прасторы, а таксама параметры прадукцыйнасці.
унікальныя індэксы
Параметр UNIQUE INDEX з'яўляецца чымсьці вялікім, чым проста індэксам з абмежаваннем на унікальнасць, - унікальным індэксах даступная аптымізацыя. Першасны ключ ці абмежаванне на унікальнасць аўтаматычна ствараюць унікальны горада.
У Management Studio унікальны індэкс ствараецца з дапамогай ўстаноўкі сцяжка Unique ва ўкладцы General дыялогавага акна параметраў індэкса.
У праграмным кодзе унікальнасць індэкса паказваецца з дапамогай дадання ў вызначэнне ключавога слова UNIQUE:
CREATE UNIQUE INDEX OrderNumber ON [Order] (OrderNumber);
Каэфіцыент запаўнення індэкса
Любому індэксе трэба трохі вольнай прасторы ў дрэве, каб ўстаўка новых запісаў не прыводзіла да рэструктурызацыі азначніка. Калі серверу трэба ўставіць новую запіс у запоўненую старонку, ён разбівае гэтую старонку на дзве, пасля чаго запісвае дзьве напалову поўныя старонкі на дыск. Такі ход рэчаў выклікае тры патэнцыйныя праблемы: разбіваецца сама старонка, новыя старонкі больш не з'яўляюцца паслядоўнымі, і на кожнай старонцы змяшчаецца меншы аб'ём інфармацыі. У выніку для чытання таго жа аб'ёму дадзеных спатрэбіцца праглядзець большую колькасць старонак.
Паколькі індэкс ўяўляе сабой збалансаванае дрэва, кожная старонка павінна ўтрымліваць як мінімум два радкі. Каэфіцыент запаўнення і параметр pad index ўплываюць як на прамежкавыя старонкі, так і на ліставыя вузлы, як паказана ў табл. 50.1.
Табліца 50.1. Каэфіцыент запаўнення і параметр pad index
каэфіцыент
запаўнення
прамежкавыя старонкі
ліставай вузел
0
Адна свабодная запіс
100% -ною запаўненне
1-99
Адна свабодная запіс або аб'ём, меншы каэфіцыента запаўнення, калі ўсталяваны параметр pad index
Аб'ём, меншы каэфіцыента запаўнення
100
Адна свабодная запіс
100% -ною запаўненне
Каэфіцыент запаўнення ужываецца толькі да ліставым вузлах індэкса, калі да яго не ужыты параметр pad index. Гэты параметр паказвае серверу ўжываць слабасць каэфіцыента запаўнення таксама і да прамежкавым ўзроўнях збалансаванага дрэва.
Найлепшы каэфіцыент запаўнення залежыць ад прызначэння базы дадзеных і тыпу кластеризованного азначніка. Калі база дадзеных у асноўным прызначана для здабывання дадзеных або першасны ключ з'яўляецца паслядоўным, то высокі каэфіцыент запаўнення дазволіць максімальна спакаваць індэксны старонкі. Калі кластеризованый індэкс ня паслядоўны (напрыклад, натуральны першасны ключ), то табліца патэнцыйна ўспрымальная да падзелу старонак, - у гэтым выпадку выкарыстоўвайце нізкі каэфіцыент запаўнення і часта выконвайце дэфрагментацыю старонак.
Дадатковая Каэфіцыент запаўнення індэкса па меры падзелу старонак паступова ут- икформация рачивает сваю ролю. Для аднаўлення каэфіцыента запаўнення план абслугоўвання павінен ўключаць у сябе перыядычную реиндексацию. Інфармацыя аб парадку падтрымкі індэксаў змяшчаецца ў чале 37.
У Management Studio каэфіцыент запаўнення усталёўваецца ва ўкладцы Options дыялогавага акна параметраў індэкса. У праграмным кодзе Т-SQL параметры каэфіцыента запаўнення і index pad паказваюцца пасля каманды CREATE INDEX. У наступным прыкладзе ствараецца індэкс OrderNumber з 15% вольнай прасторы як на ліставых вузлах, так і на прамежкавых старонках:
CREATE NONCLUSTERED INDEX IxOrderNumber ON dbo. [Order] (OrderNumber)
WITH FILLFACTOR = 85, PAD_INDEX = ON;
Кіраванне блакаваннем індэксаў, іх стварэнне ў рэальным часе і ограни- Норинка меньванне паралелізму індэксаў з'яўляюцца новымі параметрамі, якія з'явіліся
2005 у версій SQL Server 2005. Для параметраў pad_index, fillfactor, sort_in_db,
ignore_dup_key, statistiсs_norecompute і drop_existing быў зменены сінтаксіс. Новы сінтаксіс патрабуе абавязковага ўключэння выразы = оп. З меркаванняў зваротнай сумяшчальнасці працягвае падтрымлівацца і стары сінтаксіс, аднак у будучых версіях гэтага ўжо не будзе.
Абмежаванне блакаванняў і паралелізму
Рэжым працы блакаванняў у запытах, якія выкарыстоўваюць індэксы, можа кіравацца з дапамогай параметраў allow_row_locks і allow_page_locks. Звычайна гэтыя блакаванні дазволеныя.
Парадак сартаваньня індэксаў
СКБД SQL Server здольная ствараць сыходныя індэксы. Любы запыт, які выкарыстоўвае прапанову ORDER BY, прывядзе да сартавання па ўзрастанні, калі ў гэтым сказе не будзе відавочна паказана ключавое слова DESC.
У інструкцыі DDL CREATE INDEX ключавыя словы AS С і DESC вынікаюць непасрэдна за імем слупка.
Параметр ігнаравання дублюючыхся ключоў
Параметр I GNORE_DUP_KEY не аказвае ўплыву на сам індэкс, а толькі на тое, як пасля індэкс будзе ўплываць на змены дадзеных.
Звычайна транзакцыі з'яўляюцца атамарнага. Гэта значыць, што транзакцыя альбо выконваецца, альбо няма, як лагічная адзінка. У той жа час параметр ігнаравання дублюючыхся ключоў накіроўвае транзакцыі ўстаўкі на пацверджанне аперацыі для ўсіх радкоў, якія задавальняюць умове унікальнасці індэкса, і ігнараванне ўсіх радкоў, якія парушаюць патрабаванне унікальнасці азначніка.
Гэты параметр не парушае унікальнасць азначніка. Дубляваныя запісу застаюцца ў табліцы, такім чынам, цэласнасць базы дадзеных застаецца некранутай, у той час як атамарнага транзакцый парушаецца. Нягледзячы на тое што гэты параметр значна палягчае імпарт трыльёнаў сумніўных радкоў, мне не падабаюцца ніякія параметры, якія саслабляюць характарыстыкі Асю (г.зн. атамарнага, цэласнасці, ізаляванасці і жывучасці) базы дадзеных.
У наступным прыкладзе паўтараецца папярэдняя інструкцыя стварэння унікальнага індэкса, але ў ёй выкарыстаны параметр ігнаравання дублюючыхся ключоў:
CREATE UNIQUE INDEX OrderNumber ON [Order] (OrderNumber)
WITH IGNORE_DUP_KEY = ON
Параметр выдалення існуючага індэкса
Параметр DROP_EXISTING паказвае серверу выдаліць бягучы індэкс і ўзнавіць яго з нуля. Гэта можа прывесці да некаторага выйгрышу ў параўнанні з перабудовай існуючага індэкса ў выпадку, калі ён з'яўляецца кластеризованным, а табліца змяшчае таксама і некластеризованные індэксы. Гэта звязана з тым, што перабудова кластеризованного індэкса прыводзіць да аўтаматычнай перабудове усіх некластеризованных індэксаў.
Параметр забароны пераліку статыстыкі індэкса
Аптымізатар запытаў SQL Server залежыць ад статыстыкі размеркавання дадзеных у пытанні вызначэння, які з індэксаў з'яўляецца больш істотным у канкрэтным крытэры пошуку ў табліцы. Звычайна SQL Server абнаўляе гэтую статыстыку аўтаматычна. У той жа час непасрэдна перад запытам некаторыя табліцы могуць атрымаць вялікі аб'ём дадзеных, і ў гэтым выпадку статыстыка можа апынуцца састарэлай. Спецыяльна для сітуацый, у якіх статыстыка патрабуе абнаўлення ўручную, прызначаны параметр statistics_norecompute, які адмяняе аўтаматычнае абнаўленне статыстыкі. Практычна для ўсіх індэксаў гэты рэжым працы не рэкамендуецца.
Сартыроўка ў базе tempdb
Параметр sort_in_tempdb = on змяняе метад стварэння індэкса, фарсіруючы выкарыстанне базы tempdb, а не памяці. Калі індэкс пастаянна выдаляецца і узнаўляецца, то гэты параметр здольны скараціць час стварэння індэкса. У большасці індэксаў гэты параметр не мае вялікага значэння, да таго ж ён не абавязковы.
адключэнне індэкса
Любы індэкс можа быць часова адключаны. Для гэтага досыць зняць сцяжок Use Index ва ўкладцы Option дыялогавага акна параметраў індэкса. У праграмным кодзе T-SQL гэты эфект дасягаецца з дапамогай ўключэння параметру DISABLE ў інструкцыю DDL ALTER INDEX:
ALTER INDEX [IxContact] ON [dbo]. [Contact] DISABLE
У некаторых асабліва інтэнсіўных аперацыях імпарту аказваецца хутчэй выдаліць індэкс і узнавіць яго, чым абнаўляць яго пры ўстаўцы кожнага радка. Перавага адключэння індэкса заключаецца ў тым, што метададзеныя індэкса абслугоўваюцца ў самой базе дадзеных, а не залежаць ад праграмнага кода, вобразнага карэктны горада.
Адключэнне кластеризованного індэкса фактычна прыводзіць да адключэння Увага! ўсёй табліцы.
Каб зноў ўключыць індэкс, выкарыстоўваецца каманда ALTER INDEX. . . REBUILD WITH:
ALTER INDEX [PK____ Contact 0BC6C43E]
ON [dbo]. [Contact]
REBUILD WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW ROW LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
SORT_IN_TEMPDB = OFF,
ONLINE = OFF)
Стварэнне базавых індэксаў
Нават перад наладай месцазнаходжанне некалькіх індэксаў лёгка вызначыць. Гэтыя базавыя індэксы з'яўляюцца першым крокам у стварэнні суцэльнага набору індэксаў. Ніжэй пададзены некалькі рэкамендацый, якімі варта скарыстацца перад стварэннем гэтых базавых індэксаў.
1. Стварыце кластеризованный індэкс для кожнай з табліц. У першасных табліцах такой індэкс лепш арганізаваць на слупках, найбольш верагодна выкарыстоўваюцца для выбару радкоў, - выдатным кандыдатам з'яўляецца першасны ключ. Ць другасных табліцах, у якіх часцей за ўсё здабываецца мноства звязаных радкоў, стварайце кластеризованный ключ для найбольш важнага вонкавага ключа, групуюцца разам гэтыя звязаныя слупкі.
2. Стварыце некластеризованные індэксы для слупкоў кожнага вонкавага ключа, за выключэннем тых, якія былі праіндэксаваныя ў п. 1. У якасці ключоў індэкса выкарыстоўвайце толькі значэння вонкавага ключа.
3. Стварыце одностолбцовый індэкс для ўсіх слупкоў, якія найбольш верагодна будуць з'яўляцца ў прапановах WHERE, ORDER BY або GROUP BY.
У той час як прапанаваны план індэксацыі далёкі ад дасканаласці, ён прапануе першапачатковы кампраміс паміж адсутнасцю індэксаў і настроенымі індэксамі. У прынцыпе яго можна разглядаць як базавы ўзровень прадукцыйнасці ў адносінах да наступнай наладзе індэксаў.
Крыніца: Нільсэн, Пол. Microsoft SQL Server 2005. Біблія карыстальніка. : Пер. в е англ. - М .: таа "І.Д. Вільямс", 2008. - 1232 с. : Іл. - пару. Ціт. англ.
