Zastanawiasz się, jak usprawnić działanie zapytań do bazy danych? Odkryj, jak za pomocą języka SQL skonstruować indeks bazy danych, który zoptymalizuje szybkość wyszukiwania i pobierania informacji.
Podczas operacji pobierania danych z tabeli bazodanowej, często zachodzi konieczność filtrowania wyników na podstawie konkretnych kolumn.
Wyobraź sobie, że tworzysz zapytanie SQL, które ma za zadanie wybrać dane spełniające określone kryteria. Standardowo, wykonanie takiego zapytania wiąże się z przeglądaniem całej tabeli, w poszukiwaniu rekordów pasujących do warunku, a dopiero później zwracane są wyniki.
W przypadku dużych tabel, zawierających miliony wierszy, takie podejście może być bardzo nieefektywne. Rozwiązaniem jest utworzenie indeksu bazy danych, który znacząco przyspiesza realizację zapytań.
Czym jest indeks bazy danych?
Kiedy chcemy odnaleźć konkretne słowo w książce, czy przeszukujemy ją strona po stronie, aż natrafimy na szukany termin? Oczywiście, że nie.
Zamiast tego, korzystamy z indeksu, który wskazuje, na których stronach znajduje się dane hasło, dzięki czemu możemy od razu przejść do interesujących nas miejsc. Indeks w bazie danych działa na bardzo zbliżonej zasadzie.
Indeks bazy danych to zbiór odnośników do danych, które są uporządkowane w sposób ułatwiający i przyspieszający ich wyszukiwanie. W praktyce, indeksy bazodanowe są implementowane przy użyciu struktur danych, takich jak drzewa B+ czy tablice haszujące. Dzięki temu znacząco zwiększa się szybkość i sprawność operacji wyszukiwania informacji.
Tworzenie indeksu bazy danych w SQL
Skoro już wiemy, czym jest indeks i jak wpływa na szybkość wyszukiwania, nauczmy się, jak tworzyć indeksy w języku SQL.
Podczas wykonywania operacji filtrowania, kiedy używamy klauzuli WHERE do określenia warunku wyszukiwania, często skupiamy się na konkretnych kolumnach.
CREATE INDEX nazwa_indeksu ON tabela (kolumna)
Gdzie:
- nazwa_indeksu to nazwa, którą nadajemy tworzonemu indeksowi.
- tabela odnosi się do tabeli w relacyjnej bazie danych, w której tworzymy indeks.
- kolumna to nazwa kolumny, na której tworzymy indeks.
W zależności od potrzeb, można tworzyć indeksy na kilku kolumnach, zwane indeksami wielokolumnowymi. Oto jak to zrobić:
CREATE INDEX nazwa_indeksu ON tabela (kolumna_1, kolumna_2,...,kolumna_k)
Przejdźmy teraz do praktycznego przykładu.
Zrozumienie wzrostu wydajności dzięki indeksom
Aby zaobserwować korzyści płynące z indeksów, musimy dysponować tabelą z dużą ilością rekordów. Przykłady, które zostaną przedstawione, będą oparte o SQLite, ale z powodzeniem można je zastosować także w innych RDBMS, takich jak PostgreSQL czy MySQL.
Wypełnianie tabeli bazy danych danymi
Możemy skorzystać z wbudowanego modułu random w Pythonie do tworzenia i dodawania rekordów do bazy danych, jednak użyjemy biblioteki Faker, aby wygenerować i wprowadzić do tabeli milion wierszy.
Poniższy skrypt w języku Python:
- Tworzy i nawiązuje połączenie z bazą danych Customer_db.
- Tworzy tabelę o nazwie „customers” z polami: imię, nazwisko, miasto i liczba_zamówień.
- Generuje syntetyczne dane i wprowadza milion rekordów do tabeli „customers”.
Kod źródłowy znajdziesz również na GitHubie.
# main.py
# importy
import sqlite3
from faker import Faker
import random
# połączenie z bazą danych
db_conn = sqlite3.connect('customer_db.db')
db_cursor = db_conn.cursor()
# tworzenie tabeli
db_cursor.execute('''CREATE TABLE customers (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
city TEXT,
num_orders INTEGER)''')
# tworzenie obiektu Faker
fake = Faker()
Faker.seed(27)
# generowanie i wstawianie miliona rekordów
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))
# zatwierdzenie zmian i zamknięcie połączenia
db_conn.commit()
db_cursor.close()
db_conn.close()
Teraz możemy przejść do wykonywania zapytań.
Tworzenie indeksu na kolumnie „Miasto”
Załóżmy, że chcesz wyszukać dane klientów, filtrując wyniki po kolumnie „miasto”. Twoje zapytanie SELECT będzie wyglądało mniej więcej tak:
SELECT kolumna(y) FROM customers WHERE warunek;
Utwórzmy zatem indeks city_idx na kolumnie „city” w tabeli „customers”:
CREATE INDEX city_idx ON customers (city);
⚠ Proces tworzenia indeksu może zająć nieco czasu, ale jest to operacja jednorazowa. Korzyści w postaci przyspieszenia zapytań, szczególnie tych często wykorzystujących kolumnę „miasto”, są nie do przecenienia.
Usuwanie indeksu bazy danych
Indeks można usunąć za pomocą polecenia DROP INDEX:
DROP INDEX nazwa_indeksu;
Porównanie czasu zapytań z indeksem i bez
W celu zmierzenia czasu wykonywania zapytań w skrypcie Pythona, można użyć domyślnego licznika czasu.
Alternatywnie, można skorzystać z klienta wiersza poleceń sqlite3. Aby pracować z bazą „customer_db.db” w terminalu, uruchom następujące polecenie:
$ sqlite3 customer_db.db;
Aby przybliżyć czas wykonywania, można skorzystać z wbudowanej funkcji .timer w sqlite3:
sqlite3 > .timer on
> <zapytanie tutaj>
Ponieważ stworzyliśmy indeks na kolumnie „miasto”, zapytania filtrujące po tej kolumnie w klauzuli WHERE będą znacznie szybsze.
Najpierw uruchom zapytania bez indeksu, a następnie utwórz indeks i uruchom je ponownie. Zwróć uwagę na różnicę w czasach wykonania. Oto kilka przykładów:
Czas zapytania bez indeksuCzas zapytania z indeksemSELECT * FROM customers
WHERE city LIKE „Nowy%”
LIMIT 10;0,100 s0,001 sSELECT * FROM customers
WHERE city=’New Wesley’;0,148 s0,001 sSELECT * FROM customers
WHERE city IN („New Wesley”, „New Steven”, „New Carmenmouth”);0,247 s0,003 s
Jak widać, czasy wyszukiwania z indeksem są znacząco krótsze niż te bez indeksu w kolumnie „miasto”.
Najlepsze praktyki tworzenia i używania indeksów
Zawsze należy ocenić, czy zysk wydajności jest większy niż koszt związany z tworzeniem indeksu. Oto kilka wytycznych:
- Dobieraj odpowiednie kolumny do indeksowania. Unikaj nadmiernej liczby indeksów, ponieważ mogą one generować niepotrzebne obciążenie.
- Każda aktualizacja indeksowanej kolumny powoduje konieczność aktualizacji samego indeksu. Zatem, choć indeksy przyspieszają wyszukiwanie, to jednocześnie spowalniają operacje wstawiania i modyfikowania danych. Dlatego indeksuj kolumny często wykorzystywane do wyszukiwania, ale rzadko modyfikowane.
Kiedy nie należy tworzyć indeksu?
W tym momencie powinieneś mieć już dobrą orientację, kiedy i jak tworzyć indeksy. Podsumujmy więc, kiedy indeks może nie być konieczny:
- Gdy tabela jest niewielka i nie zawiera dużej liczby wierszy, skanowanie całej tabeli w celu pobrania danych nie jest aż tak kosztowne.
- Nie twórz indeksów dla kolumn, które są rzadko wykorzystywane do wyszukiwania. W przypadku kolumn, które nie są często odpytywane, koszt tworzenia i utrzymywania indeksu może przewyższyć potencjalne korzyści.
Podsumowując
Spójrzmy na to, czego się nauczyliśmy:
- W trakcie wysyłania zapytań do bazy danych, często zachodzi konieczność filtrowania po konkretnych kolumnach. Indeks bazy danych, utworzony na takich kolumnach, może znacznie usprawnić działanie zapytań.
- Aby utworzyć indeks na pojedynczej kolumnie, użyj składni:
CREATE INDEX nazwa_indeksu ON tabela (kolumna). Aby utworzyć indeks wielokolumnowy:CREATE INDEX nazwa_indeksu ON tabela (kolumna_1, kolumna_2,...,kolumna_k). - Każda modyfikacja indeksowanej kolumny wiąże się z koniecznością aktualizacji indeksu. Dlatego wybieraj odpowiednie kolumny – takie, po których często filtrujesz, a które są rzadko modyfikowane.
- W przypadku niewielkich tabel bazodanowych, koszt tworzenia i utrzymania indeksu może być wyższy niż zysk wydajności.
W większości współczesnych systemów zarządzania bazami danych, optymalizator zapytań ocenia, czy użycie indeksu na danej kolumnie przyspieszy wykonanie zapytania. Zatem poznanie tych zagadnień, jest kluczowe dla projektowania optymalnych struktur baz danych.
newsblog.pl