Какие бывают индексы

Индексы в базах данных — это специальные структуры данных, которые ускоряют операции поиска и выборки, выступая в роли своего рода «оглавления» для таблицы. Они создаются для одного или нескольких столбцов и позволяют СУБД быстро находить строки, не сканируя всю таблицу. Существует несколько типов индексов, каждый из которых предназначен для определенных сценариев использования и типов данных.

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

Первичный индекс (Primary Index) автоматически создается при определении первичного ключа таблицы. Он уникален для каждой строки и обеспечивает быстрый доступ к данным по первичному ключу. В большинстве СУБД первичный индекс является кластеризованным, то есть определяет физический порядок хранения строк.

Уникальный индекс (Unique Index) гарантирует, что все значения в столбце (или группе столбцов) уникальны. Он полезен для предотвращения дублирования данных в полях, которые не являются первичными ключами, например, для email или номера паспорта.

Композитный индекс (Composite Index) включает два или более столбца в одном индексе. Он оптимизирует запросы, которые фильтруются по нескольким столбцам одновременно. Порядок столбцов в композитном индексе имеет большое значение: индекс эффективен для условий, которые начинаются с первого столбца, затем второго и так далее.

Индекс по битовой карте (Bitmap Index) эффективен для столбцов с небольшим количеством уникальных значений, например, для поля «пол» или «статус». Он сжимает данные, используя битовые карты, что делает его очень быстрым для операций агрегирования и объединения. Чаще всего применяется в хранилищах данных (OLAP), а не в системах с интенсивной записью.

Индекс с обратным списком (Inverted Index) используется в полнотекстовом поиске. Для каждого уникального слова в документе или наборе данных хранится список всех мест его встречаемости. Это позволяет быстро находить документы по ключевым словам. Пример — полнотекстовые индексы в MySQL (FULLTEXT) или PostgreSQL.

Пространственный индекс (Spatial Index) применяется для географических данных. Он облегчает запросы по местоположению, позволяя эффективно находить элементы в определенной области. Примеры — R-деревья в PostgreSQL (PostGIS) или MySQL.

Кластеризованный индекс (Clustered Index) определяет физический порядок хранения строк данных в таблице. Каждая таблица может иметь только один кластеризованный индекс, так как строки не могут быть отсортированы двумя способами одновременно. В MySQL (InnoDB) первичный ключ автоматически становится кластеризованным индексом.

Как это работает

Индексы обычно реализуются в виде B-деревьев (сбалансированных деревьев) или хеш-таблиц. B-дерево позволяет выполнять поиск, вставку и удаление за логарифмическое время, что значительно быстрее полного сканирования таблицы. Хеш-индексы эффективны для точного сравнения, но не поддерживают диапазонные запросы.

При создании индекса СУБД строит структуру, которая хранит значения столбца и ссылки на соответствующие строки. При выполнении запроса оптимизатор решает, использовать ли индекс, и если да, то обращается к нему, а не к таблице напрямую.

Пример

Рассмотрим таблицу users с полями id, email, name, created_at. Если часто выполняется запрос:

SELECT * FROM users WHERE email = 'user@example.com';

то создание уникального индекса на email ускорит его:

CREATE UNIQUE INDEX idx_users_email ON users (email);

Для запроса, фильтрующего по created_at и status (например, WHERE created_at > '2024-01-01' AND status = 'active'), полезен композитный индекс:

CREATE INDEX idx_users_created_status ON users (created_at, status);

Подводные камни

Индексы ускоряют чтение, но замедляют запись (INSERT, UPDATE, DELETE), так как при каждом изменении данных индекс нужно обновлять. Поэтому не стоит создавать индексы на каждый столбец — только на те, которые часто используются в условиях WHERE, JOIN или ORDER BY.

Также важно помнить, что индексы занимают дополнительное место на диске. Для больших таблиц это может быть существенно.

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

• Первичный индекс — всегда, для каждой таблицы.
• Уникальный индекс — для полей, которые должны быть уникальны, но не являются первичным ключом.
• Композитный индекс — для запросов с фильтрацией по нескольким столбцам.
• Полнотекстовый индекс — для поиска по текстовым полям.
• Пространственный индекс — для геоданных.
• Битмап-индекс — для хранилищ данных с низкой кардинальностью.

Коротко

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