Какие типы индексов есть и для чего они нужны?

Для чего нужны индексы?

Индекс — это отдельная структура данных, которая хранит копию части данных таблицы (значения определенных столбцов) в специальном, удобном для поиска виде. Его основная цель — минимизировать количество данных, которые СУБД должна просканировать для выполнения запроса.

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

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

   • Назначение: Самый распространенный тип. Эффективен для поиска по равенству (=), диапазону (BETWEEN, >, <), а также для сортировки (ORDER BY).

   • Как работает: Хранит данные в сбалансированном дереве, что позволяет быстро находить значения за логарифмическое время.

   • Пример: CREATE INDEX idx_user_email ON users(email);

2. Hash

   • Назначение: Только для поиска по точному совпадению (=). Не подходит для диапазонов или сортировки.

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

   • Пример: CREATE INDEX idx_user_id_hash ON users USING HASH(id);

3. GIN (Generalized Inverted Index)

   • Назначение: Для индексации составных значений, где один документ/запись содержит множество значений.

   • Области применения: Полнотекстовый поиск, массивы, JSONB-документы в PostgreSQL.

   • Пример: CREATE INDEX idx_products_tags ON products USING GIN(tags); (где tags — массив)

4. GiST (Generalized Search Tree)

   • Назначение: Индекс для данных, которые можно представить в геометрическом пространстве. Поддерживает различные стратегии поиска (близости, пересечения).

   • Области применения: Географические данные (поиск объектов в радиусе), полнотекстовый поиск (раньше был основным).

   • Пример: CREATE INDEX idx_places_location ON places USING GIST(location);

5. SPATIAL (в MySQL)

   • Назначение: Аналог GiST для MySQL, предназначенный для работы с геоданными.

   • Пример: CREATE SPATIAL INDEX idx_geom ON maps(geom);

6. BRIN (Block Range INdex)

   • Назначение: Для очень больших таблиц, где данные физически отсортированы на диске (например, по timestamp). Занимает очень мало места.

   • Как работает: Хранит информацию о минимальном и максимальном значении в блоках страниц. Подходит для данных с естественной корреляцией с физическим расположением.

   • Пример: CREATE INDEX idx_logs_time ON logs USING BRIN(created_at);

Вывод:
Правильный выбор типа индекса критически важен для производительности:

• Используйте B-Tree по умолчанию для большинства столбцов, участвующих в WHERE и ORDER BY.

• Используйте GIN для индексации массивов, JSON и полнотекстового поиска.

• Используйте GiST/SPATIAL для геоданных.

• Используйте BRIN для огромных лог-таблиц с временными метками.