共计 29669 个字符，预计需要花费 75 分钟才能阅读完成。
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
作者：京东物流 马红岩
内容起源：京东云开发者社区