Что такое кластеризованный и некластеризованный индекс в SQL Server? Когда какой использовать?

Кластеризованные и некластеризованные индексы в SQL Server

В SQL Server индексы — это структуры данных, ускоряющие поиск и извлечение данных из таблиц. Они реализованы как B-деревья и делятся на два основных типа.

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

Кластеризованный индекс определяет физический порядок хранения данных в таблице. Таблица может иметь только один кластеризованный индекс, поскольку данные могут быть физически отсортированы только одним способом.

Ключевые особенности:

• Данные в таблице хранятся в порядке ключа индекса
• Листовые узлы индекса содержат сами строки данных
• Создание кластеризованного индекса перестраивает таблицу
• Часто создается на первичном ключе (по умолчанию, если явно не указано иное)

-- Создание кластеризованного индекса
CREATE CLUSTERED INDEX IX_Orders_OrderDate 
ON Orders(OrderDate);

Некластеризованный индекс

Некластеризованный индекс — это отдельная структура данных, содержащая копию части данных таблицы с указателями на физическое расположение строк. Таблица может иметь до 999 некластеризованных индексов.

Ключевые особенности:

• Листовые узлы содержат ключи индекса + указатели на данные
• Не изменяет физический порядок хранения данных
• Требует дополнительного дискового пространства
• Может включать дополнительные столбцы (INCLUDE)

-- Создание некластеризованного индекса
CREATE NONCLUSTERED INDEX IX_Customers_LastName 
ON Customers(LastName)
INCLUDE (FirstName, Email);

Когда использовать каждый тип индекса

Кластеризованный индекс следует использовать:

1. Для столбцов с уникальными значениями — первичные ключи, уникальные идентификаторы
2. Для часто используемых диапазонных запросов — WHERE Date BETWEEN '2023-01-01' AND '2023-12-31'
3. Для столбцов, используемых в ORDER BY — данные уже физически отсортированы
4. Для таблиц, часто объединяемых по этому столбцу — улучшает производительность JOIN
5. Для монотонно возрастающих ключей — избегает фрагментации при вставках

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

-- Таблица заказов с кластеризованным индексом по дате
-- для быстрого поиска заказов за период
CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    OrderDate DATETIME NOT NULL,
    CustomerID INT NOT NULL,
    Amount DECIMAL(10,2)
);

CREATE CLUSTERED INDEX IX_Orders_OrderDate ON Orders(OrderDate);

Некластеризованный индекс следует использовать:

1. Для часто запрашиваемых столбцов в WHERE — ускоряет поиск отдельных записей
2. Для покрывающих индексов — когда индекс содержит все необходимые для запроса столбцы
3. Для столбцов с высоким уровнем уникальности — email, телефон, номер документа
4. Для таблиц с частыми операциями вставки/обновления — меньше накладных расходов
5. Для внешних ключей — ускоряет операции JOIN

Пример покрывающего индекса:

-- Индекс покрывает запрос полностью
CREATE NONCLUSTERED INDEX IX_Products_Category 
ON Products(CategoryID)
INCLUDE (ProductName, UnitPrice, UnitsInStock);

-- Зарос использует только индекс, без обращения к таблице
SELECT ProductName, UnitPrice 
FROM Products 
WHERE CategoryID = 5;

Практические рекомендации

Производительность и обслуживание:

• Кластеризованный индекс следует выбирать тщательно — его изменение требует полной перестройки таблицы
• Некластеризованные индексы улучшают чтение, но замедляют операции DML (INSERT, UPDATE, DELETE)
• Индексы с включенными столбцами уменьшают операции ключевого поиска
• Фильтрованные индексы эффективны для подмножеств данных

-- Фильтрованный индекс для активных записей
CREATE NONCLUSTERED INDEX IX_Active_Users 
ON Users(LastName, FirstName)
WHERE IsActive = 1;

Антипаттерны и предостережения:

1. Не создавайте кластеризованный индекс на часто изменяемых столбцах — это вызывает постоянную перестройку таблицы
2. Избегайте широких кластеризованных индексов — они увеличивают размер всех некластеризованных индексов
3. Не создавайте избыточные индексы — следите за дублированием функциональности
4. Регулярно обслуживайте индексы — используйте REORGANIZE и REBUILD для борьбы с фрагментацией

Заключение

Выбор между кластеризованным и некластеризованным индексом зависит от:

• Шаблонов доступа к данным — как часто и каким образом запрашиваются данные
• Соотношения операций чтения/записи — больше чтения или больше изменений
• Уникальности данных — требуется ли физическое упорядочивание
• Требований к производительности — какие запросы наиболее критичны

Оптимальная стратегия часто включает:

1. Один кластеризованный индекс на наиболее часто используемом для диапазонных запросов столбце
2. Несколько некластеризованных индексов для часто фильтруемых столбцов
3. Покрывающие индексы для критичных запросов
4. Регулярный мониторинг использования индексов через DMV (Dynamic Management Views)

Правильное проектирование индексов — это баланс между скоростью чтения, производительностью записи и использованием дискового пространства, основанный на конкретных требованиях вашего приложения и рабочих нагрузках.