Проекции
Введение
ClickHouse предлагает различные механизмы для ускорения аналитических запросов на больших объемах данных в сценариях реального времени. Одним из таких механизмов для ускорения ваших запросов является использование Проекций. Проекции помогают оптимизировать запросы, создавая переупорядочение данных по интересующим атрибутам. Это может быть:
- Полное переупорядочение
- Подмножество оригинальной таблицы с другим порядком
- Предварительно вычисленная агрегация (аналогично материализованному представлению), но с порядком, согласованным с агрегацией.
Как работают Проекции?
Практически, проекцию можно рассматривать как дополнительную, скрытую таблицу к оригинальной таблице. Проекция может иметь другой порядок строк и, следовательно, другой первичный индекс, чем у оригинальной таблицы, и она может автоматически и инкрементно предварительно вычислять агрегированные значения. В результате использование Проекций предоставляет два "регулятора" для ускорения выполнения запроса:
- Правильное использование первичных индексов
- Предварительное вычисление агрегатов
Проекции отчасти схожи с Материализованными Представлениями, которые также позволяют вам иметь несколько порядков строк и предварительно вычислять агрегации во время вставки. Проекции автоматически обновляются и синхронизируются с оригинальной таблицей, в отличие от материализованных представлений, которые обновляются явно. Когда запрос нацелен на оригинальную таблицу, ClickHouse автоматически выбирает первичные ключи и выбирает таблицу, которая может сгенерировать тот же правильный результат, но требует чтения наименьшего количества данных, как показано на рисунке ниже:
Когда использовать Проекции?
Проекции являются привлекательной функцией для новых пользователей, так как они автоматически поддерживаются в процессе вставки данных. Более того, запросы могут быть отправлены только в одну таблицу, где проекции используются, где это возможно, для ускорения времени отклика.
Это контрастирует с материализованными представлениями, где пользователю необходимо выбирать соответствующую оптимизированную целевую таблицу или переписывать свой запрос в зависимости от фильтров. Это увеличивает нагрузку на клиентские приложения и усложняет работу на стороне клиента.
Несмотря на эти преимущества, у проекций есть некоторые присущие ограничения, которые пользователи должны учитывать и, следовательно, они должны использоваться с осторожностью.
- Проекции не позволяют использовать разные TTL для исходной таблицы и (скрытой) целевой таблицы, материализованные представления позволяют разные TTL.
- Проекции в настоящее время не поддерживают
optimize_read_in_orderдля (скрытой) целевой таблицы. - Легковесные обновления и удаления не поддерживаются для таблиц с проекциями.
- Материализованные представления могут связываться: целевая таблица одного материализованного представления может быть исходной таблицей другого материализованного представления и т. д. Это невозможно с проекциями.
- Проекции не поддерживают соединения, но материализованные представления поддерживают.
- Проекции не поддерживают фильтры (клауза
WHERE), но материализованные представления поддерживают.
Мы рекомендуем использовать проекции, когда:
- Требуется полное переупорядочение данных. Хотя выражение в проекции может в теории использовать
GROUP BY, материализованные представления более эффективны для поддержания агрегатов. Оптимизатор запросов также с большей вероятностью будет использовать проекции, которые используют простое переупорядочение, т.е.SELECT * ORDER BY x. Пользователи могут выбрать подмножество колонок в этом выражении, чтобы уменьшить занимаемое место. - Пользователи готовы принять сопутствующее увеличение занимаемого места и накладные расходы на запись данных дважды. Проверьте влияние на скорость вставки и оцените накладные расходы на хранение.
Примеры
Фильтрация по колонкам, которые не входят в первичный ключ
В этом примере мы покажем, как добавить проекцию в таблицу. Мы также рассмотрим, как проекция может быть использована для ускорения запросов, которые фильтруют по колонкам, которые не входят в первичный ключ таблицы.
Для этого примера мы будем использовать набор данных New York Taxi Data, доступный на sql.clickhouse.com, который отсортирован по pickup_datetime.
Давайте напишем простой запрос, чтобы найти все идентификаторы поездок, для которых пассажиры оставили чаевые водителю больше $200:
Обратите внимание, что поскольку мы фильтруем по tip_amount, который не входит в ORDER BY, ClickHouse должен был выполнить полное сканирование таблицы. Давайте ускорим этот запрос.
Чтобы сохранить оригинальную таблицу и результаты, мы создадим новую таблицу и скопируем данные, используя INSERT INTO SELECT:
Чтобы добавить проекцию, мы используем оператор ALTER TABLE вместе с оператором ADD PROJECTION:
После того как проекция добавлена, необходимо воспользоваться оператором MATERIALIZE PROJECTION, чтобы данные в ней физически упорядочивались и переписывались в соответствии с указанным выше запросом:
Теперь давайте снова запустим запрос, теперь, когда мы добавили проекцию:
Обратите внимание, как нам удалось существенно уменьшить время выполнения запроса и необходимо было просмотреть меньше строк.
Мы можем подтвердить, что наш вышеуказанный запрос действительно использовал проекцию, которую мы создали, запросив таблицу system.query_log:
Использование проекций для ускорения запросов по ценам в Великобритании
Чтобы продемонстрировать, как проекции могут быть использованы для ускорения производительности запросов, давайте посмотрим на пример, использующий набор данных из реальной жизни. Для этого примера мы будем использовать таблицу из нашего учебника по ценам на недвижимость в Великобритании с 30,03 миллиона строк. Этот набор данных также доступен в нашей среде sql.clickhouse.com.
Если вы хотите увидеть, как таблица была создана и данные вставлены, вы можете обратиться к странице "Набор данных по ценам на недвижимость в Великобритании".
Мы можем запустить два простых запроса на этом наборе данных. Первый перечисляет округа в Лондоне, которые имеют самые высокие цены, а второй вычисляет среднюю цену для округов:
Обратите внимание, что, несмотря на очень быструю работу, оба запроса выполнены с полным сканированием всей таблицы из 30,03 миллиона строк из-за того, что ни town, ни price не были в нашем операторе ORDER BY, когда мы создавали таблицу:
Давайте посмотрим, сможем ли мы ускорить этот запрос с помощью проекций.
Чтобы сохранить оригинальную таблицу и результаты, мы создадим новую таблицу и скопируем данные, используя INSERT INTO SELECT:
Мы создаем и заполняем проекцию prj_oby_town_price, которая создает дополнительную (скрытую) таблицу с первичным индексом, упорядоченным по городу и цене, чтобы оптимизировать запрос, который перечисляет округа в конкретном городе для самых высоких цен:
Настройка mutations_sync используется для принудительного выполнения синхронной операции.
Мы создаем и заполняем проекцию prj_gby_county – дополнительную (скрытую) таблицу, которая инкрементально предварительно вычисляет агрегированные значения avg(price) для всех существующих 130 округов Великобритании:
Если в проекции, как в проекции prj_gby_county выше, используется клауза GROUP BY, то основе хранения (скрытой) таблицы становится AggregatingMergeTree, и все агрегатные функции преобразуются в AggregateFunction. Это обеспечивает правильную инкрементную агрегацию данных.
На рисунке ниже представлена визуализация основной таблицы uk_price_paid_with_projections и ее двух проекций:
Если мы снова выполним запрос, который перечисляет округа в Лондоне для трех самых высоких цен, мы увидим улучшение в производительности запроса:
Аналогично, для запроса, который перечисляет округа Великобритании с тремя самыми высокими средними ценами:
Обратите внимание, что оба запроса нацелены на оригинальную таблицу и что оба запроса привели к полному сканированию таблицы (все 30,03 миллиона строк были считаны с диска) до того, как мы создали две проекции.
Также обратите внимание, что запрос, который перечисляет округа в Лондоне для трех самых высоких цен, обрабатывает 2,17 миллиона строк. Когда мы напрямую использовали вторую таблицу, оптимизированную для этого запроса, только 81,92 тысячи строк были считаны с диска.
Причина разницы в том, что в настоящее время оптимизация optimize_read_in_order, упомянутая выше, не поддерживается для проекций.
Мы проверяем таблицу system.query_log, чтобы увидеть, что ClickHouse автоматически использовал две проекции для двух вышеуказанных запросов (смотрите столбец проекций ниже):
Дополнительные примеры
Следующие примеры используют тот же набор данных по ценам в Великобритании, сопоставляя запросы с проекциями и без них.
Для сохранения нашей оригинальной таблицы (и производительности) мы снова создадим копию таблицы, используя CREATE AS и INSERT INTO SELECT.
Создание Проекции
Давайте создадим агрегатную проекцию по измерениям toYear(date), district и town:
Заполним проекцию для существующих данных. (Без материализации она будет создана только для вновь вставленных данных):
Следующие запросы сопоставляют производительность с проекциями и без них. Чтобы отключить использование проекций, мы используем настройку optimize_use_projections, которая включена по умолчанию.
Запрос 1. Средняя цена за год
Результаты должны быть одинаковыми, но производительность во втором примере лучше!
Запрос 2. Средняя цена за год в Лондоне
Запрос 3. Самые дорогие районы
Условие (date >= '2020-01-01') нужно модифицировать, чтобы оно соответствовало размерности проекции (toYear(date) >= 2020):
Снова результат одинаковый, но отметьте улучшение в производительности запросов для второго запроса.
