Индекси на SQL Server - клуч за оптимално работење на базата на податоци
Повеќето сериозни деловни апликации сè уште користат системи за релациони бази на податоци, како што е SQL Server, за складирање на податоци. Така што на податоците зачувани таму исто така може да се пристапи со високи перформанси, индексите се многу одлучувачки фактор - од дизајнот на базата на податоци до барањето. Сепак, MS SQL Server сега нуди цела низа различни форми на индекси: Покрај класичните групирани и негрупирани индекси, постојат индекси за посебни области на примена како што се индекси со целосен текст, индекси на XML и просторни индекси. Покрај тоа, подоцна беа додадени индексите на столбови, првично дизајнирани за складишта на податоци, кои најдоцна во третата генерација исто така можат ефикасно да се користат за други области.
Општо како работат индексите
За подобро разбирање како работат индексите, корисно е да се разбере како SQL Server ги чува податоците на дискот. Податоците се зачувуваат во датотеки со наставка .mdf или .ndf. Овие се поделени на мемориски страници со големина од по 8 KB, што е исто така најмалата единица во која SQL Server чита или пишува податоци. Осум последователни страници за меморија формираат таканаречена мера. Додека мемориската страница содржи податоци само од една табела, додатоците се и униформни и мешани, кои содржат страници за меморија од различни табели. На пример, ако едно пребарување ги чита сите лица со одредено презиме од табелата што нема индекс, тој мора да ги прочита сите страници со меморија што содржат податоци на таа табела.
За да може поефикасно да се извршат овие пребарувања, соодветни структури се корисни, кои осигуруваат дека релевантните страници за меморија можат побрзо да се најдат, а со тоа не може да се читаат сите страници на меморија. Ова е токму она што го прават индексите.
Групирани индекси
Во наједноставниот случај, самата табела е подредена според одредена колона (во случај на табелата за лица, на пример, според презимето). Токму тоа го прави групиран индекс. Сепак, ова не е направено во форма на едноставна листа, туку во форма на бинарно дрво, во кое секој јазол на дрвото ја користи својата страница за меморија која содржи азбучно подредена листа на имиња, дополнета со упатување на страницата за меморија во која понатаму побарајте го ова име (или името што го следи по азбучен ред). Ова може - во зависност од бројот на записи на податоци во табелата - да продолжи во неколку фази додека конечно не се направи упатување на страницата за меморија во која се зачувани целосните лични податоци. Ова значи дека треба да се прочитаат само неколку страници со податоци пред да се најдат соодветните редови на табелата.
Додека презимето сигурно може да се појави неколку пати во табела со лица, во пракса обично се користи единствена колона за групиран индекс, што често одговара на примарниот клуч на табелата, т.е. клучот со кој сите редови на табелата можат јасно да се идентификуваат. Затоа, при креирање на примарен клуч, имплицитно се генерира групиран индекс за него, освен ако клучниот збор НЕКЛУСТЕРИРАН е специфично наведен (Список 1).
Најчестата варијанта за групиран индекс е Id колона од типот int (или bigint за големи табели) со спецификација на идентитет што гарантира дека на колоната автоматски assigned се доделуваат единствени броеви. Како алтернатива, често се користат колони од типот на единствен идентификатор, кои исто така се испорачуваат со единствени - но не и континуирани - вредности преку функцијата NewId () како стандардно ограничување. Ова е особено корисно во дистрибуирани средини, каде што се создаваат нови редови во различни копии на базата на податоци кои потоа се спојуваат без преклопување на примарните клучеви.
Главен недостаток на групирани индекси е тоа што тие можат да се сортираат само по еден редослед. Барање што пребарува на друга колона (како на пр. Име) нема да има корист од индекс на колоната Презиме. За да се реши ова, се користат n негрупирани индекси.
Приказ на планови за извршување
Со цел да се провери употребата на индекси во пребарувања, студиото за управување со SQL Server нуди можност за прикажување на очекуваните, како и на реалните планови за извршување на пребарувања. И двете опции можат да се активираат најлесно преку соодветните опции во менито за пребарување или алтернативно преку лентата со алатки. Треба да се напомене дека приказот на очекуваниот план за извршување не го извршува самото барање, додека реалниот план за извршување може да се прикаже само откако ќе се изврши барањето. Во графички прикажаните планови за извршување, кои може да се читаат од горниот десен агол, можете брзо да видите дали (и како) се пристапува до индекс или до самата табела. Со „како“ треба да се забележат клучните зборови „скенирање“ и „барање“. Индекс барање значи дека релевантните редови се наоѓаат со бинарно пребарување во индексот, додека скенирањето покажува дека табелата или индексот се прочитани во целост, што во повеќето случаи е значително повеќе време. Подетални информации за читање на плановите за спроведување може да најдете во упатствата за овој напис [1], [2], [3].
Не-групирани индекси
Не групираните индекси претставуваат дополнителна структура на податоци со која се управува и во структура на дрво, но чии лисја не содржат вистински податоци за линијата, туку упатување на адресата на податоците. Едноставно кажано, негрупиран индекс се однесува како индекс на крајот од книгата: Тој е сортиран сам за да можете брзо да пребарувате за одреден термин. Откако ќе се најде терминот, тој е проследен само со бројот на страницата каде што може да се најде терминот. Потребна е друга промена на соодветната страница за книги за да ги пронајдете саканите информации. Со индексот на базата на податоци, упатувањето на реалните податоци е секако малку покомплексно од обичниот број на страница. Овој процес, познат како пребарување на редови, користи идентификатор на редови (скратено RID), кој се состои од три дела:
- Број на датотека со база на податоци (бидејќи табелата може да се подели на неколку датотеки)
- Бројот на страницата за меморија
- Бројот на запис за податоци на оваа мемориска страница
Сепак, ова се однесува само ако табелата нема групиран индекс и затоа е зачувана како нередена „грамада“. Ако, пак, постои групиран индекс, клучната вредност на групираниот индекс е специфицирана како упатување на нивото на листот на негрупираниот индекс. Треба да се пресече друго дрво со индекси пред да се најдат вистинските податоци. (Сепак, дополнителниот напор за ова т.н. клучно разгледување обично е прилично мал).
Сл. 1: Дрво за индекс за табела без групиран индекс
Голема предност на негрупираните индекси е што може да има неколку од нив за табела, кои се подредени според различни колони. Ова значи дека потоа можете да пребарувате за различни критериуми со високи перформанси (Сл. 1).
Бидејќи индексите кои не се групирани се дополнителни структури на податоци, секогаш мора да се биде свесен за фактот дека, од една страна, тие бараат дополнителна меморија и, од друга страна, сите индекси што ги содржат променетите колони мора да се ажурираат секогаш кога ќе се променат самите податоци. Ако е можно, не треба да креирате посебен индекс за секоја колона од табелата, туку секогаш да мерите за кои колони има индекс и каде може да се распредели. Како основно правило, можете да запомните дека дополнителните индекси можат да го забрзаат пристапот за читање, но да ги забават пристапите за запишување. Одлуката за кои колони има индекс има смисла може да се донесе врз основа на следниве критериуми:
- Ако колоната често се пребарува или филтрира, тоа зборува во прилог на индекс.
- Истото важи и за колоните што се користат како колони за странски клучеви, бидејќи тие се филтрираат за време на JOIN.
- Ако колоната содржи само неколку различни вредности (една зборува и за мала селективност), ова повеќе зборува против индексот на неа. Ова е особено точно за колоните што користат тип на податоци за бит.
- Ако табелата главно се чита и само ретко се менува, прифатливи се повеќе индекси.
- Ако табелата е првенствено напишана (на пример, табела со дневник) и само ретко се чита, бројот на индекси треба да се чува што е можно понизок.
Комбинирани индекси
Еден начин да се одржи бројот на индекси со кои се управува низок, но исто така да се прават индекси употребливи што е можно поефикасно, е да се користат комбинирани индекси што се дефинирани преку неколку колони. На пример, ако има посебен индекс за колоните за име и презиме во табелата за лица, следното барање тешко дека ќе може да ги користи двата индекси:
Изберете * ОД Лице.Лице
КАДЕ Име = "'он" И Презиме = "Вуд"
Наместо тоа, SQL Server автоматски ќе го користи индексот од кој се очекува помал број страници што треба да се читаат (индексот на колоната со поголема селективност). Сепак, бидејќи може да има неколку записи и за името и за презимето, би било уште поефикасно да имате индекс што ги содржи обете колони, односно комбиниран индекс што може да се креира на следниов начин:
КРЕИРАЈТЕ НЕНКЛУСТЕРИРАН ИНДЕКС IX_Person_LastName_FirstName
ВО лице. Лице (презиме, име)
Додека името на индексот (IX_Person_LastName_FirstName) е само вообичаена конвенција за именување, редоследот на колоните во дефиницијата на индексот е од одлучувачко значење. Со ставање на презимето на прво место, индексот може да се користи ефикасно и кога пребарување бара само презиме (бидејќи ова е примарен критериум за подредување на индексот). Ако наместо тоа, барате само име, може да се користи и индексот, но не преку ефективниот пристап преку стеблото на бинарен индекс (барај индекс). Наместо тоа, треба да се прочита целиот индекс (скенирање на индекс) за да се најдат сите можни комбинации што го содржат името што го барате (што во повеќето случаи е сè уште поефикасно од читањето на целата табела без индекс).
Бидејќи пребарувањето само за името сигурно ќе се случи многу поретко во пракса отколку пребарувањето за презимето, погоре избраниот редослед на колони се препорачува и има несакан ефект што можете да го направите без посебните индекси за колоните за име и презиме.
Покривање индекси и вклучување колони
При објаснување на негрупираните индекси, беше посочено дека има упатувања на вистинските записи на податоци на ниво на лист од стеблото на бинарен индекс (или во форма на групиран индекс-клуч или како повеќеделна адреса составена од број на датотека, мемориска страница и број на линија). Идеално, сепак, сите испитувани колони се содржани во самиот индекс, затоа нема потреба да се следи оваа референца. Ако следното барање е извршено со постоечки комбиниран индекс на колоните Презиме и Име:
тогаш индексот може да се пребарува со помош на колоната Презиме, но тој веќе го содржи и прашаното Презиме за да може да се испушти нормално неопходниот преглед за целата линија на податоци. Потоа, се зборува за индекс на покривање (во однос на барањето), бидејќи тоа ги опфаќа сите колони за барањето.
Со цел да се искористи предноста на индексот на покривање што е можно почесто без премногу често да се прераспоредува самиот индекс, колоните можат да се интегрираат во индексот, таканаречени вклучуваат колони. Ова значи дека вредностите на овие колони се содржани на нивото на листот на индексот, но не се земени предвид за сортирање на индексот (затоа е доволно ако колоните постојат само на нивото на листот, а не на јазлите погоре). За барањето прикажано последно, следниот индекс би бил доволен:
Може да се дефинираат и неколку колони, чиј редослед е тогаш ирелевантен, бидејќи тие не се релевантни за сортирањето. Ако требаше да ги вклучите сите колони во индексот (или за сортирање или како што вклучуваат колони), ќе симулиравте групиран индекс, но со неповолност што потоа се користи просторот за повикување на самата податочна линија. што е непотребно во оваа варијанта, бидејќи е целосно содржано во индексот. Значи, ова дефинитивно не се препорачува, особено затоа што индексите можат поефикасно да се користат колку што се помали.
Филтрирани индекси
Од SQL Server 2008, постои друга можност со филтрираните индекси да го одржуваат индексот колку што е можно покомпактен и со тоа да го минимизираат бројот на мемориски страници што треба да се читаат. Индексот е дополнет со клаузула КАДЕ, така што индексот треба да се креира само за редовите што го исполнуваат наведениот услов. Ова, следствено, резултира со ограничување дека условите за филтрирање можат да се користат само во комбинација со не-групирани индекси.
Типичен случај на употреба за филтрирани индекси се табели со колони кои не се пополнети во голем дел од редовите:
КАДЕ Средното име не е ништовно
Индексирани прегледи
Од верзијата 2005 година, SQL Server исто така понуди опција за создавање индекси засновани на прегледи. Како резултат, податоците што ги бара прегледот се зачувуваат непотребно како копија, но се подредуваат според критериумите за индекс. Овој концепт честопати се нарекува материјализиран поглед (овој термин е особено чест во опкружувањето на Oracle). Главната предност на ваквиот индексиран поглед е што сите податоци што треба да се пребаруваат се веќе поврзани и се во правилен редослед на сортирање. Така, барањето треба да пристапи само до индексираниот приказ без претходно да ги спои податоците од неколку табели со користење на JOIN услови. Дополнителни прегледи на редови или клучеви исто така се изоставени затоа што - под услов да се создаде соодветно приказ - сите колони што треба да се пребаруваат се содржани во приказот, а со тоа и во индексот на него.
За да можете да користите индексирани прегледи, мора да се почитуваат неколку ограничувања:
- Самиот приказ мора да биде креиран со опцијата СО СЕМЕБИНДИНГ, што спречува промена на структурата на податоците на колоните што се користат во приказот.
- Индексот на приказот мора да биде креиран како ЕДИНСТВЕН КЛАСТЕРИЕН ИНДЕКС.
- Индексот не смее да се филтрира, но тоа не значи никакво реално ограничување, бидејќи критериумот за филтер лесно може да се смести во приказот што го користи индексот.
Список 2 покажува како може да се креира индексиран преглед во два чекори. Откако ќе се создаде поглед со додавањето SCHEMABINDING, се генерира UNIQUE CLASSERED INDEX за овој приказ. Ако сега извршите едноставно пребарување во погледот, од планот за извршување можете да утврдите дека е прочитан само индексот, а не табелите зад него:
Интересен несакан ефект на индексираните ставови е тоа што тие можат да се користат дури и ако табелите зад нив не се обраќаат експлицитно, туку прегледот. Ова може лесно да се потврди со разгледување на планот за извршување на следното барање:
Кога креирате план за извршување, SQL Server автоматски препознава дека има соодветен индексиран приказ до кој може да се пристапи побрзо отколку што се користат табелите и нивните индекси.
Заклучок
Во првиот дел од оваа серија написи, беа создадени основите и беше даден преглед на „класичните“ варијанти на индекс, кои се достапни подолго време. Следната рата од оваа серија ќе разгледа одржување на индекси и уште неколку напредни опции, како што е компресија на индекс.
Врски и литература
[1] Пантер, Роберт: „Оптимизирање на SQL пребарувања“, entwickler.press, јуни 2014 година, ISBN: 978-3868021233
[2] Пантер, Роберт: „SQL Server Performance Ratgeber“, entwickler.press, февруари 2010 година, ISBN: 978-3868020304
[3] Фритчи, Грант: „Планови за извршување на SQL сервер“, Објавување на едноставни разговори, октомври 2012 година, ISBN: 978-паметна блокада>
Програмер на Windows
Оваа статија е објавена во Windows Developer. Windows Developer обезбедува сеопфатни, неутрални од производители информации за новите трендови и можности за развој на софтвер и систем за технологиите на Microsoft.