Vil du øke hastigheten på databasespørsmål? Lær hvordan du oppretter en databaseindeks ved hjelp av SQL og optimaliserer søkeytelsen – og øker hastigheten på datainnhentingen.
Når du henter data fra en databasetabell, må du filtrere basert på spesifikke kolonner oftere.
Anta at du skriver en SQL-spørring for å hente dataene basert på spesifikke forhold. Som standard kjører spørringen en full tabellskanning til alle postene som tilfredsstiller betingelsen er funnet, og returnerer deretter resultatene.
Dette kan være ekstremt ineffektivt når du må spørre en stor databasetabell med flere millioner rader. Du kan øke hastigheten på slike spørringer ved å opprette en databaseindeks.
Innholdsfortegnelse
Hva er en databaseindeks?
Når du ønsker å finne et spesifikt begrep i en bok, vil du gjøre en full bokskanning – den ene siden etter den andre – for å se etter den aktuelle termen? Vel, det gjør du ikke.
Du vil i stedet slå opp indeksen for å finne ut hvilke sider som refererer til begrepet og hoppe rett til disse sidene. En indeks i en database fungerer mye som indeksene i en bok.
En databaseindeks er et sett med pekere eller referanser til de faktiske dataene, men sortert på en måte som gjør datainnhentingen raskere. Internt kan en databaseindeks implementeres ved hjelp av datastrukturer som B+-trær og hashtabeller. Derfor forbedrer en databaseindeks hastigheten og effektiviteten til datainnhentingsoperasjoner.
Opprette en databaseindeks i SQL
Nå som vi vet hva en databaseindeks er og hvordan den kan fremskynde datainnhenting, la oss lære hvordan du lager en databaseindeks i SQL.
Når du utfører filtreringsoperasjoner – ved å spesifisere gjenopprettingsbetingelsen ved hjelp av en WHERE-klausul – vil du kanskje spørre en bestemt kolonne oftere enn andre.
CREATE INDEX index_name ON table (column)
Her,
- indeksnavn er navnet på indeksen som skal opprettes
- Tabell refererer til tabellen i relasjonsdatabasen
- kolonne refererer til navnet på kolonnen i databasetabellen som vi trenger for å lage indeksen på.
Du kan også opprette indekser på flere kolonner – en indeks med flere kolonner – avhengig av kravene. Her er syntaksen for å gjøre det:
CREATE INDEX index_name ON table (column_1, column_2,...,column_k)
La oss nå gå videre til et praktisk eksempel.
Forstå ytelsesgevinstene til databaseindeksen
For å forstå fordelen med å lage en indeks, må vi lage en databasetabell med et stort antall poster. Kodeeksemplene er for SQLite. Men du kan også bruke andre RDBMS etter eget valg, for eksempel PostgreSQL og MySQL.
Fylle en databasetabell med poster
Du kan også bruke Pythons innebygde tilfeldige modul for å lage og sette inn poster i databasen. Vi vil imidlertid bruke Faker for å fylle databasetabellen med en million rader.
Følgende Python-skript:
- Oppretter og kobler til kunde_db-databasen.
- Opprett en kundetabell med feltene: fornavn, etternavn, by og antall ordrer.
- Genererer syntetiske data og setter inn data – en million poster – i kundetabellen.
Du kan også finne 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 å spørre.
Opprette en indeks på bykolonnen
Anta at du ønsker å få kundeinformasjonen ved å filtrere basert på bykolonnen. SELECT-søket ditt vil se slik ut:
SELECT column(s) FROM customers WHERE condition;
Så la oss lage city_idx i bykolonnen i kundetabellen:
CREATE INDEX city_idx ON customers (city);
⚠ Å lage en indeks tar ikke ubetydelig 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 å få utførelsestidene for spørringer.
Alternativt kan du kjøre spørringene ved å bruke sqlite3-kommandolinjeklienten. For å jobbe med customer_db.db ved å bruke kommandolinjeklienten, kjør følgende kommando på terminalen:
$ sqlite3 customer_db.db;
For å få de omtrentlige utførelsestidene kan du bruke .timer-funksjonaliteten innebygd i sqlite3 slik:
sqlite3 > .timer on
> <query here>
Fordi vi har laget en indeks på bykolonnen, vil spørringene som involverer filtrering basert på bykolonnen i WHERE-klausulen være mye raskere.
Kjør først spørringene. Deretter oppretter du indeksen og kjører spørringene på nytt. Noter ned gjennomføringstidene i begge tilfellene. Her er noen eksempler:
QueryTime uten IndexTime med IndexSELECT * FRA kunder
HVOR by SOM «Ny%»
GRENSE 10;0,100 s0,001 sVELG * 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 hentingstidene med indeks er flere bestillinger raskere enn de uten indeks på bykolonnen.
Beste praksis for å opprette og bruke databaseindekser
Du bør alltid sjekke om ytelsesgevinsten er større enn overheaden ved å lage en databaseindeks. Her er noen gode fremgangsmåter du bør huske på:
- Velg de riktige kolonnene for å lage en indeks. Unngå å lage for mange indekser på grunn av de betydelige kostnadene.
- Hver gang en indeksert kolonne oppdateres, bør den tilsvarende indeksen også oppdateres. Så å lage en databaseindeks (selv om det øker hastigheten på henting) reduserer innsettinger og oppdateringsoperasjoner betydelig. Derfor bør du opprette indekser på kolonner som ofte spørres, men som sjelden oppdateres.
Når bør du ikke lage en indeks?
Nå bør du ha en ide om når og hvordan du oppretter en indeks. Men la oss også si når databaseindeks kanskje ikke er nødvendig:
- Når databasetabellen er liten og ikke inneholder et stort antall rader, er full tabellskanning for å hente data ikke like dyrt.
- Ikke lag indekser på kolonner som sjelden brukes til henting. Når du oppretter indekser på kolonner som ikke spørres ofte, oppveier kostnadene ved å opprette og vedlikeholde en indeks ytelsesgevinsten.
Oppsummering
La oss gå gjennom det vi har lært:
- Når du spør en database for å hente data, må du kanskje filtrere basert på enkelte kolonner oftere. En databaseindeks på kolonner som ofte blir spurt om kan forbedre ytelsen.
- For å lage en indeks på en enkelt kolonne, bruk syntaksen: CREATE INDEX indeksnavn PÅ tabell (kolonne). Hvis du vil lage en indeks med flere kolonner, bruk: LAG INDEKS indeksnavn PÅ tabell (kolonne_1, kolonne_2,…, kolonne_k)
- Hver gang en indeksert kolonne endres, bør den tilsvarende indeksen også oppdateres. Velg derfor de riktige kolonnene – ofte spurt og mye sjeldnere oppdatert – for å lage en indeks.
- Hvis databasetabellen er relativt mindre, vil kostnadene for å opprette, vedlikeholde og oppdatere en indeks være større enn ytelsesgevinsten.
I de fleste moderne databasebehandlingssystemer er det en spørringsoptimalisering som sjekker om en indeks på en bestemt kolonne vil få spørringen til å kjøre raskere. La oss deretter lære de beste fremgangsmåtene for databasedesign.