SQL

MySQL индексы: оптимизация запросов, анализ EXPLAIN и типичные ошибки

20.04.2026

Медленные запросы к базе данных — одна из самых частых причин тормозов веб-приложения. В 90% случаев проблема решается правильно расставленными индексами. В этой статье разберём, как MySQL использует индексы, как читать вывод EXPLAIN и каких ошибок избегать.

Что такое индекс и как он работает

Индекс в MySQL — это отдельная структура данных (по умолчанию B-Tree), которая хранит отсортированные значения одного или нескольких столбцов с указателями на строки таблицы. Без индекса MySQL делает full table scan — читает все строки подряд. С индексом — находит нужные строки за O(log n).

Добавить индекс к существующей таблице просто:

-- Простой индекс на один столбец
CREATE INDEX idx_user_email ON users (email);

-- Уникальный индекс
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Составной индекс
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

-- Посмотреть индексы таблицы
SHOW INDEX FROM orders;

При создании таблицы индексы можно указывать сразу:

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  -- PRIMARY KEY — это тоже индекс
    category_id INT UNSIGNED NOT NULL,
    slug VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,

    UNIQUE KEY uq_slug (slug),
    INDEX idx_category (category_id),
    INDEX idx_category_price (category_id, price)
) ENGINE=InnoDB;

Команда EXPLAIN: читаем план запроса

Перед оптимизацией нужно понять, что именно делает MySQL. Добавьте EXPLAIN перед любым SELECT:

EXPLAIN SELECT * FROM orders
WHERE user_id = 42
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

Ключевые столбцы в выводе:

  • type — тип доступа. От худшего к лучшему: ALL → index → range → ref → eq_ref → const
  • key — какой индекс MySQL выбрал (NULL = индекс не используется)
  • rows — оценка числа строк, которые будут просмотрены
  • Extra — дополнительная информация: Using index, Using filesort, Using temporary

Самые опасные сигналы в Extra:

  • Using filesort — MySQL сортирует результат в памяти или на диске (медленно на больших выборках)
  • Using temporary — создаётся временная таблица (очень медленно)
  • type: ALL при большом rows — полный перебор, нужен индекс

Для более подробного анализа используйте EXPLAIN ANALYZE (MySQL 8.0+) или EXPLAIN FORMAT=JSON:

-- Реально выполняет запрос и показывает фактическое время
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- Вывод в JSON с деревом выполнения
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 42;

Составные индексы и правило левого префикса

Составной индекс (a, b, c) работает для запросов по a, по (a, b) и по (a, b, c). Но не работает для запросов только по b или c. Это называется правилом левого префикса.

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

-- Индекс ИСПОЛЬЗУЕТСЯ:
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid' AND created_at > '2024-01-01';

-- Индекс НЕ ИСПОЛЬЗУЕТСЯ (пропущен user_id):
SELECT * FROM orders WHERE status = 'paid';
SELECT * FROM orders WHERE created_at > '2024-01-01';

Порядок столбцов в составном индексе важен. Общее правило: сначала столбцы с условием равенства =, потом — с диапазоном >, <, BETWEEN.

-- Хорошо: сначала равенство, потом диапазон
CREATE INDEX idx_good ON orders (user_id, status, created_at);
SELECT * FROM orders WHERE user_id = 5 AND status = 'pending' AND created_at > '2024-06-01';

-- Плохо: диапазон посередине обрывает использование индекса для status
CREATE INDEX idx_bad ON orders (user_id, created_at, status);
-- Для этого запроса status уже не войдёт в индекс
SELECT * FROM orders WHERE user_id = 5 AND created_at > '2024-06-01' AND status = 'pending';

Покрывающие индексы (Covering Index)

Если индекс содержит все столбцы, нужные запросу, MySQL не обращается к основной таблице совсем. В EXPLAIN это видно как Using index в столбце Extra.

-- Запрос возвращает только user_id и created_at
SELECT user_id, created_at FROM orders WHERE user_id = 42;

-- Создаём покрывающий индекс (включает оба столбца из SELECT и WHERE)
CREATE INDEX idx_covering ON orders (user_id, created_at);

-- EXPLAIN покажет: type=ref, Extra=Using index — чтение только из индекса
EXPLAIN SELECT user_id, created_at FROM orders WHERE user_id = 42;

Покрывающий индекс особенно эффективен для отчётных запросов, которые читают много строк, но лишь несколько столбцов.

Типичные ошибки, из-за которых индекс не работает

Даже если индекс создан, MySQL может его проигнорировать:

1. Функция над индексированным столбцом

-- ПЛОХО: MySQL не может использовать индекс на email, потому что применяет функцию
SELECT * FROM users WHERE LOWER(email) = 'ivan@example.com';

-- ХОРОШО: приводим значение к нижнему регистру в запросе, не трогаем столбец
SELECT * FROM users WHERE email = LOWER('Ivan@example.com');

-- ХОРОШО: используем функциональный индекс (MySQL 8.0+)
CREATE INDEX idx_email_lower ON users ((LOWER(email)));

2. Неявное приведение типов

-- Столбец phone имеет тип VARCHAR — индекс есть
-- ПЛОХО: передаём число, MySQL конвертирует и не использует индекс
SELECT * FROM users WHERE phone = 79161234567;

-- ХОРОШО: типы совпадают
SELECT * FROM users WHERE phone = '79161234567';

3. Условие с LIKE и ведущим процентом

-- ПЛОХО: ведущий % делает индекс бесполезным
SELECT * FROM products WHERE name LIKE '%телефон%';

-- ХОРОШО: фиксированный префикс — индекс работает
SELECT * FROM products WHERE name LIKE 'телефон%';

-- Для полнотекстового поиска используйте FULLTEXT индекс:
CREATE FULLTEXT INDEX ft_name ON products (name, description);
SELECT * FROM products WHERE MATCH(name, description) AGAINST ('телефон' IN BOOLEAN MODE);

4. OR между разными столбцами

-- ПЛОХО: MySQL не может эффективно использовать индексы для OR по разным столбцам
SELECT * FROM users WHERE email = 'a@b.com' OR phone = '79161234567';

-- ХОРОШО: UNION ALL использует оба индекса
SELECT * FROM users WHERE email = 'a@b.com'
UNION ALL
SELECT * FROM users WHERE phone = '79161234567' AND email != 'a@b.com';

Практический чеклист оптимизации

Когда запрос медленный, действуйте по этому порядку:

  1. Запустите EXPLAIN и проверьте type и key
  2. Если type = ALL и таблица большая — нужен индекс на столбцы из WHERE
  3. Если есть Using filesort — добавьте в индекс столбцы из ORDER BY
  4. Проверьте, нет ли функций над индексированными столбцами в WHERE
  5. Проверьте соответствие типов в условиях
  6. Для составного индекса: столбцы равенства идут первыми
  7. Рассмотрите покрывающий индекс, если запрос читает много строк и мало столбцов
-- Полезные запросы для мониторинга медленных запросов

-- Включить лог медленных запросов (в my.cnf или на лету):
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- логировать запросы дольше 1 секунды
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Запросы без индексов через information_schema:
SELECT
    table_schema,
    table_name,
    table_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY table_rows DESC;

-- Проверить, какие индексы реально использовались (MySQL 8.0+):
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
ORDER BY count_read DESC;

Индексы — не серебряная пуля. Каждый индекс замедляет INSERT/UPDATE/DELETE и занимает место. Добавляйте их точечно, под конкретные медленные запросы, и регулярно чистите неиспользуемые.

Вам также может понравиться...