Увійти Реєстрація
Вакансії
Вакансії Laravel Компанії Статистика
Підготовка
Як підготуватися Співбесіди Тестування Документація
Блог
Всі статті Серії
Екосистема
Пакети Ресурси Проєкти Інструменти Події Відео
Інше
Про нас Реклама

Питання на співбесіді: Індекси й оптимізація запитів

Питання з реальних співбесід з відповідями: Laravel і PHP, бази даних, JavaScript і фронтенд, Git, Docker, API, безпека й архітектура. Тими самими темами, що й тести.

21 питань

Оптимізаторні підказки - коментарі спеціального вигляду одразу після ключового слова SELECT, UPDATE, DELETE чи INSERT. Вони керують оптимізатором точніше, ніж старі FORCE INDEX, і можуть стосуватися окремого запиту, таблиці чи індексу.

SELECT /*+ MAX_EXECUTION_TIME(2000) */ * FROM reports WHERE ...;

Основні групи:

  • індекси: INDEX(t idx), NO_INDEX(t idx), точніше - JOIN_INDEX, ORDER_INDEX, GROUP_INDEX; також INDEX_MERGE / NO_INDEX_MERGE, SKIP_SCAN, NO_ICP, NO_RANGE_OPTIMIZATION;
  • порядок з'єднань: JOIN_ORDER(a, b), JOIN_PREFIX(a), JOIN_SUFFIX(b), JOIN_FIXED_ORDER();
  • стратегії з'єднань і підзапитів: BNL / NO_BNL (керують і хеш-з'єднанням), BKA, SEMIJOIN(FIRSTMATCH), SUBQUERY(MATERIALIZATION), MERGE / NO_MERGE для похідних таблиць;
  • виконання: MAX_EXECUTION_TIME(мс) (лише для SELECT), SET_VAR(змінна=значення) - змінна сесії лише на час запиту, RESOURCE_GROUP(...).

Найкорисніші на практиці:

-- обмежити час важкого звіту, щоб не тримав сервер
SELECT /*+ MAX_EXECUTION_TIME(5000) */ ...;

-- більший буфер сортування лише для цього запиту
SELECT /*+ SET_VAR(sort_buffer_size = 16777216) */ ... ORDER BY ...;

-- не матеріалізувати похідну таблицю, а злити з зовнішнім запитом
SELECT /*+ MERGE(t) */ * FROM (SELECT ...) AS t WHERE ...;

Переваги над FORCE INDEX:

  • неправильна підказка не ламає запит - MySQL видає попередження й ігнорує її (а FORCE INDEX з неіснуючим індексом дає помилку);
  • для інших СУБД це просто коментар - менше зав'язки на синтаксис MySQL;
  • точкове керування без зміни змінних сесії.

У Laravel підказка має стояти одразу після SELECT. Найпростіше - через selectRaw першим виразом:

Report::query()
    ->selectRaw('/*+ MAX_EXECUTION_TIME(5000) */ *')
    ->where('year', 2026)
    ->get();

Підказки в коментарях не мають бути «з'їдені» по дорозі: деякі проксі й інструменти мініфікації SQL вирізають коментарі - це варто перевірити в EXPLAIN чи журналі запитів.

Як перевірити, що підказку застосовано: EXPLAIN і SHOW WARNINGS після нього - там видно переписаний запит з підказками, а нерозпізнані підказки дають попередження.

Застереження те саме, що й для будь-яких підказок: вони заморожують рішення. MAX_EXECUTION_TIME і SET_VAR - безпечні запобіжники; підказки індексів і порядку з'єднань - крайній засіб із коментарем, чому вони там.

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

Довгий час MySQL виконував з'єднання лише вкладеними циклами (nested loop): для кожного рядка першої таблиці шукаємо відповідні рядки в другій. З індексом на колонці з'єднання це дуже ефективно. Без індексу - катастрофа: повне сканування другої таблиці на кожен рядок першої (частково пом'якшене буфером block nested loop).

Hash join (MySQL 8.0.18+) працює інакше:

  1. побудова: менша таблиця (після фільтрів) читається в пам'ять, будується хеш-таблиця за ключем з'єднання;
  2. перевірка: більша таблиця читається один раз, для кожного рядка ключ шукається в хеш-таблиці.

Кожна таблиця читається один раз - замість N × M порівнянь виходить N + M.

EXPLAIN FORMAT=TREE
SELECT * FROM orders o JOIN import_rows r ON r.external_id = o.external_id;

-> Inner hash join (r.external_id = o.external_id)
    -> Table scan on r
    -> Hash
        -> Table scan on o

Коли MySQL обирає hash join:

  • з'єднання, для якого немає придатного індексу (з 8.0.20 hash join повністю замінив block nested loop);
  • зазвичай за рівністю, але з 8.0.20 і для нерівностей, зовнішніх з'єднань, напівз'єднань (EXISTS, IN) і антиз'єднань (NOT EXISTS);
  • коли є індекс, оптимізатор найчастіше лишається на вкладених циклах з пошуком в індексі.

Пам'ять: хеш-таблиця будується в межах join_buffer_size. Якщо не вміщується - дані розбиваються на частини у тимчасових файлах на диску, і з'єднання сповільнюється (але все одно зазвичай краще за повний перебір). Для великих аналітичних з'єднань має сенс збільшити join_buffer_size для запиту через /*+ SET_VAR(join_buffer_size = ...) */.

Керування: окремі підказки HASH_JOIN / NO_HASH_JOIN у сучасних версіях не діють. Використовуються BNL / NO_BNL (історично про block nested loop, тепер про hash join), або optimizer_switch: block_nested_loop=off.

Що це означає на практиці:

  • hash join не заміна індексам для OLTP. Запит «замовлення користувача з товарами» має йти індексами: читати всю таблицю товарів заради 5 рядків - погано навіть одним проходом;
  • для разових і аналітичних запитів (звірка імпорту з існуючими даними, звіти на репліці) hash join робить прийнятними запити без спеціальних індексів;
  • побачивши hash join у плані частого запиту, варто перевірити, чи не бракує індексу на колонці з'єднання. Часта причина - різні типи чи кодування колонок, через які індекс непридатний.

Докладніше в документації: Оптимізація hash join

Для деяких запитів MySQL створює внутрішню тимчасову таблицю - проміжне сховище результату. В EXPLAIN це Using temporary в Extra, у TREE-форматі - вузли на кшталт Aggregate using temporary table чи Materialize.

Коли вона з'являється:

  • GROUP BY, який не можна виконати по порядку індексу;
  • GROUP BY і ORDER BY за різними колонками (згрупувати, потім пересортувати);
  • DISTINCT разом із ORDER BY;
  • UNION (з дедуплікацією; UNION ALL у більшості випадків обходиться без неї);
  • матеріалізація похідних таблиць, CTE, підзапитів;
  • віконні функції;
  • багатотабличний UPDATE, що читає з таблиці, яку змінює.

Де вона живе. За замовчуванням внутрішні тимчасові таблиці в пам'яті створює рушій TempTable. Ліміт - temptable_max_ram (у MySQL 8.4 за замовчуванням 3% оперативної пам'яті в межах 1-4 ГБ) на весь сервер, а не на запит. Коли він вичерпаний, дані йдуть на диск - у внутрішні таблиці InnoDB.

Також на окрему таблицю діє tmp_table_size - перевищення переводить на диск саме її.

Як побачити, що тимчасові таблиці йдуть на диск:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables       - усього внутрішніх тимчасових таблиць
-- Created_tmp_disk_tables  - з них на диску

SELECT * FROM sys.statements_with_temp_tables
ORDER BY disk_tmp_tables DESC LIMIT 10;   -- які запити винні

Зростання частки дискових таблиць - сигнал розібратися з конкретними запитами.

Як позбутися тимчасової таблиці (краще, ніж збільшувати ліміти):

  • індекс під GROUP BY: якщо групування йде за колонками індексу після рівностей з WHERE, MySQL групує на льоту в порядку індексу;
  • однакові GROUP BY і ORDER BY: від MySQL 8.0 GROUP BY не сортує неявно, тож ORDER BY за тими самими колонками дешевий;
  • менше колонок у проміжному результаті: SELECT * з TEXT-полями роздуває тимчасову таблицю;
  • UNION ALL замість UNION, коли дублікати неможливі чи не важливі;
  • агрегувати до з'єднання: спершу згрупувати велику таблицю в похідній таблиці, потім з'єднати з довідниками.

Коли збільшувати ліміти: якщо аналітичні запити на репліці регулярно йдуть на диск і переписати їх не вдається. Підвищувати варто разом із контролем загальної пам'яті: ліміт TempTable глобальний, а tmp_table_size може застосовуватися до кожної з таблиць одночасно в багатьох сесіях.

Докладніше в документації: Внутрішні тимчасові таблиці

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 (чому оптимізатор помилився) → виправлення статистики, індексу чи запиту.

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

Оптимізатор обирає план за оцінками: скільки рядків у таблиці, скільки різних значень в індексі, скільки рядків підійде під умову. Якщо оцінки хибні, план теж.

Статистика індексів InnoDB:

  • постійна (innodb_stats_persistent = ON, за замовчуванням) - зберігається в mysql.innodb_table_stats і mysql.innodb_index_stats, переживає перезапуск, тож плани стабільні;
  • збирається вибірково: читається innodb_stats_persistent_sample_pages (20) випадкових сторінок індексу, а не весь індекс. Для великих таблиць з нерівномірними даними цього може бути замало;
  • автоматично перераховується (innodb_stats_auto_recalc), коли змінилося понад 10% рядків, - у фоні, з невеликою затримкою.

Коли статистика підводить:

  • щойно завантажено великий обсяг даних - автоперерахунок ще не відбувся;
  • зміни малі відносно таблиці (менше 10%), але зачепили саме «гарячу» частину;
  • сильний перекіс: 20 сторінок вибірки не відображають розподілу.

Що робити:

ANALYZE TABLE orders;   -- перерахувати статистику зараз

-- для конкретної великої таблиці - більше сторінок вибірки
ALTER TABLE orders STATS_SAMPLE_PAGES = 200;

ANALYZE TABLE для InnoDB швидкий (та сама вибірка сторінок), але викликає неявний коміт, тож усередині транзакції застосунку його не запускають.

Гістограми (MySQL 8.0+) описують розподіл значень колонки:

ANALYZE TABLE orders UPDATE HISTOGRAM ON status, country WITH 64 BUCKETS AUTO UPDATE;

SELECT column_name, JSON_EXTRACT(histogram, '$."histogram-type"')
FROM information_schema.column_statistics WHERE table_name = 'orders';

Навіщо вони, якщо є статистика індексів. Для колонок з індексом оптимізатор оцінює рядки через index dives. Для колонок без індексу він знає лише кількість рядків і бере грубі припущення. Гістограма каже, що status = 'refunded' - це 0,1% таблиці, а status = 'paid' - 90%. Це впливає на порядок з'єднань і вибір способу доступу до інших таблиць.

Особливості гістограм:

  • корисні для колонок без індексу, з нерівномірним розподілом, що з'являються в умовах і з'єднаннях;
  • не створюються для колонок з одноколонковим унікальним індексом;
  • за замовчуванням оновлюються лише вручну. У MySQL 8.4 з'явилася опція AUTO UPDATE - тоді гістограму оновлює і ANALYZE TABLE, і фоновий перерахунок статистики;
  • побудова гістограми читає таблицю (з вибіркою в межах histogram_generation_max_mem_size), тож на великих таблицях її варто запускати поза піком.

Добра практика: після масового імпорту чи міграції даних запускати ANALYZE TABLE для зачеплених таблиць - це дешево і прибирає цілий клас «раптом повільних» запитів.

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

Наївна вставка по одному рядку в автокоміті - найповільніший варіант: на кожен рядок окремий запит по мережі, окремий коміт і окремий fsync журналу. Мільйон рядків - мільйон fsync.

1. Групувати рядки в один INSERT.

INSERT INTO products (sku, name, price) VALUES
    ('A-1', 'Товар 1', 100.00),
    ('A-2', 'Товар 2', 150.00),
    ...;   -- сотні-тисячі рядків на запит

У Laravel - DB::table('products')->insert($chunk) частинами по 500-2000 рядків. Розмір запиту обмежений max_allowed_packet, а кількість параметрів у PDO - теж не безмежна.

2. Комітити пачками, а не кожен рядок.

foreach (array_chunk($rows, 1000) as $chunk) {
    DB::transaction(fn () => DB::table('products')->insert($chunk));
}

Але й одна гігантська транзакція на мільйони рядків погана: величезний undo-журнал, довгий відкат при помилці, відставання реплік. Транзакції по кілька тисяч рядків - баланс.

3. LOAD DATA - найшвидший спосіб для файлу:

LOAD DATA LOCAL INFILE '/tmp/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
(sku, name, price);

LOCAL вимагає дозволу на обох боках (local_infile), що з міркувань безпеки часто вимкнено.

4. Вставляти в порядку первинного ключа. Тоді рядки дописуються в кінець кластерного індексу без розщеплення сторінок. Відсортований файл вантажиться помітно швидше за перемішаний.

5. Тимчасово вимкнути перевірки в сесії (лише для довірених даних):

SET unique_checks = 0;        -- відкладені перевірки вторинних унікальних індексів
SET foreign_key_checks = 0;   -- без перевірок зовнішніх ключів
-- завантаження
SET unique_checks = 1;
SET foreign_key_checks = 1;

Відповідальність за коректність даних переходить на вас: дублікати чи «осиротілі» зовнішні ключі після цього ніхто не впіймає.

6. Вторинні індекси - після завантаження. Для порожньої таблиці часто швидше завантажити дані лише з первинним ключем, а вторинні індекси створити потім: InnoDB будує індекс сортуванням, що ефективніше за мільйони окремих вставок у дерево.

7. Що ще впливає:

  • innodb_autoinc_lock_mode = 2 (за замовчуванням у MySQL 8) - паралельні вставки не блокують одна одну на автоінкременті;
  • достатній буферний пул і журнал повтору (innodb_redo_log_capacity);
  • для тимчасового імпорту на окремому інстансі можна послабити innodb_flush_log_at_trx_commit - але ніколи на основній базі з живими даними;
  • після завантаження - ANALYZE TABLE, щоб оптимізатор знав про нові дані.

Не забувати про репліки: мільйонний імпорт генерує стільки ж подій у бінарному журналі, і репліки відстануть. Пауза між пачками дає їм наздогнати.

Докладніше в документації: Масове завантаження даних в InnoDB