Що таке індекси в MySQL

Індекс — це окрема структура даних, яка прискорює пошук по таблиці. Без індексу MySQL робить повний перебір усіх рядків (Full Table Scan). З правильним індексом пошук виконується за логарифмічний час — O(log n) замість O(n).

Типи індексів

Тип	Опис	Коли використовувати
`PRIMARY KEY`	Унікальний, не NULL, один на таблицю	Основний ідентифікатор рядка
`UNIQUE`	Забороняє дублікати	Email, username, номер замовлення
`INDEX / KEY`	Звичайний неунікальний індекс	Поля у WHERE, ORDER BY, JOIN
`FULLTEXT`	Повнотекстовий пошук	Пошук по тексту (альтернатива LIKE)
`SPATIAL`	Геопросторовий	Координати, геолокація

Створення індексів

-- Під час створення таблиці
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status_created (status, created_at)
);

-- Додати до наявної таблиці
ALTER TABLE orders ADD INDEX idx_status (status);

-- Унікальний індекс
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- Складений індекс
CREATE INDEX idx_user_status ON orders(user_id, status);

Правило порядку в складених індексах

Складений індекс (a, b, c) працює для запитів: WHERE a, WHERE a AND b, WHERE a AND b AND c. Але НЕ працює для: WHERE b, WHERE c, WHERE b AND c — перший стовпець має бути в умові.

-- Цей індекс (user_id, status) допоможе:
SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';
SELECT * FROM orders WHERE user_id = 5;

-- Але НЕ допоможе:
SELECT * FROM orders WHERE status = 'pending';

Аналіз запиту через EXPLAIN

EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = 'pending'\G

-- Погано: type=ALL (повний перебір)
-- Добре: type=ref чи type=eq_ref

Перегляд індексів таблиці

SHOW INDEX FROM orders;

-- Або через information_schema:
SELECT INDEX_NAME, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

Видалення індексів

DROP INDEX idx_status ON orders;
ALTER TABLE orders DROP INDEX idx_status;

Не додавайте індекси на всі стовпці — кожен індекс уповільнює INSERT/UPDATE/DELETE і займає місце на диску. Додавайте лише ті індекси, які реально прискорюють запити.

Коли індекс не допомагає

Індекс не використовується оптимізатором навіть за наявності, якщо запит застосовує функцію до індексованого стовпця: WHERE YEAR(created_at) = 2026 змушує сервер обчислити YEAR() для кожного рядка, повністю ігноруючи індекс на created_at. Те саме з WHERE status LIKE '%pending%' — маска з провідним % не може використати B-Tree індекс, бо порядок сортування не допомагає знайти збіг усередині рядка. Неявне приведення типів — ще одна часта причина: порівняння стовпця VARCHAR із числом без лапок може призвести до повного сканування замість використання індексу.

Порівняння вартості: індекс на читання і запис

Операція	Без індексу	З індексом
SELECT за умовою	Full Table Scan, O(n)	O(log n), швидше на великих таблицях у рази
INSERT одного рядка	Лише запис даних	Додатковий запис у B-Tree кожного індексу
UPDATE індексованого стовпця	Лише оновлення даних	Перебудова позиції рядка в усіх зачеплених індексах

Читаємо вивід EXPLAIN порядково

EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = 'pending'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: ref
possible_keys: idx_user_status
          key: idx_user_status
      key_len: 4
          ref: const
         rows: 12
        Extra: Using index condition

possible_keys — список індексів, які оптимізатор розглядав; key — який реально обрав. Якщо key порожній за непорожнього possible_keys, оптимізатор вирішив, що повний скан дешевший — зазвичай так буває, коли вибірка охоплює більшу частину таблиці. rows — оцінка кількості переглянутих рядків, не гарантовано точна, але корисна для порівняння варіантів запиту.

Індекси для FULLTEXT і SPATIAL: межі застосовності

FULLTEXT-індекс працює лише з InnoDB (з MySQL 5.6) і MyISAM, не замінює повноцінний пошуковий рушій на кшталт Elasticsearch чи Sphinx на великих обсягах тексту і не вміє ранжувати релевантність так само гнучко. Для сайту з кількома тисячами статей FULLTEXT більш ніж достатній; для мільйонів документів із фасетним пошуком варто виносити пошук у зовнішній рушій, а MySQL лишити джерелом істини для даних.

Перевірка розміру і використання індексів

-- Розмір кожного індексу таблиці в мегабайтах
SELECT index_name, ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE table_name = 'orders' AND stat_name = 'size';

-- Індекси, які жодного разу не використовувалися з моменту запуску сервера
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0
ORDER BY object_schema, object_name;

Другий запит вимагає увімкненого performance_schema і показує невикористовувані індекси — кандидатів на видалення, якщо вони не потрібні для UNIQUE-обмежень. Детальний розбір оптимізації окремих запитів — у статті про читання плану запиту, а діагностика вже виконаних повільних запитів — у статті про Slow Query Log.