Создайте первую проекцию

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

Руководство по быстрому старту

Real-time аналитикаХранилище данныхОбсервабилитиAI/MLCloudOss

Предварительные требования

To successfully follow this guide, you’ll need the following:

• A running ClickHouse Cloud service. If you don’t have one yet, complete the Create your first Cloud service quickstart first.

Вам также следует пройти следующие руководства из раздела «Быстрый старт», поскольку это руководство напрямую опирается на таблицу uk_price_paid и представленные в них концепции:

• Создайте свою первую таблицу MergeTree
• Создайте свое первое materialized view

Что вы создадите

В кратком руководстве по MergeTree вы увидели, что при запросах к uk_price_paid по town или county требуется полное сканирование таблицы, поскольку таблица отсортирована по (postcode, addr1, addr2). В этом кратком руководстве вы решите эту проблему, создав проекцию — дополнительное отсортированное представление данных, хранящееся внутри той же таблицы. В отличие от materialized views, проекции не требуют отдельной целевой таблицы, остаются синхронизированными с мутациями (удалениями и обновлениями) и прозрачно используются оптимизатором запросов — вы по-прежнему выполняете запросы к таблице с тем же именем. К концу этого руководства вы поймёте, как добавить и материализовать проекцию, как ClickHouse автоматически её выбирает и в каких случаях проекции предпочтительнее materialized views.

1. Зачем нужна проекция

   Ваша таблица uk_price_paid отсортирована по (postcode, addr1, addr2). Это значит, что ClickHouse может пропускать большие блоки данных, когда вы фильтруете по postcode, addr1 или addr2, но при фильтрации по town запросам приходится сканировать каждую строку — все 30 миллионов.

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

   Ключевые отличия от materialized views:

   • Нет отдельной таблицы — проекция находится прямо внутри uk_price_paid
   • Прозрачная оптимизация запросов — вы обращаетесь к uk_price_paid как обычно; ClickHouse автоматически выбирает проекцию
   • Синхронизируется с мутациями — удаления и обновления, применённые к таблице, отражаются в проекции

   Подробнее см. в справочной документации по проекциям.

2. Добавьте проекцию в таблицу

   Определите проекцию для uk_price_paid, в которой town, date, price и type хранятся с сортировкой по (town, date):

   ALTER TABLE uk_price_paid
       ADD PROJECTION uk_price_paid_by_town
       (
           SELECT town, date, price, type
           ORDER BY (town, date)
       );
   

   Это регистрирует проекцию в метаданных таблицы, но не материализует её для существующих данных — только будущие вставки будут её заполнять.

   Убедитесь, что проекция присутствует в определении таблицы:

   SHOW CREATE TABLE uk_price_paid;
   

   В выводе должен быть блок PROJECTION uk_price_paid_by_town.

3. Материализуйте проекцию для существующих данных

   Как и materialized view, недавно добавленная проекция применяется только к будущим вставкам. Чтобы заполнить её 30 миллионами строк, уже находящимися в таблице, нужно материализовать её явно:

   ALTER TABLE uk_price_paid
       MATERIALIZE PROJECTION uk_price_paid_by_town;
   

   Эта операция выполняется как фоновая мутация. Вы можете проверить её прогресс:

   SELECT
       mutation_id,
       command,
       is_done
   FROM system.mutations
   WHERE table = 'uk_price_paid'
   ORDER BY create_time DESC
   LIMIT 5;
   

   Когда is_done = 1, проекция полностью материализована. Также можно убедиться в этом, проверив system.projection_parts:

   SELECT
       name,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
   FROM system.projection_parts
   WHERE table = 'uk_price_paid'
     AND active = true
   GROUP BY name;
   
4. Выполните запрос к таблице и обратите внимание на автоматическое использование проекции

   Теперь выполните запрос с фильтром по town — к той же таблице, к которой вы обращались ранее:

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   Проверьте статистику запроса — считывается значительно меньше строк, чем до появления проекции, поскольку ClickHouse автоматически выбрал чтение из проекции uk_price_paid_by_town вместо сканирования исходных данных.

   Убедиться, что проекция действительно использовалась, можно с помощью EXPLAIN:

   EXPLAIN
   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

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

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC
   SETTINGS optimize_use_projections = 0;
   

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

5. Сравнение проекций и materialized views

   И проекции, и materialized views решают одну и ту же задачу — ускоряют чтение при альтернативных шаблонах доступа, — но предполагают разные компромиссы. Коротко говоря, проекции лучше подходят, когда нужен лишь другой порядок сортировки тех же данных; materialized views более гибки, когда требуется преобразовывать, агрегировать или направлять данные в другую схему. Подробное сравнение см. в разделе Materialized views versus projections.

6. Оцените дополнительный расход хранилища

   Проекции хранят вторую копию выбранных столбцов в той же таблице, увеличивая использование диска. Выполните запрос к system.parts, чтобы увидеть общий размер uk_price_paid (который теперь включает данные проекций):

   SELECT
       table,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS compressed_size
   FROM system.parts
   WHERE table = 'uk_price_paid'
     AND active = true
   GROUP BY table;
   

   Вы также можете проверить хранилище для конкретных проекций:

   SELECT
       name,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS projection_size
   FROM system.projection_parts
   WHERE table = 'uk_price_paid'
     AND active = true
   GROUP BY name;
   

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

Следующие шаги

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

Далее ознакомьтесь со следующими руководствами по быстрому старту:

Или подробнее изучите справочную документацию:

• materialized view и проекции
• Справочник по проекциям
• Движок таблицы MergeTree

руководство по быстрому старту