Какие индексы есть в SQL

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

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

Классификация индексов

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

1. По структуре и алгоритму

• B-дерево (B-tree) — самый распространённый тип. Поддерживает быстрый поиск, диапазонные запросы (BETWEEN, >, <) и сортировку. Эффективен для высокоселективных столбцов.

  -- Создание индекса B-tree (по умолчанию в большинстве СУБД)
  CREATE INDEX idx_user_email ON users(email);
  

• Хеш-индекс (Hash) — использует хеш-таблицу, идеален для операций точного совпадения (=). Не поддерживает диапазонные запросы и сортировку. Часто используется в оперативной памяти.

  -- Создание хеш-индекса (например, в PostgreSQL)
  CREATE INDEX idx_user_id_hash ON users USING HASH(id);
  

• Bitmap-индекс (Bitmap) — эффективен для столбцов с небольшим количеством уникальных значений (низкая кардинальность), например, gender или status. Широко используется в OLAP-системах (например, Oracle).

• R-дерево (R-tree) — специализированный индекс для пространственных данных (координаты, геометрия). Позволяет эффективно выполнять запросы на расстояние или вхождение в область.

  -- Индекс для геоданных в PostgreSQL (с расширением PostGIS)
  CREATE INDEX idx_location_geom ON locations USING GIST(geom);
  

• GiST (Generalized Search Tree) и SP-GiST — обобщённые индексы в PostgreSQL для сложных типов данных (полнотекстовый поиск, массивы, диапазоны).

• GIN (Generalized Inverted Index) — оптимизирован для составных значений (массивы, JSON, полнотекстовый поиск). Эффективен, когда нужно проверить наличие элемента.

  -- GIN-индекс для поиска по массиву тегов
  CREATE INDEX idx_article_tags ON articles USING GIN(tags);
  

2. По уникальности

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

  CREATE UNIQUE INDEX uq_user_phone ON users(phone_number);
  

• Неуникальный индекс (Non-unique Index) — допускает дублирование значений. Создаётся по умолчанию.

3. По количеству столбцов

• Одно-столбцовый индекс (Single-column) — создаётся на одном столбце таблицы.
• Составной индекс (Composite/Multi-column) — включает несколько столбцов. Порядок столбцов критически важен: индекс будет работать для запросов, которые используют префикс этого списка (левый принцип).

  -- Составной индекс по фамилии и имени
  CREATE INDEX idx_name_surname ON employees(surname, first_name);
  -- Этот индекс будет использоваться для WHERE surname = 'Ivanov'
  -- и для WHERE surname = 'Ivanov' AND first_name = 'Alexey'
  -- но, скорее всего, НЕ будет использоваться для WHERE first_name = 'Alexey'
  

4. Специальные и функциональные индексы

• Частичный индекс (Partial/Filtered) — индексирует только подмножество строк, удовлетворяющее условию WHERE. Экономит место и повышает производительность для часто запрашиваемых данных.

  -- Индекс только для активных пользователей
  CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
  

• Индекс по выражению (Functional/Expression-based) — создаётся не на столбец, а на результат вычисления выражения или функции.

  -- Индекс для поиска по нижнему регистру email
  CREATE INDEX idx_user_lower_email ON users(LOWER(email));
  

• Покрывающий индекс (Covering Index) — ключевая концепция оптимизации. Такой индекс содержит все столбцы, необходимые для выполнения запроса (как в ключе, так и во включаемых столбцах). Это позволяет СУБД получить данные непосредственно из индекса, избегая дорогостоящего обращения к самой таблице (операция "index-only scan").

  -- Включаем столбец 'name' в индекс для покрывающего запроса
  CREATE INDEX idx_covering ON orders(user_id, order_date) INCLUDE (total_amount);
  -- Зарос SELECT user_id, order_date, total_amount FROM orders WHERE user_id = 100
  -- может быть выполнен используя только индекс.
  

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

• Некластеризованный индекс (Non-clustered Index) — создаёт отдельную структуру данных, которая содержит ключи индекса и указатели на фактические строки таблицы. На таблицу может быть множество таких индексов.

Критерии выбора и лучшие практики

• Индексируйте столбцы, часто используемые в условиях WHERE, JOIN, ORDER BY и GROUP BY.
• Избегайте избыточных индексов. Два индекса (A, B) и (A) часто избыточны, так как первый может обслуживать запросы ко второму.
• Мониторьте и анализируйте использование индексов через EXPLAIN (или EXPLAIN ANALYZE).

  EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
  

• Учитывайте селективность. Высокоселективные столбцы (с большим % уникальных значений) — лучшие кандидаты для индексов B-tree.
• Составные индексы проектируйте, исходя из паттернов запросов, соблюдая порядок столбцов.

Правильное проектирование индексов — это баланс между скоростью чтения и стоимостью записи/обслуживания. Оно требует глубокого понимания данных, рабочих нагрузок (OLTP vs OLAP) и возможностей конкретной СУБД.