Databasernas grunder

vår 2026

7. Databasernas egenskaper

Dataintegritet

Dataintegritet innebär att datan i databasen är korrekt och konsekvent. Det primära ansvaret för datakvaliteten ligger hos användaren eller applikationen som ändrar databasen. Även den som designar databasen kan påverka datakvaliteten genom att lägga till villkor i tabellerna som övervakar den information som matas in i databasen.

Villkor för kolumner

När vi skapar en tabell kan vi lägga till villkor på kolumnerna som databashanteraren övervakar när data läggs till eller ändras. Dessa villkor kan begränsa vilken information som får matas in i databasen. Vanliga villkor är följande:

UNIQUE

Villkoret UNIQUE innebär att kolumnen måste ha olika värden på varje rad. Till exempel i följande tabell är kravet att varje produkt ska ha ett unikt namn:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE,
  price INTEGER
);

Villkoret UNIQUE kan också gälla flera kolumner, och då anges det separat efter kolumnerna:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER,
  UNIQUE(name, price)
);

Detta innebär att tabellen inte kan innehålla två rader med samma namn och samma pris.

NOT NULL och DEFAULT

Villkoret NOT NULL innebär att kolumnen inte får innehålla värdet NULL. Till exempel i följande tabell får produktens pris inte fattas:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER NOT NULL
);

DEFAULT ger kolumnen ett standardvärde om inget värde anges när en rad läggs till. Till exempel kan vi ange priset till standardvärdet 0 på följande sätt:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER DEFAULT 0
);

CHECK

Ett vanligt sätt att skapa ett villkor är att använda nyckelordet CHECK, följt av ett valfritt villkorsuttryck. Till exempel i följande tabell innebär villkoret price >= 0 att priset inte får vara negativt:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER,
  CHECK (price >= 0)
);

Övervakning av villkoren

Fördelen med villkor är att databashanteraren övervakar dem och inte tillåter användare att lägga till eller ändra data som bryter mot villkoren. Nedan är ett exempel på detta i SQLite:

sqlite> CREATE TABLE Products (id INTEGER PRIMARY KEY,
   ...>                        name TEXT,
   ...>                        price INTEGER,
   ...>                        CHECK (price >= 0));
sqlite> INSERT INTO Products(name, price) VALUES ('rädisa', 4);
sqlite> INSERT INTO Products(name, price) VALUES ('selleri', 7);
sqlite> INSERT INTO Products(name, price) VALUES ('rova', –2);
Error: CHECK constraint failed: Products
sqlite> SELECT * FROM Products;
1|rädisa|4
2|selleri|7
sqlite> UPDATE Products SET price = –2 WHERE id = 2;
Error: CHECK constraint failed: Products

I exemplet ovan försöker man lägga till en rad i tabellen Products där priset är negativt. Detta bryter mot villkoret price >= 0. SQLite tillåter således inte raden att läggas till, utan ger felet CHECK constraint failed: Products. Samma sak händer om vi senare försöker ändra priset i en redan befintlig rad till ett negativt värde.

Villkor i programmering

Villkor i tabeller kan man dra nytta av i programmering för att göra koden enklare och mer pålitlig.

Låt oss betrakta ett exempel där vi vill försäkra oss om att varje produkt som läggs till i tabellen har ett unikt namn. Ett sätt att göra detta i koden är att innan ett INSERT-kommando utföra ett SELECT-kommando som kontrollerar att det ännu inte finns någon produkt med samma namn i tabellen:

result = db.execute("SELECT 1 FROM Products WHERE name = ?", [name])
if result.fetchone() is None:
    db.execute("INSERT INTO Products (name, price) VALUES (?, ?)",
               [name, price])
    print("Produkten har lagts till i tabellen")
else:
    print("Produkten kunde inte läggas till eftersom den redan finns i tabellen")

Idén är att SELECT-frågan returnerar 1 om det redan finns en produkt med samma namn i tabellen, och inget resultat alls om produkten inte finns. På detta sätt kan man identifiera en situation där produkten redan har lagts till i tabellen.

Denna lösning är inte värst bra eftersom den är onödigt komplicerad och dessutom inte fungerar korrekt i alla situationer. Eftersom databasen kan ha flera användare är det möjligt att en annan användare lägger till en produkt i databasen mellan utförandet av SELECT- och INSERT-kommandot. I sådant fall förlitar sig koden på föråldrad information om tabellens innehåll och samma produkt finns då dubbelt i tabellen.

En bättre lösning är att använda villkoret UNIQUE på kolumnen name i tabellen och på så sätt försäkra sig om att tabellen inte kan innehålla två produkter med samma namn. Vi kan nu implementera hur en produkt läggs till i databasen på följande sätt:

try:
    db.execute("INSERT INTO Products (name, price) VALUES (?, ?)",
               [name, price])
    print("Produkten har lagts till i tabellen")
except sqlite3.IntegrityError:
    print("Produkten kunde inte läggas till eftersom den redan finns i tabellen")

I det här fallet misslyckas INSERT-kommandot om det redan finns en produkt med samma namn i tabellen. I Python-koden kan situationen identifieras genom att tilläggsförsöket ger felet sqlite3.IntegrityError. Koden kan alltså försöka lägga till produkten utan att först kontrollera om den redan finns i tabellen, och det eventuella felet som uppstår visar att produkten redan har lagts till tidigare.

Detta är klart en bättre lösning än att i koden kontrollera med en SELECT-fråga om produkten redan finns i tabellen, eftersom databasen med hjälp av villkoret UNIQUE på ett pålitligt sätt säkerställer detta. Själva koden blir nu också kortare.

Villkor för referenser

Villkor som gäller referenser i tabeller säkerställer att referenserna pekar på faktiska rader. Låt oss betrakta följande tabeller som exempel:

CREATE TABLE Teachers (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER
);

Tanken här är att kolumnen teacher_id i tabellen Courses ska referera till kolumnen id i tabellen Teachers. Databasens användare kan dock ange vilket värde som helst för kolumnen teacher_id (t.ex. siffran 123), vilket gör att databasens innehåll då blir felaktigt.

Detta kan förbättras genom att vid skapandet av tabellen Courses ange med REFERENCES att kolumnen teacher_id refererar till tabellen Teachers:

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER REFERENCES Teachers
);

Kolumnen teacher_id i tabellen Courses är en främmande nyckel, kallas också referensattribut (på engelska foreign key), som refererar till primärnyckeln id i tabellen Teachers. Det innebär att värdet i kolumnen teacher_id i tabellen Courses måste referera till en faktisk rad i tabellen Teachers.

Observera att SQLite av historiska skäl inte övervakar referenser som standard, utan vi måste först ange följande kommando:

sqlite> PRAGMA foreign_keys = ON;

Detta är en särskild egenskap i SQLite. Referenser övervakas alltid i andra databashanterare.

Nedan är ett exempel på hur referenser övervakas:

sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE Teachers (id INTEGER PRIMARY KEY, name TEXT);
sqlite> CREATE TABLE Courses (id INTEGER PRIMARY KEY, name TEXT,
   ...>                       teacher_id INTEGER REFERENCES Teachers);
sqlite> INSERT INTO Teachers (name) VALUES ('Kaila');
sqlite> INSERT INTO Teachers (name) VALUES ('Kivinen');
sqlite> SELECT * FROM Teachers;
1|Kaila
2|Kivinen
sqlite> INSERT INTO Courses (name, teacher_id)
   ...>             VALUES ('Beräkningsmodeller', 2);
sqlite> INSERT INTO Courses (name, teacher_id)
   ...>             VALUES ('PHP-programmering', 123);
Error: FOREIGN KEY constraint failed

Tabellen Teachers innehåller två lärare med ID-numren 1 och 2. När vi försöker lägga till en rad i tabellen Courses där teacher_id är 123 tillåter SQLite inte detta, utan vi får felmeddelandet FOREIGN KEY constraint failed.

Referenser och borttagningar

Villkor som gäller referenser kan vara mer komplicerade än vanliga kolumnvillkor, eftersom referenser kopplar samman två tabeller. Vad händer om man försöker ta bort en rad i en tabell som refereras i en annan tabell?

Standardinställningen i databashanterare är vanligtvis att en rad inte kan tas bort om den refereras någon annanstans. Om vi exempelvis i slutet av det tidigare exemplet försöker ta bort raden med ID 2 från tabellen Teachers kommer det inte att lyckas, eftersom raden refereras i tabellen Courses:

sqlite> DELETE FROM Teachers WHERE id = 2;
Error: FOREIGN KEY constraint failed

Om vi vill kan vi dock vid skapandet av tabellen specificera exakt vad som ska hända i en sådan situation. Ett alternativ är ON DELETE CASCADE, vilket innebär att när en rad tas bort raderas även de rader som refererar till den:

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER REFERENCES Teachers ON DELETE CASCADE
);

Om en lärare nu tas bort från databasen, tas automatiskt alla kurser som läraren undervisar i också bort. Denna lösning är dock inte nödvändigtvis den bästa, eftersom den kan leda till att data försvinner oväntat från databasens tabeller.

Möjliga alternativ i ON DELETE -delen är:

Det kan kännas förvirrande att standardalternativet NO ACTION förhindrar borttagning av en rad, även om namnet kan antyda något annat. Alternativen NO ACTION och RESTRICT fungerar i praktiken nästan på samma sätt, men beroende på databashanteraren kan det finnas skillnader i vissa speciella situationer.

Transaktioner

En transaktion är en serie efterföljande SQL-kommandon som databassystemet ämnar att köra som en enda helhet. Databasanvändaren kan lita på att antingen (1) alla kommandon körs och ändringarna sparas permanent i databasen, eller (2) transaktionen avbryts och inga av kommandona leder till några ändringar i databasen.

I samband med transaktioner nämns ofta ACID. Dessa bokstäver står för fyra egenskaper som transaktioner bör ha. Bokstäverna kommer från följande ord:

Transaktionens olika steg

I själva verket är en transaktion något som används hela tiden i databaser eftersom varje SQL-kommando som körs som standard utgör en egen transaktion. Låt oss titta på följande exempel där priset höjs med ett på varje produkt:

UPDATE Products SET price = price + 1;

Eftersom kommandot körs som en transaktion kan vi vara säkra på att priset antingen höjs med ett för samtliga produkter eller att inga priser ändras alls. Det senare kan till exempel inträffa om strömmen går mitt under uppdateringen. I så fall ändras inga priser, och det är alltså inte möjligt att endast vissa priser uppdateras.

Ofta syftar ordet transaktion i synnerhet på att helheten som körs består av flera SQL-kommandon. Vi börjar med kommandot BEGIN som startar transaktionen, varefter alla kommandon som ingår i transaktionen körs på vanligt sätt. Transaktionen avslutas med kommandot COMMIT.

Ett klassiskt exempel på en transaktion är när pengar överförs från ett bankkonto till ett annat. Till exempel överför följande transaktion 100 euro från Maijas konto till Uolevis konto:

BEGIN;
UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
UPDATE Accounts SET balance = balance + 100 WHERE customer = 'Uolevi';
COMMIT;

Idén med en transaktion är att inga permanenta förändringar sker innan kommandot COMMIT körs. I exemplet ovan är det alltså inte möjligt att Maija förlorar 100 euro utan att Uolevi får pengarna. Antingen ändras båda kontonas saldon (pengarna överförs), eller så förblir båda saldon oförändrade.

Om en transaktion av någon anledning avbryts innan kommandot COMMIT körs, återgår alla ändringar som gjorts i transaktionen. En orsak till att en transaktion avbryts kan exempelvis vara ett fel i datorns funktion (till exempel ett strömavbrott). Vi kan själva också avbryta transaktionen genom att ange kommandot ROLLBACK.

Att testa transaktioner

Ett bra sätt att förstå transaktioner är att testa dem i praktiken och se hur de fungerar. Här är ett exempel på hur det kan se ut i SQLite:

sqlite> CREATE TABLE Accounts (id INTEGER PRIMARY KEY,
   ...>                        customer TEXT, balance INTEGER);
sqlite> INSERT INTO Accounts (customer, balance) VALUES ('Uolevi', 350);
sqlite> INSERT INTO Accounts (customer, balance) VALUES ('Maija', 600);
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|600
sqlite> BEGIN;
sqlite> UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|500
sqlite> ROLLBACK;
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|600
sqlite> BEGIN;
sqlite> UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
sqlite> UPDATE Accounts SET balance = balance + 100 WHERE customer = 'Uolevi';
sqlite> COMMIT;
sqlite> SELECT * FROM Accounts;
1|Uolevi|450
2|Maija|500

Till att börja med har Uolevi 350 euro på sitt konto och Maija 600 euro på sitt konto. I den första transaktionen tar vi först 100 euro från Maijas konto, men ändrar oss sedan och avbryter transaktionen. Ändringen som gjordes i transaktionen återställs alltså, och kontonas saldon är desamma som i början. I den andra transaktionen genomförs däremot hela transaktionen. Efteråt har Uolevi 450 euro på sitt konto och Maija 500 euro.

Observera att ändringarna inom en transaktion är synliga, även om de ännu inte har sparats permanent i databasen. Till exempel visar SELECT-frågan i den första transaktionen att Maijas kontosaldo är 500 euro eftersom det föregående UPDATE-kommandot redan har ändrat saldot.

Transaktioner i programmering

Transaktionskommandon (BEGIN, COMMIT osv.) kan köras i programmering på samma sätt som andra SQL-kommandon. Till exempel lägger följande kod till tusen rader i tabellen Products inom en och samma transaktion i en for-loop:

db.execute("BEGIN")

for i in range(1000):
    db.execute("""
               INSERT INTO Products (name, price) VALUES (?, ?)
               """, ["product" + str(i), 1])

db.execute("COMMIT")

Eftersom koden körs inom en transaktion kommer antingen alla rader att läggas till i databasen eller inga rader alls om transaktionen av någon anledning misslyckas.

I det här fallet gör användningen av en transaktion också koden snabbare, eftersom varje rad inte läggs till i en separat transaktion utan tilläggen sker som en helhet. Det hjälper databasen att hantera insättningen av rader mer effektivt.

Parallella transaktioner

En ytterligare aspekt i hanteringen av transaktioner är att en databas kan ha flera användare som har pågående transaktioner samtidigt. I vilken utsträckning bör transaktioner från olika användare isoleras från varandra?

Detta är en fråga som inte har endast ett korrekt svar, utan svaret beror på situationen och på databasens egenskaper. Å ena sidan skulle den bästa lösningen vara att isolera transaktionerna helt från varandra, men å andra sidan kan detta försämra databasens användbarhet.

SQL-standarden definierar transaktioners isoleringsnivåer på följande sätt:

Nivå 1 (read uncommitted)

Det tillåts att en transaktion ser ändringar som en annan transaktion har gjort, även om den andra transaktionen ännu inte har slutförts.

Nivå 2 (read committed)

Till skillnad från nivå 1 får en transaktion här se ändringar som en annan transaktion har gjort endast om den andra transaktionen har slutförts.

Nivå 3 (repeatable read)

För nivå 3 gäller kraven för nivå 2, och dessutom, om samma rad läses flera gånger under en transaktion, ska innehållet vara detsamma varje gång.

Nivå 4 (serializable)

Transaktionerna är helt isolerade, och kommandona beter sig som om transaktionerna hade körts efter varandra, en i taget, i någon ordning.

Exempel

Låt oss titta på en situation där priset på produkt 1 i början är 8, och två användare (K1 och K2) samtidigt kör kommandon inom sina transaktioner:

K1: BEGIN;

K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;

K1: SELECT price FROM Products WHERE id = 1;

K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;

K1: SELECT price FROM Products WHERE id = 1;
K1: COMMIT;

På nivå 1 kan användare 1 få resultaten 5 och 7 från sina frågor eftersom ändringar som användare 2 gör kan bli synliga direkt, även om transaktionen som användare 2 utför ännu inte har slutförts.

På nivå 2 kan användare 1 få resultaten 8 och 7 från sina frågor eftersom den andra transaktionen ännu inte har slutförts vid den första frågan, medan den är slutförd vid den andra frågan.

På nivåerna 3 och 4 får användare 1 resultaten 8 och 8 från sina frågor eftersom detta är situationen före det att transaktionen börjar och en transaktion som slutförs under tiden inte får ändra innehållet i raden som lästs.

Transaktioner i praktiken

Hur transaktioner implementeras och vilka isoleringsnivåer som är tillgängliga beror på vilken databashanterare som används. I exempelvis SQLite är den enda möjliga nivån 4, medan PostgreSQL stöder nivåerna 2–4, där nivå 2 är standard.

Isoleringsnivå 4 är på sätt och vis den “bästa”, eftersom ändringar i transaktioner då inte kan bli synliga för varandra på något sätt. Varför finns det då andra nivåer och varför är till exempel standardnivån i PostgreSQL nivå 2?

En konsekvens av en hög isoleringsnivå är att det kan sakta ner eller till och med hindra transaktioner från att genomföras, eftersom slutförandet av en transaktion skulle kunna orsaka en konfliktsituation. Å andra sidan räcker det i många praktiska situationer med en lägre isoleringsnivå så länge databasanvändaren är medveten om det.

Bra kunskap om hur parallella transaktioner fungerar får man genom att studera databasernas dokumentationener och genom att själv testa att utföra parallella transaktioner. Till exempel kan vi starta två SQLite-tolkar, öppna samma databas med dem och sedan skriva kommandon som innehåller transaktioner för att göra egna observationer.

Följande konversation visar resultatet av det tidigare exemplet i två SQLite-tolkar som körs parallellt:

K1: BEGIN;
                                         
K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;

K1: SELECT price FROM Products WHERE id = 1;
8

K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;
Error: database is locked

K1: SELECT price FROM Products WHERE id=1;
8
K1: COMMIT;

Här ser man att den första transaktionen får resultatet 8 i båda frågorna. Den andra transaktionen kan däremot inte slutföras, utan ett felmeddelande Error: database is locked uppstår eftersom databasen är låst på grund av den parallella transaktionen. Isoleringen fungerar alltså som den ska, men den andra transaktionen måste försöka slutföras på nytt.

Här är motsvarande konversation i PostgreSQL-tolkar (nivå 2):

K1: BEGIN;

K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;

K1: SELECT price FROM Products WHERE id = 1;
8

K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;

K1: SELECT price FROM Products WHERE id = 1;
7
K1: COMMIT;

Nu dyker värdet 7, som den andra transaktionen ändrat, upp i den första transaktionen. Å andra sidan kan båda transaktionerna slutföras utan problem.

Hur fungerar transaktioner?

Att genomföra transaktioner är en fascinerande teknisk utmaning i databaser. På sätt och vis ska en transaktion göra ändringar i databasen eftersom olika kommandon kan bero på tidigare steg. Samtidigt får ingenting ändras permanent förrän transaktionen har avslutats

En central tanke bakom databaser är att spara förändringar på två sätt. Först antecknas en beskrivning av ändringen i en loggfil (write-ahead log) som man kan tänka sig som en lista över de kommandon som har körts. Först efter detta görs ändringarna i databasens egentliga datastrukturer. Om något oväntat händer i det senare skedet finns ändringarna redan sparade i loggfilen och kan utföras på nytt senare.

I samband med transaktioner måste databashanteraren också hålla reda på vilka ändringar som hör till vilken pågående transaktion. I praktiken kan tabellerna innehålla radändringar som bara är synliga för vissa transaktioner. Om en transaktion sedan slutförs, fogas dessa ändringar till tabellens permanenta innehåll.

Köra frågor

Idén bakom SQL-språket är att användaren bara behöver beskriva vilken information som ska hämtas, och databashanteraren sköter resten. Därför är det viktigt att databashanteraren kan hitta ett effektivt sätt att genomföra frågan och returnera resultatet.

Frågeplan

Många databashanterare kan på begäran visa sin plan för hur en given fråga ska utföras. Det här gör det möjligt att studera hur databashanteraren fungerar internt.

Låt oss som exempel titta på en fråga som hämtar uppgifter om rädisan ur tabellen Products:

SELECT * FROM Products WHERE name = 'rädisa';

Om vi i SQLite placerar ordet EXPLAIN framför en fråga får vi en beskrivning av frågeplanen som ser ut ungefär så här:

sqlite> EXPLAIN SELECT * FROM Products WHERE name = 'rädisa';
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     12    0                    00  Start at 12  
1     OpenRead       0     2     0     3              00  root=2 iDb=0; Products
2     Rewind         0     10    0                    00               
3       Column         0     1     1                    00  r[1]=Products.name
4       Ne             2     9     1     (BINARY)       52  if r[2]!=r[1] goto 9
5       Rowid          0     3     0                    00  r[3]=rowid   
6       Copy           1     4     0                    00  r[4]=r[1]    
7       Column         0     2     5                    00  r[5]=Products.price
8       ResultRow      3     3     0                    00  output=r[3..5]
9     Next           0     3     0                    01               
10    Close          0     0     0                    00               
11    Halt           0     0     0                    00               
12    Transaction    0     0     1     0              01  usesStmtJournal=0
13    TableLock      0     2     0     Products       00  iDb=0 root=2 write=0
14    String8        0     2     0     rädisa         00  r[2]='rädisa'
15    Goto           0     1     0                    00

SQLite omvandlar frågan till ett internt program som hämtar data från tabellerna. I det här fallet börjar programmet på rad 12 där transaktionen startar. På rad 14 sätts strängen ‘rädisan’ från sökvillkoret in i register 2. Därefter fortsätter programmet på rad 1 där hanteringen av tabellen Products börjar. Raderna 2–9 utgör en loop som letar upp de rader som matchar sökvillkoret i tabellen.

Vi får en mer sammanfattad frågeplan genom att sätta orden EXPLAIN QUERY PLAN framför frågan. Resultatet kan bli följande:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM Products WHERE name = 'rädisa';
0|0|0|SCAN TABLE Products

Här betyder SCAN TABLE Products att frågan går igenom raderna i tabellen Products.

Optimering av frågor

Om en fråga hämtar data från endast en tabell är den vanligtvis enkel att utföra. Utmaningarna uppstår oftast vid frågor som involverar flera tabeller. Databashanteraren måste då kunna optimera körningen av frågan, det vill säga skapa en bra plan som gör det möjligt att samla in den önskade datan effektivt från tabellerna.

Låt oss som exempel titta på följande fråga som listar kursnamn och lärare:

SELECT Courses.name, Teachers.name
FROM Courses, Teachers
WHERE Courses.teacher_id = Teachers.id;

Eftersom frågan behandlar två tabeller kan man tänka sig att den först bildar alla möjliga kombinationer av rader från tabellerna Courses och Teachers, och sedan väljer de rader där villkoret Courses.teacher_id = Teachers.id gäller. Även om detta är ett bra sätt att tänka på, överensstämmer det inte med hur en riktigt bra databashanterare fungerar.

Problemet är att tabellerna Courses och Teachers båda kan innehålla ett stort antal rader. Om till exempel varje tabell har en miljon rader, skulle antalet radkombinationer bli en biljon. Det skulle ta enormt lång tid att skapa och gå igenom alla kombinationer.

I det här fallet måste databashanteraren förstå vad användaren faktiskt vill hämta och hur villkoret i frågan begränsar resultatet. I praktiken räcker det att gå igenom alla rader i tabellen Courses och för varje rad effektivt hitta den önskade raden i tabellen Teachers.

Vi ber SQLite förklara frågeplanen:

sqlite> EXPLAIN QUERY PLAN SELECT Courses.name, Teachers.name FROM Courses, Teachers WHERE Courses.teacher_id = Teachers.id;
0|0|0|SCAN TABLE Courses
0|1|1|SEARCH TABLE Teachers USING INTEGER PRIMARY KEY (rowid=?)

Frågan går igenom raderna i tabellen Courses (SCAN TABLE Courses) och hämtar information från tabellen Teachers via primärnyckeln (SEARCH TABLE Teachers). Det senare betyder att när en viss rad i tabellen Courses behandlas, hämtar frågan effektivt raden i tabellen Teachers där Teachers.id är samma som Courses.teacher_id.

Hur kan man i praktiken hämta raden från tabellen Teachers på ett effektivt sätt? Detta görs genom att använda det index som är kopplat till tabellen.

Index

Ett index är en sökstrukturerad datakonstruktion som lagras tillsammans med en databastabell med syftet att effektivisera utförandet av frågor som rör tabellen. Med hjälp av ett index kan databashanteraren snabbt avgöra var i tabellen rader som matchar ett visst sökvillkor finns.

Man kan tänka på ett index på samma sätt som ett register i slutet av en bok, där det anges på vilka sidor olika sökord förekommer. Med hjälp av registret hittar vi ett visst ords placering mycket snabbare än om vi läste igenom hela boken.

Primärnyckelindex

När en tabell skapas i databasen får dess primärnyckel automatiskt ett index. Tack vare detta kan man effektivt utföra sökningar där villkoret rör primärnyckeln.

När vi i SQLite exempelvis skapar tabellen

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER
);

får tabellen ett index på kolumnen id, och vi kan effektivt söka efter produkter baserat på ID-nummer. Tack vare detta fungerar till exempel följande fråga effektivt:

SELECT price FROM Products WHERE id = 3;

Vi kan bekräfta detta genom att titta på frågeplanen:

sqlite> EXPLAIN QUERY PLAN SELECT price FROM Products WHERE id = 3;
selectid    order       from        detail                                                   
----------  ----------  ----------  ---------------------------------------------------------
0           0           0           SEARCH TABLE Products USING INTEGER PRIMARY KEY (rowid=?)

Frågeplanen innehåller SEARCH TABLE, vilket betyder att frågan kan hämta data effektivt från tabellen med hjälp av index.

Skapa index

Primärnyckelindexet är praktiskt, men vi kanske också vill söka information baserat på en annan kolumn. Till exempel hämtar följande fråga rader baserat på kolumnen price:

SELECT name FROM Products WHERE price = 4;

Denna fråga är som standard inte effektiv eftersom kolumnen price saknar index. Vi ser detta genom att titta på frågeplanen:

sqlite> EXPLAIN QUERY PLAN SELECT name FROM Products WHERE price = 4;
selectid    order       from        detail             
----------  ----------  ----------  -------------------
0           0           0           SCAN TABLE Products

SCAN TABLE betyder att frågan måste gå igenom alla rader i tabellen. Detta tar länge om tabellen innehåller många rader.

Vi kan skapa ett nytt index som gör frågor som använder kolumnen price mer effektiva. Vi kan skapa indexet med kommandot CREATE INDEX på följande sätt:

CREATE INDEX idx_price ON Products (price);

Här är idx_price namnet på indexet som vi kan referera till senare. Efter skapandet fungerar indexet helt automatiskt, vilket innebär att databashanteraren använder indexet i frågor och uppdaterar det.

Låt oss nu titta på frågeplanen efter att vi har skapat indexet:

sqlite> EXPLAIN QUERY PLAN SELECT name FROM Products WHERE price = 4;
selectid    order       from        detail                                               
----------  ----------  ----------  -----------------------------------------------------
0           0           0           SEARCH TABLE Products USING INDEX idx_price (price=?)

I och med att indexet nu finns används inte längre SCAN TABLE utan istället SEARCH TABLE i planen. Planen visar också att indexet idx_price kommer att användas.

Fler användningsområden

Vi kan också använda index i frågor där vi söker efter mindre eller större värden. Med hjälp av indexet som skapats för kolumnen price kan vi till exempel hitta rader där villkoret price < 3 eller price >= 8 gäller.

Det är också möjligt att skapa index baserat på flera kolumner. Till exempel kan vi skapa ett index på följande sätt:

CREATE INDEX idx_price ON Products (price, name);

I det här indexet är raderna ordnade i första hand efter pris och i andra hand efter namn. Indexet effektiviserar sökningar där sökvillkoret är antingen endast pris eller både pris och namn. Indexet förbättrar däremot inte sökningar där sökvillkoret endast är namn.

Hur fungerar index?

Ett index behöver en stödkonstruktion, en sökstruktur, som gör det möjligt att effektivt hämta rader baserat på kolumnvärden. Detta kan till exempel implementeras som en trädstruktur, där nycklarna utgörs av kolumnvärdena.

Relaterad teori behandlas mer ingående i kursen Datastrukturer och algoritmer i samband med binära sökträd. Typiska trädstrukturer som används i databaser är B-träd och dess varianter.

När ska man skapa index?

I princip kunde man tänka sig att skapa ett index för varje kolumn i tabellen, vilket skulle göra många typer av frågor snabba. Detta är dock inte en bra idé i praktiken.

Även om index gör frågor snabbare finns det också två problem: ett index tar upp utrymme och gör dessutom insättning och ändring av data långsammare. Det senare beror på att när tabellens innehåll ändras måste ändringen uppdateras i alla index som är kopplade till tabellen. Index ska alltså användas med eftertanke och inte skapas i onödan.

En bra anledning att skapa ett index är om vi ofta vill utföra vissa typer av frågor som tar länge eftersom databashanteraren annars måste gå igenom alla rader i tabellen i onödan. I sådana fall kan vi lägga till ett lämpligt index på tabellen, vilket gör att frågorna fungerar effektivt i fortsättningen.

Index har i praktiken stor betydelse för databasers effektivitet. Många databaser fungerar långsamt därför att de saknar viktiga index.

Index är också ett exempel på hur redundant information kan effektivisera frågor. I index lagras dock inte den redundanta informationen i tabellen utan utanför tabellen i en separat sökstruktur.