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

Question

Индексы в БД работают как оглавление в книге — они ускоряют поиск данных, но замедляют добавление и обновление, так как их нужно поддерживать в актуальном состоянии. Основные типы: B-Tree для сортировки и поиска по диапазону, Hash для точного совпадения, GiST и SPATIAL для геоданных, GIN для составных значений (массивы, полнотекстовый поиск) и BRIN для очень больших таблиц с естественной сортировкой данных.

YeaHub · Accepted Answer

Для чего нужны индексы?Индекс — это отдельная структура данных, которая хранит копию части данных таблицы (значения определенных столбцов) в специальном, удобном для поиска виде. Его основная цель — минимизировать количество данных, которые СУБД должна просканировать для выполнения запроса.Основные типы индексовB-Tree (B-дерево)Назначение: Самый распространенный тип. Эффективен для поиска по равенству (=), диапазону (BETWEEN, >, <), а также для сортировки (ORDER BY).Как работает: Хранит данные в сбалансированном дереве, что позволяет быстро находить значения за логарифмическое время.Пример: CREATE INDEX idx_user_email ON users(email);HashНазначение: Только для поиска по точному совпадению (=). Не подходит для диапазонов или сортировки.Как работает: Использует хеш-таблицу. Обычно работает быстрее B-Tree для операций равенства, но имеет ограниченную применимость.Пример: CREATE INDEX idx_user_id_hash ON users USING HASH(id);GIN (Generalized Inverted Index)Назначение: Для индексации составных значений, где один документ/запись содержит множество значений.Области применения: Полнотекстовый поиск, массивы, JSONB-документы в PostgreSQL.Пример: CREATE INDEX idx_products_tags ON products USING GIN(tags); (где tags — массив)GiST (Generalized Search Tree)Назначение: Индекс для данных, которые можно представить в геометрическом пространстве. Поддерживает различные стратегии поиска (близости, пересечения).Области применения: Географические данные (поиск объектов в радиусе), полнотекстовый поиск (раньше был основным).Пример: CREATE INDEX idx_places_location ON places USING GIST(location);SPATIAL (в MySQL)Назначение: Аналог GiST для MySQL, предназначенный для работы с геоданными.Пример: CREATE SPATIAL INDEX idx_geom ON maps(geom);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 для огромных лог-таблиц с временными метками.