Индексация MySQL: от 2‑секундных поисков до результатов за 30 мс

Правильный индекс может превратить 2‑секундный поиск товара в 30 мс – улучшение на 98%. Тем не менее, большинство сайтов WordPress работают только с индексами по умолчанию, а многие кастомные PHP-приложения создаются без какой-либо стратегии индексации. Это руководство научит вас проектировать индексы, которые заставят вашу базу данных летать.

Почему индексация важна для веб-производительности

Когда пользователь посещает страницу, которая запрашивает базу данных (списки продуктов, результаты поиска, профили пользователей), MySQL должен найти соответствующие строки. Без индексов MySQL выполняет полное сканирование таблицы – читает каждую строку, пока не найдет совпадения. Для таблицы из 100 000 строк полное сканирование занимает 200‑500 мс. При 1000 одновременных пользователей это превращается в секунды задержки, и процессор сервера работает на пределе.

Индексы подобны индексу книги: они точно указывают MySQL, где найти строки, превращая операцию O(n) в O(log n). Результат – запросы, которые возвращают результаты за миллисекунды вместо секунд.

Как работают индексы MySQL (простыми словами)

MySQL по умолчанию использует B‑Tree индексы. Они хранят значения столбцов в отсортированной древовидной структуре. Когда вы выполняете запрос с предложением WHERE по индексированному столбцу, MySQL обходит дерево, чтобы найти соответствующие значения, примерно за log₂(N) шагов. Для 1 миллиона строк это около 20 шагов вместо 1 миллиона сканирований.

Критическое правило: Индексы помогают при фильтрации по равенству (=), диапазону (<, >, BETWEEN) или совпадению префикса (LIKE 'термин%'). Они не могут помочь с LIKE '%термин%' (начальный символ подстановки) или вызовами функций, такими как WHERE DATE(created_at) = '2025-01-01'.

Выявление медленных запросов – использование EXPLAIN

Прежде чем добавлять индексы, найдите самые медленные запросы. Включите журнал медленных запросов MySQL:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;  -- log queries slower than 0.5 seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Затем выполните mysqldumpslow -s t /var/log/mysql/slow.log, чтобы увидеть самые медленные запросы.

Далее добавьте EXPLAIN перед любым подозрительным запросом, чтобы увидеть, как MySQL его выполняет. Например:

EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price < 100;

Посмотрите на столбец type:

• ALL – полное сканирование таблицы (хуже всего).
• range или ref – использование индекса (хорошо).
• const – поиск по первичному ключу (лучше всего).

Также проверьте rows – количество сканируемых строк. Если оно близко к общему количеству строк в таблице, добавьте индекс.

Практическая стратегия индексации – простые и составные индексы

Индексы на одном столбце

Используйте, когда вы часто фильтруете по одному столбцу.

CREATE INDEX idx_user_id ON orders (user_id);

Составные (многоколоночные) индексы

Когда вы фильтруете по нескольким столбцам, составной индекс гораздо эффективнее отдельных индексов. MySQL может использовать только один индекс на ссылку на таблицу, но составной индекс охватывает несколько столбцов.

-- Slow: 2 seconds, scans 50,000 rows
SELECT * FROM products WHERE category_id = 5 AND price < 100;

-- Add composite index (order matters!)
CREATE INDEX idx_category_price ON products (category_id, price);

-- Now: 30ms, scans 127 rows

Правило порядка: Поместите наиболее селективный столбец первым (тот, который отфильтровывает больше всего строк). Обычно category_id сокращает 50 000 → 5 000, затем price сокращает до 200. Если поменять местами (price, category_id), индекс будет менее эффективен для запросов с диапазоном по цене.

Покрывающие индексы (Святой Грааль)

Если вашему запросу нужны только столбцы, которые есть в индексе, MySQL может ответить на запрос полностью из индекса – без необходимости обращаться к данным таблицы. Это чрезвычайно быстро.

-- Query only needs id and name
SELECT id, name FROM products WHERE category_id = 5;

-- Covering index
CREATE INDEX idx_category_id_name ON products (category_id, name);

-- EXPLAIN will show "Using index" in Extra column

Реальный пример: фильтрация товаров в интернет-магазине – до и после

У интернет-магазина с 50 000 товаров была страница поиска, которая фильтровала по категории, бренду и ценовому диапазону. Исходный запрос:

SELECT * FROM products 
WHERE category_id = 12 
  AND brand_id IN (3,7,9) 
  AND price BETWEEN 50 AND 200 
ORDER BY price LIMIT 24;

Без индекса: полное сканирование таблицы – 2,3 секунды, 50 000 строк отсканировано.

После анализа: Они добавили составной индекс:

CREATE INDEX idx_category_brand_price ON products (category_id, brand_id, price);

Результат: Тот же запрос выполнился за 80 мс (улучшение на 96 %), отсканировав только 312 строк. Время загрузки страницы упало с 3,2 секунд до 0,6 секунды.

Индексация для WordPress (если вы должны остаться)

WordPress печально известен медленными запросами из-за своей обобщенной схемы. Таблица wp_postmeta хранит каждое пользовательское поле как пару ключ‑значение, что приводит к миллионам строк. Вы можете добавлять пользовательские индексы для улучшения распространенных запросов:

-- If you frequently query postmeta by meta_key and meta_value
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key_value (meta_key, meta_value(100));

Но даже с индексацией WordPress по-прежнему страдает от накладных расходов wp_query. Кастомные PHP-приложения, спроектированные с правильной схемой и индексами, всегда будут быстрее.

Обслуживание индексов – когда добавлять, когда удалять

• Добавляйте индексы на столбцы, используемые в WHERE, JOIN, ORDER BY и GROUP BY.
• Удаляйте индексы со столбцов, которые используются редко или имеют очень низкую мощность (например, столбец `status` только с двумя значениями). Каждый индекс замедляет операции INSERT, UPDATE и DELETE, потому что MySQL также должен обновлять индекс.
• Отслеживайте использование индексов с помощью SHOW INDEX FROM table; и SELECT * FROM sys.schema_unused_indexes; (MySQL 8.0).

Инструменты для анализа индексации

• EXPLAIN – встроенный, всегда начинайте с него.
• MySQL Workbench – визуальный EXPLAIN и панель производительности.
• pt‑query‑digest (Percona Toolkit) – анализирует журнал медленных запросов и предлагает индексы.
• phpMyAdmin – функция «Профилирование» для просмотра деталей выполнения запросов.

Пример клиента: платформа недвижимости

У сайта недвижимости было 200 000 объявлений. Пользователи искали по городу, ценовому диапазону, количеству спален и типу недвижимости. Страница поиска загружалась 4–6 секунд, потому что запрос каждый раз сканировал всю таблицу.

Решение: После анализа журналов медленных запросов и использования EXPLAIN мы добавили составной индекс:

CREATE INDEX idx_city_price_beds_type ON properties (city_id, price, bedrooms, property_type);

Мы также изменили запрос, чтобы использовать покрывающий индекс, выбирая только необходимые столбцы (id, заголовок, цена, миниатюра) вместо `SELECT *`.

Результаты:

• Время запроса: 5,2 секунды → 90 мс.
• Нагрузка на процессор сервера снизилась на 70%.
• Время загрузки страницы: 6,5 с → 1,2 с (включая фронтенд).
• Вовлеченность пользователей увеличилась на 34 %, потому что пользователи не бросали медленный поиск.

Распространенные ошибки и способы их исправления

Ошибка 1: Индексация каждого столбца

Проблема: Слишком много индексов замедляют запись. Решение: Индексируйте только столбцы, которые встречаются в WHERE, JOIN или ORDER BY.

Ошибка 2: Использование функций на индексированных столбцах

Плохо: WHERE DATE(created_at) = '2025-01-01' – игнорирует индекс на created_at. Хорошо: WHERE created_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59'.

Ошибка 3: Начальный символ подстановки в LIKE

Плохо: WHERE name LIKE '%виджет%' – никогда не использует индекс. Хорошо: Используйте индекс полнотекстового поиска для частичных совпадений.

Ошибка 4: Неиспользование EXPLAIN до и после

Всегда запускайте EXPLAIN перед добавлением индекса и снова после, чтобы проверить улучшение.

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

Для MySQL 8.0 вы можете включить performance schema и использовать схему sys для поиска отсутствующих индексов:

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

Для старых версий pt-index-usage из percona-toolkit анализирует журналы медленных запросов и предлагает индексы.

Готовы оптимизировать свою базу данных?

Я создаю кастомные PHP-приложения с правильной индексацией базы данных с самого первого дня. Будь то медленный сайт WordPress или кастомное приложение с узкими местами в запросах, я могу проанализировать ваши журналы медленных запросов, добавить правильные индексы и часто сократить время загрузки страниц на 50 % и более.

Давайте поговорим о проблемах производительности вашей базы данных. Я предоставлю бесплатную оценку ваших самых медленных запросов.

Все показатели производительности взяты из реальных проверок клиентов с использованием MySQL 8.0 на VPS Hostinger. Ваши результаты могут отличаться в зависимости от размера таблиц и серверного оборудования.