Čo je to index?
Indexy v MySQL triedia dáta organizovaným spôsobom. Sú vytvorené v stĺpcoch, ktoré budú použité na filtrovanie údajov. Predstavte si index ako abecedne zoradený zoznam. Je jednoduchšie vyhľadať názvy, ktoré boli zoradené v abecednom poradí, ako tie, ktoré nie sú zoradené.
Používanie indexu v tabuľkách, ktoré sú často aktualizované, môže mať za následok zlý výkon. Je to preto, že MySQL vytvára nový blok indexov zakaždým, keď sú v tabuľke pridané alebo aktualizované údaje. Vo všeobecnosti by sa indexy mali používať v tabuľkách, ktorých údaje sa často nemenia, ale vo vybraných vyhľadávacích dotazoch sa často používajú.
Čo používa index?
Nikto nemá rád pomalé systémy. Vysoký výkon systému má prvoradý význam takmer vo všetkých databázových systémoch. Väčšina podnikateľov investuje veľké prostriedky do hardvéru, aby vyhľadávanie a manipulácia s dátami mohla byť rýchlejšia. Investície do hardvéru však môže podnik obmedziť. Optimalizácia databázy je lacnejšie a lepšie riešenie.
Pomalosť času odozvy je zvyčajne spôsobená náhodným ukladaním záznamov do databázových tabuliek. Vyhľadávacie dotazy musia prechádzať celým náhodne uloženým záznamom jeden po druhom, aby našli požadované údaje. To má za následok slabý výkon databáz, pokiaľ ide o získavanie údajov z veľkých tabuliek. Preto sa používajú indexy, ktoré triedia údaje, aby sa uľahčilo ich vyhľadávanie.
Syntax: Vytvorte index
Indexy je možné definovať dvoma spôsobmi
- V čase vytvorenia tabuľky
- Po vytvorení tabuľky
Pre našu myflixdb očakávame veľa vyhľadávaní v databáze s celým menom.
Do indexu v novej tabuľke „members_indexed“ pridáme stĺpec „full_names“.
To nám pomáha dosiahnuť skript uvedený nižšie.
CREATE TABLE `members_indexed` (`membership_number` int(11) NOT NULL AUTO_INCREMENT,`full_names` varchar(150) DEFAULT NULL,`gender` varchar(6) DEFAULT NULL,`date_of_birth` date DEFAULT NULL,`physical_address` varchar(255) DEFAULT NULL,`postal_address` varchar(255) DEFAULT NULL,`contact_number` varchar(75) DEFAULT NULL,`email` varchar(255) DEFAULT NULL,PRIMARY KEY (`membership_number`),INDEX(full_names)) ENGINE=InnoDB;
Vyššie uvedený skript SQL vykonajte v pracovnom stole MySQL oproti priečinku „myflixdb“.
Obnovením myflixdb sa zobrazí novovytvorená tabuľka s názvom members_indexed.
Tabuľka „Note“ members_indexed má v uzle indexov „full_names“.
Keď sa členská základňa rozšíri a počet záznamov sa zvýši, vyhľadávacie dotazy v tabuľke members_indexed, ktoré používajú klauzuly WHERE a ORDER BY, budú oveľa rýchlejšie v porovnaní s tými, ktoré sa vykonali v tabuľke členov bez definovaného indexu.
Pridajte základnú syntax indexu
Vyššie uvedený príklad vytvoril index pri definovaní databázovej tabuľky. Predpokladajme, že už máme definovanú tabuľku a vyhľadávacie dotazy sú v nej veľmi pomalé. Vrátenie výsledkov trvá príliš dlho. Po preskúmaní problému zistíme, že môžeme výrazne zlepšiť výkon systému vytvorením INDEXU v najbežnejšie používanom stĺpci v klauzule WHERE.
Na pridanie indexu môžeme použiť nasledujúci dotaz
CREATE INDEX id_index ON table_name(column_name);
Predpokladajme, že vyhľadávacie dotazy v tabuľke filmov sú veľmi pomalé a na urýchlenie dotazov chceme použiť index „názvu filmu“, môžeme to dosiahnuť nasledujúcim skriptom.
CREATE INDEX `title_index` ON `movies`(`title`);
Vykonaním vyššie uvedeného dotazu sa vytvorí index v poli nadpisu v tabuľke filmov.
To znamená, že všetky vyhľadávacie dotazy na stole filmov pomocou „nadpisu“ budú rýchlejšie.
Vyhľadávacie dotazy v ďalších poliach v tabuľke filmov však budú v porovnaní s dopytmi založenými na indexovanom poli stále pomalšie.
Poznámka: V prípade potreby môžete vytvoriť indexy vo viacerých stĺpcoch v závislosti od polí, ktoré chcete použiť pre svoj databázový vyhľadávací stroj.
Ak chcete zobraziť indexy definované v konkrétnej tabuľke, môžete na to použiť nasledujúci skript.
SHOW INDEXES FROM table_name;
Poďme sa teraz pozrieť na všetky indexy definované v tabuľke filmov v myflixdb.
SHOW INDEXES FROM `movies`;
Vykonanie vyššie uvedeného skriptu v pracovnej ploche MySQL proti myflixdb nám poskytne výsledky vytvoreného indexu.
Poznámka: Primárny a cudzí kľúč v tabuľke už boli indexované serverom MySQL. Každý index má svoj vlastný jedinečný názov a zobrazuje sa tiež stĺpec, v ktorom je definovaný.
Syntax: Drop index
Príkaz drop sa používa na odstránenie už definovaných indexov z tabuľky.
Môžu sa vyskytnúť prípady, keď ste už definovali index pre tabuľku, ktorá sa často aktualizuje. Možno budete chcieť odstrániť indexy z takejto tabuľky, aby ste zlepšili výkonnosť dotazov UPDATE a INSERT. Základná syntax použitá na vloženie indexu do tabuľky je nasledovná.
DROP INDEX `index_id` ON `table_name`;
Pozrime sa teraz na praktický príklad.
DROP INDEX ` full_names` ON `members_indexed`;
Vykonaním vyššie uvedeného príkazu sa index s id `full_names` zruší z tabuľky members_indexed.
Zhrnutie
- Indexy sú veľmi silné, pokiaľ ide o výrazné zlepšenie výkonu vyhľadávacích dotazov MySQL.
- Indexy je možné definovať pri vytváraní tabuľky alebo pridávať neskôr, keď už bola tabuľka vytvorená.
- Indexy môžete definovať pre viac ako jeden stĺpec v tabuľke.
- ZOBRAZIŤ INDEX Z TABUĽKY sa používa na zobrazenie definovaných indexov v tabuľke.
- Príkaz DROP sa používa na odstránenie definovaného indexu v danej tabuľke.