ClickHouse-写

学习笔记ClickHouse写方式对不对通过资源使用率就可以看出来摘自官网原理数据在磁盘上物理上是以part形式写入和存储的以part的形式按照 排序键-排序、列切割、压缩后写到磁盘逻辑上归到分区键所在的文件下。相同分区下的part会合并(可按照参数配置调整),合并过的part变为inactive,新的part标记为activeinactive会定期删除可配置这里合并就会使用到cpu当频繁inset into 就会堆积大量的part后台cpu都去合并了会打的很高所以需要优化首选攒批写入5000-1w-10w按照现在的业务逐渐网上加也不要一次性加的太猛了保持一秒个位数的插入次数。查看调整哪些表需要调整批大小---查询更多表详情SELECTsubstr(query, 1, 140) AS query_snippet,count() AS insert_cnt,round(avg(query_duration_ms)) AS avg_ms,round(avg(written_rows), 1) AS avg_rows,formatReadableSize(avg(written_bytes)) AS avg_bytes,sum(written_rows) AS total_rowsFROM clusterAllReplicas(default_cluster, system.query_log)WHERE event_date 2026-08-18AND event_time 2026-08-18 16:00:00AND event_time 2026-08-18 16:01:00AND is_initial_query 1AND type IN (2, 3, 4)AND query_kind InsertGROUP BY query_snippetORDER BY insert_cnt DESCLIMIT 25字段 说明clusterAllReplicas(default_cluster, system.query_log) 查询集群所有副本节点的 query_logevent_date query_log 的分区日期按 UTC 切分当天数据可能只含部分时段event_time 查询事件时间需根据实际要验证的时段调整is_initial_query 1 只统计初始查询排除分布式子查询避免重复计数type IN (2, 3, 4) 2查询完成3查询异常4查询被停止query_kind Insert 只看 Insert 操作substr(query, 1, 140) 截取 SQL 前 140 字符用于识别涉及的表名written_rows / written_bytes 每次 insert 写入的行数和字节数https://clickhouse.com/docs/concepts/core-concepts/partsThe data from each table in the ClickHouse MergeTree engine family is organized on disk as a collection of immutabledata parts.A data part is created whenever a set of rows is inserted into the table.1、SortingThe rows are sorted by the table’s sorting key(town, street), and a sparse primary index is generated for the sorted rows.2、SplittingThe sorted data is split into columns.3、CompressionEach column is compressed.4、Writing to diskThe compressed columns are saved as binary column files within a new directory representing the insert’s data part. The sparse primary index is also compressed and stored in the same directory.