SQL
Indexes

Invoering

Opmerkingen

Index maken

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

Hiermee maakt u een index voor de kolom EmployeeId in de tabel Cars . Deze index verbetert de snelheid van zoekopdrachten waarin de server wordt gevraagd om te sorteren of te selecteren op waarden in EmployeeId , zoals:

SELECT * FROM Cars WHERE EmployeeId = 1

De index kan meer dan 1 kolom bevatten, zoals hieronder;

CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);

In dit geval zou de index nuttig zijn voor query's die vragen om te sorteren of te selecteren op alle opgenomen kolommen, als de set voorwaarden op dezelfde manier is geordend. Dat betekent dat het bij het ophalen van de gegevens de rijen kan vinden om op te halen met behulp van de index, in plaats van door de volledige tabel te kijken.

In het volgende geval wordt bijvoorbeeld de tweede index gebruikt;

SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC

Als de volgorde echter verschilt, heeft de index niet dezelfde voordelen als hieronder;

SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC

De index is niet zo nuttig omdat de database de hele index moet ophalen, over alle waarden van EmployeeId en CarID, om te bepalen welke items OwnerId = 17 .

(De index kan nog steeds worden gebruikt; het kan zijn dat de OwnerId vindt dat het ophalen van de index en het filteren op de OwnerId en vervolgens alleen de benodigde rijen ophalen sneller is dan het ophalen van de volledige tabel, vooral als de tabel groot is.)

Geclusterde, unieke en gesorteerde indexen

Indexen kunnen verschillende kenmerken hebben die kunnen worden ingesteld bij het maken of door bestaande indexen te wijzigen.

CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email);  

De bovenstaande SQL-instructie maakt een nieuwe geclusterde index voor werknemers. Geclusterde indexen zijn indexen die de feitelijke structuur van de tabel bepalen; de tabel zelf is gesorteerd om overeen te komen met de structuur van de index. Dat betekent dat er maximaal één geclusterde index op een tabel kan staan. Als er al een geclusterde index in de tabel bestaat, mislukt de bovenstaande instructie. (Tabellen zonder geclusterde indexen worden ook heaps genoemd.)

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

Hiermee maakt u een unieke index voor de kolom E-mail in de tabel Klanten . Deze index, samen met het versnellen van zoekopdrachten zoals een normale index, zorgt er ook voor dat elk e-mailadres in die kolom uniek is. Als een rij wordt ingevoegd of bijgewerkt met een niet-unieke e- mailwaarde, mislukt het invoegen of bijwerken standaard.

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

Dit creëert een index voor klanten die ook een tabelbeperking creëert dat de EmployeeID uniek moet zijn. (Dit zal mislukken als de kolom momenteel niet uniek is - in dit geval als er werknemers zijn die een ID delen.)

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

Hiermee maakt u een index die in aflopende volgorde wordt gesorteerd. Standaard zijn de indexen (althans in MSSQL-server) oplopend, maar dat kan worden gewijzigd.

Invoegen met een unieke index

UPDATE Customers SET Email = "[email protected]" WHERE id = 1;

Dit mislukt als er een unieke index is ingesteld in de kolom E-mail van klanten . Alternatief gedrag kan echter voor dit geval worden gedefinieerd:

UPDATE Customers SET Email = "[email protected]" WHERE id = 1 ON DUPLICATE KEY; 

SAP ASE: Drop index

Met deze opdracht wordt de index in de tabel verwijderd. Het werkt op SAP ASE server.

Syntaxis:

DROP INDEX [table name].[index name]

Voorbeeld:

DROP INDEX Cars.index_1

Gesorteerde index

Als u een index gebruikt die is gesorteerd op de manier waarop u deze zou ophalen, zou de SELECT instructie geen extra sortering uitvoeren tijdens het ophalen.

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

Wanneer u de query uitvoert

SELECT * FROM scoreboard ORDER BY score DESC;

Het databasesysteem zou geen extra sortering uitvoeren, omdat het een index-lookup in die volgorde kan uitvoeren.

Een index neerzetten of uitschakelen en opnieuw opbouwen

DROP INDEX ix_cars_employee_id ON Cars;  

We kunnen het commando DROP om onze index te verwijderen. In dit voorbeeld zullen we DROP de index genoemd ix_cars_employee_id op tafel Cars.

Hiermee wordt de index volledig verwijderd, en als de index geclusterd is, wordt eventuele clustering verwijderd. Het kan niet worden herbouwd zonder de index opnieuw te maken, die traag en rekenkundig duur kan zijn. Als alternatief kan de index worden uitgeschakeld:

ALTER INDEX ix_cars_employee_id ON Cars DISABLE; 

Hierdoor kan de tabel de structuur behouden, samen met de metagegevens over de index.

Kritiek, dit behoudt de indexstatistieken, zodat het mogelijk is om de verandering gemakkelijk te evalueren. Indien gerechtvaardigd, kan de index later opnieuw worden opgebouwd in plaats van volledig opnieuw te worden gemaakt;

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

Unieke index die NULLS toestaat

CREATE UNIQUE INDEX idx_license_id 
   ON Person(DrivingLicenseID) WHERE DrivingLicenseID IS NOT NULL
GO

Dit schema maakt een 0..1-relatie mogelijk - mensen kunnen nul of één rijbewijs hebben en elk rijbewijs kan maar tot één persoon behoren

Index opnieuw opbouwen

In de loop van de tijd kunnen B-Tree-indexen gefragmenteerd raken door het bijwerken / verwijderen / invoegen van gegevens. In SQLServer-terminologie kunnen we een interne (indexpagina hebben die half leeg is) en extern (logische paginavolgorde komt niet overeen met fysieke volgorde). Het opnieuw opbouwen van de index lijkt erg op het verwijderen en opnieuw maken van de index.

We kunnen een index opnieuw maken met

ALTER INDEX index_name REBUILD; 

Standaard is het opnieuw opbouwen van de index een offline bewerking die de tabel vergrendelt en DML daartegen voorkomt, maar veel RDBMS maken online opnieuw bouwen mogelijk. Sommige DB-leveranciers bieden ook alternatieven voor het opnieuw opbouwen van de index, zoals REORGANIZE (SQLServer) of COALESCE / SHRINK SPACE (Oracle).

Geclusterde index

Wanneer u geclusterde index gebruikt, worden de rijen van de tabel gesorteerd op de kolom waarop de geclusterde index wordt toegepast. Daarom kan er slechts één geclusterde index op de tabel staan, omdat u de tabel niet in twee verschillende kolommen kunt rangschikken.

Over het algemeen is het het beste om geclusterde index te gebruiken bij het uitvoeren van reads op big data-tabellen. De keerzijde van geclusterde index is wanneer naar tafel wordt geschreven en gegevens moeten worden gereorganiseerd (gereviseerd).

Een voorbeeld van het maken van een geclusterde index op een tabel Medewerkers op kolom Employee_S achternaam:

CREATE CLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Niet geclusterde index

Niet-geclusterde indexen worden afzonderlijk van de tabel opgeslagen. Elke index in deze structuur bevat een aanwijzer naar de rij in de tabel die deze vertegenwoordigt.

Deze verwijzingen worden rijzoekers genoemd. De structuur van de rijzoeker is afhankelijk van of de gegevenspagina's zijn opgeslagen in een heap of een geclusterde tabel. Voor een heap is een rijzoeker een aanwijzer naar de rij. Voor een geclusterde tabel is de rijzoeker de geclusterde indexsleutel.

Een voorbeeld van het maken van een niet-geclusterde index op tafel Werknemers en kolom Employee_S achternaam:

CREATE NONCLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Er kunnen meerdere niet-geclusterde indexen op de tabel staan. De leesbewerkingen zijn over het algemeen langzamer met niet-geclusterde indexen dan met geclusterde indexen omdat u eerst naar de index moet gaan en dan naar de tabel. Er zijn echter geen beperkingen in schrijfbewerkingen.

Gedeeltelijke of gefilterde index

Met SQL Server en SQLite kunnen indexen worden gemaakt die niet alleen een subset van kolommen, maar ook een subset van rijen bevatten.

Beschouw een constant groeiend aantal bestellingen met order_state_id gelijk aan afgewerkt (2), en een stabiel aantal bestellingen met order_state_id equal aan gestart (1).

Als uw bedrijf dergelijke vragen gebruikt:

SELECT id, comment
  FROM orders
 WHERE order_state_id =  1
   AND product_id = @some_value;

Met gedeeltelijke indexering kunt u de index beperken, inclusief alleen de onafgemaakte orders:

CREATE INDEX Started_Orders
          ON orders(product_id)
       WHERE order_state_id = 1;

Deze index is kleiner dan een ongefilterde index, wat ruimte bespaart en de kosten voor het bijwerken van de index vermindert.