ClickHouse SQL军规

核心军规（2条）

  • 不要把所有业务逻辑计算都写在SQL中
  • 拒绝3B：拒绝大sql，大事物，大批量

数据类型（3条）

  • 用好数值类型，字段类型尽可能的小。
  • 字段类型尽量用数值型
  • 建表时能用数值型或日期时间型表示的字段，就不要用字符串——全String类型在以Hive为中心的数仓建设中常见，但CH环境不应受此影响。
  • 直接用DataTime: 直接用DateTime表示时间列，而不是用整形的时间戳。因为CH中DateTime的底层就是时间戳，效率高，可读性好，且转换函数丰富。
  • 不用Nullable: 官方已经指出Nullable类型几乎总是会拖累性能，因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记，并且Nullable列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值。

分区和索引（2条）

  • 事实表必须分区，分区粒度根据业务特点决定，不宜过粗或过细。我们当前都是按天分区，按小时、周、月分区也比较常见（系统表中的query_log、trace_log表默认就是按月分区的）。
  • 必须指定索引列，在绝大多数查询的WHERE语句中都会用到的列适合作为索引。CH的索引非MySQL的B树索引，而是类似Kafka log风格的稀疏索引，故不用考虑最左原则，但是建议日期列和区分度较低的列在前，区分度较高的列在后。

分布式表使用（3条）

  • 生产环境中提供线上服务的表均采用复制表与分布式表相结合，即ReplicatedMergeTree+Distributed引擎。分布式表的表名为本地表名加上_all后缀。
  • 如果表中不是必须保留全量历史数据，建议指定TTL，可以免去手动过期历史数据的麻烦。TTL也可以通过ALTER TABLE语句随时修改。
  • 建议指定use_minimalistic_part_header_in_zookeeper = 1设置项，能够显著压缩表元数据在ZooKeeper中的存储。该项也可以写入config.xml中的<merge_tree>一节。

查询相关事项

单表查询（6条）

  • 所有应用层查询禁止使用SELECT *。
  • 查询分区表必须指定分区（所谓partition pruning），不能全表查询。
  • 大规模数据集上的ORDER BY要加LIMIT限制。
  • 结果集上的简单运算在前端展示时再进行，减少SQL中不必要的虚拟列（如求两列的和或比例）。
  • 业务场景非强制要求100%准确的基数计量，应该用uniq()函数而不是uniqExact()函数或DISTINCT关键字。uniq()底层采用HyperLogLog实现，能够以低于1%的精度损失换来极大的性能提升。
  • 能够重用的模式化查询（如固定刷新的BI报表、热力图等）一定要做成物化视图，并在物化视图上查询出结果，可以避免大量的重复计算。关于其用法，参见物化视图简介与ClickHouse中的应用示例。

多表查询（5条）

  • 当两表关联查询只需要从左表出结果时，建议用IN而不是JOIN，即写成SELECT ... FROM left_table WHERE join_key IN (SELECT ... FROM right_table)的风格。
  • 不管是LEFT、RIGHT还是INNER JOIN操作，小表都必须放在右侧。因为CH默认在大多数情况下都用hash join算法，左表固定为probe table，右表固定为build table且被广播。
  • CH的查询优化器比较弱，JOIN操作的谓词不会下推，因此要尽量减少JOIN时的左右表的数据量，必要时可以提前对某张表进行聚合操作，减少数据条数，即一定要先做完过滤、聚合等操作，再在结果集上做JOIN。这点与写其他平台SQL语句的习惯很不同，初期尤其需要注意。
  • 两张分布式表上的IN和JOIN之前必须加上GLOBAL关键字。如果不加GLOBAL关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询N2次（N是该分布式表的shard数量），这就是所谓的查询放大，会带来不小的overhead。加上GLOBAL关键字之后，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上。
  • 为每一个账户添加join_use_nulls配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准SQL中的Null值

写入相关事项（4条）

  • 分布式表的底表，而不直接写分布式表。参见ClickHouse复制表、分布式表机制与使用方法
  • 做小批量零碎的写入，每批次至少千条级别，避免给merge造成太大压力。
  • 同时写入太多个分区，或者写入过快（官方给出的阈值为1秒1次），容易因为merge的速度跟不上parts生成的速度而报出"too many parts"的错误。如果正常情况下还会出现此错误，建议在CPU资源允许的情况下适当调大后台任务线程数background_pool_size，默认值为16。
  • 写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致CH无法及时对新导入的数据进行合并，从而影响查询性能

附录

CPU一般在50%左右会出现查询波动，达到70%会出现大范围的查询超时，CPU是最关键的指标，要非常关注。

CH配置时需要重点关注的参数，可根据集群负载及业务情况进行适当的调整：

config.xml

<!-- Maximum number of threads in the Global thread pool.-->
 <max_thread_pool_size>10000</max_thread_pool_size>

users.xml

<background_pool_size>64</background_pool_size>
<!--300秒也就是5分钟超时-->
<max_execution_time>300</max_execution_time>
<!--超时即杀掉:throw :break-->
<timeout_overflow_mode>throw</timeout_overflow_mode>
<max_threads>20</max_threads>
<max_bytes_before_external_group_by></max_bytes_before_external_group_by>53687091200
<!--单查询内存数,100G-->
<max_memory_usage>107374182400</max_memory_usage>
<!--总内存数,120G-->         <max_memory_usage_for_all_queries>120849018880</max_memory_usage_for_all_queries>
<!-- Use cache of uncompressed blocks of data. Meaningful only for processing many of very short queries. -->
<use_uncompressed_cache>0</use_uncompressed_cache>
<join_use_nulls>0</join_use_nulls>