Créez votre première projection

Votre table uk_price_paid est triée selon (postcode, addr1, addr2). Cela signifie que ClickHouse peut ignorer de grands blocs de données lorsque vous filtrez sur postcode, addr1 ou addr2, mais les requêtes qui filtrent sur town doivent parcourir chaque ligne - soit la totalité des 30 millions.

Une projection stocke une copie triée supplémentaire de (certaines ou de toutes les) colonnes dans la même table. Lorsque vous interrogez la table, l’optimiseur de requêtes vérifie automatiquement si la lecture depuis la projection nécessiterait moins de granules que les données de base, et l’utilise de façon transparente si c’est le cas.

Principales différences par rapport aux vues matérialisées :

• Pas de table distincte - la projection se trouve dans uk_price_paid elle-même
• Optimisation transparente des requêtes - vous interrogez uk_price_paid normalement ; ClickHouse choisit automatiquement la projection
• Reste synchronisée avec les mutations - les suppressions et les mises à jour appliquées à la table sont répercutées dans la projection

Pour en savoir plus, consultez la documentation de référence sur les projections.

Définissez une projection sur uk_price_paid qui stocke town, date, price et type, triés selon (town, date) :

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

Cela enregistre la projection dans les métadonnées de la table, mais ne la matérialise pas pour les données existantes : seules les insertions futures l’alimenteront.

Vérifiez que la projection apparaît dans la définition de la table :

SHOW CREATE TABLE uk_price_paid;

Vous devriez voir le bloc PROJECTION uk_price_paid_by_town dans le résultat.

Comme les vues matérialisées, une projection ajoutée récemment ne s'applique qu'aux futures insertions. Pour l'alimenter avec les 30 millions de lignes déjà présentes dans la table, matérialisez-la explicitement :

ALTER TABLE uk_price_paid
    MATERIALIZE PROJECTION uk_price_paid_by_town;

Cela s’exécute en arrière-plan, sous forme de mutation. Vous pouvez en suivre la progression :

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

Une fois is_done = 1, la projection est entièrement matérialisée. Vous pouvez aussi le vérifier en consultant 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;

Exécutez maintenant une requête en filtrant sur town — sur la même table que précédemment :

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;

Vérifiez les statistiques de la requête : beaucoup moins de lignes sont lues qu’avant la création de la projection, car ClickHouse a automatiquement choisi de lire à partir de la projection uk_price_paid_by_town au lieu de parcourir les données de base.

Vous pouvez confirmer que la projection a bien été utilisée avec 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;

Recherchez ReadFromMergeTree dans le résultat, avec une référence au nom de la projection. Si vous souhaitez comparer explicitement les performances, vous pouvez désactiver l’optimisation des projections pour une seule requête :

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;

Cela force un parcours complet de la table, ce qui vous permet de voir la différence de nombre de lignes lues.

Les projections et les vues matérialisées résolvent toutes deux le même problème — accélérer les lectures pour d’autres modes d’accès — mais elles impliquent des compromis différents. En bref, les projections sont les mieux adaptées lorsque vous avez simplement besoin d’un ordre de tri différent sur les mêmes données ; les vues matérialisées sont plus flexibles lorsque vous devez transformer, agréger ou acheminer des données vers un schéma différent. Pour une comparaison détaillée, consultez Vues matérialisées et projections.

Les projections stockent une seconde copie des colonnes sélectionnées dans la même table, ce qui augmente l’espace disque utilisé. Interrogez system.parts pour voir la taille totale de uk_price_paid (qui inclut désormais les données des projections) :

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;

Vous pouvez également consulter l’espace de stockage propre aux projections :

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;

Il s’agit du même compromis fondamental que pour les vues matérialisées : plus d’espace disque pour des lectures plus rapides. La projection peut être plus petite qu’une copie complète, car elle n’inclut que les quatre colonnes sélectionnées et la compression varie selon l’ordre de tri.