Какие индексы используются в базах данных?

Индексы в базах данных: полный обзор для PHP-разработчика

В контексте Backend-разработки на PHP понимание индексов — критически важный навык для оптимизации производительности приложений, работающих с базами данных. Индексы представляют собой специальные структуры данных, которые ускоряют операции поиска (SELECT), фильтрации (WHERE) и сортировки (ORDER BY) за счет дополнительных затрат на запись и хранение.

Основные типы индексов

1. B-Tree индекс (дерево поиска)

Наиболее распространенный тип, используемый по умолчанию в MySQL/PostgreSQL. Эффективен для:

• Точных совпадений (WHERE column = 'value')
• Диапазонных запросов (WHERE column BETWEEN 10 AND 20)
• Сортировки (ORDER BY column)
• Префиксного поиска (WHERE column LIKE 'prefix%')

-- Создание B-Tree индекса
CREATE INDEX idx_users_email ON users(email);

2. Хэш-индекс

Использует хэш-таблицы для сверхбыстрого поиска точных совпадений, но не поддерживает:

• Диапазонные запросы
• Сортировку
• Частичные совпадения

-- Пример для PostgreSQL
CREATE INDEX idx_users_id_hash ON users USING HASH(id);

3. Составной (композитный) индекс

Индекс по нескольким столбцам, учитывающий их порядок:

• Правило левого префикса: индекс (col1, col2, col3) работает для запросов с col1, (col1, col2), (col1, col2, col3), но НЕ для (col2, col3)

CREATE INDEX idx_users_name_department ON users(last_name, department_id);

4. Уникальный индекс

Гарантирует уникальность значений (или комбинаций значений) в индексируемых столбцах:

CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

5. Полнотекстовый индекс

Специализированный индекс для текстового поиска с поддержкой морфологии, релевантности:

-- MySQL FULLTEXT индекс
CREATE FULLTEXT INDEX idx_articles_content ON articles(content);

6. Пространственный индекс (Spatial)

Для географических данных и геометрических объектов:

CREATE SPATIAL INDEX idx_locations_coords ON locations(coordinates);

7. Частичный индекс

Индексирует только подмножество строк, соответствующих условию (экономит место):

-- PostgreSQL частичный индекс для активных пользователей
CREATE INDEX idx_users_active ON users(email) WHERE is_active = TRUE;

Практические рекомендации для PHP-разработчика

Когда создавать индексы:

• Столбцы в условиях WHERE, JOIN, ORDER BY
• Внешние ключи (часто индексируются автоматически)
• Часто используемые комбинации полей в запросах
• Уникальные поля (почта, логин)

Когда избегать индексов:

• Часто изменяемые таблицы с интенсивной записью
• Маленькие таблицы (< 1000 строк)
• Столбцы с низкой селективностью (пол с значениями М/Ж)
• Избыточные индексы, дублирующие друг друга

Анализ использования индексов:

-- MySQL: объяснение плана запроса
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- PostgreSQL: статистика использования индексов
SELECT * FROM pg_stat_user_indexes;

Оптимизация запросов в PHP-приложениях

// Пример: использование подготовленных выражений с индексами
$stmt = $pdo->prepare("SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY created_at DESC");
$stmt->execute([$userId, 'completed']);
$orders = $stmt->fetchAll();

// Для составного индекса (user_id, status, created_at) этот запрос будет эффективен

Ключевые метрики для мониторинга:

• Index hit rate — процент попаданий в кэш индексов
• Sequential scans vs Index scans в PostgreSQL
• Rows examined vs Rows returned в MySQL

Распространенные ошибки:

1. Индексирование всех столбцов подряд
2. Игнорирование порядка полей в составных индексах
3. Отсутствие индексов на полях в JOIN условиях
4. Недостаточное обновление статистики индексов

Правильное использование индексов в средних и крупных PHP-проектах может ускорить выполнение запросов в десятки и сотни раз, снижая нагрузку на базу данных и улучшая отклик приложения для пользователей.