PREWHERE 优化是如何工作的？

在 ClickHouse 中，PREWHERE 子句是一种查询执行优化。它通过避免不必要的数据读取和过滤掉无关数据，在读取非过滤列之前减少 I/O 并提高查询速度。

本指南解释了 PREWHERE 的工作原理、如何衡量其影响以及如何对其进行调整以获得最佳性能。

没有 PREWHERE 优化的查询处理

我们将首先说明如何在 uk_price_paid_simple 表上处理查询，而不使用 PREWHERE

① 查询包含对 town 列的过滤，该列是表的主键的一部分，因此也是主索引的一部分。

② 为了加速查询，ClickHouse 将表的的主索引加载到内存中。

③ 它扫描索引条目以识别来自 town 列的哪些颗粒可能包含匹配谓词的行。

④ 这些潜在相关的颗粒会与任何其他查询所需的列的位置对齐的颗粒一起加载到内存中。

⑤ 然后在查询执行期间应用剩余的过滤器。

如您所见，如果没有 PREWHERE，所有潜在相关的列都会在过滤之前加载，即使只有少数行实际匹配。

PREWHERE 如何提高查询效率

以下动画展示了如何应用 PREWHERE 子句处理上述查询中的所有查询谓词。

前三个处理步骤与之前相同

① 查询包含对 town 列的过滤，该列是表的主键的一部分，因此也是主索引的一部分。

② 与没有 PREWHERE 子句的运行类似，为了加速查询，ClickHouse 将主索引加载到内存中，

③ 然后扫描索引条目以识别来自 town 列的哪些颗粒可能包含匹配谓词的行。

现在，由于 PREWHERE 子句，下一步有所不同：ClickHouse 不会立即读取所有相关列，而是逐列过滤数据，仅加载真正需要的内容。这大大减少了 I/O，尤其是在宽表的情况下。

在每个步骤中，它仅加载包含至少一行已存活（即匹配）前一个过滤器的颗粒。因此，要加载和评估每个过滤器的颗粒数量会单调递减

步骤 1：按 town 过滤
ClickHouse 首先通过 ① 读取 town 列中的选定颗粒并检查哪些颗粒实际上包含匹配 London 的行来开始 PREWHERE 处理。

在我们的示例中，所有选定的颗粒都匹配，因此 ② 对应于下一个过滤列—date—的位置对齐的颗粒随后被选中进行处理

步骤 2：按 date 过滤
接下来，ClickHouse ① 读取选定的 date 列颗粒以评估过滤器 date > '2024-12-31'。

在这种情况下，三个颗粒中有两个包含匹配的行，因此 ② 仅从下一个过滤列—price—中选择它们的位置对齐的颗粒以进行进一步处理

步骤 3：按 price 过滤
最后，ClickHouse ① 读取来自 price 列的两个选定颗粒以评估最后一个过滤器 price > 10_000。

只有两个颗粒中的一个包含匹配的行，因此 ② 仅需要从 SELECT 列—street—加载其位置对齐的颗粒以进行进一步处理

到最后一步，只有最少的一组列颗粒（包含匹配的行）被加载。这降低了内存使用量、减少了磁盘 I/O 并加快了查询执行速度。

PREWHERE 减少读取的数据，而不是处理的行数

请注意，ClickHouse 在 PREWHERE 和非 PREWHERE 查询版本中处理相同数量的行。但是，应用 PREWHERE 优化后，不必为每个处理的行加载所有列值。

PREWHERE 优化会自动应用

可以手动添加 PREWHERE 子句，如上面的示例所示。但是，您无需手动编写 PREWHERE。当设置 optimize_move_to_prewhere 启用（默认值为 true）时，ClickHouse 会自动将过滤器条件从 WHERE 移动到 PREWHERE，优先选择那些将减少读取量的过滤器。

其想法是较小的列扫描速度更快，并且到处理较大的列时，大多数颗粒已经过过滤。由于所有列具有相同数量的行，因此列的大小主要由其数据类型决定，例如，UInt8 列通常比 String 列小得多。

从版本 23.2 开始，ClickHouse 默认遵循此策略，对多步处理的 PREWHERE 过滤器列进行排序，按未压缩大小升序排列。

从版本 23.11 开始，可选的列统计信息可以通过基于实际数据选择性（而不仅仅是列大小）来选择过滤器处理顺序来进一步改进这一点。

如何衡量 PREWHERE 的影响

要验证 PREWHERE 是否正在帮助您的查询，您可以比较启用和禁用 optimize_move_to_prewhere 设置 的查询性能。

我们首先在禁用 optimize_move_to_prewhere 设置的情况下运行查询

SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' AND date > '2024-12-31' AND price < 10_000
SETTINGS optimize_move_to_prewhere = false;

   ┌─street──────┐
1. │ MOYSER ROAD │
2. │ AVENUE ROAD │
3. │ AVENUE ROAD │
   └─────────────┘

3 rows in set. Elapsed: 0.056 sec. Processed 2.31 million rows, 23.36 MB (41.09 million rows/s., 415.43 MB/s.)
Peak memory usage: 132.10 MiB.

ClickHouse 在处理 231 万行查询时读取了 23.36 MB 的列数据。

接下来，我们启用 optimize_move_to_prewhere 设置运行查询。（请注意，该设置是可选的，因为默认情况下该设置已启用）

SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' AND date > '2024-12-31' AND price < 10_000
SETTINGS optimize_move_to_prewhere = true;

   ┌─street──────┐
1. │ MOYSER ROAD │
2. │ AVENUE ROAD │
3. │ AVENUE ROAD │
   └─────────────┘

3 rows in set. Elapsed: 0.017 sec. Processed 2.31 million rows, 6.74 MB (135.29 million rows/s., 394.44 MB/s.)
Peak memory usage: 132.11 MiB.

处理的行数相同（231 万），但由于 PREWHERE，ClickHouse 读取的列数据减少了三倍多—仅 6.74 MB 而不是 23.36 MB—从而将总运行时间缩短了三倍。

要更深入地了解 ClickHouse 在后台如何应用 PREWHERE，请使用 EXPLAIN 和跟踪日志。

我们使用 EXPLAIN 子句检查查询的逻辑计划

EXPLAIN PLAN actions = 1
SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' and date > '2024-12-31' and price < 10_000;

...
Prewhere info                                                                                                                                                                                                                                          
  Prewhere filter column: 
    and(greater(__table1.date, '2024-12-31'_String), 
    less(__table1.price, 10000_UInt16), 
    equals(__table1.town, 'LONDON'_String)) 
...

我们省略了大部分计划输出，因为它非常冗长。本质上，它显示所有三个列谓词都已自动移动到 PREWHERE。

如果您自己重现此操作，您还会看到查询计划中这些谓词的顺序基于列的数据类型大小。由于我们尚未启用列统计信息，ClickHouse 使用大小作为确定 PREWHERE 处理顺序的后备方案。

如果您想更深入地了解，您可以指示 ClickHouse 在查询执行期间返回所有测试级别日志条目，以观察每个单独的 PREWHERE 处理步骤

SELECT
    street
FROM
   uk.uk_price_paid_simple
WHERE
   town = 'LONDON' AND date > '2024-12-31' AND price < 10_000
SETTINGS send_logs_level = 'test';

...
<Trace> ... Condition greater(date, '2024-12-31'_String) moved to PREWHERE
<Trace> ... Condition less(price, 10000_UInt16) moved to PREWHERE
<Trace> ... Condition equals(town, 'LONDON'_String) moved to PREWHERE
...
<Test> ... Executing prewhere actions on block: greater(__table1.date, '2024-12-31'_String)
<Test> ... Executing prewhere actions on block: less(__table1.price, 10000_UInt16)
...

关键要点

• PREWHERE 避免读取稍后将被过滤掉的列数据，从而节省 I/O 和内存。
• 当启用 optimize_move_to_prewhere（默认值）时，它会自动工作。
• 过滤顺序很重要：小的和选择性高的列应该放在前面。
• 使用 EXPLAIN 和日志来验证是否应用了 PREWHERE 并了解其效果。
• PREWHERE 对宽表和具有选择性过滤器的扫描影响最大。