Индексы в базах данных — это структуры данных, используемые для ускорения поиска и выборки строк из таблиц. Они функционируют как указатели, позволяя СУБД быстрее находить нужные данные без необходимости полного сканирования таблицы. В зависимости от СУБД (например, PostgreSQL, MySQL, Oracle, SQL Server), типы индексов могут отличаться по реализации и возможностям. Ниже представлены основные и специальные типы индексов, которые применяются в современных реляционных СУБД.

🔹 1. B-Tree индекс (Balanced Tree)

Это наиболее распространённый тип индекса. Используется по умолчанию в большинстве СУБД (например, PostgreSQL и MySQL/InnoDB).

Особенности:

• Эффективен для поиска, сортировки, диапазонных запросов (BETWEEN, >, <, >=, <=)

• Поддерживает равенство (=) и сортировку (ORDER BY)

• Автоматически сбалансированное дерево: глубина минимальна, операции поиска и вставки выполняются за O(log n)

Пример использования:

CREATE INDEX idx_name ON users(name);

🔹 2. Hash индекс

Индекс на основе хэш-функции, применяемой к значениям индексируемого поля.

Особенности:

• Быстрый поиск по точному совпадению (WHERE key = value)

• Не подходит для диапазонных запросов

• В PostgreSQL используется только с =, не работает с ORDER BY, LIKE, BETWEEN

• В MySQL (только движок MEMORY) ограниченное применение

Пример:

CREATE INDEX idx_email_hash ON users USING HASH (email);

🔹 3. GIN (Generalized Inverted Index)

Особенно эффективен для массивов, JSONB, полнотекстового поиска, tsvector.

Особенности:

• Каждый элемент массива или документа индексируется отдельно

• Подходит для @>, ?, @@, jsonb_contains, tsquery и т. д.

• Используется в PostgreSQL

Пример:

CREATE INDEX idx_tags_gin ON articles USING GIN (tags);

🔹 4. GiST (Generalized Search Tree)

Подходит для индексации геометрических, текстовых и нестандартных типов данных (например, R-деревья, интервалы).

Особенности:

• Позволяет реализовывать индексацию для пользовательских типов

• Используется в PostgreSQL для географических расширений (PostGIS)

• Менее эффективен по скорости, чем GIN, но более универсален

Пример:

CREATE INDEX idx_location ON places USING GIST (coordinates);

🔹 5. SP-GiST (Space-partitioned GiST)

Оптимизирован для непересекающихся пространственных данных, особенно полезен для работы с большими объемами геоданных.

Особенности:

• Эффективен для деревьев вроде k-d trees, **quad-trees
  **
• Используется для ускорения поиска в многомерных данных

🔹 6. BRIN (Block Range INdexes)

Индексы по диапазону блоков данных. Используются при хранении больших объемов данных, отсортированных по какому-то признаку (например, время или ID).

Особенности:

• Хранит минимальное и максимальное значение в блоке

• Очень компактен, но менее точен

• Особенно полезен для временных и логов

Пример:

CREATE INDEX idx_logs_date ON logs USING BRIN (created_at);

🔹 7. Fulltext индекс

Используется для полнотекстового поиска по текстовым полям в MySQL и других СУБД.

Особенности:

• Поддерживает поисковые операторы: MATCH ... AGAINST в MySQL

• Работает с текстами, учитывает стоп-слова, морфологию, **релевантность
  **

• В PostgreSQL для этого используется tsvector + GIN

Пример (MySQL):

CREATE FULLTEXT INDEX idx_content ON articles(content);

🔹 8. Composite (многоколонковый) индекс

Индекс по нескольким колонкам одновременно. Полезен, когда запрос использует несколько полей в WHERE или ORDER BY.

Особенности:

• Важен порядок колонок при создании

• Используется, если запрос использует начальные колонки в фильтрах

• Например, если индекс (A, B, C), то он используется при фильтрах по A, A+B, или A+B+C

Пример:

CREATE INDEX idx_user_name_email ON users(last_name, first_name, email);

🔹 9. Уникальные индексы (UNIQUE)

Обеспечивают уникальность значений в одном или нескольких полях.

Особенности:

• Автоматически создаются при объявлении PRIMARY KEY или UNIQUE

• Проверяют дублирование при вставке или обновлении

Пример:

CREATE UNIQUE INDEX idx_unique_email ON users(email);

🔹 10. Partial (фильтрованные) индексы

Индексируется только подмножество строк, соответствующее определенному условию.

Особенности:

• Экономит пространство

• Ускоряет запросы, затрагивающие только подмножество данных

Пример:

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

🔹 11. Function-based индекс (вычисляемый)

Индекс на основе значения выражения или функции.

Особенности:

• Поддерживается PostgreSQL и Oracle

• Полезен для ускорения поиска по вычисляемым значениям

Пример:

CREATE INDEX idx_lower_name ON users (lower(name));

🔹 12. Clustered и Non-Clustered индексы (SQL Server, MySQL/InnoDB)

• Clustered Index — определяет физический порядок строк в таблице (в InnoDB primary key всегда кластерный).

• Non-Clustered Index — отдельная структура, содержащая ключ и ссылку на строку.

Особенности:

• В таблице может быть только один кластерный индекс

• Некластерных может быть много

Дополнительные понятия:

• Covering Index — индекс, содержащий все поля, необходимые для запроса.

• Invisible Index (MySQL 8+) — индекс, который временно исключён из плана выполнения запроса, но не удалён.

• Expression Index — разновидность function-based индекса в некоторых СУБД.

Выбор типа индекса зависит от характера данных, запросов, объема таблицы, требований к скорости чтения/записи, наличия ограничений по уникальности и специфики СУБД.