Индексы в SQL

Основные концепции

Индекс в SQL — это структура данных, которая ускоряет операции поиска и выборки строк из таблицы за счет дополнительного места на диске и замедления операций записи (INSERT, UPDATE, DELETE).

Принцип работы:

• Индекс хранит отсортированные копии значений одного или нескольких столбцов
• Каждое значение связано с указателем на соответствующую строку в таблице
• Вместо полного сканирования таблицы (full scan) БД использует индекс для быстрого поиска

Типы индексов

1. B-tree (сбалансированное дерево)

• Самый распространенный тип
• Эффективен для: точного поиска, диапазонных запросов, сортировки
• Сложность поиска: O(log n)
• Поддерживает операции: =, >, <, >=, <=, BETWEEN, LIKE (без ведущего %)

2. Hash-индекс

• Использует хеш-таблицу для поиска
• Эффективен только для точного сравнения (=)
• Не поддерживает диапазонные запросы и сортировку
• Используется в основном в памяти (например, в MySQL MEMORY)

3. GiST, GIN, SP-GiST (PostgreSQL)

• Для специализированных типов данных: полнотекстовый поиск, геоданные, массивы
• GIN — для композитных типов (массивы, JSONB)
• GiST — для геоданных и полнотекстового поиска

4. Bitmap-индекс

• Использует битовые маски для каждого уникального значения
• Эффективен для столбцов с небольшим количеством уникальных значений (кардинальность)
• Хорош для сложных комбинаций условий

Структура и организация

Кластеризованный (clustered) индекс:

• Определяет физический порядок данных в таблице
• Может быть только один на таблицу
• Листовые узлы содержат сами данные, а не указатели
• В MySQL InnoDB первичный ключ — кластеризованный индекс

Некластеризованный (non-clustered) индекс:

• Не влияет на физический порядок данных
• Листовые узлы содержат указатели на строки (или кластеризованный ключ)
• Может быть много на таблицу

Покрывающий (covering) индекс:

• Содержит все столбцы, необходимые для запроса
• Позволяет выполнить запрос без обращения к таблице
• Включает INCLUDE-столбцы (SQL Server) или использует композитный индекс

Практические аспекты

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

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

Когда избегать:

• Маленькие таблицы (полное сканирование быстрее)
• Столбцы с низкой селективностью (пол/статус)
• Часто обновляемые столбцы
• Таблицы с высокой нагрузкой на запись

Композитные индексы:

• Важен порядок столбцов (самый селективный первый)
• Работает для префиксных запросов (первые столбцы)
• Пример: INDEX(a, b, c) покроет WHERE a=1, WHERE a=1 AND b=2, но не WHERE b=2

Типичные проблемы и ошибки

1. Избыточное индексирование

• Каждый индекс замедляет запись
• Занимает место на диске
• Увеличивает время планирования запроса

2. Неправильный порядок столбцов

• Композитный индекс (status, created_at) не поможет при фильтрации только по created_at
• Нужно ставить наиболее селективные столбцы первыми

3. Игнорирование покрывающих индексов

• Запрос SELECT id, name FROM users WHERE email = ? можно покрыть индексом (email, id, name)
• Это исключит обращение к таблице

4. Индексы на функциях

• WHERE UPPER(name) = 'IVAN' не использует индекс на name
• Решение: функциональный индекс (PostgreSQL) или виртуальный столбец

5. Непонимание работы NULL

• В некоторых СУБД NULL не индексируется (Oracle)
• В PostgreSQL NULL включаются в B-tree индекс

Мониторинг и оптимизация

Инструменты:

• EXPLAIN ANALYZE — план выполнения запроса
• pg_stat_user_indexes (PostgreSQL) — статистика использования
• SHOW INDEX FROM table (MySQL)
• sys.dm_db_index_usage_stats (SQL Server)

Метрики:

• Seq Scans vs Index Scans
• Index usage ratio
• Bloat индексов (фрагментация)
• Cache hit ratio

Примеры для Go-разработчика

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

-- Композитный индекс с покрытием
CREATE INDEX idx_users_status_created 
ON users(status, created_at) 
INCLUDE (name, email);

-- Частичный индекс (PostgreSQL)
CREATE INDEX idx_active_users 
ON users(email) 
WHERE status = 'active';

-- Индекс для полнотекстового поиска (PostgreSQL)
CREATE INDEX idx_posts_search 
ON posts 
USING GIN(to_tsvector('russian', content));

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