Индексите на SQL Server - ключът към оптималната производителност на базата данни

Повечето сериозни бизнес приложения все още използват релационни бази данни като SQL Server за съхранение на данни. Така че данните, съхранявани там, също могат да бъдат достъпни с висока производителност, индексите са много решаващ фактор - от дизайна на базата данни до заявката. Въпреки това MS SQL Server вече предлага цяла гама от различни форми на индекси: В допълнение към класическите групирани и негрупирани индекси, има индекси за специални области на приложение като пълен текст индекси, XML индекси и пространствени индекси. Освен това по-късно бяха добавени индексите на колоните, първоначално проектирани за хранилища на данни, които най-късно в третото поколение могат да се използват ефективно и за други области.

оптималната

Общо как работят индексите

За да разберете по-добре как работят индексите, е полезно да разберете как SQL Server съхранява данните на диска. Данните се съхраняват във файлове с разширение .mdf или .ndf. Те са разделени на страници с памет с размер от 8 KB всяка, което е и най-малката единица, в която SQL Server чете или записва данни. Осем последователни страници с памет всяка образуват така наречената степен. Докато страницата с памет съдържа само данни от една таблица, разширенията са еднородни и смесени, които съдържат страници с памет от различни таблици. Например, ако заявка чете всички хора с определено фамилно име от таблица, която няма индекс, тя трябва да прочете всички страници в паметта, които съдържат данни в тази таблица.

За да можем да изпълняваме тези заявки по-ефективно, подходящи структури са полезни, които гарантират, че съответните страници с памет могат да бъдат намерени по-бързо и по този начин не всички страници с памет могат да бъдат прочетени. Точно това правят индексите.

Клъстерирани индекси

В най-простия случай самата таблица се сортира според конкретна колона (в случая на таблицата на човека, например, според фамилното име). Точно това прави клъстерираният индекс. Това обаче не се прави под формата на обикновен списък, а под формата на двоично дърво, в което всеки възел на дървото използва собствена страница с памет, която съдържа азбучен сортиран списък с имена, допълнен с препратка към страницата с памет, в която по-нататък потърсете това име (или името, следващо го по азбучен ред). Това може - в зависимост от броя на записите с данни в таблицата - да продължи на няколко етапа, докато накрая се направи препратка към страницата с памет, в която се съхраняват пълните лични данни. Това означава, че трябва да бъдат прочетени само няколко страници с данни, преди да бъдат намерени съответните редове в таблицата.

Докато фамилното име със сигурност може да се появи няколко пъти в таблица с лица, на практика обикновено се използва уникална колона за групиран индекс, който често съответства на първичния ключ на таблицата, т.е.ключът, с който всички редове на таблица могат да бъдат ясно идентифицирани. Следователно, когато се създава първичен ключ, групово индекс се генерира неявно за него, освен ако ключовата дума NONCLUSTERED не е посочена изрично (Листинг 1).

Най-често срещаният вариант за клъстерен индекс е колона Id от тип int (или bigint за големи таблици) със спецификация за идентичност, която гарантира, че на колоната автоматично се присвояват уникални номера. Като алтернатива често се използват колони от типа uniqueidentifier, които също се доставят с уникални - но не непрекъснати - стойности чрез функцията NewId () като ограничение по подразбиране. Това е особено полезно в разпределени среди, където се създават нови редове в различни копия на база данни, които след това се обединяват по-късно, без да се припокриват първичните ключове.
Основен недостатък на клъстерираните индекси е, че те могат да бъдат сортирани само в един ред. Заявка, която търси в друга колона (като собственото име), няма да се възползва от индекс в колоната Фамилно име. За да се реши това, се използват n негрупирани индекси.

Показване на планове за изпълнение

За да провери използването на индексите в заявките, SQL Server Management Studio предлага възможност за показване както на очакваните, така и на действителните планове за изпълнение на заявките. И двете опции могат да бъдат активирани най-лесно чрез съответните опции в менюто за заявки или алтернативно чрез лентата с инструменти. Трябва да се отбележи, че показването на очаквания план за изпълнение не изпълнява самата заявка, докато действителният план за изпълнение може да се покаже само след изпълнението на заявката. В графично показаните планове за изпълнение, които могат да бъдат прочетени отгоре вдясно, можете бързо да видите дали (и как) се осъществява достъп до индекс или самата таблица. С „как“ трябва да се отбележат ключовите думи „сканиране“ и „търсене“. Търсенето на индекс означава, че съответните редове се намират чрез двоично търсене в дървото на индекса, докато сканирането показва, че таблицата или индексът са прочетени напълно, което в повечето случаи отнема значително повече време. По-подробна информация за плановете за изпълнение на четенето може да се намери в препратките към тази статия [1], [2], [3].

Неклъстерирани индекси

Негрупираните индекси представляват допълнителна структура от данни, която също се управлява в дървовидна структура, но листата на която не съдържат действителните редови данни, а препратка към адреса на данните. Казано по-просто, негрупираният индекс се държи като индекс в края на книга: Той сам се сортира, за да можете бързо да търсите конкретен термин. След като терминът бъде намерен, той е последван само от номера на страницата, където терминът може да бъде намерен. Необходима е още една промяна на съответната страница на книгата, за да намерите информацията, която искате. С индекса на базата данни препратката към реалните данни е, разбира се, малко по-сложна от обикновения номер на страница. Този процес, известен като търсене на редове, използва идентификатор на ред (накратко RID), който се състои от три части:

  1. Номерът на файла на базата данни (тъй като таблица може да бъде разделена на няколко файла)
  2. Номерът на страницата с памет
  3. Номерът на записа на данни на тази страница с памет

Това обаче важи само ако таблицата няма групиран индекс и поради това се съхранява като неподредена "купчина". Ако, от друга страна, съществува групиран индекс, ключовата стойност на групирания индекс се посочва като препратка към нивото на листа на негрупирания индекс. Трябва да се премине през друго дърво на индекса, преди да могат да се намерят действителните данни. (Въпреки това, допълнителните усилия за това, така наречено търсене на ключове, обикновено са доста ниски.)

Фиг. 1: Дърво на индекса за таблица без групиран индекс

Голямо предимство на негрупираните индекси е, че може да има няколко от тях за таблица, които са сортирани според различни колони. Това означава, че след това можете да търсите различни критерии с висока производителност (Фиг. 1).

Тъй като индексите, които не са групирани, са допълнителни структури от данни, винаги трябва да се знае, че, от една страна, те изискват допълнителна памет, а от друга страна, всички индекси, съдържащи променените колони, трябва да се актуализират всеки път, когато самите данни се променят. Ако е възможно, не трябва да създавате отделен индекс за всяка колона на таблица, но винаги претегляйте кои колони има смисъл от индекса и къде може да се освободи. Като основно правило можете да запомните, че допълнителните индекси могат да ускорят достъпа до четене, но да забавят достъпа до запис. Решението за кои колони има смисъл даден индекс може да се вземе въз основа на следните критерии:

  • Ако често се търси или филтрира колоната, това говори в полза на индекс.
  • Същото се отнася и за колони, които се използват като колони с външен ключ, тъй като те се филтрират по време на JOIN.
  • Ако колоната съдържа само няколко различни стойности (едната също говори за ниска селективност), това по-скоро говори срещу индекс в нея. Това важи особено за колони, които използват битовия тип данни.
  • Ако таблицата се чете предимно и само рядко се променя, приемливи са повече индекси.
  • Ако таблицата е написана предимно (напр. Таблица в дневник) и се чете рядко, броят на индексите трябва да бъде възможно най-малък.

Комбинирани индекси

Един от начините да се поддържа броят на индексите, които да се управляват ниски, но също така да се направи възможно най-ефективното използване на индексите, е използването на комбинирани индекси, които са дефинирани в няколко колони. Ако например има отделен индекс за колоните за име и фамилия в таблица на човек, следната заявка едва ли ще може да използва двата индекса:

ИЗБЕРЕТЕ * ОТ Person.Person

WHERE FirstName = 'John' И LastName = 'Wood'

Вместо това SQL Server автоматично ще използва индекса, от който се очаква по-малкият брой страници за четене (индексът в колоната с по-висока селективност). Тъй като обаче може да има няколко записа както за първото, така и за фамилното име, би било още по-ефективно да има индекс, който съдържа и двете колони, т.е.

СЪЗДАЙТЕ НЕКЛУСТРИРАН ИНДЕКС IX_Person_LastName_FirstName

ON Person.Person (LastName, FirstName)

Докато името на индекса (IX_Person_LastName_FirstName) е само често срещано споразумение за именуване, редът на колоните в дефиницията на индекса е от решаващо значение. Поставяйки фамилното име на първо място, индексът може също така да се използва ефективно, когато заявка търси само фамилното име (тъй като това е основният критерий за сортиране за индекса). Ако вместо това търсите само собствено име, индексът също може да се използва, но не чрез ефективен достъп чрез дървото на двоичния индекс (търсене на индекс). Вместо това трябва да се прочете целият индекс (сканиране на индекс), за да се намерят всички възможни комбинации, които съдържат първото име, което търсите (което в повечето случаи е все още по-ефективно от четенето на цялата таблица без индекс).

Тъй като търсенето само на собственото име със сигурност ще се случи много по-рядко на практика от търсенето на фамилното име, избраният по-горе ред на колоните се препоръчва и има страничния ефект, който можете да направите без отделните индекси за колоните за първо име и фамилия.

Покриващи индекси и включващи колони

При обяснението на негрупираните индекси беше посочено, че има препратки към реалните записи на данни на ниво листа на дървото на двоичния индекс (или под формата на групиран индексен ключ, или като адрес от няколко части, съставен от номер на файл, страница на паметта и номер на ред). В идеалния случай обаче всички заявени колони се съдържат в самия индекс, така че няма нужда да следвате тази препратка. Ако следната заявка се изпълни със съществуващ комбиниран индекс на колоните LastName и FirstName:

тогава индексът може да се търси с помощта на колоната LastName, но той също така съдържа заявеното LastName, така че нормално необходимото търсене на пълния ред с данни може да бъде пропуснато. След това се говори за обхващащ индекс (по отношение на заявката), тъй като това обхваща всички колони за заявката.

За да се използва предимството на покриващ индекс възможно най-често, без да се налага да пренареждате самия индекс твърде често, колоните могат да бъдат интегрирани в индекса като така наречените включващи колони. Това означава, че стойностите на тези колони се съдържат на нивото на листа на дървото на индекса, но не се вземат предвид при сортирането на индекса (следователно е достатъчно, ако колоните съществуват само на нивото на листа, а не на възлите по-горе). Следният индекс също би бил достатъчен за последно показаната заявка:

Могат да бъдат дефинирани и няколко колони за включване, чийто ред е тогава без значение, тъй като те не са от значение за сортирането. Ако трябваше да включите всички колони в индекса (или за сортиране, или като колони за включване), бихте симулирали групиран индекс, но с недостатъка, че след това се използва пространството за препратка към самия ред с данни. което е ненужно в този вариант, тъй като се съдържа изцяло в индекса. Така че това определено не се препоръчва, особено след като индексите могат да се използват по-ефективно, колкото по-малки са те.

Филтрирани индекси

От SQL Server 2008 има друга възможност с филтрираните индекси да поддържат индекса възможно най-компактен и по този начин да минимизират броя на страниците с памет за четене. Индексът се допълва от клауза WHERE, така че индексът трябва да бъде създаден само за редовете, които отговарят на посоченото условие. Следователно това води до ограничението, че условията за филтриране могат да се използват само в комбинация с негрупирани индекси.

Типичен случай на употреба за филтрирани индекси са таблици с колони, които не се попълват в голяма част от редовете:

КЪДЕ БЛИЗНОТО НЕ Е НУЛО

Индексирани възгледи

От версия 2005, SQL Server предлага и възможност за създаване на индекси въз основа на изгледи. В резултат на това данните, изискани от изгледа, се запазват излишно като копие, но се сортират според критериите за индекс. Поради това тази концепция често се нарича материализиран възглед (този термин е особено разпространен в средата на Oracle). Основното предимство на такъв индексиран изглед е, че всички данни, които трябва да бъдат заявени, вече са свързани и са в правилния ред на сортиране. По този начин заявката трябва да има достъп само до индексирания изглед, без първо да обединява данните от няколко таблици, използвайки JOIN условия. Допълнителни прегледи на редове или ключове също са пропуснати, защото - при условие че изгледът е създаден по съответния начин - всички колони, които трябва да бъдат заявени, се съдържат в изгледа и по този начин също в индекса върху него.

За да може да се използват индексирани изгледи обаче, трябва да се спазват няколко ограничения:

  • Самият изглед трябва да бъде създаден с опцията WITH SCHEMABINDING, която предотвратява промяната на структурата на данните на колоните, използвани в изгледа.
  • Индексът в изгледа трябва да бъде създаден като УНИКАЛЕН КЛАСТЕРИРАН ИНДЕКС.
  • Индексът не трябва да се филтрира, но това не означава никакво реално ограничение, тъй като критерият за филтър може лесно да бъде приет в изгледа, използван от индекса.

Листинг 2 показва как може да се създаде индексиран изглед в две стъпки. След като изгледът е създаден с добавка SCHEMABINDING, за този изглед се генерира УНИКАЛНИЯТ КЛУСТРИРАН ИНДЕКС. Ако сега изпълните проста заявка в изгледа, можете да определите от плана за изпълнение, че е прочетен само самият индекс, а не таблиците зад него:

Интересен страничен ефект от индексираните изгледи е, че те могат да се използват, дори ако таблиците зад него не са адресирани изрично, а по-скоро изгледът. Това може лесно да се провери, като се разгледа планът за изпълнение на следната заявка:

Когато създава план за изпълнение, SQL Server автоматично разпознава, че има подходящ индексиран изглед, който може да бъде достъпен по-бързо от използваните таблици и техните индекси.

Заключение

В първата част на тази поредица от статии бяха създадени основите и беше даден преглед на „класическите“ варианти на индекса, които са на разположение от дълго време. Следващата част от тази серия ще разгледа поддържането на индекси и няколко по-разширени опции, като компресиране на индекса.

Връзки и литература

[1] Пантера, Робърт: „Оптимизиране на SQL заявки“, entwickler.press, юни 2014 г., ISBN: 978-3868021233

[2] Пантера, Робърт: „SQL Server Performance Ratgeber“, entwickler.press, февруари 2010 г., ISBN: 978-3868020304

[3] Fritchey, Grant: "Планове за изпълнение на SQL Server", Публикация на Simple Talk, октомври 2012 г., ISBN: 978-smartblock>

Програмист на Windows

Тази статия е публикувана в Windows Developer. Windows Developer предоставя изчерпателна, неутрална от производителя информация за новите тенденции и възможности в разработването на софтуер и системи за всички аспекти на технологиите на Microsoft.