[Explained] Sådan opretter du et databaseindeks i SQL

Vil du fremskynde databaseforespørgsler? Lær, hvordan du opretter et databaseindeks ved hjælp af SQL og optimerer forespørgselsydeevne – og fremskynder datahentning.

Når du henter data fra en databasetabel, skal du oftere filtrere baseret på specifikke kolonner.

Antag, at du skriver en SQL-forespørgsel for at hente data baseret på specifikke forhold. Som standard kører forespørgslen en fuld-tabel-scanning, indtil alle de poster, der opfylder betingelsen, er blevet fundet, og returnerer derefter resultaterne.

Dette kan være ekstremt ineffektivt, når du skal forespørge i en stor databasetabel med flere millioner rækker. Du kan fremskynde sådanne forespørgsler ved at oprette et databaseindeks.

Hvad er et databaseindeks?

Når du vil finde et specifikt udtryk i en bog, vil du så lave en hel bogscanning – den ene side efter den anden – for at lede efter den bestemte term? Nå, det gør du ikke.

Du skal i stedet slå indekset op for at finde ud af, hvilke sider der refererer til udtrykket og springe direkte til disse sider. Et indeks i en database fungerer meget som indekserne i en bog.

Et databaseindeks er et sæt pointere eller referencer til de faktiske data, men sorteret på en måde, der gør datahentning hurtigere. Internt kan et databaseindeks implementeres ved hjælp af datastrukturer såsom B+ træer og hashtabeller. Derfor forbedrer et databaseindeks hastigheden og effektiviteten af ​​datahentningsoperationer.

Oprettelse af et databaseindeks i SQL

Nu hvor vi ved, hvad et databaseindeks er, og hvordan det kan fremskynde datahentning, lad os lære, hvordan man opretter et databaseindeks i SQL.

Når du udfører filtreringshandlinger – ved at angive genfindingsbetingelsen ved hjælp af en WHERE-sætning – vil du måske forespørge en bestemt kolonne oftere end andre.

CREATE INDEX index_name ON table (column)

Her,

• indeksnavn er navnet på det indeks, der skal oprettes
• tabel refererer til tabellen i relationsdatabasen
• kolonne refererer til navnet på kolonnen i databasetabellen, som vi skal oprette indekset på.

Du kan også oprette indekser på flere kolonner – et indeks med flere kolonner – afhængigt af kravene. Her er syntaksen til at gøre det:

CREATE INDEX index_name ON table (column_1, column_2,...,column_k)

Lad os nu gå videre til et praktisk eksempel.

Forståelse af præstationsgevinsterne ved databaseindeks

For at forstå fordelen ved at oprette et indeks, skal vi oprette en databasetabel med et stort antal poster. Kodeeksemplerne er til SQLite. Men du kan også bruge andre RDBMS efter eget valg, såsom PostgreSQL og MySQL.

Udfyldning af en databasetabel med poster

Du kan også bruge Pythons indbyggede tilfældige modul til at oprette og indsætte poster i databasen. Vi bruger dog Faker at udfylde databasetabellen med en million rækker.

Følgende Python-script:

• Opretter og forbinder til customer_db-databasen.
• Opret en kundetabel med felterne: fornavn, efternavn, by og antal_ordrer.
• Genererer syntetiske data og indsætter data – en million poster – i kundetabellen.

Du kan også finde 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 begynde at spørge.

Oprettelse af et indeks på bykolonnen

Antag, at du ønsker at få kundeoplysningerne ved at filtrere baseret på bykolonnen. Din SELECT-forespørgsel vil se sådan ud:

SELECT column(s) FROM customers
WHERE condition;

Så lad os oprette city_idx i bykolonnen i kundetabellen:

CREATE INDEX city_idx ON customers (city);

⚠ Oprettelse af et indeks tager en ikke ubetydelig tid og er en engangsoperation. Men ydeevnefordelene, når du har brug for et stort antal forespørgsler – ved at filtrere på bykolonnen – vil være betydelige.

Sletning af et databaseindeks

For at slette et indeks kan du bruge DROP INDEX-sætningen sådan:

DROP INDEX index_name;

Sammenligning af forespørgselstider med og uden indeks

Hvis du vil køre forespørgsler i et Python-script, kan du bruge standardtimeren til at få udførelsestiderne for forespørgsler.

Alternativt kan du køre forespørgslerne ved hjælp af sqlite3-kommandolinjeklienten. For at arbejde med customer_db.db ved hjælp af kommandolinjeklienten skal du køre følgende kommando på terminalen:

$ sqlite3 customer_db.db;

For at få de omtrentlige udførelsestider kan du bruge .timer-funktionaliteten indbygget i sqlite3 sådan:

sqlite3 > .timer on
        > <query here>

Fordi vi har oprettet et indeks på bykolonnen, vil de forespørgsler, der involverer filtrering baseret på bykolonnen i WHERE-sætningen, være meget hurtigere.

Kør først forespørgslerne. Opret derefter indekset og kør forespørgslerne igen. Notér udførelsestiderne i begge tilfælde. Her er nogle eksempler:

QueryTime uden IndexTime med IndexSELECT * FRA kunder
HVOR by SOM ‘Ny%’
LIMIT 10;0,100 s0,001 sSELECT * FRA kunder
WHERE city=’New Wesley’;0,148 s0,001 sSELECT * FRA kunder
WHERE city IN (‘New Wesley’, ‘New Steven’, ‘New Carmenmouth’);0,247 s0,003 s

Vi ser, at hentningstiderne med indeks er flere ordrer hurtigere end dem uden indeks på bykolonnen.

Bedste fremgangsmåder til at oprette og bruge databaseindekser

Du bør altid kontrollere, om præstationsgevinsterne er større end overheaden ved at oprette et databaseindeks. Her er nogle bedste fremgangsmåder at huske på:

• Vælg de rigtige kolonner for at oprette et indeks. Undgå at oprette for mange indekser på grund af de betydelige omkostninger.
• Hver gang en indekseret kolonne opdateres, bør det tilsvarende indeks også opdateres. Så oprettelse af et databaseindeks (selvom fremskynder hentning) forsinker indsættelser og opdateringsoperationer betydeligt. Derfor bør du oprette indekser på kolonner, der ofte forespørges, men sjældent opdateres.

Hvornår bør du ikke oprette et indeks?

Nu burde du have en idé om, hvornår og hvordan du opretter et indeks. Men lad os også sige, hvornår databaseindeks måske ikke er nødvendigt:

• Når databasetabellen er lille og ikke indeholder et stort antal rækker, er fuld-tabelscanning for at hente data ikke så dyrt.
• Opret ikke indekser på kolonner, der sjældent bruges til hentning. Når du opretter indekser på kolonner, der ikke ofte forespørges på, opvejer omkostningerne ved at oprette og vedligeholde et indeks præstationsgevinsterne.

Opsummering

Lad os gennemgå, hvad vi har lært:

• Når du forespørger en database for at hente data, skal du muligvis filtrere baseret på bestemte kolonner oftere. Et databaseindeks på sådanne hyppigt forespurgte kolonner kan forbedre ydeevnen.
• For at oprette et indeks på en enkelt kolonne skal du bruge syntaksen: CREATE INDEX indeksnavn PÅ tabel (kolonne). Hvis du vil oprette et indeks med flere kolonner, skal du bruge: CREATE INDEX index_name ON table (column_1, column_2,…,column_k)
• Når en indekseret kolonne ændres, bør det tilsvarende indeks også opdateres. Vælg derfor de rigtige kolonner – hyppigt forespurgt og meget sjældnere opdateret – for at oprette et indeks.
• Hvis databasetabellen er relativt mindre, vil omkostningerne ved at oprette, vedligeholde og opdatere et indeks være større end ydeevnegevinsten.

I de fleste moderne databasestyringssystemer er der en forespørgselsoptimering, der kontrollerer, om et indeks på en bestemt kolonne vil få forespørgslen til at køre hurtigere. Lad os derefter lære den bedste praksis for databasedesign.