Contacten
IBSurgeon
Deze pagina is automatisch vertaald. Lees het Engelse origineel. English

IBSurgeon-bibliotheek

Indexen (InterBase en Firebird)

Alexey Kovyazin, laatste update 07-sep-2005

Het concept dat als basis voor indices wordt aangenomen, is eenvoudig en visueel en is een van de belangrijkste fundamenten van databaseontwerp. Op basis van indices zijn veel basisobjecten van databases gebaseerd, en bovendien is het correcte gebruik van indices de sleutel tot een verbetering van de productiviteit van databaseapplicaties. Maar wat is een index? Een index is een geordende verwijzing naar de records in de tabel. Verwijzing betekent dat de index waarden bevat van een of meerdere velden in de tabel en de adressen van datapagina’s waar deze waarden zich bevinden (voor details over datapagina’s, zie het hoofdstuk “InterBase database structuur”) (deel 4). Met andere woorden, een index bestaat uit paren van waarden “veldwaarde” - “fysieke locatie van dit veld”.

Dus, op basis van de waarde van het veld (of velden), opgenomen in de index, kunnen we met behulp van de index snel de plaats in de tabel vinden waar het record met deze waarde is toegewezen. Geordend betekent dat de waarden van de velden die in de index zijn opgeslagen, geordend zijn. Heel vaak wordt een index vergeleken met een bibliotheekcatalogus, waarin alle boeken op kaarten zijn vastgelegd en op een bepaalde manier zijn geordend: op alfabet of op onderwerp, en elke kaart bevat de informatie waar precies het betreffende boek in de opslag is toegewezen.

Waarom hebben we indices nodig?

Het enige waar indices voor zorgen, is het versnellen van het ophalen van records op basis van het geïndexeerde veld (geïndexeerd - betekent opgenomen in de index). De belangrijkste functie van indices is het bieden van snel recordophalen in de tabel. Elk gebruik van een index komt hierop neer.

Hoe wordt deze ophaalfunctie gerealiseerd? Aan de invoer van deze functie hebben we de waarde van het geïndexeerde veld (of meerdere velden). Als resultaat van het ophalen moeten we het volledige record ontvangen, waarin het geïndexeerde veld een vooraf ingestelde waarde heeft. Eerst wordt in de index (om preciezer te zijn, in de geordende reeks van waarden van het geïndexeerde veld) de vereiste waarde gezocht, vervolgens wordt het adres van de datapagina genomen waar het vereiste record zich bevindt, gaat de server naar deze pagina en leest het gevonden record. Het lijkt nogal omslachtig, maar zoeken met behulp van een index is vele malen sneller dan het opeenvolgend doorlopen van alle waarden uit de tabel.

Als we de analogie tussen index en bibliotheekcatalogus voortzetten, zien we dat het ophalen van records met behulp van een index sterk lijkt op het zoeken van een boek met behulp van een kaart. Wanneer we een boek vinden in een vrij kleine catalogus (in vergelijking met de hele bibliotheekopslag), ontvangen we onmiddellijk de informatie over waar precies het boek is opgeslagen en kunnen we er direct naartoe. Zoeken zonder index kan worden vergeleken met het opeenvolgend doorlopen van alle boeken in de bibliotheek!

Het doorlopen van alle records in de tabel wordt direct of natuurlijk genoemd. We moeten zeggen dat, ondanks de kracht van moderne computers, natuurlijk doorlopen erg lang kan duren als de tabel een groot aantal records bevat.

Hoe zijn ze georganiseerd?

Een index is geen onderdeel van de tabel, het is een afzonderlijk object dat is verbonden met de tabel en andere databaseobjecten. Dit is een zeer belangrijk punt van DBMS-implementatie dat het mogelijk maakt om informatieopslag te scheiden van de weergave ervan.

InterBase slaat, net als elke andere relationele database, records in tabellen op in ongeordende volgorde, d.w.z. het maakt zich helemaal niet druk over hoe records fysiek in de tabel zijn toegewezen. Ongeordende opslag betekent dat twee records die achter elkaar aan de tabel zijn toegevoegd, niet naast elkaar hoeven te staan. Bovendien hebben de gegevens die uit de tabel worden geëxtraheerd ook geen volgorde, behalve de volgorde die expliciet moet worden gespecificeerd door de gebruiker die een ophaalquery maakt.

We kunnen echter niet zonder het ordenen van de opgeslagen gegevens: eindgebruikers van applicaties willen de gegevens in een bepaalde volgorde zien - bijvoorbeeld achternamen van mensen op alfabet. Indices lossen het probleem van gegevensweergave in geordende vorm op. Veldwaarden die in de index zijn opgenomen, worden geordend en weergegeven in een speciale weergave, geoptimaliseerd voor het zoeken naar de vereiste waarden (dit is namelijk essentieel voor het maken van geordende reeksen).

Het scheiden van gegevensopslag van hun weergave biedt extra voordelen in vergelijking met direct sorteren - misschien moet u de oorspronkelijke tabel op verschillende manieren sorteren. Dan helpen indices u - er kunnen tot 64 indices voor elke tabel zijn!

Als we spreken over de implementatie van indices op fysiek niveau, vertegenwoordigen ze een binaire boom waarvan de knopen paren “veldwaarde in index” - “gegevenslocatie in de tabel” vertegenwoordigen. Het ophalen van het vereiste record in de index wordt uitgevoerd met behulp van het mechanisme van hash-zoeken - een van de snelste zoekalgoritmen.

Index toepassing

Nu het duidelijk is wat we van indices kunnen eisen, is het tijd om te weten over hun functie in een database. Indices worden in drie hoofdgevallen gebruikt:

  1. Versnelling van de uitvoering van queries. Indices worden gemaakt voor de velden die worden gebruikt onder de zoekvoorwaarden van SQL-queries.

  2. Ondersteuning van uniciteit van waarden in velden; een primaire sleutelbeperking (waarover werd verteld in het hoofdstuk “Tabellen. Primaire sleutels”) vereist dat er in de tabel geen twee identieke waarden van de velden zijn die in een primaire sleutel zijn opgenomen. Om aan deze voorwaarde te voldoen, moet u bij het invoegen van een nieuw record zoeken naar dezelfde waarde die zal worden ingevoegd. Voor het ophalen van records wordt de speciale variant van index gebruikt - een unieke index (zie hieronder).

  3. Ondersteuning van referentiële integriteit. Beperkingen van externe sleutels (die worden behandeld in het hoofdstuk “Databasebeperkingen”) worden gebruikt om te controleren dat de waarden die in de tabel worden ingevoegd, noodzakelijkerwijs in een andere tabel bestaan. Bij het maken van een externe sleutel wordt automatisch een index gemaakt. Deze index wordt toegepast voor het versnellen van queries die gebruikmaken van het samenvoegen van tabellen, evenals voor het controleren van de voorwaarden van de externe sleutel. We hebben kort alle mogelijke indextoepassingen behandeld. Nu zullen we de bijzonderheden van elk geval in meer detail bekijken en we zullen de meest voorkomende vragen over indextoepassing beantwoorden.

Versnelling van de uitvoering van queries met behulp van indices

Hierboven is beschreven dat toepassing van indices de uitvoering van queries sterk kan versnellen. Dit is voor de meeste gevallen echt zo, maar er zijn bepaalde kanttekeningen. Eerst beantwoorden we de vraag die vaak opkomt bij degenen die kennis hebben gemaakt met indices. Als indices het ophalen uit een database versnellen, waarom zouden we dan niet alle velden in de tabel indexeren? Er zijn twee momenten die algemene indexering blokkeren - schijfruimte en kosten bij het wijzigen van de gegevens in de tabel. Elke gemaakte index heeft een grootte die gelijk is aan de gegevensgrootte in het geïndexeerde veld, plus de gegevensgrootte van de recordlocaties. Als we indices maken voor elk veld in de tabel, zal hun totale grootte meer zijn dan de gegevensgrootte in de tabel! Daarom leidt het maken van een groot aantal indices tot een enorme uitgave van schijfruimte.

Het tweede moment is belangrijker. Dit zijn de kosten bij het wijzigen van de gegevens in de tabel. In een relationeel DBMS zijn records in tabellen, zoals u weet, ongeordend en daarom gaan het toevoegen/verwijderen van records zonder significante kosten van serverbronnen. Zelfs als een record uit het midden van een database wordt verwijderd, is er geen verplaatsing van gegevenshoeveelheden om deze leegte op te vullen - dat is niet nodig: de server markeert eenvoudigweg de lege plaats en schrijft daar iets wanneer nodig. Wat betreft toevoeging, in de meeste gevallen wordt dit aan het einde van de tabel uitgevoerd. Echter, hoewel de server de hoofdgegevens in de tabel niet verplaatst bij wijziging, worden de gegevens die in indices zijn opgeslagen, elke keer opnieuw geordend bij het toevoegen/verwijderen van records! Met andere woorden, de server moet de index herbouwen bij het toevoegen van een record aan het midden van de tabel. Zeker, indeximplementatie is op de een of andere manier bedoeld voor frequente reorganisaties, maar deze bewerkingen kosten toch tijd en processorbronnen en wanneer er een groot aantal indices in de tabel is, kan gegevenswijziging daarin veel langzamer zijn dan in dezelfde tabel zonder indices!

Dit zijn twee hoofdredenen die algemene indexering belemmeren. Daarnaast zijn er nog enkele opmerkingen die indextoepassing beperken. De eerste is een regel van 20%. Deze zegt dat als de ophaalquery meer dan 20% van de records uit de tabel retourneert, indexgebruik het ophalen van gegevens kan vertragen! Zeker, de situatie hangt af van een concrete query en de voorwaarden die aan het ophalen zijn gesteld, maar we moeten onthouden dat 20% van de records een drempel is waarop de efficiëntie van indexgebruik twijfelachtig wordt. De tweede opmerking is niet zo duidelijk geformuleerd. Deze is verbonden met het werk van de InterBase optimizer.

De optimizer is een verzameling mechanismen die het schema voor het uitvoeren van de query ontwikkelen. Wanneer de gebruiker een SQL-query aan InterBase geeft, specificeert hij wat de server moet retourneren na het uitvoeren van de query, maar definieert niet HOE de server de query moet uitvoeren. De optimizer maakt op basis van de gegeven query het schema voor de uitvoering ervan, d.w.z. van waar en in welke volgorde de gegevens voor het uitvoeren van de query zullen worden genomen, welke indices daarbij zullen worden gebruikt. Wanneer de server de ophaalvoorwaarden analyseert (dit zijn voornamelijk delen van expressies WHERE, ORDER BY, enz.) voor elk veld dat in de voorwaarde is opgenomen, probeert de server index te gebruiken. Helaas is het algoritme voor het maken van het schema onvolledig en gebruikt de optimizer vaak indices die niet erg effectief zijn voor de concrete query, waardoor de uitvoeringstijd aanzienlijk kan worden vertraagd. Daarom kan het maken van onnodige indices leiden tot het maken van niet-optimale schema’s.

Er moet worden opgemerkt dat in de Yaffil-kloon dit probleem is opgelost door het gebruik van moderne algoritmen voor het maken van schema’s. Het derde geval waarin een index niet nodig is, zijn velden met een beperkte set waarden - bijvoorbeeld het veld dat de informatie over het geslacht van de persoon opslaat en slechts twee mogelijke waarden bevat - “F” en “M”; het heeft geen zin om dit veld te indexeren. Dus we hebben de belangrijkste beperkingen voor het maken van indices behandeld. Nu moeten we het probleem behandelen, wanneer het nodig is om indices te gebruiken om verbetering van productiviteit te bereiken. Er zijn 3 hoofdgevallen waarin een veld moet worden geïndexeerd:

  • Wanneer dit veld wordt gebruikt onder de ophaalvoorwaarden in queries
  • Wanneer samenvoegingen van tabellen dit veld gebruiken
  • Wanneer dit veld wordt gebruikt in de sorteerverklaring ORDER BY Als het veld op de hierboven genoemde manier wordt toegepast, kan het maken van de index ervoor leiden tot verbetering van de queryproductiviteit.

Laten we een syntaxis van het maken van indices bekijken. Hier is een volledige indeling van het DDL-commando dat het mogelijk maakt om indices te maken:

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index ON table (col [, col …]);

De minimale expressie die de index maakt, is de volgende:

CREATE INDEX my_index ON Table_example(ID)

In dit voorbeeld wordt index met naam my_index gemaakt voor tabel Table_example, en veld ID is het geïndexeerde veld. De index is oplopend, d.w.z. waarden erin zijn geordend door oplopend, evenals niet-uniek, en het betekent dat veld ID verschillende identieke waarden kan hebben. Het is zeker het eenvoudigste voorbeeld van een index - de meest voorkomende. Zoals we uit de beschrijving van de syntaxis kunnen zien, kan een index niet één, maar een paar velden bevatten. Zo’n index wordt gebruikt wanneer queries vaak worden uitgevoerd en een combinatie van geïndexeerde velden bevatten onder de zoek- of sorteervoorwaarden. Bijvoorbeeld, als we een tabel hebben met velden Achternaam, Voornaam, Patroniem, wordt zo’n index toegepast bij het maken van de query die sortering gebruikt op Achternaam, Voornaam en Patroniem. In het algemeen is het niet nodig om de voorwaarden voor alle 3 velden die in de index zijn toegepast te specificeren om de voordelen ervan te gebruiken. Als we het resultaat van de query willen sorteren, wordt de index gebruikt in het geval het eerste veld in een sorteervoorwaarde samenvalt met het eerste veld in de index. Bijvoorbeeld, onze index wordt toegepast in het geval van sortering op Achternaam en Voornaam.

Volgens documentatie voor optimalisatie van query-uitvoering die in de verklaring WHERE een samenvoeging van velden met OR-voorwaarde bevat, moeten we niet de samengevoegde index gebruiken, maar een paar enkele indices voor alle velden die in de OR-voorwaarde zijn opgenomen.

Wat betreft de vraag over de sorteervolgorde van indexen, deze kan oplopend of aflopend zijn. Waarom hebben we verschillende sorteervolgordes nodig? Uiteraard, voor verschillende sorteerbewerkingen! Als we mensen willen sorteren op achternaam in oplopende volgorde, maken we een oplopende index (ASC), en als we aflopend willen sorteren (van Z tot A) - dan aflopend! Als we beide willen, moeten we beide indexen aanmaken.

Ondersteuning van referentiële integriteit met behulp van indexen

Er is nog een optie in de indexdefinitie - UNIQUE. Als we dit specificeren, staat de index alleen unieke waarden toe om in de tabel te worden ingevoegd. Eigenlijk is dit de basis voor de implementatie van unieke sleutels. Unieke sleutels worden veel gebruikt in databases. Dat wil zeggen dat РК een unieke sleutelindex is, maar niet elke UK is РК. We hebben hierboven alleen over РК gesproken. Een primaire sleutel is het meest gebruikte type van een unieke sleutel. Bij het aanmaken van een primaire sleutel voor de tabel wordt automatisch een unieke index aangemaakt. Deze krijgt een naam die is samengesteld uit RDB$PRIMARYNNN, waarbij NNN een opeenvolgend uniek nummer binnen een database is. Zo worden twee belangrijke beperkingen van referentiële integriteit - een unieke sleutel en een primaire sleutel - gerealiseerd door het gebruik van een unieke index. Het is duidelijk dat het begrip uniciteit onverenigbaar is met het begrip ongedefinieerde waarde. Met andere woorden, er mogen geen NULL-waarden voorkomen in de velden die in unieke indexen zijn opgenomen. Voordat u een unieke index voor een veld aanmaakt, is het noodzakelijk om een NOT NULL-beperking in te stellen. Als de index wordt aangemaakt voor gegevens die al bestaan, wordt bij het aanmaken het geïndexeerde veld gecontroleerd op herhalende waarden. Als die aanwezig zijn, wordt het aanmaken van de index verboden.

Naast unieke en primaire sleutelbeperkingen ligt het mechanisme van indexen ten grondslag aan de implementatie van nog een beperking van referentiële integriteit - een externe sleutel. De externe sleutelbeperking wordt ingesteld voor een of meerdere velden van elke tabel en voorkomt dat waarden in deze velden worden ingevoegd die niet zijn opgenomen in de primaire sleutel van de andere, bovenliggende tabel. Voor de implementatie van de externe sleutel, d.w.z. voor het uitvoeren van de controle of er een waarde in de bovenliggende tabel bestaat, wordt automatisch een speciale index aangemaakt. De naam ervan is RDB$FOREIGNNN, waarbij NNN een opeenvolgend uniek nummer binnen een database is.

Waarom wordt het mechanisme van indexen gebruikt voor de implementatie van de beperkingen van referentiële integriteit? De zaak is dat indexen in InterBase een speciale, bevoorrechte positie hebben - er wordt gezegd dat ze buiten de context van transacties worden uitgevoerd. Dit is een zeer belangrijke eigenschap. We zullen later over transacties spreken, in het hoofdstuk dat daaraan is gewijd. Nu vermelden we alleen dat wanneer indexen buiten transacties vallen, dit betekent dat alle gebruikers die gelijktijdig met de gegevens in dezelfde tabel werken, de beperkingen van referentiële integriteit moeten naleven.

Optimalisatie van indexproductiviteit

In de titel van dit deel kunnen we een paradox ontdekken - indexen, zoals hierboven gezegd, dienen om de uitvoering van query’s te versnellen, en het blijkt dat ze ook geoptimaliseerd moeten worden! Maar wat te doen (zo is het leven) - iemand moet voor indexen zorgen. Wat gebeurt er met indexen? Waarom “verliezen ze hun vorm”? We moeten nogmaals zeggen dat indexen zijn geïmplementeerd als een binaire boom. En wanneer een nieuw record wordt toegevoegd (bijgewerkt, verwijderd - zoals u wilt) aan de tabel, wordt een nieuwe tak aan de boom toegevoegd. Deze takken worden niet in het midden van de boom toegevoegd, maar aan de toppen van andere takken. Geleidelijk wordt de boom steeds vertakter (of ongebalanceerd), en het zoeken - minder effectief. Het herbouwen van de boom of (in sommige gevallen) het herberekenen van statistieken kan de situatie verbeteren.

Periodiek is het vereist om de index opnieuw aan te maken om de productiviteit te herstellen. Indexhercreatie gebeurt in de volgende gevallen:

  • Bij het herbouwen van de index met behulp van de opdracht ALTER INDEX.
  • Bij het verwijderen en opnieuw aanmaken van de index met behulp van de opdrachten DROP INDEX en CREATE INDEX.
  • Bij het maken van een back-up en het herstellen vanuit een back-upkopie met behulp van de gbak-tool.

Ook kunt u herberekening van statistieken gebruiken. Maar u moet begrijpen dat deze bewerking de indexstatus niet verandert, het informeert alleen de optimizer over de nauwkeurige informatie over de status ervan, waardoor deze de index correct kan gebruiken. Met andere woorden, herberekening van statistieken is geen “genezing” van de index, maar alleen de nauwkeurige diagnose van de status ervan. Laten we al deze manieren van indexoptimalisatie in meer detail bekijken. Het gebruik van de opdracht ALTER INDEX heeft het volgende formaat:

ALTER INDEX naam {ACTIVE | INACTIVE};

Hier is naam de naam van de index, en ACTIVE en INACTIVE - twee statussen van de index waarnaar deze kan worden omgezet met behulp van de opdracht ALTER INDEX. Parameter ACTIVE betekent dat de index actief is en kan worden toegepast in alle query’s en procedures. Als u de index op INACTIVE instelt, resulteert dit in het uitschakelen van het gebruik ervan. Voor het herschikken van de boom moeten twee opdrachten opeenvolgend worden uitgevoerd:

ALTER INDEX naam INACTIVE; ALTER INDEX naam ACTIVE;

Zo wordt de index herbouwd. Het gebruik van ALTER INDEX heeft een aantal beperkingen: u kunt de indexen die worden gebruikt in primaire, unieke en externe sleutels niet herbouwen; u kunt de index niet herbouwen als deze op dit moment door een query wordt gebruikt; en ook voor het wijzigen van de index is het noodzakelijk om de rechten van beheerder (SYSDBA) te hebben of de maker van de betreffende index te zijn.

Hercreatie van de index met behulp van de opdrachten DROP INDEX en CREATE INDEX leidt tot een volledige verwijdering van de index uit een database, en vervolgens tot het aanmaken ervan vanaf een blanco afdruk. De syntaxis van de opdracht DROP INDEX is duidelijk:

DROP INDEX naam_van_index;

Na het verwijderen is het noodzakelijk om de index met dezelfde naam en parameters opnieuw aan te maken met behulp van de opdracht CREATE INDEX waarvan we de syntaxis al hebben bekeken. De manier om de index te herbouwen door volledige hercreatie heeft beperkingen die vergelijkbaar zijn met de beperkingen voor het gebruik van ALTER INDEX.

De derde manier om de index te herbouwen is gebaseerd op de eigenschap van back-upkopieën van InterBase-databases die zijn gemaakt met de gbak-hulpprogramma. De zaak is dat bij het maken van een back-up de gegevens die in de index zijn opgenomen niet in een back-upkopie worden opgeslagen, alleen de indexdefinitie wordt opgeslagen. Bij het herstellen vanuit een back-upkopie wordt de index opnieuw aangemaakt. Als u meer over de back-up wilt weten, zie het hoofdstuk “Back-up maken en herstellen vanuit een back-upkopie” (deel 4).

De vierde manier om de indexproductiviteit te verbeteren is het verzamelen van statistieken over indexen met behulp van de opdracht SET STATISTICS. Tabelstatistieken is een waarde binnen het bereik van 0 tot 1 waarvan de waarde afhangt van het aantal verschillende records in de tabel. De InterBase-optimizer gebruikt statistieken om de efficiëntie van de toepassing van deze of gene index in een query te bepalen. Wanneer het aantal records in de tabel sterk kan veranderen (bijvoorbeeld vanwege een groot aantal invoegingen of verwijderingen), kan herberekening van statistieken de productiviteit aanzienlijk verbeteren. De opdracht voor herberekening van statistieken is als volgt:

SET STATISTICS INDEX naam;

Hier is naam een naam van de index waarvoor statistieken worden herberekend. Herberekening van statistieken herbouwt de index niet en is daarom vrij van de meeste beperkingen die zijn ingesteld voor de hierboven beschreven manieren om de productiviteit te verbeteren, behalve dat alleen de maker van de index of de systeembeheerder (de gebruiker met de naam SYSDBA) statistieken kan herberekenen. Correcte statistieken stellen de optimizer in staat om een juiste beslissing te nemen over het al dan niet gebruiken van een index.

We hebben een aantal manieren bekeken om de productiviteit van indexen te verbeteren. Met behulp van de opdrachten ALTER INDEX en DROP/CREATE INDEX kunnen we elke index herbouwen, behalve de systeemindexen die automatisch zijn aangemaakt en bedoeld zijn voor het waarborgen van referentiële integriteit. Als u deze indexen wilt herbouwen, moet u de opdrachten voor het wijzigen en aanmaken van tabellen gebruiken - ALTER TABLE en CREATE TABLE, omdat deze indexen een integraal onderdeel zijn van tabelsleutels.