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

До MySQL 8.0 синтаксис INDEX (a DESC) приймався, але ігнорувався - індекс завжди будувався за зростанням. З 8.0 спадні індекси справжні: значення фізично впорядковані за спаданням.

Коли це потрібно - сортування в різних напрямках:

SELECT * FROM products
WHERE category_id = 3
ORDER BY rating DESC, price ASC
LIMIT 20;

• індекс (category_id, rating, price): читаючи вперед, отримуємо rating ASC, price ASC; назад - rating DESC, price DESC. Потрібної комбінації DESC, ASC немає - буде Using filesort;
• індекс (category_id, rating DESC, price ASC) віддає рядки рівно в потрібному порядку, запит читає 20 записів і зупиняється.

Коли спадний індекс НЕ потрібен. Сортування в одному напрямку будь-яким індексом обслуговується і вперед, і назад:

-- індекс (user_id, created_at)
WHERE user_id = 7 ORDER BY created_at DESC LIMIT 20
-- EXPLAIN: Extra = Backward index scan; Using index condition...

Зворотне сканування працює, але в InnoDB трохи повільніше за пряме: сторінки зв'язані у двонапрямлений список, проте всередині сторінки записи оптимізовані для руху вперед. На гарячих запитах «останні N записів» спадний індекс (user_id, created_at DESC) дає невеликий, але вимірний виграш - і прибирає Backward index scan з плану.

Інші ситуації:

• MIN()/MAX() за колонкою індексу оптимізуються в обох напрямках;
• GROUP BY зі спадними частинами індексу підтримується;
• спадні індекси підтримує лише InnoDB, і не для FULLTEXT, SPATIAL чи хеш-індексів.

У Laravel-міграції напрям колонки в індексі задається через сирий вираз:

$table->rawIndex('category_id, rating DESC, price', 'products_category_rating_price_idx');

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

PostgreSQL має спадні індекси давно, плюс NULLS FIRST/LAST. У MySQL NULL завжди вважається найменшим значенням, і окремого керування його позицією в індексі немає.

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

Оптимізаторні підказки - коментарі спеціального вигляду одразу після ключового слова 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