Какие типы индексов знаешь в SQL?

Типы индексов в SQL (на примере SQL Server и общих принципов)

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

1. Классификация по структуре и организации

Кластеризованный индекс (Clustered Index)

• Переупорядочивает физическое хранение строк в таблице в соответствии с ключом индекса. Таблица может иметь только один кластеризованный индекс (поскольку данные физически могут быть упорядочены лишь одним способом).
• Ключевые особенности:
  • Данные хранятся в отсортированном порядке (B-дерево).
  • Быстрые операции WHERE, ORDER BY и GROUP BY по ключу индекса.
  • В SQL Server, если кластеризованный индекс не создан явно, таблица становится кучей (heap) с неупорядоченным хранением.
• Пример создания:

  CREATE CLUSTERED INDEX IX_EmployeeID ON Employees(EmployeeID);
  

Некластеризованный индекс (Non-Clustered Index)

• Не изменяет физический порядок данных, а создает отдельную структуру (также B-дерево), содержащую ключи индекса и указатели на строки данных.
• Одна таблица может иметь множество некластеризованных индексов (например, до 999 в SQL Server).
• Пример:

  CREATE NONCLUSTERED INDEX IX_LastName ON Employees(LastName);
  

2. Специализированные и составные индексы

Составной индекс (Composite Index)

• Создается по нескольким столбцам. Порядок столбцов важен: индекс эффективен для запросов с условиями, начинающимися с первого столбца.
• Пример:

  CREATE INDEX IX_LastName_FirstName ON Employees(LastName, FirstName);
  

Уникальный индекс (Unique Index)

• Гарантирует уникальность значений в индексируемых столбцах (запрещает дубликаты). Может быть как кластеризованным, так и некластеризованным.
• Часто используется для поддержки ограничений PRIMARY KEY или UNIQUE CONSTRAINT.
• Пример:

  CREATE UNIQUE INDEX UQ_Email ON Employees(Email);
  

Индекс с включенными столбцами (Covering Index / INCLUDE)

• Некластеризованный индекс, который включает дополнительные непключевые столбцы на уровне листьев B-дерева. Это позволяет выполнять запросы целиком из индекса без обращения к основной таблице (покрывающий запрос).
• Пример:

  CREATE INDEX IX_LastName_INC ON Employees(LastName) INCLUDE (FirstName, Email);
  

3. Индексы для специальных сценариев

Полнотекстовый индекс (Full-Text Index)

• Предназначен для быстрого поиска по текстовым данным (столбцы TEXT, VARCHAR(MAX)), поддерживает сложные запросы с морфологией, синонимами и ранжированием.
• Используется с операторами CONTAINS или FREETEXT.

Пространственный индекс (Spatial Index)

• Оптимизирован для работы с геопространственными данными (типы geometry, geography в SQL Server). Основан на R-деревьях или сеточных структурах.

Фильтрованный индекс (Filtered Index)

• Индекс с условием WHERE, который строится только для подмножества строк. Эффективен для сценариев, когда запросы часто фильтруют по определенному критерию.
• Пример (индекс только для активных сотрудников):

  CREATE INDEX IX_ActiveEmployees ON Employees(DepartmentID) WHERE IsActive = 1;
  

Колоночный индекс (Columnstore Index)

• Хранит данные по столбцам, а не по строкам. Оптимизирован для аналитических запросов (OLAP) с агрегациями и сканированием больших объемов данных.
• Особенности:
  • Сильно увеличивает скорость SUM, AVG, GROUP BY на больших таблицах.
  • Поддерживает сжатие данных.
• Пример:

  CREATE COLUMNSTORE INDEX CS_Orders ON OrderDetails(ProductID, Quantity, Price);
  

4. Другие типы (в зависимости от СУБД)

• Хэш-индекс (используется в MySQL для хранилища Memory): для точечных запросов, но не поддерживает диапазоны.
• Индекс по выражению (Function-Based Index) (например, в Oracle, PostgreSQL): индекс, ключ которого основан на выражении или функции от столбцов.

  CREATE INDEX idx_uppername ON Employees(UPPER(LastName));
  

Ключевые принципы выбора индексов

• Индексы ускоряют чтение, но замедляют запись (DML-операции).
• Покрывающие индексы (Covering Index) могут исключить обращение к таблице.
• Мониторинг использования индексов: в SQL Server — системные представления sys.dm_db_index_usage_stats, sys.dm_db_index_physical_stats.
• Проблемы: слишком много индексов ухудшают производительность записи; фрагментация индексов требует обслуживания (REBUILD/REORGANIZE).

Эффективное проектирование индексов требует анализа паттернов запросов, селективности данных и баланса между производительностью чтения и записи.