Запропонувати правку

Покращити цю статтю

Допрацюйте відповідь до «Що таке clustered і non-clustered індекси?». Ваші зміни проходять модерацію перед публікацією.

Потрібне підтвердження

Контент

Що ви змінюєте

Заголовок (UA)

Коротка відповідь (UA)

Показується над повною відповіддю для швидкого нагадування.

Відповідь (UA)

**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.

Markdown · перетягуйте зображення · ⌘B / ⌘I шорткати1662 слів

Для рев’юера

Примітка для модератора (необов’язково)

Бачить лише модератор. Прискорює рев’ю.