clickhouse学习记录

clickhouse学习记录

clickhouse为什么这么快

Architecture choices

面向列的存储:源数据通常包含数百甚至数千列，而报表只能使用其中的几个列。系统需要避免读取不必要的列，以避免昂贵的磁盘读取操作。
索引:驻留在内存中的ClickHouse数据结构只允许读取必要的列，并且只允许读取这些列的必要行范围。
数据压缩:将同一列的不同值存储在一起通常会带来更好的压缩比(与面向行的系统相比)，因为在实际数据中，一列对于相邻的行通常具有相同的值，或者没有那么多不同的值。除了通用的压缩，ClickHouse支持专门的编解码器，可以使数据更加紧凑。
向量化查询执行:ClickHouse不仅在列中存储数据，而且还在列中处理数据。这将导致更好的CPU缓存利用率，并允许使用SIMD CPU指令。
可伸缩性:ClickHouse可以利用所有可用的CPU内核和磁盘来执行单个查询。不仅在单个服务器上，而且在集群的所有CPU内核和磁盘上。

Attention to Low-Level Details

clickhouse的特色

True Column-Oriented Database Management System

在真正的面向列的DBMS中，没有额外的数据与值一起存储。这意味着必须支持定长值，以避免将其长度“number”存储在值旁边。例如，十亿个uint8类型的值应该消耗大约1gb的未压缩内存，否则会严重影响CPU的使用。即使在未压缩的情况下，也必须紧凑地存储数据(没有任何“垃圾”)，因为解压缩的速度(CPU使用率)主要取决于未压缩数据的量。

Data Compression

除了在磁盘空间和CPU消耗之间进行不同权衡的高效通用压缩编解码器之外，ClickHouse还为特定类型的数据提供专门的编解码器，这使得ClickHouse能够与更小众的数据库(如时间序列数据库)竞争并超越它们。

Parallel Processing on Multiple Cores

Distributed Processing on Multiple Servers

在ClickHouse中，数据可以驻留在不同的分片上。每个分片可以是一组用于容错的副本。所有分片都用于并行运行查询，对用户透明。

SQL Support

Vector Computation Engine

Real-Time Data Inserts

ClickHouse支持带有主键的表。为了快速执行对主键范围的查询，使用合并树对数据进行增量排序。因此，数据可以不断地添加到表中。当摄取新数据时，不需要锁。

Primary Indexes

通过按主键对数据进行物理排序，可以在不到几十毫秒的时间内以低延迟根据特定值或值范围提取数据。

Secondary Indexes

与其他数据库管理系统不同，ClickHouse中的二级索引不指向特定的行或行范围。相反，它们允许数据库提前知道某些数据部分中的所有行都不匹配查询过滤条件，并且根本不读取它们，因此它们被称为数据跳过索引。

Suitable for Online Queries

Support for Approximated Calculations

ClickHouse provides various ways to trade accuracy for performance:

Aggregate functions for approximated calculation of the number of distinct values, medians, and quantiles.
Running a query based on a part (sample) of data and getting an approximated result. In this case, proportionally less data is retrieved from the disk.
Running an aggregation for a limited number of random keys, instead of for all keys. Under certain conditions for key distribution in the data, this provides a reasonably accurate result while using fewer resources.

Adaptive Join Algorithm

ClickHouse自适应地选择如何连接多个表，通过首选散列连接算法，如果有多个大表，则返回到合并连接算法。

Data Replication and Data Integrity Support

ClickHouse使用异步多主复制。在写入任何可用的副本之后，所有剩余的副本都会在后台检索它们的副本。系统在不同的副本上维护相同的数据。大多数故障后的恢复是自动执行的，在复杂的情况下是半自动的。

Role-Based Access Control

ClickHouse使用SQL查询实现用户帐户管理，并允许基于角色的访问控制配置，类似于ANSI SQL标准和流行的关系数据库管理系统。

Features that Can Be Considered Disadvantages

No full-fledged transactions.
Lack of ability to modify or delete already inserted data with a high rate and low latency. There are batch deletes and updates available to clean up or modify data, for example, to comply with GDPR.
The sparse index makes ClickHouse not so efficient for point queries retrieving single rows by their keys.

建表优化

数据类型

时间字段类型

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

空值存储类型

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

分区和索引

分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区 ，也可以指定为以单表一亿数据为例，分区大小控制在 10-30个为最佳。必须指定索引列，ClickHouse中的 索引列即排序列 ，通过 order by指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、 查询频率大的在前 原则；还有基数特别大的不适合做索引列，如用户表的userid字段；通常筛选后的数据满足在百万以内为最佳。

表参数

Index_granularity是用来控制索引粒度的 默认是 8192 如非必须不建议调整。如果表中不是必须保留全量历史数据，建议指定 TTL （生存时间值），可以免去手动过期历史数据的麻烦， TTL 也可以通过 alter table 语句随时修改。

写入和删除优化

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

配置优化

例如cpu、内存、存储资源的配置优化

clickhouse语法优化

count优化

消除子查询重复字段

谓词下推

聚合计算外推

聚合函数消除

删除重复的order by key

删除重复的limit by key

删除重复的using key

标量替换

三元运算优化

等等

单表查询

prewhere替代where

Prewhere和where语句的作用相同用来过滤数据。不同之处在于prewhere只支持*MergeTree族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取select声明的列字段来补全其余属性。当查询列明显多于筛选列时使用Prewhere可十倍提升查询性能，Prewhere会自动优化执行过滤阶段的数据读取方式，降低io操作。
默认情况，我们肯定不会关闭where自动优化成prewhere，但是某些场景即使开启优
化，也不会自动转换成prewhere，需要手动指定prewhere
⚫使用常量表达式
⚫使用默认值为alias类型的字段
⚫包含了arrayJOINglobalInglobalNotIn或者indexHint的查询
⚫select查询的列字段和where的谓词相同
⚫使用了主键字段

数据采样

SAMPLE

列裁剪与分区裁剪

数据量太大时应避免使用select*操作，查询的性能会与查询的字段大小和数量成线性表换，字段越少，消耗的io资源越少，性能就会越高。
分区裁剪就是只读取需要的分区在过滤条件中指定。

orderby结合where、limit

避免构建虚拟列

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

反例
select Income,Age,Income/Age as IncRate from datasets.hits_vl;
正例:拿到Income,Age后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储
select Income,Age  from datasets.hits_vl;

uniqCombined替代distinct

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

使用物化视图

其他注意事项

查询熔断

为了避免因个别慢查询引起的服务雪崩的问题，除了可以为单个查询设置超时以外，还可以配置周期熔断，在一个查询周期内，如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。

关闭虚拟内存

物理内存和虚拟内存的数据交换，会导致查询缓慢，资源允许的情况下关闭虚拟内存

配置join_use_nulls配置

left join右表不存在相应字段则返回该字段相应数据类型的默认值

批量写入时先排序

批量写入数据时，必须空值每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致clickhouse无法及时对新导入的数据进行合并，从而影响查询性能

关注CPU

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

多表关联

用in代替join

当多表联查时查询的数据仅从其中一张表出时可考虑用IN操作而不是JOIN

insert into hits_v2
select a.* from hits_v1 a where a.CounterID in (select CounterID from visits_v1);

反例
insert into table hits_v2
select a.* from hits_v1 a left join visits_v1 b on a.CounterID = b.CounterID;

大小表join

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

分布式表使用GLOBAL

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

使用字典表

将一些需要关联分析的业务创建成字典表进行join操作，前提是字典表不宜太大，因为字典表会常驻内存

提前过滤

通过增加逻辑过滤可以减少数据扫描，达到提高执行速度及降低内存消耗的目的

数据一致性

对数据一致性支持最好的Mergetree，也只是保证最终一致性。
在使用ReplacingMergTree、SummingMergeTree这类表引擎的时候，会出现短暂的数据不一致的情况。
在某些对数据一致性非常敏感的场景，通常由一下集中解决方案

手动OPTIMIZE

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

通过Gruop by去重

执行去重的查询

select
    user_id,
    argMax(score,creat_time) AS score,
    argMax(deleted,creat_time) AS deleted,
    max(creat_time) AS ctime
from test_a
group by user_d
having deleted=0;

argMax(field1field2):按照field2的最大值取field1的值。当我们更新数据时，会写入一行新的数据，例如上面语句中，通过查询最大的create_time得到修改后的score字段值。

通过final查询

当指定FINAL时，ClickHouse在返回结果之前完全合并数据，从而执行给定表引擎在合并期间发生的所有数据转换。
带有FINAL的SELECT查询是并行执行的。max_final_threads设置限制了使用的线程数。
缺点

使用FINAL的查询比不使用FINAL的查询执行得稍微慢一些，因为:
在执行查询时合并数据。
带有FINAL的查询除了读取查询中指定的列外，还读取主键列。
在大多数情况下，避免使用FINAL。常见的方法是使用不同的查询，假设MergeTree引擎的后台进程尚未发生，并通过应用聚合来处理它(例如，丢弃重复项)。
FINAL可以使用FINAL设置自动应用于使用会话或用户配置文件的查询中的所有表。

物化视图（快照）

物化视图和普通视图的区别

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

优缺点

优点：查询速度快，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总
的行数少了，因为都预计算好了。
缺点：它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。

基础

ClickHouse 物化视图会自动在表之间转换数据。它们就像在运行查询后插入并将结果存放在第二个表中的触发器。假设我们有一个表格来记录用户下载，如下所示。

CREATE TABLE download (
  when DateTime,
  userid UInt32,
  bytes Float32
) ENGINE=MergeTree
PARTITION BY toYYYYMM(when)
ORDER BY (userid, when)

INSERT INTO download
  SELECT
    now() + number * 60 as when,
    25,
    rand() % 100000000
  FROM system.numbers
  LIMIT 5000

SELECT
  toStartOfDay(when) AS day,
  userid,
  count() as downloads,
  sum(bytes) AS bytes
FROM download
GROUP BY userid, day
ORDER BY userid, day
┌─────────────────day─┬─userid─┬─downloads─┬───────bytes─┐
│ 2019-09-04 00:00:00 	│     25 │       656 │ 33269129531  │
│ 2019-09-05 00:00:00 	│     25 │      1440 │ 70947968936  │
│ 2019-09-06 00:00:00 	│     25 │      1440 │ 71590088068  │
│ 2019-09-07 00:00:00 	│     25 │      1440 │ 72100523395  │
│ 2019-09-08 00:00:00 	│     25 │        24 │  1141389078  │
└─────────────────────┴───────┴──────────┴─────────────┘

CREATE MATERIALIZED VIEW download_daily_mv
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(day) ORDER BY (userid, day)
POPULATE
AS SELECT
  toStartOfDay(when) AS day,
  userid,
  count() as downloads,
  sum(bytes) AS bytes
FROM download
GROUP BY userid, day

这里有三件重要的事情需要注意。首先，物化视图定义允许使用类似于 CREATE TABLE 的语法，因为该命令实际上将创建一个隐藏的目标表来保存视图数据。我们使用旨在简化求和和计数的 ClickHouse 引擎：SummingMergeTree，它是计算聚合的物化视图的推荐引擎。

其次，视图定义包括关键字 POPULATE。这告诉 ClickHouse 将视图立刻计算 dowload 表中的现有数据，就好像它刚刚插入一样。稍后我们将更多地讨论自动填充。

最后，视图定义包括一个 SELECT 语句，该语句定义了在加载视图时如何转换数据。此查询在表中的新数据上运行，以计算每个用户 ID 每天的下载次数和总字节数。它本质上与我们以交互方式运行的查询相同，但在这种情况下，结果将被放入隐藏的目标表中。我们可以跳过排序，因为视图定义已经确保了排序顺序。

现在让我们直接从物化视图中查询

SELECT * FROM download_daily_mv
ORDER BY day, userid 
LIMIT 5
┌─────────────────day─┬─userid─┬─downloads─┬───────bytes─┐
│ 2019-09-04 00:00:00 	│     25 │       656 │ 33269129531  │
│ 2019-09-05 00:00:00 	│     25 │      1440 │ 70947968936  │
│ 2019-09-06 00:00:00 	│     25 │      1440 │ 71590088068  │
│ 2019-09-07 00:00:00 	│     25 │      1440 │ 72100523395  │
│ 2019-09-08 00:00:00 	│     25 │        24 │  1141389078  │
└─────────────────────┴───────┴──────────┴─────────────┘

与之前的查询完全相同。原因是上面介绍的 POPULATE 关键字。它确保源表中的现有数据自动加载到视图中。但是有一个重要的警告：如果在视图填充时插入新数据，ClickHouse 将错过它们。我们将在本系列的第二部分展示如何手动插入数据并避免丢失数据的问题。

现在尝试使用其他用户向表中添加更多数据。

INSERT INTO download
  SELECT
    now() + number * 60 as when,
    22,
    rand() % 100000000
  FROM system.numbers
  LIMIT 5000

如果您从物化视图中进行选择，您会看到它现在包含用户 ID 22 和 25 的总数。请注意，一旦 INSERT 完成 新数据立即可用，视图就会被填充。这是 ClickHouse 物化视图的一个重要特性，使它们对实时分析非常有用。

这是查询和新结果。

SELECT * FROM download_daily_mv ORDER BY userid, day
┌─────────────────day─┬─userid─┬─downloads─┬───────bytes─┐
│ 2019-09-04 00:00:00 	│     22 │       654 │ 31655571524  │
│ 2019-09-05 00:00:00 	│     22 │      1440 │ 71514547751  │
│ 2019-09-06 00:00:00 	│     22 │      1440 │ 71839871989  │
│ 2019-09-07 00:00:00 	│     22 │      1440 │ 70915563752  │
│ 2019-09-08 00:00:00 	│     22 │        26 │  1227350921  │
│ 2019-09-04 00:00:00 	│     25 │       656 │ 33269129531  │
│ 2019-09-05 00:00:00 	│     25 │      1440 │ 70947968936  │
│ 2019-09-06 00:00:00 	│     25 │      1440 │ 71590088068  │
│ 2019-09-07 00:00:00 	│     25 │      1440 │ 72100523395  │
│ 2019-09-08 00:00:00 	│     25 │        24 │  1141389078  │
└─────────────────────┴───────┴──────────┴─────────────┘

作为最后一个示例，让我们使用每日视图按月选择总计。在这种情况下，我们将每日视图视为普通表格并按月分组如下。我们添加了 WITH TOTALS 子句，该子句打印了一个方便的聚合总和。

SELECT
    toStartOfMonth(day) AS month,
    userid,
    sum(downloads),
    sum(bytes)
FROM download_daily_mv
GROUP BY userid, month WITH TOTALS
ORDER BY userid, month
┌──────month─┬─userid─┬─sum(downloads)─┬───sum(bytes)─┐
│ 2019-09-01 	│     22 │           5000 │ 247152905937  │
│ 2019-09-01 	│     25 │           5000 │ 249049099008  │
└────────────┴───────┴───────────────┴─────────────┘
Totals:
┌──────month─┬─userid─┬─sum(downloads)─┬───sum(bytes)─┐
│ 0000-00-00 	│      0 │          10000 │ 496202004945  │
└────────────┴───────┴───────────────┴─────────────┘

下图说明了数据的逻辑流。

如图所示，源表上的 INSERT 值被转换并应用于目标表。要填充视图，您只需将值插入源表。您可以从目标表以及物化视图中进行查询。从物化视图的查询会被传递到视图自动创建的内部表。从图中还需要注意另一件重要的事情，物化视图创建一个具有特殊名称的私有表来保存数据。如果您通过 DROP TABLE download_daily_mv 删除实体化视图，则私有表将消失。如果您需要更改视图，则需要将其删除并使用新数据重新创建。(后面会针对这个特性给出解决方案)

我们刚刚查看的示例使用 SummingMergeTree 创建一个视图来添加每日用户下载量。我们在物化视图中的 SELECT 上使用标准 SQL 语法。这是 SummingMergeTree 引擎的一项特殊功能，仅适用于求和和计数。对于其他类型的聚合，我们需要使用不同的方法。

此外，我们的示例使用 POPULATE 关键字将现有表数据发布到视图创建的私有目标表中。如果在填充视图时有新的 INSERT 到达，ClickHouse 将错过它们。当您是唯一使用数据集的人时，这个限制很容易解决，但对于不断加载数据的生产系统来说是个问题。此外，当视图被删除时，私有表也会消失。这使得很难更改视图以适应源表中的模式更改。

在下一节中，我们将展示如何创建物化视图来计算其他类型的聚合，例如平均值或最大值/最小值。我们还将展示如何显式定义目标表并使用我们自己的 SQL 语句手动将数据加载到其中。我们还将简要介绍模式迁移。同时，我们希望您喜欢这个简短的介绍，并发现这些示例很有用。

进阶

在第 1 部分中，我们介绍了一种构建 ClickHouse 物化视图的方法，该视图使用 SummingMergeTree 引擎计算总和/计数。 SummingMergeTree 可以对这两种类型的聚合使用普通的 SQL 语法。我们还让物化视图定义自动为数据创建基础表。这两种技术都很快，但对生产系统有限制。在当前帖子中，我们将展示如何在现有表上创建具有一系列聚合类型的物化视图；此方法适用当您需要计算的不仅仅是简单的总和时；对于表有大量到达数据或必须处理架构更改的情况，它也很方便。

使用状态函数和TO表创建更灵活的视图

在以下示例中，我们将测量来自设备的读数。让我们从表定义开始。

CREATE TABLE counter (
  when DateTime DEFAULT now(),
  device UInt32,
  value Float32
) ENGINE=MergeTree
PARTITION BY toYYYYMM(when)
ORDER BY (device, when)

接下来，我们添加足够的数据以使查询时间变得足够慢：10 台设备的 10 亿行数据。

INSERT INTO counter
  SELECT
    toDateTime('2015-01-01 00:00:00') + toInt64(number/10) AS when,
    (number % 10) + 1 AS device,
    (device * 3) +  (number/10000) + (rand() % 53) * 0.1 AS value
  FROM system.numbers LIMIT 1000000

现在让我们看一个我们希望定期运行的示例查询。它汇总了整个采样期间所有设备的所有数据。在这种情况下，这意味着表中价值 3.25 年的数据，所有这些数据都在 2019 年之前。

SELECT
    device,
    count(*) AS count,
    max(value) AS max,
    min(value) AS min,
    avg(value) AS avg
FROM counter
GROUP BY device
ORDER BY device ASC
. . .
10 rows in set. Elapsed: 2.709 sec. Processed 1.00 billion rows, 8.00 GB (369.09 million rows/s., 2.95 GB/s.)

前面的查询很慢，因为它必须读取表中的所有数据才能得到答案。我们想要设计一个读取更少数据的物化视图。事实证明，如果我们定义一个每天汇总数据的视图，ClickHouse 将正确汇总整个时间间隔内的每日总数。

与我们之前的简单示例不同，我们将自己定义目标表。这样做的好处是表现在是可见的，这使得加载数据以及进行模式迁移变得更加容易。这是目标表定义。

CREATE TABLE counter_daily (
  day DateTime,
  device UInt32,
  count UInt64,
  max_value_state AggregateFunction(max, Float32),
  min_value_state AggregateFunction(min, Float32),
  avg_value_state AggregateFunction(avg, Float32)
)
ENGINE = SummingMergeTree()
PARTITION BY tuple()
ORDER BY (device, day)

表定义引入了一种新的数据类型，称为聚合函数，它保存部分聚合的数据。除总和或计数以外的聚合都需要该类型。接下来我们创建对应的物化视图。它从 counter（源表）中选择，并使用 CREATE 语句中的特殊 TO 语法将数据发送到 counter_daily（目标表）。

CREATE MATERIALIZED VIEW counter_daily_mv
TO counter_daily
AS SELECT
    toStartOfDay(when) as day,
    device,
    count(*) as count,
    maxState(value) AS max_value_state,
    minState(value) AS min_value_state,
    avgState(value) AS avg_value_state
FROM counter
WHERE when >= toDate('2019-01-01 00:00:00')
GROUP BY device, day
ORDER BY device, day

TO 关键字让我们指向目标表，但有一个缺点。 ClickHouse 不允许将 POPULATE 关键字与 TO 一起使用。因此，物化视图开始没有数据。我们将手动加载数据。但我们还将使用一个很好的技巧，使我们能够避免在同时进行活动数据加载的情况下出现问题。

请注意，视图定义有一个 WHERE 子句。这表示应忽略 2019 年之前的任何数据。我们现在有一种方法可以以不丢失数据的方式处理数据加载。该视图将处理 2019 年到达的新数据。同时，我们可以使用 INSERT 加载 2018 年及之前的旧数据。

让我们通过将新数据加载到 counter 表中来演示它是如何工作的。新数据将于 2019 年开始，并应自动加载到视图中。

INSERT INTO counter
  SELECT
    toDateTime('2019-01-01 00:00:00') + toInt64(number/10) AS when,
    (number % 10) + 1 AS device,
    (device * 3) +  (number / 10000) + (rand() % 53) * 0.1 AS value
  FROM system.numbers LIMIT 10000000

现在让我们使用以下 INSERT 手动加载旧数据。它会加载 2018 年及之前的所有数据。

INSERT INTO counter_daily
SELECT
  toStartOfDay(when) as day,
  device,
  count(*) AS count,
  maxState(value) AS max_value_state,
  minState(value) AS min_value_state,
  avgState(value) AS avg_value_state
FROM counter
WHERE when < toDateTime('2019-01-01 00:00:00')
GROUP BY device, day
ORDER BY device, day

我们终于准备好从视图中选择数据。与目标表和物化视图一样，ClickHouse 使用专门的语法从视图中进行选择。

SELECT
  device,
  sum(count) AS count,
  maxMerge(max_value_state) AS max,
  minMerge(min_value_state) AS min,
  avgMerge(avg_value_state) AS avg
FROM counter_daily
GROUP BY device
ORDER BY device ASC
┌─device─┬────count─┬───────max─┬─────min─┬──────────────avg─┐
│      1 │ 101000000 │  100008.17 │   3.008 │  49515.50042561026 │
│      2 │ 101000000 │ 100011.164 │  6.0031 │ 49518.500627177054 │
│      3 │ 101000000 │  100014.17 │  9.0062 │  49521.50087863756 │
│      4 │ 101000000 │  100017.04 │ 12.0333 │   49524.5006612177 │
│      5 │ 101000000 │  100020.19 │ 15.0284 │  49527.50092650661 │
│      6 │ 101000000 │  100023.15 │ 18.0025 │  49530.50098047898 │
│      7 │ 101000000 │ 100026.195 │ 21.0326 │  49533.50099656529 │
│      8 │ 101000000 │  100029.18 │ 24.0297 │  49536.50119239665 │
│      9 │ 101000000 │ 100031.984 │ 27.0258 │  49539.50119958179 │
│     10 │ 101000000 │  100035.17 │ 30.0229 │ 49542.501308345716 │
└───────┴──────────┴───────────┴────────┴───────────────────┘
10 rows in set. Elapsed: 0.003 sec. Processed 11.70 thousand rows, 945.49 KB (3.76 million rows/s., 304.25 MB/s.)

此查询正确汇总了所有数据，包括新行。您可以通过在 counter 表上重新运行原始 SELECT 来检查。不同之处在于物化视图返回数据的速度快了大约 900 倍。值得学习一些新语法来获得它！

在这一点上，我们可以回过头来解释幕后发生的事情。

聚合函数

聚合函数(AggregateFunction)就像收集器，允许 ClickHouse 从分布在许多部分的数据中构建聚合。下图显示了它如何计算平均值。我们从源表中的一个可选值开始。物化视图使用 avgState 函数将数据转换为部分聚合，该函数是一种内部结构。最后，在选择数据时，应用 avgMerge 将部分聚合总计为结果数。

部分聚合使物化视图能够处理分布在多个节点上的许多部分的数据。即使您按变量更改分组，合并函数也会正确组装聚合。仅仅将简单的平均值组合起来是行不通的，因为当每个部分平均值加到总数中时，它们将缺乏缩放每个部分平均值所需的权重。这种行为有一个重要的后果。

还记得上面提到 ClickHouse 可以使用带有汇总每日数据的物化视图来回答我们的示例查询吗？这是聚合函数如何工作的结果。这意味着我们的每日视图也可以回答有关周、月、年或整个间隔的问题。

ClickHouse 有点不寻常，它直接暴露了 SQL 语法中的部分聚合，但它们解决问题的方式非常强大。当您设计物化视图时，请尝试使用每日总结之类的技巧来解决单个视图的多个问题。一个视图可以回答很多问题。

支持物化视图的表引擎

ClickHouse 有多个可用于物化视图的引擎。AggregatingMergeTree 引擎仅适用于聚合函数。如果要进行计数或求和，则需要使用目标表中的聚合函数数据类型来定义它们。您还需要在视图和选择语句中使用状态和合并功能。例如，要处理计数，您需要在上面的工作示例中使用 count State(count) 和 count Merge(count)。

我们建议使用 SummingMergeTree 引擎在物化视图中进行聚合。它可以很好地处理聚合函数。然而，它隐藏了它们的总和和计数，这对于简单的情况很方便。在这种情况下，它不会阻止您使用状态和合并功能；只是您不必这样做。同时它完成了聚合合并树所做的一切。

管理 schema

数据库 schema 在生产系统中往往会发生变化，尤其是那些正在积极开发的系统。当使用具有显式目标表的物化视图时，您可以相对轻松地管理此类更改。

让我们举一个简单的例子。假设 counter 表的名称更改为 counter_replicated。应用此更改后，物化视图将不起作用。更糟糕的是会阻塞 INSERT 到 counter 器表。您可以按如下方式处理更改。

-- Delete view prior to schema change.
DROP TABLE counter_daily_mv
-- Rename source table.
RENAME TABLE counter TO counter_replicated
-- Recreate view with correct source table name.
CREATE MATERIALIZED VIEW counter_daily_mv
TO counter_daily
AS SELECT
  toStartOfDay(when) as day,
  device,
  count(*) as count,
  maxState(value) AS max_value_state,
  minState(value) AS min_value_state,
  avgState(value) AS avg_value_state
FROM counter_replicated
GROUP BY device, day
ORDER BY device, day

根据架构迁移中的实际步骤，您可能必须解决在更改物化视图定义时到达的丢失数据。您可以使用过滤条件和手动加载来处理它，如我们在主要示例中所示。

物化视图的管道和数据大小

最后，我们再来看看数据表和物化视图之间的关系。目标表是普通表。您可以从目标表或物化视图中选择数据。没有区别。此外，如果您删除物化视图，该表仍然存在。正如我们刚刚展示的，您可以通过简单地删除并重新创建视图来更改视图。如果您需要更改目标表本身，请像对任何其他表一样运行 ALTER TABLE 命令。

该图还显示了源表和目标表的数据大小。物化视图通常比它们聚合数据的表小得多。这肯定是这里的情况。以下查询显示了此示例的大小差异。

SELECT
  table,
  formatReadableSize(sum(data_compressed_bytes)) AS tc,
  formatReadableSize(sum(data_uncompressed_bytes)) AS tu,
  sum(data_compressed_bytes) / sum(data_uncompressed_bytes) AS ratio
FROM system.columns
WHERE database = currentDatabase()
GROUP BY table
ORDER BY table ASC
┌─table────────────┬─tc─────────┬─tu─────────┬──────────────ratio─┐
│ counter          	│ 6.52 GiB   	│ 11.29 GiB  	│ 0.5778520850660066 	 │
│ counter_daily    	│ 210.35 KiB 	│ 422.75 KiB 	│ 0.4975675675675676 	 │
│ counter_daily_mv 	│ 0.00 B     	│ 0.00 B     	│                nan 	 │
└─────────────────┴────────────┴────────────┴────────────────────┘

如计算所示，物化视图目标表比派生物化视图的源数据小大约 30,000 倍。这种差异极大地加快了查询速度。正如我们之前展示的，当使用来自物化视图的数据时，我们的测试查询运行速度提高了大约 900 倍。

MySQL引擎

MaterializeMySQL引擎

This is an experimental feature that should not be used in production.

参考文档：

clickhouse官方文档 https://clickhouse.com/docs/en/intro
尚硅谷clickhouse学习文档 http://m.atguigu.com/
CSDN博主:小王是个弟弟 博客:clickhouse物化视图 https://blog.csdn.net/qq_41858402/article/details/125499359