Що таке clustered і non-clustered індекси?

Загальні питання~6 хв читання

Clustered index (кластерний індекс) - це структура B+ дерева, де листові вузли МІСТЯТЬ самі рядки даних, фізично впорядковані за ключем; non-clustered index (некластерний) - окреме B+ дерево з вказівниками на ці рядки.

Теорія

TL;DR

Аналогія: clustered index = телефонний довідник (дані фізично відсортовані); non-clustered = предметний покажчик у кінці книги (окремий список з посиланнями на сторінки)
Максимум один clustered index на таблицю; non-clustered можна до ~999 у SQL Server
Clustered виграє на range scan (без зайвого переходу); non-clustered додає bookmark lookup, якщо індекс не покриває всі потрібні колонки
SQL Server автоматично створює clustered index на PRIMARY KEY
Правило вибору: clustered на колонку, по якій найчастіше range-запити; non-clustered для решти фільтрів

Швидкий приклад

sql

-- SQL Server: clustered створюється автоматично на PRIMARY KEY
CREATE TABLE Orders (
    OrderID    int IDENTITY PRIMARY KEY,  -- тут clustered index
    CustomerID int,
    OrderDate  date
);

-- Range scan по clustered ключу: читає суміжні сторінки, одне проходження дерева
SELECT * FROM Orders WHERE OrderID BETWEEN 100 AND 200;

-- Non-clustered: окреме B-дерево по CustomerID
CREATE NONCLUSTERED INDEX IX_CustomerID ON Orders(CustomerID);

-- Спочатку шукає по індексу, потім іде до clustered за повним рядком
SELECT * FROM Orders WHERE CustomerID = 123;

Перший запит читає суцільний блок сторінок за одне проходження. Другий спочатку шукає в IX_CustomerID, потім робить ще один перехід до clustered index за повним рядком. Це і є bookmark lookup.

Ключова різниця

Clustered index зберігає самі рядки у листових вузлах B+ дерева, впорядкованих за ключем. Таблиця і індекс - одна й та сама структура. Оскільки фізичний порядок буває тільки один, на таблицю може бути лише один clustered index. Non-clustered будує окреме B+ дерево, чиї листові вузли містять тільки ключ і вказівник (clustered key або row ID на heap). Range scan по clustered ключу швидкий, бо сторінки йдуть підряд на диску. Non-clustered запит додає крок переходу за вказівником, якщо індекс не покриваючий.

Коли що використовувати

Range-запити по датах або послідовних ID (наприклад, WHERE OrderDate BETWEEN) → clustered, фізичний порядок робить послідовне читання дешевим
Фільтрація по другорядних колонках (наприклад, WHERE CustomerID = ?) → non-clustered, не треба переупорядковувати всю таблицю
Read-heavy таблиці для звітів → можна додати composite non-clustered індекси, вони прискорять читання
Write-heavy таблиці → обмежуй кількість non-clustered; кожен INSERT оновлює всі індекси
Покриваючі запити, де SELECT потребує тільки проіндексовані колонки → non-clustered з INCLUDE, без bookmark lookup

Таблиця порівняння

Аспект	Clustered index	Non-clustered index
Зберігання даних	Рядки фізично відсортовані за ключем; листок = дані	Окреме B-дерево; листок = ключ + вказівник
Ліміт на таблицю	1 (або 0 для heap)	~999 у SQL Server
Range scan	Найшвидший; сторінки послідовні	Повільніший без покриття; потрібен bookmark lookup
Вартість INSERT	Середня; можливі page splits при випадкових ключах	Вища; кожен запис оновлює всі індекси
Розмір	Немає накладних витрат (дані = індекс)	~10-20% додатково на кожен індекс
Для чого	Основна колонка сортування (OrderID, event_date)	Другорядні фільтри (CustomerID, status)

Як B+ дерево зберігає все це

Обидва типи індексів використовують B+ дерево. Різниця в тому, що знаходиться у листових вузлах. У clustered index листові вузли - це самі сторінки даних, впорядковані за ключем. Пошук проходить дерево до листа, потім сканує суміжні сторінки для range-запитів без зайвого I/O. У non-clustered index листові вузли містять ключ індексу плюс локатор рядка. Якщо таблиця має clustered index, цей локатор - значення clustered ключа. Движок переходить по ньому до clustered index у другому lookup. Якщо non-clustered включає всі потрібні колонки (покриваючий індекс, або covering index), движок взагалі не звертається до clustered.

Типові помилки

Помилка: clustered index на колонку з малою кількістю значень, наприклад gender

Лише два можливі значення означають, що половина таблиці концентрується на невеликій кількості сторінок. INSERT постійно борються за ті самі сторінки і викликають page splits при кожному записі.

sql

-- Неправильно: два значення = hotspot сторінки, постійні page splits
CREATE CLUSTERED INDEX IX_Gender ON users(gender);

-- Правильно: послідовний, унікальний, рівномірний розподіл
CREATE CLUSTERED INDEX IX_UserID ON users(user_id);

Помилка: GUID як первинний ключ без послідовного порядку

Випадкові GUID вставляються в довільні позиції B+ дерева і викликають page splits по всьому файлу. Фрагментація швидко досягає 80%+. Використовуй NEWSEQUENTIALID() у SQL Server або ULID в інших системах.

sql

-- Неправильно: випадковий порядок вставки, масова фрагментація
id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY

-- Правильно: послідовні GUID, вставки завжди в кінець дерева
id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY

Помилка: SELECT * через вузький non-clustered індекс на широкій таблиці

Non-clustered індекс по level виглядає швидким в EXPLAIN, поки запит не зачіпає 10 мільйонів рядків і кожен раз не тягне повний TEXT стовпець із clustered index.

sql

-- Повільно: шукає IX_level, потім 10M разів тягне повний рядок
SELECT * FROM logs WHERE level = 'ERROR';

-- Швидко: покриваючий індекс включає message, жодного звернення до таблиці
CREATE NONCLUSTERED INDEX IX_level_covering
    ON logs(level) INCLUDE (message);
SELECT level, message FROM logs WHERE level = 'ERROR';

Різниця між цими двома запитами на таблиці з 10M рядків - хвилини проти секунд. Це найпоширеніша пастка рівня senior у log analytics.

Помилка: 15+ non-clustered індексів на активній таблиці

Кожен non-clustered індекс оновлюється при кожному записі. На Reddit описували реальний кейс: після 12-го індексу TPS впав на 80%. Перевіряй sys.dm_db_index_usage_stats, знаходь індекси, які ніхто не читає, і видаляй їх.

sql

-- Знайти невикористовувані індекси у SQL Server
SELECT object_name(i.object_id) AS table_name,
       i.name AS index_name,
       s.user_seeks + s.user_scans + s.user_lookups AS reads,
       s.user_updates AS writes
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID()
ORDER BY reads ASC;

Де зустрічається на практиці

SQL Server / Microsoft Dynamics: clustered на OrderID; composite non-clustered на CustomerID, OrderDate для звітів
PostgreSQL / GitLab CI: clustered на job_id (через PK); composite non-clustered на status, created_at для черги задач
MySQL / InnoDB (WordPress): clustered на PK завжди (InnoDB це вимагає); non-clustered на post_title для пошуку
BigQuery / Google Analytics: кластеровані таблиці за event_date; окремі search indexes для другорядних фільтрів
Write-heavy системи: тримай кількість non-clustered на рівні 3-5; для аналітики розглядай columnstore замість B-tree

Питання на співбесіді

Q: Чому на таблицю може бути тільки один clustered index?
A: Clustered index - це і є фізичний порядок зберігання рядків. Не можна одночасно зберігати ті самі рядки у двох різних фізичних порядках. Другий clustered index вимагав би другої повної копії таблиці, що за суттю і є покриваючий non-clustered індекс.

Q: Що таке bookmark lookup (пошук за закладкою) і як його усунути?
A: Коли non-clustered пошук повертає локатор рядка, движок робить другий перехід до clustered index (або heap) щоб отримати повний рядок. Додай INCLUDE-колонки до non-clustered індексу, щоб покрити всі колонки з SELECT, і движок взагалі не торкатиметься clustered index.

Q: Як GUID як первинний ключ викликає фрагментацію?
A: Нові рядки з випадковими GUID вставляються в довільні позиції B+ дерева і викликають page splits по всьому файлу. Послідовні ключі типу INT IDENTITY завжди додаються до крайнього правого листового вузла, що тримає фрагментацію біля нуля.

Q: Як виявити і виправити фрагментацію індексів?
A: Запитай sys.dm_db_index_physical_stats і дивись на avg_fragmentation_in_percent. Більше 30% - REBUILD; від 10 до 30% - REORGANIZE. REBUILD створює нову структуру, REORGANIZE дефрагментує листові сторінки на місці без повного блокування.

Q: Senior-питання: є таблиця фактів на 1 мільярд рядків у star schema. Як спроєктувати індекси для join-ів по date і dimension ключах?
A: Clustered columnstore по колонці дати для аналітичних batch-сканів; non-clustered B-tree індекси по FK-колонках з INCLUDE ключових мір для точкових запитів. Компроміс: швидкість запису (columnstore буферизує вставки у delta store перед стисненням) проти латентності читання. Тести Brent Ozar показують 10-100x стиснення і швидкість сканування на таблицях фактів з columnstore.

Приклади

Базовий: range scan проти вторинного пошуку

sql

CREATE TABLE Orders (
    OrderID    int IDENTITY PRIMARY KEY,  -- clustered
    CustomerID int,
    OrderDate  date,
    Amount     decimal(10,2)
);

CREATE NONCLUSTERED INDEX IX_CustomerID ON Orders(CustomerID);

-- Clustered range: одне проходження дерева, 1000 послідовних листових сторінок
SELECT OrderID, Amount
FROM Orders
WHERE OrderID BETWEEN 1000 AND 2000;

-- Non-clustered seek + bookmark lookup: два проходження дерева
SELECT OrderID, Amount
FROM Orders
WHERE CustomerID = 456;

Перший запит проходить clustered B+ дерево один раз і читає суміжні сторінки. Другий проходить IX_CustomerID, збирає локатори рядків, потім іде до clustered дерева за кожним із них. На 1000 збігів - приблизно 2000 звернень замість 1000.

Середній: composite покриваючий індекс для dashboard-запиту

sql

-- Таблиця продажів e-commerce
CREATE TABLE sales (
    sale_id   BIGINT PRIMARY KEY,        -- clustered на sale_id
    user_id   BIGINT,
    sale_date TIMESTAMP,
    amount    DECIMAL(12,2)
);

-- Composite non-clustered покриває весь dashboard-запит
CREATE INDEX idx_user_date_amount
    ON sales(user_id, sale_date)
    INCLUDE (amount);

-- Dashboard: суми по користувачу за останні 30 днів
-- Використовує тільки idx_user_date_amount, clustered index не чіпає
SELECT user_id, SUM(amount)
FROM sales
WHERE user_id = 456
  AND sale_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id;

INCLUDE (amount) робить індекс покриваючим. Запит не виходить за межі сторінок non-clustered індексу. Без INCLUDE движок звертався б до clustered index за кожним рядком щоб отримати amount. На таблиці з 100M рядків різниця відчутна.

Senior: покриваючий індекс для усунення bookmark lookup

sql

-- Таблиця логів на 10M рядків
CREATE TABLE logs (
    id      BIGINT PRIMARY KEY,
    level   VARCHAR(10),
    message TEXT
);

-- Вузький індекс тільки по level
CREATE NONCLUSTERED INDEX IX_level ON logs(level);

-- Виглядає нормально в EXPLAIN, але повільно на практиці:
-- движок знаходить 2M рядків ERROR через IX_level,
-- потім 2M разів тягне TEXT стовпець із clustered index
SELECT level, message FROM logs WHERE level = 'ERROR';

-- Виправлення: включаємо message в індекс
CREATE NONCLUSTERED INDEX IX_level_covering
    ON logs(level)
    INCLUDE (message);

-- Тепер всі дані є в non-clustered індексі
-- Жодного bookmark lookup, жодного звернення до clustered
SELECT level, message FROM logs WHERE level = 'ERROR';

Я бачив цей паттерн у продакшені. Оригінальний запит виконувався 4 хвилини на таблиці з 10M рядків. Після додавання INCLUDE (message) стало 8 секунд. Індекс трохи виріс, але 10 мільйонів випадкових звернень до clustered index замінились одним послідовним скануванням non-clustered.

Коротка відповідь

Для співбесіди

Premium

Коротка відповідь допоможе вам впевнено відповідати на цю тему під час співбесіди.

Дочитали статтю?