SQL
index

Introduktion

Anmärkningar

Skapa ett index

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

Detta skapar ett index för kolumnen EmployeeId i tabellen Cars . Detta index kommer att förbättra hastigheten på frågor som ber servern att sortera eller välja efter värden i EmployeeId , till exempel följande:

SELECT * FROM Cars WHERE EmployeeId = 1

Indexet kan innehålla mer än 1 kolumn, som i följande;

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

I det här fallet skulle indexet vara användbart för frågor som ber om att sortera eller välja efter alla medföljande kolumner, om uppsättningen av villkor är ordnade på samma sätt. Det betyder att när man hämtar data kan den hitta raderna att hämta med indexet istället för att titta igenom hela tabellen.

Till exempel skulle följande fall använda det andra indexet;

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

Om ordningen skiljer sig har indexet emellertid inte samma fördelar som i följande;

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

Indexet är inte lika användbart eftersom databasen måste hämta hela indexet, över alla värden för EmployeeId och CarID, för att hitta vilka objekt som har OwnerId = 17 .

(Indexet kan fortfarande användas; det kan vara så att sökfrågeoptimeraren finner att hämtning av indexet och filtrering på OwnerId , då att hämta endast de rader som krävs är snabbare än att hämta hela tabellen, särskilt om tabellen är stor.)

Clustered, Unique and Sorted Index

Index kan ha flera egenskaper som kan ställas in antingen vid skapandet eller genom att ändra befintliga index.

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

Ovanstående SQL-uttalande skapar ett nytt klusterindex för anställda. Clustered index är index som dikterar tabellens faktiska struktur; själva tabellen sorteras för att matcha strukturen i indexet. Det betyder att det kan finnas högst ett klusterindex på ett bord. Om ett klusterindex redan finns på tabellen, kommer uttalandet ovan att misslyckas. (Tabeller utan klusterindex kallas också högar.)

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

Detta skapar ett unikt index för kolumnen E-post i tabellen Kunder . Detta index, tillsammans med att påskynda frågor som ett normalt index, kommer också att tvinga varje e-postadress i den kolumnen att vara unik. Om en rad infogas eller uppdateras med ett icke-unikt e- postvärde, kommer insättningen eller uppdateringen som standard att misslyckas.

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

Detta skapar ett index för kunder som också skapar en tabellbegränsning att EmployeeID måste vara unik. (Detta kommer att misslyckas om kolumnen för närvarande inte är unik - i detta fall om det finns anställda som delar ett ID.)

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

Detta skapar ett index som sorteras i fallande ordning. Som standard stiger index (i MSSQL-server, åtminstone) upp, men det kan ändras.

Infoga med ett unikt index

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

Detta misslyckas om ett unikt index är inställt i kundens e- postkolumn . Alternativt beteende kan dock definieras för detta fall:

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

SAP ASE: Drop index

Detta kommando släpper index i tabellen. Det fungerar på SAP ASE server.

Syntax:

DROP INDEX [table name].[index name]

Exempel:

DROP INDEX Cars.index_1

Sorterat index

Om du använder ett index som är sorterat på samma sätt som du skulle hämta det, kommer SELECT uttalandet inte att göra ytterligare sortering när det hämtas.

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

När du kör frågan

SELECT * FROM scoreboard ORDER BY score DESC;

Databassystemet skulle inte göra ytterligare sortering, eftersom det kan göra en indexuppslag i den ordningen.

Släpp ett index, eller inaktivera och återuppbygga det

DROP INDEX ix_cars_employee_id ON Cars;  

Vi kan använda kommandot DROP att ta bort vårt index. I det här exemplet kommer vi att DROP indexet som heter ix_cars_employee_id på bordet Cars .

Detta raderar indexet helt och om indexet är klustert kommer alla klustering att tas bort. Det kan inte byggas om utan att återskapa indexet, vilket kan vara långsamt och beräkningsbart dyrt. Som ett alternativ kan indexet inaktiveras:

ALTER INDEX ix_cars_employee_id ON Cars DISABLE; 

Detta gör att tabellen kan behålla strukturen tillsammans med metadata om indexet.

Kritiskt behåller detta indexstatistiken så att det är möjligt att enkelt utvärdera förändringen. Om det är motiverat kan indexet senare byggas om istället för att återskapas helt;

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

Unikt index som tillåter NULLS

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

Detta schema möjliggör en 0..1-relation - människor kan ha noll- eller ett körkort och varje licens kan bara tillhöra en person

Återställ index

Under tiden kan B-Tree-index bli fragmenterade på grund av uppdatering / radering / infoga data. I SQLServer-terminologin kan vi ha intern (indexsida som är halvtom) och extern (logisk sidordning motsvarar inte fysisk ordning). Ombyggnadsindex liknar mycket att släppa och skapa det igen.

Vi kan bygga ett index igen med

ALTER INDEX index_name REBUILD; 

Som standard är ombyggnadsindex offline operation som låser tabellen och förhindrar DML mot det, men många RDBMS tillåter ombyggnad online. Vissa DB-leverantörer erbjuder också alternativ till ombyggnad av index, till exempel REORGANIZE (SQLServer) eller COALESCE / SHRINK SPACE (Oracle).

Clustered index

När du använder klusterindex sorteras raderna i tabellen efter kolumnen till vilken det klusterade indexet tillämpas. Därför kan det bara finnas ett klusterindex på tabellen eftersom du inte kan beställa tabellen efter två olika kolumner.

Generellt sett är det bäst att använda klusterindex när du utför avläsningar i stora datatabeller. Bredden av klusterindex är när man skriver till tabell och data måste omorganiseras (utväg).

Ett exempel på att skapa ett klusterindex på en tabell Anställda i kolumnen Medarbetarnamn:

CREATE CLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Icke klusterindex

Icke-slutna index lagras separat från tabellen. Varje index i denna struktur innehåller en pekare till raden i tabellen som den representerar.

Dessa pekare kallas rad-locatorer. Strukturen för radlokaliseraren beror på om datasidorna är lagrade i en hög eller en gruppad tabell. För en hög är en radläsare en pekare till raden. För en grupperad tabell är radlokatorn den klusterade indexnyckeln.

Ett exempel på att skapa ett icke-klusterindex på tabellen Anställda och kolumnen Medarbetarnamn:

CREATE NONCLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Det kan finnas flera icke-slutna index på tabellen. Läsoperationerna är i allmänhet långsammare med icke-klusterade index än med klusterindex eftersom du måste gå först till index och än till tabellen. Det finns dock inga begränsningar i skrivverksamheten.

Partiellt eller filtrerat index

SQL Server och SQLite tillåter att skapa index som inte bara innehåller en delmängd av kolumner, utan också en delmängd av rader.

Tänk på ett konstant växande antal order med order_state_id lika med färdig (2), och ett stabilt antal order med order_state_id equal med startat (1).

Om ditt företag använder sådana frågor:

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

Partiell indexering låter dig begränsa indexet, inklusive endast oavslutade order:

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

Detta index kommer att vara mindre än ett ofiltrerat index, vilket sparar utrymme och minskar kostnaderna för att uppdatera indexet.