为什么我的主键没有被使用？如何检查？
·6 分钟阅读
涵盖了主键未在排序中使用的常见原因，以及我们如何确认
检查您的主键
用户可能会遇到查询速度比预期慢的情况，他们认为自己正在按主键排序或过滤。在本文中，我们将展示用户如何确认主键被使用，并强调其未被使用的常见原因。
创建表
考虑以下简单表
CREATE TABLE logs
(
`code` LowCardinality(String),
`timestamp` DateTime64(3)
)
ENGINE = MergeTree
ORDER BY (code, toUnixTimestamp(timestamp))
请注意，我们的排序键如何将 toUnixTimestamp(timestamp) 作为第二个条目包含在内。
填充数据
用 1 亿行数据填充此表
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
┌───count()─┐
│ 100000000 │ -- 100.00 million
└───────────┘
1 row in set. Elapsed: 0.002 sec.
基本过滤
如果我们按代码过滤，我们可以看到输出中扫描的行数。 - 49.15 thousand。请注意，这只是总共 1 亿行数据的一个子集。
SELECT count() AS c
FROM logs
WHERE code = '200'
┌────────c─┐
│ 65607542 │ -- 65.61 million
└──────────┘
1 row in set. Elapsed: 0.021 sec. Processed 49.15 thousand rows, 49.17 KB (2.34 million rows/s., 2.34 MB/s.)
Peak memory usage: 92.70 KiB.
此外，我们可以使用 EXPLAIN indexes=1 子句确认索引的使用
EXPLAIN indexes = 1
SELECT count() AS c
FROM logs
WHERE code = '200'
┌─explain────────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection)) │
│ AggregatingProjection │
│ Expression (Before GROUP BY) │
│ Filter ((WHERE + Change column names to column identifiers)) │
│ ReadFromMergeTree (default.logs) │
│ Indexes: │
│ PrimaryKey │
│ Keys: │
│ code │
│ Condition: (code in ['200', '200']) │
│ Parts: 3/3 │
│ Granules: 8012/12209 │
│ ReadFromPreparedSource (_minmax_count_projection) │
└────────────────────────────────────────────────────────────────────┘
请注意，扫描的粒度数 8012 只是总数 12209 的一小部分。下面突出显示的部分确认了主键代码的使用。
PrimaryKey
Keys:
code
粒度是 ClickHouse 中数据处理的单位，每个粒度通常包含 8192 行。有关粒度及其过滤方式的更多详细信息，我们建议阅读本指南。
注意
在排序键中较后的键上进行过滤不如在元组中较早的键上进行过滤有效。有关原因，请参阅此处
多键过滤
假设我们按 code 和 timestamp 进行过滤
SELECT count()
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')
┌─count()─┐
│ 689742 │
└─────────┘
1 row in set. Elapsed: 0.008 sec. Processed 712.70 thousand rows, 6.41 MB (88.92 million rows/s., 799.27 MB/s.)
EXPLAIN indexes = 1
SELECT count()
FROM logs
WHERE (code = '200') AND (timestamp >= '2025-01-01 00:00:00') AND (timestamp <= '2026-01-01 00:00:00')
┌─explain───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Expression ((Project names + Projection)) │
│ Aggregating │
│ Expression (Before GROUP BY) │
│ Expression │
│ ReadFromMergeTree (default.logs) │
│ Indexes: │
│ PrimaryKey │
│ Keys: │
│ code │
│ toUnixTimestamp(timestamp) │
│ Condition: and((toUnixTimestamp(timestamp) in (-Inf, 1767225600]), and((toUnixTimestamp(timestamp) in [1735689600, +Inf)), (code in ['200', '200']))) │
│ Parts: 3/3 │
│ Granules: 87/12209 │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
13 rows in set. Elapsed: 0.002 sec.
在这种情况下，两个排序键都用于过滤行，因此只需要读取 87 个粒度。
在排序中使用键
ClickHouse 还可以利用排序键进行高效排序。具体来说，
当 optimize_read_in_order 设置启用（默认情况下启用）时，ClickHouse 服务器使用表索引并按 ORDER BY 键的顺序读取数据。这使我们可以在指定 LIMIT 的情况下避免读取所有数据。因此，具有小限制的大数据查询处理速度更快。有关更多详细信息，请参阅此处和此处。
然而，这需要所用键的对齐。
例如，考虑以下查询
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
┌─code─┬───────────────timestamp─┐
│ 200 │ 2025-01-01 00:00:01.000 │
│ 200 │ 2025-01-01 00:00:45.000 │
│ 200 │ 2025-01-01 00:01:01.000 │
│ 200 │ 2025-01-01 00:01:45.000 │
│ 200 │ 2025-01-01 00:02:01.000 │
│ 200 │ 2025-01-01 00:03:01.000 │
│ 200 │ 2025-01-01 00:03:45.000 │
│ 200 │ 2025-01-01 00:04:01.000 │
│ 200 │ 2025-01-01 00:05:45.000 │
│ 200 │ 2025-01-01 00:06:01.000 │
└──────┴─────────────────────────
10 rows in set. Elapsed: 0.009 sec. Processed 712.70 thousand rows, 6.41 MB (80.13 million rows/s., 720.27 MB/s.)
Peak memory usage: 125.50 KiB.
我们可以通过使用 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
┌─explain───────────────────────────────────────────────────────────────────────┐
│ (Expression) │
│ ExpressionTransform │
│ (Limit) │
│ Limit │
│ (Sorting) │
│ MergingSortedTransform 12 → 1 │
│ MergeSortingTransform × 12 │
│ LimitsCheckingTransform × 12 │
│ PartialSortingTransform × 12 │
│ (Expression) │
│ ExpressionTransform × 12 │
│ (Expression) │
│ ExpressionTransform × 12 │
│ (ReadFromMergeTree) │
│ MergeTreeSelect(pool: ReadPool, algorithm: Thread) × 12 0 → 1 │
└───────────────────────────────────────────────────────────────────────────────┘
15 rows in set. Elapsed: 0.004 sec.
此处的行 MergeTreeSelect(pool: ReadPool, algorithm: Thread) 并未指示优化的使用，而是指示标准读取。这是由我们的表排序键使用 toUnixTimestamp(Timestamp) 而不是 timestamp 引起的。纠正此不匹配可以解决问题
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
┌─explain──────────────────────────────────────────────────────────────────────────┐
│ (Expression) │
│ ExpressionTransform │
│ (Limit) │
│ Limit │
│ (Sorting) │
│ MergingSortedTransform 3 → 1 │
│ BufferChunks × 3 │
│ (Expression) │
│ ExpressionTransform × 3 │
│ (Expression) │
│ ExpressionTransform × 3 │
│ (ReadFromMergeTree) │
│ MergeTreeSelect(pool: ReadPoolInOrder, algorithm: InOrder) × 3 0 → 1 │
└──────────────────────────────────────────────────────────────────────────────────┘
13 rows in set. Elapsed: 0.003 sec.