Dix règles d'optimisation pour clickhouse SQL

Tout le monde sait,ClickHouse De SQL Les règles d'optimisation sont basées surRBO(Rule Based Optimization)De,Vous savez quelles sont les règles d'optimisation ？

Ce qui suit,Ce sont les dix règles d'optimisation que j'ai compilées à la fin de l'année dernière,Pas nécessairement tous.,Juste pour jeter des briques et du jade.Si vous avez quelque chose à ajouter,,Bienvenue à me faire confiance..

1. COUNT Optimisation :

En appelant count Fonction,Si vous utilisez count() Ou count(*),Et non. where Conditions,Est directement utilisé system.tables De total_rows,Par exemple:

EXPLAIN
SELECT count()
FROM test_x
 
 
Query id: d255fb14-7160-4f1a-9148-9810494d792d
 
 
┌─explain──────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))          │
│   MergingAggregated                                  │
│     ReadFromPreparedSource (Optimized trivial count) │
└──────────────────────────────────────────────────────┘

Attention! Optimized trivial count ,C'est vrai count Optimisation de.

Si count Champs de colonne spécifiques,Cette optimisation n'est pas utilisée：

EXPLAIN
SELECT count(id)
FROM test_x
 
 
Query id: 170b10db-88d7-45a1-ae8a-8d683707b635
 
 
┌─explain───────────────────────────────────────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY))                                   │
│   Aggregating                                                                 │
│     Expression (Before GROUP BY)                                              │
│       SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│         ReadFromStorage (MergeTree)                                           │
└───────────────────────────────────────────────────────────────────────────────┘

2. Supprimer les champs dupliqués de la Sous - Requête:

Il y a deux répétitions dans la requête de phrase suivante id Champ,Ça va être lourd:

EXPLAIN SYNTAX
SELECT
    a.id,
    b.name,
    a.price,
    b.id
FROM id_join_tb1 AS a
LEFT JOIN 
(
    SELECT
        id,
        id,
        name,
        time
    FROM join_tb1
) AS b USING (id)
 
 
Query id: 6879ecc6-8579-4f01-964c-9eab4b15687a
 
 
┌─explain───────────────┐
│ SELECT                │
│     id,               │
│     name,             │
│     price,            │
│     b.id              │
│ FROM id_join_tb1 AS a │
│ ALL LEFT JOIN         │
│ (                     │
│     SELECT            │
│         id,           │
│         name          │
│     FROM join_tb1     │
│ ) AS b USING (id)     │
└───────────────────────┘

3. Prédicat Push down:

Quand group by Oui. having Clause,Mais non. with cube、with rollup Ou with totals Quand il est embelli,having Le filtre est poussé vers le bas where Filtre à l'avance.Par exemple, la requête suivante,HAVING name Est devenu WHERE name,In group by Filtrage précédent:

EXPLAIN SYNTAX
SELECT name
FROM join_tb1
GROUP BY name
HAVING name = ''
 
 
Query id: 6eb2f8eb-2e29-43ae-9414-5914b921a622
 
 
┌─explain─────────┐
│ SELECT name     │
│ FROM join_tb1   │
│ WHERE name = '' │
│ GROUP BY name   │
└─────────────────┘

La même chose.,Les sous - requêtes supportent également la déduction prédictive, Par exemple, l'énoncé suivant  WHERE id = 10:

EXPLAIN SYNTAX
SELECT *
FROM 
(
    SELECT id
    FROM id_join_tb1
)
WHERE id = 10
 
 
Query id: 44a3e084-4b8a-4847-9909-ec34c8d8be74
 
 
┌─explain──────────────┐
│ SELECT id            │
│ FROM                 │
│ (                    │
│     SELECT id        │
│     FROM id_join_tb1 │
│     WHERE id = 10    │
│ )                    │
│ WHERE id = 10        │
└──────────────────────┘

Un autre exemple:

EXPLAIN SYNTAX
SELECT *
FROM 
(
    SELECT *
    FROM 
    (
        SELECT id
        FROM id_join_tb1
    )
    UNION ALL
    SELECT *
    FROM 
    (
        SELECT id
        FROM id_join_tb1
    )
)
WHERE id = 10
 
 
Query id: a807c968-a4b9-4f84-a80d-48c8385d2206
 
 
┌─explain──────────────────┐
│ SELECT id                │
│ FROM                     │
│ (                        │
│     SELECT id            │
│     FROM                 │
│     (                    │
│         SELECT id        │
│         FROM id_join_tb1 │
│         WHERE id = 10    │
│     )                    │
│     WHERE id = 10        │
│     UNION ALL            │
│     SELECT id            │
│     FROM                 │
│     (                    │
│         SELECT id        │
│         FROM id_join_tb1 │
│         WHERE id = 10    │
│     )                    │
│     WHERE id = 10        │
│ )                        │
│ WHERE id = 10            │
└──────────────────────────┘

4. Extrapolation des calculs d'agrégation:

Calcul dans la fonction d'agrégation,Extrapolera,Par exemple：

EXPLAIN SYNTAX
SELECT sum(id * 2)
FROM join_tb1
 
 
Query id: 027a5dce-fa57-447a-9615-888881069d61
 
 
┌─explain────────────┐
│ SELECT sum(id) * 2 │
│ FROM join_tb1      │
└────────────────────┘

5. Annulation de la fonction d'agrégation:

Si la clé d'agrégation,C'est - à - dire group by key Utiliser min、max、any Fonction d'agrégation,Supprimer la fonction,Par exemple：

EXPLAIN SYNTAX
SELECT
    sum(id * 2),
    max(name),
    max(id)
FROM join_tb1
GROUP BY id
 
 
Query id: 4d72f7fa-5146-4365-adc4-260566f5f414
 
 
┌─explain──────────┐
│ SELECT           │
│     sum(id) * 2, │
│     max(name),   │
│     id           │
│ FROM join_tb1    │
│ GROUP BY id      │
└──────────────────┘

6. Supprimer les duplicatas group by key

Par exemple, l'énoncé suivant,Dupliquer les clés d'agrégation id Les champs sont supprimés:

EXPLAIN SYNTAX
SELECT *
FROM join_tb1
ORDER BY
    id ASC,
    id ASC,
    name ASC,
    name ASC
 
 
Query id: 3fc0267a-9bf7-4811-b384-4a9e90517bbf
 
 
┌─explain───────┐
│ SELECT        │
│     id,       │
│     name,     │
│     time      │
│ FROM join_tb1 │
│ ORDER BY      │
│     id ASC,   │
│     name ASC  │
└───────────────┘

7. Supprimer les duplicatas limit by key

Par exemple, l'énoncé suivant,Déclaration répétée name Les champs sont supprimés:

EXPLAIN SYNTAX
SELECT *
FROM join_tb1
LIMIT 3 BY
    name,
    name
LIMIT 10
 
 
Query id: e87a0ed9-66b4-49c7-b6ea-b5c8ad3d7901
 
 
┌─explain─────────┐
│ SELECT          │
│     id,         │
│     name,       │
│     time        │
│ FROM join_tb1   │
│ LIMIT 3 BY name │
│ LIMIT 10        │
└─────────────────┘

8. Supprimer les duplicatas USING Key

Par exemple, l'énoncé suivant,Dupliquer la clé associée id Les champs sont supprimés:

EXPLAIN SYNTAX
SELECT
    a.id,
    a.id,
    b.name,
    a.price,
    b.id
FROM id_join_tb1 AS a
LEFT JOIN join_tb1 AS b USING (id, id)
 
 
Query id: d0917046-71da-469e-b738-14d947bf53e3
 
 
┌─explain────────────────────────────────┐
│ SELECT                                 │
│     id,                                │
│     id,                                │
│     name,                              │
│     price,                             │
│     b.id                               │
│ FROM id_join_tb1 AS a                  │
│ ALL LEFT JOIN join_tb1 AS b USING (id) │
└────────────────────────────────────────┘

9. Remplacement scalaire

Si la Sous - requête ne renvoie qu'une seule ligne de données,Remplacer par scalaire lorsqu'il est référencé,Par exemple, dans l'énoncé suivant total_disk_usage Champ:

EXPLAIN SYNTAX
WITH 
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10
 
 
Query id: a9c7431f-cd51-4a85-9fba-b6301578a8cd
 
 
┌─explain────────────────────────────────────────────────────────┐
│ WITH identity(CAST(0, 'UInt64')) AS total_disk_usage           │
│ SELECT                                                         │
│     (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage, │
│     table                                                      │
│ FROM system.parts                                              │
│ GROUP BY table                                                 │
│ ORDER BY table_disk_usage DESC                                 │
│ LIMIT 10                                                       │
└────────────────────────────────────────────────────────────────┘

10. Optimisation des opérations TERNAIRES

Si elle est allumée optimize_if_chain_to_multiif Paramètres,L'opérateur ternaire est remplacé par multiIf Fonctions,Par exemple:

EXPLAIN SYNTAX 
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') 
FROM numbers(10) 
settings optimize_if_chain_to_multiif = 1
 
 
Query id: fd5cde0f-a73f-4763-b823-42f9367f658b
 
 
┌─explain─────────────────────────────────────────────────────────┐
│ SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'xyz') │
│ FROM numbers(10)                                                │
│ SETTINGS optimize_if_chain_to_multiif = 1                       │
└─────────────────────────────────────────────────────────────────┘

Toutes ces règles. Get On y est??

Auteur：Jukai,ClickHouseUn des contributeurs,ClickHousePrédicateur,Architecte principal,《ClickHouseAnalyse des principes et application pratique》《 Construction d'une plateforme de Big Data de classe entreprise ：Architecture et mise en œuvre》Auteur,

Plus de dix ansITExpérience professionnelle, Une étude approfondie des principales technologies et solutions dans le domaine du Big Data , Exceller dans la conception architecturale et l'intégration de systèmes distribués . A dirigé la planification de nombreux produits au niveau de la plateforme Big Data 、 Travaux de conception et de développement , Expérience pratique de première ligne .