nyc_taxi.trips_small_inferred. Чтобы выполнить их без изменений, создайте и заполните таблицу, если ещё не сделали этого:
Настройка демонстрационного набора данных
Настройка демонстрационного набора данных
Размер исходного файла Parquet составляет примерно 5,8 ГБ. Его загрузка может занять несколько минут в зависимости от скорости сети и доступных ресурсов.
Если собранные данные не соответствуют ни одной из этих категорий, вернитесь к плану запроса, а не пытайтесь искусственно подогнать запрос под один из подходов.
- Используйте, когда: запрос читает широкие столбцы или ненужные ему столбцы.
- Изменение: сократите размер или количество столбцов, считываемых запросом.
- Проверка: сравните
read_bytes, использование памяти и длительность при одинаковых условиях.
String, а также выбирайте наименьший знаковый или беззнаковый числовой тип, который безопасно покрывает ожидаемый диапазон. Для временных столбцов используйте Date или DateTime, если только вам не нужен более широкий диапазон или дробная точность Date32 либо DateTime64.
Используйте столбцы с типом Nullable обоснованно
Столбец Nullable помимо значений хранит отдельную маску NULL, которую ClickHouse также должен читать и обрабатывать. Используйте его, если различие между значением NULL и значением типа по умолчанию существенно. Если столбец гарантированно содержит значение, тип без Nullable позволяет избежать этой дополнительной обработки.
Перед изменением столбца проверьте исходные данные и путь ингестии, а не исходите из того, что наблюдаемые данные без NULL всегда будут оставаться такими. Практический пример оптимизации показывает, как определить столбцы, содержащие значения NULL, и измерить влияние изменения схемы.
Используйте кодирование с использованием словаря для повторяющихся значений
LowCardinality использует кодирование с использованием словаря и часто эффективно для строковых столбцов, например со значениями status, кодами стран или другими измерениями, в которых уникальных значений значительно меньше, чем строк. Около 10 000 уникальных значений — полезная отправная точка для поиска кандидатов, а не жёсткий предел. Избегайте идентификаторов и других преимущественно уникальных столбцов и сравнивайте показатели до и после изменения типа.
Более подробные рекомендации см. в разделе Выбор типов данных.
Поскольку ClickHouse хранит данные по столбцам, выбор меньшего количества столбцов напрямую сокращает объём считываемых данных. Перечисляйте необходимые столбцы вместо SELECT *, особенно при работе с широкими таблицами или запросами, которые возвращают лишь небольшую часть данных из каждой строки.
Используйте read_bytes из system.query_log, чтобы сравнить объём данных, считанных до и после сокращения списка выбранных столбцов. Если значение read_bytes остаётся высоким, проверьте план запроса на наличие выражений, фильтров, JOIN или вложенных запросов, которым по-прежнему требуются дополнительные столбцы.
Например, если для панели мониторинга нужны только время посадки, тип оплаты и общая сумма, выбирайте эти столбцы, а не всю строку:
SELECT *. Число возвращённых строк не изменится, однако read_bytes должен отражать меньший объём данных, считанных из столбцов.
- Используйте, когда: селективный фильтр по-прежнему считывает много частей или гранул.
- Изменение: Согласуйте физическую структуру данных с фильтрами, используемыми в регулярно выполняемых запросах.
- Проверьте: Сравните части и гранулы, выбранные
EXPLAIN indexes = 1, затем проверьтеread_rows,read_bytesи длительность.
MergeTree ключ сортировки определяет порядок размещения строк на диске. По умолчанию он также служит первичным ключом, на основе которого строится разреженный первичный индекс. В отличие от первичного ключа в OLTP-базе данных, первичный ключ ClickHouse не обеспечивает уникальность. Его преимущество в производительности заключается в том, что он позволяет ClickHouse пропускать гранулы, не соответствующие фильтрам запроса.
Отдавайте приоритет столбцам, которые часто используются в селективных фильтрах, и учитывайте их порядок в ключе. Группировка связанных значений также может улучшить сжатие. Если порядок группировки или сортировки в запросе соответствует ключу, ClickHouse может использовать оптимизации обработки данных в порядке сортировки для GROUP BY или ORDER BY.
Сравните части и гранулы, выбранные EXPLAIN indexes = 1, до и после тестирования другого ключа сортировки. Также сравните read_rows, read_bytes и длительность при одинаковых условиях. Подробные рекомендации см. в разделе Выбор первичного ключа.
В таблице примера используется ORDER BY (), поэтому следующий селективный фильтр по дате не может исключать гранулы с помощью ключа сортировки:
В ClickHouse 25.9 и более поздних версиях эти настройки обеспечивают, что
EXPLAIN отображает используемые индексы, а также исключаемые ими части и гранулы.pickup_datetime, а затем выполните для неё тот же EXPLAIN. В разделе первичного ключа плана должно отображаться меньше выбранных гранул, прежде чем оценивать общее изменение по длительности или использованию памяти.
Если ключ сортировки не позволяет эффективно поддерживать важный шаблон доступа, рассмотрите следующие более специализированные варианты.
Партиционирование для управления данными и отсечения
Партиционирование — это прежде всего механизм управления данными для таких операций, как хранение, перемещение и удаление. Оно может сократить объем работы при выполнении запроса, если фильтры позволяют ClickHouse исключать целые партиции, однако не должно быть основным способом ускорения запросов.
Например, месячные партиции позволяют удалять данные за целые месяцы, если срок хранения также задается по месяцам. Используйте партиционирование, только если ключ партиционирования соответствует требованиям к жизненному циклу данных или хорошо изученному шаблону доступа. Поддерживайте низкую мощность ключа: ключ высокой мощности создает множество частей, которые нельзя объединять между партициями, и может снизить производительность. Используйте EXPLAIN indexes = 1, чтобы убедиться, что запрос действительно отсекает партиции.
Добавьте индекс пропуска данных для локализованного фильтра
Индекс пропуска данных хранит метаданные, позволяющие ClickHouse не читать блоки, которые заведомо не соответствуют фильтру. Он наиболее полезен, когда ключ сортировки не поддерживает важный фильтр, а совпадающие значения достаточно локализованы внутри блоков.
Например, индекс bloom filter может помочь при поиске по равенству, когда в большинстве блоков отсутствует искомое значение. Используйте индексы пропуска данных после анализа типов данных и ключа сортировки. Индекс, который редко позволяет исключить блок, увеличивает затраты на хранение и вычисления, почти не сокращая объем работы. Протестируйте тип индекса и гранулярность на репрезентативных данных, затем используйте EXPLAIN indexes = 1, чтобы сравнить выбранные гранулы и проверить read_rows, read_bytes и длительность.
Используйте проекции избирательно
Проекции хранят альтернативные структуры данных вместе с таблицей. Они могут предоставлять другой ключ сортировки или предварительно вычисленный результат, а ClickHouse может выбрать подходящую проекцию без необходимости явно указывать ее в запросе.
Например, проекция с сортировкой по payment_type может поддерживать регулярно используемый фильтр, который не поддерживает сортировка базовой таблицы. Используйте небольшое число проекций для важных шаблонов доступа, которые базовая сортировка не может эффективно обслуживать.
Проекции хранят дополнительные индексные данные или данные столбцов и требуют дополнительной работы при вставке и слиянии; проекция по всем столбцам дублирует хранимые в ней столбцы. Активное использование проекций также может увеличить объем работы, необходимый для выбора оптимальной проекции во время выполнения запроса. Для крупных развертываний со множеством различных шаблонов доступа обычно проще эксплуатировать меньшее число проекций или отдельные специализированные таблицы. При выборе между этими механизмами см. Materialized views versus projections.
Добавьте альтернативную сортировку для запросов, фильтрующих по типу платежа и времени посадки, продолжая обращаться к исходной таблице:
EXPLAIN projections = 1 убедитесь, что ClickHouse выбирает проекцию и считывает меньше строк или байтов. Прежде чем широко применять этот подход, также оцените дополнительные затраты на вставку и место в хранилище.
- Используйте, когда: Одни и те же преобразования или агрегации регулярно занимают значительную часть времени выполнения запроса.
- Изменение: Перенесите повторяющиеся вычисления на этап ингестии, планового обновления или в специально созданную структуру данных.
- Проверка: Убедитесь, что запрос обрабатывает меньший объём результатов и выполняет меньше вычислений во время выполнения, а нагрузка на ингестию или обновление остаётся приемлемой.
В каждом разделе описана базовая реализация, основной эксплуатационный компромисс и способ проверить результат.
Используйте incremental materialized view, когда необходимо поддерживать в актуальном состоянии регулярно применяемую фильтрацию, преобразование или агрегацию по мере поступления данных. Оно обрабатывает каждый новый вставленный блок и записывает преобразованный результат в целевую таблицу. Компромисс — дополнительная нагрузка при ингестии и необходимость явно указать целевую таблицу.
Например, панель мониторинга, которая регулярно подсчитывает поездки по дням, может считывать данные из небольшой агрегатной таблицы вместо группировки исходных данных для каждого запроса:
sum(trip_count), сгруппировав по pickup_date, чтобы строки, ожидающие фонового слияния, объединялись во время выполнения запроса. Представление обрабатывает только новые вставки, поэтому отдельно выполните дозагрузку существующих исходных данных. Проверьте изменения, сравнив длительность и количество прочитанных строк с исходной агрегацией, затем убедитесь, что дополнительные затраты на вставку приемлемы.
Используйте refreshable materialized view, если допустимы слегка устаревшие результаты, а полный результат можно пересчитывать с приемлемой периодичностью. Оно повторно выполняет свой запрос по расписанию. Компромисс — между актуальностью результатов и стоимостью каждого обновления.
Например, отчёт может каждый час пересчитывать общие суммы поездок по типам оплаты:
system.view_refreshes, чтобы убедиться, что длительность обновления, status и частота соответствуют рабочей нагрузке.
Используйте специализированную таблицу, если для отдельной рабочей нагрузки требуются существенно иные схема, ключ сортировки или жизненный цикл. Она позволяет явно контролировать физическую структуру и может быть понятнее, чем поддерживать множество проекций. Это требует дополнительного хранилища и управления конвейером. Повторяющиеся JOIN или преобразования также можно перенести в конвейер ингестии, если это позволяют исходные данные и требования к актуальности. Подробные рекомендации по проектированию см. в разделах Использование materialized views и Денормализация данных.
Например, создайте более узкую таблицу, упорядоченную для панели мониторинга, которая фильтрует поездки по типу оплаты и времени посадки:
Nullable из этих двух целевых столбцов. Убедитесь, что такой подход соответствует требованиям к данным рабочей нагрузки. Панель мониторинга должна явно обращаться к этой таблице, а конвейер ингестии — поддерживать её в актуальном состоянии. Проверьте изменение, сравнив с запросом к исходной таблице число прочитанных строк и байтов, использование памяти и длительность. Учтите также дополнительные затраты на хранилище и обслуживание конвейера.
При оценке изменений повторяйте исходные измерения в сопоставимых условиях. Убедитесь, что изменение сокращает объём целевой работы, не перенося узкое место в другое звено.
Перейдите к практическому примеру оптимизации, чтобы ознакомиться с изменениями схемы и ключа сортировки, измеренными относительно исходного базового уровня.