Чем руководствоваться при добавлении индекса?

Чем руководствоваться при добавлении индекса в базу данных

Добавление индексов — это ключевая часть оптимизации производительности баз данных. Правильно созданные индексы могут ускорить выполнение запросов в сотни раз, но их неоправданное использование приводит к снижению скорости операций INSERT, UPDATE, DELETE и увеличению потребления дискового пространства. Я руководствуюсь следующими принципами:

1. Анализ типов и частоты запросов

Первым шагом я изучаю, какие запросы выполняются чаще всего и какие операции являются критическими для пользовательского опыта. Индексы нужно создавать для:

• Часто выполняемых SELECT-запросов, особенно с условиями WHERE
• JOIN-операций, где используются внешние ключи
• ORDER BY и GROUP BY по определенным колонкам
• Колонкам в условиях WHERE с операторами сравнения (=, >, <, BETWEEN)

Пример анализа запроса без индекса и с индексом:

-- Без индекса (может выполняться полное сканирование таблицы)
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';

-- После создания составного индекса на (user_id, status)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

2. Принципы выбора типа индекса

Для разных сценариев оптимальны разные типы индексов:

• B-tree индексы — стандартный выбор для большинства случаев (равенство, диапазоны)
• Hash индексы — только для операций равенства (=), не поддерживают диапазоны
• Составные (комбинированные) индексы — когда условия включают несколько колонок
• Уникальные индексы — для обеспечения уникальности данных (например, email пользователя)
• Полнотекстовые индексы — для поиска по текстовым данным (FULLTEXT в MySQL)

3. Правила создания составных индексов

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

-- Индекс на (col1, col2, col3) будет работать для:
-- WHERE col1 = 'val'
-- WHERE col1 = 'val' AND col2 = 'val'
-- WHERE col1 = 'val' AND col2 = 'val' AND col3 = 'val'
-- Но НЕ будет работать для:
-- WHERE col2 = 'val' (без col1)
-- WHERE col3 = 'val' (без col1 и col2)

CREATE INDEX idx_multi ON table_name(col1, col2, col3);

4. Мониторинг и использование статистики

Перед добавлением индекса я всегда проверяю:

• Кардинальность колонки — индекс эффективнее для колонок с высокой уникальностью значений
• Размер таблицы — для маленьких таблиц индексы часто не нужны (полное сканирование быстрее)
• План выполнения запроса с помощью EXPLAIN (в MySQL) или аналогичных инструментов

Пример использования EXPLAIN для анализа:

EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
-- Если в результате виден тип ALL (полное сканирование) — индекс необходим
-- Если виден тип ref или eq_ref с использованием существующего индекса — возможно, новый индекс не нужен

5. Баланс между производительностью чтения и записи

Я всегда учитываю, что каждый индекс:

• Ускоряет SELECT, но замедляет INSERT, UPDATE, DELETE
• Увеличивает объем хранимых данных
• Может привести к фрагментации индексов при частых изменениях данных

Ключевые рекомендации по балансу:

• Не индексировать колонки с частыми изменениями (например, счетчик просмотров)
• Ограничить количество индексов на таблицы с высокочастотными операциями записи
• Регулярно проводить обслуживание индексов (REINDEX, оптимизация)

6. Особенности для ORM и фреймворков

При работе с PHP фреймворками (Laravel, Symfony) я учитываю:

• Автоматическую генерацию индексов через миграции (но часто требуется дополнительная оптимизация)
• Индексы для внешних ключей, которые ORM часто использует в JOIN
• Индексы для колонок, участвующих в популярных scope-методах или фильтрации

Пример создания индекса в миграции Laravel:

// В файле миграции Laravel
Schema::table('users', function (Blueprint $table) {
    $table->index(['email', 'status'], 'idx_users_email_status');
});

Итоговый подход

Мой процесс принятия решения выглядит так:

1. Анализ медленных запросов через мониторинг или логи
2. Изучение планов выполнения этих запросов (EXPLAIN)
3. Выбор типа индекса и колонок на основе частоты и типа условий
4. Тестирование индекса на тестовой базе данных с реальными данными
5. Создание индекса в production и мониторинг изменения производительности
6. Периодическая ревизия индексов для удаления неиспользуемых или добавления новых по мере изменения паттернов запросов

Главный принцип: индекс должен создаваться не по предположениям, а на основе конкретных данных о производительности и паттернах использования базы данных. Индексы — это мощный инструмент оптимизации, но их нужно применять с пониманием компромиссов и постоянным мониторингом эффекта.