2.3. 开发规范 · ClickHouse实践与进阶

**1\. 查询sql编写规范** （1）当多表联查时，查询的数据仅从其中一张表出时，可考虑使用IN操作而不是JOIN。（2）多表查询性能较差，多表Join时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较，ClickHouse中无论是Left Join 、Right Join还是Inner Join永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。（3）将一些需要关联分析的业务创建成字典表进行join操作，前提是字典表不宜太大，因为字典表会常驻内存。（4）禁⽌业务select \* ，列存数据,每减少一个字段会减少大量的数据扫描,提升查询效率。（5）建议使用 limit 限制返回数据条数使用limit返回指定的结果集数量，不会进行向下扫描，大大提升了查询效率。（6）查询时如果可以建议带上分区键查询,可以有效减少数据扫描量,提升查询效率。（7）CK的稀疏索引使得点查询(即kv类型的查询)性能不佳，千万不要把它简单当做关系型数据库进行查询。（8）使用Global优化分布式子查询，避免出现查询指数级放大。（9）使用 uniqCombined 替代 distinctuniqCombined 对去重进行了优化，通过近似去重提升十倍查询性能。（10）尽量不去使用字符串类型，时间类型最终会转换成数值类型进行处理，数值类型在执行效率和存储上远好过字符串。（11）ClickHouse的分布式表性能性价比不如物理表高，建表分区字段值不宜过多，防止数据导入过程磁盘可能会被打满。（12）不要在唯一列或大基数列上进行分组或去重操作，基数太大会消耗过多的io和内存。（13）CPU一般在50%左右会出现查询波动，达到70%会出现大范围的查询超时，CPU是最关键的指标，要非常关注。 **2. 数据写入注意事项** （1）不适合高并发写入，最好还是从异步化队列写入，batch insert 5w-10w 起步，尽量不要执行单条或插入操作，会产生大量小分区文件，给后台merge任务带来巨大压力。（2）几乎完全不支持update/delete，也不支持事务。（3）建议表要指定分区键,尤其是数据量大的表，插入/查询/合并都是以分区为单位，合理的分区可以提升整体性能。（4）分区不建议太多，如果分区太多，会因需要打开的文件描述符过多导致查询效率不佳。（5）数据在写入ClickHouse前预先的对数据进行分组，避免一次插入的数据属于多个分区。（6）注意MerTree 主键允许存在重复数据(ReplacingMergeTree可以在分区内去重)。 **3. 建表规范** （1）本地表命名格式：{tab\_name}\_local，分布式表命名格式：{tab\_name}\_shard 。（2）物化视图命名规范：{tabl\_name\_xxx}\_mv 。（3）尽量不要使用Nullable类型,该类型对性能有一定影响,且不能包含在索引中。（4）合理设置分区，所有本地表使用order by关键字指定分区字段，建议采用日期作为一级分区。默认 order by 字段作为主键。（5）如果表中不是必须保留全量历史数据，建议指定TTL，可以免去手动过期历史数据的麻烦。（6）所有复制引擎表建表指定 use\_minimalistic\_part\_header\_in\_zookeeper=1。本地ReplicatedMergeTree表建表模板如下所示： ~~~sql CREATE TABLE IF NOT EXISTS ads. ads_af_city_complaint_1d _local ON cluster ycdata_3shards_3replicas (`id` UInt64 COMMENT '序号', `order_id` UInt64 COMMENT '订单号', `gross_weight` UInt64 COMMENT '权重', `create_time` Date COMMENT '创建时间', `event` String COMMENT '事件') ENGINE = ReplicatedMergeTree('/clickhouse/table/{shared}/ads_af_city_complaint_1d _local', '{replica}') PARTITION BY create_time ORDER BY id TTL create_time + toIntervalDay(90) SETTINGS index_granularity = 8192, use_minimalistic_part_header_in_zookeeper = 1; ~~~ **解释：** * TTL 定义了数据保留策略为90天。 * {shared},{replica}无需替换为一个具体值。 * ycdata\_3shards\_3replicas为clickhouse是集群名称。