MySQL EXPLAIN ANALYZE vysvětleno: čtení plánů provádění a optimalizace dotazů (průvodce 8.0)

1. Introduction

Execution Plans: Essential for Database Performance Optimization

V webových aplikacích a podnikových systémech je výkon databáze kritickým faktorem, který přímo ovlivňuje celkovou dobu odezvy. Při používání MySQL je pochopení „plánu provedení“ nezbytné pro hodnocení efektivity dotazu. Tradiční příkaz EXPLAIN zobrazuje plán provedení před spuštěním SQL příkazu a již dlouho poskytuje vývojářům cenné informace.

“EXPLAIN ANALYZE” Introduced in MySQL 8.0

Představeno v MySQL 8.0.18, EXPLAIN ANALYZE je výkonným rozšířením tradičního EXPLAIN. Zatímco EXPLAIN poskytoval jen „teoretický plán“, EXPLAIN ANALYZE dotaz skutečně spustí a vrátí měřená data, jako je doba provedení a počet zpracovaných řádků. To umožňuje přesnější identifikaci úzkých míst a ověření výsledků optimalizace dotazu.

Why EXPLAIN ANALYZE Matters

Například pořadí JOIN, využití indexů a podmínky filtrování významně ovlivňují dobu provedení. Použitím EXPLAIN ANALYZE můžete vizuálně potvrdit, jak se SQL příkaz provádí, a určit, kde jsou neefektivity a co by mělo být optimalizováno. To je zvláště nepostradatelné při práci s velkými datovými sadami nebo složitými dotazy.

Purpose of This Article and Target Audience

Tento článek vysvětluje vše od základů MySQL EXPLAIN ANALYZE po interpretaci jeho výstupu a praktické optimalizační techniky. Je určen vývojářům a inženýrům infrastruktury, kteří pravidelně používají MySQL, stejně jako inženýrům zajímajícím se o ladění výkonu. Pro zajištění srozumitelnosti i pro začátečníky zahrnujeme vysvětlení terminologie a konkrétní příklady po celou dobu.

2. Differences Between EXPLAIN and EXPLAIN ANALYZE

The Role and Basic Usage of EXPLAIN

MySQL EXPLAIN je analytický nástroj používaný k pochopení předem, jak bude SQL příkaz (zejména SELECT) proveden. Umožňuje potvrdit plány provedení, jako je využití indexů, pořadí spojení a rozsahy vyhledávání.

Například:

EXPLAIN SELECT * FROM users WHERE age > 30;

Po spuštění tohoto příkazu MySQL neprovede dotaz, ale místo toho zobrazí, jak plánuje jeho zpracování v tabulkové podobě. Výstup obsahuje informace jako použitý index (key), metoda přístupu (type) a odhadovaný počet řádků (rows).

The Role and Features of EXPLAIN ANALYZE

Naopak EXPLAIN ANALYZE, představený v MySQL 8.0.18, spustí dotaz a zobrazí plán provedení na základě skutečných naměřených hodnot. To umožňuje potvrdit detaily, které nebyly viditelné v tradičním EXPLAIN, jako je skutečná doba zpracování a počet skutečně zpracovaných řádků.

Příklad:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

Tento příkaz spustí dotaz a vrátí výstup zahrnující:

• Doba provedení pro každý krok plánu (např. 0.0022 sec)
• Skutečný počet přečtených řádků (rows)
• Struktura zpracování (snadno vizualizovatelná pomocí formátu TREE)

Summary of Key Differences

Which One Should You Use?

• Použijte EXPLAIN, když chcete rychle zkontrolovat strukturu dotazu.
• Použijte EXPLAIN ANALYZE, když potřebujete konkrétní údaje o době provedení a nákladech dotazu.

Zvláště v situacích ladění výkonu umožňuje EXPLAIN ANALYZE optimalizaci založenou na reálných datech o provedení místo odhadů, což z něj činí mimořádně výkonný nástroj.

3. Output Formats of EXPLAIN ANALYZE

Three Output Formats: TRADITIONAL, JSON, and TREE

MySQL EXPLAIN ANALYZE může výstup zobrazovat v různých formátech podle vašeho účelu. V MySQL 8.0 a novějších jsou k dispozici následující tři formáty.

Podívejme se podrobněji na rozdíly.

TRADITIONAL Format (Default)

TRADITIONAL výstup je podobný klasickému stylu EXPLAIN a umožňuje vám prohlédnout si plány provádění ve známé formě. Pokud spustíte EXPLAIN ANALYZE bez určení formátu, je výsledek obecně zobrazen v tomto formátu.

Příklad výstupu (úryvek):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

• cost : odhadované náklady
• actual time : měřený čas
• rows : odhadovaný počet zpracovaných řádků (před provedením)
• loops : počet opakování (zejména důležité pro JOIN)

Formát TRADITIONAL je snadný pro lidi k prohlédnutí a pochopení, což ho činí vhodným pro začátečníky a rychlé kontroly.

Formát JSON

Formát JSON je podrobnější a snadněji zpracovatelný programově. Výstup je strukturovaný, přičemž každý uzel je reprezentován jako vnořený objekt.

Příkaz:

EXPLAIN ANALYZE FORMAT=JSON SELECT * FROM users WHERE age > 30;

Část výstupu (hezky naformátovaná):

{
  "query_block": {
    "table": {
      "table_name": "users",
      "access_type": "range",
      "rows_examined_per_scan": 100,
      "actual_rows": 80,
      "filtered": 100,
      "cost_info": {
        "query_cost": "0.35"
      },
      "timing": {
        "start_time": 0.001,
        "end_time": 0.004
      }
    }
  }
}

Tento formát je méně vizuálně čitelný, ale je extrémně pohodlný, když chcete data analyzovat a předat je do nástrojů pro analýzu nebo nástěnek.

Formát TREE (Čitelný a skvělý pro vizualizaci struktury)

Formát TREE zobrazuje strukturu provádění dotazu jako strom, což usnadňuje pochopení pořadí zpracování JOINů a poddotazů.

Příkaz:

EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM users WHERE age > 30;

Příklad výstupu (zjednodušený):

-> Table scan on users  (actual time=0.002..0.004 rows=8 loops=1)

Pro složité dotazy může vnoření vypadat takto:

-> Nested loop join
    -> Table scan on users
    -> Index lookup on orders using idx_user_id

Formát TREE je obzvláště užitečný pro dotazy s mnoha JOINy nebo složitým vnořením, kde potřebujete pochopit tok zpracování.

Který formát byste měli použít?

Vyberte formát, který nejlépe vyhovuje vašemu cíli, a prohlédněte si plán provádění v nejpřehlednějším a analyzovatelném stylu.

4. Jak interpretovat plány provádění

Proč potřebujete číst plány provádění

Výkon dotazů MySQL se může výrazně lišit v závislosti na objemu dat a dostupnosti indexů. Správnou interpretací výstupu plánu provádění z EXPLAIN ANALYZE můžete objektivně identifikovat, kde dochází k plýtvání prací a co by mělo být zlepšeno. Tato dovednost je základním kamenem ladění výkonu, zejména pro dotazy, které zpracovávají velká data nebo složité spoje.

Základní struktura plánu provádění

Výstup EXPLAIN ANALYZE obsahuje informace, jako následující (vysvětleno zde na základě výstupu ve stylu TRADITIONAL):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

Tento jediný řádek obsahuje více důležitých polí.

Jak číst klíčová pole

1. cost vs. actual time

• cost je interní odhad vypočítaný MySQL a slouží k relativnímu hodnocení.
• actual time odráží skutečný uplynulý čas a je důležitější pro analýzu výkonu.

Například:

(cost=0.35 rows=100) (actual time=0.002..0.004 rows=100)

Pokud se odhady a měření shodují, je plán provádění pravděpodobně přesný. Pokud je rozdíl velký, mohou být statistiky tabulek nepřesné.

2. rows vs. actual rows

• rows je počet řádků, které MySQL předpokládá, že přečte.
• actual rows je počet skutečně přečtených řádků (zahrnuto v závorkách ve výstupu ve stylu TRADITIONAL).

Pokud je zde velký rozdíl, možná budete muset obnovit statistiky nebo přehodnotit návrh indexu.

3. loops

If loops=1, the step runs once. With JOINs or subqueries, you may see loops=10 or loops=1000. The larger the value, the more likely nested loops are causing heavy processing.

Pochopte vnořenou strukturu plánů provádění

When multiple tables are joined, the execution plan is shown as a tree (especially clear in TREE format).

Example:

-> Nested loop join
    -> Table scan on users
    -> Table scan on orders

Problém

• Obě tabulky jsou plně prohledány, což vede k vysokým nákladům na spojení.

Opatření

• Přidejte index na users.age a filtrujte dříve, aby se snížila zátěž spojení.

Jak identifikovat úzká místa výkonu

Focusing on the following points makes bottlenecks easier to find:

• Uzly s dlouhým skutečným časem a mnoha řádky : These consume most of the execution time
• Místa, kde dochází k úplnému skenování tabulky : Likely missing or unused indexes
• Kroky s mnoha smyčkami : Indicates inefficient JOIN order or nesting
• Velké rozdíly mezi řádky a skutečnými řádky : Suggests inaccurate statistics or excessive data access

Use these insights as the foundation for the “Query Optimization” techniques introduced in the next section.

5. Praktické příklady optimalizace dotazů

Co je optimalizace dotazů?

Query optimization refers to reviewing and improving SQL statements so they can be executed more efficiently. Based on how MySQL processes queries internally (execution plans), you apply improvements such as adding indexes, adjusting join order, and eliminating unnecessary processing.

Here, we demonstrate how to improve queries using EXPLAIN ANALYZE with concrete examples.

Příklad 1: Zrychlení pomocí indexů

Před optimalizací

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

Plán provádění (úryvek)

-> Table scan on users  (cost=10.5 rows=100000) (actual time=0.001..0.230 rows=1 loops=1)

Problém

• The output shows Table scan , meaning a full table scan is performed. With large datasets, this leads to significant delays.

Řešení: Přidat index

CREATE INDEX idx_email ON users(email);

Plán provádění po optimalizaci

-> Index lookup on users using idx_email  (cost=0.1 rows=1) (actual time=0.001..0.002 rows=1 loops=1)

Výsledek

• Execution time significantly reduced.
• Full table scan avoided by using the index.

Příklad 2: Optimalizace pořadí JOINů

Před optimalizací

SELECT * FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

Plán provádění (úryvek)

-> Nested loop join
    -> Table scan on orders
    -> Table scan on users

Problém

• Both tables are fully scanned, resulting in high join costs.

Řešení

• Add an index on users.age and filter first to reduce the join target size.

  CREATE INDEX idx_age ON users(age);
  

Plán provádění po optimalizaci

-> Nested loop join
    -> Index range scan on users using idx_age
    -> Index lookup on orders using idx_user_id

Výsledek

• JOIN targets are filtered first, reducing overall processing load.

Příklad 3: Revize poddotazu

Před optimalizací

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Problém

• The subquery may be evaluated repeatedly, degrading performance.

Řešení: Přepsat jako JOIN

SELECT DISTINCT users.*
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.total > 1000;

Výsledek

• The execution plan is optimized for JOIN processing, and indexes are more likely to be used.

Důležitost porovnání před/po

Using EXPLAIN ANALYZE, you can verify optimization results with actual measured values. By comparing execution time and row counts before and after improvements, you ensure that tuning efforts are based on real performance gains rather than assumptions.

Důležité úvahy při optimalizaci

• Přidání příliš mnoha indexů může být kontraproduktivní (pomalejší výkon INSERT/UPDATE).
• Plány provádění závisí na objemu dat a statistikách, takže je vyžadována validace pro každé prostředí.
• Jedna optimalizace zřídka řeší vše. Analýza úzkých míst je první.

6. Opatření a osvědčené postupy

Důležité poznámky při používání EXPLAIN ANALYZE

Although EXPLAIN ANALYZE is extremely powerful, improper use can lead to misunderstandings or even operational risks. Keeping the following points in mind ensures safe and effective query analysis.

1. Vyhněte se neuváženému spouštění v produkci

Because EXPLAIN ANALYZE actually executes the query, mistakenly using it with modification statements (INSERT/UPDATE/DELETE) can change data.

• In general, only use it with SELECT statements.
• Prefer running it in a staging or testing environment rather than production.

2. Zvažte dopad cachování

MySQL may return results from cache if the same query is executed repeatedly. As a result, execution time reported by EXPLAIN ANALYZE may differ from real‑world behavior.

Protiopatření:

• Clear the cache before execution ( RESET QUERY CACHE; ).
• Run multiple times and evaluate based on average values.

3. Udržujte statistiky aktuální

MySQL builds execution plans based on table and index statistics. If statistics are outdated, both EXPLAIN and EXPLAIN ANALYZE may provide misleading information.

After large INSERT or DELETE operations, update statistics using ANALYZE TABLE.

ANALYZE TABLE users;

4. Indexy nejsou všelék

While indexes often improve performance, too many indexes slow down write operations.

Choosing between composite indexes and single‑column indexes is also important. Design indexes carefully based on query patterns and usage frequency.

5. Nehodnoťte jen podle doby provádění

Results from EXPLAIN ANALYZE reflect only the performance of a single query. In real applications, network latency or backend processing may be the actual bottleneck.

Therefore, analyze queries within the context of the entire system architecture.

Shrnutí osvědčených postupů

7. Často kladené otázky (FAQ)

Q1. Od které verze je EXPLAIN ANALYZE k dispozici?

A.
MySQL’s EXPLAIN ANALYZE was introduced in version 8.0.18 and later. It is not supported in versions prior to 8.0, so you should verify your MySQL version before using it.

Q2. Může spuštění EXPLAIN ANALYZE měnit data?

A.
EXPLAIN ANALYZE executes the query internally. When used with a SELECT statement, it does not modify data. Therefore, when used with a SELECT statement, it does not modify data. However, if you mistakenly use it with INSERT, UPDATE, or DELETE, the data will be modified just as with a normal query. For safety, it is recommended to run analyses in a test or staging database rather than in production.

Q3. Není EXPLAIN sám o sobě dostačující?

A.
EXPLAIN is sufficient for reviewing the “estimated” execution plan. However, it does not provide measured values such as actual execution time or actual row counts. If you need serious query tuning or want to verify optimization effects, EXPLAIN ANALYZE is more useful.

Q4. Jak přesné jsou hodnoty jako „loops“ a „actual time“?

A.
Values such as actual time and loops are real execution metrics measured internally by MySQL. However, they may fluctuate slightly depending on OS conditions, cache state, and server load.

Z tohoto důvodu se nespoléhejte na jediné měření. Místo toho spusťte dotaz vícekrát a vyhodnoťte trendy.

Q5. Co přesně představuje „cost“?

A.
cost je odhadovaná hodnota vypočítaná interním modelem nákladů MySQL. Odráží relativní hodnocení nákladů na CPU a I/O. Není vyjádřena v sekundách.

Například, pokud vidíte (cost=0.3) a (cost=2.5), ten druhý je odhadován jako dražší v relativním smyslu.

Q6. Jaké jsou výhody používání formátu JSON nebo TREE?

A.

• Formát JSON : Strukturovaný výstup, který je snadno parsovatelný programově. Užitečný pro nástroje automatizace a dashboardy.
• Formát TREE : Zpřehledňuje tok provádění a vnoření vizuálně. Ideální pro porozumění složitým dotazům a pořadí JOINů.

Vyberte formát, který nejlépe vyhovuje vašemu účelu.

Q7. Co bych měl dělat, pokud nemohu zlepšit výkon po prozkoumání plánu provádění?

A.
Zvažte další přístupy, jako jsou:

• Přepracování indexů (kompozitní indexy nebo pokrývající indexy)
• Přepsání dotazů (poddotazy → JOINy, odstranění nepotřebných sloupců SELECT)
• Použití zobrazení nebo dočasných tabulek
• Prozkoumání konfigurace MySQL (velikosti bufferů, alokace paměti atd.)

Ladění výkonu zřídka uspěje s jedinou technikou. Komplexní a iterativní přístup je nezbytný.