Советы и приёмы по оптимизации базовых типов данных в ClickHouse

Вопрос

Какие типы данных следует использовать в ClickHouse, чтобы оптимизировать запросы с точки зрения скорости и объёма хранения?

Ответ

При автоматической миграции из другой системы или при выборе типа данных пользователи нередко руководствуются принципами «чем больше — тем лучше», «выбери что попроще» или «выбери наиболее универсальный вариант». Для небольших датасетов объёмом в миллионы, а то и миллиарды строк такой подход, скорее всего, сработает. В сценариях, где разница в производительности запросов невелика, это может быть незаметно и вполне допустимо. Однако по мере роста объёма данных это станет ощутимой проблемой. Разница между запросом, выполняющимся за 50 мс и за 500 мс, может быть приемлемой для большинства сценариев использования — например, в веб-интерфейсе — однако один из них в 10 раз медленнее другого, пусть для конечного пользователя это и практически незаметно. Пример исходной таблицы:

timestamp Datetime64(9),
group_id Int64,
vendor_id String,
product_id String,
category1 Int64,
code_name String,
paid_status String,
country_code String,
description String,
price Float64,
attributes Map(String, String)

Пример данных:

3456, 0123456789, bd6087b7-6026-4974-9122-bc99faae5d84, "2024-03-01 01:00:01.000", 98, "bear", paid", "us", "corvette model car", 123.45, {"color" : "blue", "size" : "S"}
156, 0000012345, bd6087b7-6026-4974-9122-bc99faae5d84, "2024-03-01 01:00:02:123", 45, "tiger", "not paid", "uk", "electric car", 53432.10, {"color" : "red", "model" : "X"} 
...

Ниже приведены рекомендации по оптимизации этих данных: timestamp : DateTime64(9) Если не требуется научная точность, значение точности 9 (наносекунды) вряд ли оправдано. Возможно, оно имеет смысл для отображения или сортировки, но, как правило, не для поиска в запросах, первичных ключей и т. д.

Рекомендация: Для PK — сортировка по: DateTime Для отображения или сортировки добавьте дополнительный столбец, то есть timestamp_microseconds : DateTime64(6)

group_id : Int64 Это целочисленный тип — выберите наименьший целочисленный тип, достаточный для хранения максимального значения в данном столбце. Судя по выборке данных и названию столбца, вряд ли потребуется хранить квинтиллион значений; скорее всего, подойдёт Int16, который поддерживает до 16 тысяч значений.

Рекомендуется: Int16

vendor_id : String Этот столбец похож на число, но содержит ведущие нули — форматирование, по всей видимости, важно сохранить. Также судя по всему имеет фиксированное количество символов.

Рекомендуется: FixedString(10)

product_id : String Это поле буквенно-цифровое, поэтому интуитивно кажется строкой, однако на самом деле является UUID.

Рекомендуется: UUID

category1 : Int64 Значения небольшие, категорий немного, и их количество вряд ли будет существенно расти или оно ограничено. Менее 255

Рекомендуется: UInt8

code_name : String Похоже, что это поле может принимать лишь ограниченный набор строковых значений. В подобных ситуациях, когда количество уникальных строковых значений может исчисляться сотнями или тысячами, рекомендуется использовать поля с низкой мощностью (low cardinality).

Рекомендуется: LowCardinality(String)

paid_status : String Здесь используется строковое значение «paid» или «not_paid». Если возможны только два значения, используйте булев тип.

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

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

Рекомендуется: LowCardinality(FixedString(2))

price : Float64 Числа с плавающей точкой не рекомендуется использовать при известной фиксированной точности, особенно для финансовых данных и вычислений. Лучше использовать типы Decimal с нужной точностью. В данном случае цена товара, скорее всего, не превышает 999 999,00.

Рекомендуемый тип: Decimal(10,2)

attributes : map Нередко встречаются таблицы с динамическими атрибутами, хранящимися в map-значениях. Поиск по ключам или значениям в таких структурах, как правило, выполняется медленнее. Существует несколько способов ускорить работу с map-значениями. Если определённые ключи присутствуют в большинстве записей, лучше вынести их в отдельный столбец с низкой мощностью, а редко встречающиеся (разреженные) ключи — в другой столбец с высокой мощностью. Затем можно создать индексы пропуска данных, что повысит эффективность, хотя и усложнит запросы.

Рекомендация: lc_attributes: Map(String, String), hc_attributes: Map(String, String).

В зависимости от характера запросов также можно использовать следующие варианты, чтобы создать индекс пропуска данных и/или извлечь атрибуты: Использование Array Join для извлечения данных в столбцы с помощью materialized view: https://clickhouse.com/docs/knowledgebase/using-array-join-to-extract-and-query-attributes Использование индекса пропуска данных для ключей: https://clickhouse.com/docs/knowledgebase/improve-map-performance

​Вопрос

​Ответ

Вопрос

Ответ