Что такое индексы в 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.