Индексы и производительность — ClickHouse: от схемы

Индексы и производительность в ClickHouse

Первичные, вторичные индексы, проекции и оптимизация чтения данных

#Результат урока

Вы сможете связать ключ сортировки с паттернами запросов, доказать пропуск гранул через EXPLAIN indexes = 1 и оценить цену проекции или data-skipping индекса.

#Архитектура индексации в ClickHouse

ClickHouse использует разреженные индексы (sparse indexes), которые отличаются от традиционных B-деревьев:

Характеристика	B-дерево (PostgreSQL)	Разреженный индекс (ClickHouse)
Плотность	Плотный (каждая строка)	Разреженный (каждая N-я строка)
Назначение	Быстрый поиск строк	Пропуск нерелевантных частей данных
Размер	Зависит от индекса и данных	Мал, потому что хранит метки гранул
Обновление	Поддерживается механизмом индекса	Создаётся вместе с каждой частью данных

Принцип работы:

Данные в партиции (отсортированы):
Гранула 0: | 1, 2, 3, ..., 8192 |      ← 8192 строки
Гранула 1: | 8193, 8194, ..., 16384 |
Гранула 2: | 16385, ..., 24576 |
...

Первичный индекс (разреженный):
| (key=1, mark=0) | (key=8193, mark=1) | (key=16385, mark=2) |

Запрос: WHERE key BETWEEN 5000 AND 10000
→ Индекс находит гранулы 0 и 1
→ Читаются только эти 2 гранулы (~16K строк вместо всех)

#Первичный индекс (Primary Key)

#Создание

CREATE TABLE events
(
    event_time DateTime,
    user_id    UInt64,
    event_type String,
    value      Decimal(10, 2)
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id)           -- Ключ сортировки
PRIMARY KEY (event_time, user_id);       -- Первичный индекс

Важно:

PRIMARY KEY по умолчанию совпадает с ORDER BY
Можно указать другой PRIMARY KEY, но он должен быть префиксом ORDER BY
Первичный индекс не гарантирует уникальность

#Правила выбора ORDER BY / PRIMARY KEY

Правило префикса:

ORDER BY (A, B, C)  -- Хороший порядок

-- Запросы будут быстрыми:
WHERE A = ?                    -- индекс используется
WHERE A = ? AND B = ?          -- индекс используется
WHERE A = ? AND B = ? AND C = ?-- индекс используется

-- Пропуск начальных колонок обычно снижает эффективность:
WHERE B = ?                    -- индекс может помочь слабее
WHERE C = ?                    -- часто читается больше гранул
WHERE B = ? AND C = ?          -- результат зависит от распределения

Рекомендации:

Первыми указывайте колонки для точной фильтрации (=, IN)
Затем — для диапазонов (>, <, BETWEEN)
Избегайте функций в начале ключа: ORDER BY (toYYYYMM(event_time), ...)

#Пример: оптимальный ORDER BY

-- Таблица событий
CREATE TABLE page_views
(
    event_date  Date,
    user_id     UInt64,
    session_id  UInt64,
    page_url    String,
    country     String
)
ENGINE = MergeTree()
-- Типичные запросы:
-- 1. WHERE event_date = ? AND user_id = ?
-- 2. WHERE event_date = ? (агрегация по дате)
ORDER BY (event_date, user_id, session_id);

#Вторичные индексы (Data Skipping Indexes)

Вторичные индексы в ClickHouse называются индексами пропуска данных (data skipping indexes).

#Типы вторичных индексов

Тип	Описание	Когда использовать
minmax	Мин/макс значения	Для диапазонов, когда значения коррелируют с порядком данных
set	Множество значений в блоке	Для локально низкой кардинальности и равенства/IN
ngrambf	N-граммы для строк	Для LIKE '%pattern%' поиска
tokenbf	Токены для строк	Для полнотекстового поиска

#Синтаксис

CREATE TABLE products
(
    id          UInt64,
    name        String,
    price       Decimal(10, 2),
    category_id UInt32,
    description String,
    
    -- Вторичные индексы
    INDEX idx_price price TYPE minmax GRANULARITY 4,
    INDEX idx_category category_id TYPE set(100) GRANULARITY 4,
    INDEX idx_name name TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 4
)
ENGINE = MergeTree()
ORDER BY id;

Параметры:

GRANULARITY — к скольким гранулам применяется индекс (по умолчанию 4)
Для set — максимальное количество уникальных значений
Для ngrambf — размер n-граммы, размер bloom фильтра, количество хэшей

#minmax индекс

INDEX idx_price price TYPE minmax GRANULARITY 4

Как работает:

Хранит мин/макс значение для каждой гранулы
Пропускает гранулы, где price вне диапазона запроса

-- Запрос
SELECT * FROM products WHERE price BETWEEN 100 AND 200;

-- Индекс проверяет:
-- Гранула 0: min=10, max=50   → Пропустить (50 < 100)
-- Гранула 1: min=80, max=150  → Читать (пересечение)
-- Гранула 2: min=180, max=300 → Читать (пересечение)
-- Гранула 3: min=350, max=500 → Пропустить (350 > 200)

#set индекс

INDEX idx_category category_id TYPE set(100) GRANULARITY 4

Как работает:

Хранит до 100 уникальных значений для каждой гранулы
Пропускает гранулы, где нет нужных значений

-- Запрос
SELECT * FROM products WHERE category_id IN (1, 5, 10);

-- Индекс проверяет:
-- Гранула 0: {1, 2, 3} → Читать (есть 1)
-- Гранула 1: {4, 6, 8} → Пропустить (нет 1, 5, 10)
-- Гранула 2: {5, 7, 9} → Читать (есть 5)

#ngrambf индекс (для LIKE)

INDEX idx_name name TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 4

Параметры ngrambf_v1(N, size, hash, seed):

N — размер н-граммы (3 = триграммы)
size — размер bloom фильтра в байтах
hash — количество хэш-функций
seed — сид для хэширования

-- Запрос
SELECT * FROM products WHERE name LIKE '%phone%';

-- Bloom фильтр проверяет наличие триграмм 'pho', 'hon', 'one'
-- Может давать false positives, но не false negatives

#Добавление индекса в существующую таблицу

-- Добавление индекса
ALTER TABLE products 
ADD INDEX idx_price price TYPE minmax GRANULARITY 4;

-- Построение индекса для существующих данных
ALTER TABLE products MATERIALIZE INDEX idx_price;

-- Удаление индекса
ALTER TABLE products DROP INDEX idx_price;

Важно: Индекс строится только для новых данных. Для существующих нужно выполнить MATERIALIZE INDEX.

#Проекции (Projections)

Проекция — это дополнительная отсортированная копия данных с другой структурой.

#Создание проекции

CREATE TABLE events
(
    event_time DateTime,
    user_id    UInt64,
    event_type String,
    value      Decimal(10, 2)
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

-- Добавление проекции для агрегации по user_id
ALTER TABLE events ADD PROJECTION user_stats (
    SELECT 
        user_id,
        count() AS event_count,
        sum(value) AS total_value
    GROUP BY user_id
    ORDER BY user_id
);

-- Построение проекции для существующих данных
ALTER TABLE events MATERIALIZE PROJECTION user_stats;

#Как ClickHouse использует проекции

-- Исходный запрос
SELECT 
    user_id,
    count() AS event_count
FROM events
GROUP BY user_id;

-- ClickHouse автоматически использует проекцию user_stats
-- вместо сканирования полной таблицы

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

Автоматический выбор подходящей проекции планировщиком
Потенциальное уменьшение чтения и вычислений, которое нужно измерить
Прозрачно для приложения

#Типы проекций

1. Проекция с другой сортировкой:

ALTER TABLE events ADD PROJECTION by_user (
    SELECT * ORDER BY user_id
);

2. Проекция с агрегацией:

ALTER TABLE events ADD PROJECTION daily_stats (
    SELECT 
        toDate(event_time) AS date,
        event_type,
        count() AS events
    GROUP BY date, event_type
    ORDER BY date
);

3. Проекция с фильтрацией:

ALTER TABLE events ADD PROJECTION mobile_only (
    SELECT * WHERE is_mobile = 1 ORDER BY event_time
);

#Управление проекциями

-- Просмотр проекций
SELECT name FROM system.projections WHERE table = 'events';

-- Удаление проекции
ALTER TABLE events DROP PROJECTION user_stats;

-- Принудительное использование проекции
SELECT 
    user_id,
    count()
FROM events
SETTINGS allow_experimental_projection_optimization = 1
GROUP BY user_id;

#Сравнение: индексы vs проекции

Характеристика	Вторичный индекс	Проекция
Назначение	Пропуск гранул	Альтернативное представление данных
Размер	Зависит от типа и гранулярности	Значительный: дополнительные данные
Эффект	Только при исключении гранул	Только если план выбрал проекцию
Для агрегаций	Нет	Да
Для фильтрации	Да	Да
Прозрачность	Автоматически	Автоматически

#Оптимизация чтения: практические приёмы

#1. Проверьте автоматический PREWHERE

-- Анализатор может сам перенести подходящий предикат в PREWHERE
SELECT * FROM events 
WHERE country = 'RU' AND value > 100;

-- PREWHERE фильтрует по одной колонке до чтения остальных
SELECT * FROM events 
PREWHERE country = 'RU' 
WHERE value > 100;

Когда использовать:

Одна колонка селективнее других
Колонки имеют разный размер (сначала фильтровать по маленьким)
План не выполнил нужный перенос автоматически

#2. Проверяйте преобразование функций в WHERE

-- Эффект зависит от функции и версии анализатора
SELECT * FROM events 
WHERE toYYYYMM(event_time) = 202603;

-- Хорошо: диапазонное условие
SELECT * FROM events 
WHERE event_time >= '2026-03-01' 
  AND event_time < '2026-04-01';

#3. Используйте LIMIT для тестирования

-- Быстрый просмотр данных
SELECT * FROM events LIMIT 10;

-- Проверка селективности условия
SELECT count() FROM events WHERE condition;

#4. Избегайте SELECT *

-- Плохо: читает все колонки
SELECT * FROM events WHERE user_id = 123;

-- Хорошо: только нужные колонки
SELECT event_time, event_type, value 
FROM events 
WHERE user_id = 123;

#5. Оптимизация JOIN

-- JOIN с предварительной фильтрацией
SELECT 
    u.name,
    sum(o.amount) AS total
FROM 
    (SELECT * FROM users WHERE country = 'RU') AS u
    LEFT JOIN 
    (SELECT * FROM orders WHERE created_at >= '2026-01-01') AS o
    ON u.id = o.user_id
GROUP BY u.name;

#Мониторинг использования индексов

#system.query_log

SELECT 
    query,
    read_rows,
    read_bytes,
    result_rows,
    result_bytes,
    elapsed
FROM system.query_log
WHERE query_date = today()
  AND query LIKE '%SELECT%FROM events%'
ORDER BY event_time DESC
LIMIT 10;

#EXPLAIN

-- План выполнения
EXPLAIN SELECT * FROM events WHERE user_id = 123;

-- План с индексами
EXPLAIN indexes = 1 SELECT * FROM events WHERE user_id = 123;

-- План с проекциями
EXPLAIN projections = 1 
SELECT user_id, count() FROM events GROUP BY user_id;

#Практика: индекс должен окупиться

Снимите EXPLAIN indexes = 1 и метрики запроса без вторичного индекса.
Добавьте индекс, выполните MATERIALIZE INDEX только на учебной таблице и повторите замеры.
Измените распределение данных так, чтобы корреляция с ключом сортировки исчезла.
Покажите случай, в котором индекс перестал исключать гранулы.
Сравните размер индекса и время вставки.

Индекс засчитывается только при измеримом уменьшении read_rows или read_bytes на заявленном паттерне запросов.

Избегайте функций в WHERE для использования индексов

Индексы помогают читать меньше данных на одном узле. Следующая тема добавляет второй узел и другой класс задач: репликацию, кворумы записи и восстановление после сбоя.

Характеристика

B-дерево (PostgreSQL)

Разреженный индекс (ClickHouse)

Плотность

Плотный (каждая строка)

Разреженный (каждая N-я строка)

Назначение

Быстрый поиск строк

Пропуск нерелевантных частей данных

Размер

Зависит от индекса и данных

Мал, потому что хранит метки гранул

Обновление

Поддерживается механизмом индекса

Создаётся вместе с каждой частью данных

Тип

Описание

Когда использовать

minmax

Мин/макс значения

Для диапазонов, когда значения коррелируют с порядком данных

set

Множество значений в блоке

Для локально низкой кардинальности и равенства/IN

ngrambf

N-граммы для строк

Для LIKE '%pattern%' поиска

tokenbf

Токены для строк

Для полнотекстового поиска

Характеристика

Вторичный индекс

Проекция

Назначение

Пропуск гранул

Альтернативное представление данных

Размер

Зависит от типа и гранулярности

Значительный: дополнительные данные

Эффект

Только при исключении гранул

Только если план выбрал проекцию

Для агрегаций

Нет

Да

Для фильтрации

Да

Прозрачность

Автоматически