Indexering av databaser (skapa index för snabbare sökning)

https://en.wikipedia.org/wiki/Index_(database)

Jag är inte så bra på engelska och det fanns ingen svensk sida på Wikipedia. Finns det någon där ute som kan sammanfatta den där artikeln på ett enkelt sätt och förklara för mig vad ett index är i en databas?

Om jag har en databas som är X GB stor, med miljoner rader data, kommer jag att kunna söka mycket snabbare om jag skapar ett index för databasen?

Hur ser det här indexet ut i databasen? Hur gör man för att skapa indexet?

Påverkas storleken av databasen av att man skapar ett index?

Hur mycket snabbare blir en sökning i databasen med ett index?

// user3815276

Dababasen jag använder är strukturerad enligt följande:
Exampel på sökning:
Kan en sökning som den där göras snabbare genom att skapa ett index för databasen?

Hur gör man?

Det jag fick lära mig i en kurs inom IT på universitetet så var index i filsystem-sammanhang ett register. Det vill säga, istället för att du söker på "kukneger" och söker igenom databasen i sin helhet, så ska ett index hjälpa till att visa vart "kukneger" finns, så sökningen går snabbare.

Var dock ett antal år sedan jag läste om det där och jag är noll intresserad så inte fan vet jag. Är full som en jävla åsna så att skriva om IT är det sista jag borde göra. Ska hoppa i badet nu!

Okej, jag tror jag förstår, så ett index i en databas gör det alltså möjligt att söka igenom endast de relevanta delarna istället för all data?

Men hur sparar databasen den här information om vilken data som är relevant? Hur vet den var den ska söka?

Index på tabeller (eller snarare kolumner i tabeller) används mycket riktigt av query optimizern för att snabbare hitta data, man kan se det som en katalog över var data ska finnas. Denna katalog är betydligt mindre än datat i tabellen och därför kommer sökningar som görs mot index alltid att vara snabbare än en genomsökning av hela tabellen (table scan). Beroende på hur mycket data som finns i tabellen och hur detta är distribuerat kan sökningarna ske mer eller mindre fort, men är det fråga om miljoner rader är en faktor på 10 inga orimligheter på skillnaderna mellan en index scan och en table scan.

Beroende på vad för databas man använder kan man skapa index på olika sätt, ska man göra det med SQL är en bra början , sedan skiljer sig syntaxen åt beroende på RDBMS. I SQL server management studio finns det GUI-stöd för att skapa index på en eller flera kolumner i en tabell.

Det du har presenterat är inte en databas. det är möjligen en tabell i en databas. Det som är intressant att indexera i den där tabellen är ju ditt WHERE-villkor, dvs är en lämplig kandidat att indexera. Hur detta görs beror som sagt på vad för RDBMS du kör.

För att se skillnaderna på sökningar med eller utan index blir man tvungen att ta till andra verktyg, i sql server kan man exempelvis i management studio välja show actual execution plan (eller estimated execution plan) för att se hur query optimizern tänkt sig att exekvera frågan.

I mysql borde EXPLAIN framför själva frågan ge dig samma effekt, om än presenterat lite annorlunda. SQL server har ju även sql server profiler att ta till där man kan kolla exekveringstid etc för de frågor som körs i databasen.

Normalt sett skapas även ett automagiskt index på en tabell om man i densamma har en primärnyckel definierad.

Hittar inte query optimizern ett index den tror kan användas(eller om ett index påträffas som är irrelevant för sökningen, alternativt inte kommer hjälpa) kommer en tabellsökning ske istället, detsamma gäller om index helt och hållet saknas på tabellen.

Orkar inte förklara heuristiken bakom execution plans för dig, men index sparas på disk tillsammans med övrig statistik och metadata för tabellen. Uppdateras tabellen ofta kommer med andra ord indexet att bli utdaterat förr eller senare och ställa till mer trassel än nytta, av den anledningen brukar man ha nattliga jobb som bygger om samtliga index i databasen.

Instruktioner för att hitta data sparas alltså, men samma data förekommer inte på två olika platser i databasen? Det här borde väl inte påverka den sammanladga storleken av databasen allt för mycket?

Och det beror på att bok_forfattare är det som avgör om data är relevant? Snabb åtkomst till den här datan = snabbare sökning?

Men en sökning med gammalt index kommer ändå delvis att använda det tidigare indexet och därför vara snabbare än om man inte alls hade något index?

Finns det några nackdelar då med att skapa index för alla möjliga sökningar man kan göra i en databas?

Index brukar normalt sett vara betydligt mindre än vad själva datat i tabellen är ja. Tänk på att index endast innefattar en delmängd av datat i tabeller, rent logiskt säger det ju sig självt att ett index om 2 kolumner kommer ta betydligt mindre plats än en tabell med 8 kolumner, right? Den eventuella extraplats ett index tar i en databas vägs gott och väl upp av sökprestanda.

Det är ju knappast det du ska oroa dig för, börja istället räkna på timpriserna för väntan på söliga databasfrågor och väg dessa mot priset för extra diskutrymme för index så blir det uppenbart vad man ska satsa på, tror du inte det med?

Ja du sa det själv...

Att sätta index på tabeller är inte alldeles självklart utan det kräver normalt sett en lite bättre analys av vad för frågor som ställs mot databasen. I det här fallet är det ju författare du söker på och då är det ju givetvis det som ska indexeras.

I större frågor däremot måste man ta i beaktande vilka tabeller som joinas ihop och lämpligen indexera dessa kolumner i de bägge tabellerna, är det så att en tabell innehåller väldigt lite data och kommer så göra under överskådlig tid framöver kan man ju även överväga att inte indexera denna tabell alls, eftersom det ändå inte kommer tillföra något.

Index uppdateras delvis varje gång man skriver till en tabell. Det innebär att skrivoperationer kommer bli väldigt klumpiga att hantera för databasen om det sitter trehundra index på tabellen som måste uppdateras samtidigt.

Indexera med måtta med andra ord, att bara helt planlöst kasta på index på en tabell tillför sällan något mervärde utan kan ibland även vara ivägen.

Meningen är ju dessutom att indexet ska vara mindre än det faktiska tabelldatat, men indexerar man varenda kolumn blir ju indexet i stort sett lika stort som det faktiska tabelldatat, då har man ju inte direkt vunnit något, snarare skapat mer skrivoverhead om databasen måste uppdatera indexet vid varenda skrivning.

Du kan jämföra en databastabell med en telefonkatalog. Telefonkatalogen är sorterad så att namnen kommer i alfabetisk ordning. Har du ett namn och vill slå upp telefonnumret så går det snabbt att söka i katalogen eftersom du kan hoppa över sidor tills du kommer ungefär rätt. Har du däremot ett telefonnummer och vill hitta namnet, så innehåller visserligen telefonkatalogen all information du behöver, men för att hitta det du söker måste du läser varje sida uppifrån och ner tills du hittar rätt nummer. Sorteringsordningen är med andra ord kritisk för att telefonkatalogen skall vara användbar.

En databastabell är normalt sorterad i primärnyckelordning och det kommer att gå fort att göra en uppslagning om du vet primärnyckeln. Om du däremot vill slå upp en rad givet andra kolumnvärden så måste databasen göra en "full table scan", vilket är samma sak som att läsa telefonkatalogen sida för sida.

Ett index kan liknas vid ett supplementärt register som har en annan sorteringsordning. Om vi längst bak i telefonkatalogen lägger till ett register som är sorterar i nummerordning och bara innehåller nummer och namn, så kan vi använda detta register för att i ett första steg hitta rätt namn givet ett visst nummer och sedan i ett andra steg söka på namnet i katalogens huvuddel för att hitta adress och andra uppgifter som också är kopplade till numret. Vi behöver visserligen göra två uppslagningar, men det går ändå mycket fortare än att läsa hela katalogen sida för sida.

Ett index i en databas är normal implementerat som en egen tabell som är sorterad i annan ordning än huvudtabellen och som bara innehåller de kolumner som den är sorterad efter samt de kolumner som ingår i primärnyckeln. Som tidigare påpekats tillkommer en kostnad vid skrivningar eftersom varje index-tabell måste ändras varje gång som huvudtabellen ändras.

I en sql-databas är den konkreta implementationen dold för användaren, men det är ändå så här det fungerar under ytan.

Låter som samma princip hashtables bygger på. En enkel databas bygger endast på en kollektion, en indexerad databas skulle väl kunna sägas vara en lista med kollektioner.

collection[index]

Dock skulle den indexerade databasen bara vara referenser till den verkliga positionen i databasen inte en kopia av ursprunget. Vilket innebär att du kan sortera dina index hur du vill, och samma data kan hittas i olika index (register).