Clickhouse-查询优化

[toc]

# 查询优化

# 单表查询

# Prewhere 替代 where

Prewhere 和 where 语句的作用相同，用来过滤数据。不同之处在于 prewhere 只支持 MergeTree 族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取 select 声明的列字段来补全其余属性。

当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作。

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

#关闭 where 自动转 prewhere(默认情况下， where 条件会自动优化成 prewhere)
set optimize_move_to_prewhere=0; 
# 使用 where
select WatchID, 
 JavaEnable, 
 Title, 
 GoodEvent, 
 EventTime, 
 EventDate, 
 CounterID, 
 ClientIP, 
 ClientIP6, 
 RegionID, 
 UserID, 
 CounterClass, 
 OS, 
 UserAgent, 
 URL, 
 Referer, 
 URLDomain, 
 RefererDomain, 
 Refresh, 
 IsRobot, 
 RefererCategories, 
 URLCategories, 
 URLRegions, 
 RefererRegions, 
 ResolutionWidth, 
 ResolutionHeight, 
 ResolutionDepth, 
 FlashMajor, 
 FlashMinor, 
 FlashMinor2
from datasets.hits_v1 where UserID='3198390223272470366';

# 使用 prewhere 关键字
select WatchID, 
 JavaEnable,
 Title, 
 GoodEvent, 
 EventTime, 
 EventDate, 
 CounterID, 
 ClientIP, 
 ClientIP6, 
 RegionID, 
 UserID, 
 CounterClass, 
 OS, 
 UserAgent, 
 URL, 
 Referer, 
 URLDomain, 
 RefererDomain, 
 Refresh, 
 IsRobot, 
 RefererCategories, 
 URLCategories, 
 URLRegions, 
 RefererRegions, 
 ResolutionWidth, 
 ResolutionHeight, 
 ResolutionDepth, 
 FlashMajor, 
 FlashMinor, 
 FlashMinor2
from datasets.hits_v1 prewhere UserID='3198390223272470366';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

默认情况，我们肯定不会关闭 where 自动优化成 prewhere，但是某些场景即使开启优化，也不会自动转换成 prewhere，需要手动指定 prewhere：

使用常量表达式
使用默认值为 alias 类型的字段
包含了 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查询
select 查询的列字段和 where 的谓词相同
使用了主键字段

# 数据采样

通过采样运算可极大提升数据分析的性能

SELECT Title,count(*) AS PageViews 
FROM hits_v1
SAMPLE 0.1 #代表采样 10%的数据,也可以是具体的条数
WHERE CounterID =57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000

1
2
3
4
5
6

采样修饰符只有在 MergeTree engine 表中才有效，且在创建表时需要指定采样策略。

# 列裁剪与分区裁剪

数据量太大时应避免使用 select * 操作，查询的性能会与查询的字段大小和数量成线性表换，字段越少，消耗的 io 资源越少，性能就会越高。

反例：
select * from datasets.hits_v1;
正例：
select WatchID, 
 JavaEnable, 
 Title, 
 GoodEvent, 
 EventTime, 
 EventDate, 
 CounterID, 
 ClientIP, 
 ClientIP6, 
 RegionID, 
 UserID
from datasets.hits_v1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

分区裁剪就是只读取需要的分区，在过滤条件中指定。

select WatchID, 
 JavaEnable, 
 Title, 
 GoodEvent, 
 EventTime, 
 EventDate, 
 CounterID, 
 ClientIP, 
 ClientIP6, 
 RegionID, 
 UserID
from datasets.hits_v1
where EventDate='2014-03-23';

1
2
3
4
5
6
7
8
9
10
11
12
13

# orderby 结合 where、limit

千万以上数据集进行 order by 查询时需要搭配 where 条件和 limit 语句一起使用。

#正例：
SELECT UserID,Age
FROM hits_v1 
WHERE CounterID=57
ORDER BY Age DESC LIMIT 1000

#反例：
SELECT UserID,Age
FROM hits_v1 
ORDER BY Age DESC

1
2
3
4
5
6
7
8
9
10

# 避免构建虚拟列

如非必须，不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。

反例：
SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;

正例：拿到 Income 和 Age 后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储
SELECT Income,Age FROM datasets.hits_v1;

1
2
3
4
5

# uniqCombined 替代 distinct

性能可提升 10 倍以上，uniqCombined 底层采用类似 HyperLogLog 算法实现，能接受2%左右的数据误差，可直接使用这种去重方式提升查询性能。Count(distinct )会使用 uniqExact精确去重。

反例：
select count(distinct rand()) from hits_v1;

正例：
SELECT uniqCombined(rand()) from datasets.hits_v1

1
2
3
4
5

# 其他注意

# 查询熔断

为了避免因个别慢查询引起的服务雪崩的问题，除了可以为单个查询设置超时以外，还可以配置周期熔断，在一个查询周期内，如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。

# 时间周期的累计用量熔断

这种方式下系统资源的用量是按照时间周期累积统计的，当累积量达到阈值，则直到下个计算周期开始之前，该用户无法继续进行操作。这种方式通过在 user.xml 中的 quota 标签进行配置，示例如下：

配置的 0 表示不做限制

<quotas>
	<default> <!-- 自定义名称 -->
		<internal>
			<duartion>3600</duartion> <!-- 时间周期 单位：秒 -->
			<queries>0</queries> <!-- 在周期内允许执行的查询次数 -->
			<errors>0</errors> <!-- 在周期内允许发生异常的次数 -->
			<result_rows>0</result_rows> <!-- 在周期内允许查询返回的结果行数 -->
			<read_rows>0</read_rows> <!-- 在周期内允许分布式查询节点读取的数据行数-->
			<execution_time>0</execution_time> <!-- 在周期内允许执行的查询时间 -->
		</internal>
	</default>
</quotas>

1
2
3
4
5
6
7
8
9
10
11
12

# 单次查询的用量熔断

这种方式下系统资源的用量是按照单次查询统计的，而具体的熔断规则则是由许多不同配置项组成，这些配置项需要定义在用户 profile 中。

针对普通查询的熔断配置
- max_memory_usage：在单个 ClickHouse 服务进程中，运行一次查询限制使用的最大内存量，默认值为 10GB。
- max_memory_usage_for_user：以用户为单位进行统计，单个用户在运行查询时限制使用的最大内存量，默认值为 0，即不做限制。
- max_memory_usage_for_all_queries：所有运行的查询累加在一起所限制的最大内存量，默认值为 0，即不做限制。
针对数据写入和聚合查询相关的熔断配置
- max_partitions_per_insert_block：在单次写入时，限制创建的最大分区个数，默认值为 100 个。
- max_rows_to_group_by：在执行 GROUP BY 聚合查询时限制去重后聚合 KEY 的最大个数，默认值为 0，即不做限制。当超过阈值时，处理方式由 group_by_overflow_mode 参数指定。
- group_by_overflow_mode当 max_rows_to_group_by熔断规则触发时，group_by_overflow_mode会提供三种处理方式：
  - throw：抛出异常，默认值。
  - break：立即停止查询，并返回当前数据。
  - any：仅根据当前已存在的聚合 KEY 继续完成聚合查询。
- max_bytes_before_external_group_by：在执行 GROUP BY 聚合查询时限制使用的最大内存量，默认值为 0，即不做限制。当超过阈值时，聚合查询将会进一步借用本地磁盘。

# 关闭虚拟内存

物理内存和虚拟内存的数据交换，会导致查询变慢，资源允许的情况下关闭虚拟内存。

# 配置 join_use_nulls

为每一个账户添加 join_use_nulls 配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准 SQL 中的 Null 值。

# 批量写入时先排序

批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致 ClickHouse 无法及时对新导入的数据进行合并，从而影响查询性能。

# 关注 CPU

cpu 一般在 50%左右会出现查询波动，达到 70%会出现大范围的查询超时，cpu 是最关键的指标，要非常关注。

# TODO-多表关联

上次更新: 2023/03/10, 17:30:33

← Clickhouse-语法优化规则 Clickhouse-数据一致性→