Для чего используется несколько типов Index в БД?

Типы индексов в базах данных

Индексы — это критически важные структуры данных, которые значительно повышают производительность запросов. Разные типы индексов оптимизированы под разные сценарии использования.

B-Tree индекс (наиболее универсальный)

B-Tree — это сбалансированная структура дерева, используется по умолчанию в большинстве СУБД (PostgreSQL, MySQL, SQLite).

Когда использовать:

• Поиск по точному значению: WHERE user_id = 123
• Диапазонные запросы: WHERE age > 18 AND age < 65
• Сортировка: ORDER BY created_at
• Проверка NULL: WHERE column IS NULL

Пример:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Hash индекс

Hash индекс хранит хеши значений и идеален для точного совпадения.

Когда использовать:

• Только точный поиск по значению
• Высокая кардинальность (много уникальных значений)
• Быстрые lookups в памяти

Ограничения:

• НЕ работает с диапазонами
• НЕ поддерживает ORDER BY
• Занимает больше памяти

Пример:

CREATE INDEX idx_user_uuid ON users USING HASH(uuid);

BRIN индекс (Block Range Index)

BRIN — индекс, который хранит информацию о диапазонах значений для блоков таблицы.

Когда использовать:

• Очень большие таблицы (миллиарды строк)
• Данные отсортированы или квазиотсортированы
• Временные серии (created_at, updated_at)
• Когда размер индекса критичен

Преимущества:

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

Пример:

CREATE INDEX idx_events_timestamp ON events USING BRIN(event_time);

GiST индекс (Generalized Search Tree)

GiST — обобщённое дерево поиска для геопространственных данных и полнотекстового поиска.

Когда использовать:

• Геометрические поиски
• Полнотекстовый поиск
• JSON поиск в PostgreSQL

Пример:

CREATE INDEX idx_locations_geom ON locations USING GIST(geom);
CREATE INDEX idx_documents_search ON documents USING GIST(content);

GIN индекс (Generalized Inverted Index)

GIN — инвертированный индекс, идеален для многозначных колонок (массивы, JSONB).

Когда использовать:

• Поиск в массивах: WHERE tags @> ARRAY[node]
• JSONB поиск: WHERE data @> {status: active}
• Полнотекстовый поиск (быстрее чем GiST)

Пример:

CREATE INDEX idx_users_tags ON users USING GIN(tags);
CREATE INDEX idx_config_data ON config USING GIN(data);

Композитные индексы (Multi-column indexes)

Композитные индексы оптимизируют запросы с несколькими условиями.

Правило порядка колонок:

1. Колонки из WHERE с точным совпадением
2. Колонки для сортировки (ORDER BY)
3. Колонки для диапазонного поиска

Пример:

CREATE INDEX idx_orders_status_user_created ON orders(status, user_id, created_at DESC);

SELECT * FROM orders WHERE status = 'completed' AND user_id = 42 ORDER BY created_at DESC;

Практические рекомендации

1. Всегда профилируй: используй EXPLAIN ANALYZE перед созданием индекса
2. Не переусложняй: каждый индекс замедляет INSERT и UPDATE
3. Следи за размером: большие индексы вытесняют данные из кэша
4. Foreign keys: индексируй колонки для связей
5. Частые запросы: индексируй то, что часто ищешь

Правильный выбор типа индекса может улучшить производительность запросов в 100+ раз, особенно на больших таблицах.