Что такое кластерный индекс в БД?

# Что такое кластерный индекс в БД?

Кластерный индекс (Clustered Index) — это индекс, который определяет физический порядок хранения строк в таблице базы данных. Это одна из самых важных концепций в проектировании БД для производительности.

Основное понимание

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

Ключевые характеристики кластерного индекса

1. Один на таблицу

В одной таблице может быть только ОДИН кластерный индекс, потому что он определяет физический порядок строк, а физический порядок может быть только одним.

CREATE TABLE users (
    user_id INT PRIMARY KEY,  -- PRIMARY KEY создаёт кластерный индекс по умолчанию
    name VARCHAR(100),
    email VARCHAR(100)
) ENGINE=InnoDB;

-- Обычно PRIMARY KEY = кластерный индекс
-- Но можно явно указать другой столбец

2. Физический порядок

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

CREATE TABLE orders (
    order_id INT PRIMARY KEY,  -- Кластерный индекс
    customer_id INT,
    order_date DATE,
    amount DECIMAL
) ENGINE=InnoDB;

-- На диске строки хранятся в порядке order_id:
-- order_id: 1, customer_id: 100, order_date: 2024-01-01
-- order_id: 2, customer_id: 101, order_date: 2024-01-02
-- order_id: 3, customer_id: 100, order_date: 2024-01-03
-- ...

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

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

-- PRIMARY KEY автоматически создаёт кластерный индекс
CREATE TABLE users (
    user_id INT PRIMARY KEY,           -- Кластерный индекс
    name VARCHAR(100),
    email VARCHAR(100),
    created_at DATETIME
);

-- Поиск по PRIMARY KEY очень быстро, потому что это кластерный индекс
SELECT * FROM users WHERE user_id = 5;  -- Быстро! O(log n)

Структура на диске:

Б-дерево листового уровня содержит ВСЕ данные строки:

Лист 1: user_id=1-10 [name, email, created_at - ПОЛНЫЕ ДАННЫЕ]
Лист 2: user_id=11-20 [name, email, created_at - ПОЛНЫЕ ДАННЫЕ]
Лист 3: user_id=21-30 [name, email, created_at - ПОЛНЫЕ ДАННЫЕ]

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

-- Создаём отдельный индекс по email
CREATE INDEX idx_email ON users(email);

-- Поиск по email
SELECT * FROM users WHERE email = 'john@example.com';  -- Нужна extra работа

Структура на диске:

Б-дерево индекса содержит только email + указатель на кластерный индекс:

Лист индекса: email='alex@ex.com' -> указатель на кластерный ключ (user_id=5)
              email='bob@ex.com' -> указатель на кластерный ключ (user_id=3)

Затем база должна использовать этот указатель,
чтобы найти полные данные в кластерном индексе

Практический пример

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,        -- Кластерный индекс (автоматический)
    name VARCHAR(100),
    email VARCHAR(100),
    country VARCHAR(50),
    created_at DATETIME
) ENGINE=InnoDB;

-- Добавим некластерный индекс по email
CREATE INDEX idx_email ON customers(email);
CREATE INDEX idx_country ON customers(country);

-- Запросы и их производительность:

-- 1. По кластерному индексу (PRIMARY KEY) - ОЧЕНЬ БЫСТРО
SELECT * FROM customers WHERE customer_id = 123;
-- Прямо идёт в нужное место в Б-дереве

-- 2. По некластерному индексу - медленнее
SELECT * FROM customers WHERE email = 'john@example.com';
-- Сначала ищет в индексе email,
-- затем использует найденный customer_id,
-- чтобы найти полные данные (KEY LOOKUP)

-- 3. По другому столбцу без индекса - ПОЛНОЕ СКАНИРОВАНИЕ
SELECT * FROM customers WHERE name LIKE 'John%';
-- Сканирует всю таблицу! Очень медленно

Операция Key Lookup

Когда вы используете некластерный индекс и нужны данные, которых нет в индексе, база выполняет "Key Lookup":

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,            -- Кластерный индекс
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL
);

CREATE INDEX idx_department ON employees(department);

-- Этот запрос выполнит Key Lookup
SELECT emp_id, salary FROM employees WHERE department = 'IT';

Этапы:
1. Поиск в индексе idx_department по 'IT'
2. Индекс содержит: department='IT' -> emp_id=1, emp_id=2, emp_id=3
3. Для каждого emp_id выполняет Key Lookup в кластерном индексе
4. Получает полные данные (name, salary)

Включённые столбцы (Included Columns)

Чтобы избежать Key Lookup, можно добавить столбцы в некластерный индекс:

-- Без INCLUDE - нужен Key Lookup
CREATE INDEX idx_dept ON employees(department);

-- С INCLUDE - НЕ нужен Key Lookup
CREATE INDEX idx_dept_covered ON employees(department)
INCLUDE (salary);

-- Теперь этот запрос не требует Key Lookup:
SELECT emp_id, salary FROM employees WHERE department = 'IT';
-- Всё нужное уже в индексе!

Выбор кластерного индекса

Хороший выбор для кластерного индекса:

-- 1. Столбец, часто используется в WHERE
CREATE TABLE orders (
    order_id INT PRIMARY KEY,      -- Хороший выбор
    customer_id INT,
    order_date DATE,
    amount DECIMAL
);

-- 2. Столбец с узким диапазоном значений
CREATE TABLE events (
    event_id INT PRIMARY KEY,      -- Хороший выбор
    event_time DATETIME,
    user_id INT
);

-- 3. Столбец, который часто используется в JOIN
CREATE TABLE products (
    product_id INT PRIMARY KEY,    -- Хороший выбор
    category_id INT,
    name VARCHAR(100)
);

Плохой выбор для кластерного индекса:

-- 1. GUID (слишком большой, сложный)
CREATE TABLE users (
    user_guid UNIQUEIDENTIFIER PRIMARY KEY,  -- Плохо!
    email VARCHAR(100)
);

-- 2. Строковый столбец (медленный поиск)
CREATE TABLE users (
    username VARCHAR(100) PRIMARY KEY,  -- Плохо!
    email VARCHAR(100)
);

-- Лучше:
CREATE TABLE users (
    user_id INT PRIMARY KEY,       -- Хороший кластерный
    username VARCHAR(100) UNIQUE,  -- Некластерный индекс
    email VARCHAR(100)
);

В Java (Hibernate/JPA)

// На уровне JPA мы не управляем индексами напрямую,
// но хорошо понимаем их при проектировании запросов

@Entity
@Table(name = "orders")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long orderId;  // PRIMARY KEY = Кластерный индекс
    
    @Column(name = "customer_id")
    private Long customerId;
    
    @Column(name = "order_date")
    private LocalDateTime orderDate;
}

// Хороший запрос - использует кластерный индекс
Order order = repository.findById(123L);  // По PRIMARY KEY - быстро!

// Медленный запрос - полное сканирование или некластерный индекс
List<Order> orders = repository.findByCustomerId(456L);
// Может быть медленно, если нет индекса по customer_id

// Решение - добавляем индекс
@Entity
@Table(name = "orders", indexes = {
    @Index(name = "idx_customer_id", columnList = "customer_id")
})
public class Order {
    // ...
}

Проблемы с кластерным индексом

1. Фрагментация

Когда кластерный индекс основан на последовательно растущем значении (user_id), новые данные вставляются в конец. Но если вставляются случайные значения или есть удаления, возникает фрагментация.

-- Плохо: GUID как PRIMARY KEY (случайный порядок)
CREATE TABLE logs (
    log_id UNIQUEIDENTIFIER PRIMARY KEY,  -- Вызывает фрагментацию
    timestamp DATETIME,
    message TEXT
);

-- Хорошо: Identity (последовательные значения)
CREATE TABLE logs (
    log_id INT PRIMARY KEY IDENTITY(1,1),  -- Хороший порядок
    timestamp DATETIME,
    message TEXT
);

2. Пересортировка при обновлении

Если обновить значение кластерного индекса, база должна переместить всю строку, что дорого.

-- Избегай обновления PRIMARY KEY!
UPDATE orders SET order_id = 999 WHERE order_id = 1;  -- ДОРОГО!

-- Лучше используй неменяющийся индекс
UPDATE orders SET status = 'cancelled' WHERE order_id = 1;  -- OK

Итоговая таблица

Практические советы

1. Выбирай кластерный индекс мудро — по одному столбцу, который часто используется в поиске
2. Предпочитай INT для PRIMARY KEY — быстрее чем GUID или строки
3. Не обновляй кластерный индекс — это дорогая операция
4. Используй некластерные индексы для других часто используемых столбцов в WHERE
5. Мониторь фрагментацию — периодически дефрагментируй индексы

Понимание кластерного индекса критично для оптимизации производительности БД и написания правильных запросов.