Clickhouse使用优化

阳光穿透心脏的1/2处 2023-10-06 17:43 34阅读 0赞

#### 1.数据类型 ####

1）虽然clickhouse底层将DateTime存储为时间戳Long类型，但不建议直接存储Long类型，因为DateTime不需要经过函数转换处理，执行效率高、可读性好。

2）官方已经指出Nullable类型几乎总是会拖累性能，因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记，并且Nullable列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值（例如用-1表示没有商品ID）。

#### 2.分区和索引 ####

1）分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可指定为tuple()；以单表1亿数据为例，分区大小控制在10-30个为最佳。

2）必须指定索引列，clickhouse中的索引列即排序列，通过order by指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、查询频率大的在前原则；还有基数特别大的不适合做索引列，如用户表的userid字段；通常筛选后的数据满足在百万以内为最佳。

#### 3.表参数 ####

index\_granularity 是用来控制索引粒度的 默认是8192，如非必须不建议调整。

如果表中不是必须保留全量历史数据，建议指定TTL，可以免去手动过期历史数据的麻烦。TTL也可以通过ALTER TABLE语句随时修改。

#### 4.单表查询 ####

使用prewhere替代where关键字；当查询列明显多于筛选列时使用prewhere可十倍提升查询性能。

SELECT * from fl_db.stock_plate sp prewhere code='600570';

#### 5.数据采样策略 ####

通过采用运算可极大提升数据分析的性能。

数据量太大时应避免使用select \* 操作，查询的性能会与查询的字段大小和数量成线性变换；字段越少，消耗的io资源就越少，性能就会越高。

千万以上数据集进行order by查询时需要搭配where条件和limit语句一起使用。

如非必须不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。

不建议在高基列上执行distinct去重查询，改为近似去重 uniqCombined。

多表Join时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较。

#### 6.存储 ####

clickhouse不支持设置多数据目录，为了提升数据io性能，可以挂载虚拟券组，一个券组绑定多块物理磁盘提升读写性能；多数查询场景SSD盘会比普通机械硬盘快2-3倍。

##### 特别注意： #####

1.  JOIN操作时一定要把数据量小的表放在右边，ClickHouse中无论是Left Join 、Right Join还是Inner Join永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。

2.为每一个账户添加join\_use\_nulls配置。ClickHouse的SQL语法是非标准的，默认情况下，以Left Join为例，如果左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准SQL中的Null值。对于习惯了标准SQL的我们来说，这种返回值经常会造成困扰。

3.通过ClickHouse官方的JDBC向ClickHouse中批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好通过Order By语句对需要导入的数据进行排序。无序的数据或者数据中涉及的分区太多，会导致ClickHouse无法及时的对新导入的数据进行合并，从而影响查询性能。

4.尽量减少JOIN时的左右表的数据量，必要时可以提前对某张表进行聚合操作，减少数据条数。有些时候，先GROUP BY再JOIN比先JOIN再GROUP BY查询时间更短。

5.ClickHouse版本迭代很快，建议用去年的稳定版，不能太激进，新版本我们在使用过程中遇到过一些bug，内存泄漏，语法不兼容但也不报错，配置文件并发数修改后无法生效等问题。

6.避免使用分布式表，ClickHouse的分布式表性能上性价比不如物理表高，建表分区字段值不宜过多，太多的分区数据导入过程磁盘可能会被打满。

7.服务器CPU一般在50%左右会出现查询波动，CPU达到70%会出现大范围的查询超时，所以ClickHouse最关键的指标CPU要非常关注。我们内部对所有ClickHouse查询都有监控，当出现查询波动的时候会有邮件预警。

8.查询测试Case有：6000W数据关联1000W数据再关联2000W数据sum一个月间夜量返回结果：190ms；2.4亿数据关联2000W的数据group by一个月的数据大概390ms。但ClickHouse并非无所不能，查询语句需要不断的调优，可能与查询条件有关，不同的查询条件表是左join还是右join也是很有讲究的。