关于clickhouse:万字长文详述ClickHouse在京喜达实时数据的探索与实践-京东云技术团队

1 前言

京喜达技术部在社区团购场景下采纳JDQ+Flink+Elasticsearch架构来打造实时数据报表。随着业务的倒退 Elasticsearch开始暴露出一些弊病，不适宜大批量的数据查问，高频次深度分页导出导致ES宕机、不能准确去重统计，多个字段聚合计算时性能降落显著。所以引入ClickHouse来解决这些弊病。

数据写入链路是业务数据(binlog)通过解决转换成固定格局的MQ音讯，Flink订阅不同Topic来接管不同生产零碎的表数据，进行关联、计算、过滤、补充根底数据等加工关联汇总成宽表，最初将加工后的DataStream数据流双写入ES和ClickHouse。查问服务通过JSF和物流网关对外裸露提供给内部进行展现，因为ClickHouse将所有计算能力都用在一次查问上，所以不善于高并发查问。咱们通过对局部实时聚合指标接口减少缓存，或者定时工作查问ClickHosue计算指标存储到ES，局部指标不再实时查ClickHouse而是查ES中计算好的指标来抗住并发，并且这种形式可能极大进步开发效率，易保护，可能对立指标口径。

在引入ClickHouse过程中经验各种艰难，消耗大量精力去摸索并一一解决，在这里记录一下心愿可能给没有接触过ClickHouse的同学提供一些方向上的指引防止多走弯路，如果文中有谬误也心愿多蕴含给出指导，欢送大家一起探讨ClickHouse相干的话题。本文偏长但全是干货，请预留40\~60分钟进行浏览。

[]()2 遇到的问题

前文说到遇到了很多艰难，上面这些遇到的问题是本文讲述的重点内容。

咱们该应用什么表引擎
Flink如何写入到ClickHouse
查问ClickHouse为什么要比查问ES慢1\~2分钟
写入分布式表还是本地表
为什么只有某个分片CPU使用率高
如何定位是哪些SQL在耗费CPU，这么多慢SQL，我怎么晓得是哪个SQL引起的
找到了慢SQL，如何进行优化
如何抗住高并发、保障ClickHouse可用性

[]()3 表引擎抉择与查问计划

在抉择表引擎以及查问计划之前，先把需要捋分明。前言中说到咱们是在Flink中结构宽表，在业务上会波及到数据的更新的操作，会呈现同一个业务单号屡次写入数据库。ES的upsert反对这种须要笼罩之前数据的操作，ClickHouse中没有upsert，所以须要摸索出可能反对upsert的计划。带着这个需要来看一下ClickHouse的表引擎以及查问计划。

ClickHouse有很多表引擎，表引擎决定了数据以什么形式存储，以什么形式加载，以及数据表领有什么样的个性。目前ClickHouse表引擎一共分为四个系列，别离是Log、MergeTree、Integration、Special。

Log系列：实用于大量数据(小于一百万行)的场景，不反对索引，所以对于范畴查问效率不高。
Integration系列：次要用于导入内部数据到ClickHouse，或者在ClickHouse中间接操作内部数据，反对Kafka、HDFS、JDBC、Mysql等。
Special系列：比方Memory将数据存储在内存，重启后会失落数据，查问性能极好，File间接将本地文件作为数据存储等大多是为了特定场景而定制的。
MergeTree系列：MergeTree家族本身领有多种引擎的变种，其中MergeTree作为家族中最根底的引擎提供主键索引、数据分区、数据正本和数据采样等能力并且反对极大量的数据写入，家族中其余引擎在MergeTree引擎的根底上各有千秋。

Log、Special、Integration次要用于非凡用处，场景绝对无限。其中最能体现ClickHouse性能特点的是MergeTree及其家族表引擎，也是官网主推的存储引擎，简直反对所有ClickHouse外围性能，在生产环境的大部分场景中都会应用此系列的表引擎。咱们的业务也不例外须要应用主键索引，日数据增量在2500多万的增量，所以MergeTree系列是咱们须要摸索的指标。

MergeTree系列的表引擎是为插入大量数据而生，数据是以数据片段的模式一个接一个的疾速写入，ClickHouse为了防止数据片段过多会在后盾依照肯定的规定进行合并造成新的段，相比在插入时一直的批改曾经存储在磁盘的数据，这种插入后合并再合并的策略效率要高很多。这种数据片段重复合并的特点，也正是MergeTree系列(合并树家族)名称的由来。为了防止造成过多的数据片段，须要进行批量写入。MergeTree系列蕴含MergeTree、ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree、SummingMergeTree、AggregatingMergeTree引擎，上面就介绍下这几种引擎。

[]()3.1 MergeTree：合并树

MergeTree反对所有ClickHouse SQL语法。大部分性能点和咱们相熟的MySQL是相似的，然而有些性能差别比拟大，比方主键，MergeTree系列的主键并不用于去重，MySQL中一个表中不能存在两条雷同主键的数据，然而ClickHouse中是能够的。

上面建表语句中，定义了订单号，商品数量，创立工夫，更新工夫。依照创立工夫进行数据分区，orderNo作为主键(primary key)，orderNo也作为排序键(order by)，默认状况下主键和排序键雷同，大部分状况不须要再专门指定primary key，这个例子中指定只是为了阐明下主键和排序键的关系。当然排序键能够与的主键字段不同，然而主键必须为排序键的子集，例如主键(a,b), 排序键必须为(a,b, , )，并且组成主键的字段必须在排序键字段中的最左侧。

CREATE TABLE test_MergeTree (  orderNo String,  number Int16,  createTime DateTime,  updateTime DateTime) ENGINE = MergeTree()PARTITION BY createTimeORDER BY  (orderNo)PRIMARY KEY (orderNo);insert into test_MergeTree values('1', '20', '2021-01-01 00:00:00', '2021-01-01 00:00:00');insert into test_MergeTree values('1', '30', '2021-01-01 00:00:00', '2021-01-01 01:00:00');

留神这里写入的两条数据主键orderNo都是1的两条数据，这个场景是咱们先创立订单，再更新了订单的商品数量为30和更新工夫，此时业务理论订单量为1，商品件量是30。

插入主键雷同的数据不会产生抵触，并且查问数据两条雷同主键的数据都存在。下图是查问后果，因为每次插入都会造成一个part，第一次insert生成了1609430400\_1\_1\_0 数据分区文件，第二次insert生成了1609430400\_2\_2\_0 数据分区文件，后盾还没触发合并，所以在clickhouse-client上的展现后果是离开两个表格的(图形化查问工具DBeaver、DataGrip不能看出是两个表格，能够通过docker搭建ClickHouse环境通过client形式执行语句，文末有搭建CK环境文档)。

预期后果应该是number从20更新成30，updateTime也会更新成相应的值，同一个业务主键只存在一行数据，可是最终是保留了两条。Clickhouse中的这种解决逻辑会导致咱们查问进去的数据是不正确的。比方去重统计订单数量，count(orderNo)，统计下单件数sum(number)。

上面尝试将两行数据进行合并。

进行强制的分段合并后，还是有两条数据，并不是咱们预期的保留最初一条商品数量为30的数据。然而两行数据合并到了一个表格中，其中的起因是1609430400\_1\_1\_0，1609430400\_2\_2\_0 的partitionID雷同合并成了1609430400\_1\_2\_1这一个文件。合并实现后其中1609430400\_1\_1\_0，1609430400\_2\_2\_0会在肯定工夫(默认8min)后被后盾删除。下图是分区文件的命名规定，partitionID：1609430400 = 2021-01-01 00:00:00，MinBolckNum、MaxBolckNum：是最小数据块最大数据块，是一个整形自增的编号。Level：0能够了解为分区合并过的次数，默认值是0，每次合并过后生成的新的分区后会加1。

综合上述，能够看出MergeTree尽管有主键，但并不是相似MySQL用来放弃记录惟一的去重作用，只是用来查问减速，即便在手动合并之后，主键雷同的数据行也仍旧存在，不能按业务单据去重导致count(orderNo)，sum(number)拿到的后果是不正确的，不实用咱们的需要。

[]()3.2 ReplacingMergeTree：替换合并树

MergeTree尽管有主键，然而不能对雷同主键的数据进行去重，咱们的业务场景不能有反复数据。ClickHouse提供了ReplacingMergeTree引擎用来去重，可能在合并分区时删除反复的数据。我了解的去重分两个方面，一个是物理去重，就是反复的数据间接被删除掉，另一个是查问去重，不解决物理数据，然而查问后果是曾经将反复数据过滤掉的。

示例如下，ReplacingMergeTree建表办法和MergeTree没有特地大的差别，只是ENGINE 由MergeTree更改为ReplacingMergeTree([ver])，其中ver是版本列，是一个选填项，官网给出反对的类型是UInt ，Date或者DateTime，然而我试验Int类型也是能够反对的(ClickHouse 20.8.11)。ReplacingMergeTree在数据合并时物理数据去重，去重策略如下。

如果ver版本列未指定，雷同主键行中保留最初插入的一行。
如果ver版本列曾经指定，上面实例就指定了version列为版本列，去重是将会保留version值最大的一行，与数据插入程序无关。

CREATE TABLE test_ReplacingMergeTree (  orderNo String,  version Int16,  number Int16,  createTime DateTime,  updateTime DateTime) ENGINE = ReplacingMergeTree(version)PARTITION BY createTimeORDER BY  (orderNo)PRIMARY KEY (orderNo);1) insert into test_ReplacingMergeTree values('1', 1, '20', '2021-01-01 00:00:00', '2021-01-01 00:00:00');2) insert into test_ReplacingMergeTree values('1', 2, '30', '2021-01-01 00:00:00', '2021-01-01 01:00:00');3) insert into test_ReplacingMergeTree values('1', 3, '30', '2021-01-02 00:00:00', '2021-01-01 01:00:00');-- final形式去重select * from test_ReplacingMergeTree final;-- argMax形式去重select argMax(orderNo,version) as orderNo, argMax(number,version) as number,argMax(createTime,version),argMax(updateTime,version) from test_ReplacingMergeTree;

下图是在执行完前两条insert语句后进行三次查问的后果，三种形式查问均未对物理存储的数据产生影响，final、argMax形式只是查问后果是去重的。

一般查问：查问后果未去重，物理数据未去重(未合并分区文件)
final去重查问：查问后果已去重，物理数据未去重(未合并分区文件)
argMax去重查问：查问后果已去重，物理数据未去重(未合并分区文件)

其中final和argMax查问形式都过滤掉了反复数据。咱们的示例都是基于本地表做的操作，final和argMax在后果上没有差别，然而如果基于分布式表进行试验，两条数据落在了不同数据分片(留神这里不是数据分区)，那么final和argMax的后果将会产生差别。final的后果将是未去重的，起因是final只能对本地表做去重查问，不能对跨分片的数据进行去重查问，然而argMax的后果是去重的。argMax是通过比拟第二参数version的大小，来取出咱们要查问的最新数据来达到过滤掉反复数据的目标，其原理是将每个Shard的数据搂到同一个Shard的内存中进行比拟计算，所以反对跨分片的去重。

因为后盾的合并是在不确定工夫执行的，执行合并命令，而后再应用一般查问，发现后果曾经是去重后的数据，version=2，number=30 是咱们想保留的数据。

执行第三条insert语句，第三条的主键和前两条统一，然而分区字段createTime字段不同，前两条是2021-01-01 00:00:00，第三条是2021-01-02 00:00:00，如果依照上述的了解，在强制合并会后将会保留version = 3的这条数据。咱们执行一般查问之后发现，version = 1和2的数据做了合并去重，保留了2，然而version=3的还是存在的，这其中的起因ReplacingMergeTree是已分区为单位删除反复数据。前两个insert的分区字段createTime字段雷同，partitionID雷同，所以都合并到了1609430400\_1\_2\_1分区文件，而第三条insert与前两条不统一，不能合并到一个分区文件，不能做到物理去重。最初通过final去重查问发现能够反对查问去重，argMax也是一样的成果未作展现。

ReplacingMergeTree具备如下特点

应用主键作为判断反复数据的惟一键，反对插入雷同主键数据。
在合并分区的时候会触发删除反复数据的逻辑。然而合并的机会不确定，所以在查问的时候可能会有反复数据，然而最终会去重。能够手动调用optimize，然而会引发对数据大量的读写，不倡议生产应用。
以数据分区为单位删除反复数据，当分区合并时，同一分区内的反复数据会被删除，不同分区的反复数据不会被删除。
能够通过final，argMax形式做查问去重，这种形式无论有没有做过数据合并，都能够失去正确的查问后果。

ReplacingMergeTree最佳应用计划

一般select查问：对时效不高的离线查问能够采纳ClickHouse主动合并配合，然而须要保障同一业务单据落在同一个数据分区，分布式表也须要保障在同一个分片(Shard)，这是一种最高效，最节俭计算资源的查问形式。
final形式查问：对于实时查问能够应用final，final是本地去重，须要保障同一主键数据落在同一个分片(Shard)，然而不须要落在同一个数据分区，这种形式效率次之，然而与一般select相比会耗费一些性能，如果where条件对主键索引，二级索引，分区字段命中的比拟好的话效率也能够齐全能够应用。
argMax形式查问：对于实时查问能够应用argMax，argMax的应用要求最低，咋查都能去重，然而因为它的实现形式，效率会低很多，也很耗费性能，不倡议应用。前面9.4.3会配合压测数据与final进行比照。

上述的三种应用计划中其中ReplacingMergeTree配合final形式查问，是合乎咱们需要的。

[]()3.3 CollapsingMergeTree/VersionedCollapsingMergeTree：折叠合并树

折叠合并树不再通过示例来进行阐明。可参考官网示例。

CollapsingMergeTree通过定义一个sign标记位字段，记录数据行的状态。如果sign标记位1(《状态》行)，则示意这是一行无效的数据，如果sign标记位为 -1(《勾销》行)，则示意这行数据须要被删除。须要留神的是数据主键雷同才可能会被折叠。

如果sign=1比sign=-1的数据多至多一行，则保留最初一行sign=1的数据。
如果sign=-1比sign=1多至多一行，则保留第一行sign=-1的行。
如果sign=1与sign=-1的行数一样多，最初一行是sign=1，则保留第一行sign=-1和最初一行sign=1的数据。
如果sign=1与sign=-1的行数一样多，最初一行是sign=-1，则什么都不保留。
其余状况ClickHouse不会报错但会打印告警日志，这种状况下，查问的后果是不确定不可预知的。

在应用CollapsingMergeTree时候须要留神

1）与ReplacingMergeTree一样，折叠数据不是实时触发的，是在分区合并的时候才会体现，在合并之前还是会查问到反复数据。解决形式有两种

应用optimize强制合并，同样也不倡议在生产环境中应用效率极低并且耗费资源的强制合并。
改写查问形式，通过group by 配合有符号的sign列来实现。这种形式减少了应用的编码老本

2）在写入方面通过《勾销》行删除或批改数据的形式须要写入数据的程序记录《状态》行的数据，极大的减少存储老本和编程的复杂性。Flink在上线或者某些状况下会重跑数据，会失落程序中的记录的数据行，可能会造成sign=1与sign=-1不对等不能进行合并，这一点是咱们无奈承受的问题。

CollapsingMergeTree还有一个弊病，对写入的程序有严格的要求，如果依照失常程序写入，先写入sign=1的行再写入sign=-1的行，可能失常合并，如果程序反过来则不能失常合并。ClickHouse提供了VersionedCollapsingMergeTree，通过减少版本号来解决程序问题。然而其余的个性与CollapsingMergeTree完全一致，也不能满足咱们的需要

[]()3.4 表引擎总结

咱们具体介绍了MergeTree系列中的MergeTree、ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree四种表引擎，还有SummingMergeTree、AggregatingMergeTree没有介绍，SummingMergeTree是为不关怀明细数据，只关怀汇总数据设计的表引擎。MergeTree也可能满足这种只关注汇总数据的需要，通过group by配合sum，count聚合函数就能够满足，然而每次查问都进行实时聚合会减少很大的开销。咱们既有明细数据需要，又须要汇总指标需要，所以SummingMergeTree不能满足咱们的需要。AggregatingMergeTree是SummingMergeTree升级版，实质上还是雷同的，区别在于：SummingMergeTree对非主键列进行sum聚合，而AggregatingMergeTree则能够指定各种聚合函数。同样也满足不了需要。

最终咱们选用了ReplacingMergeTree引擎，分布式表通过业务主键sipHash64(docId)进行shard保障同一业务主键数据落在同一分片，同时应用业务单据创立工夫按月/按天进行分区。配合final进行查问去重。这种计划在双十一期间数据日增3000W，业务顶峰数据库QPS93，32C 128G 6分片 2正本的集群CPU使用率最高在60%，零碎整体稳固。下文的所有实际优化也都是基于ReplacingMergeTree引擎。

[]()4 Flink如何写入ClickHouse

[]()4.1 Flink版本问题

Flink反对通过JDBC Connector将数据写入JDBC数据库，然而Flink不同版本的JDBC connector写入形式有很大区别。因为Flink在1.11版本对JDBC Connector进行了一次较大的重构：

1.11版本之前包名为flink-jdbc
1.11版本(蕴含)之后包名为flink-connector-jdbc

两者对Flink中以不同形式写入ClickHouse Sink的反对状况如下:

起初咱们应用1.10.3版本的Flink，flink-jdbc不反对应用DataStream流写入，须要降级Flink版本至1.11.x及以上版本来应用flink-connector-jdbc来写入数据到ClickHouse。

[]()4.2 结构ClickHouse Sink

/** * 结构Sink * @param clusterPrefix clickhouse 数据库名称 * @param sql   insert 占位符 eq：insert into demo (id, name) values (?, ?) */public static SinkFunction getSink(String clusterPrefix, String sql) {    String clusterUrl = LoadPropertiesUtil.appInfoProcessMap.get(clusterPrefix + CLUSTER_URL);    String clusterUsername = LoadPropertiesUtil.appInfoProcessMap.get(clusterPrefix + CLUSTER_USER_NAME);    String clusterPassword = LoadPropertiesUtil.appInfoProcessMap.get(clusterPrefix + CLUSTER_PASSWORD);    return JdbcSink.sink(sql, new CkSinkBuilder<>(),            new JdbcExecutionOptions.Builder().withBatchSize(200000).build(),             new JdbcConnectionOptions.JdbcConnectionOptionsBuilder()                    .withDriverName("ru.yandex.clickhouse.ClickHouseDriver")                    .withUrl(clusterUrl)                    .withUsername(clusterUsername)                    .withPassword(clusterPassword)                    .build());}

应用flink-connector-jdbc的JdbcSink.sink() api来结构Flink sink。JdbcSink.sink()入参含意如下

sql：占位符模式的sql语句，例如：insert into demo (id, name) values (?, ?)
new CkSinkBuilder<>()：org.apache.flink.connector.jdbc.JdbcStatementBuilder接口的实现类，次要是将流中数据映射到java.sql.PreparedStatement 来结构PreparedStatement ，具体不再赘述。
第三个入参：flink sink的执行策略。
第四个入参：jdbc的驱动，连贯，账号与明码。

应用时间接在DataStream流中addSink即可。

[]()5 Flink写入ClickHouse策略

Flink同时写入ES和Clikhouse，然而在进行数据查问的时候发现ClickHouse永远要比ES慢一些，开始狐疑是ClickHouse合并等解决会消耗一些工夫，然而ClickHouse这些合并操作不会影响查问。起初查阅Flink写入策略代码发现是咱们应用的策略有问题。

上段(4.2)代码中new JdbcExecutionOptions.Builder().withBatchSize(200000).build()为写入策略，ClickHouse为了进步写入性能倡议进行不少于1000行的批量写入，或每秒不超过一个写入申请。策略是20W行记录进行写入一次，Flink进行Checkpoint的时候也会进行写入提交。所以当数据量积攒到20W或者Flink忘性Checkpoint的时候ClickHouse外面才会有数据。咱们的ES sink策略是1000行或5s进行写入提交，所以呈现了写入ClickHouse要比写入ES慢的景象。

达到20W或者进行Checkpoint的时候进行提交有一个弊病，当数据量小达不到20W这个量级，Checkpoint工夫距离t1，一次checkpoint工夫为t2，那么从接管到JDQ音讯到写入到ClickHouse最长工夫距离为t1+t2，齐全依赖Checkpoint工夫，有时候有数据积压最慢有1\~2min。进而对ClickHouse的写入策略进行优化，new JdbcExecutionOptions.Builder().withBatchIntervalMs(30 * 1000).build() 优化为没30s进行提交一次。这样如果Checkpoint慢的话能够触发30s提交策略，否则Checkpoint的时候提交，这也是一种比拟折中的策略，能够依据本人的业务个性进行调整，在调试提交工夫的时候发现如果距离过小，zookeeper的cpu使用率会晋升，10s提交一次zk使用率会从5%以下晋升到10%左右。

Flink中的org.apache.flink.connector.jdbc.internal.JdbcBatchingOutputFormat#open解决逻辑如下图。

[]()6 写入分布式表还是本地表

先说后果，咱们是写入分布式表。\
网上的材料和ClickHouse云服务的共事都倡议写入本地表。分布式表实际上是一张逻辑表并不存储实在的物理数据。如查问分布式表，分布式表会把查问申请发到每一个分片的本地表上进行查问，而后再汇合每个分片本地表的后果，汇总之后再返回。写入分布式表，分布式表会依据肯定规定，将写入的数据依照规定存储到不同的分片上。如果写入分布式表也只是单纯的网络转发，影响也不大，然而写入分布式表并非单纯的转发，理论状况见下图。

有三个分片S1、S2、S3，客户端连贯到S1节点，进行写入分布式表操作。

第一步：写入分布式表1000条数据，分布式表会依据路由规定，假如依照规定300条调配到S1，200条到S2，500条到S3
第二步：client给过去1000条数据，属于S1的300条数据间接写入磁盘，数据S2,S3的数据也会写入到S1的长期目录
第三步：S2,S3接管到zk的变更告诉，生成拉取S1中以后分片对应的长期目录数据的工作，并且将工作放到一个队列，等到某个时机会将数据拉到本身节点。

从分布式表的写入形式能够看到，会将所有数据落到client连贯分片的磁盘上。如果数据量大，磁盘的IO会造成瓶颈。并且MergeTree系列引擎存在合并行为，自身就有写放大(一条数据合并屡次)，占用肯定磁盘性能。在网上看到写入本地表的案例都是日增量百亿，千亿。咱们抉择写入分布式表次要有两点，一是简略，因为写入本地表须要革新代码，本人指定写入哪个节点，另一个是开发过程中写入本地表并未呈现什么重大的性能瓶颈。双十一期间数据日增3000W(合并后)行并未造成写入压力。如果后续产生瓶颈，可能会放弃写入分布式表。

[]()7 为什么只有某个分片CPU使用率高

[]()7.1 数据分布不平均，导致局部节点CPU高

上图是在接入ClickHouse过程中遇到的一个问题，其中7-1节点CPU使用率十分高，不同节点的差别十分大。起初通过SQL定位发现不同节点上的数据量差别也十分大，其中7-1节点数据量是最多的，导致7-1节点相比其余节点须要解决的数据行数十分多，所以CPU绝对会高很多。因为咱们应用网格站编码，分拣仓编码hash后做分布式表的数据分片策略，然而分拣仓编码和网站编码的基数比拟小，导致hash后不够扩散造成这种数据歪斜的景象。起初改用业务主键做hash，解决了这种局部节点CPU高的问题。

[]()7.2 某节点触发合并，导致该节点CPU高

7-4节点(主节点和正本)，CPU毫无征兆的比其余节点高很多，在排除新业务上线、大促等突发状况后进行慢SQL定位，通过query\_log进行剖析每个节点的慢查问，具体语句见第8大节。

通过两个节点的慢SQL进行比照，发现是如下SQL的查问状况有较大差别。

SELECT    ifNull(sum(t1.unTrackQty), 0) AS unTrackQtyFROM    wms.wms_order_sku_local AS t1 FINAL PREWHERE t1.shipmentOrderCreateTime > '2021-11-17 11:00:00'    AND t1.shipmentOrderCreateTime <= '2021-11-18 11:00:00'    AND t1.gridStationNo = 'WG0000514'    AND t1.warehouseNo NOT IN ('wms-6-979', 'wms-6-978', '6_979', '6_978')    AND t1.orderType = '10'WHERE    t1.ckDeliveryTaskStatus = '3'

然而咱们有个纳闷，同样的语句，同样的执行次数，而且两个节点的数据量，part数量都没有差别，为什么7-4节点扫描的行数是7-0上的5倍，把这个起因找到，应该就能定位到问题的根本原因了。\
接下来咱们应用clickhouse-client进行SQL查问，开启trace级别日志，查看SQL的执行过程。具体执行形式以及查问日志剖析参考下文9.1大节，这里咱们间接剖析后果。

下面两张图能够剖析出

7-0节点：扫描了4个part分区文件，共计94W行，耗时0.089s
7-4节点：扫描了2个part分区文件，其中有一个part491W行，共计502W行，耗时0.439s

很显著7-4节点的202111\_0\_408188\_322这个分区比拟异样，因为咱们是按月分区的，7-4节点不晓得什么起因产生了分区合并，导致咱们检索的11月17号的数据落到了这个大分区上，所以然而查问会过滤11月初到18号的所有数据，和7-0节点产生了差别。上述的SQL通过 gridStationNo = ‘WG0000514’ 条件进行查问，所以在对gridStationNo 字段进行创立二级索引后解决了这个问题。

在减少加二级索引后7-4节点：扫描了2个part分区文件，共计38W行，耗时0.103s。

[]()7.3 物理机故障

这种状况少见，然而也遇到过一次

[]()8 如何定位是哪些SQL在耗费CPU

我认为能够通过两个方向来排查问题，一个是SQL执行频率是否过高，另一个方向是判断是否有慢SQL在执行，高频执行或者慢查问都会大量耗费CPU的计算资源。上面通过两个案例来阐明一下排查CPU偏高的两种无效办法，上面两种尽管操作上是不同的，然而外围都是通过剖析query\_log来进行剖析定位的。

[]()8.1 grafana定位高频执行SQL

在12月份上线了一些需要，最近发现CPU使用率比照来看使用率偏高，须要排查具体是哪些SQL导致的。

通过上图自行搭建的grafana监控能够看出(搭建文档)，有几个查问语句执行频率十分高，通过SQL定位到查问接口代码逻辑，发现一次前端接口申请后端接口会执行多条类似条件的SQL语句，只是业务状态不雷同。这种须要统计不同类型、不同状态的语句，能够进行条件聚合进行优化，9.4.1大节细讲。优化后语句执行频率极大的升高。

[]()8.2 扫描行数高/应用内存高：query\_log\_all剖析

上节说SQL执行频率高，导致CPU使用率高。如果SQL频率执行频率很低很低，然而CPU还是很高该怎么解决。SQL执行频率低，可能存在扫描的数据行数很大的状况，耗费的磁盘IO，内存，CPU这些资源很大，这种状况下就须要换个伎俩来排查进去这个很坏很坏的SQL(T⌓T)。

ClickHouse本身有system.query\_log表，用于记录所有的语句的执行日志，下图是该表的一些关键字段信息

-- 创立query_log分布式表CREATE TABLE IF NOT EXISTS system.query_log_allON CLUSTER defaultAS system.query_logENGINE = Distributed(sht_ck_cluster_pro,system,query_log,rand());-- 查问语句select     -- 执行次数    count(), -- 均匀查问工夫    avg(query_duration_ms) avgTime,    -- 均匀每次读取数据行数    floor(avg(read_rows)) avgRow,    -- 均匀每次读取数据大小    floor(avg(read_rows) / 10000000) avgMB,    -- 具体查问语句    any(query),    -- 去除掉where条件，用户group by归类    substring(query, positionCaseInsensitive(query, 'select'), positionCaseInsensitive(query, 'from')) as queryLimitfrom system.query_log_all/system.query_logwhere event_date = '2022-01-21'  and type = 2group by queryLimitorder by avgRow desc;

query\_log是本地表，须要创立分布式表，查问所有节点的查问日志，而后再执行查问剖析语句，执行成果见下图，图中能够看出有几个语句均匀扫秒行数曾经到了亿级别，这种语句可能就存在问题。通过扫描行数能够剖析出索引，查问条件等不合理的语句。7.2中的某个节点CPU偏高就是通过这种形式定位到有问题的SQL语句，而后进一步排查从而解决的。

[]()9 如何优化慢查问

ClickHouse的SQL优化比较简单，查问的大部分耗时都在磁盘IO上，能够参考下这个小试验来了解。外围优化方向就是升高ClickHouse单次查询处理的数据量，也就是升高磁盘IO。上面介绍下慢查问剖析伎俩、建表语句优化形式，还有一些查问语句优化。

[]()9.1 应用服务日志进行慢查问剖析

尽管ClickHouse在20.6版本之后曾经提供查看查问打算的原生EXPLAIN，然而提供的信息对咱们进行慢SQL优化提供的帮忙不是很大，在20.6版本前借助后盾的服务日志，能够拿到更多的信息供咱们剖析。与EXPLAIN相比我更偏向于应用查看服务日志这种形式进行剖析，这种形式须要应用clickhouse-client进行执行SQL语句，文末有通过docker搭建CK环境文档。高版本的EXPLAIN提供了ESTIMATE能够查问到SQL语句扫描的part数量、数据行数等细粒度信息，EXPLAIN应用形式能够参考官网文档阐明。\
用一个慢查问来进行剖析，通过8.2中的query\_log\_all定位到下列慢SQL。

select    ifNull(sum(interceptLackQty), 0) as interceptLackQtyfrom wms.wms_order_sku_local final    prewhere productionEndTime = '2022-02-17 08:00:00'    and orderType = '10'where shipmentOrderDetailDeleted = '0'  and ckContainerDetailDeleted = '0'

应用clickhouse-client，send\_logs\_level参数指定日志级别为trace。

clickhouse-client -h 地址 --port 端口 --user 用户名 --password 明码 --send_logs_level=trace

在client中执行上述慢SQL，服务端打印日志如下，日志量较大，省去局部局部行，不影响整体日志的完整性。

[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.036317 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Debug> executeQuery: (from 11.77.96.163:35988, user: bjwangjiangbo) select ifNull(sum(interceptLackQty), 0) as interceptLackQty from wms.wms_order_sku_local final prewhere productionEndTime = '2022-02-17 08:00:00' and orderType = '10' where shipmentOrderDetailDeleted = '0' and ckContainerDetailDeleted = '0'[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.037876 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> ContextAccess (bjwangjiangbo): Access granted: SELECT(orderType, interceptLackQty, productionEndTime, shipmentOrderDetailDeleted, ckContainerDetailDeleted) ON wms.wms_order_sku_local[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038239 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Debug> wms.wms_order_sku_local (SelectExecutor): Key condition: unknown, unknown, and, unknown, unknown, and, and, unknown, unknown, and, and[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038271 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Debug> wms.wms_order_sku_local (SelectExecutor): MinMax index condition: unknown, unknown, and, unknown, unknown, and, and, unknown, unknown, and, and[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038399 [ 1340 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202101_0_0_0_3[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038475 [ 1407 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202103_0_17_2_22[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038491 [ 111 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202103_18_20_1_22..................................省去若干行(此块含意为：在分区内检索有没有应用索引).................................................[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.039041 [ 1205 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202202_1723330_1723365_7[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.039054 [ 159 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202202_1723367_1723367_0[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.038928 [ 248 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> wms.wms_order_sku_local (SelectExecutor): Not using primary index on part 202201_3675258_3700711_1054[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.039355 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Debug> wms.wms_order_sku_local (SelectExecutor): Selected 47 parts by date, 47 parts by key, 9471 marks by primary key, 9471 marks to read from 47 ranges[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.039495 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202101_0_0_0_3, approx. 65536 rows starting from 0[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.039583 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202101_1_1_0_3, approx. 16384 rows starting from 0[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.040291 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202102_0_2_1_4, approx. 146850 rows starting from 0..................................省去若干行（每个分区读取的数据行数信息）.................................................[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.043538 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202202_1723330_1723365_7, approx. 24576 rows starting from 0[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.043604 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202202_1723366_1723366_0, approx. 8192 rows starting from 0[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.043677 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> MergeTreeSelectProcessor: Reading 1 ranges from part 202202_1723367_1723367_0, approx. 8192 rows starting from 0..................................实现数据读取，开始进行聚合计算.................................................[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.047880 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> InterpreterSelectQuery: FetchColumns -> Complete[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.263500 [ 1377 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> AggregatingTransform: Aggregating[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.263680 [ 1439 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> Aggregator: Aggregation method: without_key..................................省去若干行（数据读取实现后做聚合操作）.................................................[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.263840 [ 156 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> AggregatingTransform: Aggregated. 12298 to 1 rows (from 36.03 KiB) in 0.215046273 sec. (57187.69187876137 rows/sec., 167.54 KiB/sec.)[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.264283 [ 377 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> AggregatingTransform: Aggregated. 12176 to 1 rows (from 35.67 KiB) in 0.215476999 sec. (56507.191284950095 rows/sec., 165.55 KiB/sec.)[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.264307 [ 377 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Trace> Aggregator: Merging aggregated data..................................实现聚合计算，返回最终后果.................................................┌─interceptLackQty─┐│              563 │└──────────────────┘...................................数据处理耗时，速度，信息展现................................................[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.265490 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Information> executeQuery: Read 73645604 rows, 1.20 GiB in 0.229100749 sec., 321455099 rows/sec., 5.22 GiB/sec.[chi-ck-t8ebn40kv7-3-0-0] 2022.02.17 21:21:54.265551 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} <Debug> MemoryTracker: Peak memory usage (for query): 60.37 MiB.1 rows in set. Elapsed: 0.267 sec. Processed 73.65 million rows, 1.28 GB (276.03 million rows/s., 4.81 GB/s.)

当初剖析下，从上述日志中可能拿到什么信息，首先该查问语句没有应用主键索引，具体信息如下

2022.02.17 21:21:54.038239 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} wms.wms\_order\_sku\_local (SelectExecutor): Key condition: unknown, unknown, and, unknown, unknown, and, and, unknown, unknown, and, and

同样也没有应用分区索引，具体信息如下

2022.02.17 21:21:54.038271 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} wms.wms\_order\_sku\_local (SelectExecutor): MinMax index condition: unknown, unknown, and, unknown, unknown, and, and, unknown, unknown, and, and

此次查问一共扫描36个parts，9390个MarkRange，通过查问system.parts零碎分区信息表发现以后表一共领有36个沉闷的分区，相当于全表扫描。

2022.02.17 21:44:58.012832 [ 1138 ] {f1561330-4988-4598-a95d-bd12b15bc750} wms.wms\_order\_sku\_local (SelectExecutor): Selected 36 parts by date, 36 parts by key, 9390 marks by primary key, 9390 marks to read from 36 ranges

此次查问总共读取了73645604 行数据，这个行数也是这个表的总数据行数，读取耗时0.229100749s，共读取1.20GB的数据。

2022.02.17 21:21:54.265490 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} executeQuery: Read 73645604 rows, 1.20 GiB in 0.229100749 sec., 321455099 rows/sec., 5.22 GiB/sec.

此次查问语句耗费的内存最大为60.37MB

2022.02.17 21:21:54.265551 [ 618 ] {ea8f56fe-cf2b-4260-8f44-a006458bdab3} MemoryTracker: Peak memory usage (for query): 60.37 MiB.

最初汇总了下信息，此次查问总共消耗了0.267s，解决了7365W数据，共1.28GB，并且给出了数据处理速度。

1 rows in set. Elapsed: 0.267 sec. Processed 73.65 million rows, 1.28 GB (276.03 million rows/s., 4.81 GB/s.)

通过上述能够发现两点重大问题

没有应用主键索引：导致全表扫描
没有应用分区索引：导致全表扫描

所以须要再查问条件上增加主键字段或者分区索引来进行优化。

shipmentOrderCreateTime为分区键，在增加这个条件后再看下成果。

通过剖析日志能够看到没有应用主键索引，然而应用了分区索引，扫描分片数为6，MarkRange 186，共扫描1409001行数据，应用内存40.76MB，扫描数据大小等大幅度降低节俭大量服务器资源，并且晋升了查问速度，0.267s升高到0.18s。

[]()9.2 建表优化

[]()9.2.1 尽量不应用Nullable类型

从实际上看，设置成Nullable对性能影响也没有多大，可能是因为咱们数据量比拟小。不过官网曾经明确指出尽量不要应用Nullable类型，因为Nullable字段不能被索引，而且Nullable列除了有一个存储正常值的文件，还会有一个额定的文件来存储Null标记。

Using Nullable almost always negatively affects performance, keep this in mind when designing your databases.

CREATE TABLE test_Nullable(  orderNo String,  number Nullable(Int16),  createTime DateTime) ENGINE = MergeTree()PARTITION BY createTimeORDER BY  (orderNo)PRIMARY KEY (orderNo);

上述建表语句为例，number 列会生成number.null.*两个额定文件，占用额定存储空间，而orderNo列则没有额定的null标识的存储文件。

咱们理论利用中建表，难免会遇到这种可能为null的字段，这种状况下能够应用不可能呈现的一个值作为默认值，例如将状态字段都是0及以上的值，那么能够设置为-1为默认值，而不是应用nullable。

[]()9.2.2 分区粒度

分区粒度依据业务场景个性来设置，不宜过粗也不宜过细。咱们的数据个别都是依照工夫来严格划分，所以都是按天、按月来划分分区。如果索引粒度过细按分钟、按小时等划分会产生大量的分区目录，更不能间接PARTITION BY create\_time ，会导致分区数量惊人的多，简直每条数据都有一个分区会重大的影响性能。如果索引粒度过粗，会导致单个分区的数据量级比拟大，下面7.2节的问题和索引粒度也有关系，按月分区，单个分区数据量达到500W级，数据范畴1号到18号，只查问17号，18号两天的数据量，然而优化按月分区，分区合并之后不得不解决不相干的1号到16号的额定数据，如果按天分区就不会产生CPU飙升的景象。所以要依据本人业务个性来创立，放弃一个准则就是查问只解决本次查问条件范畴内的数据，不额定解决不相干的数据。

[]()9.2.3 分布式表抉择适合的分片规定

以上文7.1中为例，分布式表抉择的分片规定不合理，导致数据歪斜重大落到了少数几个分片中。没有施展出分布式数据库整个集群的计算能力，而是把压力全压在了少部分机器上。这样整体集群的性能必定是上不来的，所以依据业务场景抉择适合的分片规定，比方咱们将sipHash64(warehouseNo)优化为sipHash64(docId)，其中docId是业务上惟一的一个标识。

[]()9.3 性能测试，比照优化成果

在聊查问优化之前先说一个小工具，clickhouse提供的一个clickhouse-benchmark性能测试工具，环境和前文提到的一样通过docker搭建CK环境，压测参数可参考官网文档，这里我举一个简略的单并发测试示例。

clickhouse-benchmark -c 1 -h 链接地址 --port 端口号 --user 账号 --password 明码 <<< "具体SQL语句"

通过这种形式能够理解SQL级别的QPS和TP99等信息，这样就能够测试语句优化前后的性能差别。

[]()9.4 查问优化

[]()9.4.1 条件聚合函数升高扫描数据行数

假如一个接口要统计某天的”入库件量”，”无效出库单量”，”复核件量”。

-- 入库件量select sum(qty) from table_1 final prewhere type = 'inbound' and dt = '2021-01-01';-- 无效出库单量select count(distinct orderNo) final from table_1 prewhere type = 'outbound' and dt = '2021-01-01' where and status = '1' ;-- 复核件量select sum(qty) from table_1 final prewhere type = 'check' and dt = '2021-01-01';

一个接口出三个指标须要上述三个SQL语句查问table\_1 来实现，然而咱们不难发现dt是统一的，区别在于type和status两个条件。假如dt = ‘2021-01-1’ 每次查问须要扫描100W行数据，那么一次接口申请将会扫描300W行数据。通过条件聚合函数优化后将三次查问改成一次，那么扫描行数将升高为100W行，所以能极大的节俭集群的计算资源。

select sumIf(qty, type = 'inbound'), -- 入库件量countIf(distinct orderNo, type = 'outbound' and status = '1'), -- 无效出库单量sumIf(qty, type = 'check') -- 复核件量prewhere dt = '2021-01-01';

条件聚合函数是比拟灵便的，可依据本人业务状况自由发挥，记住一个主旨就是缩小整体的扫描量，就能达到晋升查问性能的目标。

[]()9.4.2 二级索引

MergeTree 系列的表引擎能够指定跳数索引。\
跳数索引是指数据片段依照粒度(建表时指定的index\_granularity)宰割成小块后，将granularity\_value数量的小块组合成一个大的块，对这些大块写入索引信息，这样有助于应用where筛选时跳过大量不必要的数据，缩小SELECT须要读取的数据量。

CREATE TABLE table_name(    u64 UInt64,    i32 Int32,    s String,    ...    INDEX a (u64 * i32, s) TYPE minmax GRANULARITY 3,    INDEX b (u64 * length(s)) TYPE set(1000) GRANULARITY 4) ENGINE = MergeTree()...

上例中的索引能让 ClickHouse 执行上面这些查问时缩小读取数据量。

SELECT count() FROM table WHERE s < 'z'SELECT count() FROM table WHERE u64 * i32 == 10 AND u64 * length(s) >= 1234

反对的索引类型

minmax：以index granularity为单位，存储指定表达式计算后的min、max值；在等值和范畴查问中可能帮忙疾速跳过不满足要求的块，缩小IO。
set(max\_rows)：以index granularity为单位，存储指定表达式的distinct value汇合，用于疾速判断等值查问是否命中该块，缩小IO。
ngrambf\_v1(n, size\_of\_bloom\_filter\_in\_bytes, number\_of\_hash\_functions, random\_seed)：将string进行ngram分词后，构建bloom filter，可能优化等值、like、in等查问条件。
tokenbf\_v1(size\_of\_bloom\_filter\_in\_bytes, number\_of\_hash\_functions, random\_seed)：与ngrambf\_v1相似，区别是不应用ngram进行分词，而是通过标点符号进行词语宰割。
bloom\_filter([false\_positive])：对指定列构建bloom filter，用于减速等值、like、in等查问条件的执行。

创立二级索引示例

Alter table wms.wms_order_sku_local ON cluster default ADD INDEX belongProvinceCode_idx belongProvinceCode TYPE set(0) GRANULARITY 5;Alter table wms.wms_order_sku_local ON cluster default ADD INDEX productionEndTime_idx productionEndTime TYPE minmax GRANULARITY 5;

重建分区索引数据：在创立二级索引前插入的数据，不能走二级索引，须要重建每个分区的索引数据后能力失效

-- 拼接出所有数据分区的MATERIALIZE语句select concat('alter table wms.wms_order_sku_local on cluster default ', 'MATERIALIZE INDEX productionEndTime_idx in PARTITION '||partition_id||',')from system.partswhere database = 'wms' and table = 'wms_order_sku_local'group by partition_id-- 执行上述SQL查问出的所有MATERIALIZE语句进行重建分区索引数据

[]()9.4.3 final替换argMax进行去重

比照下final和argMax两种形式的性能差距，如下SQL

-- final形式select count(distinct groupOrderCode), sum(arriveNum), count(distinct sku) from tms.group_order final prewhere siteCode = 'WG0001544' and createTime >= '2022-03-14 22:00:00' and createTime <= '2022-03-15 22:00:00' where arriveNum > 0 and test <> '1'-- argMax形式select count(distinct groupOrderCode), sum(arriveNumTemp), count(distinct sku) from (select argMax(groupOrderCode,version) as groupOrderCode, argMax(arriveNum,version) as arriveNumTemp, argMax(sku,version) as sku from tms.group_order prewhere siteCode = 'WG0001544' and createTime >= '2022-03-14 22:00:00' and createTime <= '2022-03-15 22:00:00' where arriveNum > 0 and test <> '1' group by docId)

final形式的TP99显著要比argMax形式优良很多

[]()9.4.4 prewhere代替where

ClickHouse的语法反对了额定的prewhere过滤条件，它会先于where条件进行判断，能够看做是更高效率的where，作用都是过滤数据。当在sql的filter条件中加上prewhere过滤条件时，存储扫描会分两阶段进行，先读取prewhere表达式中依赖的列值存储块，查看是否有记录满足条件，在把满足条件的其余列读出来，以下述的SQL为例，其中prewhere形式会优先扫描type，dt字段，将符合条件的列取出来，当没有任何记录满足条件时，其余列的数据就能够跳过不读了。相当于在Mark Range的根底上进一步放大扫描范畴。prewhere相比where而言，解决的数据量会更少，性能会更高。看这段话可能不太容易了解，

-- 惯例形式select count(distinct orderNo) final from table_1 where type = 'outbound' and status = '1' and dt = '2021-01-01';-- prewhere形式select count(distinct orderNo) final from table_1 prewhere type = 'outbound' and dt = '2021-01-01' where and status = '1' ;

上节咱们说了应用final进行去重优化。通过final去重，并且应用prewhere进行查问条件优化时有个坑须要留神，prewhere会优先于final进行执行，所以对于status这种值可变的字段处理过程中，可能查问到中间状态的数据行，导致最终数据不统一。

如上图所示，docId:123\_1的业务数据，进行三次写入，到version=103的数据是最新版本数据，当咱们应用where过滤status这个可变值字段时，语句1，语句2后果如下。

--语句1：应用where + status=1 查问，无奈命中docId:123_1这行数据select count(distinct orderNo) final from table_1 where type = 'outbound' and dt = '2021-01-01' and status = '1';--语句2：应用where + status=2 查问，能够查问到docId:123_1这行数据select count(distinct orderNo) final from table_1 where type = 'outbound' and dt = '2021-01-01' and status = '2';

当咱们引入prewhere后，语句3写法：prewhere过滤status字段时将status=1，version=102的数据会过滤出来，导致咱们查问后果不正确。正确的写法是语句2，将不可变字段应用prewhere进行优化。

-- 语句3：谬误形式，将status放到prewhereselect count(distinct orderNo) final from table_1 prewhere type = 'outbound' and dt = '2021-01-01' and status = '1';-- 语句4：正确prewhere形式，status可变字段放到where上select count(distinct orderNo) final from table_1 prewhere type = 'outbound' and dt = '2021-01-01' where and status = '1' ;

其余限度：prewhere目前只能用于MergeTree系列的表引擎

[]()9.4.5 列裁剪，分区裁剪

ClickHouse 非常适合存储大数据量的宽表，因而咱们应该防止应用 SELECT * 操作，这是一个十分影响的操作。该当对列进行裁剪，只抉择你须要的列，因为字段越少，耗费的 IO 资源就越少，从而性能就越高。\
而分区裁剪就是只读取须要分区，管制好分区字段查问范畴。

[]()9.4.6 where、group by 程序

where和group by中的列程序，要和建表语句中order by的列程序对立，并且放在最后面使得它们有间断不间断的公共前缀，否则会影响查问性能。

-- 建表语句create table group_order_local(    docId              String,    version            UInt64,    siteCode           String,    groupOrderCode     String,    sku                String,    ... 省略非关键字段 ...     createTime         DateTime) engine = ReplicatedReplacingMergeTree('/clickhouse/tms/group_order/{shard}', '{replica}', version)PARTITION BY toYYYYMM(createTime)ORDER BY (siteCode, groupOrderCode, sku);--查问语句1select count(distinct groupOrderCode) groupOrderQty, ifNull(sum(arriveNum),0) arriveNumSum,count(distinct sku) skuQtyfrom  tms.group_order finalprewhere createTime >= '2021-09-14 22:00:00' and createTime <= '2021-09-15 22:00:00'and siteCode = 'WG0000709'where arriveNum > 0 and test <> '1'--查问语句2 （where/prewhere中字段）select count(distinct groupOrderCode) groupOrderQty, ifNull(sum(arriveNum),0) arriveNumSum,count(distinct sku) skuQtyfrom  tms.group_order finalprewhere siteCode = 'WG0000709' and createTime >= '2021-09-14 22:00:00' and createTime <= '2021-09-15 22:00:00'where arriveNum > 0 and test <> '1'

建表语句 ORDER BY (siteCode, groupOrderCode, sku)，语句1没有符合要求通过压测QPS6.4，TP99 0.56s，语句2符合要求通过压测QPS 14.9，TP99 0.12s

[]()10 如何抗住高并发、保障ClickHouse可用性

1）升高查问速度，进步吞吐量

max\_threads：位于 users.xml 中，示意单个查问所能应用的最大 CPU 个数，默认是 CPU 核数，如果机器是32C，则会起32个线程来解决以后申请。能够把max\_threads调低，就义单次查问速度来保障ClickHouse的可用性，晋升并发能力。可通过jdbc的url来配置

下图是基于32C128G配置，在保障CK集群可能提供稳固服务CPU使用率在50%的状况下针对max\_threads做的一个压测，接口级别压测，一次申请执行5次SQL，解决数据量508W行。能够看出max\_threads越小，QPS越优良TP99越差。可依据本身业务状况来进行调整一个适合的配置值。

2）接口减少肯定工夫的缓存\
3）异步工作执行查问语句，将聚合指标后果落到ES中，利用查问ES中的聚合后果\
4）物化视图，通过预聚合形式解决这种问题，然而咱们这种业务场景不实用

11 材料汇合

•建库、建表、创立二级索引等操作

•更改ORDER BY字段，PARTITION BY，备份数据，单表迁徙数据等操作

•基于docker搭建clickhouse-client链接ck集群

•基于docker搭建grafana监控SQL执行状况

•test环境自行搭建clickhouse

作者：京东物流马红岩
内容起源：京东云开发者社区