Jak zkontrolovat a optimalizovat indexy MySQL: Kompletní průvodce s SHOW INDEX, EXPLAIN a laděním výkonu

1. Úvod

MySQL je relační databáze široce používaná v mnoha webových aplikacích a systémech pro správu dat. Pro zrychlení načítání dat existuje mechanismus nazývaný „index“. Nicméně, pokud není správně spravován, může index ve skutečnosti snížit výkon.

Proč je důležité kontrolovat indexy?

Index v databázi je podobný rejstříku v knize. Správně navržené indexy zvyšují rychlost provádění vyhledávacích dotazů. Nicméně mohou nastat následující problémy:

• Indexy nejsou správně vytvořeny → Může způsobit pomalé vyhledávání
• Existují zbytečné indexy → Vedou k pomalejším aktualizacím a vkládáním
• Nejistota, které indexy jsou používány → Ztěžuje rozhodování, zda by měly být nepoužívané indexy odstraněny

Co se v tomto článku naučíte

• Základní mechanismus MySQL indexů
• Jak zkontrolovat aktuální indexy (pomocí SQL příkazů)
• Jak spravovat a optimalizovat indexy
• Techniky pro analýzu využití indexů

Nyní se systematicky podíváme na MySQL indexy a využijeme tyto znalosti ke zlepšení výkonu databáze.

2. Co je MySQL index?

Index je nezbytná vlastnost pro zlepšení výkonu databáze. V této sekci vysvětlujeme základní pojem, typy, výhody a nevýhody indexů.

Základní pojem indexů

Databázový index je mechanismus, který umožňuje rychlé vyhledávání hodnot ve specifických sloupcích. Například při hledání konkrétního záznamu v tabulce obsahující velké množství dat, bez indexu musí databáze prohledat všechny záznamy (full table scan). Použití indexu umožňuje efektivní načítání dat a výrazně zvyšuje rychlost zpracování.

Typy MySQL indexů

MySQL podporuje několik typů indexů, z nichž každý je vhodný pro konkrétní případy použití.

1. PRIMARY KEY (Primární klíčový index)

• Lze nastavit pouze jednou na tabulku
• Zaručuje jedinečnost řádků v tabulce
• Funguje jako clusterovaný index

1. UNIQUE Index

• Zajišťuje, že hodnoty ve specifikovaném sloupci se nebudou duplikovat
• Hodnoty NULL jsou povoleny (více NULLů je povoleno)

1. INDEX (Obecný index)

• Používá se ke zrychlení vyhledávání
• Duplicita dat je povolena

1. FULLTEXT Index (pro fulltextové vyhledávání)

• Optimalizuje vyhledávání textu
• Používá se v kombinaci se syntaxí MATCH ... AGAINST

1. SPATIAL Index (pro geografická data)

• Navržen pro prostorová (GIS) data

Výhody a nevýhody indexů

Výhody

• Zvyšuje rychlost vyhledávání v dotazech
• Zlepšuje výkon operací JOIN a podmínek WHERE
• Umožňuje efektivnější získávání konkrétních dat

Nevýhody

• Více indexů zpomaluje operace INSERT, UPDATE a DELETE
• Zabírá diskový prostor
• Špatně navržené indexy mohou snižovat výkon

3. Jak zkontrolovat MySQL indexy

Aby bylo možné správně spravovat MySQL indexy, je důležité zjistit, které indexy jsou aktuálně definovány na tabulce. V této sekci vysvětlujeme, jak kontrolovat indexy pomocí příkazu SHOW INDEX, INFORMATION_SCHEMA.STATISTICS a příkazu mysqlshow.

Příkaz SHOW INDEX (základní metoda)

V MySQL můžete použít příkaz SHOW INDEX k získání seznamu indexů definovaných na konkrétní tabulce. Tento příkaz vám umožní zkontrolovat podrobnosti jako název indexu, sloupce zahrnuté v indexu a zda existuje unikátní omezení.

Základní syntaxe

SHOW INDEX FROM table_name;

Příklad

Například pro kontrolu indexů definovaných v tabulce users spusťte následující SQL:

SHOW INDEX FROM users;

Výstup příkladu

Použití INFORMATION_SCHEMA.STATISTICS (získání podrobných informací)

Pomocí systémové tabulky MySQL INFORMATION_SCHEMA.STATISTICS můžete získat stejné informace jako SHOW INDEX, ale s větší flexibilitou.

Zkontrolovat indexy pro konkrétní tabulku

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

Získat indexy v celé databázi

SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name';

Příkaz mysqlshow (Kontrola z příkazové řádky)

Můžete také získat informace o indexech pomocí nástrojů MySQL v příkazové řádce. To je zvláště užitečné při práci přes SSH na MySQL serveru.

Jak spustit příkaz

mysqlshow -u username -p password database_name table_name

Příklad

mysqlshow -u root -p my_database users

Co dělat, pokud neexistují žádné indexy

Pokud se po spuštění SHOW INDEX nebo dotazu na INFORMATION_SCHEMA.STATISTICS nezobrazí žádné indexy, tabulka možná nemá vhodné indexy. V takovém případě můžete zlepšit výkon vyhledávání vytvořením potřebných indexů.

Vytvořit nový index

CREATE INDEX idx_column ON users (email);

Nastavit primární klíč (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Odstranit zbytečné indexy

ALTER TABLE users DROP INDEX idx_column;

4. Jak zkontrolovat využití indexů

Kontrola, zda indexy fungují správně, je kritickým krokem při optimalizaci výkonu MySQL. V této sekci vysvětlujeme, jak zjistit, které indexy dotaz používá, pomocí příkazu EXPLAIN a Performance Schema.

Analýza dotazu pomocí EXPLAIN

EXPLAIN příkaz se používá k vizualizaci toho, jak bude daný SQL dotaz proveden. Pomáhá analyzovat použité indexy, přístupové metody a plán provedení, což je užitečné pro ověření, zda indexy fungují podle očekávání.

Základní syntaxe

EXPLAIN SELECT * FROM table_name WHERE condition;

Příklad

Například pro vyhledání v tabulce users pomocí sloupce email jako podmínky:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Výstup příkladu

Klíčové body

• Pokud type = ALL, dotaz provádí úplné prohlednutí tabulky, takže pravděpodobně potřebujete index.
• Pokud se v key objeví název indexu, tento index se používá.
• Pokud je hodnota rows příliš velká, může být nutná optimalizace dotazu.

Použití Performance Schema

S performance_schema v MySQL můžete podrobně analyzovat, které indexy jsou používány a jak často během provádění dotazu.

Získat statistiky provádění dotazu

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%';

Zkontrolovat využití indexu pro konkrétní tabulku

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name' AND OBJECT_NAME = 'users';

Co dělat, pokud se index nepoužívá

1. Zkontrolovat dotaz

Pokud se index nepoužívá, může být problém ve struktuře dotazu. Například následující vzor může zabránit použití indexu.

❌ Nesprávný příklad (Funkce zakazuje použití indexu)

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

→ Kvůli LOWER(email) může být index na email ignorován.

✅ Opravený příklad (Vyhněte se použití funkce)

SELECT * FROM users WHERE email = 'test@example.com';

2. Znovu vytvořit index

Pokud existující index nefunguje správně, jeho odstranění a opětovné vytvoření může někdy zlepšit výkon.

ALTER TABLE users DROP INDEX idx_email;
CREATE INDEX idx_email ON users(email);

3. Aktualizovat statistiky

Pokud se statistiky tabulky zastarají, MySQL nemusí indexy používat optimálně. Statistiky můžete obnovit následujícím příkazem:

ANALYZE TABLE users;

5. Správa indexů

MySQL indexy jsou nezbytné pro zlepšení výkonu při načítání dat. Nicméně, pokud nejsou správně spravovány, mohou zhoršit celkový výkon databáze. V této sekci podrobně vysvětlujeme, jak vytvářet, mazat a identifikovat zbytečné indexy.

Creating Indexes

Vytvořením vhodných indexů můžete výrazně urychlit vyhledávání dat. V MySQL můžete přidávat indexy pomocí CREATE INDEX nebo ALTER TABLE.

Basic Syntax

CREATE INDEX index_name ON table_name(column_name);

Example

Pro přidání indexu ke sloupci email v tabulce users:

CREATE INDEX idx_email ON users(email);

Multi-Column Index (Composite Index)

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

Unique Index

CREATE UNIQUE INDEX idx_unique_email ON users(email);

Setting a Primary Key (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Dropping Indexes

Odstranění zbytečných indexů pomáhá snížit zátěž databáze.

Basic Syntax

ALTER TABLE table_name DROP INDEX index_name;

Example

Například pro smazání indexu s názvem idx_email:

ALTER TABLE users DROP INDEX idx_email;

Identifying and Removing Unnecessary Indexes

Check for Unused Indexes

SELECT * FROM sys.schema_unused_indexes;

Check Table Status (Index Impact)

SHOW TABLE STATUS LIKE 'users';

Remove Unnecessary Indexes

ALTER TABLE users DROP INDEX idx_unused;

Po smazání se doporučuje aktualizovat statistiky pomocí ANALYZE TABLE.

ANALYZE TABLE users;

6. Index Optimization (Performance Improvement)

Správná správa indexů může výrazně zlepšit výkon dotazů v MySQL. Nicméně pouhé vytváření indexů nestačí — správný návrh, správa a monitorování jsou nezbytné pro udržení optimálního výkonu.

Proper Index Design

Dobře navržené indexy mohou dramaticky zlepšit rychlost vyhledávání v MySQL.

Cases Where Indexes Should Be Applied

Cases Where Indexes Should Not Be Applied

Using the Slow Query Log

Protokol pomalých dotazů vám umožňuje identifikovat dlouho běžící dotazy a analyzovat, které indexy nejsou použity.

Enable the Slow Query Log

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking longer than 2 seconds

Check the Slow Query Log

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Analyze a Slow Query

EXPLAIN SELECT * FROM users WHERE last_login > '2024-01-01';

Example of Applying an Index

CREATE INDEX idx_last_login ON users(last_login);

Updating Statistics (ANALYZE & OPTIMIZE)

ANALYZE TABLE (Update Statistics)

ANALYZE TABLE users;

OPTIMIZE TABLE (Defragmentation)

OPTIMIZE TABLE users;

7. FAQ About Indexes (Frequently Asked Questions)

MySQL indexy jsou nezbytným mechanismem pro zlepšení výkonu databáze. Nicméně, pokud nejsou správně spravovány, mohou mít opačný efekt. V této sekci shrnujeme často kladené otázky (FAQ) a odpovědi související s MySQL indexy.

Will search speed improve as I add more indexes?

A. Not necessarily.

Indexy jsou navrženy tak, aby zlepšovaly výkon dotazů, ale přidání příliš mnoha indexů může ve skutečnosti zhoršit výkon databáze.

• Zvyšuje zátěž zápisu (INSERT, UPDATE, DELETE)
• Zda je index použit, závisí na dotazu
• Zbytečné indexy spotřebovávají paměť

Which columns should have indexes?

A. It is effective to apply indexes to the following types of columns:

Are indexes created automatically?

A. Some indexes are created automatically, but others must be added manually.

Automaticky vytvořené indexy

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY index
  email VARCHAR(255) UNIQUE -- Index automatically created by UNIQUE constraint
);

Ručně vytvořené indexy

CREATE INDEX idx_email ON users(email);

Jak si mám vybrat mezi B-Tree, Hash a FULLTEXT indexy?

Jak mohu zkontrolovat velikost indexů?

SHOW TABLE STATUS LIKE 'users';

Jak mohu zjistit, zda je index používán?

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

Kdy bych měl odstranit index?

Odstranit zbytečný index

ALTER TABLE users DROP INDEX idx_unused;

8. Shrnutí

V tomto článku jsme komplexně pokryli indexy MySQL – od základů po ověřovací metody, správu, optimalizaci a často kladené otázky. Zde shrnujeme klíčové body z každé sekce a uvádíme nejlepší postupy pro optimalizaci správy indexů MySQL.

Klíčové poznatky

Co je MySQL index?

• Index je mechanismus, který urychluje načítání dat.
• Existuje několik typů, včetně B-Tree, Hash a FULLTEXT indexů .
• Nejefektivnější, když je aplikován na sloupce použité ve WHERE klauzulích, JOINech a ORDER BY.

Jak zkontrolovat MySQL indexy

• Použijte příkaz SHOW INDEX k zobrazení seznamu indexů.
• Použijte INFORMATION_SCHEMA.STATISTICS k získání podrobných informací.

Jak zkontrolovat využití indexu

• Použijte EXPLAIN k zobrazení, které indexy dotaz používá.
• Použijte Performance Schema k analýze četnosti využití indexů.

Správa indexů

• Použijte CREATE INDEX k aplikaci indexů na vhodné sloupce.
• Použijte ALTER TABLE DROP INDEX k odstranění zbytečných indexů.
• Zkontrolujte velikost indexu pomocí SHOW TABLE STATUS a optimalizujte podle potřeby.

Optimalizace indexů (zlepšení výkonu)

• Aplikujte indexy na často používané sloupce ve WHERE, JOIN a ORDER BY.
• Použijte Slow Query Log k identifikaci a optimalizaci pomalých dotazů.
• Aktualizujte statistiky pomocí ANALYZE TABLE a OPTIMIZE TABLE.

Nejlepší postupy pro správu MySQL indexů

1. Identifikujte úzká místa dotazů před aplikací indexů.
2. Vyberte vhodné indexy.

• Správně používejte jednosloupcové i složené indexy.
• Použijte UNIQUE INDEX, kde jsou vyžadována omezení jedinečnosti.

1. Pravidelně odstraňujte zbytečné indexy.

• Identifikujte nepoužívané indexy pomocí SHOW INDEX a schema_unused_indexes.

1. Pravidelně aktualizujte statistiky.

• Aktualizujte statistiky pomocí ANALYZE TABLE.
• Spusťte OPTIMIZE TABLE k odstranění fragmentace způsobené mazáním a aktualizacemi.

Další kroky

✅ Praktický kontrolní seznam
✅ Zkontrolovali jste aktuální indexy pomocí SHOW INDEX?
✅ Povolili jste Slow Query Log a identifikovali pomalé dotazy?
✅ Analyzovali jste plán provedení dotazu pomocí EXPLAIN?
✅ Odstranili jste zbytečné indexy a vytvořili vhodné?
✅ Aktualizovali jste statistiky pomocí ANALYZE TABLE?

Závěrečné shrnutí

• Správně spravované MySQL indexy výrazně zlepšují výkon vyhledávání.
• Používejte Slow Query Log a EXPLAIN k analýze účinnosti indexů a optimalizaci.
• Udržujte výkon databáze pravidelnou aktualizací statistik a optimalizací tabulek.

Toto je kompletní průvodce správou MySQL indexů.

Využijte tyto znalosti k vytvoření rychlejších a efektivnějších databázových systémů. 💡🚀