略微加速

略速 - 互联网笔记

Clickhouse WHERE 和 PREWHERE 子句

2022-06-28 leiting (1879阅读)

标签 数据库

WHERE 子句:

where子句基于条件表达式来实现数据过滤,若过滤条件恰好是主键字段则能
进一步通过索引加助查询,在指标索引特性的表引擎


where子句是一条查询语句能否启用索引的判断依据。


where表达式包含主键 能够通过索引过滤数据区间。


PREWHERE子句:

prewhere 目前只适用于*MergeTree系列的表引擎,可以看做是
对where的一种优化,和where语句的作用相同,用来过滤数据。

不同之处在于prewhere首先会读取指定的列数据,来判断数据过
滤,等待数据过滤之后再读取select 声明的列字段来补全其余属性。

在某些场合下,prewhere语句比where语句处理的数据量更少性能更高。

相关的参数:
Clickhouse> select * from system.settings where name like '%prewhere%'\G

SELECT *
FROM system.settings
WHERE name LIKE '%prewhere%'

Row 1:
──────
name:        optimize_move_to_prewhere
value:       1
changed:     0
description: Allows disabling WHERE to PREWHERE optimization in SELECT queries from MergeTree.
min:         ᴺᵁᴸᴸ
max:         ᴺᵁᴸᴸ
readonly:    0
type:        SettingBool

1 rows in set. Elapsed: 0.002 sec.


Clickhouse> select count(1) from datasets.hits_v1;

SELECT count(1)
FROM datasets.hits_v1

┌─count(1)─┐
│  8873898 │
└──────────┘

1 rows in set. Elapsed: 0.011 sec.

Clickhouse> select count(1) from datasets.hits_v1 where JavaEnable=1;

SELECT count(1)
FROM datasets.hits_v1
WHERE JavaEnable = 1

┌─count(1)─┐
│  6535088 │
└──────────┘

1 rows in set. Elapsed: 0.013 sec. Processed 8.87 million rows, 8.87 MB (704.88 million rows/s., 704.88 MB/s.)

Clickhouse> select count(1) from datasets.hits_v1 prewhere JavaEnable=1;

SELECT count(1)
FROM datasets.hits_v1
PREWHERE JavaEnable = 1

┌─count(1)─┐
│  6535088 │
└──────────┘

1 rows in set. Elapsed: 0.009 sec. Processed 8.87 million rows, 8.87 MB (977.33 million rows/s., 977.33 MB/s.)


可以看到相同的结果,时间变少了,每秒处理的数据吞吐量增加。 根据JavaEnable字段进行了过滤。
测试2:
select WatchID,Title,GoodEvent from datasets.hits_v1 where JavaEnable=1;
6535088 rows in set. Elapsed: 73.843 sec. Processed 8.87 million rows, 863.90 MB
(120.17 thousand rows/s., 11.70 MB/s.)

select WatchID,Title,GoodEvent from datasets.hits_v1 prewhere JavaEnable=1;
6535088 rows in set. Elapsed: 73.282 sec. Processed 8.87 million rows, 863.90 MB
(121.09 thousand rows/s., 11.79 MB/s.)

通常Prewhere性能更优,是都需要将所有的where子句都替换为prewhere呢?其实不必这样,clickhouse提供了自动
化优化的功能,会在条件合适的情况下将where替换为prewhere。默认已经开启了此参数。


不能自动优化的情形:

1.使用常量表达式:
 
2.使用默认值为alias类型的字段
 
3.包含了arrayJOIN,globalIn,globalNotIn或者indexHint的查询:
 
4.select查询的列字段和where的谓词相同:
 
5.使用了主键字段:
 
虽然在上述情形不能自动将谓词移动到prewhere,但是仍然可以
使用prewhere。以主键字段为例子,当使用了prewhere进行主键查询,
首先会通过稀疏索引过滤数据区间,接着读取prewhere指定的条件列
进行过滤,这样就可能戒掉数据区间的尾巴,从而返回低
于index_granularity 粒度的数据范围。
 
 
即便如此,相比其他场合移动谓词带来的性能提升,这类效果比较有
限,目前在这类场合下仍然保持不移动的处理方式。


参考:

https://clickhouse.tech/docs/en/sql-reference/statements/select/prewhere/

https://clickhouse.tech/docs/en/sql-reference/statements/select/where/

北京半月雨文化科技有限公司.版权所有 京ICP备12026184号-3