ClickHouse SQL调优

MergeTree引擎，数据是以分区目录的形式进行组织存储的，在进行的数据查询时，使用分区可以有效跳过无用的数据文件，减少数据的读取。

MergeTree引擎会根据索引字段进行数据排序，并且根据index_granularity的配置生成稀疏索引。根据索引字段查询，能快速过滤数据，减少数据的读取，大大提升查询性能。

OLAP分析场景，一张大宽表通常能有几百上千列，选择其中少数的几列做维度列、指标列计算。匹配这种场景下，ClickHouse的数据也是按照列存储的。如果使用select *，会大大加重系统的压力。

如果返回的数据量过大，客户端有可能出现内存溢出等服务异常。

对于前端使用ClickHouse的场景，如果要查询的数据量比较大，建议每次可适当地进行分页查询返回数据，以减少查询数据量对网络带宽和计算资源的占用。

【不做limit限制】

耗时：1.124

【做limit限制】

耗时：0.002

两表JOIN时，会将右表数据加载到内存中，再根据右表数据遍历左表做匹配，将小表放在右边，减少匹配查询的次数。根据使用的情况，大表join小表的性能比小表join大表的性能有数量级的提升。

【大表在左小表在右】

SELECT count(a.id)
FROM
(
SELECT id
FROM mytable
WHERE id < 100000000
) AS a
INNER JOIN
(
SELECT id
FROM mytable
WHERE id < 1000000
) AS b ON a.id = b.id;
耗时：0.145 sec。

【大表在右小表在左】

SELECT count(a.id)
FROM
(
SELECT id
FROM mytable
WHERE id < 1000000
) AS a
INNER JOIN
(
SELECT id
FROM mytable
WHERE id < 100000000
) AS b ON a.id = b.id;
耗时：0.996 sec。

【错误示例】

select did from (select did from tableA) limit 10;

【正确示例】

select did from (select did from tableA limit 10);

ClickHouse分布式join的性能较差，建议在模型侧将数据聚合成大宽表再导入ClickHouse。

【两表join查询】

SELECT
col1,
col2
FROM
(
SELECT
t1.col1 AS col1,
t2.col2 AS col2
FROM
(
SELECT
did,
col1
FROM table1
WHERE cc_pt_d = '2020-03-30'
) AS t1
LEFT JOIN
(
SELECT
did AS did_v2,
col2
FROM table2
WHERE pt_d = '2020-03-30'
) AS t2 ON t2.did_v2 = t1.did
) AS t
GROUP BY
col1,
col2
LIMIT 10;
耗时: 40秒。

【大宽表查询】

SELECT
col1,
col2
FROM
table1
GROUP BY
col1,
col2
LIMIT 10;
耗时: 8秒。

【示例】

SELECT uniqCombined(did) from pp.scene_model where pt_d < ‘2020-11-10’ and pt_d > ‘2020-11-03’;

【效果对比】

PREWHERE相对于WHERE在执行时的区别：首先只读取PREWHERE表达式所指定的列，根据条件做数据过滤，再根据过滤后的数据读取其他列。这通常会减少磁盘读取数据的压力。

PREWHERE只支持*MergeTree系列的表。系统配置optimize_move_to_prewhere默认开启，将WHERE转成PREWHERE，可以根据自己的业务场景调整这个配置。

查询语句中同时有PREWHERE和WHERE，在这种情况下，PREWHERE先于WHERE执行。

Clickhouse快是因为采用了并行处理机制，即使一个查询，默认也会用服务器一半的CPU去执行，所以ClickHouse对高并发查询的场景支持的不够。

官方默认的最大并发数是100，可以根据实际场景调整并发配置，实际使用中并发数配置的是150，建议不超过200。

ClickHouse支持连接集群中的任意节点查询，如果查询集中到一台节点，可能会导致该节点的压力过大并且可靠性不高。建议使用ClickHouseBalancer或者其他负载均衡服务，均衡查询负载，提升可靠性。

ClickHouse提供多种近似去重算法，通过count_distinct_implementation配置，支持将countDistinct语法转成所配置的近似算法。查询性能有数量级的提升。

近似算法的误差一般在1%以内。在数据准确度要求不高，比如趋势分析等，建议使用近似去重提升用户体验。

【使用精确去重查询】

耗时：1.280秒。

【使用近似查询】

耗时：0.061秒。

【字符编码前，32字节的String类型字段did】

CREATE TABLE default.Test_String ON Cluster default_cluster
(
  `EventDate` DateTime,
  `did`       String,
  `UserID`    UInt32,
  `ver`       UInt16
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/Test_String', '{replica}')
PARTITION BY toYYYYMM(EventDate)　
ORDER BY (EventDate, intHash32(UserID))　
SETTINGS index_granularity = 8192;
select count(distinct did) from dws_wallet_xxx_mlb_ds;
执行耗时：142秒。

【字符编码后，将32位长String转码成int类型】

CREATE TABLE default.Test_Int ON Cluster default_cluster
(
  `EventDate` DateTime,
  `did`       UInt32,
  `UserID`    UInt32,
  `ver`       UInt16
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/Test_Int', '{replica}')
PARTITION BY toYYYYMM(EventDate)　
ORDER BY (EventDate, intHash32(UserID))　
SETTINGS index_granularity = 8192;
select count(distinct did_int) from dws_wallesst_xxx_mlb_ds;
执行耗时：34秒。

【countDistinct做精确去重】

select count(distinct did_int) from dws_wallet_xxx_mlb_ds;
执行耗时：34秒。

【countDistinct做精确去重】

select groupBitmapMergeState(arrayReduce('groupBitmapState', [toUInt64(did)])) as user1 from t_r_309;
执行耗时：8秒。

对于查询方式比较固定的场景，建议使用物化视图，提前做好数据聚合，相对于查询明细表，性能有数量级的提升。

【物化视图创建】

明细表、物化视图创建参见 【建议】 物化视图创建参考。

【明细表插入数据】

INSERT INTO counter SELECT
toDateTime('2019-01-01 00:00:00') + toInt64(number / 10) AS when,
(number % 10) + 1 AS device,
((device * 3) + (number / 10000)) + ((rand() % 53) * 0.1) AS value
FROM system.numbers
LIMIT 100000000;

【查询明细表】

【查询物化视图】

【效果对比】

使用物化视图后，遍历的数据量从1亿下降到2000，耗时从0.211秒下降到0.002秒，性能提升100倍。

【场景】

用户画像，用户数预估：计算t_r_309和t_r_308 join后，did字段的基数。

【表join示例】

SELECT countDistinct(a.did)
FROM
(
SELECT DISTINCT did
FROM t_r_309
) AS a
INNER JOIN
(
SELECT DISTINCT did
FROM t_r_308
) AS b ON a.did = b.did;

【bitmap实现示例】

SELECT bitmapAndCardinality(user1, user2)
FROM
(
SELECT
1 AS join_id,
groupBitmapMergeState(arrayReduce('groupBitmapState', [toUInt32(did)])) AS user1
FROM t_r_309
) AS a
INNER JOIN
(
SELECT
1 AS join_id,
groupBitmapMergeState(arrayReduce('groupBitmapState', [toUInt32(did)])) AS user2
FROM t_r_308
) AS b ON a.join_id = b.join_id;

【效果对比】

多张表join后计算，随着join数越多，时延越大，基本在几十秒以上。使用bitmap计算预估，耗时在3秒以内。

ClickHouse基于分布式表的查询会转换成所有分片的本地表的操作，再汇总结果。实际使用中，join和global join的执行逻辑差别很大，建议使用global join做分布式表查询。

【场景说明】

1. 查询的集群有N个分片（shard）
2. A_all是分布式表，对应的本地表是A_local
3. B_all是分布式表，对应的本地表是B_local

SELECT * FROM A_all AS t1 JOIN B_all AS t2 ON t1.id = t2.id;

执行逻辑如下：

1. 在发起查询的节点，将查询分发到所有分片，转成A_all Join B_local。
2. 在收到a中每个请求的分片，再将请求分发到所有分片，转成A_local Join B_local。
3. 可以看到，分布式表的join操作，存在查询放大的问题。

SELECT * FROM A_all AS t1 GLOBAL JOIN B_all AS t2 ON t1.id = t2.id;

执行逻辑如下：

1. 在查询发起的节点，查询B_all的所有数据到本地的缓存表T中，并将T分发到所有节点。
2. 查询发起的节点，将本地缓存表T分发到所有分片。
3. 每个分片执行A_local join T。
4. 在收到a中每个请求的分片，再将请求分发到所有分片，转成A_local Join B_local。

【效果对比】

可以看到，使用GLOBAL关键字后，查询的放大减少了很多。不过，由于需要将右表汇总再分发到所有机器，如果右表的数据量很大，需要考虑机器的内存，避免内存溢出。

ClickHouse提供了两种数据压缩方式供选择：LZ4和ZSTD。

默认的LZ4压缩方式，会提供更快的执行效率，但是同时，要付出较多的磁盘容量占用的代价。

如果使用去重引擎进行数据查询，且使用argMax函数和final关键字，会导致整个查询性能较差，需要提前对重复数据做合并去重optimize操作，查询时候直接查询不需要使用argMax函数和final关键字，提升查询性能。