ClickHouse SQL调优
思路
如果我们收到需求SQL执行较慢，需要通过调优解决。需要分析SQL性能瓶颈点，是资源瓶颈还是非资源瓶颈问题，如果是资源瓶颈问题可以通过扩容解决，否则有以下几个方法进一步排查调优，调优原则为尽最大优化手段从磁盘上扫描最少数据，以提升单条SQL查询性能，这样集群所有SQL整体查询性能和并发/TPS才能获取较优的调优结果。
- 业务合理性分析
需要联合业务侧架构师或对ClickHouse比较了解，负责ClickHouse数据库建模人员，排查建表、SQL编写、数据库参数、并发数等是否合理，逐项排查分析，消除每一项对性能影响点，调优性能问题。
- 查看执行计划
- 如果业务上经过分析，都比较合理，我们需要获取用户反馈的TOP性能差距SQL或从query_log中查找查询时间TOP慢的SQL进行分析。
- 分析查询计划，通过explain indexes = 1 {SQL STATMENT};命令查看执行计划，如下执行计划重点关注：分区和索引优化后的扫描数据Parts文件和Granules数据块是否在总数据量上有较大量级别上的提升。
在如下案例中的Partition(默认MinMax索引)/PrimaryKey(一级索引)/Skip(二级索引)，通过这些优化索引过滤后的数据扫描为Parts: 5/123，Granules: 23/8513，基本上parts文件和granules数据块获取了96%和99.7%性能优化收益，当前的这个执行计划优化就比较理想，查询性能较优（详见ClickHouse官方文档https://clickhouse.com/docs/best-practices/use-data-skipping-indices-where-appropriate）。
--建表 CREATE TABLE stackoverflow.posts ( `Id` Int32 CODEC(Delta(4), ZSTD(1)), `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8), `AcceptedAnswerId` UInt32, `CreationDate` DateTime64(3, 'UTC'), `Score` Int32, `ViewCount` UInt32 CODEC(Delta(4), ZSTD(1)), `Body` String, `OwnerUserId` Int32, `OwnerDisplayName` String, `LastEditorUserId` Int32, `LastEditorDisplayName` String, `LastEditDate` DateTime64(3, 'UTC') CODEC(Delta(8), ZSTD(1)), `LastActivityDate` DateTime64(3, 'UTC'), `Title` String, `Tags` String, `AnswerCount` UInt16 CODEC(Delta(2), ZSTD(1)), `CommentCount` UInt8, `FavoriteCount` UInt8, `ContentLicense` LowCardinality(String), `ParentId` String, `CommunityOwnedDate` DateTime64(3, 'UTC'), `ClosedDate` DateTime64(3, 'UTC'), INDEX view_count_idx ViewCount TYPE minmax GRANULARITY 1 --index here ) ENGINE = MergeTree PARTITION BY toYear(CreationDate) ORDER BY (PostTypeId, toDate(CreationDate)); --执行计划 EXPLAIN indexes = 1 SELECT count() FROM stackoverflow.posts WHERE (CreationDate > '2009-01-01') AND (ViewCount > 10000000) ┌─explain────────────────────────────────────────────────────────────┐ │ Expression ((Project names + Projection)) │ │ Aggregating │ │ Expression (Before GROUP BY) │ │ Expression │ │ ReadFromMergeTree (stackoverflow.posts) │ │ Indexes: │ │ MinMax │ │ Keys: │ │ CreationDate │ │ Condition: (CreationDate in ('1230768000', +Inf)) │ │ Parts: 123/128 │ │ Granules: 8513/8545 │ │ Partition │ │ Keys: │ │ toYear(CreationDate) │ │ Condition: (toYear(CreationDate) in [2009, +Inf)) │ │ Parts: 123/123 │ │ Granules: 8513/8513 │ │ PrimaryKey │ │ Keys: │ │ toDate(CreationDate) │ │ Condition: (toDate(CreationDate) in [14245, +Inf)) │ │ Parts: 123/123 │ │ Granules: 8513/8513 │ │ Skip │ │ Name: view_count_idx │ │ Description: minmax GRANULARITY 1 │ │ Parts: 5/123 │ │ Granules: 23/8513 │ └────────────────────────────────────────────────────────────────────┘ 29 rows in set. Elapsed: 0.211 sec. - 查看trace log执行过程瓶颈点
可在session级别配置参数send_logs_level='trace'，也可以在SQL级别配置，{SQL STATMENT} SETTINGS send_logs_level='trace'（如下2种命令），执行SQL后观察打印出的信息，查看第二列时间主要时间花费在哪个阶段。
SET send_logs_level = 'trace'; SELECT * FROM db1.table1;
cat send_logs_level_example.sql | ./clickhouse client -n -m --host abc123.us-west-2.aws.clickhouse.cloud --secure --port 9440 --password ABC123 --send_logs_level=trace 2>&1 | tee send_log_results.txt
[c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.437263 [ 1247 ] {4b347939-db0f-48f8-9550-1ccc7d591c44} <Debug> executeQuery: (from 71.56.215.107:47946) SET send_logs_level = 'trace'; (stage: Complete) [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.437546 [ 1247 ] {4b347939-db0f-48f8-9550-1ccc7d591c44} <Debug> TCPHandler: Processed in 0.000727434 sec. [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.508066 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Debug> executeQuery: (from 71.56.215.107:47946) SELECT * FROM db1.table1; (stage: Complete) [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.508437 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Trace> InterpreterSelectQuery: FetchColumns -> Complete [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.508530 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Debug> db1.table1 (781f25db-3cd1-47c6-a76e-701945a67485) (SelectExecutor): Key condition: unknown [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.508581 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Trace> db1.table1 (781f25db-3cd1-47c6-a76e-701945a67485) (SelectExecutor): Filtering marks by primary and secondary keys [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.508994 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Debug> db1.table1 (781f25db-3cd1-47c6-a76e-701945a67485) (SelectExecutor): Selected 2/2 parts by partition key, 2 parts by primary key, 2/2 marks by primary key, 2 marks to read from 2 ranges [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.509034 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Trace> db1.table1 (781f25db-3cd1-47c6-a76e-701945a67485) (SelectExecutor): Spreading mark ranges among streams (default reading) [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.509102 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Debug> MergeTreePrefetchedReadPool(db1.table1 (781f25db-3cd1-47c6-a76e-701945a67485)): Increasing prefetch step from 0 to 24 [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.509146 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Debug> MergeTreePrefetchedReadPool(db1.table1 (781f25db-3cd1-47c6-a76e-701945a67485)): Part: all_0_3_2, sum_marks: 1, approx mark size: 0, prefetch_step_bytes: 0, prefetch_step_marks: 24, (ranges: (0, 1)) [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.509180 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Debug> MergeTreePrefetchedReadPool(db1.table1 (781f25db-3cd1-47c6-a76e-701945a67485)): Increasing prefetch step from 0 to 24 [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.509218 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Debug> MergeTreePrefetchedReadPool(db1.table1 (781f25db-3cd1-47c6-a76e-701945a67485)): Part: all_4_4_0, sum_marks: 1, approx mark size: 0, prefetch_step_bytes: 0, prefetch_step_marks: 24, (ranges: (0, 1)) [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.509251 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Debug> MergeTreePrefetchedReadPool(db1.table1 (781f25db-3cd1-47c6-a76e-701945a67485)): Sum marks: 2, threads: 2, min_marks_per_thread: 1, min prefetch step marks: 24, prefetches limit: 200, total_size_approx: 0 [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.509312 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Debug> db1.table1 (781f25db-3cd1-47c6-a76e-701945a67485) (SelectExecutor): Reading approx. 8 rows with 2 streams 1 a 2 b 3 test, test 4 test, "test" 1 a 2 b 3 a 4 b [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.510601 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Debug> executeQuery: Read 8 rows, 152.00 B in 0.002588 sec., 3091.190108191654 rows/sec., 57.36 KiB/sec. [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.510663 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} <Debug> TCPHandler: Processed in 0.002984367 sec.以上3个步骤在调优过程中需要进行递进式分析，每一步分析解决后不需要再进行下一步分析。
规则/性能优化检查点
- 合理使用数据表的分区字段和索引字段。
MergeTree引擎，数据是以分区目录的形式进行组织存储的，在进行的数据查询时，使用分区可以有效跳过无用的数据文件，减少数据的读取。
MergeTree引擎会根据索引字段进行数据排序，并且根据index_granularity的配置生成稀疏索引。根据索引字段查询，能快速过滤数据，减少数据的读取，大大提升查询性能。
- 不要用select *，只查询需要的字段，减少机器负载，提升查询性能。
OLAP分析场景，一张大宽表通常能有几百上千列，选择其中少数的几列做维度列、指标列计算。匹配这种场景下，ClickHouse的数据也是按照列存储的。如果使用select *，会大大加重系统的压力。
- 通过limit限制查询返回的数据量，节省计算资源、减少网络开销。
对于前端使用ClickHouse的场景，如果要查询的数据量比较大，建议每次可适当地进行分页查询返回数据，以减少查询数据量对网络带宽和计算资源的占用。
【不做limit限制】
耗时：1.124
【做limit限制】
耗时：0.002
- join查询时小表在右。
在ClickHouse中，通常不建议使用JOIN，如果必须要进行JOIN查询，需要进行两两表关联查询，两表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。
- ClickHouse不支持limit下推，SQL生成时需要优化，以免SQL性能受影响。
select did from (select did from tableA) limit 10;
【正确示例】
select did from (select did from tableA limit 10);
- 基于大宽表做数据分析，尽量不要使用大表join大表的操作。
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’;
- 在分组、join等操前做数据过滤，减少计算的数据量。
- 用PREWHERE替代WHERE，优先过滤数据，加速查询。
PREWHERE相对于WHERE在执行时的区别：首先只读取PREWHERE表达式所指定的列，根据条件做数据过滤，再根据过滤后的数据读取其他列。这通常会减少磁盘读取数据的压力。
PREWHERE只支持*MergeTree系列的表。系统配置optimize_move_to_prewhere默认开启，将WHERE转成PREWHERE，可以根据自己的业务场景调整这个配置。
查询语句中同时有PREWHERE和WHERE，在这种情况下，PREWHERE先于WHERE执行。
- 合理配置最大并发数。
Clickhouse快是因为采用了并行处理机制，即使一个查询，默认也会用服务器一半的CPU去执行，所以ClickHouse对高并发查询的场景支持的不够好。
官方默认的最大并发数是100，可以根据实际场景调整并发配置，实际使用中复杂查询并发数配置为几十，简单查询SQL建议并发不超过100。
- 部署负载均衡组件，查询基于负载均衡组件进行，避免单点查询压力太大影响性能。
ClickHouse支持连接集群中的任意节点查询，如果应用查询集中到集群中一个节点，可能会导致该节点的负载并且可靠性受影响。建议使用ClickHouseBalancer/ELB服务，均衡查询负载，提升集群可靠性。
- 用近似去重（uniqCombined、uniq）替代精确去重。
ClickHouse提供多种近似去重算法，通过count_distinct_implementation配置，支持将countDistinct语法转成所配置的近似算法。查询性能有数量级的提升。
近似算法的误差一般在1%以内。在数据准确度要求不高，比如趋势分析等，建议使用近似去重提升用户体验。
【使用精确去重查询】
耗时：1.280秒。
【使用近似查询】
耗时：0.061秒。
- 对于字符串类型的字段做复杂计算，建议先编码成整数类型，以提升计算性能。
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_wallet_xxx_mlb_ds; 执行耗时：34秒。 - 高基数（大于10W）字段（int类型），使用bitmap做精确去重。
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倍。
- 使用bitmap做跨表预估计算。
用户画像，用户数预估：计算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秒以内。
- 使用GLOBAL JOIN/IN替换普通的JOIN。
ClickHouse基于分布式表的查询会转换成所有分片的本地表的操作，再汇总结果。实际使用中，join和global join的执行逻辑差别很大，建议使用global join做分布式表查询。
【场景说明】
- 查询的集群有N个分片（shard）
- A_all是分布式表，对应的本地表是A_local
- B_all是分布式表，对应的本地表是B_local
【分布式表直接join示例】SELECT * FROM A_all AS t1 JOIN B_all AS t2 ON t1.id = t2.id;
执行逻辑如下：
- 在发起查询的节点，将查询分发到所有分片，转成A_all Join B_local。
- 在收到a中每个请求的分片，再将请求分发到所有分片，转成A_local Join B_local。
- 可以看到，分布式表的join操作，存在查询放大的问题。
【分布式表global join示例】SELECT * FROM A_all AS t1 GLOBAL JOIN B_all AS t2 ON t1.id = t2.id;
执行逻辑如下：
- 在查询发起的节点，查询B_all的所有数据到本地的缓存表T中，并将T分发到所有节点。
- 查询发起的节点，将本地缓存表T分发到所有分片。
- 每个分片执行A_local join T。
- 在收到a中每个请求的分片，再将请求分发到所有分片，转成A_local Join B_local。
【效果对比】
可以看到，使用GLOBAL关键字后，查询的放大减少了很多。不过，由于需要将右表汇总再分发到所有机器，如果右表的数据量很大，需要考虑机器的内存，避免内存溢出。
- 数据压缩算法的选择，建议使用默认的lz4压缩算法。
ClickHouse提供了两种数据压缩方式供选择：LZ4和ZSTD。
默认的LZ4压缩方式，会提供更快的执行效率，但是同时，要付出较多的磁盘容量占用的代价。
- ReplacingMergeTree表引擎数据查询，需要先做数据去重合并提升性能。
如果使用去重引擎进行数据查询，且使用argMax函数和final关键字，会导致整个查询性能较差，需要提前对重复数据做合并去重optimize操作，查询时候直接查询不需要使用argMax函数和final关键字，提升查询性能。
