Einen schlechten Plan mit EXPLAIN ANALYZE und dem Optimizer Trace untersuchen

Befehl

-- Plan + actual time and rows (the query actually runs)
EXPLAIN ANALYZE
SELECT region, COUNT(*) FROM shop.big WHERE id < 20000 GROUP BY region;

-- Why the optimizer chose this plan: optimizer trace
SET SESSION optimizer_trace = 'enabled=on', SESSION optimizer_trace_max_mem_size = 1048576;
SELECT * FROM shop.big WHERE region = 'TR' AND id > 100 LIMIT 5;
SELECT JSON_PRETTY(JSON_EXTRACT(TRACE, '$**.considered_execution_plans')) AS plans,
       MISSING_BYTES_BEYOND_MAX_MEM_SIZE
FROM information_schema.OPTIMIZER_TRACE;
SET SESSION optimizer_trace = 'enabled=off';

Was es tut

EXPLAIN ANALYZE zeigt für jeden Planschritt geschätzte (cost, rows) und tatsächliche (actual time, rows, loops) Werte nebeneinander; der Optimizer Trace zeigt die erwogenen Zugriffspfade, ihre Kosten und warum jeder gewählt oder verworfen wurde.

Wann einsetzen

Wenn eine Abfrage plötzlich langsamer wird (Planänderung), der Optimizer den falschen Index wählt oder um die Ursache einer Abweichung zwischen geschätzten und tatsächlichen Zeilen zu finden.

Vorsicht

EXPLAIN ANALYZE führt die Abfrage tatsächlich aus; bei schweren Abfragen auf der Produktion vorsichtig sein. Es funktioniert mit SELECT, TABLE und UPDATE / DELETE über mehrere Tabellen. Eine Abweichung von mehr als 10x zwischen geschätzten und tatsächlichen Zeilen deutet auf ein Statistik- / Histogrammproblem hin (ANALYZE TABLE, Histogramme). Auf der inneren Seite eines Nested Loop gilt actual rows pro Loop; Gesamt = rows × loops. Ist MISSING_BYTES_BEYOND_MAX_MEM_SIZE > 0, wurde der Trace abgeschnitten; erhöhe optimizer_trace_max_mem_size. Der Trace enthält nur die letzte Anweisung derselben Session.