【标签画像】ClickHouse的Join调优

概要

基于clickhouse做标签画像系统的标签加工引擎时，自定义标签跑批加工之后需要统一合并到大宽表中以供客群分析或者其他功能的使用，标签规模的大小严重影响到合表的效率，因clickhouse的设计主要用于实时数据的分析，离线加工时ck的压力严重取决于数据的量级，基于此背景对ck做出调研分析与调优。

为什么要合并到宽表

例如在对客户进行圈群时，筛选当日女性支付次数超过10次的客户群，则需要join客户和消费明细两张表，但是对于数据量级过亿甚至百亿的消费明细数据表，通过clickhouse做join表聚合查询时他需要把集群表（ck有本地表和集群表之分，普通查询会分发到本地聚合，然后汇总到执行服务）的全量数据读取到内存，从而使ck的内存爆炸（当前只做单表加工优化，集群表见下一篇），所以需要单独加工后统一合并至宽表，虽然宽表多则几百个字段，但是对于ck这种列示存储的OLAP数据库来说不在话下。

调优细节

1. 最大连表数max_subquery_depth

clickhouse默认最大连表数为100，但是自定义标签随随便便就能创建几百个，如果想一次性合表成功的话查过100个就会抛出异常，基于此可以增加max_subquery_depth的数量（需要根据实际数据量测试，数据量大同样会抛异常：内存溢出）
2. 最大可用内存max_memory

ck默认最大单查询使用内存为9.31GB，可以动态在sql中指定，尾部添加SETTINGS max_memory=<内存值>

3. join算法(单节点join，集群表见下篇文章)

clickhouse单机join操作默认采用hash join算法，可选用merge join。其中merge join算法数据会溢出到磁盘，性能相比前者差。

• Merge Join
  MergeJoin是一种基于排序的连接算法，它要求参与连接的表在连接字段上进行排序。Merge Join 的原理如下:
  a.对参与连接的表按照连接字段进行排序，确保两个表的连接字段是有序的。
  b.使用两个指针分别指向两个表的第一个记录。
  c.比较两个指针所指向的记录的连接字段的值，如果相等，则将这两条记录合并为一条，并输出。
  d.如果两个指针所指向的记录的连接字段的值不相等，则将连接字段较小的记录的指针向后移动一位，然后继续比较。
  e.重复步骤 3 和步骤 4，直到其中一个表的记录全部被处理完
  Merge Join 的优势在于它只需要对参与连接的表进行一次排序，并且可以并行处理多个连接操作，从而提高查询的效率
• 使用Merge Join 的注意事项
  a.数据排序
  为了使用 Merge Join，参与连接的表必须在连接字段上进行排序。如果表没有按照连接字段排序，可以使用ClickHouse 提供的ORDER BY 语句对表进行排序。
  b.数据类型
  Merge Join 要求连接字段的数据类型必须相同，否则无法进行连接。在进行连接操作之前，需要确保连接字段的数据类型一致。
  c.内存限制
  Merge Join 使用了一定的内存来存储连接字段的值，如果连接字段的值较大或者连接的表的数据量很大，可能会导致内存不足。在使用 Merge Join 时，需要根据实际情况调整 ClickHouse 的内存配置，确保有足够的内存来执行连接操作。
  d.多表连接
  Merge Join 可以连接两个或多个表。当连接多个表时，需要保证每个表的连接字段都进行了排序，并且连接字段的数据类型相同。
• 其他算法
  以下为官网摘抄：
  
  可见不同的场景对于join的算法使用还是需要权衡的，当前合表场景因数据量大，为了减轻内存的压力选择使用partial_merge算法，他的核心就是先排序，再分批做join，但是在测试的时候还是抛出了内存溢出的异常，于是又经过查阅资料发现了在做排序的时候也是可以优化的，max_bytes_before_external_sort可以指定做排序时的最大使用内存，当查出这个值时ck会启用外部空间，也就是把多余的数据打到磁盘，这样就可以尽可能的多的进行join表操作了，当前通过测试，32G内存，单节点500万数据量，可同时合表60个标签表，耗时10分钟