clickhouse+springboot+mybatisplus实践和调优

背景和参考

一天接到一个需求，对于一个已经确定数据库选型为clickhouse的项目做开发。考虑到现在行业已经很不景气了，降本增效是很重要的。能否借助mybatisplus实现快速开发呢？
众所周知，clickhouse使用带有方言的SQL语法,而mybatisplus通过反射实体类生成SQL语句。clickhouse提供了jdbc驱动，mybatis和mybatisplus都可以在jdbc驱动上工作，不太需要关注底层驱动的具体实现。mybatisplus可以支持clickhouse的可能性是很高的。
参考搜索到的一些博客
https://blog.csdn.net/fx9590/article/details/105163804
https://juejin.cn/post/7087931138072641549
开始折腾！

引入clickhouse+mybatisPlus

这里我是用的是2.5.2版本的springboot

<parent>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-parent</artifactId>
   <version>2.5.2</version>
   <relativePath/> <!-- lookup parent from repository -->
</parent>

通过maven引入必须的依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.1-patch</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-extension</artifactId>
    <version>3.5.3.1</version>
</dependency>

这里引入mybatis-plus-extension是为了使用分页插件。
引入依赖后，在application.yml中进行配置。这里我使用的是spring datasourse和hikariCP。

spring:
  datasource:
    url: jdbc:clickhouse://x.x.x.x:x/databaseName
    username: name
    password: password
    driver-class-name: ru.yandex.clickhouse.ClickHouseDriver
    hikari:
      maximum-pool-size: 100
      minimum-idle: 5
      connection-timeout: 300000
      idle-timeout: 600000
      max-lifetime: 1800000

然后需要配置mybatisplus分页插件。对于我这个版本引用的mybatisplus，其配置方法如下

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.CLICK_HOUSE));
        return mybatisPlusInterceptor;
    }
}

然后通过继承mybatisplus的BaseMapper编写Mapper接口。

@Mapper
public interface MyEntityMapper extends BaseMapper<MyEntity> {
}

然后通过@MapperScan注解，在启动类上标注需要扫描mapper的包路径，即刚在写的MyEntityMapper所在的包路径。

@SpringBootApplication
@MapperScan("org.mywork.mapper")
public class MyworkApplication {

    public static void main(String[] args) {
        SpringApplication.run(MyworkApplication.class, args);
    }

}

在实体类中，使用@TableName和@TableField标注实体类所对应的表和字段。

@TableName("t_mytable")
@Data
public class MyEntity{
    @TableField("my_field")
    private String muField;
}

方言问题

配置结束，大功告成...了吗？
如果认为这样就完事那就大错特错了，因为clickhouse的sql方言与mysql有很大区别，比如UPDATE语法。
在我的首次实践中，在测试环境中尝试更新数据时jdbc报错了，原因是clickhouse在使用UPDATE语法时，正确的语法是

ALTER TABLE t_table_name UPDATE `field`='value' [WHERE ...]

因为语法不同，不能直接使用BaseMapper的update相关方法，这使得mybatisplus带来的便利性一下子降低了。但我们仍然可以利用mybatisplus的QueryWrapper协助构建WHERE条件。
以mybatis的方式通过注解手动拼接sql语句

@Mapper
public interface MyEntityMapper extends BaseMapper<MyEntity> {
    @Update("ALTER TABLE t_my_table UPDATE field=#{entity.field} ${ew.customSqlSegment}")
    void update(@Param("entity") MyEntity entity,@Param(Constants.WRAPPER) Wrapper<MyEntity> wrapper);
}

按照mybatisplus官网上提供的文档，可以使用@Param(Constants.WRAPPER)对Wrapper类型入参进行标注，并使用${ew.customSqlSegment}表示Wrapper对象所代表的部分sql语句。
在此基础上，仍然可以使用mybatis提供的sql脚本功能，通过<script>标签让sql语句更加灵活,比如可以根据实体对象的字段情况进行拼接。

@Mapper
public interface MyEntityMapper extends BaseMapper<MyEntity> {
    @Update("<script>ALTER TABLE t_my_table UPDATE field=#{entity.field} ${ew.customSqlSegment}</script>")
    void update(@Param("entity") MyEntity entity,@Param(Constants.WRAPPER) Wrapper<MyEntity> wrapper);

在mybatisplus官网中，我也并未找到对于sql方言的支持方法，只知道在配置分页插件时，分页插件可以通过指定数据库类型来确定分页所使用的方言；那么自己拼接带有方言的sql语句就是一种比较合理的选择了。
经过上述改动，在clickhouse中执行update命令成功了。本次折腾终于完事了...吗？

UPDATE性能调优

在我以为这样就完成开发的时候，clickhouse数据库出现了明显的慢查。相信聪明的同学已经想到了，clickhouse的update语句不是这么用的，大量的ALTER TABLE ... UPDATE ...语句会为clickhouse带来极大的负担。这是因为比起mysql等OLTP（在线事务处理）数据库，clickhouse是出于OLAP（在线数据分析）目的设计开发的，不期望数据有大量的UPDATE。另外，ALTER TABLE是非常重的操作，它会导致clickhouse在后台一直重新组织数据。
根据clickhouse官网的解释
https://clickhouse.com/docs/en/sql-reference/statements/alter...

note
The ALTER TABLE prefix makes this syntax different from most other systems supporting SQL. It is intended to signify that unlike similar queries in OLTP databases this is a heavy operation not designed for frequent use.

在设计上是不期望频繁使用的。而我的使用场景恰恰需要极其频繁地更新数据，所以考虑到数据库选型和业务场景，似乎这个程序在架构上就有些问题。
有没有替代UPDATE的方法呢？当然是有的。
根据clickhouse官网对于更新和删除数据的描述
https://clickhouse.com/docs/en/guides/developer/mutations

tip
If you need to perform frequent updates, consider using deduplication in ClickHouse, which allows you to update and/or delete rows without generating a mutation event.

这里提到的mutation event是clickhouse的一种后台数据处理机制。因为clickhouse是没有事务的，数据一旦提交到数据库就会开始排队被后台处理，可以中断但无法回滚。每个ALTER TABLE都会产生mutation。
这里官方建议我们使用去重数据。
根据clickhouse官网对去重数据的描述
https://clickhouse.com/docs/en/guides/developer/deduplication

ClickHouse is built for speed when it comes to data insertion. The storage files are immutable and ClickHouse does not check for an existing primary key before inserting a row-so deduplication involves a bit more effort. This also means that deduplication is not immediate-it is eventual, which has a few side effects:

At any moment in time your table can still have duplicates (rows with the same sorting key)
The actual removal of duplicate rows occurs during the merging of parts
Your queries need to allow for the possibility of duplicates

需要首先对业务需求进行评估，因为这种去重数据的方法的副作用非常明显。由于clickhouse非事务和后台异步处理的特性，我们查到的数据可能会存在重复，并且不是实时的。如果可以接受，那么这种方法才可用，否则需要重新考虑数据库选型的问题。
在上面这一篇官方文档中有非常详细的介绍，针对去重数据有两种实现：

Deduplication is implemented in ClickHouse using the following table engines:
ReplacingMergeTree table engine: with this table engine, duplicate rows with the same sorting key are removed during merges. is a good option for emulating upsert behavior (where you want queries to return the last row inserted).ReplacingMergeTree
Collapsing rows: the and table engines use a logic where an existing row is "canceled" and a new row is inserted. They are more complex to implement than , but your queries and aggregations can be simpler to write without worrying about whether or not data has been merged yet. These two table engines are useful when you need to update data frequently.CollapsingMergeTree VersionedCollapsingMergeTree

在文档的下文中，则详细展示了使用上述几种mergeTree实现频繁更新的方法。本质上都是通过插入数据+去重操作实现最后查出的数据只有一条。
这里简要介绍一下
1.使用replacingMergeTree 建表时指定主键，每次插入全量新数据，并在查询语句中使用FINAL关键字通过主键去重获得最终数据。但是FINAL仅在数据量较小的时候表现得好。
2.还是replacingMergeTree 每次插入全量新数据，但通过其他字段(如时间、严格递增的字段等）筛选出当前最新（或数值最大）的条目。这个筛选过程可以使用内建函数如Max()等。官网文档中表示这比FINAL性能要更好。
3.使用CollapsingMergeTree 建表时指定mergeTree的符号列和表的主键。每次插入2条数据，第一条数据表示抵消之前的旧数据，第二条数据表示新数据。数据的抵消需要为数据添加布尔类型的符号列来实现，有效的为1，无效的为-1；比如使用实体的uuid+符号列，当插入一条数据（uuid1,1,field1,field2,...）时，后续插入一条(uuid1,-1,...)即可表示删除之前的数据。再插入(uuid1,1,...)即表示插入新数据。这种方法也需要使用FINAL关键字进行查询。如果有多个线程提交数据，则顺序不能保证。
4.使用VersionedCollapsingMergeTree 建表时指定mergeTree的符号列和版本列，并指定表的主键。和使用CollapsingMergeTree类似，需要通过符号列对旧数据做删除，新数据与旧数据通过版本列进行区分。在查询时，可以使用FINAL关键字查询最终结果，也可以巧妙地通过符号列手动进行去重。它的好处是异步提交的数据不会有乱序问题。
以上几种方式，数据的物理去重都是在clickhouse后台进程异步发生的，单纯查询数据库内所有数据时有可能查到重复数据，所以我们需要对查询语句进行一些改造。
这里我使用的是第一种方式，使用replacingMergeTree+FINAL关键字查询。由于建表时使用的是普通的MergeTree，因此我们对表进行了重建，重建后的表使用使用replacingMergeTree。
改动查询语句：

 @Select("SELECT `my_field` FROM t_my_table FINAL ${ew.customSqlSegment} ")
    List<MyEntity> selectList(@Param(Constants.WRAPPER) QueryWrapper<MyEntity> queryWrapper);

由于改动了查询语句，分页插件也没办法生效了。所以我是手动拼接分页查询的

@Select("SELECT `my_field` FROM t_my_table FINAL ${ew.customSqlSegment} LIMIT #{offset}, #{size}")
    List<MyEntity> selectListWithLimit(@Param(Constants.WRAPPER) Wrapper<MyEntity> queryWrapper, Long offset, Long size);

由于需要用新数据替掉旧数据，因此整体的更新逻辑也产生了变化。对于需要更新的数据，要先查出全量字段，在内存中组装并更新实体对象然后插入全部字段，否则会导致更新的数据缺少字段或数据。整体上流程比之前变得麻烦了一些。
改动之后，clickhouse的查询性能变得可以接受了。后续还可以再进行优化，比如去掉查询语句中的FINAL，增加时间列作为去重依据等。

下面进行技术总结：

1.由于方言和clickhouse使用的特殊性，mybatisplus提供的封装好的查询没太多用武之地。尤其需要注意方言。
2.mybatisplus+clickhouse引入和配置比较容易，只要按上述文档中操作即可。
3.使用clickhouse作为OLTP数据库的场景，需要对更新删除操作进行特殊处理，稍显复杂。