ClickHouse 进阶【建表、查询优化】

1、ClickHouse 进阶

因为上一节部署了集群模式，所以需要启动 Zookeeper 和 ck 集群；

1.1、Explain 基本语法

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] 
SELECT ... [FORMAT ...]

• AST：用于查看语法树
• SYNTAX：用于优化语法
• PLAN：用于查看执行计划
  • header：打印计划中各个步骤的 head 说明，默认关闭，默认值 0;
  • description：打印计划中各个步骤的描述，默认开启，默认值 1;
  • actions：打印计划中各个步骤的详细信息，默认关闭，默认值 0;
• PIPELINE：用于查看 pipeline 计划
  • header：打印计划中各个步骤的 head 说明，默认关闭；
  • graph：用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合 graphviz 查看；
  • actions：如果开启了 graph，紧凑打印打，默认开启；

其中，PLAN 和 PIPELINE 还可以进行额外的显示设置；

1.2、建表优化

        在之前使用 Hive 做数仓的时候，我们通常直接把日期类型直接存储为 String 类型，用的时候再用 date_format 函数转一下；但是在 ck 中并不是这样的；

1.2.1、数据类型

1）时间字段类型

        虽然 ClickHouse 底层将 DateTime 存储为时间戳 Long 类型，但不建议把时间存储为 Long 类型， 因为 DateTime 不需要经过函数转换处理，执行效率高、可读性好。

CREATE TABLE t_type2
(`id` UInt32,`sku_id` String,`total_amount` Decimal(16, 2),`create_time` DataTime
)
ENGINE = ReplacingMergeTree(create_time)
PARTITION BY create_time
PRIMARY KEY id
ORDER BY (id, sku_id)

2）空值存储类型

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

        这里指定了列 y 可以为 null （但是这里的 x 不可以为 null，如果非要给 x 列插入 null 的话，默认会被转为 x 列所对应类型的默认值）

可以看到，因为 y 列可以为 null，所以 ck 会为 y 单独创建一个文件存储 null 值；

1.2.2、分区和索引

        分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为 Tuple()， 以单表一亿数据为例，分区大小控制在 10-30 个为最佳。

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

1.2.3、Index_granularity

        Index_granularity 是用来控制索引粒度的，默认是 8192，如非必须不建议调整。

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

 1.2.4、写入和删除优化

• 尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台 Merge 任务带来巨大压力
• 不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不 上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据（依服务器性能而定）

        在服务器内存充裕的情况下增加内存配额，一般通过 max_memory_usage 来实现

        在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行 速度，一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数 来实现。

1.3、语法优化规则

1.3.1、count 优化

在调用 count 函数时，如果使用的是 count() 或者 count(*)，且没有 where 条件，则 会直接使用 system.tables 的 total_rows

查看 count 的执行计划：

注意 Optimized trivial count ，这是对 count 的优化。 如果 count 具体的列字段，则不会使用此项优化：

1.3.2、谓词下推

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

子查询支持谓词下推：

1.3.3、聚合计算外推

聚合函数内的计算，会外推，例如：

1.4、查询优化

1.4.1、单表查询

1）Prewhere 替代 where

        Prewhere 和 where 语句的作用相同，用来过滤数据。不同之处在于 prewhere 只支持 * MergeTree 族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤 之后再读取 select 声明的列字段来补全其余属性。

        当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作。

        在某些场合下，prewhere 语句比 where 语句处理的数据量更少性能更高。

某些场景即使开启优化，也不会自动转换成 prewhere，需要手动指定 prewhere：

• 使用常量表达式
• 包含 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查询
• select 查询的列字段和 where 的谓词相同
• 使用了主键字段

1.4.2、数据采样

通过采样运算可极大提升数据分析的性能

SELECTTitle,count(*) AS PageViews
FROM hits_v1
SAMPLE 1 / 10
WHERE CounterID = 57
GROUP BY Title
ORDER BY PageViews DESC
LIMIT 1000

1.4.3、列裁剪与分区裁剪

        数据量太大时应避免使用 select * 操作，查询的性能会与查询的字段大小和数量成线性 表换，字段越少，消耗的 io 资源越少，性能就会越高。

1.4.4、orderby 结合 where、limit

        千万以上数据集进行 order by 查询时需要搭配 where 条件和 limit 语句一起使用；

1.4.5、避免构建虚拟列

        如非必须，不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储；

1.4.6、uniqCombined 替代 distinct

        性能可提升 10 倍以上，uniqCombined 底层采用类似 HyperLogLog 算法实现，能接收 2% 左右的数据误差，可直接使用这种去重方式提升查询性能。Count(distinct )会使用 uniqExact 精确去重。

        不建议在千万级不同数据上执行 distinct 去重查询，改为近似去重 uniqCombined；

1.4.7、物化视图

        区别于普通视图，物化视图会把数据存下来，而普通视图并不保存数据；

1.5、多表关联

1.5.1、大小表 join

        多表 join 时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较，ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录 到左表中查找该记录是否存在，所以右表必须是小表。

1.5.2、分布式表使用 GLOBAL

        两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上。如果不加 GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 N²次（N 是该分布式表的分片数量），这就是查询放大，会带来很大开销。

1.6、数据一致性

即便对数据一致性支持最好的 Mergetree，也只是保证最终一致性：

        我们在使用 ReplacingMergeTree、SummingMergeTree 这类表引擎的时候，会出现短暂 数据不一致的情况。

在某些对一致性非常敏感的场景，通常有以下几种解决方案：

1.6.1、手动 OPTIMIZE

在写入数据后，立刻执行 OPTIMIZE 强制触发新写入分区的合并动作：

OPTIMIZE TABLE test_a FINAL;

但是数据量大时优化可能非常耗时，而且优化时数据对外无法访问，所以并不推荐使用；

1.6.2、通过 Group by 去重

SELECT user_id , argMax(score, create_time) AS score, argMax(deleted, create_time) AS deleted, max(create_time) AS ctime 
FROM test_a 
GROUP BY user_id 
HAVING deleted = 0;

函数说明：

• argMax(field1，field2)：按照 field2 的最大值取 field1 的值。

比如 argMax(score,create_time)，当 score 字段有多个时，取 create_time 最大的；

1.7、物化视图

        ClickHouse 的物化视图是一种查询结果的持久化，它确实是给我们带来了查询效率的提 升。用户查起来跟表没有区别，它就是一张表，它也像是一张时刻在预计算的表，创建的过 程它是用了一个特殊引擎，加上后来 as select，就是 create 一个 table as select 的写法。

         “查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多 表 join 之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着 基础表的变化而变化，所以它也称为快照（snapshot）

1.7.1、物化视图与普通视图的区别

        普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以 将普通视图理解为是个子查询。物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表；

到这里我终于理解了为什么实习的时候一张视图创建了 30 分钟，因为存储引擎用的是 StarRocks 物化视图；

1.7.2、优缺点

优点：查询速度快，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总 的行数少了，因为都预计算好了。

缺点：它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去 重、去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且 如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带 宽占满、存储一下子增加了很多。

1.7.3、语法

也是 create 语法，会创建一个隐藏的目标表来保存视图数据。也可以 TO 表名，保存到 一张显式的表。没有加 TO 表名，表名默认就是 .inner.物化视图名

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] 
[ENGINE = engine] [POPULATE] AS SELECT ...

总结

        这一块还是不好理解，如果没有项目真正实践，这些优化都是纸上谈兵；这里先有个大致了解，感觉学到最后，这些大数据数据库框架的优化的很多相同点都是有迹可循的；