[Explained] Hur man skapar ett databasindex i SQL

Önskar du att dina databasfrågor skulle utföras snabbare? Upptäck hur du skapar ett databasindex med SQL för att optimera frågeprestandan och påskynda datahämtningen.

Vid informationsinhämtning från en databastabell, är det vanligt att behöva filtrera data utifrån specifika kolumner.

Låt oss anta att du formulerar en SQL-fråga för att hämta information baserat på definierade kriterier. Som standard kommer frågan att genomföra en fullständig genomsökning av tabellen tills samtliga poster som uppfyller villkoren har identifierats, och därefter returnera resultatet.

Detta förfaringssätt kan vara synnerligen ineffektivt, i synnerhet när det handlar om stora databastabeller med miljontals rader. Du kan påskynda sådana sökningar genom att implementera ett databasindex.

Vad är ett databasindex?

När du söker efter en specifik term i en bok, skulle du då bläddra igenom hela boken, sida för sida, i jakt på termen? Sannolikt inte.

Istället använder du bokens index för att hitta de sidor där termen nämns och går direkt till de relevanta sidorna. På liknande sätt fungerar index i databaser.

Ett databasindex är en samling pekare eller referenser till den faktiska datan, organiserad på ett sätt som möjliggör snabbare datahämtning. Intern, kan ett databasindex implementeras med hjälp av datastrukturer som B+-träd och hashtabeller. Därför förbättrar ett databasindex både hastigheten och effektiviteten vid informationshämtning.

Skapa ett databasindex i SQL

Nu när vi förstår vad ett databasindex är och hur det påskyndar datahämtningen, ska vi undersöka hur man skapar ett databasindex i SQL.

Om du utför filtreringsoperationer – genom att definiera hämtningsvillkor med hjälp av en WHERE-klausul – kanske du upptäcker att du oftare vill göra förfrågningar mot en viss kolumn än andra.

CREATE INDEX index_namn ON tabell (kolumn)

Här betecknar:

• `index_namn` namnet på indexet som ska skapas.
• `tabell` den specifika tabellen i databasen.
• `kolumn` namnet på den kolumn i databastabellen som vi vill indexera.

Du har även möjligheten att skapa index för flera kolumner samtidigt – ett s.k. flerkolumnsindex – beroende på dina specifika behov. Syntaxen för detta ser ut så här:

CREATE INDEX index_namn ON tabell (kolumn_1, kolumn_2,...,kolumn_k)

Låt oss nu gå vidare till ett praktiskt exempel.

Förstå prestandafördelarna med ett databasindex

För att fullt ut förstå fördelarna med att implementera ett index, behöver vi först skapa en databastabell som innehåller en stor mängd data. Kodexemplen är baserade på SQLite. Det går även bra att använda andra RDBMS (relational database management systems) som PostgreSQL eller MySQL.

Fylla en databastabell med data

Du kan använda Pythons inbyggda modul för slumpmässiga tal för att generera och infoga data i databasen. Vi kommer dock att använda Faker för att fylla databastabellen med en miljon rader.

Följande Python-skript gör följande:

• Skapar och ansluter till databasen `customer_db`.
• Skapar en tabell med namnet `customers` med fälten: `förnamn`, `efternamn`, `stad` och `antal_order`.
• Genererar syntetisk data och lägger in en miljon poster i `customers`-tabellen.

Du hittar även koden på GitHub.

# main.py
# imports
import sqlite3
from faker import Faker
import random

# connect to the db
db_conn = sqlite3.connect('customer_db.db')
db_cursor = db_conn.cursor()

# create table
db_cursor.execute('''CREATE TABLE customers (
                  id INTEGER PRIMARY KEY,
                  first_name TEXT,
                  last_name TEXT,
                  city TEXT,
                  num_orders INTEGER)''')

# create a Faker object
fake = Faker()
Faker.seed(27)

# create and insert 1 million records
num_records = 1_000_000

for _ in range(num_records):
    first_name = fake.first_name()
    last_name = fake.last_name()
    city = fake.city()
    num_orders = random.randint(0,100)
    db_cursor.execute('INSERT INTO customers (first_name, last_name, city, num_orders) VALUES (?,?,?,?)', (first_name, last_name, city, num_orders))

# commit the transaction and close the cursor and connection
db_conn.commit()
db_cursor.close()
db_conn.close()

Nu kan vi börja göra förfrågningar.

Skapa ett index på kolumnen `stad`

Anta att du vill hämta kundinformation genom att filtrera baserat på kolumnen `stad`. Din SELECT-fråga kommer då att ha följande form:

SELECT kolumn(er) FROM kunder
WHERE villkor;

Så, låt oss skapa `city_idx` i kolumnen `stad` i tabellen `kunder`:

CREATE INDEX city_idx ON kunder (stad);

⚠ Observera att det tar en viss tid att skapa ett index. Det är en engångsoperation. Dock kommer prestandafördelarna att vara märkbara när du behöver göra ett stort antal förfrågningar som filtrerar på kolumnen `stad`.

Ta bort ett databasindex

För att ta bort ett index, kan du använda DROP INDEX-satsen på följande sätt:

DROP INDEX index_namn;

Jämföra frågetider med och utan index

För att få ut frågetider inom ett Python-skript, kan du använda standard-timern för att mäta exekveringstider.

Du har även möjligheten att utföra frågor med hjälp av kommandoradsverktyget `sqlite3`. För att öppna och arbeta med `customer_db.db` via kommandoraden, kör följande kommando i terminalen:

$ sqlite3 customer_db.db;

För att få de ungefärliga körningstiderna, kan du använda `.timer`-funktionen som är inbyggd i `sqlite3` enligt följande:

sqlite3 > .timer on
       > <fråga här>

Eftersom vi har skapat ett index på kolumnen `stad`, kommer förfrågningar som filtrerar baserat på denna kolumn i WHERE-satsen att gå mycket snabbare.

Börja med att utföra frågorna. Därefter, skapa indexet och utför frågorna igen. Notera de olika exekveringstiderna. Här följer några exempel:

Query	Time utan Index	Time med Index
SELECT * FROM kunder WHERE stad LIKE ”New%” LIMIT 10;	0,100 s	0,001 s
SELECT * FROM kunder WHERE city=’New Wesley’;	0,148 s	0,001 s
SELECT * FROM kunder WHERE city IN (’New Wesley’, ’New Steven’, ’New Carmenmouth’);	0,247 s	0,003 s

Det är tydligt att hämtningstiderna med index är betydligt snabbare än de utan index på kolumnen `stad`.

Bästa praxis för att skapa och använda databasindex

Du bör alltid utvärdera om prestandafördelarna är större än kostnaderna för att skapa ett databasindex. Här är några saker att tänka på:

• Välj rätt kolumner för indexering. Undvik att skapa för många index på grund av den höga kostnaden.
• Varje gång en indexerad kolumn uppdateras måste det relaterade indexet också uppdateras. Att skapa ett databasindex (även om det påskyndar hämtning) saktar ner infogningar och uppdateringar av data. Därför bör du skapa index på kolumner som ofta efterfrågas, men som sällan uppdateras.

När bör du inte skapa ett index?

Nu bör du ha en god förståelse för när och hur man skapar ett index. Låt oss även gå igenom när databasindex kan vara onödigt:

• När databastabellen är liten och inte innehåller ett stort antal rader är en fullständig genomsökning inte särskilt ineffektiv.
• Skapa inte index på kolumner som sällan används för sökningar. Om du indexerar kolumner som inte används frekvent kommer kostnaderna för att skapa och underhålla indexet överstiga prestandafördelarna.

Sammanfattning

Låt oss sammanfatta vad vi har lärt oss:

• När du gör förfrågningar till en databas, kan det hända att du behöver filtrera baserat på vissa kolumner. Ett databasindex på dessa kolumner kan förbättra prestandan avsevärt.
• För att skapa ett index på en enskild kolumn, använd följande syntax: `CREATE INDEX index_namn ON tabell (kolumn)`. Om du vill skapa ett index med flera kolumner, använd: `CREATE INDEX index_namn ON tabell (kolumn_1, kolumn_2, … ,kolumn_k)`
• Varje gång en indexerad kolumn ändras, måste motsvarande index uppdateras. Välj därför de kolumner som är mest lämpade, det vill säga de som efterfrågas ofta, men uppdateras mer sällan, för indexering.
• Om databastabellen är relativt liten, kommer kostnaderna för att skapa, underhålla och uppdatera ett index att överstiga prestandafördelarna.

De flesta moderna databashanteringssystem har en frågeoptimerare som utvärderar om ett index på en specifik kolumn kommer att resultera i snabbare förfrågningar. Låt oss fortsätta med att utforska bästa praxis för databasdesign.