[Explained] Jak vytvořit index databáze v SQL

Obsah

Potřebujete zrychlit své databázové dotazy? Seznamte se s procesem vytváření databázových indexů pomocí SQL, což je klíč k optimalizaci výkonu a urychlení načítání dat.

Při práci s daty v databázové tabulce se často setkáte s potřebou filtrovat záznamy na základě konkrétních sloupců.

Představte si situaci, kdy formulujete SQL dotaz pro získání dat na základě určitých kritérií. Ve výchozím nastavení databáze prochází celou tabulkou, dokud nenajde všechny záznamy vyhovující vašemu požadavku, a poté je zobrazí.

Tento přístup může být značně neefektivní, zvláště pokud pracujete s rozsáhlou tabulkou obsahující miliony řádků. V takových situacích je vytvoření databázového indexu skvělým řešením pro zrychlení dotazů.

Co je databázový index?

Když hledáte konkrétní termín v knize, pravděpodobně neprocházíte každou stránku zvlášť.

Místo toho využijete rejstřík, který vám ukáže, na kterých stránkách se daný termín vyskytuje, a vy tak můžete přejít přímo na požadovaná místa. Databázový index funguje na podobném principu jako rejstřík v knize.

Databázový index představuje soubor ukazatelů nebo odkazů na skutečná data, ale uspořádaných tak, aby bylo možné data načítat mnohem rychleji. Indexy jsou interně implementovány pomocí datových struktur, jako jsou B+ stromy a hashovací tabulky. Díky tomu index databáze významně zvyšuje rychlost a efektivitu operací načítání dat.

Vytvoření databázového indexu v SQL

Nyní, když již víme, co je databázový index a jak může zrychlit načítání dat, podíváme se, jak se vytváří indexy v jazyce SQL.

Při filtrování dat, kdy používáte klauzuli WHERE pro specifikaci podmínky, se často stává, že potřebujete dotazovat konkrétní sloupec častěji než ostatní.

CREATE INDEX název_indexu ON tabulka (sloupec)

V tomto případě:

název_indexu je název vytvářeného indexu.
tabulka odkazuje na tabulku v relační databázi.
sloupec odkazuje na název sloupce v databázové tabulce, pro který index vytváříme.

Podle vašich potřeb můžete vytvářet indexy i pro více sloupců najednou. Jedná se o tzv. index s více sloupci. Zde je syntaxe pro jeho vytvoření:

CREATE INDEX název_indexu ON tabulka (sloupec_1, sloupec_2,..., sloupec_k)

Nyní se podíváme na praktický příklad.

Výhody databázových indexů pro výkon

Pro demonstraci výhod vytvoření indexu si nejdříve vytvoříme databázovou tabulku s velkým počtem záznamů. V příkladech kódu je použita databáze SQLite, ale můžete použít i jiné RDBMS, jako například PostgreSQL nebo MySQL.

Naplnění databázové tabulky daty

K vytváření a vkládání dat do databáze můžete použít například náhodný generátor modulu Python. My však využijeme Faker pro naplnění databázové tabulky milionem řádků.

Následující Python skript:

Vytvoří databázi customer_db a připojí se k ní.
Vytvoří tabulku customers se sloupci: first_name, last_name, city a num_orders.
Vygeneruje syntetická data a vloží jeden milion záznamů do tabulky customers.

Kód naleznete také na GitHubu.


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

Nyní se můžeme pustit do dotazování.

Vytvoření indexu pro sloupec města

Řekněme, že potřebujete získat informace o zákaznících na základě filtru přes sloupec city. Váš SELECT dotaz by vypadal takto:

SELECT sloupec(sloupce) FROM customers
WHERE podmínka;

Vytvoříme tedy index city_idx pro sloupec city v tabulce customers:

CREATE INDEX city_idx ON customers (city);

⚠ Vytvoření indexu vyžaduje určitý čas, ale jedná se o jednorázovou operaci. Zvýšení výkonu při následných dotazech, které filtrují podle sloupce city, je však výrazné.

Odstranění databázového indexu

Pro odstranění indexu můžete použít příkaz DROP INDEX následujícím způsobem:

DROP INDEX název_indexu;

Porovnání časů dotazování s indexem a bez něj

Pro měření časů provádění dotazů v rámci Python skriptu můžete použít standardní časovač.

Alternativně můžete spouštět dotazy pomocí příkazového řádku klienta sqlite3. Pro práci s databází customer_db.db pomocí příkazového řádku spusťte v terminálu příkaz:

$ sqlite3 customer_db.db;

Pro získání přibližných časů provádění můžete použít funkci .timer, která je součástí sqlite3, a to následujícím způsobem:

sqlite3 > .timer on
        > <dotaz>

Vzhledem k tomu, že jsme vytvořili index pro sloupec city, budou dotazy, které filtrují data pomocí tohoto sloupce, výrazně rychlejší.

Nejprve spusťte dotazy bez indexu, poté vytvořte index a spusťte dotazy znovu. Poznamenejte si časy provedení v obou případech. Zde jsou příklady:

Dotaz	Čas bez indexu	Čas s indexem
`SELECT * FROM customers WHERE city LIKE 'New%' 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

Jak vidíte, časy načítání dat s indexem jsou o několik řádů kratší než bez indexu ve sloupci city.

Doporučené postupy pro vytváření a používání databázových indexů

Vždy byste měli posoudit, zda je zrychlení výkonu vyšší než režie na vytvoření databázového indexu. Zde je několik doporučených postupů:

Pro vytvoření indexu vyberte správné sloupce. Vyvarujte se vytváření příliš mnoha indexů z důvodu nadměrné režie.
Při každé aktualizaci indexovaného sloupce se musí aktualizovat i odpovídající index. Vytvoření databázového indexu (i když zrychlí načítání) tak významně zpomalí vkládání a aktualizace dat. Proto byste měli vytvářet indexy pro sloupce, které jsou často dotazovány, ale zřídka aktualizovány.

Kdy index nevytvářet?

Nyní již máte představu o tom, kdy a jak vytvářet index. Pro úplnost si uveďme případy, kdy index databáze nemusí být nutný:

Pokud je tabulka malá a neobsahuje velké množství řádků, není průchod celou tabulkou při načítání dat tak časově náročný.
Nevytvářejte indexy na sloupcích, které se pro filtrování dat používají zřídka. Vytvoření indexu pro sloupce, které nejsou často dotazovány, zvýší režii bez výrazného zisku ve výkonu.

Shrnutí

Zopakujme si klíčové body, které jsme si probrali:

Při dotazování databáze za účelem načtení dat, budete možná muset filtrovat data podle určitých sloupců. Indexy na těchto sloupcích mohou výrazně zvýšit výkon dotazů.
Syntaxe pro vytvoření indexu pro jeden sloupec je: CREATE INDEX název_indexu ON tabulka (sloupec). Pro vytvoření indexu nad více sloupci použijte: CREATE INDEX název_indexu ON tabulka (sloupec_1, sloupec_2,...,sloupec_k).
Při každé úpravě indexovaného sloupce se musí aktualizovat i odpovídající index. Proto vybírejte správné sloupce – ty, které se často dotazují a méně často aktualizují.
Pokud je tabulka relativně malá, režie na vytváření, správu a aktualizaci indexu může převážit nad zvýšením výkonu.

Ve většině moderních systémů pro správu databází je optimalizátor dotazů, který ověřuje, zda index nad určitým sloupcem urychlí provádění dotazu. V dalším kroku se podíváme na doporučené postupy pro návrh databází.