Практическое введение в первичные индексы в ClickHouse

Введение

В этом руководстве мы углубимся в индексацию в ClickHouse. Мы подробно покажем и обсудим:

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

Вы можете самостоятельно выполнить все SQL-операторы и запросы ClickHouse, приведенные в этом руководстве, на своём компьютере. Для установки ClickHouse и инструкций по началу работы смотрите Quick Start.

примечание

Это руководство сосредоточено на разреженных первичных индексах ClickHouse.

Для вторичных индексов пропуска данных в ClickHouse, смотрите Tutorial.

Набор данных

Всё это руководство мы будем использовать пример анонимизированного набора данных о веб-трафике.

• Мы будем использовать подмножество из 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-оператора вставки. Это использует табличную функцию URL для загрузки подмножества полного набора данных, размещённого удалённо на clickhouse.com:

Ответ:

Результаты выполнения ClickHouse показывают, что приведённый выше оператор вставил 8.87 миллионов строк в таблицу.

Наконец, чтобы упростить дискуссии, которые будут рассматриваться в этом руководстве, и сделать диаграммы и результаты воспроизводимыми, мы оптимизируем таблицу, используя ключевое слово FINAL:

примечание

В общем случае, не требуется и не рекомендуется немедленно оптимизировать таблицу после загрузки данных в неё. Почему это необходимо в данном примере, станет очевидно.

Теперь мы выполняем наш первый веб-аналитический запрос. Следующий запрос вычисляет топ-10 самых кликаемых URL для интернет-пользователя с UserID 749927693:

Ответ:

Результаты выполнения ClickHouse показывают, что ClickHouse выполнил полное сканирование таблицы! Каждая отдельная строка из 8.87 миллионов строк нашей таблицы была загружена в ClickHouse, что не масштабируется.

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

Связанные материалы

• Блог: Суперускорение ваших запросов ClickHouse

Дизайн индексов ClickHouse

Дизайн индекса для работы с огромными объёмами данных

В традиционных системах управления реляционными базами данных в первичном индексе содержится одна запись на каждую строку таблицы. В результате первичный индекс будет содержать 8.87 миллионов записей для нашего набора данных. Такой индекс позволяет быстро находить определённые строки, обеспечивая высокую эффективность для запросов на поиск и точечные обновления. Поиск записи в структуре данных B(+)-Tree имеет среднюю временную сложность O(log n); более точно, log_b n = log_2 n / log_2 b, где b — это коэффициент разветвления B(+)-Tree, а n — количество индексированных строк. Поскольку b обычно находится в пределах от нескольких сотен до нескольких тысяч, B(+)-Trees являются очень плоскими структурами, и требуется немного дисковых операций для поиска записей. При 8.87 миллионах строк и коэффициенте разветвления 1000 в среднем потребуется 2.3 дисковых обращения. Эта возможность достигается за счёт дополнительных накладных расходов на диск и память, более высоких затрат на вставку при добавлении новых строк в таблицу и записей в индекс, а иногда и ребалансировки B-дерева.

Учитывая сложности, связанные с индексами B-дерева, движки таблиц в ClickHouse используют другой подход. Семейство движков MergeTree в ClickHouse было разработано и оптимизировано для работы с огромными объёмами данных. Эти таблицы предназначены для приёма миллионов вставок строк в секунду и хранения очень больших объёмов данных (сотни петабайт). Данные быстро записываются в таблицу частями, с применением правил для фоновым слияний частей. В ClickHouse каждая часть имеет свой собственный первичный индекс. Когда части сливаются, также сливаются и их первичные индексы. На очень большом масштабе, для которого рассчитан ClickHouse, крайне важно быть очень эффективным с точки зрения использования диска и памяти. Поэтому вместо индексирования каждой строки, первичный индекс для части имеет одну запись индекса (известную как 'метка') на группу строк (называемую 'гранула') - эта техника называется разреженный индекс.

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

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

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

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

Детали оператора DDL

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

• Указывает составной ключ сортировки для таблицы через оператор ORDER BY.

• Явно контролирует, сколько записей индекса будет иметь первичный индекс через следующие настройки:

  • index_granularity: явно установлен на его значение по умолчанию, равное 8192. Это означает, что для каждой группы из 8192 строк, первичный индекс будет иметь одну запись индекса. Например, если в таблице содержится 16384 строки, индекс будет иметь две записи индекса.

  • index_granularity_bytes: установлен в 0 для отключения адаптивной гранулярности индекса. Адаптивная гранулярность индекса означает, что ClickHouse автоматически создаёт одну запись индекса для группы из n строк, если выполняется одно из следующих условий:

    • Если n меньше 8192, и размер объединённых данных строк для этих n строк больше или равен 10 MB (значение по умолчанию для index_granularity_bytes).

    • Если размер объединённых данных строк для n строк меньше 10 MB, но n равно 8192.

  • compress_primary_key: установлен в 0 для отключения сжатия первичного индекса. Это позволит нам по желанию инспектировать его содержимое позже.

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

Далее вставьте данные:

Ответ выглядит так:

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

Мы можем использовать следующий запрос для получения метаданных о нашей таблице:

Ответ:

Вывод клиента ClickHouse показывает:

• Данные таблицы хранятся в широком формате в определённом каталоге на диске, что означает, что в этом каталоге будет один файл данных (и один файл меток) на столбец таблицы.
• В таблице 8.87 миллионов строк.
• Нестиснутый размер данных всех строк составляет 733.28 МБ.
• Сжатый размер на диске всех строк составляет 206.94 МБ.
• У таблицы есть первичный индекс с 1083 записями (называемыми 'метки'), и размер индекса составляет 96.93 КБ.
• В общей сложности данные таблицы, файлы меток и файл первичного индекса занимают 207.07 МБ на диске.

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

Наша таблица, которую мы создали выше, имеет

• составной первичный ключ (UserID, URL) и
• составной ключ сортировки (UserID, URL, EventTime).

примечание

• Если бы мы указали только ключ сортировки, то первичный ключ был бы неявно определён как равный ключу сортировки.

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

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

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

Вставленные строки хранятся на диске в лексикографическом (по возрастанию) порядке по столбцам первичного ключа (и дополнительному столбцу 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 всегда читает (в потоковом режиме и параллельно) целую группу (гранулу) строк. :::note Значения столбцов не хранятся физически внутри гранул: гранулы являются лишь логической организацией значений столбцов для выполнения запросов. :::

Следующая диаграмма показывает, как (значения столбцов) 8.87 миллиона строк нашей таблицы организованы в 1083 гранулы, в результате указания настроек index_granularity (установленной на значение по умолчанию 8192) в DDL-операторе таблицы.

Первые (на основе физического порядка на диске) 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 теперь показывает, что вместо полного сканирования таблицы только 8.19 тысяч строк было передано в ClickHouse.

Если включено трассировочное логирование, то файл журнала сервера ClickHouse показывает, что ClickHouse выполнял бинарный поиск по 1083 меткам индекса UserID, чтобы выявить гранулы, которые могут содержать строки со значением столбца UserID 749927693. Это требует 19 шагов со средним временем выполнения O(log2 n):

Мы видим в приведённом выше журнале трассировки, что одной метки из 1083 существующих меток оказалось достаточно для выполнения запроса.

Подробности логирования трассировки

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

Мы также можем воспроизвести это, используя оператор EXPLAIN в нашем примерном запросе:

Результат такой:

Результат для клиента показывает, что одна из 1083 гранул была выбрана как потенциально содержащая строки со значением столбца UserID 749927693.

Заключение

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

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

Это первая стадия (выбор гранул) выполнения запроса в ClickHouse.

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

Мы обсудим эту вторую стадию более подробно в следующем разделе.

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

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

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

Подробности выбора гранул

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

Чтобы подтвердить (или опровергнуть), что в грануле 176 есть строки со значением столбца UserID 749.927.693, все 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 в пределах несжатых данных.

ClickHouse нужно найти (и передать все значения из) гранулу 176 из файла данных UserID.bin и файла данных URL.bin для выполнения нашего примерного запроса (топ 10 самых кликабельных URL для интернет-пользователя с UserID 749.927.693).

Диаграмма выше показывает, как ClickHouse определяет местоположение гранулы для файла данных UserID.bin.

Параллельно ClickHouse делает то же самое для гранулы 176 для файла данных URL.bin. Две соответствующие гранулы выровнены и передаются в движок ClickHouse для дальнейшей обработки, т.е. агрегации и подсчета значений URL на группу для всех строк, где UserID равен 749.927.693, прежде чем, наконец, будет выведено 10 крупнейших групп URL в порядке убывания счёта.

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

ВТОРИЧНЫЕ столбцы ключей могут (быть) неэффективны

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

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

примечание

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

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

Мы используем запрос, который вычисляет топ-10 пользователей, которые чаще всего кликали на URL "http://public_search":

Ответ:

Вывод клиента указывает на то, что ClickHouse практически выполнял полное сканирование таблицы, несмотря на то, что столбец URL является частью составного первичного ключа! ClickHouse читает 8.81 миллиона строк из 8.87 миллионов строк таблицы.

Если включено trace_logging, то файл журнала сервера ClickHouse показывает, что ClickHouse использовал поиск с исключениями по 1083 меткам индекса URL, чтобы выявить гранулы, которые могут содержать строки со значением столбца URL "http://public_search":

Мы видим в приведённом выше журнале трассировки, что 1076 (через метки) из 1083 гранул были выбраны как потенциально содержащие строки с соответствующим значением URL.

Это приводит к тому, что 8.81 миллиона строк передаются в движок ClickHouse (параллельно с использованием 10 потоков), чтобы выявить строки, которые действительно содержат значение URL "http://public_search".

Однако, как мы увидим позже, только 39 гранул из выбранных 1076 гранул действительно содержат совпадающие строки.

Хотя первичный индекс на основе составного первичного ключа (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:

Первый элемент индекса (‘метка 0’ на диаграмме выше) хранит минимальные и максимальные значения URL для строк, относящихся к первым 4 гранулам нашей таблицы.

Второй элемент индекса (‘метка 1’) хранит минимальные и максимальные значения URL для строк, относящихся к следующим 4 гранулам нашей таблицы, и так далее.

(ClickHouse также создал специальный файл меток для индекса пропуска данных для определения местоположения групп гранул, связанных с индексными метками.)

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

Конкретное значение URL, которое ищет запрос (например, 'http://public_search'), очень вероятно находится между минимальным и максимальным значением, хранящимся в индексе для каждой группы гранул, в результате чего ClickHouse вынужден выбирать группу гранул (поскольку они могут содержать строку(и), соответствующую запросу).

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

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

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

Ниже представлены способы достижения этого.

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

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

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

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

Однако эти три варианта различаются тем, насколько прозрачно для пользователя является эта дополнительная таблица с точки зрения маршрутизации запросов и вставок.

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

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

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

Далее мы более подробно обсуждаем эти три варианта создания и использования множества первичных индексов с реальными примерами.

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

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

Вставьте все 8,87 миллиона строк из нашей оригинальной таблицы в дополнительную таблицу:

Ответ выглядит следующим образом:

И, наконец, оптимизируйте таблицу:

Поскольку мы изменили порядок столбцов в первичном ключе, вставленные строки теперь хранятся на диске в другом лексикографическом порядке (по сравнению с нашей оригинальной таблицей), и поэтому также 1083 гранулы этой таблицы содержат другие значения, чем ранее:

Это результирующий первичный ключ:

Что теперь можно использовать для значительного ускорения выполнения нашего примера запроса, фильтрующего по столбцу URL, чтобы вычислить топ-10 пользователей, которые чаще всего щелкали по URL "http://public_search":

Ответ:

Теперь, вместо того чтобы почти выполнять полное сканирование таблицы, ClickHouse выполняет этот запрос намного более эффективно.

С первичным индексом из оригинальной таблицы, где UserID был первым, а URL — вторым ключевым столбцом, ClickHouse использовал алгоритм поиска исключений по меткам индекса для выполнения этого запроса, и это было не очень эффективно из-за схожей высокой кардинальности UserID и URL.

С URL в качестве первого столбца в первичном индексе ClickHouse теперь выполняет бинарный поиск по индексным меткам. Соответствующий журнал трассировки в файле журнала сервера ClickHouse подтверждает это:

ClickHouse выбрал всего 39 меток индекса, вместо 1076, когда использовался поиск исключений.

Обратите внимание, что дополнительная таблица оптимизирована для ускорения выполнения нашего примера запроса, фильтрующего по URL.

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

Запрос, фильтрующий по UserID, теперь имеет плохую производительность

Ответ:

Журнал сервера:

Теперь у нас есть две таблицы, оптимизированные для ускорения запросов, фильтрующих по UserID, и ускорения запросов, фильтрующих по URL, соответственно.

Вариант 2: Материализованные представления

Создайте материализованное представление на нашей текущей таблице.

Ответ выглядит следующим образом:

примечание

• мы меняем порядок ключевых столбцов (по сравнению с нашей оригинальной таблицей) в первичном ключе представления
• материализованное представление поддерживается неявно созданной таблицей, чей порядок строк и первичный индекс основаны на заданном определении первичного ключа
• неявно созданная таблица отображается с помощью запроса SHOW TABLES и имеет имя, начинающееся с .inner
• также возможно сначала явно создать базовую таблицу для материализованного представления, а затем представление может быть нацелено на эту таблицу с помощью оператора TO [db].[table]
• мы используем ключевое слово POPULATE, чтобы сразу же заполнить неявно созданную таблицу всеми 8,87 миллионами строк из исходной таблицы hits_UserID_URL
• если в исходную таблицу hits_UserID_URL вставляются новые строки, то эти строки также автоматически вставляются в неявно созданную таблицу
• Эффективно, неявно созданная таблица имеет тот же порядок строк и первичный индекс, что и вторичная таблица, которую мы создали явно:

ClickHouse хранит файлы данных столбцов (.bin), файлы меток (.mrk2) и первичный индекс (primary.idx) неявно созданной таблицы в специальной папке внутри директории данных сервера ClickHouse:

Неявно созданная таблица (и ее первичный индекс), поддерживающая материализованное представление, теперь может быть использована для значительного ускорения выполнения нашего примера запроса, фильтрующего по столбцу URL:

Ответ:

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

Соответствующий журнал трассировки в файле журнала сервера 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, запрос, фильтрующий по второму ключевому столбцу, не дает большого преимущества от его наличия в индексе.

Поэтому имеет смысл удалить второй ключевой столбец из первичного индекса (что приводит к меньшему использованию памяти индексом) и использовать несколько первичных индексов вместо этого.

Однако, если ключевые столбцы в составном первичном ключе имеют большие различия в кардинальности, то для запросов полезно упорядочивать столбцы первичного ключа по возрастанию кардинальности.

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

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

В составном первичном ключе порядок ключевых столбцов может значительно влиять на обоих аспекта:

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

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

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

• сколько (в процентах) трафика на определенный URL поступает от роботов или
• насколько мы уверены, что конкретный пользователь (не) является роботом (какой процент трафика от этого пользователя предполагается (не) является трафиком от роботов)

Мы используем этот запрос для вычисления кардинальности трех столбцов, которые мы хотим использовать в качестве ключевых столбцов в сложном первичном ключе (обратите внимание, что мы используем табличную функцию URL для запроса к данным TSV ad hoc без необходимости создания локальной таблицы). Выполните этот запрос в 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, скорее всего, находятся в случайном порядке и, следовательно, имеют плохую локальность и соотношение сжатия соответственно.

Резюме

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

Сопутствующее содержание

• Блог: Super charging your ClickHouse queries

Эффективное определение отдельных строк

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

Интуитивное решение этой задачи может заключаться в использовании столбца UUID с уникальным значением на строку и для быстрого извлечения строк использовать этот столбец в качестве столбца первичного ключа.

Для самого быстрого извлечения столбец UUID должен быть первым столбцом ключа.

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

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

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

Один конкретный пример — это сервис текстовых записей https://pastila.nl, который разработал Алексей Миловидов и рассказал о этом в блоге.

При каждом изменении текстовой области данные автоматически сохраняются в строке таблицы ClickHouse (одна строка на изменение).

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

Следующая диаграмма показывает:

• порядок вставки строк при изменении контента (например, из-за нажатий клавиш при вводе текста в текстовую область) и
• порядок данных на диске из вставленных строк при использовании PRIMARY KEY (hash):

Поскольку столбец hash используется как столбец первичного ключа

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

Для того чтобы значительно улучшить коэффициент сжатия для столбца контента, при этом все ещё достигнув быстрого извлечения конкретных строк, pastila.nl использует два хеша (и составной первичный ключ) для идентификации конкретной строки:

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

Следующая диаграмма показывает:

• порядок вставки строк при изменении контента (например, из-за нажатий клавиш при вводе текста в текстовую область) и
• порядок данных на диске из вставленных строк при использовании составного PRIMARY KEY (fingerprint, hash):

Теперь строки на диске сначала упорядочены по fingerprint, а для строк с одинаковым значением fingerprint, их значение hash определяет окончательный порядок.

Так как данные, которые отличаются только небольшими изменениями, получают одинаковое значение fingerprint, схожие данные теперь хранятся на диске близко друг к другу в столбце контента. И это очень хорошо для коэффициента сжатия столбца контента, так как алгоритм сжатия в общем выигрывает от локальности данных (чем более схожие данные, тем лучше коэффициент сжатия).

Компромисс заключается в том, что для извлечения конкретной строки требуется два поля (fingerprint и hash) для оптимального использования первичного индекса, который образуется от составного PRIMARY KEY (fingerprint, hash).