[Explained] Jak utworzyć indeks bazy danych w SQL

Chcesz przyspieszyć zapytania do bazy danych? Dowiedz się, jak utworzyć indeks bazy danych przy użyciu języka SQL i zoptymalizować wydajność zapytań oraz przyspieszyć pobieranie danych.

Gdy pobierasz dane z tabeli bazy danych, będziesz musiał częściej filtrować na podstawie określonych kolumn.

Załóżmy, że piszesz zapytanie SQL w celu pobrania danych na podstawie określonych warunków. Domyślnie uruchomienie zapytania uruchamia skanowanie całej tabeli do momentu znalezienia wszystkich rekordów spełniających warunek, a następnie zwraca wyniki.

Może to być bardzo nieefektywne, gdy trzeba wysłać zapytanie do dużej tabeli bazy danych zawierającej kilka milionów wierszy. Możesz przyspieszyć takie zapytania, tworząc indeks bazy danych.

Spis treści:

Co to jest indeks bazy danych?

Kiedy chcesz znaleźć określony termin w książce, czy wykonasz skanowanie całej książki — jedna strona po drugiej — w poszukiwaniu konkretnego terminu? Cóż, nie.

Zamiast tego przeszukasz indeks, aby dowiedzieć się, które strony odwołują się do danego terminu, i przeskoczysz bezpośrednio do tych stron. Indeks w bazie danych działa bardzo podobnie do indeksów w książce.

Indeks bazy danych to zbiór wskaźników lub odniesień do rzeczywistych danych, ale posortowanych w sposób przyspieszający wyszukiwanie danych. Wewnętrznie indeks bazy danych można zaimplementować przy użyciu struktur danych, takich jak drzewa B+ i tabele mieszające. Dlatego indeks bazy danych poprawia szybkość i efektywność operacji wyszukiwania danych.

Tworzenie indeksu bazy danych w SQL

Teraz, gdy wiemy, czym jest indeks bazy danych i jak może przyspieszyć wyszukiwanie danych, nauczmy się tworzyć indeks bazy danych w języku SQL.

Podczas wykonywania operacji filtrowania — przez określenie warunku pobierania za pomocą klauzuli WHERE — możesz chcieć częściej wyszukiwać określoną kolumnę niż inne.

CREATE INDEX index_name ON table (column)

Tutaj,

nazwa_indeksu to nazwa indeksu, który ma zostać utworzony
table odnosi się do tabeli w relacyjnej bazie danych
kolumna odnosi się do nazwy kolumny w tabeli bazy danych, na której musimy utworzyć indeks.

Można także tworzyć indeksy dla wielu kolumn — indeks wielokolumnowy — w zależności od wymagań. Oto składnia, aby to zrobić:

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

Przejdźmy teraz do praktycznego przykładu.

Zrozumienie wzrostu wydajności indeksu bazy danych

Aby zrozumieć zalety tworzenia indeksu, musimy utworzyć tabelę bazy danych z dużą liczbą rekordów. Przykłady kodu są dla SQLite. Ale możesz także użyć innego wybranego RDBMS, takiego jak PostgreSQL i MySQL’a.

Wypełnianie tabeli bazy danych rekordami

Możesz także użyć wbudowanego modułu losowego Pythona do tworzenia i wstawiania rekordów do bazy danych. Jednak skorzystamy Fałszerz aby wypełnić tabelę bazy danych milionem wierszy.

Poniższy skrypt Pythona:

Tworzy i łączy się z bazą danych Customer_db.
Utwórz tabelę klientów z polami: imię, nazwisko, miasto i liczba_zamówień.
Generuje dane syntetyczne i wstawia dane — milion rekordów — do tabeli klientów.

Możesz także znaleźć kod na GitHubie.

# 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()

Teraz możemy rozpocząć zadawanie zapytań.

Tworzenie indeksu w kolumnie Miasto

Załóżmy, że chcesz uzyskać informacje o kliencie, filtrując je według kolumny miasta. Twoje zapytanie SELECT będzie wyglądać następująco:

SELECT column(s) FROM customers
WHERE condition;

Utwórzmy więc city_idx w kolumnie miasta w tabeli klientów:

CREATE INDEX city_idx ON customers (city);

⚠ Utworzenie indeksu zajmuje niemałą ilość czasu i jest operacją jednorazową. Jednak korzyści w zakresie wydajności, gdy potrzebujesz dużej liczby zapytań – poprzez filtrowanie według kolumny miasta – będą znaczące.

Usuwanie indeksu bazy danych

Aby usunąć indeks, możesz użyć instrukcji DROP INDEX w następujący sposób:

DROP INDEX index_name;

Porównanie czasów zapytań z indeksem i bez niego

Jeśli chcesz uruchamiać zapytania w skrypcie Pythona, możesz użyć domyślnego licznika czasu, aby uzyskać czasy wykonania zapytań.

Alternatywnie możesz uruchomić zapytania za pomocą klienta wiersza poleceń sqlite3. Aby pracować z customer_db.db przy użyciu klienta wiersza poleceń, uruchom następujące polecenie w terminalu:

$ sqlite3 customer_db.db;

Aby uzyskać przybliżone czasy wykonania, możesz użyć funkcji .timer wbudowanej w sqlite3 w następujący sposób:

sqlite3 > .timer on
        > <query here>

Ponieważ stworzyliśmy indeks na kolumnie miasta, zapytania, które wymagają filtrowania na podstawie kolumny miasta w klauzuli WHERE, będą znacznie szybsze.

Najpierw uruchom zapytania. Następnie utwórz indeks i ponownie uruchom zapytania. Zanotuj czasy wykonania w obu przypadkach. Oto kilka przykładów:

QueryTime bez IndexTime z IndexSELECT * OD klientów
GDZIE miasto LIKE „Nowy%”
LIMIT 10;0,100 s0,001 sWYBIERZ * OD klientów
WHERE miasto=’New Wesley’;0,148 s0,001 sWYBIERZ * OD klientów
GDZIE miasto W („New Wesley”, „New Steven”, „New Carmenmouth”);0,247 s0,003 s

Widzimy, że czasy wyszukiwania z indeksem są o kilka rzędów szybsze niż te bez indeksu w kolumnie miasto.

Najlepsze praktyki tworzenia i używania indeksów baz danych

Należy zawsze sprawdzić, czy wzrost wydajności jest większy niż narzut związany z tworzeniem indeksu bazy danych. Oto kilka najlepszych praktyk, o których warto pamiętać:

Wybierz odpowiednie kolumny, aby utworzyć indeks. Unikaj tworzenia zbyt wielu indeksów ze względu na znaczny narzut.
Za każdym razem, gdy indeksowana kolumna jest aktualizowana, odpowiedni indeks również powinien zostać zaktualizowany. Tak więc utworzenie indeksu bazy danych (chociaż przyspiesza pobieranie) znacznie spowalnia operacje wstawiania i aktualizacji. Dlatego należy tworzyć indeksy dla kolumn, które są często przeszukiwane, ale rzadko aktualizowane.

Kiedy nie należy tworzyć indeksu?

Do tej pory powinieneś wiedzieć, kiedy i jak utworzyć indeks. Ale określmy również, kiedy indeks bazy danych może nie być konieczny:

Gdy tabela bazy danych jest mała i nie zawiera dużej liczby wierszy, skanowanie całej tabeli w celu pobrania danych nie jest tak kosztowne.
Nie twórz indeksów dla kolumn, które są rzadko używane do wyszukiwania. Podczas tworzenia indeksów w kolumnach, które nie są często przeszukiwane, koszt tworzenia i utrzymywania indeksu przewyższa wzrost wydajności.

Podsumowując

Przyjrzyjmy się temu, czego się nauczyliśmy:

Podczas wysyłania zapytań do bazy danych w celu pobrania danych może zaistnieć potrzeba częstszego filtrowania na podstawie określonych kolumn. Indeks bazy danych dla takich często odpytywanych kolumn może poprawić wydajność.
Aby utworzyć indeks na pojedynczej kolumnie, użyj składni: CREATE INDEX nazwa_indeksu ON tabela (kolumna). Jeśli chcesz utworzyć indeks wielokolumnowy, użyj: CREATE INDEX nazwa_indeksu ON tabela (kolumna_1, kolumna_2,…,kolumna_k)
Za każdym razem, gdy modyfikowana jest indeksowana kolumna, odpowiedni indeks również powinien zostać zaktualizowany. Dlatego wybierz odpowiednie kolumny – często odpytywane i znacznie rzadziej aktualizowane – aby utworzyć indeks.
Jeśli tabela bazy danych jest stosunkowo mniejsza, koszt tworzenia, utrzymywania i aktualizowania indeksu będzie większy niż wzrost wydajności.

W większości nowoczesnych systemów zarządzania bazami danych istnieje optymalizator zapytań, który sprawdza, czy indeks w określonej kolumnie przyspieszy wykonanie zapytania. Następnie poznajmy najlepsze praktyki dotyczące projektowania baz danych.