ClickHouse教程 — 第二章 ClickHouse快速入门
1 常见面试题
1.1 为什么使用ClickHouse，有没有对比过其他产品？
参考1：为什么ClickHouse分析数据库这么强？（原理剖析+应用实践）
参考2：ClickHouse与其他数据库的对比
参考3：到底为什么需要 Clickhouse?
Clickhouse是一种关系型数据库，但与传统的关系型数据库（如MySQL和Oracle）不同。最大的区别在于传统的关系型数据库是行式存储，而Clickhouse是列式存储，所以它也是一个开源列式存储的OLAP（在线分析处理过程）数据库。这种列式存储方式具有天然的优势，即用于统计分析和聚类分析。
Clickhouse适用于写少但查询海量数据的场景。
在性能方面，Clickhouse在相同的服务器配置与数据量下，平均响应速度可以比传统数据库更快。因为它只需要读取要计算的列数据，而非行式的整行数据读取，降低了IO成本。此外，Clickhouse拥有优秀的数据压缩能力。它能够有效地压缩数据，减少存储空间的占用，提高查询效率。
Clickhouse在相同的服务器配置与数据量下，与其他数据库相比，平均响应速度：
Vertica的2.63倍(Vertica是一款收费的列式存储数据库)InfiniDB的17倍(可伸缩的分析数据库引擎，基于Mysql搭建)MonetDB的27倍(开源的列式数据库)Hive的126倍MySQL的429倍Greenplum的10倍Spark的1倍
Clickhouse的缺点：
- 不支持事务（这其实也是大部分
OLAP数据库的缺点）。 - 稀疏索引使得它不适合通过主键进行单行的查询。所以它不适用于需要快速查询单行数据的应用场景。
- 缺少高频率、低延迟的修改或删除已存在数据的能力。它仅能用于批量删除或修改数据。
Clickhouse并不能取代关系型数据，也不是为了处理事务性数据而开发的，Clickhouse更多的是作为OLTP（在线事务处理过程）数据库补充，方便用来进行数据分析。如果需要对数据进行更新和删除，或者需要进行多表关联，那么通常不推荐使用Clickhouse。
1.2 为什么MySQL不使用列式存储？
- 分页友好，且可以将数据都放到叶子节点，便于范围查询。
MySQL数据更新方便，这对列式存储是硬伤，所以很多OLAP数据库不支持更新，或者用复杂的方式支持更新。- 事务型数据库的主要性能瓶颈是
I/O，更新一行数据，列式存储需要进行多次I/O，但是行式少数几次就够了。
1.3 clickhouse查询优化可以从哪几方面考虑去做？
- 表设计优化：
- 数据布局：ClickHouse使用MergeTree引擎，考虑到数据的物理布局对查询性能的影响很大。合理设计表的主键、索引和分区，以最大程度地减少查询时需要扫描的数据量。
- 数据类型选择：使用合适的数据类型来减小存储空间，例如使用FixedString而不是String，使用Int32而不是Int64，等等。
- 索引的使用：
- ClickHouse支持索引，但在大多数情况下，索引的维护成本可能比查询性能提升的效果更显著。仅在特定场景下考虑使用索引，例如在OLAP场景中对少量数据进行快速查找。
- 分区和合并：
- 合理使用分区可以加速数据的查询，特别是在只关心某个时间范围的查询中。点击房子可以通过按时间分区来进行数据分割。
- 合并和优化MergeTree引擎的操作，定期进行优化，以删除过期的数据、合并分区等。
- 使用样本数据：
- 对于大数据集，可以考虑使用样本数据进行查询优化。ClickHouse提供了SAMPLE关键字，可以在查询中使用部分数据进行测试和优化。
- 使用合适的引擎：
- ClickHouse提供了多个引擎，每个引擎都有其适用的场景。了解并选择适合你的使用场景的引擎，例如MergeTree适用于时间序列数据，而Distributed适用于分布式查询。
- 配置调优：
- 调整ClickHouse的配置文件，根据硬件资源和查询负载进行调优。例如，调整max_threads、max_memory_usage等参数，以充分利用系统资源。
- 并行查询和异步执行：
- ClickHouse支持并行查询和异步执行。合理设置并行度，通过异步执行降低查询的等待时间。
- 使用合适的查询语法：
- 了解ClickHouse查询语法的最佳实践，使用合适的技巧和语法结构。避免不必要的子查询和复杂的查询结构。
- 系统监控和性能分析：
- 使用ClickHouse提供的监控工具和性能分析工具，了解系统的实际运行状况，找到潜在的性能瓶颈并进行优化。
- 使用缓存：
- ClickHouse提供了缓存功能，可以通过启用缓存来加速特定查询的执行。合理配置和使用缓存，避免对所有查询都启用缓存，因为这可能导致缓存失效的问题。
以上是一些优化ClickHouse查询性能的常见方法，实际优化过程中需要结合具体业务场景和数据特点进行调整。
1.4 clickhouse里面有三个key，一个是primary key，还有sort key，还有partition key，这三个key分别是什么？
- Primary Key（主键）：
- 主键用于唯一标识表中的每一行。ClickHouse的表可以有一个或多个列组成的主键。当你指定一个主键时，ClickHouse将确保表中的每一行都具有唯一的主键值。
- 主键的存在对于合并和查询性能很重要，因为ClickHouse利用主键信息来执行合并操作和快速查找。
- Sort Key（排序键）：
- 排序键定义了表中数据的物理排序顺序。ClickHouse使用MergeTree引擎，该引擎会根据排序键对数据进行排序存储。这种排序有助于提高范围查询的性能。
- 如果没有指定排序键，ClickHouse将按照主键的顺序对数据进行排序。但是，如果你的查询模式更偏向范围查询而非单行查询，那么定义一个合适的排序键可能会显著提高性能。
- Partition Key（分区键）：
- 分区键用于将表中的数据划分为多个逻辑分区。每个分区都可以单独存储在不同的物理位置上，这有助于提高查询性能，尤其是在对特定分区进行查询时。
- ClickHouse支持按照一个或多个列进行分区。分区键的选择通常基于查询模式和数据分布的特点。
综合来说，这三个键的作用如下：
- 主键（Primary Key）：用于唯一标识每一行。
- 排序键（Sort Key）：用于定义数据的物理排序，提高范围查询性能。
- 分区键（Partition Key）：用于将数据分成逻辑分区，提高查询性能和管理数据。
在设计表时，合理选择和配置这些键是优化ClickHouse查询性能的关键。根据数据分布、查询需求和硬件资源等因素，选择适当的主键、排序键和分区键来优化你的表结构。
1.5 clickhouse里面的primary key一般会怎么去选？
选择ClickHouse表的Primary Key（主键）时，需要考虑查询模式、数据分布以及性能优化的目标。以下是一些建议和常见的选择标准：
- 唯一性：主键应确保表中的每一行都具有唯一的标识。通常，你会选择一个或多个列的组合，以确保唯一性。
- 查询模式：考虑你的查询模式是单行查询还是范围查询。如果查询模式更偏向范围查询，那么选择适当的排序键可能比单一的主键更重要。
- 数据分布：查看数据的分布情况，确保主键的选择能够均匀分布数据。不均匀的分布可能导致某些节点的负载较高。
- 排序键：如果查询中经常涉及范围查询，考虑选择与排序键相匹配的列作为主键。这有助于提高查询性能。
- 数据类型：主键列的数据类型选择也很重要。较小的数据类型将占用更少的存储空间，并可能提高查询性能。
- 分布式表的全局唯一性：如果你的表是分布式的，确保在整个集群中主键的组合是全局唯一的。这点对于分布式表的正确性非常重要。
下面是一个简单的例子，假设有一个存储用户行为日志的表，其中user_id和event_time是两个重要的字段。在这个场景下，可能的主键选择是使用user_id作为主键，或者使用user_id和event_time的组合作为主键。选择哪一个取决于实际的查询需求，如果你更关心按用户ID进行单行查询，那么选择user_id作为主键可能更合适；如果你更关心按时间范围进行查询，那么选择user_id和event_time作为组合主键可能更有意义。
最终的选择需要根据实际业务场景和查询模式来权衡，进行基准测试以确保选择的主键能够在真实场景中提供良好的性能。
1.6 clickhouse如何加索引？
在ClickHouse中，创建索引通常是在定义表的时候完成的。ClickHouse提供了几种类型的索引，主要有主键索引、排序索引以及普通索引。下面是一些关于如何在ClickHouse中添加索引的基本信息：
- 主键索引：
- ClickHouse表中的主键是一种特殊的索引，它确保表中的每一行都具有唯一的主键值。
- 在创建表时，可以使用ENGINE和PRIMARY KEY语句定义主键索引。
CREATE TABLE example_table ( column1 UInt32, column2 String ) ENGINE = MergeTree ORDER BY column1 PRIMARY KEY column1;
上述例子中，column1被定义为主键。
- 排序索引：
- ClickHouse的MergeTree引擎默认使用主键作为排序索引。如果没有显式指定主键，表将按照插入的顺序进行排序。
- 如果需要显式定义排序索引，可以使用ORDER BY子句。
CREATE TABLE example_table ( column1 UInt32, column2 String ) ENGINE = MergeTree ORDER BY column1;
上述例子中，column1被定义为排序索引。
- 普通索引：
- ClickHouse还支持普通索引，这是一种不要求唯一性的索引。
- 在创建表时，可以使用INDEX语句定义普通索引。
CREATE TABLE example_table ( column1 UInt32, column2 String ) ENGINE = MergeTree ORDER BY column1 INDEX index_name (column2);
上述例子中，column2被定义为普通索引。
需要注意的是，索引的创建可能会对写入性能产生一定的影响，因此在选择使用索引时需要权衡查询性能和写入性能。此外，对于大规模数据的表，索引的使用需要谨慎，可能需要进行性能测试以确保索引的确能够带来性能的提升。
1.7 clickhouse如何对索引查询优化？
ClickHouse在设计上已经默认对OLAP（联机分析处理）场景进行了优化，因此对于大规模数据的索引查询已经有很好的性能。然而，以下是一些建议，可以进一步优化ClickHouse中的索引查询：
- 合理使用主键：主键是唯一性索引，ClickHouse使用主键来快速查找特定行。因此，合理选择主键是非常关键的。主键应该是你经常用于查询的字段，以确保单行查询的高效性。
- 合理使用排序键：排序键对于范围查询的性能提升很大。在创建表时，使用ORDER BY子句来定义排序键，以确保按照特定顺序存储数据。这对于时间序列数据等场景特别有效。
CREATE TABLE example_table ( column1 UInt32, column2 String ) ENGINE = MergeTree ORDER BY column1; - 适当的分区键：如果数据表非常大，合理使用分区键可以显著提高查询性能。分区键可以将数据分散存储在不同的物理位置上，以减少扫描的数据量。
CREATE TABLE example_table ( column1 UInt32, column2 String ) ENGINE = MergeTree PARTITION BY toYYYYMM(column1); - 合理选择索引：ClickHouse支持主键索引、排序索引和普通索引。在创建表时，选择适当的索引类型。主键用于唯一性标识行，排序索引用于范围查询，普通索引用于非唯一性的索引需求。
CREATE TABLE example_table ( column1 UInt32, column2 String ) ENGINE = MergeTree ORDER BY column1 PRIMARY KEY column1 INDEX index_name (column2); - 使用样本数据进行测试：在实际使用中，使用样本数据进行测试，观察查询性能，特别是在对表结构进行更改之前。ClickHouse提供了SAMPLE关键字，用于在查询中使用部分数据进行测试和优化。
SELECT * FROM example_table SAMPLE 1; - 定期维护和优化表：对于MergeTree引擎，定期执行优化操作是重要的。这包括删除过期数据、合并分区以及执行优化操作。ClickHouse提供了OPTIMIZE TABLE命令用于执行这些操作。
OPTIMIZE TABLE example_table;
这些是一些建议，帮助在ClickHouse中进行索引查询优化。实际优化过程中，需要结合具体的业务场景、数据分布和查询需求来调整表结构和索引的设计。
2 ClickHouse简介
参考1：大数据技术之 ClickHouse
参考2：ClickHouse官方介绍
参考3：ClickHouse介绍
参考4：篇一|ClickHouse快速入门
推荐阅读参考1、2，优先阅读参考1。
ClickHouse是俄罗斯的Yandex于2016年开源的用于联机分析（OLAP :Online Analytical Processing的列式数据库管理系统（DBMS：Database Management System），能够使用SQL查询实时生成分析数据报告，主要用于Web流量分析。ClickHouse的全称是Click Stream，Data WareHouse。
ClickHouse非常适用于商业智能领域，除此之外，它也能够被广泛应用于广告流量、Web、App流量、电信、金融、电子商务、信息安全、网络游戏、物联网等众多其他领域。
3 ClickHouse特点
-
列式存储
行式存储的特点：
想查找某个人所有的属性时，可以通过一次磁盘查找加顺序读取就可以；但是当想查所有人的年龄时，需要不停的查找，或者全表扫描才行，遍历的很多数据都是不需要的。列式存储的特点：
- 对于列的聚合、计数、求和等统计操作优于行式存储。
- 由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重。
- 数据压缩比更好，一方面节省了磁盘空间，另一方面对于cache也有了更大的发挥空间。
- 它在分析查询中非常高效，特别是在需要聚合大量数据时。
- 列式存储不支持事务。
-
灵活的查询语言
几乎覆盖了标准SQL的大部分语法，包括DDL和DML，以及配套的各种函数；用户管理及权限管理、数据的备份与恢复。 -
高性能
ClickHouse被设计用于处理大规模数据集，可以在秒内处理数十亿行的数据。它支持并行查询执行和高度优化的查询引擎，以确保快速的查询性能。 -
可扩展性
ClickHouse易于水平扩展。可以通过添加更多的硬件资源或节点来增加其容量和性能，以满足不断增长的数据需求。 -
多种数据格式支持
ClickHouse支持多种数据格式，包括CSV、JSON、Parquet等，使得它能够与各种数据源集成。 -
高吞吐写入能力
ClickHouse采用类LSM Tree的结构，数据写入后定期在后台Compaction。通过类LSM tree的结构，ClickHouse在数据导入时全部是顺序append写，写入后数据段不可更改，在后台compaction时也是多个段merge sort后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力。
3.1 clickhouse的列式存储
- 列式存储原理：在列式存储中，表中的每个列都被单独存储在磁盘上，而不是将整个行作为一个单元存储。这意味着数据表中的每一列都以连续的方式存储在磁盘上，而不是以行的方式存储。这种存储方式使得在分析性工作负载下非常高效，因为查询通常只需要访问一部分列，而不是整个行。
- 列压缩：
ClickHouse使用高度优化的压缩算法来存储列数据，以减少存储空间的需求。这些压缩算法可以根据数据的特性自动选择，从而进一步减少磁盘占用和提高查询性能。 - 数据访问效率：由于数据存储在列中，
ClickHouse可以跳过不必要的列，仅访问查询所需的列，从而降低了IO开销和数据传输的成本。这使得ClickHouse在分析查询中非常快速。 - 高并发性能：
ClickHouse的列式存储还使得多个查询可以同时访问相同的表，而不会产生锁定或冲突，从而实现了高并发性能。 - 节省空间：由于列式存储使用了压缩技术，并且数据通常是高度重复的，因此
ClickHouse通常比传统的行式存储方式节省更多的存储空间。 - 数据列的物理布局：
ClickHouse在物理磁盘上存储数据列时，通常会使用存储引擎中的布局优化，例如MergeTree引擎，以提高数据的可用性和查询性能。
总结：
ClickHouse的列式存储是为了最大程度地优化分析性查询而设计的。它通过将数据按列组织、压缩和高效访问，提供了卓越的性能，使其成为处理大规模数据分析任务的理想工具。
4 clickhouse缺点
- 实时性限制：
ClickHouse主要设计用于批处理分析，因此在处理实时数据和需要低延迟响应的工作负载方面不如一些专门为此目的设计的数据库系统。 - 不适用于事务处理：
ClickHouse不支持复杂的事务处理。它专注于分析查询，而不是用于支持在线事务处理（OLTP）工作负载的数据库。 - 写入性能：尽管
ClickHouse支持实时数据插入，但在大量写入数据时，写入性能可能会变得较低。这可能会导致在高写入负载下的性能问题。 - 较小的生态系统：相对于一些主流的关系型数据库或
NoSQL数据库，ClickHouse的生态系统相对较小，可能需要额外的开发工作来实现特定的集成或功能。 - 资源需求：处理大规模数据集的
ClickHouse集群可能需要大量的硬件资源，包括内存和磁盘空间。这可能会导致成本较高，尤其是在云环境中。
尽管ClickHouse存在这些缺点，但它在大规模分析性工作负载下的性能和效率通常可以弥补这些限制。选择使用ClickHouse还取决于具体的用例和需求，以确保它是最合适的数据库系统。
5 clickhouse为什么快
- 列式存储：
ClickHouse使用列式存储，将数据按列而不是按行存储在磁盘上。这意味着查询通常只需要访问所需的列，而不必加载整个行，从而减少了磁盘I/O和内存消耗。 - 压缩算法：
ClickHouse使用高度优化的压缩算法来存储数据，减少了磁盘占用和数据传输的成本。这不仅降低了存储成本，还提高了查询性能，因为更少的数据需要从磁盘读取到内存中。 - 并行查询：
ClickHouse支持并行查询执行，可以同时处理多个查询请求。这意味着多个查询可以在不互相干扰的情况下同时运行，提高了系统的吞吐量和响应速度。 - 数据分区：
ClickHouse将数据分成多个分区，每个分区可以独立压缩和查询。这有助于减小查询的数据集大小，提高了查询性能。 - 索引结构：
ClickHouse使用不同类型的索引来加速查询，包括Bloom过滤器、MergeTree索引等。这些索引可以加速特定类型的查询，使其更快速。 - 合并树：
ClickHouse使用合并树技术来定期合并和优化数据分区，以减少磁盘碎片和提高查询性能。 - 数据预热：
ClickHouse可以预热数据，将数据加载到内存中，以加速查询的响应时间。 - 缓存：
ClickHouse支持查询结果缓存，可以将查询结果缓存在内存中，以提高重复查询的性能。 - 异步处理：
ClickHouse采用异步方式处理一些操作，如数据插入和合并，以减少对查询性能的影响。 - 分布式架构：
ClickHouse可以轻松扩展到多个节点，以处理大规模数据集。这种分布式架构提供了更多的计算和存储资源，进一步提高了性能和容量。
总结：
ClickHouse的设计和优化旨在最大程度地提高查询性能和效率，特别适用于大规模分析性工作负载。它的列式存储、压缩算法、并行处理和其他性能优化策略使其成为处理大量数据的理想工具。
6 ClickHouse引擎
ClickHouse引擎分为数据库引擎和数据表引擎 。
参考1：ClickHouse表引擎之Log系列表引擎
引擎这块还需要深入了解一下，有些引擎不支持删改操作。
※5.1 ClickHouse数据引擎总结
- 数据库引擎默认是
Ordinary，在这种数据库下面的表可以是任意类型引擎。 - 生产环境中常用的表引擎是
MergeTree系列，也是官方主推的引擎。MergeTree是基础引擎，有主键索引、数据分区、数据副本、数据采样、删除和修改等功能，ReplacingMergeTree有了去重功能，SummingMergeTree有了汇总求和功能，AggregatingMergeTree有聚合功能，CollapsingMergeTree有折叠删除功能，VersionedCollapsingMergeTree有版本折叠功能，GraphiteMergeTree有压缩汇总功能。在这些的基础上还可以叠加Replicated和Distributed。
Integration系列用于集成外部的数据源，常用的有HADOOP，MySQL。
6.2 数据库引擎
数据库引擎，默认情况下，ClickHouse使用Atomic数据库引擎。它提供了可配置的table engines和SQL dialect。
- MySQL
MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。
MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。 - MaterializeMySQL
MySQL数据同步；将MySQL数据全量或增量方式同步到clickhouse中，解决MySQL服务并发访问压力过大的问题。 - Lazy
在最后一次访问之后，只在RAM中保存expiration_time_in_seconds秒。只能用于Log表。
它是为存储许多小的Log表而优化的，对于这些表，访问之间有很长的时间间隔。也就是访问不频繁，压缩率高。 - Atomic
默认的数据库引擎。它提供了可配置的table engines和SQL dialect。 - PostgreSQL
- MaterializedPostgreSQL
- Replicated
- SQLite
6.3 数据表引擎
表引擎是 ClickHouse 的一大特色。可以说， 表引擎决定了如何存储表的数据。包括：
- 数据的存储方式和位置，写到哪里以及从哪里读取数据。
- 支持哪些查询以及如何支持。
- 并发数据访问。
- 索引的使用（如果存在）。
- 是否可以执行多线性请求。
- 数据复制参数。
6.3.1 表引擎类型
参考1：表引擎
表引擎大致可分四个种类。
- 合并树家族
适用于高负载任务的最通用和功能最强大的表引擎。这些引擎的共同特点是可以快速插入数据并进行后续的后台数据处理。MergeTree系列引擎支持数据复制（使用Replicated的引擎版本），分区和一些其他引擎不支持的其他功能。
该类型的引擎：
MergeTree最常用ReplacingMergeTree最常用SummingMergeTreeAggregatingMergeTreeCollapsingMergeTreeVersionedCollapsingMergeTreeGraphiteMergeTree
- 日志引擎系列
具有最小功能的轻量级引擎。当您需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。
该类型的引擎：
TinyLogStripeLogLog
- 集成的表引擎
用于与其他的数据存储与处理系统集成的引擎。
该类型的引擎：
Kafka比较常用，采集回来的数据先存Kafka然后同步到clickhouseMySQL比较常用。ODBCJDBCHDFS
- 特别
该类型的引擎：
DistributedMaterializedViewDictionaryMergeFileNullSetJoinURLViewMemoryBuffer
6.4 数据类型
7 ClickHouse使用语法
ClickHouse的语法与MySQL语法比较接近。在此就不做过多介绍了，可参考：SQL参考
8 代码操作ClickHouse
8.1 go.mod
注意clickhouse-go/v2 版本的问题，使用 github.com/ClickHouse/clickhouse-go/v2 v2.4.1 会报：
WARNING: version 21.3.19 of ClickHouse is not supported by this client
ClickHouse (clickhouse-standalone) server version 21.3.19 revision 54447 (timezone Asia/Shanghai)
降级改为 github.com/ClickHouse/clickhouse-go/v2 v2.2.0 即可。
module clickhouse_demo
go 1.19
require github.com/ClickHouse/clickhouse-go/v2 v2.2.0
8.2 测试是否连通clickhouse
代码：
package main
import (
"fmt"
"github.com/ClickHouse/clickhouse-go/v2"
)
func main() {
conn, _ := clickhouse.Open(&clickhouse.Options{Addr: []string{"192.168.13.10:9000"}})
v, _ := conn.ServerVersion()
fmt.Println(v.String())
}
结果：
API server listening at: 127.0.0.1:51175
ClickHouse (clickhouse-standalone) server version 22.2.2 revision 54455 (timezone Asia/Shanghai)
连通正常。
8.3 注意
github.com/ClickHouse/clickhouse-go/v2里暂时没看到事务相关的特性，所以无法做到对主子表同时保存成功或失败的功能，可以使用gorm。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
