Расскажи про кластерные индексы

Кластерные индексы

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

Основные характеристики

Физическое упорядочивание данных:

• Кластерный индекс не просто создаёт индексную структуру, он физически переупорядочивает данные в таблице
• Листовые уровни кластерного индекса содержат сами данные строк (не указатели на них)
• Это делает кластерные индексы исключительно быстрыми для поиска по диапазонам

Первичный ключ:

• По умолчанию первичный ключ создаёт кластерный индекс
• На таблицу может быть несколько уникальных ключей, но только один из них будет кластеризирован

Некластерные индексы

Некластерные индексы содержат указатели (закладки) на строки кластеризированной таблицы. Их может быть несколько на таблицу (обычно до 999 в SQL Server).

-- Создание кластерного индекса на колонке user_id
CREATE CLUSTERED INDEX idx_user_id 
ON orders(user_id ASC);

-- Создание некластерного индекса на колонке order_date
CREATE NONCLUSTERED INDEX idx_order_date 
ON orders(order_date DESC);

Производительность

Преимущества:

• Очень быстрый поиск по диапазонам значений
• Эффективен для запросов с сортировкой по этой колонке
• Экономит дисковое пространство (данные не дублируются)

Недостатки:

• Дорогостоящее обновление (требует переупорядочивания данных)
• Влияет на все остальные индексы (при вставке/удалении нужно пересчитывать закладки в некластерных индексах)
• Нельзя изменить порядок кластеризации без полного пересоздания таблицы

Выбор колонки для кластеризации

Для эффективности выбирай колонку, которая:

1. Часто используется в запросах с диапазонными условиями (WHERE column BETWEEN ... AND ...)
2. Стабильна — редко изменяется
3. Часто используется для сортировки (ORDER BY)
4. Обеспечивает хорошее распределение — не содержит много NULL значений и дублей

Пример выбора стратегии

-- Таблица заказов: часто ищем заказы по диапазону дат и пользователю
CREATE TABLE orders (
    order_id INT PRIMARY KEY CLUSTERED,
    user_id INT,
    order_date DATETIME,
    amount DECIMAL(10, 2)
);

-- Часто выполняемый запрос
SELECT * FROM orders 
WHERE order_date BETWEEN 2024-01-01 AND 2024-12-31 
ORDER BY order_date;

-- Лучше сделать кластеризацию по order_date
CREATE CLUSTERED INDEX idx_order_date 
ON orders(order_date ASC);

-- А user_id как некластерный индекс
CREATE NONCLUSTERED INDEX idx_user_id 
ON orders(user_id);

В контексте Data Engineering

При работе с большими таблицами (миллионы строк) правильный выбор кластеризации критичен:

• Для фактических таблиц (fact tables) часто кластеризируют по дате
• Для справочников (dimensions) — по ID или коду
• В хранилищах данных (Data Warehouse) часто используют кластеризацию по колонкам, которые используют для партиционирования

В Apache Spark и других распределённых системах концепция классических кластерных индексов не применима, но есть аналоги через сортировку и партиционирование данных.