SQL-indekser: Få lynraske databasesøk!

by
Tweet
Share
Share
Pin
0 Shares

Ønsker du å forbedre hastigheten på dine databasespørringer? Lær hvordan du lager en databaseindeks ved hjelp av SQL og optimaliserer søkeytelsen, noe som vil føre til raskere datainnhenting.

Ved henting av data fra en databasetabell, er det ofte nødvendig å filtrere basert på spesifikke kolonner.

Anta at du skriver en SQL-spørring for å hente data basert på gitte kriterier. Som standard vil spørringen utføre en full tabellskanning, der den sjekker alle poster til den finner de som tilfredsstiller betingelsen, og returnerer deretter resultatene.

Dette kan være svært ineffektivt når du må hente data fra en stor databasetabell med millioner av rader. Du kan fremskynde slike spørringer ved å opprette en databaseindeks.

Hva er en databaseindeks?

Hvis du leter etter et spesifikt begrep i en bok, vil du da lese gjennom hele boken, side for side? Sannsynligvis ikke.

I stedet vil du bruke registeret for å finne ut hvilke sider som refererer til begrepet og deretter gå direkte til disse sidene. En indeks i en database fungerer på samme måte som registeret i en bok.

En databaseindeks er et sett med referanser til den faktiske daten, sortert på en måte som gjør datainnhenting raskere. Internt kan en databaseindeks implementeres med datastrukturer som B+-trær og hashtabeller. Derfor forbedrer en databaseindeks hastigheten og effektiviteten ved datainnhentingsoperasjoner.

Opprette en databaseindeks i SQL

Nå som vi vet hva en databaseindeks er og hvordan den kan fremskynde datainnhenting, skal vi lære å opprette en databaseindeks i SQL.

Når du utfører filtreringsoperasjoner – ved å bruke en WHERE-klausul for å angi betingelsene for datahenting – vil du kanskje ofte hente data fra en bestemt kolonne.

CREATE INDEX index_name ON table (column)

Her betyr:

  • indeksnavn: Navnet på indeksen som skal opprettes.
  • tabell: Navnet på tabellen i relasjonsdatabasen.
  • kolonne: Navnet på kolonnen i databasetabellen som indeksen skal opprettes på.

Du kan også opprette indekser på flere kolonner – en sammensatt indeks – avhengig av behovet. Her er syntaksen for å gjøre dette:

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

La oss se på et praktisk eksempel.

Forstå ytelsesgevinstene med en databaseindeks

For å forstå fordelen med å lage en indeks, må vi opprette en databasetabell med et betydelig antall poster. Kodeeksemplene er for SQLite. Du kan likevel bruke andre RDBMS som PostgreSQL og MySQL.

Fylle en databasetabell med poster

Du kan også bruke Pythons innebygde tilfeldige modul for å generere og sette inn poster i databasen. Vi vil imidlertid bruke Faker for å fylle tabellen med en million rader.

Følgende Python-skript:

  • Oppretter og kobler til databasen kunde_db.
  • Oppretter en kunder-tabell med feltene: fornavn, etternavn, by og antall bestillinger.
  • Genererer syntetisk data og setter inn data – en million poster – i kunder-tabellen.

Du finner 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()

Nå kan vi begynne å utføre spørringer.

Opprette en indeks på bykolonnen

La oss anta at du vil hente kundeinformasjon ved å filtrere basert på bykolonnen. SELECT-spørringen din vil se slik ut:

SELECT column(s) FROM customers
WHERE condition;

La oss opprette en indeks city_idx på bykolonnen i kunder-tabellen:

CREATE INDEX city_idx ON customers (city);

⚠ Å opprette en indeks tar litt tid og er en engangsoperasjon. Men ytelsesfordelene når du trenger et stort antall søk – ved å filtrere på bykolonnen – vil være betydelige.

Sletting av en databaseindeks

For å slette en indeks kan du bruke DROP INDEX-setningen slik:

DROP INDEX index_name;

Sammenligne spørretider med og uten indeks

Hvis du vil kjøre spørringer i et Python-skript, kan du bruke standardtimeren for å måle utførelsestidene for spørringer.

Alternativt kan du kjøre spørringene med sqlite3-kommandolinjeklienten. For å jobbe med customer_db.db ved hjelp av kommandolinjeklienten, kjør følgende kommando i terminalen:

$ sqlite3 customer_db.db;

For å måle utførelsestidene, kan du bruke .timer-funksjonen i sqlite3, som vist nedenfor:

sqlite3 > .timer on
        > <query here>

Fordi vi har opprettet en indeks på bykolonnen, vil spørringene som filtrerer data basert på denne kolonnen være mye raskere.

Kjør først spørringene uten indeks, og deretter med. Noter utførelsestidene i begge tilfellene. Her er noen eksempler:

Spørring Tid uten Index Tid med Index
SELECT * FROM customers
WHERE city LIKE «Ny%»
LIMIT 10;		 0.100 s 0.001 s
SELECT * FROM customers
WHERE city=’New Wesley»;		 0.148 s 0.001 s
SELECT * FROM customers
WHERE city IN («New Wesley», «New Steven», «New Carmenmouth»);		 0.247 s 0.003 s

Vi ser at hentingstidene med indeks er betydelig raskere enn de uten indeks på bykolonnen.

Beste praksis for å opprette og bruke databaseindekser

Du bør alltid vurdere om ytelsesgevinsten er større enn kostnadene ved å opprette en databaseindeks. Her er noen gode retningslinjer:

  • Velg de riktige kolonnene for å lage en indeks. Unngå å opprette for mange indekser på grunn av de ekstra ressursene de krever.
  • Hver gang en indeksert kolonne oppdateres, må den tilhørende indeksen også oppdateres. Så, selv om en databaseindeks fremskynder datahenting, reduserer den hastigheten på innsettinger og oppdateringer. Derfor bør du opprette indekser på kolonner som ofte brukes i spørringer, men som sjelden oppdateres.

Når bør du ikke lage en indeks?

Nå har du forhåpentligvis en ide om når og hvordan du oppretter en indeks. Men la oss også se når en databaseindeks kanskje ikke er nødvendig:

  • Når databasetabellen er liten og ikke inneholder mange rader, er en full tabellskanning for å hente data ikke spesielt kostbar.
  • Ikke opprett indekser på kolonner som sjelden brukes til datahenting. Hvis du oppretter indekser på kolonner som ikke ofte brukes i spørringer, vil kostnadene ved å opprette og vedlikeholde indeksen overstige ytelsesgevinsten.

Oppsummering

La oss gå gjennom det vi har lært:

  • Når du utfører spørringer mot en database for å hente data, kan det være nødvendig å filtrere basert på enkelte kolonner. En databaseindeks på kolonner som ofte brukes i spørringer kan forbedre ytelsen.
  • For å opprette en indeks på en enkelt kolonne, bruk syntaksen: CREATE INDEX indeksnavn ON tabell (kolonne). Hvis du vil opprette en sammensatt indeks, bruk: CREATE INDEX indeksnavn ON tabell (kolonne_1, kolonne_2,..., kolonne_k).
  • Hver gang en indeksert kolonne endres, må også den tilsvarende indeksen oppdateres. Velg derfor kolonnene som er ofte brukt i spørringer og sjeldnere oppdatert, for å opprette en indeks.
  • Hvis databasetabellen er relativt liten, vil kostnadene ved å opprette, vedlikeholde og oppdatere en indeks være større enn ytelsesgevinsten.

I de fleste moderne databasehåndteringssystemer finnes det en spørringsoptimalisering som sjekker om en indeks på en bestemt kolonne vil gjøre spørringen raskere. La oss derfor lære mer om de beste praksisene for databasedesign.