对比MySQL和ES后，毫不犹豫把百亿数据存到ClickHouse了

一、背景

魔笛活动平台要记录每个活动的用户行为数据，帮助客服、运营、产品、研发等快速处理客诉、解决线上问题并进行相关数据分析和报警。可以预见到需要存储和分析海量数据，预估至少几十亿甚至上百亿的数据量，所以需要选择一款能存储海量数据的数据库。由于是通过接收MQ存储或者API方式存储，所以对实时写入性能也有一定要求。同时可能后续还需要一些实时数据分析等。这里总结一下需求点：

• 可以存储海量数据；
• 写入性能好；
• 可以进行实时计算分析；
• 查询性能最好不要太差。

二、技术选型

1.MySQL单表

MySQL数据库我们是算用得最多了。但众所周知，MySQL是单机的。MySQL能存储多少数据，取决于那台服务器的硬盘大小。很多时候MySQL是没法存储那么多数据的，根据行记录头信息、可变字段列表、事务ID、指针字段、字段内容信息等不同存储量极限也会不同，数据存储量范围为一百多万条到将近5亿条数据，业界公认MySQL单表容量在1KW量级是最佳状态，这个感兴趣的可以自己去看看，这里就不再赘述，肯定不能存储几十亿条数据，所以MySQL单表不适合。

2.MySQL分库分表

分库分表确实可以存储更多的数据量，分布式事务和异步复制等技术也进一步提高了写入性能和数据的可靠性，将数据分散到多个物理服务器或表中，减少单个服务器或表的负担，查询性能也还可以，支持在线事务处理。但是有以下不足：

• MySQL存储数据耗费的资源更多，当数据量达到几十亿时候，如果每列字段都加上索引，索引占用空间的比例甚至超过数据本身的存储空间；
• 在线分析处理能力一般。因为在线分析处理（OLAP）则需要进行复杂的查询和分析，通常需要使用聚合函数等操作，这些操作会涉及到大量的数据读取和计算，因此需要大量的计算资源和内存空间，在MySQL分库分表中，数据被分散存储在多个节点上，查询数据需要通过网络进行数据的传输和计算，这会导致查询速度的降低和延迟的增加；
• 由于数据的分散存储，需要进行多个节点的数据聚合和计算，这也会增加系统的负担和延迟；
• 同时，这也导致当数据量达到几十亿时候，查询性能也会受到很大影响。

所以MySQL分库分表不太适合。

3.Elasticsearch

Elasticsearch是一个分布式的搜索引擎，并采用数据分片和高可用性等技术，可以存储海量数据。采用倒排索引的方式存储数据，可以快速检索数据。也可以进行实时数据分析。但是有以下不足：

• 由于分词等特性，写吞吐量上有着明显的瓶颈；
• 分词会增加写入操作的延迟和负载；
• 热点问题比较难解决。如果资源冗余不足，就会导致稳定性下降，数据写入会发生延迟；
• 当数据量增加时，Elasticsearch 的查询速度会变慢，因为它必须扫描整个索引才能找到符合查询条件的数据；
• 压缩率不高，存储成本也比较高。

4.Hbase

HBase是基于HDFS分布式文件系统去构建的，集群的管理基于 ZooKeeper 实现，设计是为了海量数据的快速存储和随机访问，列式存储也减少数据的读取量。列族设计、MemStore 缓存、批量写入、数据压缩等使其写入性能也非常优秀。但是有以下不足：

• 它并不是一个实时计算和数据分析的框架；
• 数据存储方式问题：HBase的数据存储方式是以列族和列的方式存储数据，这种方式适合存储结构化数据，但是在存储非结构化数据时效率较低。在实时数据分析场景下，数据可能是半结构化或者非结构化的，这种数据存储在HBase中需要进行额外的处理，导致效率下降；
• RowKey的设计对查询也有一定限制，所以Hbase不太适合；
• 数据读取效率问题：HBase的数据读取方式是通过扫描整个表或者通过索引查找特定行来实现的，这种方式在处理大量数据时效率较低，尤其是在实时数据分析场景下，需要快速响应用户的查询请求，但是HBase的读取速度无法满足这个需求。

5.ClickHouse

ClickHouse的特点是高速、可扩展、高效、低成本，它可以适应各种数据存储和处理需求，包括在线分析处理（OLAP）、实时数据分析、数据仓库、日志分析等场景。它支持SQL语言和多种数据格式，包括CSV（逗号分隔值）、JSON、XML等，并且可以通过JDBC、ODBC和HTTP等协议进行访问。

ClickHouse的性能非常出色，可以在秒级别内处理数十亿条数据，而且它支持数据压缩和分区等功能，可以大大降低存储和查询成本。基于以上特性，选择ClickHouse来存储、查询和分析数据。

三、ClickHouse详细介绍

1.来源

ClickHouse是俄罗斯的搜索巨头Yandex公司开发的面向列式存储的关系型数据库（DBMS），于2016年开源,使用C++编写的，主要用于在线分析处理查询（OLAP）,能够使用SQL查询实时生成分析数据报告。ClickHouse 是过去两年中 OLAP 领域中最热门的。

ClickHouse的初始设计目的是为了服务于自己公司的一款名叫Yandex.Metrica的产品。Metrica是一款Web流量分析工具，基于前方探针采集行为数据，然后进行一系列的数据分析，类似数据仓库的OLAP分析。而在采集数据的过程中，一次页面click(点击)，会产生一个event（事件）。所以，整个系统的逻辑就是基于页面的点击事件流，面向数据仓库进行OLAP分析。所以ClickHouse的全称是Click Stream（点击流），Data WareHouse（数据仓库），简称ClickHouse。

2.架构

ClickHouse则采用Multi-Master多主架构，集群中每个角色对等，客户端访问任意一个节点都能得到相同的效果。

Single-Master架构对于查询场景，部分查询的最后阶段会在Master节点上进行最终的数据处理，需要消耗一定的CPU以及内存资源。对于写入场景，大量的实时插入、更新、删除的需要高性能保证。同时并发连接数很大的情况Single-Master结构也较难处理。

Multi-Master通过水平扩展Master节点突破了原架构单Master的限制，配合Segment节点（计算节点）的弹性，系统整体能力尤其是连接数及读写性能得到进一步提升，更好地满足实时数仓及HTAP等业务场景的需求。

集群部署架构：

在每个节点创建一个数据表，作为一个数据分片，使用ReplicatedMergeTree表引擎实现数据副本，而分布表作为数据写入和查询的入口。

3.特点

1）列式存储

首先我们先看行存储：

按行存储的时候，一行记录的属性值存储在临近的空间，然后接着是下一条记录的属性值。好处是想查某个人所有的属性时，可以通过一次磁盘查找加顺序读取就可以。

但是当想查所有人的年龄时，需要不停的查找，或者全表扫描才行，遍历的很多数据都是不需要的，大量磁盘转动寻址的操作使得读取效率大大降低。

数据在磁盘上是以行的形式存储在磁盘上，同一行的数据紧挨着存放在一起。由于 dept 的值是离散地存储在磁盘中，在查询过程中，需要磁盘转动多次，才能完成数据的定位和返回结果。

列存储：

对于 OLAP 场景，一个典型的查询需要遍历整个表，进行分组、排序、聚合等操作，这样一来行式存储中把一整行记录存放在一起的优势就不复存在了。而且，分析型 SQL 常常不会用到所有的列，而仅仅对其中某些需要的的列做运算，那一行中无关的列也不得不参与扫描。

然而在列式存储中，而按列存储的时候，单个属性所有的值存储在临近的的空间，即一列的所有数据连续存储的，每个属性有不同的空间。由于同一列的数据被紧挨着存放在了一起，那么基于需求字段查询和返回结果时，就不许对每一行数据进行扫描，按照列找到需要的数据，磁盘的转动次数少，性能也会提高。

列的组成都是灵活的，行与行之间的列不需要相同。三行数据实际在CK中数一行数据。

列式存储优点：

• 对于列的聚合，计数，求和等统计操作要优于行式存储。
• 由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重。
• 由于数据压缩比更好，一方面节省了磁盘空间，另一方面对于cache也有了更大的发挥空间。

2）完备的DBMS功能

ClickHouse拥有完备的管理功能，所以它是真正的列式数据库管理系统，而不仅是一个数据库。作为一个DBMS，它具备了一些基本功能。

• DDL（数据定义语言）：可以动态地创建、修改或删除数据库、表和视图，而无须重启服务。
• DML（数据操作语言）：可以动态查询、插入、修改或删除数据。
• 权限控制：可以按照用户粒度设置数据库或者表的操作权限，保障数据的安全性。
• 数据备份与恢复：提供了数据备份导出与导入恢复机制，满足生产环境的要求。
• 分布式管理：提供集群模式，能够自动管理多个数据库节点。

3）数据压缩

ClickHouse数据默认使用LZ4算法压缩，它使用374台服务器的集群，存储了20.3万亿行的数据。在去除重复与副本数据的情况下，压缩后的数据达到了2PB，未压缩前（TSV格式）大概有17PB，数据总体的压缩比可以达到8:1。

ClickHouse采用列式存储，列式存储相对于行式存储另一个优势就是对数据压缩的友好性。例如：有两个字符串“ABCDE”，“BCD”，现在对它们进行压缩：

压缩前：ABCDE_BCD 压缩后：ABCDE_(5,3)

通过上面例子可以看到，压缩的本质是按照一定步长对数据进行匹配扫描，当发现重复部分的时候就进行编码转换。例如：(5,3)代表从下划线往前数5个字节，会匹配上3个字节长度的重复项，即:“BCD”。

当然，真实的压缩算法比以上举例更复杂，但压缩的本质就是如此，数据中重复性项越多，则压缩率越高，压缩率越高，则数据体量越小，而数据体量越小，则数据在网络中的传输越快，对网络带宽和磁盘IO的压力也就越小。

列式存储中同一个列的数据由于它们拥有相同的数据类型和现实语义，可能具备重复项的可能性更高，更利于数据的压缩。所以ClickHouse在数据压缩上比例很大。

压缩必然带来压缩和解压缩的CPU消耗，这是一个利用CPU时间换I/O时间的手段。事务数据库由于大部分情况下是针对行的操作，因此如果对每一行都进行一次压缩解压缩，带来的时间消耗是远大于磁盘I/O时间的。这就是事务数据库没有使用压缩技术的原因。

而ClickHouse则不同，ClickHouse的最小处理单元是块，块一般由8192行数据组成，ClickHouse的一次压缩针对的是8192行数据，这就极大降低CPU的压缩和解压缩时间。同时，ClickHouse是列存数据库，同一列的数据相对更有规律，因此能够带来比较大的压缩比。因此，块+压缩在ClickHouse中成为一个非常关键的优化手段。

4）向量化执行引擎

向量化执行，可以简单地看作一项消除程序中循环的优化。

我们看下面一个简单的代码：

for (size_t i = 0; i < 100; ++i)

c[i] = a[i] + b[i];

这个代码会环100次，将a和b数组对应下标的数字相加如何赋值给c，那么如何加速这样的计算呢，一个朴素的想法就是写出如下的代码，这是非向量化执行的方式：

c[0] = a[0] + b[0];

c[1] = a[1] + b[1];

向量化执行的方式就是并行执行一次。

为了实现向量化执行，需要利用CPU的SIMD指令。SIMD的全称是Single Instruction Multiple Data，即用单条指令操作多条数据。现代计算机系统概念中，它是通过数据并行以提高性能的一种实现方式（其他的还有指令级并行和线程级并行），它的原理是在CPU寄存器层面实现数据的并行操作。

如果这时候CPU也可以并行的计算我们写的代码，那么理论上我们的处理速度就会是之前代码的100倍，幸运的是SIMD指令就是完成这样的工作的，用SIMD指令去完成这样代码设计和执行就叫做向量化。

从上图中可以看到，CPU、CPU三级缓存、内存、磁盘数据容量与数据读取速度对比，从左向右，距离CPU越远，则数据的访问速度越慢。从寄存器中访问数据的速度，是从内存访问数据速度的300倍，是从磁盘中访问数据速度的3000万倍。所以利用CPU向量化执行的特性，对于程序的性能提升意义非凡。

ClickHouse提供了很多内置函数，在使用这些内置函数时，ClickHouse会自动进行向量化优化。因此尽可能使用提供的内置函数进行计算，而不是自己写SQL语句。下面展示错误的SQL写法以及正确的写法。

SELECT (2/(1.0 + exp(-2 * x))-1) as tanh_x …… // 错误的写法

SELECT tanh(x) as tanh_x …… // 正确的写法，直接使用ClickHouse的内置函数

5）数据分片与分布式查询

数据分片是将数据进行横向切分，这是一种在面对海量数据的场景下，解决存储和查询瓶颈的有效手段，是一种分治思想的体现。ClickHouse支持分片，而分片则依赖集群。每个集群由1到多个分片组成，而每个分片则对应了ClickHouse的1个服务节点。分片的数量上限取决于节点数量（1个分片只能对应1个服务节点）。

ClickHouse 分片可以理解为就是 ClickHouse 一个单机数据库实例（副本节点也算），多个这种单机数据库实例构成一个 ClickHouse 集群。分片是指包含数据不同部分的服务器（要读取所有数据，必须访问所有分片）。ClickHouse 通过分片，将一张表的数据水平分割在不同的节点上，随着业务的发展，当表数据的大小增加到很大时，也能够通过水平扩容， 保证数据的存储。

ClickHouse拥有高度自动化的分片功能。ClickHouse提供了本地表 ( Local Table ) 与分布式表 ( Distributed Table ) 的概念。一张本地表等同于一份数据的分片。而分布式表本身不存储任何数据，它是本地表的访问代理，其作用类似分库中间件。借助分布式表，能够代理访问多个数据分片，从而实现分布式查询。

简单理解，Distributed 表引擎只是你真实数据表（本地表）的代理，在进行数据查询时，它会将查询请求发送到各个分片上，结合索引（如果有），并行进行查询计算，最终将结果进行合并，返回到 Client。

这种设计类似数据库的分库和分表，十分灵活。例如在业务系统上线的初期，数据体量并不高，此时数据表并不需要多个分片。所以使用单个节点的本地表（单个数据分片）即可满足业务需求，待到业务增长、数据量增大的时候，再通过新增数据分片的方式分流数据，并通过分布式表实现分布式查询。

6）多线程

向量化执行是通过数据级并行的方式提升了性能，多线程处理是通过线程级并行的方式实现了性能的提升。相比基于底层硬件实现的向量化执行SIMD，线程级并行通常由更高层次的软件层面控制，目前市面上的服务器都支持多核心多线程处理能力。由于SIMD不适合用于带有较多分支判断的场景，ClickHouse也大量使用了多线程技术以实现提速，以此和向量化执行形成互补。

ClickHouse在数据存取方面，既支持分区(纵向扩展，利用多线程原理 )，也支持分片(横向扩展，利用分布式原理)，可以说是将多线程和分布式的技术应用到了极致。

7）关系模型与标准SQL查询

ClickHouse 和 MySQL 类似，把表级的存储引擎插件化，根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口和其他四大类 20 多种引擎。

相比HBase、Redis、MongoDB这类NoSQL数据库，ClickHouse使用关系模型描述数据并提供了传统数据库的概念(数据库、表、视图和函数等)。ClickHouse完全使用SQL作为查询语言(支持GROUP BY、ORDER BY、JOIN、IN等大部分标准SQL)，ClickHouse提供了标准协议的SQL查询接口，可以与第三方分析可视化系统无缝集成对接。支持mybatis和mybatis-plus，但是mybatis-plus分页支持还不是很友好，但是通过一定方式也可以实现。在SQL解析方面，ClickHouse是大小写敏感，SELECT a 和 SELECT A所代表的语义不同。

8）多主架构

Spark、HBase和Elasticsearch这类分布式系统，都采用了Master-Slave主从架构，由一个管控节点作为Leader统筹全局。而ClickHouse则采用Multi-Master多主架构，集群中的每个节点角色对等，客户端访问任意一个节点都能得到相同的效果。

这种多主的架构有许多优势，例如对等的角色使系统架构变得更加简单，不用再区分主控节点、数据节点和计算节点，集群中的所有节点功能相同。所以它天然规避了单点故障的问题。

4.数据类型

1）数值

Int Ranges

Int8 — [-128 : 127]

Int16 — [-32768 : 32767]

Int32 — [-2147483648 : 2147483647]

Int64 — [-9223372036854775808 : 9223372036854775807]

Int128 — [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]

Int256 — [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]

推荐使用：UInt

Uint Ranges

UInt8 — [0 : 255]

UInt16 — [0 : 65535]

UInt32 — [0 : 4294967295]

UInt64 — [0 : 18446744073709551615]

UInt256 — [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935]

2）浮点数

• 单精度浮点：Float32 4个字节， 有效精度7
• 双精度浮点：Float64 8个字节， 有效精度16

位数超过会发生溢出。

注意：浮点数支持正无穷(inf)、负无穷(-inf)及非数字(nan)的表达式。

3）Decimal

如果需要使用更高精度运算，需要使用Decimal：

Decimal32(S),Decimal64(S),Decimal128(S)

或者Decimal(P,S)

4）String 字符串

可以存储任意长度字符串，包括Null

FixedString 定长字符串

• FixedString 与Char都是定长字符串，对于字符有明确长度的场合建议使用固定长度字符串；
• 定长申明FixedString(N)， X表示字符长度；
• FixedString与Char不同的是 FixedString 使用null填充末尾字符，Char使用空格填充。

5）UUID

使用 generateUUIDv4()生成

6）时间类型

• DateTime 日期时间，精确到秒， 如：2021-03-01 00:00:00
• DateTime64 日期时间，精确到亚秒， 如：2021-03-01 00:00:00:00
• Date 日期， 精确到天；如：2021-03-01

7）数组Array

数组里面可以有不同类型的元素，但是类型必须兼容；

类型：c1 Array(Int8) Comment '数组example';

array(1，2.0 ，3.1)

8）枚举Enum

Enum8：底层实际存储：(String:Int8) Key/Value

Enum16: 底层存储：(String:Int16) Key/Value

• Key和Value需要保证唯一性；
• Key可以为空，但Key和Value不可以同时为空。

字段定义：c1 Enum('ready' = 1,'start' = 2,'success' = 3,'error' = 4) comment '枚举值举例';

eg：INSERT INTO Enum_TB VALUES('ready')

9）CK与其他关系型数据库类型对比

Int8 — TINYINT, BOOL, BOOLEAN, INT1

Int16 — SMALLINT, INT2.

Int32 — INT, INT4, INTEGER.

Int64 — BIGINT.

Float32 — float.

Float64 — double.

String - VARCHAR, BLOB, TEXT

FixedString - Char

DateTime - datetime

5.表引擎 - MergeTree引擎

在这众多的表引擎中，最常用的是合并树（MergeTree）表引擎及其家族系列（*MergeTree），因为只有合并树系列的表引擎才支持主键索引、数据分区、数据副本和数据采样这些特性，同时也只有此系列的表引擎支持ALTER相关操作。

MergeTree 的主要特点为：

• 存储的数据按主键排序：这样可以创建一个小型的稀疏索引来加快数据检索
• 支持数据分区：数据分区可以仅扫描指定分区数据，提高性能
• 支持数据副本
• 支持数据采样

1）MergeTree的创建

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (

name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],

name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],

省略...

) ENGINE = MergeTree()

[PARTITION BY expr]

[ORDER BY expr]

[PRIMARY KEY expr]

[SAMPLE BY expr]

[SETTINGS name=value, 省略...]

• PARTITION BY [选填]：分区键，用于指定表数据以何种标准进行分区。分区键既可以是单个列字段，也可以通过元组的形式使用多个列字段，同时它也支持使用列表达式。如果不声明分区键，则ClickHouse会生成一个名为all的分区。合理使用数据分区，可以有效减少查询时数据文件的扫描范围
• ORDER BY [必填]：排序键，用于指定在一个数据片段内，数据以何种标准排序。默认情况下主键（PRIMARY KEY）与排序键相同。排序键既可以是单个列字段，例如ORDER BY CounterID，也可以通过元组的形式使用多个列字段，例如ORDER BY（CounterID,EventDate）。当使用多个列字段排序时，以ORDER BY（CounterID,EventDate）为例，在单个数据片段内，数据首先会以CounterID排序，相同CounterID的数据再按EventDate排序
• PRIMARY KEY [选填]：主键，顾名思义，声明后会依照主键字段生成一级索引，用于加速表查询。默认情况下，主键与排序键(ORDER BY)相同，所以通常直接使用ORDER BY代为指定主键，无须刻意通过PRIMARY KEY声明。所以在一般情况下，在单个数据片段内，数据与一级索引以相同的规则升序排列。与其他数据库不同，MergeTree主键允许存在重复数据（ReplacingMergeTree引擎可以去重）
• SAMPLE BY [选填]：用于抽样的表达式，可选项。如果要用抽样表达式，主键中必须包含这个表达式。例如：SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))。
• SETTINGS：index_granularity [选填]：index_granularity对于MergeTree而言是一项非常重要的参数，它表示索引的粒度，默认值为8192。也就是说，MergeTree的索引在默认情况下，每间隔8192行数据才生成一条索引
• SETTINGS：index_granularity_bytes [选填]：在19.11版本之前，ClickHouse只支持固定大小的索引间隔，由index_granularity控制，默认为8192。在新版本中，它增加了自适应间隔大小的特性，即根据每一批次写入数据的体量大小，动态划分间隔大小。而数据的体量大小，正是由index_granularity_bytes参数控制的，默认为10M(10×1024×1024)，设置为0表示不启动自适应功能。
• 数据 TTL：支持整个表数据的有效期设置和单字段有效期设置。

6.索引

• 稠密索引：一条数据创建一条索引
• 稀疏索引：一段数据创建一条索引

1）一级索引

一级索引是稀疏索引，意思就是说：每一段数据生成一条索引记录，而不是每一条数据都生成索引， 如果是每一条数据都生成索引，则是稠密索引。用一个形象的例子来说明：如果把MergeTree比作一本书，那么稀疏索引就好比是这本书的一级章节目录。一级章节目录不会具体对应到每个字的位置，只会记录每个章节的起始页码。

MergeTree 的主键使用 PRIMARY KEY 定义，待主键定义之后，MergeTree 会依据 index_granularity 间隔（默认 8192 行），为数据表生成一级索引并保存至 primary.idx 文件内。

稀疏索引的优势是显而易见的，它仅需使用少量的索引标记就能够记录大量数据的区间位置信息，且数据量越大优势越为明显。以默认的索引粒度（8192）为例，MergeTree只需要12208行索引标记就能为1亿行数据记录提供索引。由于稀疏索引占用空间小，所以primary.idx内的索引数据常驻内存，取用速度自然极快。

在 ClickHouse 中，一级索引常驻内存。总的来说：一级索引和标记文件一一对齐，两个 索引标记之间的数据，就是一个数据区间，在数据文件中，这个数据区间的所有数据，生成一个压缩数据块。每列压缩数据文件，存储每一列的数据，每一列字段都有独立的数据文件，每一列都有对应的标记文件，保存了列压缩文件中数据的偏移量信息，与稀疏索引对齐，又与压缩文件对应，建立了稀疏索引与数据文件的映射关系。不能常驻内存，使用LRU缓存策略加快其取用速度。

需要注意的是：ClickHouse 的主键索引与 MySQL 等数据库不同，它并不用于去重，即便 primary key 相同的行，也可以同时存在于数据库中。要想实现去重效果，需要结合具体的表引擎 ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree 实现。

2）二级索引

二级索引：又称之为跳数索引。目的和一级索引一样，是为了减少待搜寻的数据的范围。

跳数索引的默认是关闭的，需要通过参数来开启，索引生成粒度由 granularity 控制，如果生成了二级索引，则会在分区目录下生成额外的：skp_idx_[Column].idx 与 skp_idx_[Column].mrk 文件。

跳数索引的生成规则：按照特定规则每隔 granularity 个 index_granularity 条数据，就会生成一条跳数索引。

比如 minmax 跳数索引，生成的是：granularity 个 index_granularity 条数据内的最大值最小值生成一条索引，如果将来需要针对构建二级索引的这个字段求最大值最小值，则可以帮助提高效率。

跳数索引一共支持四种类型：minmax(最大最小)、set(去重集合)、 ngrambf_v1（ngram 分词布隆索引）和 tokenbf_v1（标点符号分词布隆索引），一张数据表支持同时声明多个跳数索引。

3）索引粒度

数据以index_granularity(8192)被标记为多个小的区间，其中每个区间做多8192行数据。MergeTree使用MarkRange表示一个具体的区间，并通过start和end表示其具体的范围。index_granularity不但作用于一级索引还会影响标记文件和数据文件。因为仅有一级索引文件是无法完成查询工作的，需要借助于标记来定位数据，所以一级索引和和数据标记的间隔粒度相同，彼此对齐，而数据文件也会按照index_granularity的间隔粒度生成压缩数据块。

4）查询和写入

查询过程：

数据查询的本质，可以看作一个不断减小数据范围的过程。我们可以总结出数据查询流程：MergeTree 首先可以依次借助分区索引、一级索引和二级索引，将数据 扫描范围缩至最小。然后再借助数据标记，将需要解压与计算的数据范围缩至最小。

写入过程：

第一步是生产分区目录，伴随着每一批数据的写入，都会生成一个新的分区目录。在后续的某一时刻，属于相同分区的目录会按照规则合并到一起；接着按照index_granularity索引粒度，会分别生成primary.idx一级索引（若声明了二级索引则会穿创建二级索引文件）。每批数据的写入，都会生成一个新的分区目录，后续会异步地将相同分区的目录进行合并。按照索引粒度，会分别生成一级索引文件、每个字段的标记和压缩数据文件

四、ClickHouse应用场景

1.使用场景

• 绝大多数请求都是用于读访问的，数据一次写入，多次查询
• 数据需要以大批次（大于1000行）进行更新，而不是单行更新；或者根本没有更新操作
• 数据只是添加到数据库，没有必要修改
• 读取数据时，会从数据库中提取出大量的行，但只用到一小部分列
• 表很“宽”，即表中包含大量的列
• 查询频率相对较低（通常每台服务器每秒查询数百次或更少）
• 对于简单查询，允许大约50毫秒的延迟
• 列的值是比较小的数值和短字符串（例如，每个URL只有60个字节）
• 在处理单个查询时需要高吞吐量（每台服务器每秒高达数十亿行）
• 不需要事务
• 数据一致性要求较低

2.业务场景

流量分析、精准营销、广告实时竞价、BI 报表分析、用户行为分析、日志分析、实时大屏等。

• 数据仓库：ClickHouse 可以快速地处理大量的数据，支持高并发查询和复杂的聚合分析，是数据仓库和数据分析的首选工具。
• 实时数据分析：ClickHouse 支持实时数据分析和实时查询，可以快速地处理实时数据流，是实时数据分析和实时监控的首选工具。
• 时序数据存储：ClickHouse 支持时序数据存储和时序数据分析，可以快速地处理时间序列数据，是时序数据存储和时序数据分析的首选工具。
• 数据可视化：ClickHouse 支持数据可视化和报表生成，可以快速地生成各种类型的报表和图表，是数据可视化和报表生成的首选工具。

五、总结

1.缺点

• 没有完整的事务支持；
• 不支持分词查询；
• 缺少高频率，低延迟修改或删除已存在数据的能力。仅能用于批量删除或修改数据；
• 不擅长 join 操作；
• 不支持高并发，官方建议qps为100。原因：ClickHouse 将数据划分为多个 partition，每个 partition 再进一步划分为多个 index_granularity(索引粒度)，然后通过多个 CPU核心分别处理其中的一部分来实现并行数据处理。在这种设计下， 单条 Query 就能利用整机所有 CPU。极致的并行处理能力，极大降低了查询延时。所以，ClickHouse 即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多 cpu，就不利于同时并发多条查询。所以对于高 qps 的查询业务， ClickHouse 并不是强项。

2.为什么查询这么快

利用存储引擎的特殊设计充分减少磁盘I/O对查询速度的影响。从用户提交一条SQL语句进行查询到最终输出结果的过程中，大量的时间是消耗在了磁盘I/O上，在很多情况下，I/O所占用的时间可以达到整个时间的90%以上。对存储引擎磁盘I/O的优化可以获得非常大的收益。ClickHouse的存储引擎设计中大量优化的目的也是为了减少磁盘I/O。

• 列存
• 预排序：在实现范围查找时，可以将大量的随机读转换为顺序读，从而有效提高I/O效率，降低范围查询时的I/O时间
• 数据压缩：可以减少读取和写入的数据量，从而减少I/O时间
• 向量化引擎，尽可能多地使用内置函数
• 尽可能避免Join操作，可以用Spark替代
• ClickHouse 会在内存中进行 GROUP BY，并且使用 HashTable 装载数据
• 索引
• 多线程和分布式
• 算法：ClickHouse针对不同的应用场景，选择不同的算法
• 对于常量字符串查询，使用volnitsky算法
• 对于非常量字符串，使用CPU的向量化执行SIMD，进行暴力优化
• 对于字符串正则匹配，使用re2和hyperscan算法

3.为什么写入性能这么好

• 列存
• 数据压缩：能够有效地减少数据的存储空间，从而提高写入性能
• 分布式架构
• 多线程写入：ClickHouse采用多线程写入机制，能够同时处理多个写入请求，从而提高写入性能。

LSM-Tree存储结构。

先明白一个测试数据：磁盘顺序读写和随机读写的性能差距大概是1千到5千倍之间

连续 I/O 顺序读写，磁头几乎不用换道，或者换道的时间很短，性能很高，比如0.03 * 2000 MB /s

随机 I/O 随机读写，会导致磁头不停地换道，造成效率的极大降低，0.03MB/s

ClickHouse中的MergeTree也是类LSM树的思想，日志结构合并树，但不是树，而是利用磁盘顺序读写能力，实现一个多层读写的存储结构 是一种分层，有序，面向磁盘的数据结构，核心思想是利用了磁盘批量的顺序写要远比随机写性能高出很多 大大提升了数据的写入能力。

充分利用了磁盘顺序写的特性，实现高吞吐写能力，数据写入后定期在后台Compaction。在数据导入时全部是顺序append写，在后台合并时也是多个段merge sort后顺序写回磁盘。官方公开benchmark测试显示能够达到50MB-200MB/s的写入吞吐能力，按照每行100Byte估算，大约相当于50W-200W条/s的写入速度。

作者丨苗元

来源丨公众号：京东云开发者（ID：JDT_Developers）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn

直播预告丨三位大数据专家齐聚，探讨实时计算、数据湖、数据治理、平台化建设与实践

随着企业业务规模的不断扩大，数据分析处理的准确性和实时性要求也逐步提高。因此，建设兼顾效率和质量的大数据体系成为了业界的共同课题。为此，dbaplus社群携手爱奇艺三位大数据专家，围绕“爱奇艺复杂场景下的大数据体系建设与实践”这一主题开展线上直播分享，针对实时计算、数据湖、数据治理、平台化建设等议题进行深入探讨，给大家提供企业级大数据体系建设管理经验参考。

原文：对比MySQL和ES后，毫不犹豫把百亿数据存到ClickHouse了