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

EXPLAIN показує план виконання запиту, не виконуючи його. Кожен рядок виводу - одна таблиця в запиті, у порядку, в якому MySQL їх читає.

EXPLAIN SELECT * FROM orders WHERE user_id = 7 AND status = 'paid';

Найважливіші колонки:

• type - спосіб доступу. Від найкращого до найгіршого:
  • const / system - щонайбільше один рядок за первинним чи унікальним ключем;
  • eq_ref - у з'єднанні один рядок цієї таблиці на кожен рядок попередньої (за унікальним ключем);
  • ref - пошук за неунікальним індексом з рівністю;
  • range - діапазон індексу (BETWEEN, >, IN, LIKE 'abc%');
  • index - повне сканування індексу (дешевше за таблицю, але все одно все);
  • ALL - повне сканування таблиці. На великій таблиці - головний сигнал проблеми.
• possible_keys - індекси, які теоретично підходять; key - обраний. NULL у key - індекс не використано.
• key_len - скільки байтів індексу використано. Для складеного індексу показує, скільки колонок з нього реально задіяно.
• rows - оцінка кількості рядків, які доведеться прочитати (за статистикою, не точна).
• filtered - який відсоток прочитаних рядків пройде решту умов. rows × filtered / 100 - скільки рядків піде далі.
• Extra - додаткові подробиці:
  • Using index - запит обслуговано лише з індексу (покривний індекс), чудово;
  • Using where - умову перевірено після читання рядка;
  • Using index condition - частину умови перевірено ще в індексі (Index Condition Pushdown);
  • Using filesort - потрібне окреме сортування;
  • Using temporary - потрібна тимчасова таблиця (часто GROUP BY чи DISTINCT).

Що шукати першим:

1. type = ALL на великій таблиці;
2. великі rows при маленькому результаті - індекс неселективний чи відсутній;
3. Using filesort / Using temporary на запитах, що мають бути швидкими.

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

У Laravel план зручно отримати прямо з будівника: User::where('email', $email)->explain()->dd();.

Докладніше в документації: Формат виводу EXPLAIN

Складений індекс (a, b, c) - це B-дерево, впорядковане спершу за a, всередині однакових a - за b, всередині однакових (a, b) - за c. Як телефонний довідник: прізвище, потім ім'я.

Правило лівого префікса: індекс можна використати для пошуку лише за початковими колонками:

CREATE INDEX orders_idx ON orders (user_id, status, created_at);

-- індекс працює
WHERE user_id = 7
WHERE user_id = 7 AND status = 'paid'
WHERE user_id = 7 AND status = 'paid' AND created_at > '2026-01-01'
WHERE status = 'paid' AND user_id = 7     -- порядок в умові не важливий

-- індекс для пошуку не працює
WHERE status = 'paid'                     -- пропущено user_id
WHERE created_at > '2026-01-01'

Знайти всіх Іванів у довіднику, впорядкованому за прізвищем, можна лише переглянувши його повністю.

Діапазон «обриває» індекс. Колонки після першої з діапазоном (>, <, BETWEEN, LIKE 'abc%') для пошуку вже не використовуються:

WHERE user_id = 7 AND created_at > '2026-01-01' AND status = 'paid'
-- індекс (user_id, created_at, status): пошук за user_id + діапазон created_at,
-- status перевіряється для кожного запису діапазону
-- індекс (user_id, status, created_at): пошук за всіма трьома - краще

Звідси практичне правило порядку колонок: спершу колонки з рівністю, потім колонка з діапазоном чи сортуванням.

Що ще дає порядок колонок:

• ORDER BY за колонками, що йдуть одразу після колонок з рівністю, не потребує сортування: WHERE user_id = 7 ORDER BY created_at з індексом (user_id, created_at);
• окремий індекс (user_id) не потрібен, якщо є (user_id, status) - лівий префікс його заміняє. Це зайвий індекс, що лише сповільнює запис.

Skip scan (MySQL 8.0.13+) частково обходить правило: якщо в першої колонки мало різних значень, а запит покривається індексом, MySQL може «перестрибувати» по значеннях першої колонки. У EXPLAIN видно Using index for skip scan. Розраховувати на це при проєктуванні не варто.

У Laravel: $table->index(['user_id', 'status', 'created_at']); - порядок у масиві і є порядком колонок в індексі.

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

Індекс допомагає лише тоді, коли умова записана так, що MySQL може шукати безпосередньо за значенням колонки. Найчастіші причини, чому це не так:

1. Функція чи вираз над колонкою.

-- індекс на created_at не використовується
WHERE YEAR(created_at) = 2026
WHERE DATE(created_at) = '2026-10-04'
WHERE price * 1.2 > 100

-- переписати на діапазон по «голій» колонці
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'
WHERE price > 100 / 1.2

Якщо без функції ніяк, у MySQL 8 є функціональні індекси: CREATE INDEX ... ((LOWER(email))).

2. Неявне приведення типів. Рядкова колонка порівнюється з числом:

-- phone VARCHAR(20), індекс є
WHERE phone = 380501234567     -- кожен рядок перетворюється на число, індекс не працює
WHERE phone = '380501234567'   -- індекс працює

Навпаки (числова колонка, рядкова константа) - не страшно: перетворюється константа. Те саме при з'єднаннях: колонки різних типів чи різних кодувань у JOIN можуть позбавити індексу.

3. LIKE з відсотком на початку.

WHERE name LIKE 'Олек%'    -- діапазон індексу, працює
WHERE name LIKE '%сандр%'  -- немає початку, з якого шукати

Для пошуку всередині тексту - FULLTEXT-індекс чи зовнішній пошуковий рушій.

4. Порушене правило лівого префікса складеного індексу: умова не містить першої колонки.

5. OR між різними колонками. WHERE email = ? OR phone = ? може взагалі не використати індекс або використати злиття індексів. Інколи краще UNION двох запитів.

6. Оптимізатор вирішив, що повне сканування дешевше. Якщо умова підходить під значну частину таблиці (наприклад, status = 'active' для 80% рядків), читати таблицю послідовно швидше, ніж робити тисячі переходів з індексу в кластерний індекс. Це не помилка, а правильне рішення.

7. Застаріла статистика. Після масового завантаження оцінки можуть бути хибними - допомагає ANALYZE TABLE.

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

Докладніше в документації: Як MySQL використовує індекси

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

Вимоги до хорошого первинного ключа:

• короткий - він копіюється в кожен вторинний індекс;
• незмінний - зміна ключа означає переміщення рядка в дереві й оновлення всіх індексів;
• монотонно зростаючий - нові рядки дописуються в кінець дерева, сторінки заповнюються щільно;
• завжди заданий (NOT NULL).

Варіант за замовчуванням - автоінкремент:

$table->id();   // BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY

8 байтів, зростає, нічого не коштує. BIGINT замість INT - щоб не впертися в ~4,29 млрд (пропуски в нумерації з'їдають запас швидше, ніж здається).

UUID - коли справді потрібен (генерація id на клієнті, злиття даних з різних баз, неперебірні публічні ідентифікатори):

• UUIDv4 (випадковий) - найгірший варіант для InnoDB. Кожна вставка йде у випадкову сторінку: розщеплення сторінок, фрагментація, сторінки заповнені наполовину, буферний пул використовується неефективно;
• UUIDv7 / ULID (впорядковані за часом) - вставляються майже послідовно, проблеми v4 зникають. Laravel HasUuids генерує саме впорядковані UUID;
• зберігати компактно - BINARY(16), а не CHAR(36).

Компроміс: внутрішній BIGINT-ключ для зв'язків плюс окрема унікальна колонка uuid для зовнішнього світу (URL, API).

Природні ключі (email, номер телефону, код ІПН) - погана ідея: вони змінюються, бувають довгими, а зміна каскадом зачіпає всі зовнішні ключі. Унікальний індекс на них - так; первинний ключ - ні.

Складений первинний ключ доречний у таблицях зв'язку «багато-до-багатьох»:

Schema::create('role_user', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
    $table->foreignId('role_id')->constrained();
    $table->primary(['user_id', 'role_id']);
});

Рядки одного користувача лежать поруч, а пошук «ролі користувача» читає сусідні записи.

Таблиця без первинного ключа отримає прихований внутрішній ключ, недоступний у запитах. До того ж Group Replication (InnoDB Cluster) та інструменти онлайн-змін схеми вимагають явного первинного ключа, а керовані хмарні MySQL часто вмикають sql_require_primary_key.

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

Префіксний індекс індексує не всю рядкову колонку, а лише перші N символів:

CREATE INDEX urls_path_idx ON urls (path(50));

Навіщо:

• ліміт довжини ключа - для формату рядка DYNAMIC це 3072 байти. VARCHAR(1000) в utf8mb4 важить до 4000 байтів і повністю в індекс не поміститься;
• TEXT і BLOB без префікса індексувати взагалі не можна;
• розмір індексу - менший індекс краще вміщується в буферний пул.

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

SELECT COUNT(DISTINCT path) / COUNT(*)            AS full_selectivity,
       COUNT(DISTINCT LEFT(path, 20)) / COUNT(*)  AS p20,
       COUNT(DISTINCT LEFT(path, 50)) / COUNT(*)  AS p50,
       COUNT(DISTINCT LEFT(path, 100)) / COUNT(*) AS p100
FROM urls;

Беруть найменшу довжину, селективність якої близька до повної. Для URL з однаковим початком (https://example.com/blog/...) префікс має бути довшим, ніж здається.

Обмеження префіксних індексів:

• не можуть бути покривними - повне значення все одно читається з таблиці;
• не допомагають ORDER BY і GROUP BY за колонкою;
• унікальний префіксний індекс перевіряє унікальність лише префікса: два різні URL з однаковими першими 50 символами не вставляться.

Альтернатива для пошуку за точним збігом довгого рядка - індекс на хеші:

ALTER TABLE urls
    ADD COLUMN path_hash BINARY(16) AS (UNHEX(MD5(path))) STORED,
    ADD UNIQUE INDEX (path_hash);

SELECT * FROM urls WHERE path_hash = UNHEX(MD5(?)) AND path = ?;

Індекс компактний (16 байтів), працює й для унікальності повних значень.

Історична довідка про Laravel. Schema::defaultStringLength(191) з'явився через старий ліміт 767 байтів (формат COMPACT): 191 × 4 = 764. На сучасних MySQL цей ліміт - 3072 байти, і VARCHAR(255) з індексом працює без хитрощів.

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