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

Индексы — это специальные структуры данных, которые позволяют СУБД быстро находить строки в таблице, минуя полное сканирование. Выбор типа индекса зависит от структуры данных и типов запросов. Наиболее распространены следующие типы (на примере PostgreSQL):

1. B-Tree (B-дерево)

   • Что это: Индекс по умолчанию, сбалансированное древовидное представление данных.
   • Когда использовать: Универсальный выбор. Идеален для операций сравнения: =, >, <, BETWEEN, IN, а также для LIKE с фиксированным префиксом ('text%') и сортировки (ORDER BY).
   • Пример: CREATE INDEX idx_users_name ON users(name);

2. Hash

   • Что это: Хеш-таблица, где ключ — это хеш от значения столбца.
   • Когда использовать: Только для точных совпадений по оператору равенства (=). Работает очень быстро для таких запросов, но не поддерживает диапазоны или сортировку.
   • Пример: CREATE INDEX idx_users_email ON users USING HASH(email);

3. GIN (Generalized Inverted Index)

   • Что это: Инвертированный индекс, который хранит для каждого значения (например, слова или элемента JSON) список строк, где оно встречается.
   • Когда использовать: Для поиска внутри составных типов данных: массивы (text[]), JSONB (jsonb), полнотекстовый поиск (tsvector). Эффективен для операторов содержания: @>, <@, ?, &&.
   • Пример: CREATE INDEX idx_posts_tags ON posts USING GIN(tags);

4. GiST (Generalized Search Tree)

   • Что это: Обобщенное поисковое дерево. Это не тип индекса, а фреймворк для построения различных индексных схем.
   • Когда использовать: Для сложных типов данных, таких как геометрические фигуры (поиск пересечений, вхождений) и полнотекстовый поиск.

5. BRIN (Block Range Index)

   • Что это: Очень легковесный индекс, который хранит минимальное и максимальное значение для больших диапазонов строк (блоков).
   • Когда использовать: Для очень больших таблиц, данные в которых имеют естественную корреляцию с их физическим расположением (например, логи с временными метками). Занимает мало места, но менее точен, чем B-Tree.

Общие рекомендации

• Компромисс: Индексы значительно ускоряют операции чтения (SELECT), но замедляют операции записи (INSERT, UPDATE, DELETE), так как СУБД необходимо обновлять не только таблицу, но и её индексы.
• Анализ: Используйте EXPLAIN ANALYZE для анализа планов запросов и определения, какие поля нуждаются в индексации.