ClickHouse 概述

What Is ClickHouse? 乌拉

ClickHouse® is a column-oriented database management system (DBMS) for online analytical processing of queries (OLAP). https://clickhouse.tech/docs/en/

ClickHouse 是战斗民族的 Yandex号称小谷歌 于 2016 年开源的列式存储数据库（DBMS），使用 C++ 语言编写，主要用于在线分析处理查询（OLAP），能够使用 SQL 查询实时生成分析数据报告，2020年底在国内遍地开花，很多大厂都在部署大量的节点,从战略层面直接撇开Hadoop生态(战略核潜艇级别)，乌拉。

 

ClickHouse 采用类 LSM Tree 的结构（同学们是不是很惊喜，有没有想起来HBASE），数据写后定期在后台 Compaction 。通过类 LSM tree 的结构，ClickHouse 在数据导入时全部是顺A ppend 写，写入后数据段不可更改，在后台Compaction 时也是多个段M erge sort 后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力，即便在 HDD 上也有着优异的写入性能。

Key Properties of OLAP Scenario 

• The vast majority of requests are for read access.
• Data is updated in fairly large batches (> 1000 rows), not by single rows; or it is not updated at all.
• Data is added to the DB but is not modified.
• For reads, quite a large number of rows are extracted from the DB, but only a small subset of columns.
• Tables are “wide,” meaning they contain a large number of columns.
• Queries are relatively rare (usually hundreds of queries per server or less per second).
• For simple queries, latencies around 50 ms are allowed.
• Column values are fairly small: numbers and short strings (for example, 60 bytes per URL).
• Requires high throughput when processing a single query (up to billions of rows per second per server).
• Transactions are not necessary.
• Low requirements for data consistency.
• There is one large table per query. All tables are small, except for one.
• A query result is significantly smaller than the source data. In other words, data is filtered or aggregated, so the result fits in a single server’s RAM.

It is easy to see that the OLAP scenario is very different from other popular scenarios (such as OLTP or Key-Value access). So it does not make sense to try to use OLTP or a Key-Value DB for processing analytical queries if you want to get decent performance. For example, if you try to use MongoDB or Redis for analytics, you will get very poor performance compared to OLAP databases.

OLAP 场景的关键属性 

• 绝大多数请求是读取访问。
• 数据以相当大的批次（> 1000 行）更新，而不是按单行更新；或者它根本没有更新。
• 数据被添加到数据库中，但不会被修改。
• 对于读取，从数据库中提取了相当多的行，但只提取了一小部分列。
• 表是“宽的”，这意味着它们包含大量列。
• 查询相对较少（通常每台服务器有数百个查询或每秒更少）。
• 对于简单查询，允许大约 50 毫秒的延迟。
• 列值相当小：数字和短字符串（例如，每个 URL 60 个字节）。
• 处理单个查询时需要高吞吐量（每台服务器每秒高达数十亿行）。
• 交易不是必需的。
• 对数据一致性要求低。
• 每个查询有一个大表。除了一张桌子，所有桌子都很小。
• 查询结果明显小于源数据。换句话说，数据被过滤或聚合，因此结果适合单个服务器的 RAM。

不难看出，OLAP 场景与其他流行的场景（如 OLTP 或 Key-Value 访问）有很大的不同。因此，如果您想获得不错的性能，尝试使用 OLTP 或键值数据库来处理分析查询是没有意义的。例如，如果您尝试使用 MongoDB 或 Redis 进行分析，与 OLAP 数据库相比，您将获得非常差的性能。

Why Column-Oriented Databases Work Better in the OLAP Scenario 

Column-oriented databases are better suited to OLAP scenarios: they are at least 100 times faster in processing most queries. The reasons are explained in detail below, but the fact is easier to demonstrate visually:

面向列的数据库更适合 OLAP 场景：它们处理大多数查询的速度至少快 100 倍。下面详细解释原因，但事实更容易直观地展示：

Row-oriented DBMS

 Column-oriented DBMS

See the difference?

Input/output 

1. For an analytical query, only a small number of table columns need to be read. In a column-oriented database, you can read just the data you need. For example, if you need 5 columns out of 100, you can expect a 20-fold reduction in I/O.
2. Since data is read in packets, it is easier to compress. Data in columns is also easier to compress. This further reduces the I/O volume.
3. Due to the reduced I/O, more data fits in the system cache.

For example, the query “count the number of records for each advertising platform” requires reading one “advertising platform ID” column, which takes up 1 byte uncompressed. If most of the traffic was not from advertising platforms, you can expect at least 10-fold compression of this column. When using a quick compression algorithm, data decompression is possible at a speed of at least several gigabytes of uncompressed data per second. In other words, this query can be processed at a speed of approximately several billion rows per second on a single server. This speed is actually achieved in practice.

输入输出 

1. 对于分析查询，只需要读取少量的表列。在面向列的数据库中，您可以只读取您需要的数据。例如，如果您需要 100 列中的 5 列，您可以预期 I/O 减少 20 倍。
2. 由于数据是以包的形式读取的，所以更容易压缩。列中的数据也更容易压缩。这进一步减少了 I/O 量。
3. 由于减少了 I/O，系统缓存中可以容纳更多数据。

例如，查询“统计每个广告平台的记录数”需要读取一个“广告平台ID”列，未压缩占用1个字节。如果大部分流量不是来自广告平台，那么您可以预期此列至少会压缩 10 倍。使用快速压缩算法时，数据解压缩速度至少可以达到每秒几 GB 的未压缩数据。换句话说，该查询可以在单个服务器上以每秒大约数十亿行的速度处理。这个速度实际上是在实践中达到的。

CPU 

Since executing a query requires processing a large number of rows, it helps to dispatch all operations for entire vectors instead of for separate rows, or to implement the query engine so that there is almost no dispatching cost. If you do not do this, with any half-decent disk subsystem, the query interpreter inevitably stalls the CPU. It makes sense to both store data in columns and process it, when possible, by columns.

There are two ways to do this:

1. A vector engine. All operations are written for vectors, instead of for separate values. This means you do not need to call operations very often, and dispatching costs are negligible. Operation code contains an optimized internal cycle.

2. Code generation. The code generated for the query has all the indirect calls in it.

This is not done in “normal” databases, because it does not make sense when running simple queries. However, there are exceptions. For example, MemSQL uses code generation to reduce latency when processing SQL queries. (For comparison, analytical DBMSs require optimization of throughput, not latency.)

Note that for CPU efficiency, the query language must be declarative (SQL or MDX), or at least a vector (J, K). The query should only contain implicit loops, allowing for optimization.

中央处理器 

由于执行查询需要处理大量行，因此它有助于为整个向量而不是单独的行调度所有操作，或者实现查询引擎以便几乎没有调度成本。如果你不这样做，对于任何半体面的磁盘子系统，查询解释器不可避免地会停止 CPU。将数据存储在列中并在可能的情况下按列进行处理是有意义的。

有两种方法可以做到这一点：

1. 矢量引擎。所有操作都是为向量编写的，而不是为单独的值编写的。这意味着您不需要经常调用操作，并且调度成本可以忽略不计。操作码包含优化的内部循环。

2. 代码生成。为查询生成的代码中包含所有间接调用。

这不是在“普通”数据库中完成的，因为在运行简单查询时它没有意义。但是，也有例外。例如，MemSQL 使用代码生成来减少处理 SQL 查询时的延迟。（相比之下，分析型 DBMS 需要优化吞吐量，而不是延迟。）

请注意，为了 CPU 效率，查询语言必须是声明性的（SQL 或 MDX），或者至少是向量（J，K）。查询应该只包含隐式循环，允许优化。

相关学习资料

链接:https://pan.baidu.com/s/1DjCYs9PhqJpdKqoqXk2fjQ  密码:lk21