1. 主键索引
在 ClickHouse 中选择有效的主键对查询性能和存储效率至关重要。
- 如果不指定primary key，primary key默认就是ordering key。
- 在选择ordering key时，优先考虑在查询条件中常用的列，尤其是那些排除大量行的列。
- 选择与表中其他数据高度相关的列也很有益，因为连续存储能够提高压缩比和在 group by 和 order by 操作中的内存效率。
2. 分区键
分区是ClickHouse管理数据的重要方式。
- ClickHouse默认是没有分表的，不建议使用分表，分表需要业务上做复杂适配。
- 选择低基数的字段作为分区键，ClickHouse 不会跨分区合并数据，因此过多的分区会导致过多未合并的部分（too many parts error）。
- ClickHouse 会自动在分区列上构建 MinMax 索引，这可以显著加速对这些列进行过滤的查询。例如，当表按 toYYYYMMDD(date) 分区时，按天过滤可以让引擎完全跳过无关的分区及其部分。
- 建议每个分区的数据条数在千万~几亿左右（具体受磁盘、内存、查询模式和CPU能力影响）。
- 分区数量不要超过几千个较为合适，几十到几百是比较常见的数量。
3. 数据类型
- 使用严格类型：始终为列选择正确的数据类型。数值和日期字段应使用适当的数值和日期类型，而不是通用的字符串类型。
- 避免可空列：可空列通过维护单独的列来跟踪空值，从而引入额外的开销。仅在明确需要区分空状态和null状态时使用 Nullable。否则，默认值或零等效值通常足够。
- 低基数LowCardinality字段：任何唯一值少于 10,000 的列都是低基数的选择。
- 专用类型：IP支持IPv4和IPv6类型。
4. 数据写入
- 批量插入（客户端处理）：建议以至少 1,000 行为一个批次插入数据，理想情况下在 10,000 到 100,000 行之间。
-
异步插入（服务端处理）：
- 异步插入通过将传入数据写入内存缓冲区，然后根据可配置的阈值刷新到存储中，将批处理责任从客户端转移到服务器。
-
插入被缓冲，只有在满足其中一个刷新条件时才写入磁盘：(1) 缓冲区达到指定大小 (async_insert_max_data_size)(2) 已经过了时间阈值 (async_insert_busy_timeout_ms)(3) 累积的插入查询达到最大数量 (async_insert_max_query_number)
- 在发生刷新之前，数据无法被查询。
- 不推荐以同步模式发送许多小批次，这样会导致创建许多 parts。这将导致查询性能差和 “too many parts” 错误。
5. 查询优化
- ordering key和partition key是查询性能的关键因素。
- 避免使用select *，按需获取字段。
- where和group by中的列顺序，要和建表语句中order by的列顺序统一，遵循最左原则。
- join查询性能不是很好，应避免使用，替代方案：业务设计使用大宽表，如必须使用join，右表选小表（hash join 右表会全部加载到内存）。
- 使用 EXPLAIN indexes=1 定位慢查询的原因。
6. 数据TTL
- TTL机制：ALTER TABLE events MODIFY TTL event_time + INTERVAL 30 DAY;
- 分区后，删除分区：ALTER TABLE <表名> DROP PARTITION <分区值>
- 如果需要按磁盘大小来做大量数据删除，建议直接删除分区，因为删除数据行会导致后台merge影响性能。
- 支持定义冷/热磁盘，通过TTL TO VOLUME实现数据冷热迁移。
- 通用用途：内存与CPU比率 4:1
- 数据仓库用例：内存与 CPU 比率 8:1
- 计算优化用例：内存与 CPU 比率 2:1
参考：
https://clickhouse.com/docs/zh/best-practices