En ndeks, dit speciale opzoektabellen die een databasezoekmachine kan gebruiken om het zoeken naar gegevens te versnellen. In eenvoudige bewoordingen is een index een verwijzing naar gegevens in een tabel. Een index in een database lijkt erg op een index aan het einde van een boek.

Als je bijvoorbeeld wilt linken naar alle pagina's in een boek over een bepaald onderwerp, kijk dan eerst in de index, waarin alle onderwerpen alfabetisch zijn gerangschikt, en ga dan door naar een of meer specifieke paginanummers.

De index helpt bij het versnellen van vragen en aanbiedingen, maar vertraagt ​​het invoeren van gegevens, met verklaringen UPDATE en INSERT... Indexen kunnen worden gemaakt of verwijderd zonder de gegevens te beïnvloeden.

Het maken van een index omvat een verklaring MAAK INDEX, waarmee u de index een naam kunt geven om de tabel aan te geven en welke kolom of kolommen u wilt indexeren, en om aan te geven of de index oplopend of aflopend is.

Indexen kunnen ook uniek zijn, met een beperking UNIEK, zodat de index dubbele vermeldingen in een kolom of combinatie van kolommen met een index voorkomt.

CREATE INDEX Commando

Basissyntaxis MAAK INDEX als volgt:

CREATE INDEX index_name ON table_name;

Indexen met één kolom

Er wordt een index met één kolom gemaakt op basis van slechts één tabelkolom. De basissyntaxis is als volgt.

CREATE INDEX index_name ON table_name (kolomnaam);

Unieke indexen

Unieke indexen worden niet alleen voor het werk gebruikt, maar ook om de gegevensintegriteit te waarborgen. Door de unieke index kunnen geen dubbele waarden in de tabel worden ingevoegd. De basissyntaxis is als volgt.

MAAK UNIEKE INDEX indexnaam op tabelnaam (kolomnaam);

Samengestelde indexen

Een samengestelde index is een index op twee of meer kolommen van een tabel. De basissyntaxis is als volgt.

CREATE INDEX index_name op table_name (kolom1, kolom2);

Ongeacht of u een index met één kolom of een samengestelde index wilt maken, houd rekening met de kolom(men) die u heel vaak kunt gebruiken als filtervoorwaarde in een WHERE-component.

Als er slechts één kolom in gebruik is, moet de index voor één kolom worden geselecteerd. Als er twee of meer kolommen zijn die vaak worden gebruikt als filters in de WHERE-component, is een samengestelde index de beste keuze.

Impliciete indexen

Impliciete indexen zijn indexen die automatisch worden gemaakt op de databaseserver wanneer een object wordt gemaakt. Indexen worden automatisch gegenereerd voor de primaire sleutel en de unieke beperking.

DROP INDEX-opdracht

De index kan worden verwijderd met het SQL-commando LATEN VALLEN... Wees voorzichtig bij het laten vallen van een index, aangezien de prestaties kunnen vertragen of verbeteren.

De basissyntaxis is als volgt:

DROP INDEX indexnaam;

U kunt het INDEX-beperkingsvoorbeeld bekijken om enkele praktijkvoorbeelden van indices te zien.

Wanneer moet u indexen vermijden?

Hoewel indexen zijn ontworpen om de databaseprestaties te verbeteren, zijn er momenten waarop ze moeten worden vermeden.

De volgende instructies geven aan wanneer het gebruik van de index moet worden herzien.

• Indexen mogen niet worden gebruikt op kleine tabellen.
• Tabellen die regelmatig grote updates of invoegingen hebben.
• Indexen mogen niet worden gebruikt voor kolommen met een groot aantal null-waarden.
• Kolommen die vaak worden gemanipuleerd mogen niet worden geïndexeerd.

Een van de belangrijkste manieren om een ​​hoge productiviteit te bereiken SQL-server is het gebruik van indices. Een index versnelt het queryproces door snelle toegang te bieden tot rijen met gegevens in een tabel, net zoals een index in een boek u helpt snel de informatie te vinden die u nodig hebt. In dit artikel zal ik een snel overzicht geven van indexen in SQL-server en leg uit hoe ze in de database zijn georganiseerd en hoe ze databasequery's helpen versnellen.

Indexen worden gemaakt op kolommen in tabellen en weergaven. Indexen bieden een manier om snel gegevens te vinden op basis van de waarden in deze kolommen. Als u bijvoorbeeld een index op een primaire sleutel maakt en vervolgens naar een rij met gegevens zoekt met behulp van de primaire sleutelwaarden, dan: SQL-server vindt eerst de indexwaarde en gebruikt vervolgens de index om snel de volledige gegevensrij te vinden. Zonder index wordt een volledige scan (scan) van alle rijen in de tabel uitgevoerd, wat een aanzienlijke prestatie-impact kan hebben.
U kunt een index maken voor de meeste kolommen van een tabel of weergave. De uitzondering zijn voornamelijk kolommen met gegevenstypen voor het opslaan van grote objecten ( LOB), zoals afbeelding, tekst of varchar (max)... U kunt ook indexen maken op kolommen die zijn ontworpen om gegevens in de indeling op te slaan XML, maar deze indexen zijn iets anders gerangschikt dan de standaardindexen en hun beschouwing valt buiten het bestek van dit artikel. Het artikel dekt ook niet columnstore indices. In plaats daarvan concentreer ik me op de indexen die het meest worden gebruikt in databases. SQL-server.
Een index bestaat uit een set pagina's, indexknooppunten, die zijn georganiseerd in een boomstructuur - evenwichtige boom... Deze structuur is hiërarchisch van aard en begint met het hoofdknooppunt bovenaan de hiërarchie en bladknooppunten onderaan, zoals weergegeven in de afbeelding:


Wanneer u een query uitvoert op een geïndexeerde kolom, begint de query-engine vanaf de bovenkant van het hoofdknooppunt en gaat geleidelijk naar beneden door de tussenliggende knooppunten, waarbij elke laag van de middelste laag meer gedetailleerde informatie over de gegevens bevat. De query-engine blijft de indexknooppunten doorlopen totdat deze het laagste niveau met indexbladeren bereikt. Als u bijvoorbeeld de waarde 123 zoekt in een geïndexeerde kolom, bepaalt de query-engine eerst de pagina op het eerste tussenliggende niveau op hoofdniveau. In dit geval wijst de eerste pagina naar een waarde van 1 tot 100, en de tweede van 101 tot 200, dus de query-engine verwijst naar de tweede pagina van dit tussenliggende niveau. Verder zal blijken dat u de derde pagina van het volgende tussenliggende niveau moet raadplegen. Vanaf hier leest de query-engine de waarde van de index zelf op een lager niveau. Indexbladen kunnen zowel de tabelgegevens zelf bevatten als gewoon een verwijzing naar de gegevensrijen in de tabel, afhankelijk van het type index: geclusterde index of niet-geclusterde index.

Geclusterde index

De geclusterde index slaat de feitelijke rijen met gegevens op in de bladeren van de index. Terugkerend naar het vorige voorbeeld, betekent dit dat de rij met gegevens die is gekoppeld aan de sleutelwaarde van 123, wordt opgeslagen in de index zelf. Een belangrijk kenmerk van een geclusterde index is dat alle waarden in een bepaalde volgorde worden gesorteerd, oplopend of aflopend. Een tabel of weergave kan dus slechts één geclusterde index hebben. Bovendien moet worden opgemerkt dat de gegevens in een tabel alleen in gesorteerde vorm worden opgeslagen als op deze tabel een geclusterde index is gemaakt.
Een tabel die geen geclusterde index heeft, wordt een heap genoemd.

Niet-geclusterde index

In tegenstelling tot een geclusterde index, bevatten de bladeren van een niet-geclusterde index alleen die kolommen ( toets), waarmee deze index wordt bepaald, en bevat ook een verwijzing naar de rijen met echte gegevens in de tabel. Dit betekent dat het subquerysysteem extra handelingen nodig heeft om de vereiste gegevens te lokaliseren en op te halen. De inhoud van een verwijzing naar gegevens hangt af van of de gegevens zijn opgeslagen op een geclusterde tabel of op een heap. Als de aanwijzer verwijst naar een geclusterde tabel, leidt deze naar een geclusterde index die kan worden gebruikt om de echte gegevens te vinden. Als de aanwijzer naar een heap verwijst, leidt deze naar een specifieke identifier van de gegevensrij. Niet-geclusterde indexen kunnen niet worden gesorteerd als geclusterde indexen, maar u kunt meer dan één niet-geclusterde index voor een tabel of weergave maken, maximaal 999. Dit betekent niet dat u zoveel mogelijk indexen moet maken. Indexen kunnen de systeemprestaties zowel verbeteren als verslechteren. U kunt niet alleen meerdere niet-geclusterde indexen maken, maar u kunt ook extra kolommen opnemen ( opgenomen kolom) naar zijn index: de bladeren van de index zullen niet alleen de waarde van de geïndexeerde kolommen zelf opslaan, maar ook de waarden van deze niet-geïndexeerde extra kolommen. Met deze aanpak kunt u enkele van de beperkingen van de index omzeilen. U kunt bijvoorbeeld een niet-indexeerbare kolom opnemen of de limiet voor de lengte van de index (in de meeste gevallen 900 bytes) omzeilen.

Indextypen

Naast het feit dat een index geclusterd of niet-geclusterd kan zijn, is het mogelijk om deze optioneel te configureren als een samengestelde index, een unieke index of een dekkende index.

Samengestelde index

Zo'n index kan meer dan één kolom bevatten. U kunt maximaal 16 kolommen in de index opnemen, maar hun totale lengte is beperkt tot 900 bytes. Zowel geclusterde als niet-geclusterde indexen kunnen samengesteld zijn.

Unieke index

Deze index zorgt ervoor dat elke waarde in de geïndexeerde kolom uniek is. Als de index samengesteld is, geldt uniciteit voor alle kolommen in de index, maar niet voor elke afzonderlijke kolom. Als u bijvoorbeeld een unieke index voor kolommen maakt NAAM en ACHTERNAAM, dan moet de volledige naam uniek zijn, maar duplicaten in de voor- of achternaam zijn afzonderlijk mogelijk.
Er wordt automatisch een unieke index gemaakt wanneer u kolombeperkingen definieert: primaire sleutel of uniekheidsbeperking voor waarden:

• Hoofdsleutel
  Wanneer u een primaire sleutelbeperking definieert voor een of meer kolommen, dan SQL-server maakt automatisch een unieke geclusterde index aan als de geclusterde index nog niet eerder is gemaakt (in dit geval wordt een unieke niet-geclusterde index gemaakt op de primaire sleutel)
• Uniciteit van waarden
  Wanneer u een beperking definieert voor de uniciteit van waarden, dan SQL-server creëert automatisch een unieke niet-geclusterde index. U kunt opgeven dat er een unieke geclusterde index wordt gemaakt als er nog geen geclusterde index op de tabel is gemaakt

Dekkingsindex

Met een dergelijke index kan een specifieke zoekopdracht onmiddellijk alle benodigde gegevens uit de bladeren van de index halen zonder extra oproepen naar de records van de tabel zelf.

Indexen ontwerpen

Hoe nuttig indexen ook kunnen zijn, ze moeten zorgvuldig worden ontworpen. Aangezien indexen veel schijfruimte in beslag kunnen nemen, wilt u niet meer indexen maken dan nodig is. Bovendien worden indexen automatisch bijgewerkt wanneer de gegevensrij zelf wordt bijgewerkt, wat kan leiden tot extra resource-overhead en prestatievermindering. Er zijn verschillende overwegingen voor databases en query's waarmee u rekening moet houden bij het ontwerpen van indexen.

Database

Zoals eerder opgemerkt, kunnen indexen de systeemprestaties verbeteren omdat ze: ze bieden de query-engine een snel pad om gegevens te vinden. U moet echter ook overwegen hoe vaak u van plan bent gegevens in te voegen, bij te werken of te verwijderen. Wanneer u de gegevens wijzigt, moeten de indexen ook worden gewijzigd om de juiste acties op de gegevens weer te geven, wat de systeemprestaties aanzienlijk kan verslechteren. Houd rekening met de volgende richtlijnen bij het plannen van uw indexeringsstrategie:

• Gebruik voor tabellen die regelmatig worden bijgewerkt, zo min mogelijk indexen.
• Als de tabel een grote hoeveelheid gegevens bevat, maar de wijzigingen zijn klein, gebruik dan zoveel indexen als nodig is om de prestaties van uw zoekopdrachten te verbeteren. Denk echter goed na voordat u indexen op kleine tabellen gebruikt. misschien kan het gebruik van een indexzoekopdracht langer duren dan alleen het scannen van alle rijen.
• Probeer voor geclusterde indexen de velden zo kort mogelijk te houden. Het is het beste om een ​​geclusterde index te gebruiken op kolommen die unieke waarden hebben en geen NULL's toestaan. Dit is de reden waarom de primaire sleutel vaak wordt gebruikt als een geclusterde index.
• De uniciteit van de waarden in de kolom is van invloed op de prestaties van de index. Over het algemeen geldt dat hoe meer duplicaten u in een kolom heeft, hoe slechter de index zal presteren. Aan de andere kant, hoe meer unieke waarden er zijn, hoe beter de gezondheid van de index. Gebruik waar mogelijk een unieke index.
• Houd bij een samengestelde index rekening met de volgorde van de kolommen in de index. Kolommen die in uitdrukkingen worden gebruikt WAAR(bijvoorbeeld, WHERE Voornaam = "Charlie") moet eerst in de index staan. Volgende kolommen moeten worden vermeld op basis van hun uniciteit (kolommen met het hoogste aantal unieke waarden komen eerst).
• U kunt ook een index opgeven voor berekende kolommen als ze aan bepaalde vereisten voldoen. De uitdrukking die wordt gebruikt om de waarde van een kolom op te halen, moet bijvoorbeeld deterministisch zijn (retourneert altijd hetzelfde resultaat voor een bepaalde set invoerparameters).

Databasequery's

Een andere overweging waarmee u rekening moet houden bij het ontwerpen van indexen, is welke query's er worden uitgevoerd op de database. Zoals eerder vermeld, moet u overwegen hoe vaak de gegevens veranderen. Bovendien moeten de volgende principes worden gebruikt:

• Probeer zoveel mogelijk rijen in één query in te voegen of te wijzigen, in plaats van dit in meerdere afzonderlijke query's te doen.
• Maak een niet-geclusterde index op kolommen die vaak worden gebruikt in uw zoekopdrachten als zoektermen in WAAR en verbindingen in MEEDOEN.
• Overweeg het indexeren van kolommen die worden gebruikt in zoekopdrachten met tekenreeksen voor exacte overeenkomstwaarden.

En nu eigenlijk:

14 vragen die je te verlegen was om te stellen over indexen in SQL Server

Waarom kan een tabel geen twee geclusterde indexen hebben?

Wil je een kort antwoord? Een geclusterde index is een tabel. Wanneer u een geclusterde index voor een tabel maakt, sorteert de opslagengine alle rijen in de tabel in oplopende of aflopende volgorde volgens de definitie van de index. Een geclusterde index is geen aparte entiteit zoals andere indexen, maar een mechanisme voor het sorteren van gegevens in een tabel en het vergemakkelijken van snelle toegang tot rijen met gegevens.
Stel dat u een tabel hebt met de geschiedenis van verkooptransacties. De tabel Verkoop bevat informatie zoals order-ID, artikelpositie in de order, artikelnummer, artikelhoeveelheid, ordernummer en datum, enzovoort. U maakt een geclusterde index op kolommen Order ID en Lijn-ID, gesorteerd in oplopende volgorde zoals hieronder weergegeven: T-SQL code:
MAAK UNIEKE GECLUSTERDE INDEX ix_oriderid_lineid OP dbo.Sales (OrderID, LineID);
Wanneer u dit script uitvoert, worden alle rijen in de tabel eerst fysiek gesorteerd op de OrderID-kolom en vervolgens op de LineID, maar de gegevens zelf blijven in een enkel logisch blok, in de tabel. Om deze reden kunt u geen twee geclusterde indexen maken. Er kan slechts één tabel met één gegevens zijn en deze tabel kan slechts één keer in een specifieke volgorde worden gesorteerd.

Als een geclusterde tafel veel voordelen biedt, waarom dan een hoop gebruiken?

Je hebt gelijk. Geclusterde tabellen zijn geweldig en de meeste van uw zoekopdrachten zullen beter presteren op tabellen met een geclusterde index. Maar in sommige gevallen wilt u de tafels misschien in hun natuurlijke, onberispelijke staat laten, d.w.z. als een hoop, en maak alleen niet-geclusterde indexen om uw zoekopdrachten gezond te houden.
De heap slaat, zoals u zich herinnert, gegevens op in een willekeurige volgorde. Meestal voegt het opslagsubsysteem gegevens toe aan de tabel in de volgorde waarin deze zijn ingevoegd, maar het subsysteem verplaatst ook graag rijen voor efficiëntere opslag. Hierdoor heeft u geen enkele kans om te voorspellen in welke volgorde de gegevens worden opgeslagen.
Als de query-engine gegevens moet vinden zonder de voordelen van een niet-geclusterde index, zal deze een volledige tabelscan uitvoeren om de benodigde rijen te vinden. Op zeer kleine tafels is dit meestal geen probleem, maar naarmate de hoop groter wordt, nemen de prestaties snel af. Natuurlijk kan een niet-geclusterde index helpen door een aanwijzer te gebruiken naar het bestand, de pagina en de rij waar de gegevens zijn opgeslagen - meestal een veel beter alternatief voor tabelscans. Toch is het moeilijk om de voordelen van een geclusterde index te vergelijken als we kijken naar de prestaties van query's.
De hoop kan echter in bepaalde situaties de prestaties helpen verbeteren. Overweeg een tabel met een groot aantal invoegingen, maar onregelmatige updates of verwijderingen van gegevens. Een logtabel wordt bijvoorbeeld voornamelijk gebruikt om waarden in te voegen tot ze worden gearchiveerd. Op de heap ziet u geen paginering en gegevensfragmentatie zoals bij een geclusterde index, omdat de rijen eenvoudig aan het einde van de heap worden toegevoegd. Te veel pagina's splitsen kan een aanzienlijke invloed hebben op de prestaties, en op een niet zo goede manier. Over het algemeen stelt de heap u in staat om relatief pijnloos gegevens in te voegen en hoeft u niet te worstelen met opslag- en onderhoudsoverhead zoals bij een geclusterde index.
Maar het ontbreken van het bijwerken en verwijderen van gegevens mag niet als de enige reden worden beschouwd. De manier waarop de gegevens worden bemonsterd, is ook een belangrijke factor. Gebruik de heap bijvoorbeeld niet als u vaak gegevensbereiken opvraagt, of als de gevraagde gegevens vaak gesorteerd of gegroepeerd moeten worden.
Dit alles betekent dat u alleen moet overwegen om de heap te gebruiken als u met zeer kleine tabellen werkt, of dat al uw interactie met de tabel beperkt is tot het invoegen van gegevens en uw zoekopdrachten uiterst eenvoudig zijn (en u gebruikt sowieso niet-geclusterde indexen). Blijf anders bij een goed ontworpen geclusterde index, bijvoorbeeld gedefinieerd op een eenvoudig oplopend sleutelveld, zoals een veelgebruikte kolom met IDENTITEIT.

Hoe wijzig ik de standaard vulfactor voor een index?

Het wijzigen van de standaard vulfactor van een index is één ding. Begrijpen hoe de standaardratio werkt, is anders. Maar eerst een paar stappen terug. De indexvulfactor bepaalt de hoeveelheid ruimte op een pagina om de index op het onderste niveau (bladniveau) op te slaan voordat een nieuwe pagina wordt gevuld. Als de coëfficiënt bijvoorbeeld is ingesteld op 90, neemt de index bij groei 90% op de pagina en gaat vervolgens naar de volgende pagina.
Standaard is de vulfactorwaarde van de index in SQL-server is gelijk aan 0, wat gelijk is aan 100. Als gevolg hiervan nemen alle nieuwe indexen deze instelling automatisch over, tenzij u specifiek in de code een waarde opgeeft die afwijkt van de standaardwaarde van het systeem of het standaardgedrag wijzigt. Je kunt gebruiken Studio voor SQL Server-beheer om de standaardwaarde te corrigeren of een door het systeem opgeslagen procedure uit te voeren sp_configure... Bijvoorbeeld de volgende set T-SQL commando's stelt de waarde van de coëfficiënt in op 90 (je moet eerst overschakelen naar de geavanceerde instellingenmodus):
EXEC sp_configure "toon geavanceerde opties", 1; GA OPNIEUW CONFIGUREREN; GO EXEC sp_configure "vulfactor", 90; GA OPNIEUW CONFIGUREREN; Gaan
Nadat u de waarde van de indexvulfactor hebt gewijzigd, moet u de service opnieuw starten SQL-server... U kunt nu de ingestelde waarde controleren door sp_configure uit te voeren zonder het opgegeven tweede argument:
EXEC sp_configure "vulfactor" GO
Deze opdracht zou een waarde van 90 moeten retourneren. Als gevolg hiervan zullen alle nieuw gemaakte indexen deze waarde gebruiken. U kunt dit testen door een index te maken en om een ​​vulfactorwaarde te vragen:
GEBRUIK AdventureWorks2012; - uw database GA EEN NIET-GECLUSTERDE INDEX MAKEN ix_people_lastname ON Person.Person (Achternaam); GA SELECT fill_factor VANUIT sys.indexes WAAR object_id = object_id ("Persoon.Persoon") EN naam = "ix_people_lastname";
In dit voorbeeld hebben we een niet-geclusterde index op de tafel gemaakt Persoon in de database AdventureWorks2012... Nadat we de index hebben gemaakt, kunnen we de vulfactorwaarde uit de systeemtabellen van sys.indexes halen. Het verzoek moet 90 retourneren.
Laten we echter zeggen dat we de index hebben verwijderd en opnieuw hebben gemaakt, maar nu hebben we een specifieke vulfactorwaarde opgegeven:
MAAK NIET-GECLUSTERDE INDEX ix_people_lastname ON Person.Person (Achternaam) MET (vulfactor = 80); GA SELECT fill_factor VANUIT sys.indexes WAAR object_id = object_id ("Persoon.Persoon") EN naam = "ix_people_lastname";
Deze keer hebben we de instructie toegevoegd MET en optie vulfactor voor onze maak index bewerking MAAK INDEX en gaf de waarde 80 aan. Operator KIES geeft nu de juiste waarde terug.
Tot nu toe is alles vrij eenvoudig verlopen. Waar je echt vastloopt in dit hele proces, is wanneer je een index maakt met de standaardcoëfficiëntwaarde, ervan uitgaande dat je die waarde kent. Iemand is bijvoorbeeld onhandig aan het sleutelen aan de serverinstellingen en hij is zo overdonderd dat hij de vulfactorwaarde van de index op 20 zet. In de tussentijd ga je door met het maken van indexen, ervan uitgaande dat de standaardwaarde 0 is. hebben geen manier om de waarde van de coëfficiënt eerder te achterhalen, zolang u geen index maakt en vervolgens de waarde controleert zoals we deden in onze voorbeelden. Anders zul je moeten wachten op het moment dat de queryprestaties zo sterk dalen dat je iets begint te vermoeden.
Een ander probleem om in gedachten te houden is het opnieuw opbouwen van de index. Net als bij het maken van een index, kunt u de vulfactor van de index opgeven wanneer u deze opnieuw opbouwt. In tegenstelling tot de opdracht create index gebruikt het opnieuw opbouwen echter niet de standaard serverinstellingen, hoewel het lijkt. Sterker nog, als u niet specifiek de waarde van de vulfactor van de index aangeeft, dan SQL-server zal de waarde gebruiken van de coëfficiënt waarmee deze index bestond vóór de herstructurering ervan. Bijvoorbeeld de volgende bewerking: ALTER INDEX herbouwt de index die we zojuist hebben gemaakt:
ALTER INDEX ix_people_lastname ON Person.Person REBUILD; GA SELECT fill_factor VANUIT sys.indexes WAAR object_id = object_id ("Persoon.Persoon") EN naam = "ix_people_lastname";
Wanneer we de vulfactorwaarde controleren, krijgen we een waarde van 80, omdat we dat hebben opgegeven toen we de index voor het laatst maakten. De standaardwaarde wordt genegeerd.
Zoals u kunt zien, is het wijzigen van de vulfactorwaarde van de index niet zo moeilijk. Het is veel moeilijker om de huidige waarde te kennen en te begrijpen wanneer deze wordt toegepast. Als u altijd een factor specifiek specificeert bij het maken en opnieuw opbouwen van indexen, dan weet u altijd het specifieke resultaat. Tenzij je voor iemand anders moet zorgen dat je serverinstellingen opnieuw verknoeien, waardoor alle indexen opnieuw worden opgebouwd met belachelijk lage indexvulfactoren.

Kan ik een geclusterde index maken op een kolom die duplicaten bevat?

Ja en nee. Ja, u kunt een geclusterde index maken op een sleutelkolom die dubbele waarden bevat. Nee, de waarde van de sleutelkolom kan niet in een niet-unieke staat blijven. Laat het me uitleggen. Als u een niet-unieke geclusterde index voor een kolom maakt, voegt de opslagengine een uniquifier toe aan de dubbele waarde om uniekheid te garanderen en er zo voor te zorgen dat elke rij in de geclusterde tabel kan worden geïdentificeerd.
U kunt bijvoorbeeld besluiten om een ​​geclusterde index te maken op een kolom in een klantgegevenstabel. Achternaam de achternaam houden. De kolom bevat waarden zoals Franklin, Hancock, Washington en Smith. Vervolgens vul je de waarden voor Adams, Hancock, Smith en Smith opnieuw in. Maar de waarde van de sleutelkolom moet uniek zijn, dus het opslagsubsysteem zal de waarde van de duplicaten wijzigen zodat ze er ongeveer zo uitzien: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 en Smith5678.
Op het eerste gezicht lijkt deze benadering normaal, maar een integerwaarde vergroot de sleutelgrootte, wat een probleem kan zijn bij een groot aantal duplicaten, en deze waarden zullen de basis worden van een niet-geclusterde index of een refererende sleutelreferentie. Om deze redenen moet u waar mogelijk altijd proberen unieke geclusterde indexen te maken. Als dit niet mogelijk is, probeer dan in ieder geval kolommen te gebruiken met een zeer hoog gehalte aan unieke waarden.

Hoe wordt de tabel opgeslagen als er geen geclusterde index is gemaakt?

SQL-server ondersteunt twee soorten tabellen: geclusterde tabellen met een geclusterde index en heaptabellen of gewoon heaps. In tegenstelling tot geclusterde tabellen worden gegevens in de heap op geen enkele manier gesorteerd. In feite is dit een hoop (hoop) gegevens. Als u een rij aan zo'n tabel toevoegt, zal het opslagsubsysteem deze eenvoudig aan het einde van de pagina toevoegen. Wanneer de pagina is gevuld met gegevens, wordt deze toegevoegd aan de nieuwe pagina. In de meeste gevallen zult u een geclusterde index op een tabel willen maken om te profiteren van de sorteermogelijkheid en om zoekopdrachten te versnellen (probeer u eens voor te stellen dat u een telefoonnummer in een adresboek vindt dat niet volgens enig principe is gesorteerd). Als u er echter voor kiest geen geclusterde index te maken, kunt u nog steeds een niet-geclusterde index op de heap maken. In dit geval heeft elke rij van de index een aanwijzer naar een heaprij. De aanwijzer bevat een bestands-ID, paginanummer en dataregelnummer.

Wat is de relatie tussen uniciteitsbeperkingen voor een waarde en een primaire sleutel met tabelindexen?

De primaire sleutel en de unieke beperking zorgen ervoor dat de waarden in de kolom uniek zijn. U kunt slechts één primaire sleutel voor een tabel maken en deze mag geen waarden bevatten NUL... U kunt verschillende beperkingen maken voor de uniciteit van een waarde voor een tabel, en elk van hen kan een enkele record hebben met NUL.
Wanneer u een primaire sleutel maakt, maakt het opslagsubsysteem ook een unieke geclusterde index als er nog geen geclusterde index is gemaakt. U kunt het standaardgedrag echter overschrijven en er wordt een niet-geclusterde index gemaakt. Als er een geclusterde index bestaat wanneer u een primaire sleutel maakt, wordt er een unieke niet-geclusterde index gemaakt.
Wanneer u een uniciteitsbeperking maakt, maakt het opslagsubsysteem een ​​unieke, niet-geclusterde index. U kunt echter aangeven dat u een unieke geclusterde index wilt maken als deze nog niet eerder is gemaakt.
Over het algemeen zijn de beperkingen op de uniciteit van een waarde en een unieke index hetzelfde.

Waarom worden geclusterde en niet-geclusterde indexen B-Tree genoemd in SQL Server?

Basisindexen in SQL Server, al dan niet geclusterd, worden verspreid over paginasets die indexknooppunten zijn. Deze pagina's zijn georganiseerd in een specifieke hiërarchie met een boomstructuur die een evenwichtige boom wordt genoemd. Op het bovenste niveau bevindt zich het hoofdknooppunt, onderaan de bladeindknooppunten, met tussenliggende knooppunten tussen het bovenste en onderste niveau, zoals weergegeven in de afbeelding:


Het hoofdknooppunt biedt het belangrijkste toegangspunt voor query's die proberen gegevens via de index op te halen. Vanaf dit knooppunt initieert de query-engine een hiërarchische analyse naar een geschikt eindknooppunt dat gegevens bevat.
Stel bijvoorbeeld dat er een verzoek wordt ontvangen om rijen op te halen met een sleutelwaarde van 82. De query-engine start vanaf het hoofdknooppunt, dat verwijst naar een geschikt tussenknooppunt, in ons geval 1-100. Vanaf het tussenknooppunt 1-100 gaat de overgang naar het knooppunt 51-100 en van daaruit naar het eindknooppunt 76-100. Als het een geclusterde index is, bevat het knooppuntblad de rijgegevens die zijn gekoppeld aan sleutel 82. Als het een niet-geclusterde index is, bevat het indexblad een aanwijzer naar de geclusterde tabel of een specifieke rij op de heap.

Hoe kan een index de prestaties van zoekopdrachten in het algemeen verbeteren als u al deze inodes moet doorlopen?

Ten eerste verbeteren indexen niet altijd de prestaties. Te veel slecht gemaakte indexen zorgen ervoor dat het systeem overstroomt en de queryprestaties vertragen. Het zou nauwkeuriger zijn om te zeggen dat als indexen zorgvuldig worden toegepast, ze aanzienlijke prestatieverbeteringen kunnen opleveren.
Denk aan een enorm boek over prestatieafstemming SQL-server(papieren, geen elektronische versie). Stel je voor dat je informatie wilt vinden over het configureren van een Resource Governor. U kunt pagina voor pagina met uw vinger door het hele boek slepen, of de inhoudsopgave openen en het exacte paginanummer vinden met de informatie die u zoekt (mits het boek correct is geïndexeerd en de indexen correct zijn in de tabel van inhoud). Dit scheelt je natuurlijk veel tijd, ook al moet je eerst naar een heel andere structuur (index) om de informatie die je nodig hebt uit de primaire structuur (boek) te halen.
Als een boekenindex, een aanwijzer in SQL-server stelt u in staat om nauwkeurige query's uit te voeren op de gewenste gegevens, in plaats van een volledige scan van alle gegevens in de tabel. Voor kleine tabellen zijn volledige scans meestal geen probleem, maar grote tabellen nemen veel pagina's met gegevens in beslag, wat kan resulteren in een aanzienlijke queryruntime als er geen index bestaat, zodat de query-engine onmiddellijk de juiste gegevenslocatie kan vinden. Stel je voor dat je verdwaald bent op een kruispunt met meerdere niveaus voor een grote metropool zonder kaart, en je begrijpt het idee.

Als indexen zo geweldig zijn, waarom maakt u er dan niet gewoon een voor elke kolom?

Geen enkele goede daad mag ongestraft blijven. Dit is in ieder geval het geval bij indices. Indexen presteren natuurlijk goed zolang u query's uitvoert op het ophalen van gegevens met de operator. KIES, maar zodra de frequente oproep van operators begint INSERT, UPDATE en VERWIJDEREN dus het landschap verandert heel snel.
Wanneer u een gegevensverzoek initieert door een operator KIES, vindt de query-engine de index, doorloopt de boomstructuur en vindt de gegevens waarnaar hij zoekt. Wat is er makkelijker? Maar alles verandert als u een wijzigingsverklaring initieert, zoals: UPDATE... Ja, voor het eerste deel van de instructie kan de query-engine opnieuw de index gebruiken om de gewijzigde rij te vinden - dat is goed nieuws. En als er een eenvoudige wijziging in de gegevens in een rij is die geen invloed heeft op de wijziging in de belangrijkste kolommen, dan is het wijzigingsproces volledig pijnloos. Maar wat als de wijziging ervoor zorgt dat de pagina's met de gegevens worden gesplitst, of de waarde van de sleutelkolom verandert, waardoor deze naar een andere inode wordt verplaatst - dit zou ertoe leiden dat de index een reorganisatie vereist die alle bijbehorende indexen en bewerkingen beïnvloedt, resulterend in een wijdverbreide daling van de productiviteit.
Soortgelijke processen vinden plaats wanneer de operator wordt aangeroepen VERWIJDEREN... De index kan helpen bij het vinden van de locatie van de verwijderde gegevens, maar het verwijderen van de gegevens zelf kan leiden tot paginaherschikkingen. Met betrekking tot de operator: INSERT, de belangrijkste vijand van alle indexen: je begint een grote hoeveelheid gegevens toe te voegen, wat leidt tot indexwijzigingen en hun reorganisatie, en iedereen lijdt.
Houd dus rekening met de soorten query's op uw database wanneer u overweegt welk type indexen en hoeveel u wilt maken. Groter is niet beter. Voordat u een nieuwe index aan een tabel toevoegt, moet u de kosten berekenen van niet alleen basisquery's, maar ook van de hoeveelheid gebruikte schijfruimte, de kosten voor het onderhouden van de functionaliteit en indexen, wat kan leiden tot een domino-effect voor andere bewerkingen. Uw strategie voor het ontwerpen van een index is een van de belangrijkste aspecten van de implementatie en moet veel overwegingen omvatten, van de grootte van de index, het aantal unieke waarden tot het type query's dat de index ondersteunt.

Is het verplicht om een ​​geclusterde index op een primaire sleutelkolom te maken?

U kunt een geclusterde index maken voor elke kolom die aan de vereiste voorwaarden voldoet. Het is waar dat de geclusterde index en de primaire sleutelbeperking voor elkaar zijn gemaakt en ze zijn in de hemel getrouwd, dus houd er rekening mee dat wanneer u een primaire sleutel maakt, de geclusterde index automatisch wordt gemaakt als deze nog niet is gemaakt. U kunt echter besluiten dat een geclusterde index elders beter zal presteren, en vaak is uw beslissing de moeite waard.
Het belangrijkste doel van een geclusterde index is om alle rijen in uw tabel te sorteren op basis van de sleutelkolom die is opgegeven toen de index werd gedefinieerd. Dit maakt snel zoeken en gemakkelijke toegang tot tabelgegevens mogelijk.
De primaire sleutel van een tabel kan een goede keuze zijn, omdat deze elke rij in tabellen op unieke wijze identificeert zonder dat er aanvullende gegevens hoeven te worden toegevoegd. In sommige gevallen is de beste keuze een surrogaat primaire sleutel, die niet alleen uniek is, maar ook klein en stapsgewijs verhoogd, waardoor niet-geclusterde indexen op basis van deze waarde efficiënter worden. De query-optimizer houdt ook van deze combinatie van een geclusterde index en een primaire sleutel, omdat het samenvoegen van tabellen sneller is dan het samenvoegen op een andere manier waarbij de primaire sleutel en de bijbehorende geclusterde index niet worden gebruikt. Zoals ik al zei, dit is een huwelijk gemaakt in de hemel.
Uiteindelijk is het echter vermeldenswaard dat er bij het maken van een geclusterde index rekening moet worden gehouden met verschillende aspecten: hoeveel niet-geclusterde indexen erop worden gebaseerd, hoe vaak de waarde van de indexsleutelkolom zal veranderen en hoe veel groter. Wanneer de waarden in de geclusterde indexkolommen veranderen of de index niet presteert zoals verwacht, dan kunnen alle andere indexen in de tabel worden beïnvloed. De geclusterde index moet gebaseerd zijn op de meest stabiele kolom, die in een bepaalde volgorde wordt verhoogd, maar niet willekeurig wordt gewijzigd. De index moet query's op de meest gebruikte tabelgegevens ondersteunen, dus query's maken optimaal gebruik van de gegevens die worden gesorteerd en beschikbaar zijn op de hoofdknooppunten, de bladeren van de index. Als de primaire sleutel in dit scenario past, gebruik deze dan. Als dat niet het geval is, selecteert u een andere set kolommen.

Wat als u een weergave indexeert, is het dan nog steeds een weergave?

Een weergave is een virtuele tabel die gegevens genereert uit een of meer tabellen. Kortom, het is een benoemde query die gegevens ophaalt uit de onderliggende tabellen wanneer u een query op die weergave aanroept. U kunt de prestaties van query's verbeteren door in deze weergave een geclusterde index en niet-geclusterde indexen te maken, vergelijkbaar met hoe u indexen voor een tabel maakt, maar het belangrijkste voorbehoud is dat er eerst een geclusterde index wordt gemaakt en vervolgens kunt u een niet-geclusterde index maken. een geclusterd.
Wanneer een geïndexeerde weergave (gematerialiseerde weergave) wordt gemaakt, blijft de weergavedefinitie zelf een afzonderlijke entiteit. Dit is tenslotte maar een hard-coded statement KIES opgeslagen in de databank. Maar de index is een heel ander verhaal. Wanneer u een geclusterde of niet-geclusterde index voor een weergave maakt, worden de gegevens fysiek op schijf opgeslagen, net als een normale index. Bovendien, wanneer gegevens in de onderliggende tabellen veranderen, wordt de index van de weergave automatisch gewijzigd (dit betekent dat u wellicht wilt voorkomen dat de weergaven worden geïndexeerd van tabellen die regelmatig worden gewijzigd). In ieder geval blijft de weergave een weergave - een blik op de tabellen, maar precies uitgevoerd op dit moment, met de bijbehorende indices.
Voordat u een index voor een weergave kunt maken, moet deze aan verschillende beperkingen voldoen. Een weergave kan bijvoorbeeld alleen verwijzen naar basistabellen, niet naar andere weergaven, en deze tabellen moeten zich in dezelfde database bevinden. In feite zijn er veel andere beperkingen, dus zorg ervoor dat u de documentatie raadpleegt voor: SQL-server achter alle vuile details.

Waarom een ​​dekkingsindex gebruiken in plaats van een samengestelde index?

Laten we er eerst voor zorgen dat we het verschil tussen de twee begrijpen. Een samengestelde index is slechts een gewone index die meer dan één kolom bevat. Er kunnen meerdere sleutelkolommen worden gebruikt om ervoor te zorgen dat elke rij in een tabel uniek is. Het is ook mogelijk wanneer de primaire sleutel meerdere kolommen heeft om ervoor te zorgen dat deze uniek is, of u probeert de uitvoering van veelgebruikte zoekopdrachten op meerdere kolommen te optimaliseren . In het algemeen geldt echter dat hoe meer sleutelkolommen een index bevat, hoe minder efficiënt de index is, en daarom moeten samengestelde indexen oordeelkundig worden gebruikt.
Zoals gezegd, kan een zoekopdracht enorm profiteren als alle benodigde gegevens direct op de bladeren van de index staan, net als de index zelf. Dit is geen probleem voor een geclusterde index, aangezien alle gegevens zijn er al (daarom is het zo belangrijk om goed na te denken wanneer u een geclusterde index maakt). Maar de niet-geclusterde index op bladeren bevat alleen sleutelkolommen. Er zijn aanvullende stappen vereist om de query-optimizer toegang te geven tot alle overige gegevens, wat aanzienlijke extra overhead kan veroorzaken bij het voltooien van uw query's.
Dit is waar de dekkingsindex van pas komt. Wanneer u een niet-geclusterde index definieert, kunt u extra kolommen opgeven voor uw sleutelkolommen. Stel dat uw toepassing regelmatig kolomgegevens opvraagt Order ID en Besteldatum in de tafel verkoop:
SELECT OrderID, OrderDate UIT Verkoop WAAR OrderID = 12345;
U kunt een samengestelde niet-geclusterde index voor beide kolommen maken, maar de OrderDate-kolom voegt alleen overhead voor indexonderhoud toe, maar zal nooit als een bijzonder nuttige sleutelkolom dienen. De beste oplossing zou zijn om een ​​dekkingsindex te maken met een sleutelkolom Order ID en een extra opgenomen kolom Besteldatum:
MAAK NIET-GECLUSTERDE INDEX ix_orderid OP dbo.Sales (OrderID) INCLUDE (OrderDate);
Door dit te doen, vermijdt u de nadelen van het indexeren van overtollige kolommen, terwijl u de voordelen behoudt van het opslaan van gegevens op bladeren bij het uitvoeren van query's. De meegeleverde kolom maakt geen deel uit van de sleutel, maar de gegevens worden opgeslagen in het laatste knooppunt, het indexblad. Dit kan de queryprestaties verbeteren zonder extra kosten. Bovendien zijn er minder beperkingen op de kolommen die zijn opgenomen in de dekkingsindex dan op de sleutelkolommen van de index.

Is het aantal duplicaten in een sleutelkolom van belang?

Wanneer u een index maakt, moet u proberen het aantal duplicaten in uw sleutelkolommen te verminderen. Of beter gezegd: probeer de herhalingsratio zo laag mogelijk te houden.
Als u met een samengestelde index werkt, geldt de duplicatie in het algemeen voor alle sleutelkolommen. Een enkele kolom kan veel dubbele waarden bevatten, maar er moet minimale duplicatie zijn tussen alle kolommen in de index. U maakt bijvoorbeeld een samengestelde niet-geclusterde index op kolommen Voornaam en Achternaam, je kunt veel John Doe-waarden hebben en veel Doe-waarden, maar je wilt zo min mogelijk John Doe-waarden, of beter slechts één John Doe-waarde.
De uniciteitsfactor voor sleutelkolomwaarden wordt indexselectiviteit genoemd. Hoe meer unieke waarden er zijn, hoe hoger de selectiviteit: de unieke index heeft de hoogst mogelijke selectiviteit. De query-engine houdt erg van kolommen met een hoge selectiviteit, vooral als die kolommen deelnemen aan de WHERE-clausules van uw meest uitgevoerde query's. Hoe hoger de selectiviteit van de index, hoe sneller de query-engine de omvang van de resulterende dataset kan verkleinen. Het nadeel is natuurlijk dat kolommen met relatief weinig unieke waarden zelden goede kandidaten zijn voor indexering.

Is het mogelijk om een ​​niet-geclusterde index te maken op slechts een specifieke subset van de belangrijkste kolomgegevens?

Standaard bevat een niet-geclusterde index één rij voor elke rij in de tabel. Natuurlijk kun je hetzelfde zeggen voor een geclusterde index, ervan uitgaande dat de index een tabel is. Maar wat betreft de niet-geclusterde index is de één-op-één relatie een belangrijk begrip, omdat beginnend met versie SQL Server 2008, hebt u de mogelijkheid om een ​​filterbare index te maken die de daarin opgenomen rijen beperkt. Een filterbare index kan de queryprestaties verbeteren omdat: het is kleiner en bevat gefilterde, nauwkeurigere statistieken dan alle tabelstatistieken - dit leidt tot betere uitvoeringsplannen. Een filterbare index vereist ook minder opslagruimte en lagere onderhoudskosten. De index wordt alleen bijgewerkt wanneer de gegevens die overeenkomen met het filter veranderen.
Bovendien is een filterbare index eenvoudig te maken. In de operator MAAK INDEX je hoeft alleen maar op te geven in WAAR filterende toestand. U kunt bijvoorbeeld alle rijen met NULL's uit de index filteren, zoals weergegeven in de code:
MAAK NIET-GECLUSTERDE INDEX ix_trackingnumber OP Sales.SalesOrderDetail (CarrierTrackingNumber) WAAR CarrierTrackingNumber NIET NUL IS;
We kunnen in feite alle gegevens uitfilteren die niet belangrijk zijn bij kritieke zoekopdrachten. Maar wees voorzichtig, want SQL-server legt verschillende beperkingen op aan filterbare indexen, zoals het onvermogen om een ​​filterbare index voor een weergave te maken, dus lees de documentatie aandachtig door.
Ook kan het voorkomen dat u vergelijkbare resultaten kunt bereiken door een geïndexeerde weergave te maken. Een filterbare index heeft echter verschillende voordelen, zoals het verlagen van onderhoudskosten en het verbeteren van de kwaliteit van uw uitvoeringsplannen. Gefilterde indexen kunnen ook online opnieuw worden opgebouwd. Probeer het eens met een geïndexeerde weergave.

En nog een beetje van de vertaler

Het doel van het verschijnen van deze vertaling op de pagina's van Habrahabr was om u te vertellen of te herinneren aan de SimpleTalk-blog van Rode Poort.
Het publiceert veel vermakelijke en interessante inzendingen.
Ik ben niet aangesloten bij producten van het bedrijf Rode Poort noch met hun verkoop.

Zoals beloofd zijn boeken voor wie meer wil weten
Ik zal drie zeer goede boeken van mezelf aanbevelen (links leiden naar ontsteken versies in de winkel Amazone):

In principe kunt u eenvoudige indexen openen Labels toevoegen

Microsoft SQL Server 2012 T-SQL Fundamentals (referentie voor ontwikkelaars) Auteur Itzik Ben-Gan Publicatiedatum: 15 juli 2012 De auteur, een meester in zijn vak, geeft basiskennis over het werken met databases. Als je alles bent vergeten of nooit hebt geweten, dan is het zeker de moeite waard om het te lezen.

Indexen- dit is het eerste dat goed begrepen moet worden in het werk SQL-server maar vreemd genoeg worden fundamentele vragen niet vaak gesteld op de forums en worden er niet zo veel antwoorden ontvangen.
Rob Sheldon beantwoordt deze gênante professionele vragen over indices in SQL-server: we schamen ons gewoon om sommigen van hen te vragen, en voordat we anderen vragen, zullen we eerst twee keer nadenken.

Gebruikte terminologie:

inhoudsopgave	inhoudsopgave
hoop	hoop
tafel	tafel
weergave	uitvoering
B-boom	evenwichtige boom
geclusterde index	geclusterde index
niet-geclusterde index	niet-geclusterde index
samengestelde index	samengestelde index
dekkingsindex	dekkingsindex
primaire sleutelbeperking	beperking op primaire sleutel
unieke beperking	beperking van de uniciteit van waarden
vraag	navraag
query-engine	query-engine
database	database
database-engine	opslag subsysteem
vulfactor	index vulfactor
surrogaat primaire sleutel	surrogaat primaire sleutel
query-optimalisatie	query-optimalisatie
index selectiviteit	index selectiviteit
gefilterde index	filtreerbare index
uitvoeringsplan	implementatieplan

Grondbeginselen van indexen in SQL Server.

Een van de belangrijkste manieren om een ​​hoge productiviteit te bereiken SQL-server is het gebruik van indices. Een index versnelt het queryproces door snelle toegang te bieden tot rijen met gegevens in een tabel, net zoals een index in een boek u helpt snel de informatie te vinden die u nodig hebt. In dit artikel zal ik een snel overzicht geven van indexen in SQL-server en leg uit hoe ze in de database zijn georganiseerd en hoe ze databasequery's helpen versnellen.

Indexen worden gemaakt op kolommen in tabellen en weergaven. Indexen bieden een manier om snel gegevens te vinden op basis van de waarden in deze kolommen. Als u bijvoorbeeld een index op een primaire sleutel maakt en vervolgens naar een rij met gegevens zoekt met behulp van de primaire sleutelwaarden, dan: SQL-server vindt eerst de indexwaarde en gebruikt vervolgens de index om snel de volledige gegevensrij te vinden. Zonder index wordt een volledige scan (scan) van alle rijen in de tabel uitgevoerd, wat een aanzienlijke prestatie-impact kan hebben.
U kunt een index maken voor de meeste kolommen van een tabel of weergave. De uitzondering zijn voornamelijk kolommen met gegevenstypen voor het opslaan van grote objecten ( LOB), zoals afbeelding, tekst of varchar (max)... U kunt ook indexen maken op kolommen die zijn ontworpen om gegevens in de indeling op te slaan XML, maar deze indexen zijn iets anders gerangschikt dan de standaardindexen en hun beschouwing valt buiten het bestek van dit artikel. Het artikel dekt ook niet columnstore indices. In plaats daarvan concentreer ik me op de indexen die het meest worden gebruikt in databases. SQL-server.
Een index bestaat uit een set pagina's, indexknooppunten, die zijn georganiseerd in een boomstructuur - evenwichtige boom... Deze structuur is hiërarchisch van aard en begint met het hoofdknooppunt bovenaan de hiërarchie en bladknooppunten onderaan, zoals weergegeven in de afbeelding:

Wanneer u een query uitvoert op een geïndexeerde kolom, begint de query-engine vanaf de bovenkant van het hoofdknooppunt en gaat geleidelijk naar beneden door de tussenliggende knooppunten, waarbij elke laag van de middelste laag meer gedetailleerde informatie over de gegevens bevat. De query-engine blijft de indexknooppunten doorlopen totdat deze het laagste niveau met indexbladeren bereikt. Als u bijvoorbeeld de waarde 123 zoekt in een geïndexeerde kolom, bepaalt de query-engine eerst de pagina op het eerste tussenliggende niveau op hoofdniveau. In dit geval wijst de eerste pagina naar een waarde van 1 tot 100, en de tweede van 101 tot 200, dus de query-engine verwijst naar de tweede pagina van dit tussenliggende niveau. Verder zal blijken dat u de derde pagina van het volgende tussenliggende niveau moet raadplegen. Vanaf hier leest de query-engine de waarde van de index zelf op een lager niveau. Indexbladen kunnen zowel de tabelgegevens zelf bevatten als gewoon een verwijzing naar de gegevensrijen in de tabel, afhankelijk van het type index: geclusterde index of niet-geclusterde index.

Geclusterde index

De geclusterde index slaat de feitelijke rijen met gegevens op in de bladeren van de index. Terugkerend naar het vorige voorbeeld, betekent dit dat de rij met gegevens die is gekoppeld aan de sleutelwaarde van 123, wordt opgeslagen in de index zelf. Een belangrijk kenmerk van een geclusterde index is dat alle waarden in een bepaalde volgorde worden gesorteerd, oplopend of aflopend. Een tabel of weergave kan dus slechts één geclusterde index hebben. Bovendien moet worden opgemerkt dat de gegevens in een tabel alleen in gesorteerde vorm worden opgeslagen als op deze tabel een geclusterde index is gemaakt.
Een tabel die geen geclusterde index heeft, wordt een heap genoemd.

Niet-geclusterde index

In tegenstelling tot een geclusterde index, bevatten de bladeren van een niet-geclusterde index alleen die kolommen ( toets), waarmee deze index wordt bepaald, en bevat ook een verwijzing naar de rijen met echte gegevens in de tabel. Dit betekent dat het subquerysysteem extra handelingen nodig heeft om de vereiste gegevens te lokaliseren en op te halen. De inhoud van een verwijzing naar gegevens hangt af van of de gegevens zijn opgeslagen op een geclusterde tabel of op een heap. Als de aanwijzer verwijst naar een geclusterde tabel, leidt deze naar een geclusterde index die kan worden gebruikt om de echte gegevens te vinden. Als de aanwijzer naar een heap verwijst, leidt deze naar een specifieke identifier van de gegevensrij. Niet-geclusterde indexen kunnen niet worden gesorteerd als geclusterde indexen, maar u kunt meer dan één niet-geclusterde index voor een tabel of weergave maken, maximaal 999. Dit betekent niet dat u zoveel mogelijk indexen moet maken. Indexen kunnen de systeemprestaties zowel verbeteren als verslechteren. U kunt niet alleen meerdere niet-geclusterde indexen maken, maar u kunt ook extra kolommen opnemen ( opgenomen kolom) naar zijn index: de bladeren van de index zullen niet alleen de waarde van de geïndexeerde kolommen zelf opslaan, maar ook de waarden van deze niet-geïndexeerde extra kolommen. Met deze aanpak kunt u enkele van de beperkingen van de index omzeilen. U kunt bijvoorbeeld een niet-indexeerbare kolom opnemen of de limiet voor de lengte van de index (in de meeste gevallen 900 bytes) omzeilen.

Indextypen

Naast het feit dat een index geclusterd of niet-geclusterd kan zijn, is het mogelijk om deze optioneel te configureren als een samengestelde index, een unieke index of een dekkende index.

Samengestelde index

Zo'n index kan meer dan één kolom bevatten. U kunt maximaal 16 kolommen in de index opnemen, maar hun totale lengte is beperkt tot 900 bytes. Zowel geclusterde als niet-geclusterde indexen kunnen samengesteld zijn.

Unieke index

Deze index zorgt ervoor dat elke waarde in de geïndexeerde kolom uniek is. Als de index samengesteld is, geldt uniciteit voor alle kolommen in de index, maar niet voor elke afzonderlijke kolom. Als u bijvoorbeeld een unieke index voor kolommen maakt NAAM en ACHTERNAAM, dan moet de volledige naam uniek zijn, maar duplicaten in de voor- of achternaam zijn afzonderlijk mogelijk.
Er wordt automatisch een unieke index gemaakt wanneer u kolombeperkingen definieert: primaire sleutel of uniekheidsbeperking voor waarden:

• Hoofdsleutel
  Wanneer u een primaire sleutelbeperking definieert voor een of meer kolommen, dan SQL-server maakt automatisch een unieke geclusterde index aan als de geclusterde index nog niet eerder is gemaakt (in dit geval wordt een unieke niet-geclusterde index gemaakt op de primaire sleutel)
• Uniciteit van waarden
  Wanneer u een beperking definieert voor de uniciteit van waarden, dan SQL-server creëert automatisch een unieke niet-geclusterde index. U kunt opgeven dat er een unieke geclusterde index wordt gemaakt als er nog geen geclusterde index op de tabel is gemaakt

Dekkingsindex

Met een dergelijke index kan een specifieke zoekopdracht onmiddellijk alle benodigde gegevens uit de bladeren van de index halen zonder extra oproepen naar de records van de tabel zelf.

Indexen ontwerpen

Hoe nuttig indexen ook kunnen zijn, ze moeten zorgvuldig worden ontworpen. Aangezien indexen veel schijfruimte in beslag kunnen nemen, wilt u niet meer indexen maken dan nodig is. Bovendien worden indexen automatisch bijgewerkt wanneer de gegevensrij zelf wordt bijgewerkt, wat kan leiden tot extra resource-overhead en prestatievermindering. Er zijn verschillende overwegingen voor databases en query's waarmee u rekening moet houden bij het ontwerpen van indexen.

Database

Zoals eerder opgemerkt, kunnen indexen de systeemprestaties verbeteren omdat ze: ze bieden de query-engine een snel pad om gegevens te vinden. U moet echter ook overwegen hoe vaak u van plan bent gegevens in te voegen, bij te werken of te verwijderen. Wanneer u de gegevens wijzigt, moeten de indexen ook worden gewijzigd om de juiste acties op de gegevens weer te geven, wat de systeemprestaties aanzienlijk kan verslechteren. Houd rekening met de volgende richtlijnen bij het plannen van uw indexeringsstrategie:

• Gebruik voor tabellen die regelmatig worden bijgewerkt, zo min mogelijk indexen.
• Als de tabel een grote hoeveelheid gegevens bevat, maar de wijzigingen zijn klein, gebruik dan zoveel indexen als nodig is om de prestaties van uw zoekopdrachten te verbeteren. Denk echter goed na voordat u indexen op kleine tabellen gebruikt. misschien kan het gebruik van een indexzoekopdracht langer duren dan alleen het scannen van alle rijen.
• Probeer voor geclusterde indexen de velden zo kort mogelijk te houden. Het is het beste om een ​​geclusterde index te gebruiken op kolommen die unieke waarden hebben en geen NULL's toestaan. Dit is de reden waarom de primaire sleutel vaak wordt gebruikt als een geclusterde index.
• De uniciteit van de waarden in de kolom is van invloed op de prestaties van de index. Over het algemeen geldt dat hoe meer duplicaten u in een kolom heeft, hoe slechter de index zal presteren. Aan de andere kant, hoe meer unieke waarden er zijn, hoe beter de gezondheid van de index. Gebruik waar mogelijk een unieke index.
• Houd bij een samengestelde index rekening met de volgorde van de kolommen in de index. Kolommen die in uitdrukkingen worden gebruikt WAAR(bijvoorbeeld, WHERE Voornaam = 'Charlie') moet eerst in de index staan. Volgende kolommen moeten worden vermeld op basis van hun uniciteit (kolommen met het hoogste aantal unieke waarden komen eerst).
• U kunt ook een index opgeven voor berekende kolommen als ze aan bepaalde vereisten voldoen. De uitdrukking die wordt gebruikt om de waarde van een kolom op te halen, moet bijvoorbeeld deterministisch zijn (retourneert altijd hetzelfde resultaat voor een bepaalde set invoerparameters).

Databasequery's

Een andere overweging waarmee u rekening moet houden bij het ontwerpen van indexen, is welke query's er worden uitgevoerd op de database. Zoals eerder vermeld, moet u overwegen hoe vaak de gegevens veranderen. Bovendien moeten de volgende principes worden gebruikt:

• Probeer zoveel mogelijk rijen in één query in te voegen of te wijzigen, in plaats van dit in meerdere afzonderlijke query's te doen.
• Maak een niet-geclusterde index op kolommen die vaak worden gebruikt in uw zoekopdrachten als zoektermen in WAAR en verbindingen in MEEDOEN.
• Overweeg het indexeren van kolommen die worden gebruikt in zoekopdrachten met tekenreeksen voor exacte overeenkomstwaarden.

Waarom kan een tabel geen twee geclusterde indexen hebben?

Wil je een kort antwoord? Een geclusterde index is een tabel. Wanneer u een geclusterde index voor een tabel maakt, sorteert de opslagengine alle rijen in de tabel in oplopende of aflopende volgorde volgens de definitie van de index. Een geclusterde index is geen aparte entiteit zoals andere indexen, maar een mechanisme voor het sorteren van gegevens in een tabel en het vergemakkelijken van snelle toegang tot rijen met gegevens.
Stel dat u een tabel hebt met de geschiedenis van verkooptransacties. De tabel Verkoop bevat informatie zoals order-ID, artikelpositie in de order, artikelnummer, artikelhoeveelheid, ordernummer en datum, enzovoort. U maakt een geclusterde index op kolommen Order ID en Lijn-ID, gesorteerd in oplopende volgorde zoals hieronder weergegeven: T-SQL code:

MAAK UNIEKE GECLUSTERDE INDEX ix_oriderid_lineid OP dbo.Sales (OrderID, LineID);

Wanneer u dit script uitvoert, worden alle rijen in de tabel eerst fysiek gesorteerd op de OrderID-kolom en vervolgens op de LineID, maar de gegevens zelf blijven in een enkel logisch blok, in de tabel. Om deze reden kunt u geen twee geclusterde indexen maken. Er kan slechts één tabel met één gegevens zijn en deze tabel kan slechts één keer in een specifieke volgorde worden gesorteerd.

Als een geclusterde tafel veel voordelen biedt, waarom dan een hoop gebruiken?

Je hebt gelijk. Geclusterde tabellen zijn geweldig en de meeste van uw zoekopdrachten zullen beter presteren op tabellen met een geclusterde index. Maar in sommige gevallen wilt u de tafels misschien in hun natuurlijke, onberispelijke staat laten, d.w.z. als een hoop, en maak alleen niet-geclusterde indexen om uw zoekopdrachten gezond te houden.
De heap slaat, zoals u zich herinnert, gegevens op in een willekeurige volgorde. Meestal voegt het opslagsubsysteem gegevens toe aan de tabel in de volgorde waarin deze zijn ingevoegd, maar het subsysteem verplaatst ook graag rijen voor efficiëntere opslag. Hierdoor heeft u geen enkele kans om te voorspellen in welke volgorde de gegevens worden opgeslagen.
Als de query-engine gegevens moet vinden zonder de voordelen van een niet-geclusterde index, zal deze een volledige tabelscan uitvoeren om de benodigde rijen te vinden. Op zeer kleine tafels is dit meestal geen probleem, maar naarmate de hoop groter wordt, nemen de prestaties snel af. Natuurlijk kan een niet-geclusterde index helpen door een aanwijzer te gebruiken naar het bestand, de pagina en de rij waar de gegevens zijn opgeslagen - meestal een veel beter alternatief voor tabelscans. Toch is het moeilijk om de voordelen van een geclusterde index te vergelijken als we kijken naar de prestaties van query's.
De hoop kan echter in bepaalde situaties de prestaties helpen verbeteren. Overweeg een tabel met een groot aantal invoegingen, maar onregelmatige updates of verwijderingen van gegevens. Een logtabel wordt bijvoorbeeld voornamelijk gebruikt om waarden in te voegen tot ze worden gearchiveerd. Op de heap ziet u geen paginering en gegevensfragmentatie zoals bij een geclusterde index, omdat de rijen eenvoudig aan het einde van de heap worden toegevoegd. Te veel pagina's splitsen kan een aanzienlijke invloed hebben op de prestaties, en op een niet zo goede manier. Over het algemeen stelt de heap u in staat om relatief pijnloos gegevens in te voegen en hoeft u niet te worstelen met opslag- en onderhoudsoverhead zoals bij een geclusterde index.
Maar het ontbreken van het bijwerken en verwijderen van gegevens mag niet als de enige reden worden beschouwd. De manier waarop de gegevens worden bemonsterd, is ook een belangrijke factor. Gebruik de heap bijvoorbeeld niet als u vaak gegevensbereiken opvraagt, of als de gevraagde gegevens vaak gesorteerd of gegroepeerd moeten worden.
Dit alles betekent dat u alleen moet overwegen om de heap te gebruiken als u met zeer kleine tabellen werkt, of dat al uw interactie met de tabel beperkt is tot het invoegen van gegevens en uw zoekopdrachten uiterst eenvoudig zijn (en u gebruikt sowieso niet-geclusterde indexen). Blijf anders bij een goed ontworpen geclusterde index, bijvoorbeeld gedefinieerd op een eenvoudig oplopend sleutelveld, zoals een veelgebruikte kolom met IDENTITEIT.

Hoe wijzig ik de standaard vulfactor voor een index?

Het wijzigen van de standaard vulfactor van een index is één ding. Begrijpen hoe de standaardratio werkt, is anders. Maar eerst een paar stappen terug. De indexvulfactor bepaalt de hoeveelheid ruimte op een pagina om de index op het onderste niveau (bladniveau) op te slaan voordat een nieuwe pagina wordt gevuld. Als de coëfficiënt bijvoorbeeld is ingesteld op 90, neemt de index bij groei 90% op de pagina en gaat vervolgens naar de volgende pagina.
Standaard is de vulfactorwaarde van de index in SQL-server is gelijk aan 0, wat gelijk is aan 100. Als gevolg hiervan nemen alle nieuwe indexen deze instelling automatisch over, tenzij u specifiek in de code een waarde opgeeft die afwijkt van de standaardwaarde van het systeem of het standaardgedrag wijzigt. Je kunt gebruiken Studio voor SQL Server-beheer om de standaardwaarde te corrigeren of een door het systeem opgeslagen procedure uit te voeren sp_configure... Bijvoorbeeld de volgende set T-SQL commando's stelt de waarde van de coëfficiënt in op 90 (je moet eerst overschakelen naar de geavanceerde instellingenmodus):

EXEC sp_configure "toon geavanceerde opties ", 1; GA OPNIEUW CONFIGUREREN; GA EXEC sp_configure" vulfactor ", 90; GA OPNIEUW CONFIGUREREN; GO

Nadat u de waarde van de indexvulfactor hebt gewijzigd, moet u de service opnieuw starten SQL-server... U kunt nu de ingestelde waarde controleren door sp_configure uit te voeren zonder het opgegeven tweede argument:

EXEC sp_configure "vulfactor" GO

Deze opdracht zou een waarde van 90 moeten retourneren. Als gevolg hiervan zullen alle nieuw gemaakte indexen deze waarde gebruiken. U kunt dit testen door een index te maken en om een ​​vulfactorwaarde te vragen:

GEBRUIK AdventureWorks2012; - uw database GA EEN NIET-GECLUSTERDE INDEX MAKEN ix_people_lastname ON Person.Person (Achternaam); GA SELECT fill_factor FROM sys .indexes WAAR object_id = object_id ("Persoon.Persoon") EN naam = "ix_people_lastname";

In dit voorbeeld hebben we een niet-geclusterde index op de tafel gemaakt Persoon in de database AdventureWorks2012... Nadat we de index hebben gemaakt, kunnen we de vulfactorwaarde uit de systeemtabellen van sys.indexes halen. Het verzoek moet 90 retourneren.
Laten we echter zeggen dat we de index hebben verwijderd en opnieuw hebben gemaakt, maar nu hebben we een specifieke vulfactorwaarde opgegeven:

MAAK NIET-GECLUSTERDE INDEX ix_people_lastname ON Person.Person (Achternaam) MET (vulfactor = 80); GA SELECT fill_factor FROM sys .indexes WAAR object_id = object_id ("Persoon.Persoon") EN naam = "ix_people_lastname";

Deze keer hebben we de instructie toegevoegd MET en optie vulfactor voor onze maak index bewerking MAAK INDEX en gaf de waarde 80 aan. Operator KIES geeft nu de juiste waarde terug.
Tot nu toe is alles vrij eenvoudig verlopen. Waar je echt vastloopt in dit hele proces, is wanneer je een index maakt met de standaardcoëfficiëntwaarde, ervan uitgaande dat je die waarde kent. Iemand is bijvoorbeeld onhandig aan het sleutelen aan de serverinstellingen en hij is zo overdonderd dat hij de vulfactorwaarde van de index op 20 zet. In de tussentijd ga je door met het maken van indexen, ervan uitgaande dat de standaardwaarde 0 is. hebben geen manier om de waarde van de coëfficiënt eerder te achterhalen, zolang u geen index maakt en vervolgens de waarde controleert zoals we deden in onze voorbeelden. Anders zul je moeten wachten op het moment dat de queryprestaties zo sterk dalen dat je iets begint te vermoeden.
Een ander probleem om in gedachten te houden is het opnieuw opbouwen van de index. Net als bij het maken van een index, kunt u de vulfactor van de index opgeven wanneer u deze opnieuw opbouwt. In tegenstelling tot de opdracht create index gebruikt het opnieuw opbouwen echter niet de standaard serverinstellingen, hoewel het lijkt. Sterker nog, als u niet specifiek de waarde van de vulfactor van de index aangeeft, dan SQL-server zal de waarde gebruiken van de coëfficiënt waarmee deze index bestond vóór de herstructurering ervan. Bijvoorbeeld de volgende bewerking: ALTER INDEX herbouwt de index die we zojuist hebben gemaakt:

ALTER INDEX ix_people_lastname ON Person.Person REBUILD; GA SELECT fill_factor FROM sys .indexes WAAR object_id = object_id ("Persoon.Persoon") EN naam = "ix_people_lastname";

Wanneer we de vulfactorwaarde controleren, krijgen we een waarde van 80, omdat we dat hebben opgegeven toen we de index voor het laatst maakten. De standaardwaarde wordt genegeerd.
Zoals u kunt zien, is het wijzigen van de vulfactorwaarde van de index niet zo moeilijk. Het is veel moeilijker om de huidige waarde te kennen en te begrijpen wanneer deze wordt toegepast. Als u altijd een factor specifiek specificeert bij het maken en opnieuw opbouwen van indexen, dan weet u altijd het specifieke resultaat. Tenzij je voor iemand anders moet zorgen dat je serverinstellingen opnieuw verknoeien, waardoor alle indexen opnieuw worden opgebouwd met belachelijk lage indexvulfactoren.

Kan ik een geclusterde index maken op een kolom die duplicaten bevat?

Ja en nee. Ja, u kunt een geclusterde index maken op een sleutelkolom die dubbele waarden bevat. Nee, de waarde van de sleutelkolom kan niet in een niet-unieke staat blijven. Laat het me uitleggen. Als u een niet-unieke geclusterde index voor een kolom maakt, voegt de opslagengine een uniquifier toe aan de dubbele waarde om uniekheid te garanderen en er zo voor te zorgen dat elke rij in de geclusterde tabel kan worden geïdentificeerd.
U kunt bijvoorbeeld besluiten om een ​​geclusterde index te maken op een kolom in een klantgegevenstabel. Achternaam de achternaam houden. De kolom bevat waarden zoals Franklin, Hancock, Washington en Smith. Vervolgens vul je de waarden voor Adams, Hancock, Smith en Smith opnieuw in. Maar de waarde van de sleutelkolom moet uniek zijn, dus het opslagsubsysteem zal de waarde van de duplicaten wijzigen zodat ze er ongeveer zo uitzien: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 en Smith5678.
Op het eerste gezicht lijkt deze benadering normaal, maar een integerwaarde vergroot de sleutelgrootte, wat een probleem kan zijn bij een groot aantal duplicaten, en deze waarden zullen de basis worden van een niet-geclusterde index of een refererende sleutelreferentie. Om deze redenen moet u waar mogelijk altijd proberen unieke geclusterde indexen te maken. Als dit niet mogelijk is, probeer dan in ieder geval kolommen te gebruiken met een zeer hoog gehalte aan unieke waarden.

Hoe wordt de tabel opgeslagen als er geen geclusterde index is gemaakt?

SQL-server ondersteunt twee soorten tabellen: geclusterde tabellen met een geclusterde index en heaptabellen of gewoon heaps. In tegenstelling tot geclusterde tabellen worden gegevens in de heap op geen enkele manier gesorteerd. In feite is dit een hoop (hoop) gegevens. Als u een rij aan zo'n tabel toevoegt, zal het opslagsubsysteem deze eenvoudig aan het einde van de pagina toevoegen. Wanneer de pagina is gevuld met gegevens, wordt deze toegevoegd aan de nieuwe pagina. In de meeste gevallen zult u een geclusterde index op een tabel willen maken om te profiteren van de sorteermogelijkheid en om zoekopdrachten te versnellen (probeer u eens voor te stellen dat u een telefoonnummer in een adresboek vindt dat niet volgens enig principe is gesorteerd). Als u er echter voor kiest geen geclusterde index te maken, kunt u nog steeds een niet-geclusterde index op de heap maken. In dit geval heeft elke rij van de index een aanwijzer naar een heaprij. De aanwijzer bevat een bestands-ID, paginanummer en dataregelnummer.

Wat is de relatie tussen uniciteitsbeperkingen voor een waarde en een primaire sleutel met tabelindexen?

De primaire sleutel en de unieke beperking zorgen ervoor dat de waarden in de kolom uniek zijn. U kunt slechts één primaire sleutel voor een tabel maken en deze mag geen waarden bevatten NUL... U kunt verschillende beperkingen maken voor de uniciteit van een waarde voor een tabel, en elk van hen kan een enkele record hebben met NUL.
Wanneer u een primaire sleutel maakt, maakt het opslagsubsysteem ook een unieke geclusterde index als er nog geen geclusterde index is gemaakt. U kunt het standaardgedrag echter overschrijven en er wordt een niet-geclusterde index gemaakt. Als er een geclusterde index bestaat wanneer u een primaire sleutel maakt, wordt er een unieke niet-geclusterde index gemaakt.
Wanneer u een uniciteitsbeperking maakt, maakt het opslagsubsysteem een ​​unieke, niet-geclusterde index. U kunt echter aangeven dat u een unieke geclusterde index wilt maken als deze nog niet eerder is gemaakt.
Over het algemeen zijn de beperkingen op de uniciteit van een waarde en een unieke index hetzelfde.

Waarom worden geclusterde en niet-geclusterde indexen B-Tree genoemd in SQL Server?

Basisindexen in SQL Server, al dan niet geclusterd, worden verspreid over paginasets die indexknooppunten zijn. Deze pagina's zijn georganiseerd in een specifieke hiërarchie met een boomstructuur die een evenwichtige boom wordt genoemd. Op het bovenste niveau bevindt zich het hoofdknooppunt, onderaan de bladeindknooppunten, met tussenliggende knooppunten tussen het bovenste en onderste niveau, zoals weergegeven in de afbeelding:

Het hoofdknooppunt biedt het belangrijkste toegangspunt voor query's die proberen gegevens via de index op te halen. Vanaf dit knooppunt initieert de query-engine een hiërarchische analyse naar een geschikt eindknooppunt dat gegevens bevat.
Stel bijvoorbeeld dat er een verzoek wordt ontvangen om rijen op te halen met een sleutelwaarde van 82. De query-engine start vanaf het hoofdknooppunt, dat verwijst naar een geschikt tussenknooppunt, in ons geval 1-100. Vanaf het tussenknooppunt 1-100 gaat de overgang naar het knooppunt 51-100 en van daaruit naar het eindknooppunt 76-100. Als het een geclusterde index is, bevat het knooppuntblad de rijgegevens die zijn gekoppeld aan sleutel 82. Als het een niet-geclusterde index is, bevat het indexblad een aanwijzer naar de geclusterde tabel of een specifieke rij op de heap.

Hoe kan een index de prestaties van zoekopdrachten in het algemeen verbeteren als u al deze inodes moet doorlopen?

Ten eerste verbeteren indexen niet altijd de prestaties. Te veel slecht gemaakte indexen zorgen ervoor dat het systeem overstroomt en de queryprestaties vertragen. Het zou nauwkeuriger zijn om te zeggen dat als indexen zorgvuldig worden toegepast, ze aanzienlijke prestatieverbeteringen kunnen opleveren.
Denk aan een enorm boek over prestatieafstemming SQL-server(papieren, geen elektronische versie). Stel je voor dat je informatie wilt vinden over het configureren van een Resource Governor. U kunt pagina voor pagina met uw vinger door het hele boek slepen, of de inhoudsopgave openen en het exacte paginanummer vinden met de informatie die u zoekt (mits het boek correct is geïndexeerd en de indexen correct zijn in de tabel van inhoud). Dit scheelt je natuurlijk veel tijd, ook al moet je eerst naar een heel andere structuur (index) om de informatie die je nodig hebt uit de primaire structuur (boek) te halen.
Als een boekenindex, een aanwijzer in SQL-server stelt u in staat om nauwkeurige query's uit te voeren op de gewenste gegevens, in plaats van een volledige scan van alle gegevens in de tabel. Voor kleine tabellen zijn volledige scans meestal geen probleem, maar grote tabellen nemen veel pagina's met gegevens in beslag, wat kan resulteren in een aanzienlijke queryruntime als er geen index bestaat, zodat de query-engine onmiddellijk de juiste gegevenslocatie kan vinden. Stel je voor dat je verdwaald bent op een kruispunt met meerdere niveaus voor een grote metropool zonder kaart, en je begrijpt het idee.

Als indexen zo geweldig zijn, waarom maakt u er dan niet gewoon een voor elke kolom?

Geen enkele goede daad mag ongestraft blijven. Dit is in ieder geval het geval bij indices. Indexen presteren natuurlijk goed zolang u query's uitvoert op het ophalen van gegevens met de operator. KIES, maar zodra de frequente oproep van operators begint INSERT, UPDATE en VERWIJDEREN dus het landschap verandert heel snel.
Wanneer u een gegevensverzoek initieert door een operator KIES, vindt de query-engine de index, doorloopt de boomstructuur en vindt de gegevens waarnaar hij zoekt. Wat is er makkelijker? Maar alles verandert als u een wijzigingsverklaring initieert, zoals: UPDATE... Ja, voor het eerste deel van de instructie kan de query-engine opnieuw de index gebruiken om de gewijzigde rij te vinden - dat is goed nieuws. En als er een eenvoudige wijziging in de gegevens in een rij is die geen invloed heeft op de wijziging in de belangrijkste kolommen, dan is het wijzigingsproces volledig pijnloos. Maar wat als de wijziging ervoor zorgt dat de pagina's met de gegevens worden gesplitst, of de waarde van de sleutelkolom verandert, waardoor deze naar een andere inode wordt verplaatst - dit zou ertoe leiden dat de index een reorganisatie vereist die alle bijbehorende indexen en bewerkingen beïnvloedt, resulterend in een wijdverbreide daling van de productiviteit.
Soortgelijke processen vinden plaats wanneer de operator wordt aangeroepen VERWIJDEREN... De index kan helpen bij het vinden van de locatie van de verwijderde gegevens, maar het verwijderen van de gegevens zelf kan leiden tot paginaherschikkingen. Met betrekking tot de operator: INSERT, de belangrijkste vijand van alle indexen: je begint een grote hoeveelheid gegevens toe te voegen, wat leidt tot indexwijzigingen en hun reorganisatie, en iedereen lijdt.
Houd dus rekening met de soorten query's op uw database wanneer u overweegt welk type indexen en hoeveel u wilt maken. Groter is niet beter. Voordat u een nieuwe index aan een tabel toevoegt, moet u de kosten berekenen van niet alleen basisquery's, maar ook van de hoeveelheid gebruikte schijfruimte, de kosten voor het onderhouden van de functionaliteit en indexen, wat kan leiden tot een domino-effect voor andere bewerkingen. Uw strategie voor het ontwerpen van een index is een van de belangrijkste aspecten van de implementatie en moet veel overwegingen omvatten, van de grootte van de index, het aantal unieke waarden tot het type query's dat de index ondersteunt.

Is het verplicht om een ​​geclusterde index op een primaire sleutelkolom te maken?

U kunt een geclusterde index maken voor elke kolom die aan de vereiste voorwaarden voldoet. Het is waar dat de geclusterde index en de primaire sleutelbeperking voor elkaar zijn gemaakt en ze zijn in de hemel getrouwd, dus houd er rekening mee dat wanneer u een primaire sleutel maakt, de geclusterde index automatisch wordt gemaakt als deze nog niet is gemaakt. U kunt echter besluiten dat een geclusterde index elders beter zal presteren, en vaak is uw beslissing de moeite waard.
Het belangrijkste doel van een geclusterde index is om alle rijen in uw tabel te sorteren op basis van de sleutelkolom die is opgegeven toen de index werd gedefinieerd. Dit maakt snel zoeken en gemakkelijke toegang tot tabelgegevens mogelijk.
De primaire sleutel van een tabel kan een goede keuze zijn, omdat deze elke rij in tabellen op unieke wijze identificeert zonder dat er aanvullende gegevens hoeven te worden toegevoegd. In sommige gevallen is de beste keuze een surrogaat primaire sleutel, die niet alleen uniek is, maar ook klein en stapsgewijs verhoogd, waardoor niet-geclusterde indexen op basis van deze waarde efficiënter worden. De query-optimizer houdt ook van deze combinatie van een geclusterde index en een primaire sleutel, omdat het samenvoegen van tabellen sneller is dan het samenvoegen op een andere manier waarbij de primaire sleutel en de bijbehorende geclusterde index niet worden gebruikt. Zoals ik al zei, dit is een huwelijk gemaakt in de hemel.
Uiteindelijk is het echter vermeldenswaard dat er bij het maken van een geclusterde index rekening moet worden gehouden met verschillende aspecten: hoeveel niet-geclusterde indexen erop worden gebaseerd, hoe vaak de waarde van de indexsleutelkolom zal veranderen en hoe veel groter. Wanneer de waarden in de geclusterde indexkolommen veranderen of de index niet presteert zoals verwacht, dan kunnen alle andere indexen in de tabel worden beïnvloed. De geclusterde index moet gebaseerd zijn op de meest stabiele kolom, die in een bepaalde volgorde wordt verhoogd, maar niet willekeurig wordt gewijzigd. De index moet query's op de meest gebruikte tabelgegevens ondersteunen, dus query's maken optimaal gebruik van de gegevens die worden gesorteerd en beschikbaar zijn op de hoofdknooppunten, de bladeren van de index. Als de primaire sleutel in dit scenario past, gebruik deze dan. Als dat niet het geval is, selecteert u een andere set kolommen.

Wat als u een weergave indexeert, is het dan nog steeds een weergave?

Een weergave is een virtuele tabel die gegevens genereert uit een of meer tabellen. Kortom, het is een benoemde query die gegevens ophaalt uit de onderliggende tabellen wanneer u een query op die weergave aanroept. U kunt de prestaties van query's verbeteren door in deze weergave een geclusterde index en niet-geclusterde indexen te maken, vergelijkbaar met hoe u indexen voor een tabel maakt, maar het belangrijkste voorbehoud is dat er eerst een geclusterde index wordt gemaakt en vervolgens kunt u een niet-geclusterde index maken. een geclusterd.
Wanneer een geïndexeerde weergave (gematerialiseerde weergave) wordt gemaakt, blijft de weergavedefinitie zelf een afzonderlijke entiteit. Dit is tenslotte maar een hard-coded statement KIES opgeslagen in de databank. Maar de index is een heel ander verhaal. Wanneer u een geclusterde of niet-geclusterde index voor een weergave maakt, worden de gegevens fysiek op schijf opgeslagen, net als een normale index. Bovendien, wanneer gegevens in de onderliggende tabellen veranderen, wordt de index van de weergave automatisch gewijzigd (dit betekent dat u wellicht wilt voorkomen dat de weergaven worden geïndexeerd van tabellen die regelmatig worden gewijzigd). In ieder geval blijft de weergave een weergave - een blik op de tabellen, maar precies uitgevoerd op dit moment, met de bijbehorende indices.
Voordat u een index voor een weergave kunt maken, moet deze aan verschillende beperkingen voldoen. Een weergave kan bijvoorbeeld alleen verwijzen naar basistabellen, niet naar andere weergaven, en deze tabellen moeten zich in dezelfde database bevinden. In feite zijn er veel andere beperkingen, dus zorg ervoor dat u de documentatie raadpleegt voor: SQL-server achter alle vuile details.

Waarom een ​​dekkingsindex gebruiken in plaats van een samengestelde index?

Laten we er eerst voor zorgen dat we het verschil tussen de twee begrijpen. Een samengestelde index is slechts een gewone index die meer dan één kolom bevat. Er kunnen meerdere sleutelkolommen worden gebruikt om ervoor te zorgen dat elke rij in een tabel uniek is. Het is ook mogelijk wanneer de primaire sleutel meerdere kolommen heeft om ervoor te zorgen dat deze uniek is, of u probeert de uitvoering van veelgebruikte zoekopdrachten op meerdere kolommen te optimaliseren . In het algemeen geldt echter dat hoe meer sleutelkolommen een index bevat, hoe minder efficiënt de index is, en daarom moeten samengestelde indexen oordeelkundig worden gebruikt.
Zoals gezegd, kan een zoekopdracht enorm profiteren als alle benodigde gegevens direct op de bladeren van de index staan, net als de index zelf. Dit is geen probleem voor een geclusterde index, aangezien alle gegevens zijn er al (daarom is het zo belangrijk om goed na te denken wanneer u een geclusterde index maakt). Maar de niet-geclusterde index op bladeren bevat alleen sleutelkolommen. Er zijn aanvullende stappen vereist om de query-optimizer toegang te geven tot alle overige gegevens, wat aanzienlijke extra overhead kan veroorzaken bij het voltooien van uw query's.
Dit is waar de dekkingsindex van pas komt. Wanneer u een niet-geclusterde index definieert, kunt u extra kolommen opgeven voor uw sleutelkolommen. Stel dat uw toepassing regelmatig kolomgegevens opvraagt Order ID en Besteldatum in de tafel verkoop:

SELECT OrderID, OrderDate UIT Verkoop WAAR OrderID = 12345;

U kunt een samengestelde niet-geclusterde index voor beide kolommen maken, maar de OrderDate-kolom voegt alleen overhead voor indexonderhoud toe, maar zal nooit als een bijzonder nuttige sleutelkolom dienen. De beste oplossing zou zijn om een ​​dekkingsindex te maken met een sleutelkolom Order ID en een extra opgenomen kolom Besteldatum:

MAAK NIET-GECLUSTERDE INDEX ix_orderid OP dbo.Sales (OrderID) INCLUDE (OrderDate);

Door dit te doen, vermijdt u de nadelen van het indexeren van overtollige kolommen, terwijl u de voordelen behoudt van het opslaan van gegevens op bladeren bij het uitvoeren van query's. De meegeleverde kolom maakt geen deel uit van de sleutel, maar de gegevens worden opgeslagen in het laatste knooppunt, het indexblad. Dit kan de queryprestaties verbeteren zonder extra kosten. Bovendien zijn er minder beperkingen op de kolommen die zijn opgenomen in de dekkingsindex dan op de sleutelkolommen van de index.

Is het aantal duplicaten in een sleutelkolom van belang?

Wanneer u een index maakt, moet u proberen het aantal duplicaten in uw sleutelkolommen te verminderen. Of beter gezegd: probeer de herhalingsratio zo laag mogelijk te houden.
Als u met een samengestelde index werkt, geldt de duplicatie in het algemeen voor alle sleutelkolommen. Een enkele kolom kan veel dubbele waarden bevatten, maar er moet minimale duplicatie zijn tussen alle kolommen in de index. U maakt bijvoorbeeld een samengestelde niet-geclusterde index op kolommen Voornaam en Achternaam, je kunt veel John Doe-waarden hebben en veel Doe-waarden, maar je wilt zo min mogelijk John Doe-waarden, of beter slechts één John Doe-waarde.
De uniciteitsfactor voor sleutelkolomwaarden wordt indexselectiviteit genoemd. Hoe meer unieke waarden er zijn, hoe hoger de selectiviteit: de unieke index heeft de hoogst mogelijke selectiviteit. De query-engine houdt erg van kolommen met een hoge selectiviteit, vooral als die kolommen deelnemen aan de WHERE-clausules van uw meest uitgevoerde query's. Hoe hoger de selectiviteit van de index, hoe sneller de query-engine de omvang van de resulterende dataset kan verkleinen. Het nadeel is natuurlijk dat kolommen met relatief weinig unieke waarden zelden goede kandidaten zijn voor indexering.

Is het mogelijk om een ​​niet-geclusterde index te maken op slechts een specifieke subset van de belangrijkste kolomgegevens?

Standaard bevat een niet-geclusterde index één rij voor elke rij in de tabel. Natuurlijk kun je hetzelfde zeggen voor een geclusterde index, ervan uitgaande dat de index een tabel is. Maar wat betreft de niet-geclusterde index is de één-op-één relatie een belangrijk begrip, omdat beginnend met versie SQL Server 2008, hebt u de mogelijkheid om een ​​filterbare index te maken die de daarin opgenomen rijen beperkt. Een filterbare index kan de queryprestaties verbeteren omdat: het is kleiner en bevat gefilterde, nauwkeurigere statistieken dan alle tabelstatistieken - dit leidt tot betere uitvoeringsplannen. Een filterbare index vereist ook minder opslagruimte en lagere onderhoudskosten. De index wordt alleen bijgewerkt wanneer de gegevens die overeenkomen met het filter veranderen.
Bovendien is een filterbare index eenvoudig te maken. In de operator MAAK INDEX je hoeft alleen maar op te geven in WAAR filterende toestand. U kunt bijvoorbeeld alle rijen met NULL's uit de index filteren, zoals weergegeven in de code:

MAAK NIET-GECLUSTERDE INDEX ix_trackingnumber OP Sales.SalesOrderDetail (CarrierTrackingNumber) WAAR CarrierTrackingNumber NIET NUL IS;

We kunnen in feite alle gegevens uitfilteren die niet belangrijk zijn bij kritieke zoekopdrachten. Maar wees voorzichtig, want SQL-server legt verschillende beperkingen op aan filterbare indexen, zoals het onvermogen om een ​​filterbare index voor een weergave te maken, dus lees de documentatie aandachtig door.
Ook kan het voorkomen dat u vergelijkbare resultaten kunt bereiken door een geïndexeerde weergave te maken. Een filterbare index heeft echter verschillende voordelen, zoals het verlagen van onderhoudskosten en het verbeteren van de kwaliteit van uw uitvoeringsplannen. Gefilterde indexen kunnen ook online opnieuw worden opgebouwd. Probeer het eens met een geïndexeerde weergave.

Theoretisch materiaal

Indexen stellen u in staat om informatie zo efficiënt mogelijk te vinden in enorme databases.

SQL Server 2008 ondersteunt twee basistypen indexen: geclusterd en niet-geclusterd. Beide typen indexen worden geïmplementeerd als een uitgebalanceerde boom (B-boom) met het bladniveau aan de onderkant van de structuur. Het verschil tussen de twee indextypen is dat een geclusterde index zorgt voor de fysieke volgorde van gegevens op schijf. De geclusterde index is schaars - de wijzers in de bladeren van de B-boom verwijzen naar de gegevenspagina.

Een niet-geclusterde index is compact en bevat alleen de kolommen die in de indexsleutel zijn opgenomen. In dichte indexen verwijzen wijzers in de bladeren van de B-boom naar echte gegevensreeksen. Als er geen geclusterde index voor een tabel is gedefinieerd, wordt dit een heap of ongesorteerde tabel genoemd. In het laatste geval is de tabel fysiek georganiseerd (gesorteerd) in de volgorde waarin nieuwe records worden toegevoegd, in tegenstelling tot tabellen met geclusterde indexen, die zijn geordend op de waarden van de sorteersleutel. We kunnen zeggen dat een tabel in twee vormen kan worden weergegeven, als een heap of als een geclusterde index.

Geclusterde indexen

Geclusterde indexen kunnen worden gemaakt op basis van een of meer kolommen van een tabel - deze index wordt een indexsleutel genoemd en heeft een aantal beperkingen:

De kolommen van een geclusterde index worden een clustersleutel genoemd. Een geclusterde index heeft een speciale impact op SQL Server omdat het dwingt om de gegevens in de tabel te ordenen volgens de clustersleutel. Aangezien een tabel maar op één manier kan worden besteld, kan er slechts één geclusterde index op een tabel worden gespecificeerd.

Geclusterde indexen specificeren de sorteervolgorde van gegevens in een tabel. Geclusterde indexen bieden echter geen fysieke sorteervolgorde. Een geclusterde index ordent de gegevens op schijf niet fysiek, omdat dit zou resulteren in hoge schijf-I / O bij paging. Het zorgt er alleen voor dat de geïndexeerde paginaketen logisch geordend is, waardoor SQL Server direct door de paginaketen kan navigeren bij het zoeken naar gegevens. Als SQL Server naar beneden gaat in de geïndexeerde paginaketen, worden de gegevensrijen gelezen in de volgorde van de clustersleutel.

Niet-geclusterde index

Een niet-geclusterde index legt geen beperkingen op aan de volgorde van records in een tabel, dus er kunnen veel niet-geclusterde indexen op dezelfde tabel worden gemaakt, maar deze indexen hebben dezelfde beperkingen als geclusterde indexen:

De index mag niet meer dan 16 kolommen beslaan;

De maximale grootte van de indexsleutel is 900 bytes.

Het bladniveau van een niet-geclusterde index bevat een verwijzing naar de gewenste gegevens. Als de tabel een geclusterde index heeft, wijst het bladniveau van de niet-geclusterde index naar de clusteringsleutel. Als er geen geclusterde index is, verwijzen pagina's op bladniveau naar gegevensrijen in de tabel.

De algemene syntaxis voor het maken van een relationele index is:

CREATE INDEX indexnaam

AAN<объект>(kolom [,… n])

[ ; ]

Samengestelde index

Een samengestelde index kan worden gemaakt op basis van meerdere velden. In dit geval zijn de eerder beschreven beperkingen geldig. Als de index is gebouwd op velden met een vaste grootte, mag de som van de lengtes van deze velden deze 900 bytes niet overschrijden, als de index is gebouwd op velden met een variabele lengte, kan de som van de maximale grootte van de velden groter zijn dan 900 bytes, maar de waarde van de sommen voor elk record kan niet meer zijn dan 900 bytes. Een tabel heeft bijvoorbeeld twee velden met variabele lengte van elk 500 bytes. Met SQL Server kunt u een samengestelde sleutel maken op basis van deze twee velden als er geen records zijn waarvan de som van de lengtes voor beide velden groter is dan 900 bytes. Het is de moeite waard aandacht te besteden aan het punt dat de samengestelde index voor (Kolom1, Kolom2) verschilt van (Kolom2, Kolom1), evenals van de indexen die afzonderlijk voor deze twee velden zijn gemaakt.

Indexfragmentatie

Besturingssysteembestanden hebben de neiging om na verloop van tijd gefragmenteerd te raken als gevolg van herhaaldelijk schrijven. Indexen kunnen ook gefragmenteerd raken, maar indexfragmentatie verschilt van bestandsfragmentatie.

Wanneer een index wordt gemaakt, worden alle waarden voor de indexsleutel in geordende volgorde over de indexpagina's geschreven. Bij het verwijderen van een rij uit een tabel, moet SQL Server het corresponderende item in de index verwijderen, waardoor "gaten" in de indexpagina ontstaan. SQL Server wint de vrijgekomen ruimte niet terug vanwege de hoge kosten voor het lokaliseren en hergebruiken van indexgaten. Als de waarde in de basistabel verandert, verplaatst SQL Server het aanwijzerrecord naar een andere locatie, waardoor een nieuw gat ontstaat. Indexpagina-overlopen en paginasplitsingen moeten opnieuw indexfragmentatie veroorzaken. Na verloop van tijd raken indexen van tabellen waarin gegevens worden gewijzigd, gefragmenteerd.

Om de mate van fragmentatie van een index te regelen, wordt vaak een parameter gebruikt die de vulfactor wordt genoemd. Om fragmentatie te elimineren, kunt u ook de instructie ALTER INDEX gebruiken. De vulfactor is een indexparameter die bepaalt hoeveel vrije ruimte op elke pagina op bladniveau wordt gereserveerd wanneer de index wordt gemaakt of opnieuw wordt opgebouwd. De gereserveerde ruimte maakt verdere plaatsing van aanvullende waarden mogelijk, waardoor het aantal pagina-einden wordt verminderd. De opvulfactor wordt gemeten in hele percentages, bijvoorbeeld een waarde van 75 betekent dat elke pagina op bladniveau die wordt gemaakt 25% gratis moet bevatten ruimte om toekomstige waarden te accommoderen.

Indexen defragmenteren

Omdat SQL Server geen ruimte teruggeeft aan het systeem, moet u periodiek lege ruimte in de index terugwinnen om de prestatieverbeteringen te behouden die ervoor hebben gezorgd dat de index in de eerste plaats is gemaakt. Gebruik de instructie ALTER INDEX om indexen te defragmenteren.

ALTER INDEX (index_name | ALL)

AAN