记一次性能飙升的Mysql CRUD数据表迁移到Clickhouse表的过程

起因

我们系统有一个同步商品系统属性信息的模块，包括同步商品的价格，商品的品牌，商品的品类，商品开售时间，售卖截止时间等信息，对应的mysql表如下：

create table mysql_product_info(productId bigint(20), price int, sn varchar(100), category varchar(100), sell_time_begin DateTime, sell_time_end DateTime) engine = Innodb DEFAULTCHARSET=utf8;

数据量大概是1000万，同步过来的数据的主要用途是用于数据分析，比如统计每个品牌的商品数量，统计售卖开始时间是从明天开始的商品数量等等，可以接受数据的统计延迟是分钟级
面临的主要问题:
我们使用mysql数据库来存储商品信息的数据在数据的新增修改删除等方面是满足性能要求的，最大的问题是数据的分析操作，由于数据主要是用于数据的分析用途，几乎每个分析的sql对于mysql来说都是慢查询操作，所以对数据库产生的压力极大，数据库经常告警，而且用户查询也都因为超时而放弃，DBA和用户抱怨声不断，所以才有了我们的这次数据改造之旅.

改造目标

在满足准实时性的基础上，对用户提供满足聚合查询性能的数据表

方案选择

我们主要性能瓶颈是数据分析，所以我们把目光瞄向了clickhouse，数据分析是clickhouse的强项，但是clickhouse在数据更新删除方面非常不擅长，它只适合于数据的插入操作，并且最擅长于大批量（每一批10w）的数据插入操作，所以目标就变成了怎么把mysql的数据更新删除操作转化成clickhouse的数据新增操作，我们最终选择了使用ReplacingMergeTree去重表来作为最终实现方案,通过新增记录的方式实现修改删除的效果.

技术实现

我们创建如下的ReplacingMergeTree表结构，我们没有使用分区，所以所有的1000w的数据都在一个分区中，

CREATE TABLE ck_product_info
(
productId int(64),
sn String,
category String, 
sell_time_begin DateTime
sell_time_end DateTime
sign Int8,
version UInt64
) ENGINE = ReplacingMergeTree(version)
ORDER BY (productId);

我们设计了sign和version字段，用来模拟update和delete操作: 当sign=1时表示记录的插入或更新操作；当sign=-1时表示记录的delete删除操作，
version版本号是记录操作的先后顺序，我们这里使用了毫秒时间戳，后发生的操作的时间戳比先发生的操作的时间戳要大.

由于clickhouse的表中同时存在了新增/修改/删除的记录，我们的sql查询需要以以下方式进行：

select * from (select productId, argMax(price,version) as price,argMax(sn,version) as sn, argMax(category,version) as category, argMax(sell_time_begin) as sell_time_begin, 
argMax(sell_time_end) as sell_time_end,argMax(sign,version) as sign from ck_product_info group by productId) as a where a.sign > 0

这样我们就可以提供一个每条记录都是最新有效记录的clickhouse表，为了操作的简单性，我们把这个sql封装成一个视图：

create view default.ck_product_info_view AS select * from (select id, argMax(price,version) as price,argMax(sn,version) as sn, argMax(category,version) as category, argMax(sell_time_begin) as sell_time_begin, 
argMax(sell_time_end) as sell_time_end,argMax(sign,version) as sign from ck_product_info group by productId) as a where a.sign > 0`

这样我们所有的操作都是对这个视图进行操作即可

待改进的点

一.我们以视图的方式对外提供sql查询的方式是查询了所有的字段值，其实用户的很多操作是不需要其中的一些字段的，当然clickhouse在处理简单的查询的时候可以优化掉这种视图中多余的不需要查询的字段，但是如果用户的sql很复杂，clickhouse就会多余把视图中查询的多余字段都查出来，这很极大的影响性能
解决方案：

1. 直接提供底层clickhouse语句的方式，对我们的查询用户进行培训，之前使用到视图的地方都替换成(只查找需要字段的底层clickhouse语句–比如select productId, argMax(price,version) as price,argMax(sign,version) as sign from ck_product_info group by productId) as a where a.sign > 0只查询价格字段)这种语句
2. 我们强制用户在填写sql语句的时候也要填写他需要的字段名称列表，这样我们可以根据他填写的名称列表构造底层的clickhouse语句表，只查询需要使用的字段，这样就可以满足性能要求了
3. 什么都不修改，依然是以提供视图所有字段的方式提供，依靠clickhouse的sql优化，排除掉不需要的字段

在第3点clickhouse内部没有得到完整的支持之前，我们目前倾向于使用第2点作为进一步优化的方向.