Выбор первичного ключа

Мы взаимозаменяемо используем термин "ключ сортировки", чтобы ссылаться на "первичный ключ" на этой странице. Строго говоря, они отличаются в ClickHouse, но для целей этого документа читатели могут использовать их взаимозаменяемо, при этом ключ сортировки будет относиться к колонкам, указанным в таблице ORDER BY.

Обратите внимание, что первичный ключ ClickHouse работает совсем иначе для тех, кто знаком с аналогичными терминами в OLTP базах данных, таких как Postgres.

Выбор эффективного первичного ключа в ClickHouse имеет решающее значение для производительности запросов и эффективности хранения. ClickHouse организует данные в части, каждая из которых содержит свой разреженный первичный индекс. Этот индекс значительно ускоряет запросы, уменьшая объем просматриваемых данных. Кроме того, поскольку первичный ключ определяет физический порядок данных на диске, он напрямую влияет на эффективность сжатия. Оптимально упорядоченные данные сжимаются более эффективно, что дополнительно повышает производительность, уменьшая ввод-вывод.

1. При выборе ключа сортировки следует приоритизировать колонки, часто используемые в фильтрах запросов (т.е. в WHERE клаузе), особенно те, которые исключают большое количество строк.
2. Колонки, высоко коррелирующие с другими данными в таблице, также полезны, так как непрерывное хранение улучшает коэффициенты сжатия и эффективность памяти во время операций GROUP BY и ORDER BY.

Некоторые простые правила могут быть применены, чтобы помочь выбрать ключ сортировки. Следующие могут иногда конфликтовать, поэтому рассмотрите их в порядке. Пользователи могут определить несколько ключей из этого процесса, при этом 4-5 обычно достаточно:

Важно

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

Пример

Рассмотрим следующую таблицу posts_unordered. Она содержит по одной строке на пост Stack Overflow.

Эта таблица не имеет первичного ключа - как указано в ORDER BY tuple().

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

Обратите внимание на количество строк и байт, прочитанных в этом запросе. Без первичного ключа запросы должны сканировать весь набор данных.

Использование EXPLAIN indexes=1 подтверждает полное сканирование таблицы из-за отсутствия индексации.

Предположим, таблица posts_ordered, содержащая те же данные, определяется с ORDER BY, установленным как (PostTypeId, toDate(CreationDate)), т.е.

PostTypeId имеет кардинальность 8 и является логическим выбором для первого элемента в нашем ключе сортировки. Признавая, что фильтрация по гранулярности даты, вероятно, будет достаточной (это также будет полезно для фильтров по дате и времени), мы используем toDate(CreationDate) в качестве второго компонента нашего ключа. Это также даст меньший индекс, поскольку дату можно представить 16 битами, что ускоряет фильтрацию.

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

Если тот же запрос повторить на таблице с этим ключом сортировки:

Теперь этот запрос использует разреженное индексирование, значительно сокращая объём прочитанных данных и ускоряя время выполнения в 4 раза - обратите внимание на сокращение количества строк и байт, прочитанных.

Использование индекса можно подтвердить с помощью EXPLAIN indexes=1.

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

примечание

Все колонки в таблице будут отсортированы на основе значений указанного ключа сортировки, не важно, включены ли они в сам ключ. Например, если CreationDate используется как ключ, порядок значений во всех остальных колонках будет соответствовать порядку значений в колонке CreationDate. Можно указать несколько ключей сортировки - это будет упорядочивать с теми же семантиками, что и клаузула ORDER BY в запросе SELECT.

Полное руководство по выбору первичных ключей можно найти здесь.

Для более глубокого понимания того, как ключи сортировки улучшают сжатие и дополнительно оптимизируют хранение, изучите официальные руководства по Сжатию в ClickHouse и Кодекам сжатия колонок.