Як з'ясувати, чому оптимізатор MySQL обрав саме такий план: optimizer trace?

EXPLAIN показує, який план обрано. Optimizer trace показує, чому: які способи доступу розглядалися, у скільки їх оцінено, які перетворення запиту застосовано й чому переміг саме цей варіант.

Як отримати трасування:

SET SESSION optimizer_trace = 'enabled=on';
SET SESSION optimizer_trace_max_mem_size = 1048576;

SELECT * FROM orders WHERE user_id = 7 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

SELECT trace, missing_bytes_beyond_max_mem_size
FROM information_schema.optimizer_trace\G

SET SESSION optimizer_trace = 'enabled=off';

Трасування - великий JSON. Якщо missing_bytes_beyond_max_mem_size більше нуля, вивід обрізано, і ліміт пам'яті треба збільшити.

Що в ньому шукати:

• join_preparation - як запит переписано: розкриття подань, перетворення підзапитів у напівз'єднання;
• condition_processing - спрощення умов, підстановка констант;
• rows_estimation → range_analysis - для кожного індексу: чи придатний ("usable": false з причиною), оцінка рядків (rows), вартість (cost), чи робилися index dives;
• considered_execution_plans - порядки з'єднань і їхня вартість;
• reconsidering_access_paths_for_index_ordering - чи вирішив оптимізатор перейти на інший індекс заради ORDER BY ... LIMIT. Це часте джерело «дивних» планів: запит з LIMIT раптом сканує індекс сортування замість селективного індексу з WHERE.

Типові висновки з трасування:

• оцінка рядків хибна (індекс оцінено в 50 рядків, а реально їх 500 000) - проблема статистики: ANALYZE TABLE, більше сторінок для вибірки, гістограми;
• індекс непридатний через тип колонки, кодування чи функцію в умові - видно прямо в range_analysis;
• вартість двох планів майже однакова - план «стрибає» між ними при зміні даних, і варто дати оптимізатору однозначно кращий індекс.

Обмеження й обережність:

• трасування працює лише в поточній сесії й для запитів, виконаних після увімкнення;
• воно сповільнює оптимізацію, тож вмикати його глобально на продакшені не варто;
• формат JSON не є стабільним інтерфейсом і змінюється між версіями - для ручного аналізу, не для автоматичних перевірок.

Порядок діагностики повільного запиту: EXPLAIN → EXPLAIN ANALYZE (де план розходиться з реальністю) → optimizer trace (чому оптимізатор помилився) → виправлення статистики, індексу чи запиту.

Докладніше в документації: Трасування оптимізатора