Pourquoi ma clé primaire n’est-elle pas utilisée ? Comment puis-je le vérifier ?

Il peut arriver que les utilisateurs constatent qu’une requête est plus lente que prévu alors qu’ils pensent trier ou filtrer sur une clé primaire. Dans cet article, nous montrons comment vérifier que la clé est bien utilisée et présentons les raisons courantes pour lesquelles ce n’est pas le cas.

Considérons la table simple suivante :

CREATE TABLE logs
(
    `code` LowCardinality(String),
    `timestamp` DateTime64(3)
)
ENGINE = MergeTree
ORDER BY (code, toUnixTimestamp(timestamp))

Notez que notre clé de tri comprend toUnixTimestamp(timestamp) en deuxième position.

Insérez 100 M de lignes dans cette table :

INSERT INTO logs SELECT
 ['200', '404', '502', '403'][toInt32(randBinomial(4, 0.1)) + 1] AS code,
    now() + toIntervalMinute(number) AS timestamp
FROM numbers(100000000)

0 rows in set. Elapsed: 15.845 sec. Processed 100.00 million rows, 800.00 MB (6.31 million rows/s., 50.49 MB/s.)

SELECT count()
FROM logs

┌

Si nous filtrons par code, nous pouvons voir dans le résultat le nombre de lignes analysées : 49.15 thousand. Notez qu'il s'agit d'un sous-ensemble du total de 100 M de lignes.

SELECT count() AS c
FROM logs
WHERE code = '200'

┌

De plus, nous pouvons vérifier que l’index est utilisé avec la clause EXPLAIN indexes=1 :

EXPLAIN indexes = 1
SELECT count() AS c
FROM logs
WHERE code = '200'

┌

Remarquez que le nombre de granules analysés, 8012, ne représente qu'une fraction du total, 12209. La section mise en évidence ci-dessous confirme l'utilisation du code de clé primaire.

PrimaryKey
  Keys: 
   code 

Les granules constituent l’unité de traitement des données dans ClickHouse, chacune contenant généralement 8 192 lignes. Pour en savoir plus sur les granules et sur la façon dont elles sont filtrées, nous vous recommandons de consulter ce guide.

Supposons que nous filtrions par code et timestamp :

SELECT count()
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')

┌

Dans ce cas, les deux clés de tri sont utilisées pour filtrer les lignes, de sorte qu’il suffit de ne lire que 87 granules.

ClickHouse peut également exploiter les clés de tri pour effectuer le tri plus efficacement. Plus précisément,

Lorsque le paramètre optimize_read_in_order est activé (par défaut), le serveur ClickHouse utilise l'index de la table et lit les données dans l'ordre de la clé ORDER BY. Cela permet d'éviter de lire toutes les données lorsqu'une clause LIMIT est spécifiée. Ainsi, les queries sur de gros volumes de données avec de petites limites sont traitées plus rapidement. Voir ici et ici pour plus de détails.

Cela nécessite toutefois que les clés utilisées soient alignées.

Par exemple, prenons la requête suivante :

SELECT *
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')
ORDER BY timestamp ASC
LIMIT 10

┌

Nous pouvons confirmer ici que l’optimisation n’a pas été utilisée en recourant à EXPLAIN pipeline :

EXPLAIN PIPELINE
SELECT *
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')
ORDER BY timestamp ASC
LIMIT 10

┌

Ici, la ligne MergeTreeSelect(pool: ReadPool, algorithm: Thread) n’indique pas l’utilisation de l’optimisation, mais plutôt une lecture standard. Cela s’explique par le fait que la clé de tri de notre table utilise toUnixTimestamp(Timestamp) et NON timestamp. La correction de ce décalage résout le problème :

EXPLAIN PIPELINE
SELECT *
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')
ORDER BY toUnixTimestamp(timestamp) ASC
LIMIT 10

┌