За какво са индексите в sql. Индекси в SQL Server

За какво са индексите в sql. Индекси в SQL Server
За какво са индексите в sql. Индекси в SQL Server
03.10.2021

1) Концепцията за индекс
Индексе инструмент, който осигурява бърз достъп до редовете на таблица въз основа на стойностите на една или повече колони.

Има голямо разнообразие в този оператор, тъй като не е стандартизиран, тъй като стандартите не засягат проблеми с производителността.

2) Създаване на индекси
СЪЗДАВАНЕ НА ИНДЕКС
НА()

3) Промяна и изтриване на индекси
Операторът се използва за контрол на активността на индекса:
ПРОМЕНЯ ИНДЕКС
За да премахнете индекс, използвайте оператора:
ИНДЕКС НА СПАДАНЕ

а) Правила за избор на маса
1. Препоръчително е да индексирате таблици, в които са избрани не повече от 5% от редовете.
2. Индексни таблици, които нямат дубликати в клаузата WHERE на израза SELECT.
3. Непрактично е да се индексират често актуализирани таблици.
4. Не е подходящо да се индексират таблици, които заемат не повече от 2 страници (за Oracle това са по-малко от 300 реда), тъй като пълното им сканиране не отнема повече време.

б) Правила за избор на колони
1. Първичен и външен ключ - често се използва за свързване на таблици, избор на данни и търсене. Това винаги са уникални индекси с максимална полезност
2. Когато използвате опциите за референтна цялост, винаги е необходим индекс на FK.
3. Колони, по които данните често се сортират и/или групират.
4. Колони, често търсени в клаузата WHERE на оператор SELECT.
5. Не създавайте индекси за дълги описателни колони.

в) Принципи за създаване на съставни индекси
1. Композитните индекси са добри, ако колоните поотделно имат малко уникални стойности, а съставният индекс осигурява повече уникалност.
2. Ако всички стойности, избрани от оператора SELECT, принадлежат към съставния индекс, тогава стойностите се избират от индекса.
3. Трябва да създадете съставен индекс, ако клаузата WHERE използва две или повече стойности, комбинирани с оператор И.

г) Не се препоръчва да се създава
Не се препоръчва да се създават индекси на колони, включително съставни, които:
1. Рядко се използва за търсене, обединяване и сортиране на резултати от заявки.
2. Съдържат често променящи се стойности, което изисква често актуализиране на индекса, което забавя производителността на базата данни.
3. Съдържат бр голям бройуникални стойности (по-малко от 10% m/f) или преобладаващия брой редове с една или две стойности (град на пребиваване на доставчика Москва).
4. Към тях се прилагат функции или израз в клаузата WHERE и индексът не работи.

д) Не забравяйте
Трябва да се стремите да намалите броя на индексите, тъй като голям брой от тях намалява скоростта на актуализиране на данните. Така MS SQL сървърпрепоръчва създаването на не повече от 16 индекса на таблица.
Обикновено индексите се създават за заявки и поддържане на референтна цялост.
Ако даден индекс не се използва за заявки, той трябва да бъде премахнат и референтната цялост да бъде наложена чрез тригери.

Като начало предлагам да разберем какво е покривен индекс, ще дам откъс от статия на Хабре:

Защо да използвате покриващ индекс вместо съставен индекс?
Първо, нека се уверим, че разбираме разликата между тях.
Съставен индекстова е просто обикновен индекс, който включва повече от една колона. Множество ключови колони могат да се използват, за да се гарантира уникалността на всеки ред на таблицата, също така е възможно първичният ключ да се състои от няколко колони, за да се гарантира неговата уникалност, или се опитвате да оптимизирате изпълнението на често извиквани заявки за множество колони. Като цяло обаче, колкото повече ключови колони съдържа индексът, толкова по-малко ефективен е този индекс и затова съставните индекси трябва да се използват разумно.

Както беше казано, една заявка може да извлече огромни ползи, ако всички необходими данни са незабавно разположени на листата на индекса, точно както самия индекс. Това не е проблем за клъстериран индекс, т.к всички данни вече са там (ето защо е толкова важно да мислите внимателно, когато създавате клъстерен индекс). Но негрупираният индекс на листата съдържа само ключовите колони. Оптимизаторът на заявките се нуждае от допълнителни стъпки за достъп до всички други данни, което може да причини значителни допълнителни разходи за изпълнение на вашите заявки.

Ето къде покривен индекссе притичва на помощ. Когато дефинирате неклъстъриран индекс, можете да посочите допълнителни колони в допълнение към вашите ключови.

По този начин покриващият индекс не трябва да съдържа всички избираеми колони на заявка в дървовидната структура на индекса, а само тези, които ще бъдат използвани за филтриране или групиране на данни в заявката, останалите колони от клаузата SELECT трябва да бъдат поставени в секцията на индекса INCLUDE.

Може да намерите отговора в друг въпрос от полза.

В примера по-горе съставен индекс от 3 полета се използва вместо покриващ индекс, кодът за създаване на покриващ индекс ще бъде:

СЪЗДАВАНЕ НА НЕКЛУСТРИРАН ИНДЕКС НА . (ASC) ВКЛ.

Отговарям на въпроса ви:

за покриващ индекс, редът на колоните в клаузата INCLUDE маловажно, но редът на колоните е важен за съставния индекс, защото данните от колоната се поставят в дървото на индекса в ред на колони и оптимизаторът на заявки няма да може да използва индекс от 2 колони, за да търси само стойности от 2 колони. Можете да видите илюстративен пример как ще изглежда структура на индекс от 2 колони (EMPLOYEE_ID, SUBSIDIARY_ID) на фигурата.

6. Индекси и оптимизиране на производителността

Индекси в бази данни: предназначение, влияние върху производителността, принципи на създаване на индекси

6.1 Защо са необходими индекси

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

Обикновено, колкото повече индекси, толкова по-добра е производителността на заявките към базата данни. Въпреки това, с прекомерно увеличаване на броя на индексите, производителността на операциите за модифициране на данни (вмъкване/модифициране/изтриване) намалява, размерът на базата данни се увеличава, така че добавянето на индекси трябва да се третира с повишено внимание.

някои основни принциписвързани със създаването на индекси:

· Трябва да се създадат индекси за колони, които се използват в съединения, които често се търсят и сортират. Трябва да се отбележи, че индексите винаги се създават автоматично за колони, които са предмет на ограничението на първичния ключ. Най-често се създават и за колони с външен ключ (в Access - автоматично);

Индексът трябва да е вътре автоматичен режимсъздаден за колони, които имат уникално ограничение;

· Най-добре е да създадете индекси за тези полета, в които - минималният брой дублиращи се стойности и данните са равномерно разпределени. Oracle има специални битови индекси за колони с голяма сумадублирани стойности, SQL Server и Access не предоставят този вид индекс;

· ако търсенето се извършва постоянно върху определен набор от колони (по едно и също време), тогава в този случай може да има смисъл да се създаде съставен индекс (само в SQL Server) - един индекс за група колони;

· Когато се правят промени в таблици, индексите, наложени върху тази таблица, се променят автоматично. В резултат на това индексът може да бъде силно фрагментиран, което се отразява на производителността. Индексите трябва периодично да се проверяват за фрагментация и да се дефрагментират. Когато зареждате голямо количество данни, понякога има смисъл първо да премахнете всички индекси и след като операцията приключи, да ги създадете отново;

· Индекси могат да се създават не само за таблици, но и за изгледи (само в SQL Server). Предимства - възможността за изчисляване на полета не по време на заявката, а по време на появата на нови стойности в таблиците.

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

Индексите се създават върху колони в таблици и изгледи. Индексите предоставят начин за бързо търсене на данни въз основа на стойностите в тези колони. Например, ако създадете индекс на първичния ключ и след това потърсите реда с данни, като използвате стойностите на първичния ключ, тогава SQL сървърпърво ще намери стойността на индекса и след това ще използва индекса, за да намери бързо целия ред от данни. Без индекс ще се извърши пълно търсене (сканиране) на всички редове на таблицата, което може да окаже значително влияние върху производителността.
Можете да създадете индекс на повечето колони в таблица или изглед. Изключение правят главно колони с типове данни за съхранение на големи обекти ( LOB), като изображение, текстили varchar(макс.). Можете също така да създавате индекси на колони, предназначени да съхраняват данни във формата XML, но тези индекси са подредени малко по-различно от стандартните и тяхното разглеждане е извън обхвата на тази статия. Освен това статията не обхваща колонен магазининдекси. Вместо това се фокусирам върху онези индекси, които се използват най-често в базите данни. SQL сървър.
Индексът се състои от набор от страници, индексни възли, които са организирани в дървовидна структура - балансирано дърво. Тази структура е йерархична по природа и започва с основен възел в горната част на йерархията и крайни възли, листа, в долната част, както е показано на фигурата:


Когато правите заявка за индексирана колона, машината за заявки започва от горната част на основния възел и постепенно се придвижва надолу през междинните възли, като всеки слой от средния слой съдържа по-подробна информация за данните. Подсистемата за заявки продължава да се движи през възлите на индекса, докато достигне най-долното ниво с листата на индекса. Например, ако търсите стойността 123 в индексирана колона, тогава машината за заявки първо ще определи страницата на основното ниво на първото междинно ниво. В този случай първата страница сочи стойност от 1 до 100, а втората страница сочи стойност от 101 до 200, така че машината за заявки ще препраща към втората страница от това междинно ниво. След това ще бъде изяснено, че трябва да се обърнете към третата страница от следващото средно ниво. Оттам подсистемата за заявки ще прочете на най-долното ниво стойността на самия индекс. Листовете на индекса могат да съдържат както самите данни от таблицата, така и само указател към редове с данни в таблицата, в зависимост от типа на индекса: клъстерен или неклъстерен.

Клъстерен индекс
Клъстърираният индекс съхранява действителните редове от данни в листата на индекса. Връщайки се към предишния пример, това означава, че редът от данни, свързан с ключова стойност 123, ще бъде съхранен в самия индекс. Важна характеристикаКлъстерният индекс е, че всички стойности са сортирани в определен ред, възходящ или низходящ. По този начин една таблица или изглед може да има само един клъстерен индекс. Освен това трябва да се отбележи, че данните в таблицата се съхраняват в сортирана форма само ако за тази таблица е създаден клъстерен индекс.
Таблица без клъстерен индекс се нарича куп.
Неклъстъриран индекс
За разлика от клъстерния индекс, листата на неклъстерния индекс съдържат само тези колони ( ключ), според което даден индекс, а също така съдържа указател към редове с действителни данни в таблицата. Това означава, че системата за подзапитване се нуждае от допълнителна операция за намиране и извличане на исканите данни. Съдържанието на указателя на данни зависи от това как се съхраняват данните: клъстерна таблица или купчина. Ако показалецът сочи към клъстерирана таблица, тогава той сочи към клъстериран индекс, който може да се използва за намиране на реалните данни. Ако указателят препраща към купчина, тогава той сочи към конкретен идентификатор на ред с данни. Неклъстерираните индекси не могат да бъдат сортирани, за разлика от клъстерираните, но можете да създадете повече от един неклъстериран индекс в таблица или изглед, до 999. Това не означава, че трябва да създадете възможно най-много индекси. Индексите могат или да подобрят, или да влошат производителността на системата. В допълнение към възможността да създавате множество негрупирани индекси, можете също да включите допълнителни колони ( включена колона) към неговия индекс: листата на индекса ще съхраняват не само стойността на самите индексирани колони, но и стойностите на тези неиндексирани допълнителни колони. Този подход ще ви позволи да заобиколите някои от ограниченията, поставени върху индекса. Например, можете да активирате неиндексирана колона или да заобиколите ограничението за дължина на индекса (900 байта в повечето случаи).

Видове индекси

Освен че е клъстериран или неклъстериран, индексът може да бъде допълнително конфигуриран като съставен индекс, уникален индекс или покриващ индекс.
Съставен индекс
Такъв индекс може да съдържа повече от една колона. Можете да включите до 16 колони в индекс, но общата им дължина е ограничена до 900 байта. И клъстерираните, и неклъстерираните индекси могат да бъдат съставни.
Уникален индекс
Такъв индекс гарантира, че всяка стойност в индексираната колона е уникална. Ако индексът е съставен, тогава уникалността се отнася за всички колони на индекса, но не и за всяка отделна колона. Например, ако създадете уникален индекс на колони ИМЕИ ФАМИЛИЯ, тогава пълното име трябва да е уникално, но са възможни дубликати в собственото или фамилното име отделно.
Уникален индекс се създава автоматично, когато дефинирате ограничения за колони: първичен ключ или ограничение за уникална стойност:
  • първичен ключ
    Когато дефинирате ограничение за първичен ключ за една или повече колони, тогава SQL сървъравтоматично създава уникален клъстериран индекс, ако клъстерен индекс не е бил създаден преди (в този случай уникален неклъстериран индекс се създава от първичен ключ)
  • Уникалност на ценностите
    Когато дефинирате ограничение за уникалността на стойностите, тогава SQL сървъравтоматично създава уникален негрупиран индекс. Можете да укажете да бъде създаден уникален клъстерен индекс, ако в таблицата все още не е създаден клъстерен индекс
Индекс на покритие
Такъв индекс позволява на конкретна заявка незабавно да получи всички необходими данни от листата на индекса без допълнителни извиквания към записите на самата таблица.

Дизайн на индекса

Колкото и полезни да са индексите, те трябва да бъдат внимателно проектирани. Тъй като индексите могат да заемат много дисково пространство, не искате да създавате повече индекси, отколкото са ви необходими. В допълнение, индексите се актуализират автоматично, когато се актуализира самият ред с данни, което може да доведе до допълнителни разходи за ресурси и влошаване на производителността. При проектирането на индекси има няколко съображения относно базата данни и заявките към нея.
База данни
Както беше отбелязано по-рано, индексите могат да подобрят производителността на системата, защото те те предоставят на машината за заявки бърз начин за намиране на данни. Трябва обаче също така да вземете предвид колко често ще вмъквате, актуализирате или изтривате данни. Когато промените данните, индексите също трябва да бъдат променени, за да отразяват съответните действия върху данните, което може значително да влоши производителността на системата. Имайте предвид следните насоки, когато планирате своята стратегия за индексиране:
  • За таблици, които се актуализират често, използвайте възможно най-малко индекси.
  • Ако таблицата съдържа голямо количество данни, но техните промени са незначителни, тогава използвайте толкова индекси, колкото е необходимо, за да подобрите производителността на вашите заявки. Въпреки това, помислете внимателно, преди да използвате индекси на малки таблици, като може би използването на индексно търсене може да отнеме повече време от простото сканиране на всички редове.
  • За групирани индекси се опитайте да запазите полетата възможно най-кратки. Най-добрата практика би била да се използва клъстерен индекс върху колони, които имат уникални стойности и не позволяват нули. Ето защо първичният ключ често се използва като клъстерен индекс.
  • Уникалността на стойностите в колона влияе върху производителността на индекса. Като цяло, колкото повече дубликати имате в колона, толкова по-лошо се представя индексът. От друга страна, колкото повече са уникалните стойности, толкова по-добро е здравето на индекса. Използвайте уникален индекс, когато е възможно.
  • За съставен индекс вземете предвид реда на колоните в индекса. Колони, използвани в изрази КЪДЕТО(Например, WHERE FirstName = "Чарли") трябва да е първи в индекса. Следващите колони трябва да бъдат изброени според уникалността на техните стойности (колоните с най-голям брой уникални стойности са първи).
  • Можете също да зададете индекс за изчисляеми колони, ако отговарят на определени изисквания. Например, изразите, които се използват за получаване на стойността на колона, трябва да бъдат детерминистични (винаги да връщат един и същ резултат за даден набор от входни параметри).
Заявки към бази данни
Друго съображение при проектирането на индекси е какви заявки се правят към базата данни. Както беше посочено по-рано, трябва да имате предвид колко често се променят данните. Освен това трябва да се използват следните принципи:
  • Опитайте се да вмъкнете или промените възможно най-много редове в една заявка, вместо да го правите в множество единични заявки.
  • Създайте негрупиран индекс на колони, които често се използват във вашите заявки като думи за търсене в КЪДЕТОи връзки в ПРИСЪЕДИНЯВАНЕ.
  • Помислете за индексиране на колони, използвани в заявки за търсене на редове за точно съвпадение на стойността.

А сега всъщност:

14 въпроса за индекса на SQL Server, които сте били твърде притеснени да зададете

Защо една таблица не може да има два групирани индекса?

Искате ли кратък отговор? Клъстерираният индекс е таблицата. Когато създадете клъстерен индекс на таблица, подсистемата за съхранение сортира всички редове в таблицата във възходящ или низходящ ред, според дефиницията на индекса. Клъстерираният индекс не е отделен обект като другите индекси, а механизъм за сортиране на данни в таблица и улесняване бърз достъпкъм линии за данни.
Нека си представим, че имате таблица, съдържаща историята на транзакциите за продажба. Таблицата Продажби включва информация като ИД на поръчката, позиция на артикул в поръчката, номер на артикул, количество на артикул, номер на поръчка и дата и т.н. Създавате групиран индекс на колони OrderIDИ ID на линията, сортирани във възходящ ред, както е показано по-долу T-SQLкод:
СЪЗДАВАЙТЕ УНИКАЛЕН КЛУСТЕРИРАН ИНДЕКС ix_oriderid_lineid НА dbo.Sales(OrderID, LineID);
Когато стартирате този скрипт, всички редове в таблицата ще бъдат физически сортирани първо по колоната OrderID и след това по LineID, но самите данни ще останат в един логически блок, таблицата. Поради тази причина не можете да създадете два клъстерирани индекса. Може да има само една таблица с едни и същи данни и тази таблица може да бъде сортирана само веднъж в определен ред.

Ако една клъстерирана таблица предоставя много предимства, защо да използвате купчина?

Прав си. Клъстерираните таблици са различни и повечето от вашите заявки ще се представят по-добре на таблици, които имат клъстерен индекс. Но в някои случаи може да искате да оставите масите в естествения им вид, т.е. като купчина и създавайте само неклъстерирани индекси, за да поддържате вашите заявки изпълнявани.
Не забравяйте, че купчината съхранява данни в произволен ред. Обикновено подсистемата за съхранение добавя данни към таблица в реда, в който са вмъкнати, но подсистемата за съхранение също обича да премества редове за по-ефективно съхранение. В резултат на това нямате шанс да предвидите реда, в който ще се съхраняват данните.
Ако машината за заявки трябва да намери данни без предимствата на неклъстерен индекс, тя ще направи пълно сканиране на таблица, за да намери редовете, от които се нуждае. На много малки маси това обикновено не е проблем, но тъй като купчината нараства по размер, производителността пада бързо. Разбира се, неклъстъриран индекс може да помогне, като използва указател към файла, страницата и реда, където се съхраняват необходимите данни - обикновено много. най-добрата алтернативасканиране на маса. Въпреки това е трудно да се сравнят с предимствата на клъстерен индекс, когато се разглежда производителността на заявките.
Въпреки това купчината може да помогне за подобряване на производителността в определени ситуации. Помислете за таблица с много вмъквания, но малко актуализации или изтривания. Например, регистрационна таблица се използва предимно за вмъкване на стойности, докато не бъде архивирана. В купчина няма да видите пагинация и фрагментиране на данни, както бихте направили с клъстерен индекс, защото редовете просто се добавят в края на купчината. Твърде голямото разделяне на страници може да окаже значително влияние върху производителността, и то не по най-добрия начин. Като цяло, купчината прави вмъкването на данни сравнително безболезнено и не е нужно да се справяте с разходите за съхранение и поддръжка на клъстерен индекс.
Но липсата на актуализиране и изтриване на данни не трябва да се счита за единствената причина. Важен фактор е и начинът, по който данните се извадят. Например, не трябва да използвате купчина, ако често правите заявки за диапазони от данни или ако исканите данни често трябва да бъдат сортирани или групирани.
Всичко това означава, че трябва да обмислите използването на купчината само когато работите с изключително малки таблици или цялото ви взаимодействие с таблицата е ограничено до вмъкване на данни и вашите заявки са изключително прости (и все още използвате неклъстерирани индекси). В противен случай се придържайте към добре проектиран клъстерен индекс, като такъв, дефиниран на просто нарастващо ключово поле, като широко използвана колона с ИДЕНТИЧНОСТ.

Как да променя стойността на коефициента на запълване на индекса по подразбиране?

Промяната на коефициента на запълване на индекса по подразбиране е едно нещо. Разбирането как работи съотношението по подразбиране е друг въпрос. Но първо, няколко крачки назад. Коефициентът на запълване на индекса определя количеството пространство на страница за съхраняване на индекса на най-ниското ниво (ниво на листа), преди да започне да се попълва. нова страница. Например, ако коефициентът е зададен на 90, тогава когато индексът расте, той ще заема 90% на страницата и след това ще премине към следващата страница.
По подразбиране стойността на фактора за запълване на индекса е в SQL сървъре 0, което е равно на 100. В резултат на това всички нови индекси автоматично ще наследят тази настройка, освен ако конкретно не посочите стойност, различна от системната по подразбиране в кода или промените поведението по подразбиране. Можеш да използваш SQL Server Management Studioза да коригирате стойността по подразбиране или да изпълните системна съхранена процедура sp_configure. Например следният набор T-SQLкомандата задава стойността на коефициента на 90 (първо трябва да преминете към режим на разширени настройки):
EXEC sp_configure "покажи разширени опции", 1; ОТИДЕТЕ НА ПРЕКОНФИГУРИРАНЕ; GO EXEC sp_configure "фактор на запълване", 90; ОТИДЕТЕ НА ПРЕКОНФИГУРИРАНЕ; ОТИВАМ
След като промените стойността на коефициента на запълване на индекса, трябва да рестартирате услугата SQL сървър. Вече можете да проверите зададената стойност, като изпълните sp_configure без посочения втори аргумент:
EXEC sp_configure "фактор на запълване" GO
Тази команда трябва да върне стойност 90. В резултат на това всички новосъздадени индекси ще използват тази стойност. Можете да тествате това, като създадете индекс и направите заявка за стойността на фактора на запълване:
ИЗПОЛЗВАЙТЕ AdventureWorks2012; -- вашата база данни GO CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName); ИЗБЕРЕТЕ fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
IN този примерсъздадохме неклъстъриран индекс на таблицата човекв базата данни AdventureWorks 2012. След като създадем индекса, можем да получим стойността на фактора на запълване от системните таблици sys.indexes. Заявката трябва да върне 90.
Но нека си представим, че сме изпуснали индекса и сме го създали отново, но сега сме посочили конкретна стойност на коефициента на запълване:
CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName) WITH (fillfactor=80); ИЗБЕРЕТЕ fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
Този път сме добавили инструкции СЪСи опция фактор на запълванеза нашата операция по създаване на индекс СЪЗДАВАНЕ НА ИНДЕКСи посочи стойност 80. Оператор ИЗБЕРЕТЕсега връща подходящата стойност.
Досега всичко беше доста ясно. Където наистина можете да се изгорите в целия този процес е, когато създадете индекс, като използвате стойността на фактора по подразбиране, ако приемете, че знаете стойността. Например, някой се забърква с настройките на сървъра и е толкова упорит, че задава стойността на коефициента на запълване на индекса на 20. Междувременно вие продължавате да създавате индекси, като приемате стойност по подразбиране 0. За съжаление няма начин да намерите извадете стойността на фактора на запълване преди, освен ако не създадете индекс и след това проверите стойността, както направихме в нашите примери. В противен случай ще трябва да изчакате, докато производителността на заявката спадне толкова много, че да започнете да подозирате нещо.
Друг проблем, с който трябва да сте наясно, е възстановяването на индекса. Както при създаването на индекс, можете да посочите стойността на фактора на запълване на индекса, когато го изграждате отново. Въпреки това, за разлика от командата за създаване на индекс, повторното изграждане не използва настройките на сървъра по подразбиране, въпреки че може да изглежда така. Още повече, че ако не посочите конкретно стойност на коефициента на запълване на индекса, тогава SQL сървърще използва стойността на коефициента, с който този индекс е съществувал преди да бъде преустроен. Например следната операция ПРОМЕНЯ ИНДЕКСвъзстановява индекса, който току-що създадохме:
ALTER INDEX ix_people_lastname ON Person.Person REBUILD; ИЗБЕРЕТЕ fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
Когато проверяваме стойността на коефициента на запълване, получаваме стойност 80, защото това сме посочили при последното създаване на индекса. Стойността по подразбиране се игнорира.
Както можете да видите, промяната на стойността на коефициента на запълване на индекса не е толкова трудно нещо. Много по-трудно за познаване настояща стойности разберете кога се прилага. Ако винаги указвате конкретно съотношение, когато създавате и възстановявате индекси, тогава винаги знаете конкретния резултат. Освен ако не трябва да се уверите, че някой друг няма да прецака отново настройките на сървъра, причинявайки възстановяването на всички индекси с абсурдно нисък коефициент на запълване на индекса.

Възможно ли е да се създаде клъстерен индекс върху колона, съдържаща дубликати?

Да и не. Да, можете да създадете клъстериран индекс върху ключова колона, съдържаща дублирани стойности. Не, стойността на ключова колона не може да остане в неуникално състояние. Нека обясня. Ако създадете неуникален клъстерен индекс в колона, тогава подсистемата за съхранение добавя цяло число (униквалификатор) ​​към дублиращата се стойност, за да осигури уникалност и, съответно, да гарантира, че всеки ред в клъстерната таблица може да бъде идентифициран.
Например, можете да решите да създадете клъстериран индекс в таблицата с данни за клиента в колоната фамилияпритежаващ фамилното име. Колоната съдържа стойности като Франклин, Хенкок, Вашингтон и Смит. След това поставяте отново стойностите Adams, Hancock, Smith и Smith. Но стойността на ключовата колона трябва да е уникална, така че подсистемата за съхранение ще промени стойността на дубликатите, така че да изглеждат по следния начин: Адамс, Франклин, Хенкок, Хенкок1234, Вашингтон, Смит, Смит4567 и Смит5678.
На пръв поглед този подход изглежда нормален, но целочислена стойност увеличава размера на ключа, което може да се превърне в проблем с голям брой дубликати и тези стойности ще станат основата на неклъстъриран индекс или справка външен ключ. Поради тези причини винаги трябва да се опитвате да създавате уникални клъстерирани индекси, когато е възможно. Ако това не е възможно, опитайте поне да използвате колони с много високо съдържание на уникални стойности.

Как се съхранява таблицата, ако не е създаден клъстерен индекс?

SQL сървърподдържа два типа таблици: клъстерирани таблици, които имат клъстериран индекс, и купчини таблици или просто купчини. За разлика от групираните таблици, данните в купчината не са сортирани по никакъв начин. Всъщност това е куп (куп) от данни. Ако добавите ред към такава таблица, подсистемата за съхранение просто ще го добави в края на страницата. Когато страницата се запълни с данни, те ще бъдат добавени към новата страница. В повечето случаи ще искате да създадете клъстерен индекс на таблица, за да се възползвате от сортирането и скоростта на заявките (опитайте се да си представите намирането телефонен номер V адресна книга, не сортирани по никакъв принцип). Въпреки това, ако решите да не създавате клъстерен индекс, все пак можете да създадете неклъстериран индекс в купчината. В този случай всеки индексен ред ще има указател към купчина ред. Индексът включва идентификатора на файла, номера на страницата и номера на реда за данни.

Каква е връзката между ограниченията на уникалната стойност и първичния ключ с индексите на таблицата?

Първичен ключ и уникално ограничение гарантират, че стойностите в колона са уникални. Можете да създадете само един първичен ключ на таблица и той не може да съдържа стойности. НУЛА. Можете да създадете множество ограничения върху уникалната стойност на таблица и всяко от тях може да има един запис с НУЛА.
Когато създавате първичен ключ, подсистемата за съхранение също създава уникален клъстерен индекс, ако вече не е създаден клъстерен индекс. Можете обаче да замените поведението по подразбиране и след това ще бъде създаден неклъстерен индекс. Ако съществува клъстерен индекс, когато създавате първичния ключ, тогава ще бъде създаден уникален неклъстерен индекс.
Когато създадете уникално ограничение, механизмът за съхранение създава уникален неклъстъриран индекс. Можете обаче да изберете да създадете уникален клъстерен индекс, ако такъв все още не е създаден.
Като цяло, ограничение за уникална стойност и уникален индекс са едно и също нещо.

Защо клъстерираните и неклъстерираните индекси се наричат ​​B-Tree в SQL Server?

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


Основният възел предоставя основната входна точка за заявки, които се опитват да извлекат данни чрез индекс. Започвайки от този възел, машината за заявки инициира йерархично обхождане надолу до подходящия краен възел, съдържащ данните.
Да предположим например, че има заявка за извличане на редове, съдържащи стойност на ключ 82. Подсистемата за заявки започва от основния възел, който сочи към подходящ междинен възел, в нашия случай 1-100. От междинния възел 1-100 има преход към възел 51-100, а от него към крайния възел 76-100. Ако това е клъстериран индекс, тогава листът на възела съдържа данните от реда, свързан с ключа, равен на 82. Ако това е неклъстериран индекс, тогава листът на индекса съдържа указател към клъстерирана таблица или конкретен ред в купчината.

Как по принцип един индекс може да подобри производителността на заявките, ако трябва да преминете през всички тези възли на индекса?

Първо, индексите не винаги подобряват производителността. Твърде много неправилно създадени индекси превръщат системата в блато и влошават производителността на заявките. Би било по-точно да се каже, че ако индексите се прилагат внимателно, те могат да осигурят значителни печалби в производителността.
Помислете за огромна книга, посветена на настройката на производителността SQL сървър(хартиен, не електронен вариант). Представете си, че искате да намерите информация за конфигурирането на Resource Governor. Можете да преместите пръста си страница по страница през цялата книга или да отворите съдържанието и да разберете точния номер на страницата с информацията, която търсите (ако приемем, че книгата е правилно индексирана и съдържанието има правилните индекси) . Разбира се, това ще ви спести много време, въпреки факта, че първо трябва да се обърнете към напълно различна структура (индекс), за да получите необходимата информация от основната структура (книга).
Подобно на книжния индекс, индексът в SQL сървърви позволява да изпълнявате точни запитваниякъм желаните данни вместо пълно сканиране на всички данни, съдържащи се в таблицата. За малки таблици пълното сканиране обикновено не е проблем, но големите таблици заемат много страници с данни, което може да доведе до значително време за изпълнение на заявка, ако няма въведен индекс, който да позволи на машината за заявки незабавно да получи правилното местоположение на данните . Представете си, че сте изгубени на многоетажно пътно кръстовище пред голям метрополис без карта и ще схванете идеята.

Ако индексите са толкова страхотни, защо просто не ги създадете във всяка колона?

Никое добро дело не трябва да остава ненаказано. Поне така е при индексите. Разбира се, индексите са страхотни, стига да правите заявки за извличане с оператора ИЗБЕРЕТЕ, но веднага щом честото обаждане на оператори ВМЪКНЕТЕ, АКТУАЛИЗИРАНЕИ ИЗТРИЙтака че пейзажът се променя много бързо.
Когато инициирате заявка за данни от оператор ИЗБЕРЕТЕ, машината за заявки намира индекса, преминава през неговата дървовидна структура и намира данните, които търси. Какво може да бъде по-лесно? Но нещата се променят, ако издадете декларация за промяна, като напр АКТУАЛИЗИРАНЕ. Да, за първата част от оператора, машината за заявки може отново да използва индекса, за да намери реда, който се променя - това е добра новина. И ако има проста промяна на данни в ред, която не засяга промяната в ключови колони, тогава процесът на промяна ще бъде напълно безболезнен. Но какво ще стане, ако промяната доведе до разделяне на страници, съдържащи данни, или ако стойността на ключова колона бъде променена, което води до преместването й в друг индексен възел - това ще доведе до факта, че може да се наложи индексът да бъде реорганизиран, засягайки всички свързани индекси и операции, резултатът ще бъде повсеместен спад в производителността.
Подобни процеси се случват при обаждане на оператора ИЗТРИЙ. Индексът може да помогне за намиране на данните, които се изтриват, но изтриването на самите данни може да доведе до размяна на страници. Относно оператора ВМЪКНЕТЕ, основният враг на всички индекси: започвате да добавяте много данни, което води до промяна и реорганизация на индексите и всички страдат.
Така че помислете за типовете заявки към вашата база данни, когато обмисляте какъв тип индекси и колко да създадете. Повече не означава по-добро. Преди да добавите нов индекс към таблица, помислете не само за цената на основните заявки, но и за цената на дисковото пространство, разходите за поддръжка и индексите, което може да доведе до ефект на доминото за други операции. Вашата стратегия за проектиране на индекс е един от най-важните аспекти на вашето внедряване и трябва да включва много съображения, от размера на индекса, броя на уникалните стойности до типа на заявките, поддържани от индекса.

Необходимо ли е да се създаде клъстерен индекс в колона с първичен ключ?

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

Но какво ще стане, ако индексирате изглед, ще остане ли изглед?

Представянето е виртуална маса A, който генерира данни от една или повече таблици. По същество това е наименувана заявка, която извлича данни от основните таблици, когато правите заявка за този изглед. Можете да подобрите производителността на заявката, като създадете клъстериран индекс и неклъстерирани индекси в този изглед, подобно на това как създавате индекси на таблица, но основното предупреждение е, че първо създавате клъстерен индекс и след това можете да създадете неклъстерен един.
Когато се създаде индексиран изглед (материализиран изглед), тогава самата дефиниция на изглед остава отделен обект. В крайна сметка това е просто твърдо кодирано изявление ИЗБЕРЕТЕСъхранените в базата данни. Но индексът е съвсем различна история. Когато създавате клъстерен или неклъстериран индекс в изглед, данните се съхраняват физически на диск, точно като обикновен индекс. Освен това, когато данните се променят в основните таблици, индексът на изгледа се променя автоматично (което означава, че може да искате да избегнете индексирането на изгледите на таблици, които се променят често). Така или иначе изгледът си остава изглед - поглед към масите, но току-що направен този момент, със съответните индекси.
Преди да можете да създадете индекс на изглед, той трябва да отговаря на няколко ограничения. Например един изглед може да препраща само към базови таблици, но не и към други изгледи, и тези таблици трябва да са в същата база данни. Всъщност има много други ограничения, така че не забравяйте да се обърнете към документацията за SQL сървърза всички мръсни подробности.

Защо да използвате покриващ индекс вместо съставен индекс?

Първо, нека се уверим, че разбираме разликата между тях. Съставният индекс е просто нормален индекс, който включва повече от една колона. Множество ключови колони могат да се използват, за да се гарантира уникалността на всеки ред на таблицата, също така е възможно първичният ключ да се състои от няколко колони, за да се гарантира неговата уникалност, или се опитвате да оптимизирате изпълнението на често извиквани заявки за множество колони. Като цяло обаче, колкото повече ключови колони съдържа индексът, толкова по-малко ефективен е този индекс и затова съставните индекси трябва да се използват разумно.
Както беше казано, една заявка може да извлече огромни ползи, ако всички необходими данни са незабавно разположени на листата на индекса, точно както самия индекс. Това не е проблем за клъстериран индекс, т.к всички данни вече са там (ето защо е толкова важно да мислите внимателно, когато създавате клъстерен индекс). Но негрупираният индекс на листата съдържа само ключовите колони. Оптимизаторът на заявките се нуждае от допълнителни стъпки за достъп до всички други данни, което може да причини значителни допълнителни разходи за изпълнение на вашите заявки.
Тук на помощ идва покривният индекс. Когато дефинирате неклъстъриран индекс, можете да посочите допълнителни колони в допълнение към вашите ключови. Да кажем например, че вашето приложение често изисква данни от колони. OrderIDИ Дата на поръчкана масата Продажби:
SELECT OrderID, OrderDate FROM Sales WHERE OrderID = 12345;
Можете да създадете съставен неклъстерен индекс и в двете колони, но колоната OrderDate само ще увеличи разходите за поддържане на индекса и няма да служи като особено полезна ключова колона. Най-доброто решение би било да се създаде покриващ индекс с ключова колона OrderIDи допълнителна включена колона Дата на поръчка:
CREATE NONCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE(OrderDate);
По този начин вие избягвате недостатъците на индексирането на излишни колони, като същевременно запазвате предимствата на съхраняването на данни в листа при изпълнение на заявки. Включената колона не е част от ключа, но данните се съхраняват точно на крайния възел, индексния лист. Това може да подобри производителността на заявките без допълнителни разходи. Освен това има по-малко ограничения за колоните, включени в покриващия индекс, отколкото за ключовите колони в индекса.

Има ли значение броят на дубликатите в ключова колона?

Когато създавате индекс, трябва да опитате да намалите броя на дубликатите във вашите ключови колони. Или по-точно: опитайте се да запазите съотношението на дублираната стойност възможно най-ниско.
Ако работите със съставен индекс, тогава дублирането се прилага към всички ключови колони като цяло. Една колона може да съдържа много дублиращи се стойности, но повторението между всички колони в индекса трябва да бъде сведено до минимум. Например създавате съставен негрупиран индекс върху колони Първо имеИ фамилия, можете да имате много Джони и много Ду, но искате да имате възможно най-малко Джони или още по-добре само един Джон Доу.
Коефициентът на уникалност на стойностите на ключовата колона се нарича селективност на индекса. Колкото повече са уникалните стойности, толкова по-висока е селективността: един уникален индекс има възможно най-високата селективност. Машината за заявки много обича колони с висока стойност на селективност, особено ако тези колони се появяват в клаузите WHERE на вашите най-често изпълнявани заявки. Колкото по-висока е селективността на индекса, толкова по-бързо машината за заявки може да намали размера на получения набор от данни. Недостатъкът, разбира се, е, че колоните с относително малко уникални стойности рядко са добри кандидати за индексиране.

Възможно ли е да се създаде негрупиран индекс само върху конкретно подмножество от данни на ключова колона?

По подразбиране неклъстерният индекс съдържа един ред за всеки ред на таблицата. Разбира се, можете да кажете същото за клъстерен индекс, като се има предвид, че индексът е таблицата. Но що се отнася до неклъстърирания индекс, връзката едно към едно е важна концепция, защото от версията SQL Server 2008, имате възможност да създадете филтрируем индекс, който ограничава редовете, включени в него. Филтрираният индекс може да подобри производителността на заявките, защото той е по-малък и съдържа филтрирани, по-точни статистики от всички таблични - това води до по-добри планове за изпълнение. Филтрираният индекс също така изисква по-малко място за съхранение и по-ниски разходи за поддръжка. Индексът се актуализира само когато данните, съответстващи на филтъра, се променят.
Освен това лесно се създава филтрируем индекс. В оператора СЪЗДАВАНЕ НА ИНДЕКСпросто трябва да посочите КЪДЕТОсъстояние на филтриране. Например, можете да филтрирате всички редове, съдържащи NULL от индекса, както е показано в кода:
CREATE NONCLUSTERED INDEX ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) WHERE CarrierTrackingNumber IS NOT NULL;
Всъщност можем да филтрираме всички данни, които не са важни при критични заявки. Но бъдете внимателни, защото. SQL сървърналага няколко ограничения върху индексите, които могат да се филтрират, като например невъзможността да се създаде индекс, който може да се филтрира върху изглед, така че прочетете внимателно документацията.
Също така може да се случи, че можете да постигнете подобни резултати чрез създаване на индексиран изглед. Филтрираният индекс обаче има няколко предимства, като например способността да намалите разходите за поддръжка и да подобрите качеството на вашите планове за изпълнение. Филтрираните индекси също могат да бъдат възстановени онлайн. Опитайте с индексиран изглед.

И пак малко от преводача

Целта на появата на този превод на страниците на Habrahabr беше да ви разкаже или напомни за блога SimpleTalk от редгейт.
Публикува много забавни и интересни записи.
Не съм свързан с продуктите на компанията редгейт, нито с продажбата им.

Както обещахме, книги за тези, които искат да знаят повече
Ще препоръчам три много добри книги от себе си (връзките водят към запалвамверсии в магазина Amazon):

По принцип можете да отваряте прости индекси
  • новодошлите
  • индекс
    • Добави тагове
    Microsoft SQL Server 2012 T-SQL Fundamentals (справочник за разработчици)
    Автор Ицик Бен-Ган
    Дата на публикуване: 15 юли 2012 г
    Авторът, майстор на занаята си, дава основни познания за работа с бази данни.
    Ако сте забравили всичко или никога не сте знаели, тогава определено си струва да прочетете.

    IN този материалще бъдат разгледани обекти от база данни Microsoft SQL сървъркак индекси, Ще научите какво представляват индексите, какви са видовете индекси, как да ги създавате, оптимизирате и изтривате.

    Какво представляват индексите в база данни?

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

    Типове индекси в Microsoft SQL Server

    В Microsoft SQL Server съществуват следните типове индекси:

    • струпани (струпани) е индекс, който съхранява таблични данни, сортирани по стойността на индексния ключ. Една таблица може да има само един клъстериран индекс, тъй като данните могат да бъдат сортирани само в един ред. Когато е възможно, всяка таблица трябва да има клъстерен индекс, ако дадена таблица няма клъстерен индекс, такава таблица се нарича " куп". Клъстъриран индекс се създава автоматично при създаване на ограничения на ОСНОВНИЯ КЛЮЧ ( първичен ключ) и UNIQUE, ако клъстерираният индекс на таблицата все още не е дефиниран. В случай на създаване на клъстериран индекс върху таблица ( купчини), който има неклъстерирани индекси, всички те трябва да бъдат изградени отново след създаването.
    • негрупирани (негрупирани) е индекс, който съдържа стойността на ключа и указател към низа с данни, съдържащ стойността на този ключ. Една таблица може да има множество неклъстерирани индекси. Неклъстерни индекси могат да бъдат създадени върху таблици със или без клъстерен индекс. Именно този тип индекс се използва за подобряване на производителността на често използвани заявки, тъй като неклъстерните индекси осигуряват бързо търсене и достъп до данни по ключови стойности;
    • филтрируеми (филтриран) е оптимизиран неклъстъриран индекс, който използва филтърен предикат за индексиране на част от редовете в таблица. Ако е добре проектиран, този тип индекс може да подобри производителността на заявките и да намали разходите за поддръжка и съхранение на индекса в сравнение с индексите с пълна таблица;
    • Единствен по рода си (Единствен по рода си) е индекс, който гарантира, че няма дубликати ( идентичен) индексират ключовите стойности, като по този начин гарантират уникалността на редовете по даден ключ. И клъстерираните, и неклъстерираните индекси могат да бъдат уникални. Ако създадете уникален индекс на множество колони, индексът гарантира, че всяка комбинация от стойности в ключа е уникална. Когато създавате PRIMARY KEY или UNIQUE ограничения, SQL Server автоматично създава уникален индекс на ключови колони. Уникален индекс може да бъде създаден само ако таблицата в момента няма дублиращи се стойности за ключови колони;
    • колонен (колонен магазин) е индекс, базиран на технология за колонно съхранение на данни. Този видИндексът е ефективен за големи хранилища на данни, защото може да увеличи производителността на заявките за съхранение до 10 пъти и също така да намали размера на данните до 10 пъти, тъй като данните в индекса Columnstore са компресирани. Има както клъстерирани колонни индекси, така и неклъстерирани такива;
    • пълен текст (пълен текст) е специален тип индекс, който осигурява ефективна поддръжка за търсене на сложни думи в данни от символен низ. Процесът на създаване и поддържане на индекс на пълен текст се нарича " пълнеж". Има такива видове попълване като: пълно попълване и попълване въз основа на проследяване на промените. По подразбиране SQL Server напълно попълва нов индекс с пълен текст веднага щом бъде създаден, но това може да изисква доста ресурси в зависимост от размера на таблицата, така че е възможно да се забави пълното попълване. Зареждането за проследяване на промените се използва за поддържане на индекс на пълен текст, след като първоначално е бил напълно попълнен;
    • Пространствени (Пространствени) е индекс, който позволява по-ефективно използване на специфични операции върху характеристики на колони с геометричен или географски тип данни. Този тип индекс може да бъде създаден само в пространствена колона и таблицата, за която е дефиниран пространствен индекс, трябва да съдържа първичен ключ ( ПЪРВИЧЕН КЛЮЧ);
    • XMLе друг специален тип индекс, който е предназначен за колони с тип XML данни. XML индексът подобрява ефективността на заявките за XML колони. Има два вида XML индекс: първичен и вторичен. Основният XML индекс индексира всички тагове, стойности и пътища, съхранени в XML колоната. Може да се създаде само ако таблицата има клъстерен индекс на първичния ключ. Вторичен XML индекс може да бъде създаден само ако таблицата има първичен XML индекс и се използва за подобряване на производителността на заявка за определен тип достъп до XML колона, така че има няколко вида вторични индекси: PATH, VALUE и PROPERTY;
    • Има и специални индекси за таблици, оптимизирани за памет ( OLTP в паметта), като например: хеш ( хеш) индекси и оптимизирани за памет неклъстерирани индекси, които са създадени за сканиране на обхват и подредени сканирания.

    Създаване и премахване на индекси в Microsoft SQL Server

    Преди да започнете да създавате индекс, той трябва да бъде добре проектиран, за да се използва ефективно този индекс, тъй като лошо проектираните индекси не могат да увеличат производителността, а по-скоро да я намалят. Например, голям брой индекси в таблица намалява производителността на изразите INSERT, UPDATE, DELETE и MERGE, защото когато данните в таблицата се променят, всички индекси трябва да бъдат променени съответно. Общи препоръкище разгледаме дизайна на индекса в отделна статия, но сега нека преминем към процеса на създаване и изтриване на индекси.

    Забележка! Моят SQL сървър е Microsoft SQL Server 2016 Express.

    Създаване на индекси

    За да създадете индекси в Microsoft SQL Server, има два начина: първият е използването на графичния интерфейс на средата на SQL Server Management Studio (SSMS), а вторият е използването на езика Transact-SQL, ние ще анализираме и двата метода с вас.

    Изходни данни за примери

    Нека си представим, че имаме продуктова таблица, наречена TestTable, която има три колони:

    • ProductId - идентификатор на продукта;
    • ProductName - име на продукта;
    • CategoryID - продуктова категория.
    CREATE TABLE TestTable(ProductId INT IDENTITY(1,1) NOT NULL, ProductName VARCHAR(50) NOT NULL, CategoryID INT NULL,) ON

    Пример за създаване на клъстерен индекс

    Както казах, клъстерният индекс се създава автоматично, ако например, когато създаваме таблица, посочим конкретна колона като първичен ключ ( ПЪРВИЧЕН КЛЮЧ), но тъй като не го направихме, нека да разгледаме пример за самостоятелно създаване на клъстерен индекс.

    За да създадем клъстериран индекс, можем да посочим първичен ключ за таблица и по този начин клъстерен индекс ще бъде създаден автоматично, или можем да създадем клъстерен индекс отделно.

    Например, нека просто създадем клъстерен индекс, без да създаваме първичен ключ. Нека първо го направим с Управлениестудио.

    Отворете SSMS и в изследователя на обекти намерете желаната таблица и щракнете с десния бутон върху елемента " Индекси", избирам " Създаване на индекс” и тип индекс, в нашия случай „ струпани».


    Формата ще се отвори Нов индекс”, където трябва да посочим името на новия индекс ( трябва да е уникален в рамките на таблицата), ние също така посочваме дали този индекс ще бъде уникален, ако говорим за идентификатора на продукта в продуктовата таблица, тогава, разбира се, той трябва да бъде уникален. След това изберете колоната индексен ключ), въз основа на който ще създадем клъстерен индекс, т.е. редовете с данни в таблицата ще бъдат сортирани с помощта на бутона " Добавете».


    След като въведете всички необходими параметри, щракнете върху " Добре”, в резултат на което ще бъде създаден клъстерен индекс.


    По подобен начин може да се създаде клъстерен индекс, използвайки T-SQL израз ИНДЕКС НА СЪЗДАТЕЛЯнапример така

    CREATE UNIQUE CLUSTERED INDEX IX_Clustered ON TestTable (ProductId ASC) GO

    Или, както вече казахме, може да се използва и инструкцията за създаване на първичен ключ, например

    ALTER TABLE TestTable ADD CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (ProductId ASC) GO

    Пример за създаване на неклъстъриран индекс с включени колони

    Сега нека да разгледаме пример за създаване на неклъстъриран индекс, докато указваме колоните, които няма да бъдат ключови, но ще бъдат включени в индекса. Това е полезно, когато създавате индекс за конкретна заявка, например, за да може индексът да покрие изцяло заявката, т.е. съдържаше всички колони ( това се нарича „Поискване на покритие“). Покритието на заявката подобрява производителността, тъй като оптимизаторът на заявки може да намери всички стойности на колони в индекс, без да се налага достъп до данни от таблица, което води до по-малко I/O дискови операции. Но имайте предвид, че включването на неключови колони в индекс води до увеличаване на размера на индекса, т.е. съхраняването на индекса ще изисква повече дисково пространство и може също да доведе до влошаване на производителността за INSERT, UPDATE, DELETE и MERGE операции на основната таблица.

    За да създадем неклъстериран индекс, използвайки графичния интерфейс на Management Studio, ние също намираме желаната таблица и индекси, само в този случай избираме " Създаване -> Неклъстъриран индекс».


    След отваряне на формата Нов индекс» посочваме името на индекса, добавяме ключова колона или колони с помощта на бутона « Добавете”, например, за нашия тестов случай, нека посочим CategoryID.



    В Transact-SQL ще изглежда така.

    CREATE NONCLUSTERED INDEX IX_NonClustered ON TestTable (CategoryID ASC) INCLUDE (ProductName) GO

    Пример за изтриване на индекс в Microsoft SQL Server

    За да премахнете индекс, щракнете с десния бутон върху желания индекс и щракнете върху " Изтрий", след което потвърдете действието си, като натиснете " Добре».

    или можете също да използвате изявлението ИНДЕКС НА СПАДАНЕ, Например

    DROP INDEX IX_NonClustered ON TestTable

    Обърнете внимание, че изразът DROP INDEX не се прилага за индекси, които са създадени чрез създаване на ограничения PRIMARY KEY и UNIQUE. В този случай трябва да използвате оператора ALTER TABLE с клаузата DROP CONSTRAINT, за да премахнете индекса.

    Оптимизиране на индекси в Microsoft SQL Server

    В резултат на актуализиране, добавяне или изтриване на данни в таблици, SQL Server автоматично прави подходящи промени в индексите, но с течение на времето всички тези промени могат да причинят фрагментация на данните в индекса, т.е. те ще бъдат разпръснати из цялата база данни. Фрагментирането на индексите води до намаляване на производителността на заявките, поради което периодично е необходимо да се извършват операции по поддръжка на индекси, а именно дефрагментиране, като операции по реорганизация и възстановяване на индекси.

    Кога да се използва реорганизация на индекса и кога да се изгради отново?

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

    Изберете Object_name (t1.object_id) като Nametable, T1.INDEX_ID като Indexid, T2.name като IndexName, T1.AVG_FRAGMENTATION_IN_PERCENT като фрагментация от sys.dm_db_index_physical_stats (db_id (), null, null, null, null) като t1 left join sys. индексира КАТО T2 НА T1.object_id = T2.object_id И T1.index_id = T2.index_id

    В този случай се интересуваме от колоната ср.фрагментация_в_процент, т.е. процент на логическа фрагментация.

    • Ако степента на фрагментация е по-малка от 5%, тогава реорганизацията или повторното изграждане на индекса не трябва да се изпълнява изобщо;
    • Ако степента на фрагментация е от 5 до 30%, тогава има смисъл да се извърши реорганизация на индекса, тъй като тази операцияизползва минимални системни ресурси и не изисква дългосрочни заключвания;
    • Ако степента на фрагментация е повече от 30%, тогава е необходимо да се изгради отново индексът, тъй като тази операция със значителна фрагментация има по-голям ефект от операцията за реорганизация на индекса.

    Лично аз мога да добавя следното, ако имате малка фирма и базата данни не изисква максимална ефективност в режим 24/7, т.е. Тъй като това не е суперактивна база данни, можете безопасно периодично да извършвате операция за възстановяване на индекса, без дори да определяте степента на фрагментация.

    Реорганизация на индекса

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

    Можете да използвате или графичен SSMS инструмент, или Transact-SQL израз, за ​​да реорганизирате индекс.

    Реорганизиране на индекс с помощта на Management Studio


    Реорганизация на индекса с Transact-SQL

    ALTER INDEX IX_NonClustered ON TestTable REORGANIZE GO

    Възстановяване на индекси

    Възстановяване на индексае процесът, чрез който старият индекс се премахва и се създава нов, което води до елиминиране на фрагментацията.

    Има два начина за възстановяване на индекси.

    Първо. Чрез използване на оператора ALTER INDEX с клаузата REBUILD. Този оператор замества оператора DBCC DBREINDEX. Това обикновено е методът, използван за групови преизграждания на индекси.

    Пример

    ALTER INDEX IX_NonClustered ON TestTable REBUILD GO

    И второто, използвайки оператора CREATE INDEX с клаузата DROP_EXISTING. Може да се използва например за повторно изграждане на индекс с промяна в неговата дефиниция, т.е. добавяне или премахване на ключови колони.

    Пример

    CREATE NONCLUSTERED INDEX IX_NonClustered ON TestTable (CategoryID ASC) WITH(DROP_EXISTING = ON) GO

    Функционалността за повторно изграждане също е налична в Management Studio. Кликнете с десния бутонна желания индекс Възстановете».


    С това приключваме материала за основите на индексите в Microsoft SQL Server, ако проявявате интерес T-SQL езикПрепоръчвам да прочетете моята книга