clickhouse 21.x生产实践优化

1 时间字段类型

• 建表时能用数值型或日期时间型表示的字段就不要用字符串，全String 类型在以Hive(hbase)为中心的数仓建设中常见，但 ClickHouse 环境不应受此影响。虽然 ClickHouse 底层将DateTime 存储为时间戳Long类型，但不建议存储Long 类型，因为DateTime 不需要经过函数转换处理，执行效率高、可读性好。

2 Nullable类型

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

3 分区与索引

• 一般选择按天分区，也可以指定为 Tuple()，以单表一亿数据为例，分区大小控制在 10-30 个为最佳。

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

4 数据TTL

• 如果表中不是必须保留全量历史数据，建议指定 TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL 也可以通过 alter table 语句随时修改。

5 写入与删除优化

• 尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge 任务带来巨大压力

• 不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据（依服务器性能而定）

• Too many parts错误：in_memory_parts_enable_wal 默认为 true（开启wal预写日志）

• Memory limit错误：增加内存设置max_memory_usage ，或者内存不充裕建议将超出部分内容分配到系统硬盘上max_bytes_before_external_group_by、max_bytes_before_external_sort

6 谓词下推：(各个版本性能损失有差异)

• 当 group by 有 having 子句，但是没有 with cube、with rollup 或者 with totals 修饰的时候，having 过滤会下推到 where 提前过滤。例如下面的查询，HAVING name 变成了 WHERE name，在 group by 之前过滤

7 Prewhere替代where

• 当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作
• 默认：Prewhere自动打开，但是某些场景即使开启优化，也不会自动转换成 prewhere，需要手动指定 prewhere：
  1）使用常量表达式
  2）使用默认值为 alias 类型的字段
  3）包含了 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查询
  4）select 查询的列字段和 where 的谓词相同
  5）使用了主键字段

8 类关系型数据库要求

• 千万以上数据集进行 order by 查询时需要搭配 where 条件和 limit 语句一起使用（mysql、oracle也有同样的要求）

9 避免构建虚拟列

• 其实就是mysql、oracle要求使用函数的列不会命中索引

SELECT Income,Age,Income/Age as IncRate