Indexy

Pár slov na začátek

V oblasti databázových systémů máme pod pojmem index na mysli databázovou konstrukci, která slouží ke zrychlování dotazovacích procesů. Indexy se používají na určitý sloupec v tabulce. Označují se také pojmem KEY.

Indexy nabízejí něco za něco… Na jednu stranu zrychlují operace typu SELECT, na druhou stranu mají však nevýhody, mezi nimiž jsou zvýšená spotřeba databázového prostoru a zpomalování zápisu dat, tedy například u operací typu INSERT a UPDATE. Je to proto, že při vytvoření indexu se v databázi vytvoří kopie dat z tabulky v jiné struktuře. No a pokaždé, když se provede zápis pomocí INSERT či UPDATE, musí databázový systém provést zápis na obou místech, jak v originálních datech, tak i v kopiích těchto dat. Z tohoto důvodu je dobré indexy šetřit a používat je pouze tam, kde je to potřeba.

Pojďme si něco říct o základních typech indexů. Každý typ indexu má nějaký předem určený význam, který je pro něj specifický. Společně si ukážeme následující indexy:

  • INDEX
  • PRIMARY
  • UNIQUE
  • FULLTEXT

INDEX

Jedná se o obecný index bez konkrétní specializace. Využíváme jej pro rychlejší vyhledávání dat v tabulkách. Řekněme, že máme hotovu tabulku zamestnanec. Na základě konzultace s klientem už nyní víme, že se v budoucím systému budou zaměstnanci vyhledávat především podle příjmení a čísla zaměstnance. Je tedy nasnadě nasadit tento obecný INDEX na sloupce prijmeni a cislo_zamestnance. Tato optimalizace nám zajistí rychlejší vyhledávání mezi četnými záznamy v tabulce.

Indexaci můžeme vytvořit následujícím skriptem, v němž na šestém řádku snadno indexujeme sloupec prijmeni:

SQL
CREATE TABLE zamestnanec(
id INT AUTO_INCREMENT PRIMARY KEY,
cislo_zamestnance CHAR(10) NOT NULL,
jmeno VARCHAR(50) NOT NULL,
prijmeni VARCHAR(50) NOT NULL,
INDEX(prijmeni)
);

Pokud bychom chtěli indexovat číslo zaměstnance i příjmení naráz, můžeme to udělat v rámci jednoho příkazu, jak je vidět na šestém řádku následujícího skriptu:

SQL
CREATE TABLE zamestnanec(
id INT AUTO_INCREMENT PRIMARY KEY,
cislo_zamestnance CHAR(10) NOT NULL,
jmeno VARCHAR(50) NOT NULL,
prijmeni VARCHAR(50) NOT NULL,
INDEX(cislo_zamestnance, prijmeni)
);

PRIMARY

S tímto indexem jsme se už zřejmě setkali. Jedná se o index, který slouží o jedinečnou identifikaci řádků v rámci tabulky, tedy plní roli primárního klíče. Z toho jaksi plyne, že se tento typ indexu může v tabulce vyskytovat jen jednou. Tento index si zároveň hlídá, že je každá hodnota v jeho sloupci unikátní (nevyskytuje se na žádném jiném řádku v tomto indexovaném sloupci).

Index typu PRIMARY se používá obvykle při vytváření tabulky. Využít můžeme následující dva způsoby, které vedou ke stejnému výsledku. Prvním způsobem můžeme označit primárním indexem daný sloupec na stejném řádku slovy PRIMARY KEY, jak je vidět na řádku číslo 2 následujícího skriptu: 

SQL
CREATE TABLE zamestnanec(
id INT AUTO_INCREMENT PRIMARY KEY,
cislo_zamestnance CHAR(10) NOT NULL,
jmeno VARCHAR(50) NOT NULL,
prijmeni VARCHAR(50) NOT NULL
);

Někomu může přijít přehlednější, když se primární klíč, cizí klíče a všechny další indexy zapisují na konci příkazu CREATE TABLE. Toho můžeme docílit druhou variantou, na šestém řádku vytvoříme index PRIMARY pomocí klíčových slov PRIMARY KEY a do kulatých závorek uvedeme název sloupce, který se má stát primárním klíčem, tedy bude indexován jako PRIMARY:

SQL
CREATE TABLE zamestnanec(
id INT AUTO_INCREMENT,
cislo_zamestnance CHAR(10) NOT NULL,
jmeno VARCHAR(50) NOT NULL,
prijmeni VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
);

UNIQUE

Tento typ indexu má za úkol pohlídat, že každá hodnota v jeho sloupci je unikátní (nevyskytuje se na žádném jiném řádku v tomto indexovaném sloupci), stejně jako u indexu typu PRIMARY. Index typu UNIQUE můžeme použít u více sloupců v rámci jedné tabulky.

Mějme například tabulku uzivatel. Každý uživatel se může v našem webovém systému zaregistrovat, ale nesmí přitom použít email, který by již v naší databázi existoval. K tomu se nám bude skvěle hodit index typu UNIQUE, který nasadíme na sloupec email, zde na šestém řádku:

SQL
CREATE TABLE uzivatel(
id INT AUTO_INCREMENT PRIMARY KEY,
jmeno VARCHAR(50) NOT NULL,
prijmeni VARCHAR(50) NOT NULL,
email VARCHAR(200) NOT NULL,
UNIQUE(email)
);

FULLTEXT

Jak už název napovídá, tento index slouží k optimalizovanějšímu fulltextovému vyhledávání v datech. V rámci vyhledávání se nerozlišují velká a malá písmena, jedná se tedy o takzvaný “case insensitive” proces. FULLTEXT index umožňuje vyhledávání pomocí klíčových slov a frází, není tedy nutno používat přesné hodnoty, jako je tomu u obecného INDEXU. Nasazení FULLTEXT indexu na sloupce však na druhou stranu spotřebovává více paměti v databázi.

Nyní si představme, že vytváříme tabulku pro eshop s oblečením. Tabulka nese název zbozi, a slouží k evidenci základních informací o nabízených produktech. Předpokládáme, že zákazníci budou velmi často fulltextově vyhledávat názvy našeho zboží. Proto nasadíme tento index na sloupec nazev. Nasazení indexu FULLTEXT provedeme obdobně jako u jiných indexových typů:

SQL
CREATE TABLE zbozi(
id INT AUTO_INCREMENT PRIMARY KEY,
nazev VARCHAR(150) NOT NULL,
barva VARCHAR(50) NOT NULL,
velikost VARCHAR(50) NOT NULL,
FULLTEXT(nazev)
);

Ukázka fulltext vyhledávání

Předchozím skriptem jsme vytvořili tabulku, která má nasazený index typu FULLTEXT na sloupec nazev. Nyní toho můžeme využít a vyzkoušet si fulltextové vyhledávání dat z tabulky níže.

IDNázevBarvaVelikost
1Tričko s potiskemBíláM
2Mikina s kapucíČernáL
3TeplákyModrá32
4ŠortkyKaki36
5ŠatyKvětinový vzorS
6SakoBéžová50
7TričkoČervenáM
8KraťasyModrá38
9SvetrŠedáL
10ČepiceČernáUniverzální

K fulltextovému vyhledávání využíváme standardní příkaz SELECT, který bude v klauzuli WHERE obsahovat dvě nová slovíčka MATCH a AGAINST. Těmi říkáme, že chceme vypsat všechny záznamy, kde se data ze sloupce nazev budou shodovat se zadaným výrazem ‘tričko‘:

SQL
SELECT * FROM zbozi
WHERE MATCH(nazev) AGAINST('tričko');

Výsledkem bude výpis řádků s ID 1 a 7, které obsahují hodnoty tričko:

IDNázevBarvaVelikost
1Tričko s potiskemBíláM
7TričkoČervenáM

Dodatečné přidání indexu

V předchozích ukázkách jsme vždy indexovali sloupce v rámci vytváření nové tabulky. Co ale dělat, když potřebujeme přidat index do existující tabulky? Odpovědí je použití příkazu ALTER TABLE, který známe z SQL podkategorie DDL. Zkusme například indexovat (obecným indexem) sloupec prijmeni v tabulce zamestnanec:

SQL
ALTER TABLE zamestnanec
ADD INDEX(prijmeni);

S jinými indexy by to bylo stejné. Za klíčové slovíčko ADD by se napsal konkrétní typ indexu.

Odebrání indexu

Na závěr této lekce si ukážeme, jak snadno zrušit existující index některého sloupce tabulky. Ukažme si to na tabulce z předchozího příkladu, odebereme index ze sloupce prijmeni. Pozor, při odebírání indexu se nepíší kulaté závorky:

SQL
ALTER TABLE zamestnanec
DROP INDEX prijmeni;