Перейти к основному содержанию

​
Введение

В этом руководстве мы подробно разберём индексирование в ClickHouse. Мы наглядно покажем и детально обсудим: При желании вы можете самостоятельно выполнить на своей машине все операторы ClickHouse SQL и запросы, приведённые в этом руководстве. Инструкции по установке ClickHouse и началу работы см. в разделе Быстрый старт.
Это руководство посвящено разреженным первичным индексам ClickHouse.О вторичных индексах пропуска данных в ClickHouse см. в руководстве.

​
Набор данных

В этом руководстве мы будем использовать пример обезличенного набора данных о веб-трафике.
  • Мы будем использовать подмножество из 8,87 миллиона строк (событий) из этого набора данных.
  • Размер данных в несжатом виде составляет 8,87 миллиона событий и около 700 МБ. При хранении в ClickHouse они сжимаются до 200 МБ.
  • В нашем подмножестве каждая строка содержит три столбца: идентификатор интернет-пользователя (столбец UserID), URL, по которому он щёлкнул (столбец URL), и время события (столбец EventTime).
Используя эти три столбца, мы уже можем сформулировать несколько типичных запросов веб-аналитики, например:
  • “Какие 10 URL пользователь открывал чаще всего?”
  • “Какие 10 пользователей чаще всего открывали определённый URL?”
  • “В какое время (например, в какие дни недели) пользователь чаще всего открывает определённый URL?”

​
Тестовая машина

Все показатели времени выполнения, приведённые в этом документе, основаны на запуске ClickHouse 22.2.1 локально на MacBook Pro с чипом Apple M1 Pro и 16 ГБ оперативной памяти.

​
Полное сканирование таблицы

Чтобы увидеть, как выполняется запрос к нашему набору данных без первичного ключа, создадим таблицу (с движком таблицы MergeTree), выполнив следующий SQL DDL-оператор:
Затем вставьте в таблицу подмножество набора данных hits с помощью следующего SQL-оператора INSERT. Для этого используется табличная функция URL, чтобы загрузить подмножество полного набора данных, размещённого на clickhouse.com:
Ответ:
Вывод клиента ClickHouse показывает, что приведённый выше оператор вставил в таблицу 8,87 миллиона строк. Наконец, чтобы упростить дальнейшее изложение в этом руководстве и сделать диаграммы и результаты воспроизводимыми, мы оптимизируем таблицу с помощью ключевого слова FINAL:
В целом не требуется и не рекомендуется сразу оптимизировать таблицу после загрузки в неё данных. Почему это необходимо в данном примере, станет понятно далее.
Теперь выполним наш первый запрос для веб-аналитики. Ниже вычисляются 10 URL, по которым чаще всего кликал интернет-пользователь с UserID 749927693:
Ответ:
Вывод клиента ClickHouse показывает, что ClickHouse выполнил полное сканирование таблицы! ClickHouse пришлось прочитать каждую из 8,87 миллиона строк в нашей таблице. Такой подход не масштабируется. Чтобы сделать это гораздо эффективнее и намного быстрее, нам нужно использовать таблицу с подходящим первичным ключом. Это позволит ClickHouse автоматически создать разреженный первичный индекс (на основе столбцов первичного ключа), который затем можно использовать, чтобы значительно ускорить выполнение нашего примера запроса.

​
Проектирование индексов в ClickHouse

​
Проектирование индексов для огромных объёмов данных

В традиционных реляционных системах управления базами данных первичный индекс содержал бы одну запись на каждую строку таблицы. В результате первичный индекс для нашего набора данных содержал бы 8,87 миллиона записей. Такой индекс позволяет быстро находить конкретные строки, обеспечивая высокую эффективность поисковых запросов и точечных обновлений. Поиск записи в структуре данных B(+)-Tree имеет среднюю временную сложность O(log n); точнее, log_b n = log_2 n / log_2 b, где b — коэффициент ветвления B(+)-Tree, а n — число индексированных строк. Поскольку b обычно находится в диапазоне от нескольких сотен до нескольких тысяч, B(+)-Tree представляют собой очень неглубокие структуры, и для поиска записей требуется небольшое число операций позиционирования на диске. При 8,87 миллиона строк и коэффициенте ветвления 1000 в среднем требуется 2,3 таких обращения к диску. За это приходится платить: дополнительными затратами дискового пространства и памяти, более высокими издержками на вставку при добавлении новых строк в таблицу и записей в индекс, а также иногда необходимостью перебалансировки B-Tree. С учётом ограничений, связанных с индексами B-Tree, движки таблиц в ClickHouse используют другой подход. Семейство движков MergeTree в ClickHouse спроектировано и оптимизировано для работы с огромными объёмами данных. Эти таблицы рассчитаны на приём миллионов вставок строк в секунду и хранение очень больших объёмов данных (сотни PB). Данные быстро записываются в таблицу по частям, а затем к этим частям в фоне применяются правила слияния. В ClickHouse у каждой части есть собственный первичный индекс. Когда части сливаются, первичные индексы слитой части тоже объединяются. В тех очень больших масштабах, на которые рассчитан ClickHouse, крайне важно эффективно расходовать диск и память. Поэтому вместо индексации каждой строки первичный индекс части содержит одну запись индекса (так называемую «метку») на каждую группу строк (называемую «гранулой») — этот приём называется разреженным индексом. Разреженная индексация возможна потому, что ClickHouse хранит строки каждой части на диске упорядоченными по столбцам первичного ключа. Вместо прямого поиска отдельных строк (как в индексе на основе B-Tree) разреженный первичный индекс позволяет быстро — с помощью двоичного поиска по записям индекса — определить группы строк, которые потенциально могут соответствовать запросу. Найденные группы потенциально подходящих строк (гранулы) затем параллельно передаются в движок ClickHouse для поиска совпадений. Такое проектирование индексов позволяет сделать первичный индекс небольшим (он может и должен полностью помещаться в оперативную память), при этом всё равно значительно ускоряя выполнение запросов — особенно диапазонных запросов, типичных для аналитических сценариев работы с данными. Ниже подробно показано, как ClickHouse строит и использует свой разреженный первичный индекс. Далее в статье мы обсудим некоторые рекомендации по выбору, исключению и упорядочиванию столбцов таблицы, которые используются для построения индекса (столбцов первичного ключа).

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

Создайте таблицу с составным первичным ключом по столбцам UserID и URL:

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

  • Задаёт составной ключ сортировки таблицы с помощью предложения ORDER BY.
  • Явно задаёт, сколько записей будет в первичном индексе, с помощью следующих настроек:
    • index_granularity: явно установлено значение по умолчанию 8192. Это означает, что на каждую группу из 8192 строк в первичном индексе будет одна запись. Например, если таблица содержит 16384 строки, в индексе будет две записи.
    • index_granularity_bytes: установлено в 0, чтобы отключить адаптивную гранулярность индекса. Адаптивная гранулярность индекса означает, что ClickHouse автоматически создаёт одну запись индекса для группы из n строк, если выполняется хотя бы одно из следующих условий:
      • Если n меньше 8192, а суммарный размер данных этих n строк больше или равен 10 МБ (значение по умолчанию для index_granularity_bytes).
      • Если суммарный размер данных n строк меньше 10 МБ, но n равно 8192.
    • compress_primary_key: установлено в 0, чтобы отключить сжатие первичного индекса. Это позволит нам при необходимости позже просмотреть его содержимое.

Первичный ключ в приведённом выше DDL-операторе приводит к созданию первичного индекса на основе двух указанных столбцов ключа.
Далее вставьте данные:
Ответ выглядит так:

Затем оптимизируйте таблицу:

Мы можем использовать следующий запрос, чтобы получить метаданные о нашей таблице:
Ответ:
Вывод клиента ClickHouse показывает:
  • Данные таблицы хранятся в широком формате в определённом каталоге на диске, то есть внутри этого каталога для каждого столбца таблицы будет один файл данных (и один файл меток).
  • В таблице 8,87 миллиона строк.
  • Общий размер несжатых данных всех строк составляет 733,28 МБ.
  • Общий сжатый размер всех строк на диске составляет 206,94 МБ.
  • У таблицы есть первичный индекс с 1083 записями (так называемыми ‘метками’), а размер индекса составляет 96,93 КБ.
  • В сумме данные таблицы, файлы меток и файл первичного индекса занимают на диске 207,07 МБ.

​
Данные хранятся на диске в порядке столбцов первичного ключа

Наша таблица, созданная выше, имеет
  • Если бы мы указали только ключ сортировки, первичный ключ был бы неявно задан равным ключу сортировки.
  • Для более эффективного использования памяти мы явно указали первичный ключ, содержащий только те столбцы, по которым наши запросы выполняют фильтрацию. Первичный индекс, построенный на основе первичного ключа, полностью загружается в оперативную память.
  • Чтобы сохранить единообразие диаграмм в руководстве и максимально увеличить коэффициент сжатия, мы определили отдельный ключ сортировки, включающий все столбцы таблицы (если похожие данные в столбце расположены близко друг к другу, например благодаря сортировке, они сжимаются лучше).
  • Если заданы оба ключа, первичный ключ должен быть префиксом ключа сортировки.
Вставленные строки хранятся на диске в лексикографическом порядке (по возрастанию) по столбцам первичного ключа (и по дополнительному столбцу EventTime из ключа сортировки).
ClickHouse позволяет вставлять несколько строк с одинаковыми значениями в столбцах первичного ключа. В этом случае (см. строку 1 и строку 2 на диаграмме ниже) итоговый порядок определяется указанным ключом сортировки и, следовательно, значением столбца EventTime.
ClickHouse — это столбцовая система управления базами данных. Как показано на диаграмме ниже,
  • в представлении на диске для каждого столбца таблицы существует отдельный файл данных (*.bin), в котором все значения этого столбца хранятся в сжатом формате, и
  • 8,87 миллиона строк хранятся на диске в лексикографическом порядке по возрастанию по столбцам первичного ключа (и дополнительным столбцам ключа сортировки), то есть в данном случае
    • сначала по UserID,
    • затем по URL,
    • и наконец по EventTime:
UserID.bin, URL.bin и EventTime.bin — это файлы данных на диске, в которых хранятся значения столбцов UserID, URL и EventTime.
  • Поскольку первичный ключ задает лексикографический порядок строк на диске, у таблицы может быть только один первичный ключ.
  • Мы нумеруем строки, начиная с 0, чтобы соответствовать внутренней схеме нумерации строк в ClickHouse, которая также используется в сообщениях журналирования.

​
Данные организуются в гранулы для параллельной обработки

Для обработки данных значения столбцов таблицы логически делятся на гранулы. Гранула — это наименьший неделимый набор данных, который передаётся в ClickHouse в потоковом режиме для обработки. Это означает, что вместо чтения отдельных строк ClickHouse всегда читает (в потоковом режиме и параллельно) целую группу строк — гранулу.
Значения столбцов физически не хранятся внутри гранул: гранулы — это лишь логический способ организации значений столбцов для обработки запросов.
Следующая диаграмма показывает, как (значения столбцов) 8,87 миллиона строк нашей таблицы организованы в 1083 гранулы, поскольку DDL-оператор таблицы содержит настройку index_granularity (со значением по умолчанию 8192). Первые 8192 строки (их значения столбцов) — в физическом порядке на диске — логически относятся к грануле 0, затем следующие 8192 строки (их значения столбцов) относятся к грануле 1 и так далее.
  • Последняя гранула (гранула 1082) “содержит” менее 8192 строк.
  • В начале этого руководства, в разделе “Подробности DDL-оператора”, мы упоминали, что отключили адаптивную гранулярность индекса (чтобы упростить изложение в этом руководстве, а также сделать диаграммы и результаты воспроизводимыми). Поэтому все гранулы (кроме последней) в нашей таблице-примере имеют одинаковый размер.
  • Для таблиц с адаптивной гранулярностью индекса (по умолчанию гранулярность индекса является адаптивной) размер некоторых гранул может быть меньше 8192 строк в зависимости от размера данных в строках.
  • Мы выделили оранжевым цветом некоторые значения из столбцов первичного ключа (UserID, URL). Эти выделенные оранжевым значения столбцов — значения столбцов первичного ключа из первой строки каждой гранулы. Как мы увидим ниже, именно эти выделенные оранжевым значения будут записями в первичном индексе таблицы.
  • Мы нумеруем гранулы, начиная с 0, чтобы соответствовать внутренней схеме нумерации ClickHouse, которая также используется в сообщениях журналирования.

​
У первичного индекса одна запись на гранулу

Первичный индекс создаётся на основе гранул, показанных на диаграмме выше. Этот индекс представляет собой несжатый файл в виде плоского массива (primary.idx), содержащий так называемые числовые метки индекса, начиная с 0. На диаграмме ниже показано, что индекс хранит значения столбцов первичного ключа (значения, отмеченные оранжевым на диаграмме выше) для первой строки каждой гранулы. Иными словами, первичный индекс хранит значения столбцов первичного ключа из каждой 8192-й строки таблицы (в соответствии с физическим порядком строк, заданным столбцами первичного ключа). Например:
  • первая запись индекса (‘метка 0’ на диаграмме ниже) хранит значения столбцов ключа первой строки гранулы 0 с диаграммы выше,
  • вторая запись индекса (‘метка 1’ на диаграмме ниже) хранит значения столбцов ключа первой строки гранулы 1 с диаграммы выше, и так далее.
Всего индекс содержит 1083 записи для нашей таблицы с 8,87 миллиона строк и 1083 гранулами:
  • Для таблиц с адаптивной гранулярностью индекса в первичном индексе также хранится ещё одна, дополнительная “финальная” метка, в которой записываются значения столбцов первичного ключа последней строки таблицы. Но поскольку мы отключили адаптивную гранулярность индекса (чтобы упростить объяснение в этом руководстве, а также сделать диаграммы и результаты воспроизводимыми), индекс нашей таблицы в примере не содержит этой финальной метки.
  • Файл первичного индекса полностью загружается в оперативную память. Если размер файла превышает объём доступной свободной памяти, ClickHouse выдаст ошибку.

В самоуправляемом кластере ClickHouse мы можем использовать табличную функцию file, чтобы просмотреть содержимое первичного индекса нашей таблицы-примера.Для этого сначала нужно скопировать файл первичного индекса в user_files_path одного из узлов работающего кластера:

  • Шаг 1: Получить путь к части, содержащей файл первичного индекса
    • SELECT path FROM system.parts WHERE table = 'hits_UserID_URL' AND active = 1    на тестовой машине возвращает /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4.
  • Шаг 2: Получить user_files_path
    • Значение user_files_path по умолчанию в Linux: /var/lib/clickhouse/user_files/    В Linux можно проверить, не было ли оно изменено: $ grep user_files_path /etc/clickhouse-server/config.xmlНа тестовой машине этот путь — /Users/tomschreiber/Clickhouse/user_files/
  • Шаг 3: Скопировать файл первичного индекса в user_files_path
    • cp /Users/tomschreiber/Clickhouse/store/85f/85f4ee68-6e28-4f08-98b1-7d8affa1d88c/all_1_9_4/primary.idx /Users/tomschreiber/Clickhouse/user_files/primary-hits_UserID_URL.idx

Теперь мы можем просмотреть содержимое первичного индекса через SQL:
  • Получить количество записей
    • SELECT count( )<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String'); возвращает 1083
  • Получить первые две метки индекса
    • SELECT UserID, URL<br/>FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 0, 2;    возвращает240923, http://showtopics.html%3...<br/> 4073710, http://mk.ru&pos=3_0
  • Получить последнюю метку индекса
    • SELECT UserID, URL FROM file('primary-hits_UserID_URL.idx', 'RowBinary', 'UserID UInt32, URL String')<br/>LIMIT 1082, 1; возвращает 4292714039 │ http://sosyal-mansetleri...

Это в точности соответствует нашей диаграмме содержимого первичного индекса для таблицы-примера:

Записи первичного ключа называются метками индекса, потому что каждая запись индекса обозначает начало определённого диапазона данных. В частности, для таблицы-примера:
  • Метки индекса UserID: Значения UserID, хранящиеся в первичном индексе, отсортированы по возрастанию.
    Поэтому «метка 1» на диаграмме выше указывает, что значения UserID всех строк таблицы в грануле 1 и во всех следующих гранулах гарантированно больше либо равны 4.073.710.
Как мы увидим позже, этот глобальный порядок позволяет ClickHouse использовать алгоритм двоичного поиска по меткам индекса для первого столбца ключа, когда запрос фильтрует по первому столбцу первичного ключа.
  • Метки индекса для URL: Довольно близкая мощность столбцов первичного ключа UserID и URL означает, что метки индекса для всех столбцов ключа после первого, как правило, задают диапазон данных только до тех пор, пока значение предыдущего столбца ключа остаётся одинаковым для всех строк таблицы хотя бы в пределах текущей гранулы.
    Например, поскольку значения UserID для метки 0 и метки 1 на диаграмме выше различаются, ClickHouse не может предположить, что все значения URL во всех строках таблицы в грануле 0 больше или равны 'http://showtopics.html%3...'. Однако если бы значения UserID для метки 0 и метки 1 на диаграмме выше были одинаковыми (то есть значение UserID оставалось бы одним и тем же для всех строк таблицы в пределах гранулы 0), ClickHouse мог бы предположить, что все значения URL во всех строках таблицы в грануле 0 больше или равны 'http://showtopics.html%3...'.     Позже мы подробнее обсудим, как это влияет на производительность выполнения запросов.

​
Первичный индекс используется для отбора гранул

Теперь мы можем выполнять запросы с использованием первичного индекса. Следующий запрос вычисляет 10 URL, по которым пользователь с UserID 749927693 переходил чаще всего.
Ответ:
Теперь вывод клиента ClickHouse показывает, что вместо полного сканирования таблицы в ClickHouse было передано только 8,19 тысячи строк. Если трассировочное логирование включено, то в файле журнала сервера ClickHouse видно, что ClickHouse выполнял двоичный поиск по 1083 меткам индекса UserID, чтобы определить гранулы, которые могут содержать строки со значением 749927693 в столбце UserID. Для этого требуется 19 шагов при средней временной сложности O(log2 n):
Мы видим в приведённом выше журнале трассировки, что запросу удовлетворила одна метка из 1083 существующих меток.

Была определена метка 176 (‘найденная левая граничная метка’ включается, а ‘найденная правая граничная метка’ не включается), и поэтому все 8192 строки из гранулы 176 (которая начинается со строки 1.441.792 — мы увидим это позже в этом руководстве) затем передаются в ClickHouse, чтобы найти фактические строки со значением 749927693 в столбце UserID.

Мы также можем воспроизвести это с помощью предложения EXPLAIN в нашем примере запроса:
Ответ выглядит так:
По выводу клиента видно, что из 1083 гранул была выбрана одна, которая может содержать строки со значением 749927693 в столбце UserID.
ВыводКогда запрос фильтрует по столбцу, который входит в составной ключ и является его первым столбцом, ClickHouse выполняет алгоритм двоичного поиска по меткам индекса этого ключевого столбца.

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

​
Файлы меток используются для определения расположения гранул

Следующая диаграмма иллюстрирует часть файла первичного индекса нашей таблицы. Как обсуждалось выше, с помощью двоичного поиска по 1083 меткам UserID в индексе была найдена метка 176. Следовательно, соответствующая ей гранула 176 потенциально может содержать строки со значением 749.927.693 в столбце UserID.

Приведённая выше диаграмма показывает, что метка 176 — это первая запись индекса, для которой минимальное значение UserID связанной гранулы 176 меньше 749.927.693, а минимальное значение UserID гранулы 177 для следующей метки (метки 177) больше этого значения. Поэтому только гранула 176, соответствующая метке 176, потенциально может содержать строки со значением 749.927.693 в столбце UserID.

Чтобы подтвердить (или опровергнуть), что некоторые строки в грануле 176 содержат значение 749.927.693 в столбце UserID, все 8192 строки этой гранулы нужно передать в ClickHouse потоком. Для этого ClickHouse должен знать физическое расположение гранулы 176. В ClickHouse физические расположения всех гранул нашей таблицы хранятся в файлах меток. Как и в случае с файлами данных, для каждого столбца таблицы существует отдельный файл меток. Следующая диаграмма показывает три файла меток UserID.mrk, URL.mrk и EventTime.mrk, в которых хранятся физические расположения гранул для столбцов таблицы UserID, URL и EventTime. Мы уже обсуждали, что первичный индекс — это плоский несжатый файл-массив (primary.idx), содержащий метки индекса, нумерация которых начинается с 0. Аналогично, файл меток тоже представляет собой плоский несжатый файл-массив (*.mrk), содержащий метки, нумерация которых начинается с 0. После того как ClickHouse определяет и выбирает метку индекса для гранулы, которая потенциально может содержать строки, соответствующие запросу, в файлах меток можно выполнить позиционный поиск по массиву, чтобы получить физические расположения этой гранулы. Каждая запись в файле меток для конкретного столбца хранит два расположения в виде смещений:
  • Первое смещение ('block_offset' на диаграмме выше) указывает на блок в сжатом файле данных столбца, который содержит сжатую версию выбранной гранулы. Такой сжатый блок потенциально может содержать несколько сжатых гранул. При чтении найденный сжатый блок распаковывается в оперативную память.
  • Второе смещение ('granule_offset' на диаграмме выше) из файла меток указывает расположение гранулы внутри несжатых данных блока.
После этого все 8192 строки, принадлежащие найденной несжатой грануле, передаются в ClickHouse для дальнейшей обработки.
  • Для таблиц с широким форматом и без адаптивной гранулярности индекса ClickHouse использует файлы меток .mrk, как показано выше; они содержат записи с двумя 8-байтными адресами в каждой записи. Эти записи представляют собой физические расположения гранул, и все такие гранулы имеют одинаковый размер.
Гранулярность индекса по умолчанию адаптивна, но для нашей таблицы-примера мы отключили адаптивную гранулярность индекса (чтобы упростить объяснение в этом руководстве, а также сделать диаграммы и результаты воспроизводимыми). Наша таблица использует широкий формат, потому что объём данных превышает min_bytes_for_wide_part (по умолчанию это 10 МБ для самоуправляемых кластеров).
  • Для таблиц с широким форматом и адаптивной гранулярностью индекса ClickHouse использует файлы меток .mrk2, которые содержат записи, аналогичные файлам .mrk, но с дополнительным третьим значением в каждой записи: числом строк в грануле, с которой связана текущая запись.
  • Для таблиц с компактным форматом ClickHouse использует файлы меток .mrk3.
Зачем нужны файлы метокПочему первичный индекс не содержит напрямую физические расположения гранул, соответствующих меткам индекса?Потому что в тех очень больших масштабах, на которые рассчитан ClickHouse, крайне важно эффективно расходовать дисковое пространство и память.Файл первичного индекса должен помещаться в оперативную память.В нашем примере запроса ClickHouse использовал первичный индекс и выбрал одну гранулу, которая потенциально может содержать строки, соответствующие запросу. Только для этой гранулы ClickHouse затем нужны физические смещения, чтобы считать соответствующие строки для дальнейшей обработки.Кроме того, информация о смещениях нужна только для столбцов UserID и URL.Информация о смещениях не нужна для столбцов, которые не используются в запросе, например EventTime.Для нашего примера запроса ClickHouse нужны только два смещения физического расположения для гранулы 176 в файле данных UserID (UserID.bin) и два смещения физического расположения для гранулы 176 в файле данных URL (URL.bin).Дополнительный уровень косвенности, обеспечиваемый файлами меток, позволяет не хранить непосредственно в первичном индексе записи о физических расположениях всех 1083 гранул для всех трёх столбцов и тем самым избежать хранения ненужных (и потенциально неиспользуемых) данных в оперативной памяти.
Следующая схема и текст ниже показывают, как в нашем примере запроса ClickHouse находит гранулу 176 в файле данных UserID.bin. Ранее в этом руководстве мы уже обсуждали, что ClickHouse выбрал метку 176 в первичном индексе и, следовательно, гранулу 176 как потенциально содержащую строки, соответствующие нашему запросу. Теперь ClickHouse использует выбранный из индекса номер метки (176) для позиционного поиска в массиве в файле меток UserID.mrk, чтобы получить два смещения, необходимых для нахождения гранулы 176. Как показано на схеме, первое смещение указывает на сжатый блок в файле данных UserID.bin, который, в свою очередь, содержит сжатую версию гранулы 176. После того как найденный блок распакован в оперативную память, второе смещение из файла меток можно использовать для нахождения гранулы 176 в несжатых данных. Чтобы выполнить наш пример запроса (10 URL с наибольшим числом кликов для интернет-пользователя с UserID 749.927.693), ClickHouse нужно найти гранулу 176 (и считать все значения из неё) как из файла данных UserID.bin, так и из файла данных URL.bin. На схеме выше показано, как ClickHouse находит гранулу в файле данных UserID.bin. Параллельно ClickHouse делает то же самое для гранулы 176 в файле данных URL.bin. Эти две соответствующие гранулы выравниваются и передаются в ClickHouse engine для дальнейшей обработки, то есть агрегации и подсчёта значений URL по группам для всех строк, где UserID равен 749.927.693, а затем вывода 10 самых больших групп URL в порядке убывания количества.

​
Использование нескольких индексов первичного ключа

​
Второстепенные столбцы ключа могут быть (не)эффективны

Если запрос фильтрует по столбцу, который входит в составной ключ и является первым столбцом ключа, то ClickHouse использует алгоритм двоичного поиска по меткам индекса этого столбца ключа. Но что происходит, если запрос фильтрует по столбцу, который входит в составной ключ, но не является первым столбцом ключа?
Здесь мы рассматриваем сценарий, в котором запрос намеренно фильтрует не по первому столбцу ключа, а по второстепенному столбцу ключа.Если запрос фильтрует и по первому столбцу ключа, и по любому из следующих за ним столбцов ключа, ClickHouse выполняет двоичный поиск по меткам индекса первого столбца ключа.


Мы используем запрос, который вычисляет 10 пользователей, чаще всего переходивших по URL “http://public&#95;search”:
Ответ:
Вывод клиента показывает, что ClickHouse почти выполнил полное сканирование таблицы, несмотря на то, что столбец URL является частью составного первичного ключа! ClickHouse считывает 8,81 миллиона строк из 8,87 миллиона строк в таблице. Если включен trace_logging, то файл журнала сервера ClickHouse показывает, что ClickHouse использовал универсальный алгоритм поиска с исключением по 1083 меткам индекса URL, чтобы определить гранулы, которые потенциально могут содержать строки со значением столбца URL “http://public&#95;search”:
Как видно из примера трассировочного лога выше, 1076 из 1083 гранул (по меткам индекса) были выбраны как потенциально содержащие строки с совпадающим значением URL. В результате в движок ClickHouse было передано для обработки 8,81 миллиона строк (параллельно с использованием 10 потоков), чтобы определить строки, которые действительно содержат значение URL “http://public&#95;search”. Однако, как мы увидим далее, из этих 1076 выбранных гранул только 39 действительно содержат совпадающие строки. Хотя первичный индекс, основанный на составном первичном ключе (UserID, URL), был очень полезен для ускорения запросов, фильтрующих строки по конкретному значению UserID, для ускорения запроса, фильтрующего строки по конкретному значению URL, этот индекс почти не помогает. Причина в том, что столбец URL не является первым ключевым столбцом, и поэтому ClickHouse использует универсальный алгоритм поиска с исключением (вместо двоичного поиска) по меткам индекса столбца URL, и эффективность этого алгоритма зависит от разницы в мощности между столбцом URL и предшествующим ему ключевым столбцом UserID. Чтобы проиллюстрировать это, приведём некоторые подробности о том, как работает универсальный алгоритм поиска с исключением.

​
Универсальный алгоритм поиска с исключением

Ниже показано, как работает универсальный алгоритм поиска с исключением ClickHouse, когда гранулы выбираются по вторичному столбцу, а предшествующий ключевой столбец имеет низкую или высокую мощность. В качестве примера для обоих случаев будем считать, что:
  • запрос ищет строки, где URL = “W3”.
  • используется абстрактная версия нашей таблицы hits с упрощёнными значениями UserID и URL.
  • для индекса используется один и тот же составной первичный ключ (UserID, URL). Это означает, что строки сначала упорядочиваются по значениям UserID. Затем строки с одинаковым значением UserID упорядочиваются по URL.
  • размер гранулы равен двум, то есть каждая гранула содержит две строки.
На диаграммах ниже оранжевым выделены значения ключевых столбцов для первой строки таблицы в каждой грануле. Предшествующий ключевой столбец имеет низкую мощность Предположим, что UserID имеет низкую мощность. В этом случае велика вероятность, что одно и то же значение UserID будет распределено по нескольким строкам таблицы, гранулам и, следовательно, меткам индекса. Для меток индекса с одинаковым UserID значения URL отсортированы по возрастанию (поскольку строки таблицы упорядочены сначала по UserID, а затем по URL). Это позволяет эффективно фильтровать данные, как описано ниже: Для процесса выбора гранул в наших абстрактных примерных данных на диаграмме выше возможны три разных сценария:
  1. Метка индекса 0, для которой значение URL меньше W3 и значение URL у непосредственно следующей метки индекса тоже меньше W3, может быть исключена, потому что метки 0 и 1 имеют одинаковое значение UserID. Обратите внимание: это предварительное условие исключения гарантирует, что гранула 0 целиком состоит из значений UserID U1, поэтому ClickHouse может предположить, что максимальное значение URL в грануле 0 тоже меньше W3, и исключить эту гранулу.
  2. Метка индекса 1, для которой значение URL меньше (или равно) W3, а значение URL у непосредственно следующей метки индекса больше (или равно) W3, выбирается, потому что это означает, что гранула 1 потенциально может содержать строки с URL W3.
  3. Метки индекса 2 и 3, для которых значение URL больше W3, могут быть исключены, поскольку метки первичного индекса хранят значения ключевых столбцов для первой строки таблицы в каждой грануле, а строки таблицы на диске отсортированы по значениям ключевых столбцов, поэтому гранулы 2 и 3 не могут содержать значение URL W3.
Предшествующий ключевой столбец имеет высокую мощность Когда UserID имеет высокую мощность, маловероятно, что одно и то же значение UserID будет распределено по нескольким строкам таблицы и гранулам. Это означает, что значения URL для меток индекса не возрастают монотонно: Как видно на диаграмме выше, все показанные метки, у которых значения URL меньше W3, выбираются для передачи строк связанных с ними гранул в движок ClickHouse. Это происходит потому, что, хотя все метки индекса на диаграмме подпадают под сценарий 1, описанный выше, они не удовлетворяют упомянутому предварительному условию исключения: непосредственно следующая метка индекса должна иметь то же значение UserID, что и текущая метка, — и потому не могут быть исключены. Например, рассмотрим метку индекса 0, для которой значение URL меньше W3 и значение URL у непосредственно следующей метки индекса тоже меньше W3. Она не может быть исключена, потому что непосредственно следующая метка индекса 1 не имеет того же значения UserID, что и текущая метка 0. В итоге это не позволяет ClickHouse делать предположения о максимальном значении URL в грануле 0. Вместо этого он должен исходить из того, что гранула 0 потенциально содержит строки со значением URL W3, и вынужден выбрать метку 0. То же самое верно для меток 1, 2 и 3.
Выводуниверсальный алгоритм поиска с исключением, который ClickHouse использует вместо алгоритма двоичного поиска, когда запрос фильтрует по столбцу, входящему в состав составного ключа, но не являющемуся первым столбцом ключа, наиболее эффективен, если предыдущий столбец ключа имеет низкую мощность.
В нашем примере оба столбца ключа (UserID, URL) имеют схожую высокую мощность, и, как уже объяснялось, универсальный алгоритм поиска с исключением не очень эффективен, когда предыдущий столбец ключа для столбца URL имеет более высокую или сопоставимую мощность.

​
Примечание об индексе пропуска данных

Из-за одинаково высокой мощности UserID и URL наш запрос с фильтрацией по URL тоже почти не выиграл бы от создания вторичного индекса пропуска данных для столбца URL в нашей таблице с составным первичным ключом (UserID, URL). Например, следующие два оператора создают и заполняют индекс пропуска данных minmax для столбца URL в нашей таблице:
Теперь ClickHouse создал дополнительный индекс, который хранит — для каждой группы из 4 последовательных гранул (обратите внимание на предложение GRANULARITY 4 в операторе ALTER TABLE выше) — минимальное и максимальное значения URL: Первая запись индекса (‘mark 0’ на диаграмме выше) хранит минимальное и максимальное значения URL для строк, относящихся к первым 4 гранулам нашей таблицы. Вторая запись индекса (‘mark 1’) хранит минимальное и максимальное значения URL для строк, относящихся к следующим 4 гранулам нашей таблицы, и так далее. (ClickHouse также создал специальный файл меток для индекса пропуска данных, чтобы определять расположение групп гранул, связанных с метками индекса.) Из-за столь же высокой мощности UserID и URL этот вторичный индекс пропуска данных не помогает исключать гранулы при выполнении нашего запроса с фильтрацией по URL. Конкретное значение URL, которое ищет запрос (то есть ‘http://public&#95;search&#39;), с большой вероятностью находится между минимальным и максимальным значениями, сохранёнными в индексе для каждой группы гранул, из-за чего ClickHouse вынужден выбирать эти группы гранул (поскольку они могут содержать строки, соответствующие запросу).

​
Необходимость использовать несколько первичных индексов

Как следствие, если мы хотим значительно ускорить наш пример запроса, который фильтрует строки с определённым URL, нам нужно использовать первичный индекс, оптимизированный под этот запрос. Если вдобавок мы хотим сохранить хорошую производительность нашего примера запроса, который фильтрует строки с определённым UserID, нам нужно использовать несколько первичных индексов. Ниже показано, как этого можно добиться.

​
Варианты создания дополнительных первичных индексов

Если мы хотим значительно ускорить оба наших примера запросов — тот, который фильтрует строки по конкретному UserID, и тот, который фильтрует строки по конкретному URL, — нам нужно использовать несколько первичных индексов, выбрав один из этих трех вариантов:
  • Создать вторую таблицу с другим первичным ключом.
  • Создать materialized view для существующей таблицы.
  • Добавить проекцию в существующую таблицу.
Во всех трех случаях наши примерные данные фактически будут дублироваться в дополнительной таблице, чтобы изменить первичный индекс таблицы и порядок сортировки строк. Однако эти три варианта различаются тем, насколько эта дополнительная таблица прозрачна для пользователя с точки зрения маршрутизации запросов и команд вставки. При создании второй таблицы с другим первичным ключом запросы нужно явно отправлять в ту версию таблицы, которая лучше всего подходит для конкретного запроса, а новые данные нужно явно вставлять в обе таблицы, чтобы поддерживать их синхронизацию: В случае с materialized view дополнительная таблица создается неявно, а данные в обеих таблицах автоматически поддерживаются в синхронизированном состоянии: А проекция — самый прозрачный вариант, потому что, помимо автоматической синхронизации неявно созданной (и скрытой) дополнительной таблицы при изменениях данных, ClickHouse также автоматически выберет наиболее эффективную версию таблицы для запросов: Далее мы подробнее рассмотрим эти три варианта создания и использования нескольких первичных индексов на реальных примерах.

​
Вариант 1: Вторичные таблицы

Мы создаём ещё одну таблицу, в которой меняем порядок столбцов ключа в первичном ключе по сравнению с исходной таблицей:
Вставьте все 8,87 миллиона строк из исходной таблицы в дополнительную таблицу:
Ответ выглядит так:
И наконец, оптимизируйте таблицу:
Поскольку мы изменили порядок столбцов в первичном ключе, вставленные строки теперь хранятся на диске в другом лексикографическом порядке (по сравнению с нашей исходной таблицей), и поэтому 1083 гранулы этой таблицы теперь тоже содержат иные значения, чем раньше: Вот получившийся первичный ключ: Теперь его можно использовать, чтобы значительно ускорить выполнение нашего примера запроса с фильтрацией по столбцу URL и определить 10 пользователей, которые чаще всего переходили по URL “http://public&#95;search”:
Ответ:
Теперь, вместо почти полного сканирования таблицы, ClickHouse выполнил этот запрос значительно эффективнее. С первичным индексом из исходной таблицы, где UserID был первым, а URL — вторым ключевым столбцом, ClickHouse использовал универсальный алгоритм поиска с исключением по меткам индекса для выполнения этого запроса, и это было не слишком эффективно из-за одинаково высокой мощности UserID и URL. Когда URL стал первым столбцом в первичном индексе, ClickHouse теперь выполняет двоичный поиск по меткам индекса. Это подтверждает соответствующий трассировочный лог в файле журнала сервера ClickHouse:
ClickHouse выбрал только 39 меток индекса вместо 1076 при использовании универсального алгоритма поиска с исключением. Обратите внимание, что дополнительная таблица оптимизирована для ускорения выполнения нашего примера запроса с фильтрацией по URL. Как и в случае с низкой производительностью этого запроса на нашей исходной таблице, наш пример запроса с фильтрацией по UserIDs тоже будет работать с новой дополнительной таблицей не слишком эффективно, потому что UserID теперь является вторым ключевым столбцом в первичном индексе этой таблицы, и поэтому ClickHouse будет использовать универсальный алгоритм поиска с исключением для выбора гранул, который не очень эффективен при столь же высокой мощности UserID и URL. Откройте блок с подробностями.

Ответ:
Log сервера:

Теперь у нас есть две таблицы, оптимизированные соответственно для ускорения запросов с фильтрацией по UserIDs и по URL:

​
Вариант 2: Materialized Views

Создайте materialized view на основе существующей таблицы.
Ответ выглядит так:
  • мы меняем порядок столбцов ключа в первичном ключе представления по сравнению с нашей исходной таблицей
  • materialized view опирается на неявно созданную таблицу, порядок строк и первичный индекс которой определяются заданным первичным ключом
  • неявно созданная таблица выводится запросом SHOW TABLES и имеет имя, начинающееся с .inner
  • также можно сначала явно создать базовую таблицу для materialized view, а затем представление сможет использовать эту таблицу через предложение TO [db].[table]
  • мы используем ключевое слово POPULATE, чтобы сразу заполнить неявно созданную таблицу всеми 8,87 миллиона строк из исходной таблицы hits_UserID_URL
  • если в исходную таблицу hits_UserID_URL вставляются новые строки, эти строки автоматически вставляются и в неявно созданную таблицу
  • Фактически неявно созданная таблица имеет тот же порядок строк и тот же первичный индекс, что и вторичная таблица, которую мы создали явно:
ClickHouse хранит файлы данных столбцов (.bin), файлы меток (.mrk2) и первичный индекс (primary.idx) неявно созданной таблицы в специальной папке внутри каталога данных сервера ClickHouse:
Неявно созданная таблица (и её первичный индекс), лежащая в основе materialized view, теперь может использоваться для значительного ускорения выполнения нашего запроса из примера с фильтрацией по столбцу URL:
Ответ:
Поскольку неявно созданная таблица (и её первичный индекс), лежащая в основе materialized view, фактически идентична вторичной таблице, которую мы создали явно, запрос выполняется по сути так же, как и для явно созданной таблицы. Соответствующий трассировочный лог в файле журнала сервера ClickHouse подтверждает, что ClickHouse выполняет двоичный поиск по меткам индекса:

​
Вариант 3: Проекции

Создайте проекцию для существующей таблицы:
Затем материализуйте проекцию:
  • проекция создает скрытую таблицу, порядок строк и первичный индекс которой определяются указанным предложением ORDER BY проекции
  • скрытая таблица не отображается запросом SHOW TABLES
  • мы используем ключевое слово MATERIALIZE, чтобы сразу заполнить скрытую таблицу всеми 8,87 миллиона строк из исходной таблицы hits_UserID_URL
  • если в исходную таблицу hits_UserID_URL вставляются новые строки, они также автоматически вставляются в скрытую таблицу
  • запрос всегда (синтаксически) обращен к исходной таблице hits_UserID_URL, но если порядок строк и первичный индекс скрытой таблицы позволяют выполнить запрос эффективнее, вместо исходной будет использована скрытая таблица
  • обратите внимание, что проекции не делают более эффективными запросы с ORDER BY, даже если ORDER BY совпадает с ORDER BY проекции (см. https://github.com/ClickHouse/ClickHouse/issues/47333)
  • фактически неявно созданная скрытая таблица имеет тот же порядок строк и тот же первичный индекс, что и вторичная таблица, которую мы явно создали:
ClickHouse хранит файлы данных столбцов (.bin), файлы меток (.mrk2) и первичный индекс (primary.idx) скрытой таблицы в специальной папке (выделена оранжевым на снимке экрана ниже) рядом с файлами данных, файлами меток и файлами первичного индекса исходной таблицы:
Скрытая таблица (и ее первичный индекс), созданная проекцией, теперь может (неявно) использоваться для значительного ускорения выполнения нашего примера запроса с фильтрацией по столбцу URL. Обратите внимание: синтаксически запрос обращен к исходной таблице проекции.
Результат:
Поскольку скрытая таблица (и её первичный индекс), созданная проекцией, фактически идентична вторичной таблице, которую мы создали явным образом, запрос выполняется практически так же, как и для таблицы, созданной явным образом. Соответствующий трассировочный лог в файле журнала сервера ClickHouse подтверждает, что ClickHouse выполняет двоичный поиск по меткам индекса:

​
Краткое резюме

Первичный индекс нашей таблицы с составным первичным ключом (UserID, URL) был очень полезен для ускорения запроса с фильтрацией по UserID. Но этот индекс почти не помогает ускорить запрос с фильтрацией по URL, хотя столбец URL входит в составной первичный ключ. И наоборот: Первичный индекс нашей таблицы с составным первичным ключом (URL, UserID) ускорял запрос с фильтрацией по URL, но почти не помогал запросу с фильтрацией по UserID. Из-за одинаково высокой мощности столбцов первичного ключа UserID и URL запрос с фильтрацией по второму столбцу ключа почти не выигрывает от включения второго столбца ключа в индекс. Поэтому имеет смысл удалить второй столбец ключа из первичного индекса (это уменьшит потребление памяти индексом) и вместо этого использовать несколько первичных индексов. Однако если столбцы ключа в составном первичном ключе сильно различаются по мощности, то для запросов полезно упорядочить столбцы первичного ключа по возрастанию мощности. Чем больше разница в мощности между столбцами ключа, тем важнее их порядок в ключе. Мы покажем это в следующем разделе.

​
Эффективный порядок столбцов ключа

В составном первичном ключе порядок столбцов ключа может существенно влиять как на:
  • эффективность фильтрации по вторичным столбцам ключа в запросах, так и на
  • коэффициент сжатия файлов данных таблицы.
Чтобы продемонстрировать это, мы будем использовать версию нашего набора примерных данных о веб-трафике, в котором каждая строка содержит три столбца, указывающих, был ли доступ интернет-‘пользователя’ (столбец UserID) к URL (столбец URL) помечен как трафик бота (столбец IsRobot). Мы будем использовать составной первичный ключ, включающий все три вышеупомянутых столбца. Его можно использовать для ускорения типичных запросов веб-аналитики, вычисляющих:
  • какая доля (в процентах) трафика к определённому URL приходится на ботов, или
  • насколько мы уверены, что конкретный пользователь является (или не является) ботом (какой процент трафика от этого пользователя считается трафиком бота или, наоборот, не считается)
Мы используем этот запрос, чтобы вычислить мощности трёх столбцов, которые хотим использовать в качестве столбцов ключа в составном первичном ключе (обратите внимание, что мы используем табличную функцию URL, чтобы выполнять специальные запросы к TSV-данным без необходимости создавать локальную таблицу). Выполните этот запрос в clickhouse client:
Ответ:
Мы видим, что мощность столбцов сильно различается, особенно у столбцов URL и IsRobot, поэтому порядок этих столбцов в составном первичном ключе важен как для эффективного ускорения запросов с фильтрацией по этим столбцам, так и для достижения оптимальных коэффициентов сжатия файлов с данными столбцов таблицы. Чтобы продемонстрировать это, мы создадим две версии таблицы для наших данных анализа бот-трафика:
  • таблицу hits_URL_UserID_IsRobot с составным первичным ключом (URL, UserID, IsRobot), где столбцы ключа упорядочены по мощности в порядке убывания
  • таблицу hits_IsRobot_UserID_URL с составным первичным ключом (IsRobot, UserID, URL), где столбцы ключа упорядочены по мощности в порядке возрастания
Создайте таблицу hits_URL_UserID_IsRobot с составным первичным ключом (URL, UserID, IsRobot):
И заполните её 8,87 млн строк:
Вот ответ:
Затем создайте таблицу hits_IsRobot_UserID_URL с составным первичным ключом (IsRobot, UserID, URL):
И заполните её теми же 8,87 миллиона строк, что и предыдущую таблицу:
Ответ:

​
Эффективная фильтрация по вторичным столбцам ключа

Когда запрос фильтрует хотя бы по одному столбцу, входящему в состав составного ключа, и этот столбец является первым столбцом ключа, ClickHouse использует алгоритм двоичного поиска по меткам индекса этого столбца ключа. Когда запрос фильтрует (только) по столбцу, входящему в состав составного ключа, но не являющемуся первым столбцом ключа, ClickHouse использует универсальный алгоритм поиска с исключением по меткам индекса этого столбца ключа. Во втором случае порядок столбцов ключа в составном первичном ключе важен для эффективности универсального алгоритма поиска с исключением. Ниже показан запрос, фильтрующий по столбцу UserID таблицы, в которой мы упорядочили столбцы ключа (URL, UserID, IsRobot) по мощности в порядке убывания:
Ответ:
Это тот же запрос к таблице, в котором мы упорядочили столбцы ключа (IsRobot, UserID, URL) по мощности в порядке возрастания:
Ответ:
Мы видим, что запрос выполняется значительно эффективнее и быстрее для таблицы, в которой столбцы ключа упорядочены по возрастанию мощности. Это связано с тем, что универсальный алгоритм поиска с исключением работает наиболее эффективно, когда гранулы выбираются по вторичному столбцу ключа, а предшествующий ему столбец ключа имеет меньшую мощность. Мы подробно показали это в предыдущем разделе этого руководства.

​
Оптимальный коэффициент сжатия файлов данных

Этот запрос сравнивает коэффициент сжатия для столбца UserID в двух таблицах, которые мы создали выше:
Вот ответ:
Мы видим, что коэффициент сжатия для столбца UserID значительно выше у таблицы, в которой столбцы ключа (IsRobot, UserID, URL) упорядочены по мощности по возрастанию. Хотя в обеих таблицах хранится абсолютно одинаковый набор данных (мы вставили в обе таблицы одни и те же 8,87 миллиона строк), порядок столбцов ключа в составном первичном ключе существенно влияет на то, сколько места на диске занимают сжатые файлы данных столбцов таблицы:
  • в таблице hits_URL_UserID_IsRobot с составным первичным ключом (URL, UserID, IsRobot), где столбцы ключа упорядочены по мощности по убыванию, файл данных UserID.bin занимает 11.24 MiB места на диске
  • в таблице hits_IsRobot_UserID_URL с составным первичным ключом (IsRobot, UserID, URL), где столбцы ключа упорядочены по мощности по возрастанию, файл данных UserID.bin занимает всего 877.47 KiB места на диске
Хороший коэффициент сжатия данных столбца таблицы на диске не только экономит место, но и ускоряет запросы (особенно аналитические), которым требуется чтение данных из этого столбца, поскольку для перемещения данных столбца с диска в оперативную память (файловый кеш операционной системы) требуется меньше операций ввода-вывода. Ниже мы покажем, почему для коэффициента сжатия столбцов таблицы выгодно упорядочивать столбцы первичного ключа по мощности по возрастанию. На диаграмме ниже схематично показан порядок строк на диске для первичного ключа, в котором столбцы ключа упорядочены по мощности по возрастанию: Мы уже обсуждали, что данные строк таблицы хранятся на диске в порядке столбцов первичного ключа. На приведённой выше диаграмме строки таблицы (то есть значения их столбцов на диске) сначала упорядочены по значению cl, а строки с одинаковым значением cl — по значению ch. И поскольку первый столбец ключа cl имеет низкую мощность, велика вероятность, что существуют строки с одинаковым значением cl. А значит, велика и вероятность того, что значения ch окажутся упорядоченными (локально — для строк с одинаковым значением cl). Если в столбце похожие данные расположены близко друг к другу, например благодаря сортировке, такие данные будут сжиматься лучше. В целом алгоритм сжатия выигрывает от длины последовательностей данных (чем больше данных он видит, тем лучше для сжатия) и локальности (чем более похожи данные, тем выше коэффициент сжатия). В отличие от диаграммы выше, на диаграмме ниже схематично показан порядок строк на диске для первичного ключа, в котором столбцы ключа упорядочены по мощности по убыванию: Теперь строки таблицы сначала упорядочиваются по значению ch, а строки с одинаковым значением ch — по значению cl. Но поскольку первый ключевой столбец ch имеет высокую мощность, строки с одинаковым значением ch встречаются редко. А значит, значения cl тоже вряд ли будут упорядочены (локально — в пределах строк с одинаковым значением ch). Поэтому значения cl, скорее всего, расположены в случайном порядке и, соответственно, имеют низкую локальность и плохой коэффициент сжатия.

​
Кратко

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

​
Эффективная идентификация отдельных строк

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

​
Конкретный пример

Один из наглядных примеров — сервис для публикации открытого текста https://pastila.nl, который разработал Alexey Milovidov и о котором он написал в блоге. При каждом изменении в текстовом поле данные автоматически сохраняются в строку таблицы ClickHouse (одна строка на каждое изменение). Один из способов идентифицировать и получить (конкретную версию) вставленного содержимого — использовать хеш содержимого в качестве UUID строки таблицы, в которой хранится это содержимое. Следующая диаграмма показывает
  • порядок вставки строк при изменении содержимого (например, из-за нажатий клавиш при вводе текста в текстовое поле) и
  • порядок данных на диске из вставленных строк при использовании PRIMARY KEY (hash):
Поскольку столбец hash используется в качестве столбца первичного ключа,
  • конкретные строки можно получать очень быстро, но
  • строки таблицы (то есть их данные в столбцах) хранятся на диске в порядке возрастания (уникальных и случайных) значений hash. Поэтому значения столбца content тоже хранятся в случайном порядке, без локальности данных, что приводит к неоптимальному коэффициенту сжатия файла данных столбца content.
Чтобы значительно улучшить коэффициент сжатия для столбца content и при этом сохранить быстрое получение конкретных строк, pastila.nl использует два хеша (и составной первичный ключ) для идентификации конкретной строки:
  • хеш содержимого, как обсуждалось выше, который различается для разных данных, и
  • locality-sensitive hash (fingerprint), который не меняется при небольших изменениях данных.
Следующая диаграмма показывает
  • порядок вставки строк при изменении содержимого (например, из-за нажатий клавиш при вводе текста в текстовое поле) и
  • порядок данных на диске из вставленных строк при использовании составного PRIMARY KEY (fingerprint, hash):
Теперь строки на диске сначала упорядочиваются по fingerprint, а для строк с одинаковым значением fingerprint итоговый порядок определяется значением hash. Поскольку данные, различающиеся лишь незначительно, получают одно и то же значение fingerprint, похожие данные теперь хранятся на диске рядом друг с другом в столбце content. А это очень хорошо влияет на коэффициент сжатия столбца content, поскольку алгоритмы сжатия в целом выигрывают от локальности данных (чем больше данные похожи, тем выше коэффициент сжатия). Компромисс заключается в том, что для получения конкретной строки требуются два поля (fingerprint и hash), чтобы оптимально использовать первичный индекс, который получается из составного PRIMARY KEY (fingerprint, hash).
Последнее изменение 12 июня 2026 г.