ÎN acest material astfel de obiecte de bază de date vor fi luate în considerare Microsoft SQL Server Cum indici Veți afla ce sunt indecșii, ce tipuri de indici există, cum să îi creați, să-i optimizați și să-i ștergeți.

Ce sunt indecșii dintr-o bază de date?

Index este un obiect de bază de date care este o structură de date constând din chei construite din una sau mai multe coloane ale unui tabel sau vizualizare și pointeri care se mapează la locul în care sunt stocate datele specificate. Indecii sunt proiectați pentru a prelua mai rapid rândurile dintr-un tabel, cu alte cuvinte, indecșii permit căutarea rapidă a datelor într-un tabel, ceea ce îmbunătățește foarte mult performanța interogărilor și a aplicației. De asemenea, indecșii pot fi utilizați pentru a asigura rânduri unice într-un tabel, asigurând astfel integritatea datelor.

Tipuri de indexuri în Microsoft SQL Server

În Microsoft SQL Server există următoarele tipuri de indici:

• Grupate (Grupate) este un index care stochează date de tabel sortate după valoarea cheii de index. Un tabel poate avea un singur index grupat deoarece datele pot fi sortate doar într-o singură ordine. Dacă este posibil, fiecare tabel ar trebui să aibă un index cluster dacă un tabel nu are un index cluster, tabelul se numește "; într-o grămadă" Un index grupat este creat automat atunci când creați constrângeri PRIMARY KEY ( cheie primară) și UNIQUE dacă un index grupat pe tabel nu a fost încă definit. Dacă creați un index grupat pe un tabel ( grămezi) care conține indecși non-cluster, apoi toți trebuie reconstruiți după creare.
• Negrupate (Negrupate) este un index care conține valoarea unei chei și un pointer către un rând de date care conține valoarea acelei chei. Un tabel poate avea mai mulți indecși non-cluster. Indicii non-cluster pot fi creați pe tabele cu sau fără un index cluster. Acest tip de index este utilizat pentru a îmbunătăți performanța interogărilor frecvent utilizate, deoarece indecșii non-cluster oferă căutare rapidă și acces la date după valorile cheie;
• Filtrabil (Filtrată) este un index optimizat nonclustered care utilizează un predicat de filtru pentru a indexa un subset de rânduri dintr-un tabel. Dacă este proiectat bine, acest tip de index poate îmbunătăți performanța interogărilor și, de asemenea, poate reduce costurile de întreținere și stocare a indexului în comparație cu indecșii cu tabel complet;
• Unic (Unic) este un index care asigură că nu există duplicate ( identic) valorile cheii index, garantând astfel unicitatea rândurilor pentru această cheie. Atât indecșii grupați cât și cei care nu sunt în cluster pot fi unici. Dacă creați un index unic pe mai multe coloane, indexul asigură că fiecare combinație de valori din cheie este unică. Când creați constrângeri PRIMARY KEY sau UNIQUE, serverul SQL creează automat un index unic pe coloanele cheie. Un index unic poate fi creat numai dacă tabelul nu are în prezent valori duplicate în coloanele cheie;
• Columnar (Magazin de coloane) este un index bazat pe tehnologia de stocare a datelor în coloană. Acest tip de index este eficient pentru depozitele mari de date, deoarece poate crește performanța interogărilor către depozit de până la 10 ori și, de asemenea, poate reduce dimensiunea datelor de până la 10 ori, deoarece datele din indexul Columnstore sunt comprimate. Există atât indecși de coloane grupați, cât și non-cluster;
• Text complet (Text integral) este un tip special de index care oferă suport eficient pentru căutări complexe de cuvinte pe datele șirurilor de caractere. Procesul de creare și menținere a unui index full-text se numește „ umplere" Există astfel de tipuri de umplere precum: umplere completă și umplere bazată pe urmărirea modificărilor. În mod implicit, SQL Server populează complet un nou index full-text imediat după ce acesta este creat, dar acest lucru poate necesita o cantitate semnificativă de resurse în funcție de dimensiunea tabelului, astfel încât este posibil să se întârzie populația completă. Seeding-ul bazat pe urmărirea modificărilor este utilizat pentru a menține indexul de text complet după ce este inițial completat;
• Spațial (Spațial) este un index care permite mai mult utilizare eficientă operații specifice asupra obiectelor spațiale în coloane cu un tip de date geometrie sau geografie. Acest tip de index poate fi creat doar pe o coloană spațială, iar tabelul pe care este definit indexul spațial trebuie să conțină o cheie primară ( CHEIA PRIMARĂ);
• XML este un alt tip special de index care este conceput pentru coloane cu un tip de date XML. Datorită indexului XML, eficiența procesării interogărilor împotriva coloanelor XML crește. Există două tipuri de index XML: primar și secundar. Un index XML primar indexează toate etichetele, valorile și căile stocate într-o coloană XML. Poate fi creat numai dacă tabelul are un index grupat pe cheia primară. Un index XML secundar poate fi creat doar dacă tabelul are un index XML primar și este folosit pentru a îmbunătăți performanța interogărilor pe un anumit tip de acces la coloana XML, în acest sens, există mai multe tipuri de indecși secundari: PATH , VALOARE și PROPRIETATE;
• Există, de asemenea, indecși speciali pentru tabelele optimizate pentru memorie ( OLTP în memorie) cum ar fi: Hash ( Hash) indecși optimizați pentru memorie și indecși non-cluster care sunt creați pentru scanări de interval și scanări ordonate.

Crearea și ștergerea indecșilor în Microsoft SQL Server

Înainte de a începe să creați un index, trebuie să îl proiectați bine pentru a utiliza indexul în mod eficient, deoarece indecșii proiectați prost pot să nu îmbunătățească performanța, ci mai degrabă să o reducă. De exemplu, a avea un număr mare de indici pe un tabel reduce performanța instrucțiunilor INSERT, UPDATE, DELETE și MERGE deoarece atunci când datele din tabel se modifică, toți indecșii trebuie actualizați corespunzător. Ne vom uita la recomandările generale pentru proiectarea indexurilor într-un articol separat, dar acum să trecem direct la examinarea procesului de creare și ștergere a indexurilor.

Nota! Serverul meu SQL este Microsoft SQL Server 2016 Express.

Crearea de indici

Există două moduri de a crea indici în Microsoft SQL Server: prima este utilizarea interfeței grafice a mediului SQL Server Management Studio (SSMS), iar a doua este utilizarea limbajului Transact-SQL, vom analiza ambele metode.

Date sursă pentru exemple

Să ne imaginăm că avem un tabel de produse numit TestTable, care are trei coloane:

• ProductId – identificator de produs;
• ProductName – numele produsului;
• CategoryID – categorie de produs.

CREATE TABLE TestTable(ProductId INT IDENTITY(1,1) NOT NULL, ProductName VARCHAR(50) NOT NULL, CategoryID INT NULL,) ON

Exemplu de creare a unui index grupat

După cum am spus deja, un index grupat este creat automat dacă, de exemplu, la crearea unui tabel, specificăm o anumită coloană ca cheie primară ( CHEIA PRIMARĂ), dar din moment ce nu am făcut asta, să ne uităm la un exemplu de creare a unui index cluster.

Pentru a crea un index cluster, putem specifica o cheie primară pentru tabel și astfel indexul cluster va fi creat automat, sau putem crea separat un index cluster.

De exemplu, să creăm un index grupat, fără a crea o cheie primară. Mai întâi vom face acest lucru folosind Management Studio.

Deschideți SSMS și găsiți tabelul dorit în browserul de obiecte și faceți clic dreapta pe elementul „ Indici", selectați " Creați index" și tipul de index, în cazul nostru " Grupate».

Forma „ Index nou", unde trebuie să specificăm numele noului index ( trebuie să fie unic în tabel), indicăm și dacă acest index va fi unic dacă vorbim despre identificatorul de produs din tabelul de produse, atunci, desigur, trebuie să fie unic; Apoi selectați coloana ( cheie index), pe baza căruia vom crea un index grupat, i.e. rândurile de date din tabel vor fi sortate folosind „ Adăuga».

După introducerea tuturor parametrilor necesari, faceți clic pe „ Bine", eventual va fi creat un index cluster.

În mod similar, se poate crea un index cluster folosind o instrucțiune T-SQL INDEXUL CREATURII, de exemplu, așa

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

Sau, așa cum am spus deja, am putea folosi și o declarație pentru a crea o cheie primară, de exemplu

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

Exemplu de creare a unui index nonclustered cu coloane incluse

Acum să ne uităm la un exemplu de creare a unui index neclustrat, în care vom indica coloanele care nu vor fi cheie, dar vor fi incluse în index. Acest lucru este util în cazurile în care creați un index pentru o anumită interogare, de exemplu, astfel încât indexul să acopere complet interogarea, de exemplu. conținea toate coloanele ( aceasta se numește „Solicitare acoperire”). Acoperirea interogărilor îmbunătățește performanța deoarece optimizatorul de interogări poate găsi toate valorile coloanelor din index fără a accesa datele din tabel, ceea ce duce la mai puține operațiuni I/O pe disc. Dar amintiți-vă că includerea coloanelor non-cheie în index implică o creștere a dimensiunii indexului, de exemplu. stocarea indexului va necesita mai mult spațiu pe disc și poate duce, de asemenea, la o performanță redusă pentru operațiunile INSERT, UPDATE, DELETE și MERGE din tabelul de bază.

Pentru a crea un index non-cluster folosind GUI Management Studio, găsim și tabelul dorit și elementul de indexuri, doar în în acest caz, noi alegem" Creare -> Index non-clustered».

După deschiderea formularului " Index nou"specificăm numele indexului, adăugăm o coloană sau coloane cheie folosind butonul " Adăuga", de exemplu, pentru cazul nostru de testare, să specificăm CategoryID.

În Transact-SQL ar arăta așa.

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

Exemplu de ștergere a unui index în Microsoft SQL Server

Pentru a elimina un index, puteți face clic dreapta pe indicele cerutși apăsați „ Şterge", apoi confirmați acțiunile dvs. făcând clic pe „ Bine».

sau puteți folosi și instrucțiunile INDICE DE CĂDERARE, De exemplu

DROP INDEX IX_NonClustered ON TestTable

Trebuie remarcat faptul că instrucțiunea DROP INDEX nu se aplică indecșilor care au fost creați prin crearea constrângerilor PRIMARY KEY și UNIQUE. În acest caz, pentru a elimina indexul, trebuie să utilizați instrucțiunea ALTER TABLE cu clauza DROP CONSTRAINT.

Optimizarea indicilor în Microsoft SQL Server

Ca urmare a actualizării, adăugării sau ștergerii datelor din tabelele SQL, serverul efectuează automat modificări corespunzătoare indecșilor, dar în timp toate aceste modificări pot provoca fragmentarea datelor din index, adică. vor ajunge împrăștiați în baza de date. Fragmentarea indexurilor atrage după sine o scădere a performanței interogărilor, de aceea periodic este necesară efectuarea operațiunilor de întreținere a indexului, și anume defragmentare, precum operațiunile de reorganizare și reconstrucție a indexului.

Când să folosiți reorganizarea indexului și când să reconstruiți?

Pentru a răspunde la această întrebare, trebuie mai întâi să determinați gradul de fragmentare a indexului, deoarece în funcție de fragmentarea indexului, una sau alta metodă de defragmentare va fi de preferat și mai eficientă. Puteți utiliza funcția tabel de sistem pentru a determina gradul de fragmentare a indexului sys.dm_db_index_physical_stats, care returnează informații detaliate despre dimensiunea și fragmentarea indicilor. De exemplu, folosind următoarea interogare, puteți afla gradul de fragmentare a indexului pentru toate tabelele din baza actuală date.

SELECT OBJECT_NAME(T1.object_id) AS NameTable, T1.index_id AS IndexId, T2.name AS IndexName, T1.avg_fragmentation_in_percent AS Fragmentation FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, LEFT NULL) AS T sys1, LEFT NULL. indexează AS T2 ON T1.object_id = T2.object_id ȘI T1.index_id = T2.index_id

În acest caz, ne interesează rubrica medie_fragmentare_în_procent, adică procentul de fragmentare logica.

• Dacă gradul de fragmentare este mai mic de 5%, atunci reorganizarea sau reconstruirea indexului nu trebuie începută deloc;
• Dacă gradul de fragmentare este de la 5 la 30%, atunci este logic să începem reorganizarea indicelui, deoarece această operațiune utilizează resurse minime de sistem și nu necesită blocări pe termen lung;
• Dacă gradul de fragmentare este mai mare de 30%, atunci este necesară reconstrucția indicelui, deoarece această operațiune, cu fragmentare semnificativă, dă un efect mai mare decât operația de reorganizare a indexului.

Personal, pot adăuga următoarele dacă aveți o companie mică și baza de date nu necesită producție maximă 24 de ore pe zi, adică. Deoarece nu este o bază de date super activă, puteți efectua periodic în siguranță operația de reconstrucție a indecșilor, fără a determina măcar gradul de fragmentare.

Reorganizarea indicilor

Reorganizarea indexului este un proces de defragmentare a indexului care defragmentează indecșii grupați la nivel de frunză și non-cluster pe tabele și vizualizări prin reordonarea fizică a paginilor la nivel de frunză în conformitate cu ordinea logică ( de la stânga la dreapta) nodurile de capăt.

Puteți utiliza fie instrumentul grafic SSMS, fie instrucțiunea Transact-SQL pentru a reorganiza indexul.

Reorganizarea unui index folosind Management Studio

Reorganizarea unui index folosind Transact-SQL

ALTER INDEX IX_NonClustered ON TestTable REORGANIZE GO

Reconstruirea indicilor

Reconstruirea indexului este un proces care elimină vechiul index și creează unul nou, eliminând astfel fragmentarea.

Puteți folosi două metode pentru a reconstrui indecși.

Primul. Folosind instrucțiunea ALTER INDEX cu clauza REBUILD. Această instrucțiune înlocuiește instrucțiunea DBCC DBREINDEX. De obicei, aceasta este metoda folosită pentru a reconstrui în masă indici.

Exemplu

ALTER INDEX IX_NonClustered ON TestTable RECONSTRUIRE GO

Și al doilea, folosind instrucțiunea CREATE INDEX cu clauza DROP_EXISTING. Poate fi folosit, de exemplu, pentru a reconstrui un index prin modificarea definiției acestuia, de ex. adăugarea sau eliminarea coloanelor cheie.

Exemplu

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

Funcționalitatea de reconstrucție este disponibilă și în Management Studio. Faceți clic dreapta pe indexul dorit " Reconstrui».

Aceasta încheie materialul despre elementele de bază ale indicilor în Microsoft SQL Server Dacă sunteți interesat de limbajul T-SQL, vă recomand să citiți cartea mea „.

Și indici, asta tabele de căutare speciale, care motor de căutare bazele de date pot fi folosite pentru a accelera recuperarea datelor. Mai simplu spus, un index este un pointer către datele dintr-un tabel. Indexul din baza de date este foarte asemănător cu indexul din spatele cărții.

De exemplu, dacă doriți link-uri către toate paginile dintr-o carte pe un anumit subiect, consultați mai întâi indexul, care listează toate subiectele în ordine alfabetică și apoi trece la una sau mai multe numere de pagină specifice.

Indexul ajută la accelerarea interogărilor și propozițiilor, dar încetinește introducerea datelor, cu declarații UPDATEŞi INTRODUCE. Indecșii pot fi creați sau șterși fără a afecta datele.

Crearea unui index implică o declarație CREAȚI INDEX, care vă permite să denumiți un index pentru a specifica tabelul și ce coloană sau coloane să indexați și pentru a indica dacă indexul este în ordine crescătoare sau descrescătoare.

Indicii pot fi, de asemenea, unici, cu limitare UNIC, astfel încât indexul să prevină intrările duplicate într-o coloană sau o combinație de coloane care au un index pe ea.

Comanda CREATE INDEX

Sintaxa de bază CREAȚI INDEX arata asa:

CREATE INDEX nume_index ON nume_tabel;

Indici cu o singură coloană

Un index cu o singură coloană este creat pe o singură coloană din tabel. Sintaxa de bază este următoarea.

CREATE INDEX nume_index ON nume_tabel(nume_coloană);

Indici unici

Indicii unici sunt utilizați nu numai pentru funcționare, ci și pentru a asigura integritatea datelor. Un index unic nu permite introducerea unor valori duplicate în tabel. Sintaxa de bază este următoarea.

CREATE UNIQUE INDEX nume_index pe nume_tabel(nume_coloană);

Indici compoziți

Un index compus este un index pe două sau mai multe coloane ale unui tabel. Sintaxa sa de bază este următoarea.

CREATE INDEX nume_index pe nume_tabel(coloana1, coloana2);

Indiferent dacă creați un index pe o singură coloană sau un index compus, luați în considerare coloanele pe care le puteți utiliza foarte des într-o interogare WHERE ca condiție de filtru.

Dacă există o singură coloană în uz, trebuie să fie ales un index pe o coloană. Dacă există două sau mai multe coloane care sunt adesea folosite ca filtre în clauza WHERE, un index compus va cea mai buna alegere.

Indici impliciti

Indecșii impliciti sunt indecși care sunt creați automat pe serverul bazei de date atunci când este creat un obiect. Indecșii sunt creați automat pe cheia primară și pe constrângerea unică.

Comanda DROP INDEX

Indexul poate fi șters folosind comanda SQL PICĂTURĂ. Ar trebui să fiți atenți când ștergeți un index, deoarece performanța poate fi fie mai lentă, fie mai bună.

Sintaxa de bază arată astfel:

DROP INDEX nume_index;

Puteți privi exemplul de constrângere INDEX pentru a vedea câteva exemple reale despre indici.

Când ar trebui să evitați indici?

Deși indexurile sunt menite să îmbunătățească performanța bazei de date, există momente când ar trebui evitate.

Următoarele instrucțiuni indică când trebuie reconsiderată utilizarea indexului.

• Indecii nu trebuie folosiți pe tabele mici.
• Tabelele care au operațiuni mari frecvente de actualizare sau inserare.
• Indecii nu trebuie utilizați pe coloanele care conțin un număr mare de valori nule.
• Coloanele care sunt manipulate frecvent nu trebuie indexate.

Una dintre cele mai importante moduri de a realiza performante ridicate SQL Server este utilizarea indicilor. Un index accelerează procesul de interogare, oferind acces rapid la rândurile de date dintr-un tabel, la fel ca un index dintr-o carte vă ajută să găsiți rapid informațiile de care aveți nevoie. In acest articol voi da scurtă prezentare generală indici în SQL Serverși explicați cum sunt organizate în baza de date și cum ajută la accelerarea interogărilor bazei de date.

Indecii sunt creați pe coloanele de tabel și vizualizare. Indecșii oferă o modalitate de a căuta rapid date pe baza valorilor din acele coloane. De exemplu, dacă creați un index pe o cheie primară și apoi căutați un rând de date folosind valorile cheii primare, atunci SQL Server va găsi mai întâi valoarea indexului și apoi va folosi indexul pentru a găsi rapid întregul rând de date. Fără un index, va fi efectuată o scanare completă a tuturor rândurilor din tabel, ceea ce poate avea un impact semnificativ asupra performanței.
Puteți crea un index pe majoritatea coloanelor dintr-un tabel sau vizualizare. Excepția sunt în principal coloanele cu tipuri de date pentru stocarea obiectelor mari ( LOB), ca imagine, text sau varchar(max). De asemenea, puteți crea indecși pe coloane concepute pentru a stoca date în format XML, dar acești indici sunt structurați ușor diferit față de cei standard și luarea în considerare a acestora depășește domeniul de aplicare al acestui articol. De asemenea, articolul nu discută columnstore indici. În schimb, mă concentrez pe acei indecși care sunt cel mai frecvent utilizați în bazele de date SQL Server.
Un index constă dintr-un set de pagini, noduri de index, care sunt organizate într-o structură arborescentă - arbore echilibrat. Această structură este de natură ierarhică și începe cu un nod rădăcină în partea de sus a ierarhiei și noduri frunză, frunzele, în partea de jos, așa cum se arată în figură:


Când interogați o coloană indexată, motorul de interogare pornește în partea de sus a nodului rădăcină și își parcurge drumul în jos prin nodurile intermediare, fiecare strat intermediar conținând informații mai detaliate despre date. Motorul de interogare continuă să se deplaseze prin nodurile de index până când ajunge la nivelul de jos cu frunzele de index. De exemplu, dacă căutați valoarea 123 într-o coloană indexată, motorul de interogare va determina mai întâi pagina la primul nivel intermediar la nivelul rădăcină. În acest caz, prima pagină indică o valoare de la 1 la 100, iar a doua de la 101 la 200, astfel încât motorul de interogare va accesa a doua pagină a acestui nivel intermediar. În continuare, veți vedea că ar trebui să treceți la a treia pagină a următorului nivel intermediar. De aici, subsistemul de interogări va citi valoarea indexului însuși la un nivel inferior. Frunzele de index pot conține fie datele tabelului în sine, fie pur și simplu un pointer către rânduri cu date în tabel, în funcție de tipul de index: index cluster sau index nonclustered.

Index grupat

Un index grupat stochează rândurile reale de date în frunzele indexului. Revenind la exemplul anterior, aceasta înseamnă că rândul de date asociat cu valoarea cheie de 123 va fi stocat în indexul însuși. Caracteristica importanta Un index grupat este că toate valorile sunt sortate într-o anumită ordine, fie crescător, fie descrescător. Prin urmare, un tabel sau o vizualizare poate avea un singur index grupat. În plus, trebuie remarcat faptul că datele dintr-un tabel sunt stocate în formă sortată numai dacă pe acest tabel a fost creat un index grupat.
Un tabel care nu are un index grupat se numește heap.

Index negrupat

Spre deosebire de un index grupat, frunzele unui index nonclustered conțin doar acele coloane ( cheie) prin care se determină acest index și, de asemenea, conține un pointer către rânduri cu date reale din tabel. Aceasta înseamnă că sistemul de subinterogare necesită o operațiune suplimentară pentru a localiza și a prelua datele necesare. Conținutul indicatorului de date depinde de modul în care sunt stocate datele: tabel în cluster sau heap. Dacă un indicator indică un tabel grupat, acesta indică un index grupat care poate fi utilizat pentru a găsi datele reale. Dacă un pointer se referă la un heap, atunci indică un anumit identificator de rând de date. Indecșii nonclustered nu pot fi sortați ca indici clustered, dar puteți crea mai mult de un index nonclustered pe un tabel sau vizualizare, până la 999. Acest lucru nu înseamnă că ar trebui să creați cât mai mulți indecși posibil. Indicii pot fie îmbunătăți, fie pot degrada performanța sistemului. Pe lângă faptul că puteți crea mai mulți indecși non-cluster, puteți include și coloane suplimentare ( coloana inclusă) în indexul dvs.: frunzele indexului vor stoca nu numai valoarea coloanelor indexate în sine, ci și valorile acestor coloane suplimentare neindexate. Această abordare vă va permite să ocoliți unele dintre restricțiile impuse indexului. De exemplu, puteți include o coloană neindexabilă sau puteți ocoli limita de lungime a indexului (900 de octeți în majoritatea cazurilor).

Tipuri de indici

Pe lângă faptul că este fie grupat, fie necluster, indexul poate fi configurat în continuare ca index compus, index unic sau index de acoperire.

Indice compozit

Un astfel de index poate conține mai mult de o coloană. Puteți include până la 16 coloane într-un index, dar lungimea lor totală este limitată la 900 de octeți. Atât indecșii grupați, cât și cei care nu sunt grupați pot fi compuși.

Index unic

Acest index asigură că fiecare valoare din coloana indexată este unică. Dacă indexul este compus, atunci unicitatea se aplică tuturor coloanelor din index, dar nu fiecărei coloane individuale. De exemplu, dacă creați un index unic pe coloane NUMEŞi PRENUME, atunci numele complet trebuie să fie unic, dar sunt posibile duplicate în nume sau prenume.
Un index unic este creat automat atunci când definiți o constrângere de coloană: cheie primară sau constrângere de valoare unică:

• Cheie primară
  Atunci când definiți o constrângere a cheii primare pe una sau mai multe coloane, atunci SQL Server creează automat un index cluster unic dacă nu a fost creat anterior un index cluster (în acest caz, un index unic non-cluster este creat pe cheia primară)
• Unicitatea valorilor
  Atunci când definiți o constrângere asupra unicității valorilor, atunci SQL Server creează automat un index unic non-cluster. Puteți specifica crearea unui index cluster unic dacă nu a fost încă creat niciun index cluster pe tabel

Index de acoperire

Un astfel de index permite unei interogări specifice să obțină imediat toate datele necesare din frunzele indexului fără acces suplimentar la înregistrările tabelului în sine.

Proiectarea indicilor

Oricât de utili pot fi indicii, ei trebuie proiectați cu atenție. Deoarece indexurile pot ocupa un spațiu semnificativ pe disc, nu doriți să creați mai mulți indecși decât este necesar. În plus, indecșii sunt actualizați automat atunci când rândul de date în sine este actualizat, ceea ce poate duce la supraîncărcare suplimentară a resurselor și la degradarea performanței. La proiectarea indexurilor trebuie luate în considerare mai multe considerații referitoare la baza de date și interogările împotriva acesteia.

Baza de date

După cum sa menționat mai devreme, indexurile pot îmbunătăți performanța sistemului deoarece acestea oferă motorului de interogări o modalitate rapidă de a găsi date. Cu toate acestea, ar trebui să țineți cont și de cât de des intenționați să introduceți, să actualizați sau să ștergeți datele. Când modificați datele, indecșii trebuie, de asemenea, modificați pentru a reflecta acțiunile corespunzătoare asupra datelor, ceea ce poate reduce semnificativ performanța sistemului. Luați în considerare următoarele linii directoare atunci când vă planificați strategia de indexare:

• Pentru tabelele care sunt actualizate frecvent, utilizați cât mai puțini indecși.
• Dacă tabelul conține o cantitate mare de date, dar modificările sunt minore, atunci utilizați cât mai mulți indecși este necesar pentru a îmbunătăți performanța interogărilor dvs. Cu toate acestea, gândiți-vă bine înainte de a utiliza indici pe tabele mici, deoarece... Este posibil ca utilizarea unei căutări index să dureze mai mult decât simpla scanare a tuturor rândurilor.
• Pentru indecșii grupați, încercați să păstrați câmpurile cât mai scurte posibil. Cea mai bună abordare este să utilizați un index grupat pe coloanele care au valori unice și nu permit NULL. Acesta este motivul pentru care o cheie primară este adesea folosită ca index grupat.
• Unicitatea valorilor dintr-o coloană afectează performanța indexului. În general, cu cât aveți mai multe duplicate într-o coloană, cu atât indexul are performanțe mai slabe. Pe de altă parte, cu cât sunt mai multe valori unice, cu atât performanța indicelui este mai bună. Folosiți un index unic ori de câte ori este posibil.
• Pentru un index compus, luați în considerare ordinea coloanelor din index. Coloane care sunt folosite în expresii UNDE(de exemplu, WHERE Prenume = „Charlie”) trebuie să fie primul în index. Coloanele ulterioare ar trebui listate pe baza unicității valorilor lor (coloanele cu cel mai mare număr de valori unice vin pe primul loc).
• De asemenea, puteți specifica un index pe coloanele calculate dacă acestea îndeplinesc anumite cerințe. De exemplu, expresiile folosite pentru a obține valoarea unei coloane trebuie să fie deterministe (se returnează întotdeauna același rezultat pentru un set dat de parametri de intrare).

Interogări baze de date

O altă considerație la proiectarea indicilor este ce interogări sunt executate în baza de date. După cum am menționat mai devreme, trebuie să luați în considerare cât de des se schimbă datele. În plus, trebuie utilizate următoarele principii:

• Încercați să inserați sau să modificați cât mai multe rânduri posibil într-o singură interogare, mai degrabă decât să faceți acest lucru în mai multe interogări individuale.
• Creați un index negrupat pe coloanele care sunt utilizate frecvent ca termeni de căutare în interogările dvs. UNDEși conexiuni în ÎNSCRIEȚI-VĂ.
• Luați în considerare indexarea coloanelor utilizate în interogările de căutare a rândurilor pentru potriviri exacte ale valorii.

Și acum, de fapt:

14 întrebări despre indecși în SQL Server pe care v-a fost jenă să le puneți

De ce nu poate un tabel să aibă doi indecși grupați?

Vrei un răspuns scurt? Un index grupat este un tabel. Când creați un index grupat pe un tabel, motorul de stocare sortează toate rândurile din tabel în ordine crescătoare sau descrescătoare, conform definiției indexului. Un index grupat nu este o entitate separată ca alți indecși, ci un mecanism pentru sortarea datelor într-un tabel și facilitarea accesului rapid la rândurile de date.
Să ne imaginăm că aveți un tabel care conține istoricul tranzacțiilor de vânzare. Tabelul de vânzări include informații precum ID-ul comenzii, poziția articolului în comandă, numărul articolului, cantitatea articolului, numărul și data comenzii etc. Creați un index grupat pe coloane ID comandăŞi LineID, sortate în ordine crescătoare, după cum se arată în continuare T-SQL cod:
CREAȚI INDEXUL UNIC CLUSTERED ix_oriderid_lineid PE dbo.Sales(OrderID, LineID);
Când rulați acest script, toate rândurile din tabel vor fi sortate fizic mai întâi după coloana OrderID și apoi după LineID, dar datele în sine vor rămâne într-un singur bloc logic, tabelul. Din acest motiv, nu puteți crea doi indecși grupați. Poate exista un singur tabel cu o singură dată și acel tabel poate fi sortat o singură dată într-o anumită ordine.

Dacă un tabel grupat oferă multe beneficii, atunci de ce să folosiți un heap?

ai dreptate. Tabelele grupate sunt excelente și majoritatea interogărilor dvs. vor avea performanțe mai bune pe tabelele care au un index grupat. Dar, în unele cazuri, este posibil să doriți să lăsați mesele în starea lor naturală, curată, de exemplu. sub forma unui heap și creați numai indecși non-cluster pentru a vă menține interogările în funcțiune.
Heap-ul, după cum vă amintiți, stochează datele în ordine aleatorie. De obicei, subsistemul de stocare adaugă date la un tabel în ordinea în care sunt introduse, dar subsistemul de stocare îi place, de asemenea, să mute rândurile pentru o stocare mai eficientă. Ca urmare, nu aveți nicio șansă să preziceți în ce ordine vor fi stocate datele.
Dacă motorul de interogări trebuie să găsească date fără a beneficia de un index neclustrat, atunci va face scanare completă tabele pentru a găsi rândurile de care are nevoie. Pe mesele foarte mici, aceasta nu este de obicei o problemă, dar pe măsură ce grămada crește în dimensiune, performanța scade rapid. Desigur, un index non-cluster poate ajuta folosind un indicator către fișierul, pagina și rândul în care sunt stocate datele necesare - aceasta este de obicei o alternativă mult mai bună la scanarea tabelului. Chiar și așa, este dificil să compari beneficiile unui index grupat atunci când luăm în considerare performanța interogărilor.
Cu toate acestea, grămada poate ajuta la îmbunătățirea performanței în anumite situații. Luați în considerare un tabel cu multe inserări, dar puține actualizări sau ștergeri. De exemplu, un tabel care stochează un jurnal este folosit în primul rând pentru a insera valori până când este arhivat. Pe heap, nu veți vedea paginarea și fragmentarea datelor așa cum ați face cu un index grupat, deoarece rândurile sunt pur și simplu adăugate la sfârșitul heap-ului. Împărțirea prea mult a paginilor poate avea un impact semnificativ asupra performanței și nu într-un mod bun. În general, heap-ul vă permite să inserați date relativ fără durere și nu va trebui să vă ocupați de costurile de stocare și întreținere pe care le-ați face cu un index cluster.
Dar lipsa actualizării și ștergerii datelor nu ar trebui să fie considerată singurul motiv. Modul în care datele sunt eșantionate este, de asemenea, un factor important. De exemplu, nu ar trebui să utilizați o grămadă dacă interogați frecvent intervale de date sau datele pe care le interogați adesea trebuie să fie sortate sau grupate.
Toate acestea înseamnă că ar trebui să luați în considerare utilizarea heap-ului numai atunci când lucrați cu tabele foarte mici sau toată interacțiunea cu tabelul este limitată la inserarea de date și interogările sunt extrem de simple (și utilizați indecși non-cluster). oricum). În caz contrar, rămâneți cu un index grupat bine conceput, cum ar fi unul definit pe un câmp cheie ascendent simplu, cum ar fi o coloană utilizată pe scară largă cu IDENTITATE.

Cum modific factorul de umplere implicit al indexului?

Modificarea factorului de umplere implicit al indexului este un lucru. Înțelegerea modului în care funcționează raportul implicit este o altă chestiune. Dar mai întâi, fă câțiva pași înapoi. Factorul de umplere a indexului determină cantitatea de spațiu pe pagină pentru a stoca indexul la nivelul de jos (nivelul frunzei) înainte de a începe să umpleți o pagină nouă. De exemplu, dacă coeficientul este setat la 90, atunci când indicele crește, acesta va ocupa 90% din pagină și apoi va trece la pagina următoare.
În mod implicit, valoarea factorului de umplere a indexului este în SQL Server este 0, care este la fel cu 100. Ca urmare, toți indecșii noi moștenesc automat această setare, cu excepția cazului în care specificați în mod specific o valoare în codul dvs. care este diferită de valoarea standard a sistemului sau modificați comportamentul implicit. Puteți folosi SQL Server Management Studio pentru a ajusta valoarea implicită sau pentru a rula o procedură stocată în sistem sp_configure. De exemplu, următorul set T-SQL comenzi setează valoarea coeficientului la 90 (mai întâi trebuie să comutați la modul setări avansate):
EXEC sp_configure „show advanced options”, 1; GO RECONFIGURĂ; GO EXEC sp_configure „factor de umplere”, 90; RECONFIGURAȚI; MERGE
După modificarea valorii factorului de umplere a indexului, trebuie să reporniți serviciul SQL Server. Acum puteți verifica valoarea setată rulând sp_configure fără al doilea argument specificat:
EXEC sp_configure „factor de umplere” GO
Această comandă ar trebui să returneze o valoare de 90. Ca rezultat, toți indecșii nou creați vor folosi această valoare. Puteți testa acest lucru creând un index și interogând valoarea factorului de umplere:
UTILIZAȚI AdventureWorks2012; -- baza de date GO CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
În acest exemplu, am creat un index non-cluster pe un tabel Persoanăîn baza de date AdventureWorks2012. După crearea indexului, putem obține valoarea factorului de umplere din tabelele de sistem sys.indexes. Interogarea ar trebui să returneze 90.
Cu toate acestea, să ne imaginăm că am șters indexul și l-am creat din nou, dar acum am specificat o anumită valoare a factorului de umplere:
CREATE NONCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName) WITH (fillfactor=80); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
De data aceasta am adăugat instrucțiuni CUși opțiunea factor de umplere pentru operația noastră de creare a indexului CREAȚI INDEXși a specificat valoarea 80. Operator SELECTA acum returnează valoarea corespunzătoare.
Până acum totul a fost destul de simplu. Unde vă puteți arde cu adevărat în întregul proces este atunci când creați un index care utilizează o valoare implicită a coeficientului, presupunând că cunoașteți acea valoare. De exemplu, cineva modifică setările serverului și este atât de încăpățânat încât a stabilit factorul de umplere a indexului la 20. Între timp, continuați să creați indici, presupunând că valoarea implicită este 0. Din păcate, nu aveți cum să aflați umplerea factor până când nu creați un index și apoi verificați valoarea așa cum am făcut în exemplele noastre. În caz contrar, va trebui să așteptați momentul în care performanța interogărilor scade atât de mult încât începeți să bănuiți ceva.
O altă problemă de care ar trebui să fii conștient este reconstruirea indicilor. Ca și în cazul creării unui index, puteți specifica valoarea factorului de umplere a indexului atunci când îl reconstruiți. Cu toate acestea, spre deosebire de comanda create index, reconstrucția nu folosește setările implicite ale serverului, în ciuda a ceea ce poate părea. Chiar mai mult, dacă nu specificați în mod specific valoarea factorului de umplere a indicelui, atunci SQL Server va folosi valoarea coeficientului cu care exista acest indice înainte de restructurarea lui. De exemplu, următoarea operație ALTER INDEX reconstruiește indexul pe care tocmai l-am creat:
ALTER INDEX ix_people_lastname ON Person.Person RECONSTRUIRE; GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
Când verificăm valoarea factorului de umplere, vom obține o valoare de 80, deoarece asta am specificat când am creat ultima dată indexul. Valoarea implicită este ignorată.
După cum puteți vedea, modificarea valorii factorului de umplere a indicelui nu este atât de dificilă. Este mult mai dificil să cunoști valoarea actuală și să înțelegi când este aplicată. Dacă specificați întotdeauna coeficientul atunci când creați și reconstruiți indici, atunci știți întotdeauna rezultatul specific. Cu excepția cazului în care trebuie să vă faceți griji pentru a vă asigura că altcineva nu strica setările serverului din nou, determinând ca toți indexurile să fie reconstruite cu un factor de umplere ridicol de scăzut.

Este posibil să creați un index grupat pe o coloană care conține duplicate?

Da si nu. Da, puteți crea un index grupat pe o coloană cheie care conține valori duplicate. Nu, valoarea unei coloane cheie nu poate rămâne într-o stare neunică. Lasă-mă să explic. Dacă creați un index grupat neunic pe o coloană, motorul de stocare adaugă un unificator la valoarea duplicată pentru a asigura unicitatea și, prin urmare, pentru a putea identifica fiecare rând din tabelul grupat.
De exemplu, puteți decide să creați un index grupat pe o coloană care conține date despre clienți Nume păstrând numele de familie. Coloana conține valorile Franklin, Hancock, Washington și Smith. Apoi inserați din nou valorile Adams, Hancock, Smith și Smith. Dar valoarea coloanei cheie trebuie să fie unică, astfel încât motorul de stocare va schimba valoarea duplicatelor astfel încât acestea să arate cam așa: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 și Smith5678.
La prima vedere, această abordare pare în regulă, dar o valoare întreagă mărește dimensiunea cheii, ceea ce poate deveni o problemă dacă există un număr mare de duplicate, iar aceste valori vor deveni baza unui index neclustrat sau a unui index străin. referință cheie. Din aceste motive, ar trebui să încercați întotdeauna să creați indecși grupați unici ori de câte ori este posibil. Dacă acest lucru nu este posibil, atunci cel puțin încercați să utilizați coloane cu un conținut de valoare unică foarte mare.

Cum este stocat tabelul dacă nu a fost creat un index cluster?

SQL Server acceptă două tipuri de tabele: tabele grupate care au un index grupat și tabele heap sau doar heaps. Spre deosebire de tabelele grupate, datele din heap nu sunt sortate în niciun fel. În esență, acesta este o grămadă de date. Dacă adăugați un rând la un astfel de tabel, motorul de stocare îl va adăuga pur și simplu la sfârșitul paginii. Când pagina este plină cu date, aceasta va fi adăugată la o pagină nouă. În cele mai multe cazuri, veți dori să creați un index grupat pe un tabel pentru a profita de capabilitățile de sortare și de interogări mai rapide (încercați să vă imaginați găsirea număr de telefonîntr-o agendă nesortată după niciun principiu). Cu toate acestea, dacă alegeți să nu creați un index cluster, puteți crea în continuare un index nonclustered pe heap. În acest caz, fiecare rând index va avea un pointer către un rând heap. Indexul include ID-ul fișierului, numărul paginii și numărul liniei de date.

Care este relația dintre constrângerile unicității valorii și o cheie primară cu indici de tabel?

O cheie primară și o constrângere unică asigură că valorile dintr-o coloană sunt unice. Puteți crea doar o cheie primară pentru un tabel și nu poate conține valori NUL. Puteți crea mai multe restricții privind unicitatea unei valori pentru un tabel și fiecare dintre ele poate avea o singură intrare cu NUL.
Când creați o cheie primară, motorul de stocare creează, de asemenea, un index cluster unic dacă nu a fost deja creat un index cluster. Cu toate acestea, puteți suprascrie comportamentul implicit și va fi creat un index non-cluster. Dacă există un index grupat atunci când creați cheia primară, va fi creat un index unic nonclustrat.
Când creați o constrângere unică, motorul de stocare creează un index unic, necluster. Cu toate acestea, puteți specifica crearea unui index cluster unic dacă unul nu a fost creat anterior.
În general, o constrângere asupra unei valori unice și un indice unic sunt unul și același.

De ce indexurile grupate și non-cluster sunt numiți B-tree în SQL Server?

Indecșii de bază din SQL Server, în cluster sau necluster, sunt distribuiți în seturi de pagini numite noduri de index. Aceste pagini sunt organizate într-o ierarhie specifică cu o structură arborescentă numită arbore echilibrat. La nivelul superior se află nodul rădăcină, în partea de jos sunt nodurile frunzelor, cu noduri intermediare între nivelurile de sus și de jos, așa cum se arată în figură:


Nodul rădăcină oferă punctul de intrare principal pentru interogările care încearcă să recupereze date prin index. Pornind de la acest nod, motorul de interogare inițiază o navigare în jos în structura ierarhică până la nodul frunză corespunzător care conține datele.
De exemplu, imaginați-vă că a fost primită o solicitare de selectare a rândurilor care conțin o valoare cheie de 82. Subsistemul de interogare începe să funcționeze de la nodul rădăcină, care se referă la un nod intermediar potrivit, în cazul nostru 1-100. De la nodul intermediar 1-100 există o tranziție la nodul 51-100, iar de acolo la nodul final 76-100. Dacă acesta este un index grupat, atunci frunza nodului conține datele rândului asociat cheii egale cu 82. Dacă acesta este un index negrupat, atunci frunza index conține un pointer către tabelul grupat sau un anumit rând din grămada.

Cum poate un index chiar să îmbunătățească performanța interogării dacă trebuie să traversați toate aceste noduri de index?

În primul rând, indicii nu îmbunătățesc întotdeauna performanța. Prea mulți indecși creați incorect transformă sistemul într-un mocir și degradează performanța interogărilor. Este mai corect să spunem că, dacă indicii sunt aplicați cu atenție, aceștia pot oferi câștiguri semnificative de performanță.
Gândiți-vă la o carte uriașă dedicată reglajului performanței SQL Server(versiune pe hârtie, nu versiune electronică). Imaginați-vă că doriți să găsiți informații despre configurarea Resource Governor. Puteți trage cu degetul pagină cu pagină prin întreaga carte, sau deschideți cuprinsul și aflați numărul exact al paginii cu informațiile pe care le căutați (cu condiția ca cartea să fie indexată corect și conținutul să aibă indecșii corecti). Acest lucru cu siguranță vă va economisi timp semnificativ, chiar dacă mai întâi trebuie să accesați o structură complet diferită (indexul) pentru a obține informațiile de care aveți nevoie din structura primară (carte).
Ca un index de carte, un index în SQL Server vă permite să executați interogări precise asupra datelor de care aveți nevoie în loc să scanați complet toate datele conținute într-un tabel. Pentru tabelele mici, o scanare completă nu este de obicei o problemă, dar tabelele mari ocupă multe pagini de date, ceea ce poate duce la un timp semnificativ de execuție a interogării, cu excepția cazului în care există un index pentru a permite motorului de interogare să obțină imediat locația corectă a datelor. Imaginați-vă că vă pierdeți la o intersecție rutieră cu mai multe niveluri în fața unei metropole importante fără o hartă și veți înțelege ideea.

Dacă indexurile sunt atât de grozave, de ce să nu creați unul pe fiecare coloană?

Nicio faptă bună nu trebuie să rămână nepedepsită. Cel puțin așa este în cazul indicilor. Desigur, indecșii funcționează excelent atâta timp cât rulați interogări de preluare a operatorului SELECTA, dar de îndată ce încep apelurile frecvente către operatori INTRODUCE, UPDATEŞi ŞTERGE, așa că peisajul se schimbă foarte repede.
Când inițiați o solicitare de date de către operator SELECTA, motorul de interogare găsește indexul, se deplasează prin structura sa arborescentă și descoperă datele pe care le caută. Ce poate fi mai simplu? Dar lucrurile se schimbă dacă inițiezi o declarație de modificare cum ar fi UPDATE. Da, pentru prima parte a declarației, motorul de interogare poate folosi din nou indexul pentru a localiza rândul care este modificat - aceasta este o veste bună. Și dacă există o simplă modificare a datelor într-un rând care nu afectează modificările în coloanele cheie, atunci procesul de modificare va fi complet nedureros. Dar dacă modificarea face ca paginile care conțin datele să fie împărțite sau valoarea unei coloane cheie este schimbată, ceea ce face ca aceasta să fie mutată într-un alt nod de index - acest lucru va avea ca rezultat ca indexul să aibă nevoie de o reorganizare care afectează toți indecșii și operațiunile asociate. , ducând la o scădere pe scară largă a productivității.
Procese similare apar la apelarea unui operator ŞTERGE. Un index poate ajuta la localizarea datelor care sunt șterse, dar ștergerea datelor în sine poate duce la remanierea paginii. Referitor la operator INTRODUCE, principalul inamic al tuturor indicilor: începi să adaugi o cantitate mare de date, ceea ce duce la modificări ale indicilor și reorganizarea acestora și toată lumea are de suferit.
Deci, luați în considerare tipurile de interogări către baza de date atunci când vă gândiți ce tip de indici și câți să creați. Mai mult nu înseamnă mai bine. Înainte de a adăuga un nou index la un tabel, luați în considerare costul nu numai al interogărilor subiacente, ci și al cantității de spațiu pe disc consumat, costul menținerii funcționalității și indicilor, care poate duce la un efect de domino asupra altor operațiuni. Strategia dumneavoastră de proiectare a indexului este unul dintre cele mai importante aspecte ale implementării dumneavoastră și ar trebui să includă multe considerații, de la dimensiunea indexului, numărul de valori unice până la tipul de interogări pe care indexul le va accepta.

Este necesar să se creeze un index grupat pe o coloană cu o cheie primară?

Puteți crea un index grupat pe orice coloană care îndeplinește condițiile necesare. Este adevărat că un index grupat și o constrângere a cheii primare sunt făcute unul pentru celălalt și sunt o potrivire făcută în rai, așa că înțelegeți faptul că atunci când creați o cheie primară, atunci un index grupat va fi creat automat dacă nu a fost unul. creat înainte. Cu toate acestea, puteți decide că un index grupat ar avea rezultate mai bune în altă parte și adesea decizia dvs. va fi justificată.
Scopul principal al unui index grupat este de a sorta toate rândurile din tabel pe baza coloanei cheie specificate la definirea indexului. Acest lucru oferă căutare rapidă și acces ușor la datele din tabel.
Cheia primară a unui tabel poate fi o alegere bună, deoarece identifică în mod unic fiecare rând din tabele, fără a fi nevoie să adăugați date suplimentare. În unele cazuri, cea mai bună alegere va fi o cheie primară surogat, care nu este doar unică, ci și de dimensiuni reduse și ale cărei valori cresc secvențial, făcând indici nonclustered bazați pe această valoare mai eficienți. Optimizatorul de interogări îi place, de asemenea, această combinație de index grupat și cheie primară, deoarece unirea tabelelor este mai rapidă decât alăturarea într-un alt mod care nu utilizează o cheie primară și indexul grupat asociat. După cum am spus, este un meci făcut în rai.
În cele din urmă, totuși, merită remarcat faptul că atunci când se creează un index grupat, există mai multe aspecte de luat în considerare: câți indici non-clusteri se vor baza pe acesta, cât de des se va schimba valoarea coloanei indexului cheie și cât de mare. Când valorile din coloanele unui index grupat se modifică sau indexul nu funcționează conform așteptărilor, atunci toți ceilalți indici de pe tabel pot fi afectați. Un index grupat ar trebui să se bazeze pe cea mai persistentă coloană ale cărei valori cresc într-o anumită ordine, dar nu se modifică în mod aleatoriu. Indexul trebuie să accepte interogări față de datele cel mai frecvent accesate ale tabelului, astfel încât interogările profită din plin de faptul că datele sunt sortate și accesibile la nodurile rădăcină, frunzele indexului. Dacă cheia primară se potrivește acestui scenariu, atunci utilizați-o. Dacă nu, atunci alegeți un alt set de coloane.

Ce se întâmplă dacă indexați o vizualizare, este încă o vizualizare?

Prezentarea este masă virtuală, care generează date din unul sau mai multe tabele. În esență, este o interogare numită care preia date din tabelele subiacente atunci când interogați acea vizualizare. Puteți îmbunătăți performanța interogărilor prin crearea unui index grupat și a indecșilor nonclustered în această vizualizare, similar modului în care creați indecși într-un tabel, dar principalul avertisment este că mai întâi creați un index grupat și apoi puteți crea unul nonclustered.
Când este creată o vedere indexată (vizualizare materializată), atunci definiția vederii în sine rămâne o entitate separată. Acesta este, până la urmă, doar un operator codificat SELECTA, stocate în baza de date. Dar indexul este o cu totul altă poveste. Când creați un index în cluster sau necluster pe un furnizor, datele sunt salvate fizic pe disc, la fel ca un index obișnuit. În plus, atunci când datele se modifică în tabelele de bază, indexul vizualizării se schimbă automat (aceasta înseamnă că este posibil să doriți să evitați indexarea vizualizărilor pe tabelele care se modifică frecvent). În orice caz, vizualizarea rămâne o vedere - o privire asupra tabelelor, dar executată cu precizie în în acest moment, cu indici corespunzători acestuia.
Înainte de a putea crea un index pe o vizualizare, acesta trebuie să îndeplinească mai multe constrângeri. De exemplu, o vizualizare poate face referire numai la tabele de bază, dar nu și la alte vederi, iar acele tabele trebuie să fie în aceeași bază de date. Există de fapt multe alte restricții, așa că asigurați-vă că verificați documentația SQL Server pentru toate detaliile murdare.

De ce să folosiți un indice de acoperire în loc de un index compus?

În primul rând, să ne asigurăm că înțelegem diferența dintre cele două. Un index compus este pur și simplu un index obișnuit care conține mai mult de o coloană. Pot fi folosite mai multe coloane cu cheie pentru a vă asigura că fiecare rând dintr-un tabel este unic sau este posibil să aveți mai multe coloane pentru a vă asigura că cheia primară este unică sau este posibil să încercați să optimizați execuția interogărilor executate frecvent pe mai multe coloane. În general, totuși, cu cât un index conține mai multe coloane cheie, cu atât indexul va fi mai puțin eficient, ceea ce înseamnă că indecșii compoziți trebuie utilizați în mod judicios.
După cum sa menționat, o interogare poate beneficia foarte mult dacă toate datele necesare sunt imediat localizate pe frunzele indexului, la fel ca indexul însuși. Aceasta nu este o problemă pentru un index cluster deoarece toate datele sunt deja acolo (de aceea este atât de important să vă gândiți cu atenție când creați un index grupat). Dar un index negrupat pe frunze conține doar coloane cheie. Pentru a accesa toate celelalte date, optimizatorul de interogări necesită pași suplimentari, care pot adăuga o suprasarcină semnificativă la executarea interogărilor.
Aici indicele de acoperire vine în ajutor. Când definiți un index neclustrat, puteți specifica coloane suplimentare pentru coloanele cheie. De exemplu, să presupunem că aplicația dvs. interogează frecvent datele coloanei ID comandăŞi Data comandeiîn tabel Vânzări:
SELECT ID comandă, Data comandă FROM Vânzări WHERE ID comandă = 12345;
Puteți crea un index compus non-cluster pe ambele coloane, dar coloana OrderDate va adăuga doar costul general de întreținere a indexului, fără a servi ca o coloană cheie deosebit de utilă. Cea mai bună soluție ar fi să creați un index de acoperire pe coloana cheie ID comandăși coloană inclusă în plus Data comandei:
CREATE NONCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate);
Acest lucru evită dezavantajele indexării coloanelor redundante, menținând în același timp beneficiile stocării datelor în frunze atunci când rulează interogări. Coloana inclusă nu face parte din cheie, dar datele sunt stocate exact pe nodul frunză, frunza index. Acest lucru poate îmbunătăți performanța interogărilor fără nicio suprasolicitare suplimentară. În plus, coloanele incluse în indexul de acoperire sunt supuse la mai puține restricții decât coloanele cheie din index.

Numărul de duplicate dintr-o coloană cheie contează?

Când creați un index, trebuie să încercați să reduceți numărul de duplicate din coloanele cheie. Sau mai precis: incearca sa mentii rata de repetare cat mai scazuta.
Dacă lucrați cu un index compus, atunci duplicarea se aplică tuturor coloanelor cheie în ansamblu. O singură coloană poate conține multe valori duplicate, dar ar trebui să existe o repetiție minimă între toate coloanele index. De exemplu, creați un index compus nonclustered pe coloane PrenumeŞi Nume, puteți avea un set de valori egal cu John și un set de Doe, dar doriți să aveți cât mai multe mai putine valori John Doe, sau mai bine doar un sens al lui John Doe.
Raportul de unicitate al valorilor unei coloane cheie se numește selectivitate index. Cu cât sunt mai multe valori unice, cu atât este mai mare selectivitatea: un indice unic are cea mai mare selectivitate posibilă. Motorului de interogări îi plac foarte mult coloanele cu valori de selectivitate ridicate, mai ales dacă acele coloane sunt incluse în clauzele WHERE ale interogărilor executate cel mai frecvent. Cu cât indexul este mai selectiv, cu atât mai rapid motorul de interogare poate reduce dimensiunea setului de date rezultat. Dezavantajul, desigur, este că coloanele cu relativ puține valori unice vor fi rareori candidați buni pentru indexare.

Este posibil să se creeze un index non-cluster doar pe un anumit subset al datelor unei coloane cheie?

În mod implicit, un index nonclustered conține câte un rând pentru fiecare rând din tabel. Desigur, puteți spune același lucru despre un index grupat, presupunând că un astfel de index este un tabel. Dar când vine vorba de un index non-clustered, relația unu-la-unu este un concept important deoarece, începând cu versiunea SQL Server 2008, aveți opțiunea de a crea un index filtrabil care limitează rândurile incluse în acesta. Un index filtrat poate îmbunătăți performanța interogării deoarece... are dimensiuni mai mici și conține statistici filtrate, mai precise decât toate cele tabelare - acest lucru duce la crearea unor planuri de execuție îmbunătățite. Un index filtrat necesită, de asemenea, mai puțin spațiu de stocare și costuri de întreținere mai mici. Indexul este actualizat numai atunci când datele care corespund filtrului se modifică.
În plus, un index filtrabil este ușor de creat. În operator CREAȚI INDEX trebuie doar să indicați în UNDE starea filtrului. De exemplu, puteți filtra toate rândurile care conțin NULL din index, așa cum se arată în cod:
CREATE NONCLUSTERED INDEX ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) WHERE CarrierTrackingNumber NU ESTE NULL;
Putem, de fapt, să filtram orice date care nu sunt importante în interogările critice. Dar ai grijă, pentru că... SQL Server impune mai multe restricții asupra indecșilor filtrabili, cum ar fi incapacitatea de a crea un index filtrabil pe o vizualizare, deci citiți cu atenție documentația.
De asemenea, se poate obține rezultate similare prin crearea unei vizualizări indexate. Cu toate acestea, un index filtrat are mai multe avantaje, cum ar fi capacitatea de a reduce costurile de întreținere și de a îmbunătăți calitatea planurilor dumneavoastră de execuție. Indexurile filtrate pot fi reconstruite și online. Încercați acest lucru cu o vizualizare indexată.

Și din nou puțin de la traducător

Scopul apariției acestei traduceri pe paginile lui Habrahabr a fost de a vă spune sau de a vă aminti despre blogul SimpleTalk de la RedGate.
Publică multe postări distractive și interesante.
Nu sunt afiliat cu niciun produs al companiei RedGate, nici cu vânzarea lor.

După cum am promis, cărți pentru cei care vor să afle mai multe
Recomand trei cărți foarte bune de la mine (linkurile duc la aprinde versiuni în magazin Amazon):

În principiu, puteți deschide indexuri simple Adăugați etichete

Noțiuni fundamentale pentru Microsoft SQL Server 2012 T-SQL (referință pentru dezvoltatori) Autorul Itzik Ben-Gan Data publicării: 15 iulie 2012 Autorul, un maestru al meșteșugului său, oferă cunoștințe de bază despre lucrul cu bazele de date. Dacă ai uitat totul sau nu ai știut niciodată, cu siguranță merită citit.

--Un index este o structură pe disc care este asociată cu un tabel sau vizualizare și accelerează preluarea rândurilor din tabel sau vizualizare. Un index conține chei construite dintr-una sau mai multe coloane dintr-un tabel sau vizualizare. Aceste chei sunt stocate într-o structură arborescentă echilibrată care acceptă căutarea rapidă a rândurilor după valorile lor cheie în SQL Server.

--Indecurile grupate sortează și stochează rânduri de date în tabele sau vizualizări pe baza valorilor cheie ale acestora. Aceste valori sunt coloanele incluse în definiția indexului. Există un singur index grupat per tabel, deoarece rândurile de date pot fi sortate doar într-o singură ordine.
--Rândurile de date dintr-un tabel sunt stocate în ordine de sortare numai dacă tabelul conține un index grupat. Dacă un tabel are un index grupat, atunci tabelul se numește clustered. Dacă un tabel nu are un index grupat, rândurile de date sunt stocate într-o structură neordonată numită heap.

--Un index negrupat are exact aceeași structură ca un index grupat, dar cu două diferențe importante:
--un index nonclustered nu modifică ordinea fizică a rândurilor din tabel, iar paginile frunze dintr-un index nonclustered constau din chei de index și marcaje.

--Indecșii grupați oferă o recuperare mai rapidă a datelor decât indecșii neclustrat. De obicei, se dovedesc a fi mai rapidi și la actualizări, dar nu și atunci când au loc multe actualizări în același loc în mijlocul relației.

--Din anumite motive, un index cluster tinde să ruleze mai repede decât un index nonclustered. Când sistemul scanează un index grupat, nu este nevoie să părăsiți structura B-tree pentru a scana paginile de date, deoarece astfel de pagini sunt deja prezente la nivelul frunzei arborelui.

--Un index nonclustered necesită, de asemenea, mai multe operațiuni I/O decât indexul clustered corespunzător.

--Indexul nonclustered trebuie să citească paginile de date după scanarea arborelui B sau, dacă există un index clusterizat pe o altă coloană(e) a tabelului, indexul nonclustered trebuie să citească structura arborelui B a indexului clusterizat .

--Deci, un index grupat va fi semnificativ mai rapid decât o scanare de tabel, chiar dacă selectivitatea sa este destul de slabă (interogarea returnează o mulțime de rânduri)

CREATE TABLE tsql.dbo.NI
ID int NU NULL,
T char(8) NULL
);

CREATE TABLE tsql.dbo.NCI
ID int NU NULL,
T char(8) NULL
);

--Creați un index grupat

CREAȚI INDEXUL CLUSTERED IX_1
ON tsql.dbo.NCI(ID);

--Creați un index nonclustered pe un tabel

CREAȚI INDEXUL NONCLUSTERED IX_2
ON tsql.dbo.NCI(T);

--Adăugați date de testare
DECLARE @i INT = 100000;
DECLARE @t CHAR(1) = "T";

ÎN CAZUL @i > 0
ÎNCEPE
inserați în valorile tsql.dbo.NI(@i, @t + CAST(@i AS char(6)));
inserați în valorile tsql.dbo.NCI(@i, @t + CAST(@i AS char(6)));
SET @i -= 1;
Sfârşit

--Interogări pe un tabel cu indecși
SELECT ID, T DIN tsql.dbo.NCI
COMANDA PE ID, T

SELECT ID, COUNT(*) CA C DIN tsql.dbo.NCI
GRUPĂ DUPĂ ID, T

SELECT ID, T DIN tsql.dbo.NCI
UNDE ID > 4000 ȘI ID< 55000 AND T LIKE "T%"

--Interogare folosind ambii indecși
UTILIZAȚI tsql;
SELECTARE CAST(dbo.NCI.ID AS VARCHAR)
DE LA dbo.NCI
GRUP DE DBO.NCI.ID
UNIREA TOȚI
SELECT dbo.NCI.T
DE LA dbo.NCI
GRUP DE DBO.NCI.T

--Informații despre indici
SELECT index_type_desc, index_depth, index_level,
page_count, record_count
DE LA sys.dm_db_index_physical_stats
(DB_ID(N"tsql"), OBJECT_ID(N"dbo.NCI"), NULL, NULL , "DETALIAT");

--Ștergerea indecșilor
DACĂ EXISTĂ (SELECTAȚI numele DIN sys.indexes
WHERE nume = N"IX_1")
DROP INDEX IX_1 PE tsql.dbo.NCI;

DACĂ EXISTĂ (SELECTAȚI numele DIN sys.indexes
WHERE nume = N"IX_2")
DROP INDEX IX_2 PE tsql.dbo.NCI;