# ClickHouse SQL调优 #### 思路 如果我们收到需求SQL执行较慢，需要通过调优解决。需要分析SQL性能瓶颈点，是资源瓶颈还是非资源瓶颈问题，如果是资源瓶颈问题可以通过扩容解决，否则有以下几个方法进一步排查调优，调优原则为尽可能通过优化手段从磁盘上扫描最少数据，以提升单条SQL查询性能，这样集群所有SQL整体查询性能和并发/TPS才能获取较优的调优结果。 - 业务合理性分析 需要联合业务侧架构师或对ClickHouse比较了解的、负责ClickHouse数据库建模的人员，排查建表、SQL编写、数据库参数、并发数等是否合理，逐项排查分析，消除每一项对性能影响点，调优性能问题。 - 查看执行计划 1. 如果业务上经过分析，都比较合理，我们需要获取用户反馈的TOP性能差距SQL或从query_log中查找查询时间TOP慢的SQL进行分析。 2. 分析查询计划，通过**EXPLAIN indexes = 1** *{SQL STATEMENT}* ;命令查看执行计划，如下执行计划重点关注：分区和索引优化后的扫描数据Parts文件和Granules数据块是否在总数据量上有较大量级别上的提升。 在如下案例中的Partition(默认MinMax索引)/PrimaryKey(一级索引)/Skip(二级索引)，通过这些优化索引过滤后的数据扫描为Parts: 5/123，Granules: 23/8513，基本上parts文件和granules数据块获取了96%和99.7%性能优化收益，当前的这个执行计划优化就比较理想，查询性能较优（*详见* ClickHouse官方文档）。 ``` --建表 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 STATEMENT} 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} 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} 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} 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} InterpreterSelectQuery: FetchColumns -> Complete [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.508530 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} 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} 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} 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} 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} 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} 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} 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} 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} 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} db1.table1 (781f25db-3cd1-47c6-a76e-701945a67485) (SelectExecutor): Reading approx. 8 rows with 2 streams 1a 2b 3test, test 4test, "test" 1a 2b 3a 4b [c-azure-gk-72-server-tojnalg-0] 2024.08.13 21:31:28.510601 [ 1247 ] {3406aa56-20e8-44e0-b5de-8cb7715861f3} 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} TCPHandler: Processed in 0.002984367 sec. ``` 以上3个步骤在调优过程中需要进行递进式分析，每一步分析解决后不需要再进行下一步分析。 #### 规则/性能优化检查点 1. 合理使用数据表的分区字段和索引字段。 MergeTree引擎，数据是以分区目录的形式进行组织存储的，在进行数据查询时，使用分区可以有效跳过无用的数据文件，减少数据的读取。 MergeTree引擎会根据索引字段进行数据排序，并且根据index_granularity的配置生成稀疏索引。根据索引字段查询，能快速过滤数据，减少数据的读取，大大提升查询性能。 2. 不要用**select \*** ，只查询需要的字段，减少机器负载，提升查询性能。 OLAP分析场景，一张大宽表通常能有几百上千列，选择其中少数的几列做维度列、指标列计算。匹配这种场景下，ClickHouse的数据也是按照列存储的。如果使用**select \***，会大大加重系统的压力。 3. 通过limit限制查询返回的数据量，节省计算资源、减少网络开销。 如果返回的数据量过大，客户端有可能出现内存溢出等服务异常。 对于前端使用ClickHouse的场景，如果要查询的数据量比较大，建议每次可适当地进行分页查询返回数据，以减少查询数据量对网络带宽和计算资源的占用。 【不做limit限制】 ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000001504251824.png) ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000001503772128.png) 耗时：1.124 【做limit限制】 ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000001503932040.png) ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000001555051781.png) 耗时：0.002 ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000001555011705.png) 4. 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。 ``` 5. ClickHouse不支持LIMIT下推，SQL生成时需要优化，以免SQL性能受影响。 【错误示例】 ``` select did from (select did from tableA) limit 10; ``` 【正确示例】 ``` select did from (select did from tableA limit 10); ``` 6. 基于大宽表做数据分析，尽量不要使用大表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秒。 ``` #### 建议 1. 明确数据查询的范围，增加条件过滤和查询的数据周期过滤，缩小数据查询范围。 【示例】 ``` SELECT uniqCombined(did) FROM pp.scene_model WHERE pt_d < ‘2020-11-10’ and pt_d > ‘2020-11-03’; ``` 2. 在分组、join等操前做数据过滤，减少计算的数据量。 【效果对比】 ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000001529285236.png) 3. 用PREWHERE替代WHERE，优先过滤数据，加速查询。 PREWHERE相对于WHERE在执行时的区别：首先只读取PREWHERE表达式所指定的列，根据条件做数据过滤，再根据过滤后的数据读取其他列。这通常会减少磁盘读取数据的压力。 PREWHERE只支持\*MergeTree系列的表。系统配置**optimize_move_to_prewhere**默认开启，将WHERE转成PREWHERE，可以根据自己的业务场景调整这个配置。 查询语句中同时有PREWHERE和WHERE，在这种情况下，PREWHERE先于WHERE执行。 4. 合理配置最大并发数。 ClickHouse快是因为采用了并行处理机制，即使一个查询，默认也会用服务器一半的CPU去执行，所以ClickHouse对高并发查询的场景支持得不够好。 官方默认的最大并发数是100，可以根据实际场景调整并发配置，实际使用中复杂查询并发数配置为几十，简单查询SQL建议并发不超过100。 5. 部署负载均衡组件，查询基于负载均衡组件进行，避免单点查询压力太大影响性能。 ClickHouse支持连接集群中的任意节点查询，如果应用查询集中到集群中一个节点，可能会导致该节点的负载过高并且可靠性受影响。建议使用ClickHouseBalancer/ELB服务，均衡查询负载，提升集群可靠性。 6. 用近似去重（uniqCombined、uniq）替代精确去重。 ClickHouse提供多种近似去重算法，通过count_distinct_implementation配置，支持将countDistinct语法转成所配置的近似算法。查询性能有数量级的提升。 近似算法的误差一般在1%以内。在数据准确度要求不高，比如趋势分析等，建议使用近似去重提升用户体验。 【使用精确去重查询】 ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000001554891773.png) 耗时：1.280秒。 【使用近似查询】 ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000001555171637.png) 耗时：0.061秒。 7. 对于字符串类型的字段做复杂计算，建议先编码成整数类型，以提升计算性能。 【字符编码前，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_wallet_xxx_mlb_ds; 执行耗时：34秒。 ``` 8. 高基数（大于10W）字段（int类型），使用bitmap做精确去重。 ``` select count(distinct did_int) from dws_wallet_xxx_mlb_ds; 执行耗时：34秒。 ``` ``` select groupBitmapMergeState(arrayReduce('groupBitmapState', [toUInt64(did)])) as user1 from t_r_309; 执行耗时：8秒。 ``` 9. 使用物化视图加速查询。 对于查询方式比较固定的场景，建议使用物化视图，提前做好数据聚合，相对于查询明细表，性能有数量级的提升。 【明细表插入数据】 ``` 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; ``` 【查询明细表】 ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000001529285920.png) 【查询物化视图】 ![](https://support.huaweicloud.com/devg-rule-mrs/zh-cn_image_0000001504091936.png) 【效果对比】 使用物化视图后，遍历的数据量从1亿下降到2000，耗时从0.211秒下降到0.002秒，性能提升100倍。 10. 使用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秒以内。 11. 使用GLOBAL JOIN/IN替换普通的JOIN。 ClickHouse基于分布式表的查询会转换成所有分片的本地表的操作，再汇总结果。实际使用中，join和global join的执行逻辑差别很大，建议使用global join做分布式表查询。 【场景说明】 1. 查询的集群有N个分片（shard）。 2. A_all是分布式表，对应的本地表是A_local。 3. B_all是分布式表，对应的本地表是B_local。 【分布式表直接join示例】 ``` 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操作，存在查询放大的问题。 【分布式表global 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关键字后，查询的放大减少了很多。不过，由于需要将右表汇总再分发到所有机器，如果右表的数据量很大，需要考虑机器的内存，避免内存溢出。 12. 数据压缩算法的选择，建议使用默认的lz4压缩算法。 ClickHouse提供了两种数据压缩方式供选择：LZ4和ZSTD。 默认的LZ4压缩方式，会提供更快的执行效率，但是同时，要付出较多的磁盘容量占用的代价。 13. ReplacingMergeTree表引擎数据查询，需要先做数据去重合并提升性能。 如果使用去重引擎进行数据查询，且使用argMax函数和final关键字，会导致整个查询性能较差，需要提前对重复数据做合并去重optimize操作，查询时候直接查询不需要使用argMax函数和final关键字，提升查询性能。