wdgcd logo wdgcd personal lab
Login
全文 4,761 字 约 14 分钟

clickhouse20问-1

bg

1、什么是 ClickHouse？它的主要应用场景是什么？

ClickHouse 注重高性能、大规模数据分析，尤其适用于 olap；能以极高的读取速度处理大数据集，并且支持非常复杂的查询操作；

主要应用场景包括：

a、实时数据分析

b、日志和事件数据存储

c、商业智能 BI；

列式存储、数据压缩、并行查询处理、分区和复制、丰富的函数和操作、开源与社区；

2、ClickHouse 的存储引擎有哪些？常见的引擎是什么？

a、MergeTree 家族：

MergeTree：最常用的存储引擎，支持数据表分段存储和分段查找，适用于处理大规模数据；

ReplicatedMergeTree：支持分布式数据存储，提供数据复制功能，能够提升系统的容灾能力和读取性能

ReplacingMergeTree：在普通的 MergeTree 基础上，增加了替换重复数据的功能，非常适合处理不断更新的实时数据；

SummingMergeTree：支持对相同主键行进行累加，是一个非常典型的时间序列数据处理引擎；

AggregatingMergeTree：在导入数据时即进行聚合操作，适用于需要预计算一些聚合指标的场景；

b、Log 家族：

Log：用于小规模表及日志型数据表的引擎，数据按顺序插入，不创建索引，适合存储频繁插入而少量查询的数据；

TinyLog：相较于 Log，更小的表，适用于开发测试或非常小的数据量的场景；

StripeLog：与 Log 类似，但使用 Stripe 格式，在某些读取场景下性能表现更佳

c、Buffer 引擎：作为一个中间缓存层，用于批量插入数据，提高写性能，减少频繁写磁盘带来的性能问题，缓冲中的数据会在合适时机自动刷入后端表引擎；

d、Distributed 引擎：这种引擎把数据分布到多个 clickhouse 节点上，适用于大规模分布式部署，可以显著提升查询性能和系统的水平扩展能力；

e、MaterializedView 引擎：可以将复杂视图的结果物化为物理存储，减少实时复杂查询时的计算消耗，将计算结果缓存到物理表中，以提高查询效率；

3、在 ClickHouse 中，如何创建一张表？常见的表结构语法是什么？

CREATE TABLE IF NOT EXISTS your_table_name 
(
    column1 DataType,
    column2 DataType,
    ...
) 
ENGINE = StorageEngine;

分布式表：

CREATE TABLE distributed_table
AS complex_table
ENGINE = Distributed(cluster_name, database_name, complex_table, rand());

4、ClickHouse 中的 MergeTree 引擎是什么？它有哪些优势？

MT 引擎是 clickhouse 数据库中的核心储存引擎，它以高效的查询性能和数据压缩能力著称，尤其适用于大数据场景，MT 引擎采用分区机制和数据合并策略，支持高性能的表分区和数据排序，从而加速查询操作：

MT 的优势有：

a、高效的数据压缩：使用多种压缩算法来减少磁盘空间占用；如 LZ4、ZSTD 等，根据数据特性选择适合的压缩算法可以显著节省存储空间，同时也能加速查询；

b、快速的数据写入：支持批量插入数据，高效处理大规模数据写入；

c、快速的查询性能：利用排序键和索引，显著提升读取性能；

d、支持分区：允许对数据进行分区管理，提高查询效率和灵活性；

e、数据合并机制：能自动合并小文件为大文件，优化存储和查询性能

5、ClickHouse 中，如何进行数据插入和批量插入？

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

INSERT INTO table_name (column1, column2, ...) VALUES 
(value1_1, value2_1, ...),
(value1_2, value2_2, ...),
...;

6、ClickHouse 中的分区和分桶机制是什么？如何实现？

分区是指将数据根据某个或多个字段的值，划分到不同的物理文件中，分区有助于提高查询效率，因为查询可以直接跳过不想相关的分区，从而减少扫描的数据量；

CREATE TABLE example_table
(
    id UInt32,
    name String,
    event_date Date
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date) 
ORDER BY id;

分桶（也称分片或 sharding）是将数据进一步细化划分成更小的部分，这些小部分可以分布在不同的物理节点上，从而实现数据的分布式存储和并行处理；

CREATE TABLE distributed_example_table
ENGINE = Distributed(cluster_name, database_name, example_table, id);

7、ClickHouse 如何进行数据压缩？常见的压缩算法有哪些？

clickhouse 具有非常高效的数据压缩机制，是 clickhouse 优化存储和查询性能的重要手段之一；clickhouse 通过分块将列式数据分为若干块，然后对每个块进行压缩，不同的数据类型和不同的数据分布情况可能适用不同的压缩算法；

常见的压缩算法：

LZ4：默认的压缩算法，压缩和解压速度非常快，适合大多数场景；

ZSTD：压缩比通常比 LZ4 更高，但压缩和解压速度稍慢；

LZ77：基于字典的压缩算法，适合特定场景；

Delta：对数值型的数据，通过存储相邻数据的差异值来实现压缩，高效地处理具有连续特性的数值数据

Gorilla：专用于时间序列数据的压缩算法，对于时间序列的值变化较少的情况效果非常好；

clickhouse 允许用户在创建表时使用特定的压缩算法，同时也支持对不同的列使用不同的压缩算法，使得数据存储更加灵活和高效，例如：

CREATE TABLE example (
    event_date Date CODEC(ZSTD),
    value Int32 CODEC(Delta, ZSTD)
) ENGINE = MergeTree() PARTITION BY event_date ORDER BY (event_date, value);

8、在 ClickHouse 中，如何执行简单的 SQL 查询操作？

--

9、ClickHouse 的数据类型有哪些？如何选择合适的数据类型？

数值类型：Int8、Int16、Int32、Int64、UInt8、UInt16、UInt32、UInt64、Float32、Float64

日期和时间类型：Date 类型表示的时间精确到天，适用于日期数据；DateTime 则可以表示到秒的时间，适用于需要精确时间的数据；如果有更高精度的需求，可以利用 DateTime64；

字符串类型：String 类型比较灵活，但若知道字符串的固定长度，使用 FixedString 会在存储和查询时更高效；

数组和元组类型：Array 用来存储一组类型相同的数据，比如一系列数值或字符串、Tuple 允许存储多个不同类型的数据组合，适合一些数据记录中含有不同数据类型的情况；

枚举类型：Enum8 和 Enum16 是效率最高的选项，常用于存储状态或分类数据，可以用名字替代数字，提升可读性；

UUID 和 IP 类型

10、ClickHouse 是如何支持列式存储的？列式存储的优势是什么？

ClickHouse 将每一列的数据分别存储在不同的文件或数据块中，这种存储方式使得它在大批量数据分析任务时，显著提高查询性能：

a、在只需要读取特定列时，大大提高查询性能；

b、同一列的数据类型相同，数值范围较为集中，压缩效率更高；

c、容易对单独列进行分区、排序和索引简历，进一步提升查询性能；

clickhouse 将数据划分为大小相同的块 blocks，每个块中的数据是按照列存储的；

clickhouse 在查询过程中使用向量化执行引擎，因为数据是按列存储的，向量化执行可以对列中的数据批量操作从而优化 cpu 利用率，提升计算效率；

11、ClickHouse 中的物化视图是什么？它的应用场景是什么？

物化视图是一种可以在 clickhouse 中使用的技术，它不仅可以储存查询的数据结果，还能在底层持续更新，以确保数据的最新状态；物理视图常用于提高大数据查询的执行效率，避免在每次查询时都重新计算复杂的聚合结果或过滤操作；

物化视图的优势：

a、性能提升：将繁重的计算预先执行，并存储结果，使得后续查询的响应时间大幅缩短；

b、资源优化：减少高频率、大数据量查询对系统的压力，通过提前计算好频繁使用的结果，避免重复消耗计算资源；

创建物化视图：

CREATE MATERIALIZED VIEW my_materialized_view
TO target_table AS
SELECT
    column1,
    SUM(column2) as summed_column2
FROM source_table
GROUP BY column1;

物化视图的刷新：

clickhouse 平台下，物化视图的更新是基于数据源的表进行实时增量更新的，无须手动更新，物化视图会自动同步源表的数据变更，大大减少了使用中的维护工作；

适用的应用场景：

实时数据聚合：比如网站统计用户行为数据，使用物化视图来构建实时更新的聚合统计指标；

复杂查询优化：对于那些需要频繁执行的、聚合和连接操作较多的查询，使用物化视图能够极大减少计算开销；

报表和分析

注意：物化视图的存储是有相应代价的，特别是对于大数据量的存储系统；同时虽然物化视图可以做到实时同步，但这种同步可能会稍微之后，构建具体场景合理评估是否可以接受这样的延迟；

12、在 ClickHouse 中，如何配置 MergeTree 表的分区策略？

partition by

查看分区：

SHOW PARTITION FROM t

13、ClickHouse 是如何处理大规模数据查询的？有哪些查询优化手段？

a、列式存储：使用列式存储结构，这意味着数据按列存储而不是按行存储，这种方式让 clickhouse 在读取数据时可以仅读取所需的列，大大减少了 I/O 的开销；

b、数据分片和分区：数据表可以按照一定规则分片和分区存储，每个查询只需访问相关的分片或分区

c、向量化执行：对数据批量处理，减少了一条条数据处理的开销，提高了查询速度；例如在进行聚合操作时，向量化可以减少 cpu 缓存命中率，提前批量预测数据处理路径，大大加速操作；

d、索引机制：主键索引、跳跃表索引（提高了对排序数据的查询效率）、稀疏索引和物化视图（针对高基数字段和常用查询模式进行优化）

e、内存和 cpu 优化：多核并行：充分利用 cpu 的多核资源；批量处理：减少反复读取内存和缓存的开销；simd 指令：部分算法利用 simd 指令来进行加速

f、查询优化技术：查询重写：自动将某些常见的查询模式转换成等价但更高效的查询；代价模型：根据统计信息预估查询代价，选择最优的执行计划；物化视图：预先计算并存储某些复杂查询的结果，在实际查询时直接使用；

g、数据压缩

14、在 ClickHouse 中，如何通过物化视图提高查询性能？

a、定义创建物化视图的查询，并确保其基础表是已经存在的；

b、使用 CREATE MATERIALIZED VIEW 语句定义物化视图，并指定目标表用于存储计算结果；

c、在查询时，直接从物化视图对应的目标表进行查询即可，大大减少查询的时间；

CREATE MATERIALIZED VIEW my_materialized_view
    TO target_table
    AS
SELECT column1, SUM(column2) FROM source_table GROUP BY column1;

15、ClickHouse 中的分布式表如何工作？如何实现数据的水平扩展？

clickhouse 的分布式表是一种逻辑表，用来将查询请求分散到多个分片和副本上，通过底层的分布式存储和多副本机制来提高查询的效率和数据的高可用性；

分布式表的概念：分布式表本质上是一种逻辑表，用于将数据映射到物理层面的多个打开的节点，每个节点包含一个或多个数据分片；

水平扩展的实现：实现方式主要包括增加更多的节点（水平扩展）来分担存储和查询负载，以及通过数据的 Sharding 和 Replication 策略提高存储和查询的性能；

架构设计：

Shards 分片：每个 shard 存放一部分数据，这些数据彼此分离，sharding 机制决定数据应该写入或在哪些分片上进行查询；

Replicas 副本：每个 Shard 可以有多个副本，这些副本适用于数据的冗余存储，以增加可靠性和高可用性，当一个副本宕机时，其他副本依旧可以提供服务；

分布式表的创建和使用：

创建分布式表：要创建分布式表，就要指定表的数据源集群和分片策略；

Sharding Key：指定 Sharding Key 使得写入数据时，数据能够有逻辑地分配到不同的 Shard 中，例如：

CREATE TABLE distributed_table AS base_table ENGINE = Distributed(cluster_name, database_name, table_name, sharding_key);

副本管理：配合 Zookeeper 来进行副本管理，Zookeeper 维护分片和副本的元数据状态，确保数据在多个副本间的一致性；

查询过程：当向分布式表发送查询请求时，clickhouse 会根据表的分布式元数据，自动将查询请求路由到多个分片进行并行处理，然后再将各个分片的结果汇总给用户；

并行写入：通过分片键将输入数据分割成多个子集并行写入不同的分片，从而提高写入速度；

水平扩展的具体操作：

增加节点：可以动态地向集群中添加新节点，而不需要停止服务；

重分片：在新节点加入以后，可能需要对数据进行重新分片，以均衡数据分布和查询负载；

16、在 ClickHouse 中，如何使用 TTL 自动清理数据？

a、定义 TTL，在创建表时，可以在表的结构中指定 TTL 规则

CREATE TABLE my_table (
    id UInt32,
    name String,
    created_date DateTime
) ENGINE = MergeTree()
TTL created_date + INTERVAL 30 DAY;

b、修改已有表的 TTL，如果需要修改已有表的 TTL 规则，可以使用 ALTER TABLE 命令，例如：

ALTER TABLE my_table
MODIFY TTL created_date + INTERVAL 30 DAY;

c、多条 TTL 规则

CREATE TABLE my_table (
    id UInt32,
    name String,
    created_date DateTime,
    event_month Date
) ENGINE = MergeTree()
TTL created_date + INTERVAL 30 DAY,
    event_month + INTERVAL 365 DAY DELETE WHERE name = 'obsolete';

d、分区级别的 TTL

CREATE TABLE my_table (
    id UInt32,
    name String,
    created_month Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_month)
TTL created_month + INTERVAL 12 MONTH;

e、物化视图：

CREATE MATERIALIZED VIEW my_view
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(created_date)
TTL created_date + INTERVAL 30 DAY
AS SELECT toStartOfDay(created_date) as date, count() as count
FROM my_table
GROUP BY date;

17、ClickHouse 的高可用集群架构是如何设计的？如何实现高可用？

a、Replica：Replica 是数据的副本，通过配置多个 Replica，可以保证即使某一节点实效，数据依然能够从其他节点读取，从而实现高可用；

b、Shard：Shard 是将数据分片存储在不同的服务器上，提高整体的存储性能和查询性能，每个 Shard 可以配置多个 Replica，从而实现高可用；

c、Zookeeper：Zookeeper 是一种分布式调度服务，用于管理 clickhouse 集群中的各种元数据，包括节点状态、Replica 和 Shard 的配置信息等，当某个节点异常时，zookeeper 能够检测到并将操作重定向到其他可用节点；

d、数据复制与分片：clickhouse 使用表引擎和 ReplicatedMergeTree 来实现数据的复制和分片，通过这种引擎，可以很方便地将数据按需复制到多个 Replica 上，同时进行数据合并和清理等操作；

e、数据同步：clickhouse 的 replicatedmergetree 引擎支持异步复制，虽然在性能上较优，但可能会存在数据延迟，然而在多数情况下，这种延迟是可接受的，因为查询的大部分时刻都是从本地 replica 读取的；

f、负载均衡：对查询请求进行负载均衡，负载均衡器如 nginx 会将请求分发到健康的节点上，从而避免某些节点的过载，同时减少请求响应时间；

g、故障检测和自动恢复：利用 zookeeper，快速检测到节点失效，之后系统可以自动触发数据恢复计划，将失效节点的数据从其他健康节点中复制回来；

18、在 ClickHouse 中，如何优化 SQL 查询的执行计划？

a、使用适合数据特性的引擎，如 mergetree 系列，合理配置引擎参数，如分区键、主键等；比如 max_threads 参数可以限制查询时的并发线程数；max_memory_usage 参数可以限制查询所使用的内存；

b、利用分区和分布式表来提高查询效率，合理设计避免数据倾斜；

c、使用适当的索引以减少读入的数据量；

d、避免使用低效的查询模式，如不必要的全表扫描，可考虑使用预计算聚合表或物化视图；

e、定期进行 OPTIMIZE 操作，合并小数据文件，减少查询开销；

f、根据查询需求来设计表结构，例如宽表还是窄表，在字段类型和列顺序上做优化；

g、确保系统资源如内存、cpu、磁盘io 的合理配置，防止资源瓶颈；

19、ClickHouse 是如何支持实时数据分析的？如何通过流式数据导入实现？

a、clickhouse 采用列式存储，使得数据读取和标签筛选更加高效，使用了多种压缩算法，有效减少了存储空间的占用，同时提高了 io 性能；

b、clickhouse 能够进行大规模的并行查询，主要得益于其对 cpu 多线程的充分利用和自动索引功能，这使得即使在复杂查询下，响应时间也能得到显著优化；

c、clickhouse 支持物化视图，允许预计算和存储查询结果，当有新数据插入时，自动更新视图；

d、clickhouse 支持将流式数据通过 kafka 引入、也支持 http 接口，允许 http post 写入

20、在 ClickHouse 中，如何通过 final 操作消除重复数据？

在 clickhouse 中，可以通过在 select 查询中使用 final 修饰符来消除重复数据，确切地说，当执行查询时，如果表中存在物化视图或者聚合物化视图中的数据部分有重复数据，final 修饰符将会强制 clickhouse 合并未合并的数据片段 parts 并消除其中的重复数据，具体的使用方法：

SELECT   
    column1,  
    column2,  
    ...  
FROM  
    table_name  
FINAL  
WHERE  
    condition;

clickhouse 具体来说是通过插入新的数据部分 parts 并定期尽心合并操作 merge 来确保数据的一致性和高效查询，由于这些合并操作可能不是即时进行的，因此在某些时刻，表中可能会存在重复数据；

使用 final 强制合并操作会在查询过程中占用额外的计算资源，特别是数据量较大的情况下，因为 clickhouse 要读取并处理所有的 parts，这也是 final 操作不被推荐频繁或者在大数据表上使用的原因；

替代方案：除了使用 final 来移除重复数据，还可以在数据插入的时候通过唯一约束、主键等机制来减少重复数据的产生，包括使用 clickhouse 的 replacing 引擎，这个引擎会在数据合并过程中自动去重；

可以使用系统表 system.merges 查看当前的合并操作状态，来调试和优化合并状态：

SELECT * FROM system.merges;
Back to blog