企业官网建设流程全解析

学习笔记，ClickHouse写方式对不对，通过资源使用率就可以看出来

摘自官网原理：
数据在磁盘上（物理上）是以part形式写入和存储的，以part的形式按照 排序键-排序、列切割、压缩后写到磁盘，逻辑上归到分区键所在的文件下。

相同分区下的part会合并(可按照参数配置调整),合并过的part变为inactive,新的part标记为active，inactive会定期删除（可配置）

这里合并就会使用到cpu，当频繁inset into ，就会堆积大量的part，后台cpu都去合并了，会打的很高，所以需要优化，首选攒批写入5000-1w-10w，按照现在的业务逐渐网上加，也不要一次性加的太猛了，保持一秒个位数的插入次数。

查看调整哪些表需要调整批大小
---查询更多表详情
SELECT
substr(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_rows
FROM clusterAllReplicas('default_cluster', system.query_log)
WHERE event_date = '2026-08-18'
AND event_time >= '2026-08-18 16:00:00'
AND event_time <= '2026-08-18 16:01:00'
AND is_initial_query = 1
AND type IN (2, 3, 4)
AND query_kind = 'Insert'
GROUP BY query_snippet
ORDER BY insert_cnt DESC
LIMIT 25

字段 说明
clusterAllReplicas('default_cluster', system.query_log) 查询集群所有副本节点的 query_log
event_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/parts

The 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、Sorting

The rows are sorted by the table’s sorting key(town, street), and a sparse primary index is generated for the sorted rows.

2、Splitting

The sorted data is split into columns.

3、Compression

Each column is compressed.

4、Writing to disk

The 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.