Co je to index?
Indexy v MySQL třídí data organizovaným sekvenčním způsobem. Jsou vytvořeny ve sloupcích, které budou použity k filtrování dat. Představte si index jako abecedně seřazený seznam. Je snazší vyhledat názvy, které byly seřazeny v abecedním pořadí, než ty, které nejsou seřazeny.
Použití indexu u tabulek, které jsou často aktualizovány, může mít za následek špatný výkon. Je to proto, že MySQL vytváří nový blok indexů pokaždé, když jsou v tabulce přidána nebo aktualizována data. Indexy by se obecně měly používat v tabulkách, jejichž data se často nemění, ale ve vybraných vyhledávacích dotazech se hodně používají.
Co používá index?
Nikdo nemá rád pomalé systémy. Vysoký výkon systému má zásadní význam téměř ve všech databázových systémech. Většina podniků investuje velké prostředky do hardwaru, aby vyhledávání a manipulace s daty mohla být rychlejší. Investice do hardwaru však může podnik omezit. Optimalizace databáze je levnější a lepší řešení.
Pomalost doby odezvy je obvykle způsobena náhodným ukládáním záznamů do databázových tabulek. Vyhledávací dotazy musí postupně procházet všemi náhodně uloženými záznamy, aby nalezly požadovaná data. To má za následek špatný výkon databází, pokud jde o načítání dat z velkých tabulek. Proto se používají indexy, které třídí data, aby se usnadnilo vyhledávání.
Syntaxe: Vytvořit rejstřík
Rejstříky lze definovat dvěma způsoby
- V době vytváření tabulky
- Po vytvoření tabulky
Pro naši myflixdb očekáváme spoustu vyhledávání v databázi s plným jménem.
Do indexu v nové tabulce „members_indexed“ přidáme sloupec „full_names“.
Skript zobrazený níže nám k tomu pomáhá.
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;
Spusťte výše uvedený skript SQL v pracovní ploše MySQL proti „myflixdb“.
Aktualizací myflixdb se zobrazí nově vytvořená tabulka s názvem members_indexed.
Tabulka „Note“ members_indexed má v uzlu indexů „full_names“.
Jak se členská základna rozšiřuje a zvyšuje se počet záznamů, vyhledávací dotazy v tabulce members_indexed, které používají klauzule WHERE a ORDER BY, budou mnohem rychlejší ve srovnání s těmi, které provedly tabulku členů bez definovaného indexu.
Přidejte základní syntaxi indexu
Výše uvedený příklad vytvořil index při definování databázové tabulky. Předpokládejme, že již máme definovanou tabulku a vyhledávací dotazy jsou velmi pomalé. Návrat výsledků trvá příliš dlouho. Po prozkoumání problému zjistíme, že můžeme výrazně zlepšit výkon systému vytvořením INDEXU v nejčastěji používaném sloupci v klauzuli WHERE.
K přidání indexu můžeme použít následující dotaz
CREATE INDEX id_index ON table_name(column_name);
Předpokládejme, že vyhledávací dotazy v tabulce filmů jsou velmi pomalé a chceme k urychlení dotazů použít index na „názvu filmu“, můžeme toho dosáhnout pomocí následujícího skriptu.
CREATE INDEX `title_index` ON `movies`(`title`);
Provedení výše uvedeného dotazu vytvoří index v poli nadpisu v tabulce filmů.
To znamená, že všechny vyhledávací dotazy na tabulce filmů pomocí „názvu“ budou rychlejší.
Vyhledávací dotazy v jiných polích v tabulce filmů však budou stále pomalejší ve srovnání s dotazy založenými na indexovaném poli.
Poznámka: V případě potřeby můžete vytvořit indexy na více sloupcích v závislosti na polích, která chcete použít pro svůj databázový vyhledávač.
Chcete-li zobrazit indexy definované v konkrétní tabulce, můžete k tomu použít následující skript.
SHOW INDEXES FROM table_name;
Pojďme se nyní podívat na všechny indexy definované v tabulce filmů v myflixdb.
SHOW INDEXES FROM `movies`;
Provedení výše uvedeného skriptu v pracovní ploše MySQL proti myflixdb nám dává výsledky vytvořeného indexu.
Poznámka: Primární a cizí klíče v tabulce již byly indexovány pomocí MySQL. Každý index má svůj vlastní jedinečný název a zobrazuje se také sloupec, ve kterém je definován.
Syntaxe: Drop index
Příkaz drop se používá k odebrání již definovaných indexů z tabulky.
Mohou nastat situace, kdy jste již definovali index v tabulce, která je často aktualizována. Možná budete chtít odebrat indexy z takové tabulky, abyste zlepšili výkon dotazů UPDATE a INSERT. Základní syntaxe použitá k vložení indexu do tabulky je následující.
DROP INDEX `index_id` ON `table_name`;
Pojďme se nyní podívat na praktický příklad.
DROP INDEX ` full_names` ON `members_indexed`;
Provedení výše uvedeného příkazu zruší index s id `full_names` z tabulky members_indexed.
souhrn
- Indexy jsou velmi silné, pokud jde o výrazné zlepšení výkonu vyhledávacích dotazů MySQL.
- Rejstříky lze definovat při vytváření tabulky nebo přidat později poté, co již byla tabulka vytvořena.
- Můžete definovat indexy pro více než jeden sloupec v tabulce.
- SHOW INDEX FROM table_name se používá k zobrazení definovaných indexů v tabulce.
- Příkaz DROP se používá k odebrání definovaného indexu v dané tabulce.