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 数据分区文件，第2次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 128G6分片 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
作者：京东物流 马红岩
内容来历：京东云开发者社区