Продолжаем серию постов про ClickHouse! Ранее мы разобрали основные движки таблиц.
Сегодня углубимся в сердце производительности ClickHouse — ключи и индексы.

Важное уточнение: всё, о чём поговорим сегодня, работает только для семейства движков MergeTree.

Первичный ключ (Primary Key)
Индексы ClickHouse основаны на разреженном индексировании (Sparse Indexing) — альтернативе B-деревьям, используемым традиционными СУБД.
В B-деревьях индексируется каждая строка, что хорошо подходит для точечных запросов (point queries), характерных для OLTP-задач.
Однако это приводит к низкой скорости вставки больших объемов данных и высокому потреблению памяти и дискового пространства.

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

Для наглядности создадим таблицу пользовательских логов и вставим в нее данные:

CREATE TABLE user_access_logs
(
    user_id UInt32,
    page_url String,
    access_time DateTime,
    ip_address String,
    session_duration UInt32
)
ENGINE = MergeTree
ORDER BY (user_id, access_time);

INSERT INTO user_access_logs 
SELECT 
    number % 5000 as user_id, 
    concat('https://site.com/page', toString(rand() % 100)) as page_url,
    now() - (rand() % 2592000) as access_time,
    concat('192.168.', toString(rand() % 255), '.', toString(rand() % 255)) as ip_address,
    rand() % 300 as session_duration
FROM numbers(1000000);

Важно: Если отдельно не указать первичные ключи, ClickHouse использует ключи сортировки (ORDER BY) в качестве первичных ключей. В этой таблице user_id и access_time будут первичными ключами.
При каждой вставке данных они будут сортироваться сначала по user_id, затем по access_time.

Фильтрация по первому первичному ключу
Посмотрим, что происходит при фильтрации по user_id (первый ключ):

EXPLAIN indexes=1
SELECT * FROM user_access_logs WHERE user_id = 100;

Результат анализа индексов: Система определила user_id как первичный ключ и исключила большинство гранул с его помощью!

Фильтрация по второму первичному ключу
Теперь попробуем фильтрацию по access_time (второй ключ):

EXPLAIN indexes=1
SELECT * FROM user_access_logs 
WHERE access_time >= '2024-01-15 00:00:00' 
  AND access_time < '2024-01-16 00:00:00';

Результат анализа индексов: База данных определила access_time как первичный ключ, но не смогла эффективно исключить гранулы. Почему?
Потому что ClickHouse использует бинарный поиск только для первого ключа, а для остальных ключей — общий исключающий поиск, который гораздо менее эффективен.

Решение: правильный порядок ключей
Если мы поменяем порядок ключей в ORDER BY, поместив access_time на первое место (так как временные метки часто используются для диапазонных запросов), то получим лучшие результаты:

CREATE TABLE user_access_logs_optimized
(
    `user_id` UInt32,
    `page_url` String,
    `access_time` DateTime,
    `ip_address` String,
    `session_duration` UInt32
)
ENGINE = MergeTree
ORDER BY (toStartOfDay(access_time), user_id, access_time);

Теперь при фильтрации по user_id (который стал вторым ключом):

EXPLAIN indexes=1
SELECT * FROM user_access_logs_optimized WHERE user_id = 100;

Результат: ClickHouse всё равно сможет эффективно фильтровать данные, используя комбинированную стратегию поиска.

Ключевое правило - всегда старайтесь упорядочивать первичные ключи от низкой к высокой кардинальности:
— Сначала ключи с малым количеством уникальных значений
— Затем ключи с большим количеством уникальных значений
Это обеспечит максимальную эффективность индексов для различных типов запросов.

#ClickHouse #БазыДанных #Аналитика #Индексы #Производительность #Оптимизация #OLAP