MySQL Kurz indexu – Vytvořit, přidat a upustit

Co je to index?

Indexy v MySQL třídit 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 nízký výkon. To je proto, že MySQL vytvoří nový indexový blok pokaždé, když jsou data přidána nebo aktualizována v tabulce. Obecně by se indexy měly používat u tabulek, jejichž data se často nemění, ale často se používají ve vybraných vyhledávacích dotazech.

K čemu slouží index?

Nikdo nemá rád pomalé systémy. Téměř ve všech databázových systémech je prvořadý význam vysoký výkon systému. Většina podniků investuje značné prostředky do hardwaru, takže získávání dat a manipulace mohou být rychlejší. Investice do hardwaru, které může podnik provést, jsou však omezeny. Optimalizace vaší databáze je levnější a lepší řešení.

Pomalost v době odezvy je obvykle způsobena náhodným ukládáním záznamů v databázových tabulkách. Vyhledávací dotazy musí procházet celými náhodně uloženými záznamy jeden po druhém, aby nalezly požadovaná data. To má za následek slabý výkon databází, pokud jde o načítání dat z velkých tabulek. Proto se používají indexy, které třídí data a usnadňují vyhledávání.

Syntaxe: Create Index

Indexy lze definovat 2 způsoby

1. V době tvorby tabulky
2. Po vytvoření tabulky

Příklad:

Pro naši myflixdb očekáváme spoustu vyhledávání v databázi pod celým jménem.

Sloupec „full_names“ přidáme do Indexu v nové tabulce „members_indexed“.

Níže uvedený skript 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ý SQL skript v MySQL pracovní stůl proti „myflixdb“.

Obnovení myflixdb zobrazí nově vytvořenou tabulku s názvem members_indexed.

"Poznámka" tabulka members_indexed má v uzlu indexes „full_names“.

S rozšiřováním členské základny a rostoucím počtem záznamů budou vyhledávací dotazy v tabulce members_indexed, které používají klauzule WHERE a ORDER BY, mnohem rychlejší ve srovnání s dotazy prováděnými v tabulce č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 v ní jsou velmi pomalé. Trvá příliš dlouho, než vrátí výsledky. 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 pro urychlení dotazů použít index na „název filmu“, k tomu můžeme použít následující skript.

CREATE INDEX `title_index` ON `movies`(`title`);

Provedením výše uvedeného dotazu se vytvoří index v poli názvu v tabulce filmů.

To znamená, že všechny vyhledávací dotazy na filmovém stole pomocí „title“ budou rychlejší.

Vyhledávací dotazy na jiná pole 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 pro více sloupců v závislosti na polích, která chcete použít pro svůj databázový vyhledávač.

Pokud chcete 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`;

Spuštění výše uvedeného skriptu v MySQL ponk oproti myflixdb nám dává výsledky na vytvořeném indexu.

Poznámka: Primární a cizí klíče v tabulce již byly indexovány MySQL. Každý index má svůj 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 odstranění již definovaných indexů v tabulce.

Mohou nastat situace, kdy jste již definovali index pro tabulku, která je často aktualizována. Chcete-li zlepšit výkon dotazů UPDATE a INSERT, můžete z takové tabulky odebrat indexy. Základní syntaxe použitá k vypuštění indexu na tabulku je následující.

DROP INDEX `index_id` ON `table_name`;

Podívejme se nyní na praktický příklad.

DROP INDEX ` full_names` ON `members_indexed`;

Provedení výše uvedeného příkazu odstraní index s id ` full_names ` z tabulky members_indexed.

Shrnutí

• Indexy jsou velmi silné, pokud jde o výrazné zlepšení výkonu MySQL vyhledávací dotazy.
• Indexy lze definovat při vytváření tabulky nebo je lze přidat později poté, co již byla tabulka vytvořena.
• Indexy můžete definovat pro více než jeden sloupec v tabulce.
• SHOW INDEX FROM název_tabulky se používá k zobrazení definovaných indexů v tabulce.
• Příkaz DROP se používá k odstranění definovaného indexu v dané tabulce.