ClickHouse

软件介绍

简介

• 实时数据分析数据库
• 工作速度比传统方法快100-1000倍，ClickHouse 的性能超过了目前市场上可比的面向列的DBMS
• 每秒钟每台服务器每秒处理数亿至十亿多行和数十千兆字节的数据

特点

• 开源的列存储数据库管理系统，支持线性扩展，简单方便，高可靠性
• 容错跑分快：比Vertica快5倍，比Hive快279倍，比MySQL快800倍,其可处理的数据级别已达到10亿级别
• 功能多：支持数据统计分析各种场景，支持类SQL查询，异地复制部署

优点

• 真正的面向列的DBMS（ClickHouse是一个DBMS,而不是一个单一的数据库。它允许在运行时创建表和数据库、加载数据和运行查询，而无需重新配置和重新启动服务器）
• 数据压缩（一些面向列的DBMS（INFINIDB CE 和 MonetDB）不使用数据压缩
• 磁盘存储的数据（许多面向列的DBMS(SPA HANA和GooglePowerDrill)）只能在内存中工作。但即使在数千台服务器上，内存也太小了。）
• 多核并行处理(多核多节点并行化大型查询)
• 在多个服务器上分布式处理(
• SQL支持
• 向量化引擎(数据不仅按列式存储，而且由矢量-列的部分进行处理，这使得开发者能够实现高CPU性能)
• 实时数据更新
• 支持近似计算
• 数据复制和对数据完整性的支持

缺点

• 没有完整的事务支持，不支持Transaction想快就别Transaction
• 缺少完整Update/Delete操作，缺少高频率、低延迟的修改或删除已存在数据的能力，仅用于批量删除或修改数据。
• 聚合结果必须小于一台机器的内存大小
• 支持有限操作系统，正在慢慢完善
• 不适合Key-value存储，不支持Blob等文档型数据库

系统架构

Column与Field

• Column和Field是ClickHouse数据最基础的映射单元。内存中的一列数据由一个Column对象表
  示
• Column对象分为接口和实现两个部分，在IColumn接口对象中，定义了对数据进行各种关系运算的方法
• 在大多数场合，ClickHouse都会以整列的方式操作数据，但凡事也有例外。如果需要操作单个具体的数值 ( 也就是单列中的一行数据 )，则需要使用Field对象，Field对象代表一个单值。
• 与Column对象的泛化设计思路不同，Field对象使用了聚合的设计模式。在Field对象内部聚合了Null、UInt64、String和Array等13种数据类型及相应的处理逻辑

数据类型DataType

• 负责序列化和反序列化：读写二进制或文本形式的列或单个值构成的块。直接与表的数据类型相对应
• 仅存储元数据
• 数据的序列化和反序列化工作由DataType负责
• DataType虽然负责序列化相关工作，但它并不直接负责数据的读取，而是转由从Column或Field对象获取

块Block

• Block 是表示内存中表的子集（chunk）的容器，是由三元组： (IColumn, IDataType, 列名)构成的集合
• ClickHouse内部的数据操作是面向Block对象进行的，并且采用了流的形式。Block对象可以看作数据表的子集
• Block并没有直接聚合Column和DataType对象，而是通过ColumnWithTypeAndName对象进行间接引用

块流BlockStreams

• 块流用于处理数据

• Block流操作有两组顶层接口

  • IBlockInputStream负责数据的读取和关系运算， IBlockInputStream 具有 read 方法，其能够在数据可用时获取下一个块。
  • IBlockOutputStream负责将数据输出到下一环节。 IBlockOutputStream 具有 write 方法，其能够将块写到某处。

• IBlockInputStream接口总共有60多个实现类，这些实现类大致可以分为三类

  • 第一类用于处理数据定义的DDL操作
  • 第二类用于处理关系运算的相关操作
  • 第三类则是与表引擎呼应，每一种表引擎都拥有与之对应的BlockInputStream实现

Formats格式

• 数据格式同块流一起实现。用于向客户端输出数据的»展示«格式

数据读写I/O

• 对于面向字节的输入输出，有 ReadBuffer 和 WriteBuffer 这两个抽象类
• ReadBuffer 和 WriteBuffer 由一个连续的缓冲区和指向缓冲区中某个位置的一个指针组成。
• ReadBuffer 和 WriteBuffer 的实现用于处理文件、文件描述符和网络套接字（socket），也用于实现压缩和其它用途。

数据表Table

• 在数据表的底层设计中并没有所谓的Table对象
• 表由 IStorage 接口表示。该接口的不同实现对应不同的表引擎。
• 表引擎是ClickHouse的一个显著特性，不同的表引擎由不同的子类实现。
• IStorage 中最重要的方法是 read 和 write ，除此之外还有 alter 、 rename 和 drop 等方法
• 表的 read 方法能够返回多个 IBlockInputStream 对象以允许并行处理数据。多个块输入流能够从一个表中并行读取。
• AST 查询被传递给 read 方法，表引擎可以使用它来判断是否能够使用索引，从而从表中读取更少的数据。

解析器Parser

• 解析sql语句
• 查询由一个手写递归下降解析器解析
• 解析器创建 AST 。 AST 由节点表示，节点是 IAST 的实例

解释器Interpreter

• 解析sql语句

函数Functions

• 普通函数（Functions）

  • 单行函数。不会改变行数 - 它们的执行看起来就像是独立地处理每一行数据

• 聚合函数（Aggregate Functions）

  • 组函数

Cluster与Replication

• ClickHouse的集群由分片 ( Shard ) 组成，而每个分片又通过副本 ( Replica ) 组成。

• 这种分层的概念，在一些流行的分布式系统中十分普遍

  • ClickHouse的1个节点只能拥有1个分片，也就是说如果要实现1分片、1副本，则至少需要部署2个服务节点。
  • 分片只是一个逻辑概念，其物理承载还是由副本承担的

数据定义

数据类型

• 基本数据类型

  • 整数Int8、Int16、Int32 和 Int64

  • 浮点数 Float32 和 Float64

  • 定点数 Decimal32、Decimal64 和Decimal128

  • 布尔 UInt8 限制值为0或1

• 字符串

  • String

    • 不限制长度，相当于Varchar、Text、Clob 和 Blob 等字符类型

  • FixedString

    • 相当于Char，长度固定，数据长度不够时，添加空字节（null）；长度过长返回错误消息

  • UUID

    • 32位，格式8-4-4-4-12，如果未被赋值，则用0填充

      CREATE TABLE UUID_TEST (
      c1 UUID,
      c2 String
      ) ENGINE = Memory;
      –第一行UUID有值
      INSERT INTO UUID_TEST SELECT generateUUIDv4(),‘t1’
      –第二行UUID没有值
      INSERT INTO UUID_TEST(c2) VALUES(‘t2’)

• 日期时间

  • Date: 2020-02-02 精确到天

    CREATE TABLE Date_TEST (
    c1 Date
    ) ENGINE = Memory
    –以字符串形式写入
    INSERT INTO Date_TEST VALUES(‘2019-06-22’)
    SELECT c1, toTypeName(c1) FROM Date_TEST

  • DateTime: 2020-02-02 20:20:20 精确到秒

    CREATE TABLE Datetime_TEST (
    c1 Datetime
    ) ENGINE = Memory
    –以字符串形式写入
    INSERT INTO Datetime_TEST VALUES(‘2019-06-22 00:00:00’)
    SELECT c1, toTypeName(c1) FROM Datetime_TEST

  • DateTime64: 2020-02-02 20:20:20.335 精确到亚秒，可以设置精度

    CREATE TABLE Datetime64_TEST (
    c1 Datetime64(2)
    ) ENGINE = Memory
    –以字符串形式写入
    INSERT INTO Datetime64_TEST VALUES(‘2019-06-22 00:00:00’)
    SELECT c1, toTypeName(c1) FROM Datetime64_TEST

• 复合类型

  • 数组

    • 创建数据：array(T)或[]，类型必须相同

      SELECT array(1, 2) as a , toTypeName(a)
      SELECT [1, 2, null] as a , toTypeName(a)
      CREATE TABLE Array_TEST (
      c1 Array(String)
      ) engine = Memory

  • 元组

    • 由多个元素组成，允许不同类型

    • 创建数据：(T1, T2, …)，Tuple(T1, T2, …)

      SELECT tuple(1,‘a’,now()) AS x, toTypeName(x)
      SELECT (1,2.0,null) AS x, toTypeName(x)
      CREATE TABLE Tuple_TEST (
      c1 Tuple(String,Int8)
      ) ENGINE = Memory;

  • 枚举类型

    • ClickHouse提供了Enum8和Enum16两种枚举类型，它们除了取值范围不同之外，别无二致

    • 枚举固定使用(String:Int)Key/Value键值对的形式定义数据，所以Enum8和Enum16分别会对应(String:Int8)和(String:Int16)

    • 用(String:Int) Key/Value键值对的形式定义数据,键值对不能同时为空，不允许重复，key允许
      为空字符串，需要看到对应的值进行转换

      CREATE TABLE Enum_TEST (
      c1 Enum8(‘ready’ = 1, ‘start’ = 2, ‘success’ = 3, ‘error’ = 4)
      ) ENGINE = Memory;
      –正确语句
      INSERT INTO Enum_TEST VALUES(‘ready’);
      INSERT INTO Enum_TEST VALUES(‘start’);
      –错误语句
      INSERT INTO Enum_TEST VALUES(‘stop’);

  • 嵌套类型

    • Nested（Name1 Type1，Name2 Type2，…）

    • 相当于表中嵌套一张表，插入时相当于一个多维数组的格式，一个字段对应一个数组

      CREATE TABLE nested_test (
       name String,
       age UInt8 ,
       dept Nested(
        id UInt8,
        name String
       )
      ) ENGINE = Memory;
      –行与行之间,数组长度无须对齐
      INSERT INTO nested_test VALUES (‘bruce’ , 30 , [10000,10001,10002], [‘研
      发部’,‘技术支持中心’,‘测试部’]);
      INSERT INTO nested_test VALUES (‘bruce’ , 30 , [10000,10001], [‘研发
      部’,‘技术支持中心’]);

• 其他类型

  • Nullable（TypeName）

    • 只能与基础数据类型搭配使用，表示某个类型的值可以为NULL；Nullable(Int8)表示可以存储Int8类型的值，没有值时存NULL

      CREATE TABLE Null_TEST (
      c1 String,
      c2 Nullable(UInt8)
      ) ENGINE = TinyLog;
      –通过Nullable修饰后c2字段可以被写入Null值：
      INSERT INTO Null_TEST VALUES (‘nauu’,null)
      INSERT INTO Null_TEST VALUES (‘bruce’,20)
      SELECT c1 , c2 ,toTypeName(c2) FROM Null_TEST

    • 注意

      • 不能与复合类型数据一起使用、
      • 不能作为索引字段
      • 尽量避免使用，字段被Nullable修饰后会额外生成[Column].null.bin 文件保存Null值，增加开销

  • Domain

    • Pv4 使用 UInt32 存储。如 116.253.40.133

    • IPv6 使用 FixedString(16) 存储。如 2a02:aa08:e000:3100::2

      CREATE TABLE IP4_TEST (
      url String,
      ip IPv4
      ) ENGINE = Memory;
      INSERT INTO IP4_TEST VALUES (‘www.nauu.com’,‘192.0.0.0’)
      SELECT url , ip ,toTypeName(ip) FROM IP4_TEST

数据库

• 数据库起到了命名空间的作用，可以有效规避命名冲突的问题，也为后续的数据隔离提供了支撑。任何一张数据表，都必须归属在某个数据库之下

• 操作语法

  CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]
  SHOW DATABASES
  DROP DATABASE [IF EXISTS] db_name

• 数据库引擎

  • Ordinary：默认引擎

    • 在绝大多数情况下我们都会使用默认引擎，使用时无须刻意声明。在此数据库下可以使用任意类型的表引擎。

  • Dictionary：字典引擎

    • 此类数据库会自动为所有数据字典创建它们的数据表，关于数据字典的详细介绍会在第5章展开。

  • Memory：内存引擎

    • 用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除a

  • Lazy：日志引擎

    • 此类数据库下只能使用Log系列的表引擎，关于Log表引擎的详细介绍会在第8章展开。

  • MySQL：MySQL引擎

    • 此类数据库下会自动拉取远端MySQL中的数据，并为它们创建MySQL表引擎的数据表

数据表

• ClickHouse目前提供了三种最基本的建表方法

  • 常规定义方法

    CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
    name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    省略…
    ) ENGINE = engine
    CREATE TABLE hits_v1 (
    Title String,
    URL String ,
    EventTime DateTime
    ) ENGINE = Memory;

    • 使用[db_name.]参数可以为数据表指定数据库，如果不指定此参数，则默认会使用default数据库

  • 复制其他表的结构

    CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name AS [db_name2.]
    table_name2 [ENGINE = engine]
    –创建新的数据库
    CREATE DATABASE IF NOT EXISTS new_db
    –将default.hits_v1的结构复制到new_db.hits_v1
    CREATE TABLE IF NOT EXISTS new_db.hits_v1 AS default.hits_v1 ENGINE =
    TinyLog

    • 支持在不同的数据库之间复制表结构

  • 通过SELECT子句的形式创建

    CREATE TABLE [IF NOT EXISTS] [db_name.]table_name ENGINE = engine AS
    SELECT …
    CREATE TABLE IF NOT EXISTS hits_v1_1 ENGINE = Memory AS SELECT * FROM
    hits_v1

    • 根据SELECT子句建立相应的表结构，同时还会将SELECT子句查询的数据顺带写入

• 删除表

  • 和大多数数据库一样，使用DESC查询可以返回数据表的定义结构

  • 如果想删除一张数据表，则可以使用下面的DROP语句

    • DROP TABLE [IF EXISTS] [db_name.]table_name

• 临时表

  • 创建临时表的方法是在普通表的基础之上添加TEMPORARY关键字

    CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name (
    name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    )

  • 特点

    • 它的生命周期是会话绑定的，所以它只支持Memory表引擎，如果会话结束，数据表就会被销毁；
    • 临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数。
    • 临时表的优先级是大于普通表的。当两张数据表名称相同的时候，会优先读取临时表的数据

• 分区表

  • 数据分区（partition）和数据分片（shard）是完全不同的两个概念

  • 数据分区是针对本地数据而言的，是数据的一种纵向切分。而数据分片是数据的一种横向切分

  • 案例

    CREATE TABLE partition_v1 (
    ID String,
    URL String,
    EventTime Date
    ) ENGINE = MergeTree()
    PARTITION BY toYYYYMM(EventTime)
    ORDER BY ID
    INSERT INTO partition_v1 VALUES
    (‘A000’,‘www.nauu.com’, ‘2019-05-01’),
    (‘A001’,‘www.brunce.com’, ‘2019-06-02’)
    SELECT table,partition,path from system.parts WHERE table =
    ‘partition_v1’

• 数据表操作

  • 追加新字段

    ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type]
    [default_expr] [AFTER name_after]

    ALTER TABLE testcol_v1 ADD COLUMN OS String DEFAULT ‘mac’

    ALTER TABLE testcol_v1 ADD COLUMN IP String AFTER ID

  • 修改字段类型

    ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [type] [default_expr]

    ALTER TABLE testcol_v1 MODIFY COLUMN IP IPv4

  • 修改备注

    ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name ‘some comment’

    ALTER TABLE testcol_v1 COMMENT COLUMN ID ‘主键ID’
    DESC testcol_v1

  • 删除已有字段

    ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name

    ALTER TABLE testcol_v1 DROP COLUMN URL

  • 清空数据表

    TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name

    TRUNCATE TABLE db_test.testcol_v2

视图

• 普通视图

  • CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT …
  • 普通视图不会存储任何数据，它只是一层单纯的SELECT查询映射，起着简化查询、明晰语义的作用，对查询性能不会有任何增强

• 物化视图

  • CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name]
    [ENGINE = engine] [POPULATE] AS SELECT .

  • 物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新

    • 如果使用了POPULATE修饰符，那么在创建视图的过程中，会连带将源表中已存在的数据一并导入，如同执行了SELECT INTO一般
    • 如果不使用POPULATE修饰符，那么物化视图在创建之后是没有数据的，它只会同步在此之后被写入源表的数据
    • 物化视图目前并不支持同步删除，如果在源表中删除了数据，物化视图的数据仍会保留。

数据的CRUD

• 数据的写入

  • 使用VALUES格式的常规语法
  • 使用指定格式的语法
  • 使用SELECT子句形式的语法

• 数据的删除和修改

  • ClickHouse提供了DELETE和UPDATE的能力，这类操作被称为Mutation查询，它可以看作ALTER语句的变种

  • 虽然Mutation能最终实现修改和删除，但不能完全以通常意义上的UPDATE和DELETE来理解

    • 首先，Mutation语句是一种“很重”的操作，更适用于批量数据的修改和删除；
    • 其次，它不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚；
    • 最后，Mutation语句的执行是一个异步的后台过程，语句被提交之后就会立即返回。

MergeTree

概述

• 表引擎是ClickHouse设计实现中的一大特色
• ClickHouse拥有非常庞大的表引擎体系，其共拥有合并树、外部存储、内存、文件、接口和其他6大类20多种表引擎
• MergeTree作为家族中最基础的表引擎，提供了主键索引、数据分区、数据副本和数据采样等基本能力

创建与存储

• 概述

  • MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数据片段不可修改
  • 为了避免片段过多，ClickHouse会通过后台线程，定期合并这些数据片段，属于相同分区的数据片段会被合成一个新的片段
  • 这种数据片段往复合并的特点，也正是合并树名称的由来

• 创建方式

  • 语法

  • 配置选项

    • PARTITION BY [选填]：分区键，用于指定表数据以何种标准进行分区

      • 分区键既可以是单个列字段，也可以通过元组的形式使用多个列字段，同时它也支持使用列表达式。
      • 如果不声明分区键，则ClickHouse会生成一个名为all的分区。
      • 合理使用数据分区，可以有效减少查询时数据文件的扫描范围

    • ORDER BY [必填]：排序键，用于指定在一个数据片段内，数据以何种标准排序

      • 默认情况下主键（PRIMARY KEY）与排序键相同。
      • 排序键既可以是单个列字段也可以通过元组的形式使用多个列字段
      • 当使用多个列字段排序时，以ORDERBY（CounterID,EventDate）为例，在单个数据片段内，数据首先会以CounterID排序，相同CounterID的数据再按EventDate排序

    • PRIMARY KEY [选填]：主键，顾名思义，声明后会依照主键字段生成一级索引，用于加速表查询

      • 默认情况下，主键与排序键(ORDER BY)相同
        通常直接使用ORDER BY代为指定主
        键，无须刻意通过PRIMARY KEY声明
      • 与其他数据库不同，MergeTree主键允许存在重复数据（ReplacingMergeTree可以去重）

    • SAMPLE BY [选填]：抽样表达式，用于声明数据以何种标准进行采样

      • 如果使用了此配置项，那么在主键的配置中也需要声明同样的表达式

    • SETTINGS

      • index_granularity [选填]

        • 对于MergeTree而言是一项非常重要的参数，它表示索引的粒度，默认值为8192
        • MergeTree的索引在默认情况下，每间隔8192行数据才生成一条索引

• 存储格式

  • 数据会按照分区目录的形式保存到磁盘之上

  • 一张数据表的完整物理结构分为3个层级，依次是数据表目录、分区目录及各分区下具体的数据文件

    • partition：分区目录，余下各类数据文件（primary.idx、[Column].mrk、[Column].bin等）
      都是以分区目录的形式被组织存放的，属于相同分区的数据，最终会被合并到同一个分区目
      录，而不同分区的数据，永远不会被合并在一起
    • checksums.txt：校验文件
    • columns.txt：列信息文件
    • count.txt：计数文件，用于记录当前数据分区目录下数据的总行数
    • primary.idx：一级索引文件，使用二进制格式存储。用于存放稀疏索引
    • [Column].bin：数据文件，使用压缩格式存储
    • [Column].mrk：列字段标记文件，使用二进制格式存储。标记文件中保存了.bin文件中数据的偏移量信息
    • [Column].mrk2：如果使用了自适应大小的索引间隔，则标记文件会以.mrk2命名。它的工作原理和作用与.mrk标记文件相同
    • partition.dat与minmax_[Column].idx：如果使用了分区键，例如PARTITION BYEventTime，则会额外生成partition.dat与minmax索引文件
    • skp_idx[Column].idx与skp_idx[Column].mrk：如果在建表语句中声明了二级索引，则会额外生成相应的二级索引与标记文件

数据分区

• 数据分区规则

  • MergeTree数据分区的规则由分区ID决定，而具体到每个数据分区所对应的ID，则是由分区键的取值决定的

  • 针对取值数据类型的不同，分区ID的生成逻辑目前拥有四种规则

    • 不指定分区键

      • 如果不使用分区键，即不使用PARTITION BY声明任何分区表达式，则分区ID默认取名为all，所有的数据都会被写入这个all分区

    • 使用整型

      • 直接按照该整型的字符形式输出，作为分区ID的取值

    • 使用日期类型

      • 使用按照YYYYMMDD进行格式化后的字符形式输出，并作为分区ID的取值

    • 使用其他类型

      • 既不属于整型，也不属于日期类型
      • 例如String、Float等，则通过128位Hash算法取其Hash值作为分区ID的取值

• 分区目录命名

  • 一个完整分区目录的命名公式

    • 201905表示分区目录的ID；
    • 1_1分别表示最小的数据块编号与最大的数据块编号；
    • 而最后的_0则表示目前合并的层级

  • PartitionID_MinBlockNum_MaxBlockNum_Level

    • PartitionID：分区ID
    • MinBlockNum和MaxBlockNum：顾名思义，最小数据块编号与最大数据块编号。
    • Level：合并的层级，可以理解为某个分区被合并过的次数，或者这个分区的年龄。数值越高表示年龄越大。

• 分区目录合并

  • MergeTree的分区目录并不是在数据表被创建之后就存在的，而是在数据写入过程中被创建的

    • 也就是说如果一张数据表没有任何数据，那么也不会有任何分区目录存在

  • 其次，它的分区目录在建立之后也并不是一成不变的

    • 伴随着每一批数据的写入（一次INSERT语句），MergeTree都会生成一批新的分区目录
    • 即便不同批次写入的数据属于相同分区，也会生成不同的分区目录
    • 在之后的某个时刻（写入后的10～15分钟，也可以手动执行optimize查询语句）
    • ClickHouse会通过后台任务再将属于相同分区的多个目录合并成一个新的目录。
    • 已经存在的旧分区目录并不会立即被删除，而是在之后的某个时刻通过后台任务被删除（默认8分钟）。

  • 新目录名称的合并方式遵循规则

    • MinBlockNum：取同一分区内所有目录中最小的MinBlockNum值。
    • MaxBlockNum：取同一分区内所有目录中最大的MaxBlockNum值。
    • Level：取同一分区内最大Level值并加1。

一级索引

• 概述

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

• 稀疏索引

  • primary.idx文件内的一级索引采用稀疏索引实现

    • 稠密索引中每一行索引标记都会对应到一行具体的数据记录。

    • 稀疏索引中每一行索引标记对应的是一段数据，而不是一行。

    • 仅需使用少量的索引标记就能够记录大量数据的区间位置信息，且数据量越大优势越为明显

      • 由于稀疏索引占用空间小，所以primary.idx内的索引数据常驻内存，取用速度自然极快

• 索引粒度

  • 索引粒度就如同标尺一般，会丈量整个数据的长度，并依照刻度对数据进行标注，最终将数据标记成多个间隔的小段

• 索引规则

  • 由于是稀疏索引，所以MergeTree需要间隔index_granularity行数据才会生成一条索引记录，其索引值会依据声明的主键字段获取
  • 单主键
  • 多主键

• 索引查询过程

二级索引

• 概述

  • 二级索引又称跳数索引，由数据的聚合信息构建而成
  • 根据索引类型的不同，其聚合信息的内容也不同。跳数索引的目的与一级索引一样，也是帮助查询时减少数据扫描的范围
  • 需要手动开启

• 粒度

  • granularity定义了一行跳数索引能够跳过多少个index_granularity区间的数据

• 分类

  • minmax

    • 适合连续查询，重复少的数据

  • set

    • 适合大量重复的字典数据

  • ngrambf_v1

    • 布隆过滤，快速匹配，可能误判

  • tokenbf_v1

    • grambf_v1的变种，同样也是一种布隆过滤器索引
    • 除了短语token的处理方法外
    • 会自动按照非字符的、数字的字符串分割token

数据存储

• 列式存储

  • 每列对应一个bin数据文件

  • 优势

    • 一是可以更好地进行数据压缩
    • 二是能够最小化数据扫描的范围

  • 存储方式

    • 首先，数据是经过压缩的
    • 其次，数据会事先依照ORDER BY的声明排序
    • 最后，数据是以压缩数据块的形式被组织并写入.bin文件中的

• 数据压缩

  • 一个压缩数据块由头信息和压缩数据两部分组成

    • 头信息固定使用9位字节表示，具体由1个UInt8（1字节）整型和2个UInt32（4字节）整型组成。分别代表使用的压缩算法类型、压缩后的数据大小和压缩前的数据大小
    • bin压缩文件是由多个压缩数据块组成的
    • 每个压缩数据块的体积，按照其压缩前的数据字节大小，都被严格控制在64KB～1MB

  • 数据写入过程

    • 依照索引粒度（默认情况下，每次取8192行），按批次获取数据并进行处理
    • 单个批次数据size<64KB ：如果单个批次数据小于64KB，则继续获取下一批数据，直至累积到size>=64KB时，生成下一个压缩数据块
    • 64KB<=size<=1MB直接生成下一个压缩数据块
    • size>1MB首先按照1MB大小截断并生成下一个压缩数据块。剩余数据继续依照上述规则执行

  • 优势

    • 虽然数据被压缩后能够有效减少数据大小，降低存储空间并加速数据传输效率，但数据的压缩和解压动作，其本身也会带来额外的性能损耗
    • 进一步缩小数据读取的范围

  • 压缩会丢失块的位置信息，用数据标记可解决

• 数据标记

  • 生成规则

    • 数据标记作为衔接一级索引和数据的桥梁
    • 数据标记和索引区间是对齐的，均按照index_granularity的粒度间隔
    • 为了能够与数据衔接，数据标记文件也与.bin文件一一对应
    • 一行标记数据使用一个元组表示，元组内包含两个整型数值的偏移量信息
    • 每一行标记数据都表示了一个片段的数据（默认8192行）在.bin压缩文件中的读取位置信息

  • 工作方式

    • MergeTree在读取数据时，必须通过标记数据的位置信息才能够找到所需要的数据。整个查找过程大致可以分为读取压缩数据块和读取数据两个步骤

    • 数据理解

      • 1B*8192=8192B,64KB=65536B,65536/8192=8

      • 头信息固定由9个字节组成，压缩后大小为8个字节

      • 12016=8+12000+8

        • 读取压缩数据块

    • 读取压缩数据块

      • 查询某一列数据时，MergeTree无须一次性加载整个.bin文件，而是可以根据需要，只加载特定的压缩数据块
      • 而这项特性需要借助标记文件中所保存的压缩文件中的偏移量

    • 读取数据

      • 读取解压后的数据时，MergeTree并不需要一次性扫描整段解压数据，它可以根据需要，以index_granularity的粒度加载特定的一小段
      • 为了实现这项特性，需要借助标记文件中保存的解压数据块中的偏移量

• 数据标记与数据压缩

  • 概述

    • 由于压缩数据块的划分，与一个间隔（index_granularity）内的数据大小相关，每个压缩数据块的体积都被严格控制在64KB～1MB。
    • 而一个间隔（index_granularity）的数据，又只会产生一行数据标记。
    • 那么根据一个间隔内数据的实际字节大小，数据标记和压缩数据块之间会产生三种不同的对应关系

  • 多对一

    • 多个数据标记对应一个压缩数据块
    • 当一个间隔（index_granularity）内的数据未压缩大小size小于64KB时

  • 一对一

    • 一个数据标记对应一个压缩数据块
    • 当一个间隔（index_granularity）内的数据未压缩大小size大于等于64KB且小于等于1MB时

  • 一对多

    • 一个数据标记对应多个压缩数据块
    • 当一个间隔（index_granularity）内的数据未压缩大小size直接大于1MB时

数据读写流程

• 写入数据

  • 第一步是生成分区目录，伴随着每一批数据的写入，都会生成一个新的分区目录
  • 后续的某一时刻，属于相同分区的目录会依照规则合并到一起续的某一时刻，属于相同分区的目录会依照规则合并到一起
  • 接着，按照index_granularity索引粒度，会分别生成primary.idx一级索引、每一个列字段的.mrk数据标记和.bin压缩数据文件

• 查询数据

  • 数据查询的本质，可以看作一个不断减小数据范围的过程
  • 在最理想的情况下，MergeTree首先可以依次借助分区索引、一级索引和二级索引，将数据扫描范围缩至最小
  • 然后再借助数据标记，将需要解压与计算的数据范围缩至最小

MergeTree Family

MergeTree

• 数据TTL

  • 顾名思义，它表示数据的存活时间

  • 可以为某个列字段或整张表设置TTL

    • 当时间到达时，如果是列字段级别的TTL，则会删除这一列的数据；
    • 如果是表级别的TTL，则会删除整张表的数据；
    • 如果同时设置了列级别和表级别的TTL，则会以先到期的那个为主

• 多路径存储策略

  • 19.15版本之前，MergeTree只支持单路径存储，所有的数据都会被写入config.xml配置中path指定的路径下

  • 19.15版本开始，MergeTree实现了自定义存储策略的功能，支持以数据分区为最小移动单元，将分区目录写入多块磁盘目录

  • 存储策略

    • 默认策略

      • 同19.15版本之前

    • JBOD策略

      • 轮询策略，每执行一次INSERT或者MERGE，所产生的新分区会轮询写入各个磁盘
      • 适合服务器挂载了多块磁盘，但没有做RAID的场景

    • HOT/COLD策略

      • 将存储磁盘分为HOT与COLD两类区域

        • HOT区域使用SSD这类高性能存储媒介，注重存取性能；
        • COLD区域则使用HDD这类高容量存储媒介，注重存取经济性。
        • 数据在写入MergeTree之初，首先会在HOT区域创建分区目录用于保存数据，当分区数据大小累积到阈值时，数据会自行移动到COLD区域
        • 这种策略适合服务器挂载了不同类型磁盘的场景。

ReplacingMergeTree

• 为了数据去重而设计的，它能够在合并分区时删除重复的数据

• ReplacingMergeTree是以分区为单位删除重复数据的

  • 只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除。
  • 如果要求主键完全不重复，那么这张表就不能分区

SummingMergeTree

• sum求和
• 能够在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分组下的多行数据汇总合并成一行
• 既减少了数据行，又降低了后续汇总查询的开销

AggregatingMergeTree

• 数据立方体

  • 通过以空间换时间的方法提升查询性能，将需要聚合的数据,预先计算出来，并将结果保存起来
  • 在后续进行聚合查询的时候，直接使用结果数据

• AggregatingMergeTree更为常见的应用方式是结合物化视图使用，将它作为物化视图的表引擎

CollapsingMergeTree

• 一种通过以增代删的思路，支持行级数据修改和删除的表引擎

• 通过定义一个sign标记位字段，记录数据行的状态

• 如果sign标记为1，则表示这是一行有效的数据；如果sign标记为-1，则表示这行数据需要被删除

• 当CollapsingMergeTree分区合并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除

• 这种1和-1相互抵消的操作，犹如将一张瓦楞纸折叠了一般

• 折叠规则

  • 如果sign=1比sign=-1的数据多一行，则保留最后一行sign=1的数据。
  • 如果sign=-1比sign=1的数据多一行，则保留第一行sign=-1的数据。
  • 如果sign=1和sign=-1的数据行一样多，并且最后一行是sign=1，则保留第一行sign=-1和最后一行sign=1的数据。
  • 如果sign=1和sign=-1的数据行一样多，并且最后一行是sign=-1，则什么也不保留。
  • 其余情况，ClickHouse会打印警告日志，但不会报错，在这种情形下，查询结果不可预知

• 特点

  • 折叠数据并不是实时触发的，和所有其他的MergeTree变种表引擎一样，这项特性也只有在分区合并的时候才会体现

  • 只有相同分区内的数据才有可能被折叠

  • CollapsingMergeTree对于写入数据的顺序有着严格要求

    • 先写入sign=1，再写入sign=-1，则能够正常折叠
    • 先写入sign=-1，再写入sign=1，则不能够折叠

VersionedCollapsingMergeTree

• VersionedCollapsingMergeTree表引擎的作用与CollapsingMergeTree完全相同
• 它们的不同之处在于，VersionedCollapsingMergeTree对数据的写入顺序没有要求，在同一个分区内，任意顺序的数据都能够完成折叠操作

MergeTree关系梳理

• MergeTree表引擎向下派生出6个变种表引擎

常见类型表引擎

外部存储

• 概述

  • 外部存储表引擎直接从其他的存储系统读取数据
  • 例如直接读取HDFS的文件或者MySQL数据库的表。
  • 这些表引擎只负责元数据管理和数据查询，而它们自身通常并不负责数据的写入，数据文件直接由外部系统提供。

• HDFS

  • ENGINE = HDFS(‘hdfs://node01:8020/clickhouse/hdfs_table1’,‘CSV’);

• Mysql

  • MySQL表引擎可以与MySQL数据库中的数据表建立映射，并通过SQL向其发起远程查询，包括SELECT和INSERT
  • ENGINE = MySQL(‘192.168.88.101:3306’, ‘scott’, ‘dept’, ‘root’,‘123456’);

• JDBC

  • JDBC表引擎不仅可以对接MySQL数据库，还能够与PostgreSQL、SQLite和H2数据库对接。
  • JDBC表引擎无法单独完成所有的工作，它需要依赖名为clickhouse-jdbc-bridge的查询代理服务

• Kafka

  • 目前ClickHouse还不支持恰好一次（Exactly once）的语义，因为这需要应用端与Kafka深度配合才能实现

  • ENGINE = Kafka()
    SETTINGS
    kafka_broker_list = 'host:port,… ',
    kafka_topic_list = ‘topic1,topic2,…’,
    kafka_group_name = ‘group_name’,
    kafka_format = ‘data_format’[,]
    [kafka_row_delimiter = ‘delimiter_symbol’]
    [kafka_schema = ‘’]
    [kafka_num_consumers = N]
    [kafka_skip_broken_messages = N]
    [kafka_commit_every_batch = N]

    • CREATE TABLE kafka_table(
      id UInt32,
      code String,
      name String
      ) ENGINE = Kafka()
      SETTINGS
      kafka_broker_list = ‘node01:9092’,
      kafka_topic_list = ‘topic_clickhouse’,
      kafka_group_name = ‘clickhouse’,
      kafka_format = ‘TabSeparated’,
      kafka_skip_broken_messages = 10;

  • 再次执行SELECT查询会发现kafka_table数据表空空如也,这是因为Kafka表引擎在执行查询之后就会移动offset，导致数据无法重复读取。

  • 解决方法

    • 首先是Kafka数据表A，它充当的角色是一条数据管道，负责拉取Kafka中的数据。
    • 接着是另外一张任意引擎的数据表B，它充当的角色是面向终端用户的查询表，在生产环境中通常是MergeTree系列。
    • 最后，是一张物化视图C，它负责将表A的数据实时同步到表B。

• File

  • File表引擎能够直接读取本地文件的数据，通常被作为一种扩充手段来使用
  • File表引擎的定义参数中，并没有包含文件路径这一项。所以，File表引擎的数据文件只能保存在config.xml配置中由path指定的路径下
  • 每张File数据表均由目录和文件组成，其中目录以表的名称命名，而数据文件则固定以data.format命名

内存类型

• 概述

  • 将数据全量放在内存中，对于表引擎来说是一把双刃剑

    • 一方面，这意味着拥有较好的查询性能；
    • 另一方面，如果表内装载的数据量过大，可能会带来极大的内存消耗和负担

• Memory

  • Memory表引擎直接将数据保存在内存中，数据既不会被压缩也不会被格式转换，数据在内存中保存的形态与查询时看到的如出一辙。
  • 当ClickHouse服务重启的时候，Memory表内的数据会全部丢失。
  • 当数据被写入之后，磁盘上不会创建任何数据文件

• Set

  • Set表引擎是拥有物理存储的，数据首先会被写至内存，然后被同步到磁盘文件中

  • 当服务重启时，它的数据不会丢失，当数据表被重新装载时，文件数据会再次被全量加载至内存

  • Set表引擎具有去重的能力，在数据写入的过程中，重复的数据会被自动忽略

  • Set表引擎的存储结构由两部分组成

    • [num].bin数据文件：保存了所有列字段的数据

      • num是一个自增id，从1开始
      • 伴随着每一批数据的写入（每一次INSERT），都会生成一个新的.bin文件，num也会随之加1

    • tmp临时目录：数据文件首先会被写到这个目录，当一批数据写入完毕之后，数据文件会被移出此目录

• Join

  • Join表引擎可以说是为JOIN查询而生的，它等同于将JOIN查询进行了一层简单封装

    • join_strictness：连接精度

      • 决定了JOIN查询在连接数据时所使用的策略，目前支持ALL、ANY和ASOF三种类型。

    • join_type：连接类型

      • 它决定了JOIN查询组合左右两个数据集合的策略，它们所形成的结果是交集、并集、笛卡儿积或其他形式，目前支持INNER、OUTER和CROSS三种类型

    • join_key：连接键

      • 它决定了使用哪个列字段进行关联

日志类型

• TinyLog

  • TinyLog是日志家族系列中性能最低的表引擎，它的存储结构由数据文件和元数据两部分组成

    • TinyLog既不支持分区，也没有.mrk标记文件
    • 所以它只适合在非常简单的场景下使用

• StripeLog

  • StripeLog表引擎的存储结构由固定的3个文件组成

    • data.bin：数据文件

      • 所有的列字段使用同一个文件保存，它们的数据都会被写入data.bin

    • index.mrk：数据标记

      • 保存了数据在data.bin文件中的位置信息

    • sizes.json：元数据文件

      • 记录了data.bin和index.mrk大小的信息

• Log

  • Log表引擎结合了TinyLog表引擎和StripeLog表引擎的长处，是日志家族系列中性能最高的表引擎

  • 由3个部分组成

    • [column].bin：数据文件

      • 数据文件按列独立存储，每一个列字段都拥有一个与之对应的.bin文件

    • marks.mrk：数据标记

      • 统一保存了数据在各个[column].bin文件中的位置信息

    • sizes.json：元数据文件

      • 记录了[column].bin和__marks.mrk大小的信息

接口类型

• Merge

  • Merge表引擎就如同一层使用了门面模式的代理，它本身不存储任何数据，也不支持数据写入

    • 它的作用就如其名，即负责合并多个查询的结果集。
    • Merge表引擎可以代理查询任意数量的数据表，这些查询会异步且并行执行，并最终合成一个结果集返回
    • 被代理查询的数据表被要求处于同一个数据库内，且拥有相同的表结构，但是它们可以使用不同的表引擎以及不同的分区定义
    • CREATE TABLE test_table_all as test_table_2018
      ENGINE = Merge(currentDatabase(), ‘^test_table_’)

数据查询方式

概述

• 在日常运转的过程中，数据查询也是ClickHouse的主要工作之一
• ClickHouse对于SQL语句的解析是大小写敏感的

With子句

• ClickHouse支持CTE（Common Table Expression，公共表表达式），以增强查询语句的表达

  • 在改用CTE的形式后，可以极大地提高语句的可读性和可维护性

    • SELECT pow(pow(2, 2), 3)
      WITH pow(2, 2) AS a SELECT pow(a, 3)

• With的四种使用方法

  • 定义变量

    • 可以定义变量，这些变量能够在后续的查询子句中被直接访问。

  • 调用函数

    • 可以访问SELECT子句中的列字段，并调用函数做进一步的加工处理。

  • 定义子查询

  • 在子查询中重复使用WITH

    • 在子查询中可以嵌套使用WITH子句

From子句

• FROM子句表示从何处读取数据，目前支持如下3种形式

  • 从数据表中取数
  • 从子查询中取数
  • 从表函数中取数

• 在ClickHouse中，并没有数据库中常见的DUAL虚拟表，取而代之的是system.one。

• 在FROM子句后，可以使用Final修饰符

  • 它可以配合CollapsingMergeTree和Versioned-CollapsingMergeTree等表引擎进行查询操作，以强制在查询过程中合并
  • 但由于Final修饰符会降低查询性能，所以应该尽可能避免使用它

Sample子句

• SAMPLE子句能够实现数据采样的功能，使查询仅返回采样数据而不是全部数据，从而有效减少查询负载

• SAMPLE子句的采样机制是一种幂等设计，也就是说在数据不发生变化的情况下，使用相同的采样规则总是能够返回相同的数据

• SAMPLE子句只能用于MergeTree系列引擎的数据表，并且要求在CREATE TABLE时声明SAMPLEBY抽样表达式

• 支持如下3种用法

  • SAMPLE factor

    • 表示按因子系数采样，其中factor表示采样因子，它的取值支持0～1之间的小数
    • 如果factor设置为0或者1，则效果等同于不进行数据采样

  • SAMPLE rows

    • 表示按样本数量采样，其中rows表示至少采样多少行数据，它的取值必须是大于1的整数
    • 如果rows的取值大于表内数据的总行数，则效果等于rows=1

  • SAMPLE factor OFFSET n

    • 表示按因子系数和偏移量采样，其中factor表示采样因子，n表示偏移多少数据后才开始采样，它们两个的取值都是0～1之间的小数

Array Join子句

• ARRAY JOIN子句允许在数据表的内部，与数组或嵌套类型的字段进行JOIN操作，从而将一行数组展开为多行

• 在一条SELECT语句中，只能存在一个ARRAY JOIN（使用子查询除外）。目前支持INNER和LEFT两种

  • INNER ARRAY JOIN

    • 最终的数据基于value数组被展开成了多行，并且排除掉了空数组

  • LEFT ARRAY JOIN

    • ARRAY JOIN子句支持LEFT连接策略
    • 在INNER JOIN中被排除掉的空数组出现在了返回的结果集中
    • 当同时对多个数组字段进行ARRAY JOIN操作时，查询的计算逻辑是按行合并而不是产生笛卡儿积

Join 子句

• 概述

  • JOIN子句可以对左右两张表的数据进行连接
  • JOIN的语法包含连接精度和连接类型两部分
  • JOIN查询还可以根据其执行策略被划分为本地查询和远程查询。

• 连接精度

  • 决定了JOIN查询在连接数据时所使用的策略，目前支持ALL、ANY和ASOF三种类型。如果不主动声明，则默认是ALL。

    • all

      • 如果左表内的一行数据，在右表中有多行数据与之连接匹配，则返回右表中全部连接的数据

    • any

      • 如果左表内的一行数据，在右表中有多行数据与之连接匹配，则仅返回右表中第一行连接的数据

    • asof

      • ASOF是一种模糊连接，它允许在连接键之后追加定义一个模糊连接的匹配条件asof_column
      • 最终返回的查询结果符合连接条件a.id=b.id AND a.time>=b.time，且仅返回了右表中第一行连接匹配的数据
      • ASOF支持使用USING的简写形式，USING后声明的最后一个字段会被自动转换成asof_colum模糊连接条件
      • asof_colum必须是整型、浮点型和日期型这类有序序列的数据类型；
      • asof_colum不能是数据表内的唯一字段

• 连接类型

  • Inner

    • 表示内连接，在查询时会以左表为基础逐行遍历数据，然后从右表中找出与左边连接的行，它只会返回左表与右表两个数据集合中交集的部分，其余部分都会被排除

  • OUTER

    • OUTER JOIN表示外连接，它可以进一步细分为左外连接（LEFT）、右外连接（RIGHT）和全外连接（FULL）三种形式

  • Cross

    • CROSS JOIN表示交叉连接，它会返回左表与右表两个数据集合的笛卡儿积

查询优化

• 为了能够优化JOIN查询性能，首先应该遵循左大右小的原则 ，无论使用的是哪种连接方式，右表
  都会被全部加载到内存中与左表进行比较
• JOIN查询目前没有缓存的支持
• 如果是在大量维度属性补全的查询场景中，则建议使用字典代替JOIN查询
• 连接查询的空值是由默认值填充的，这与其他数据库所采取的策略不同（由Null填充）

WHERE与PREWHERE子句

• WHERE子句基于条件表达式来实现数据过滤
  果过滤条件恰好是主键字段，则能够进一步借助
  索引加速查询

• PREWHERE目前只能用于MergeTree系列的表引擎，它可以看作对WHERE的一种优化
  其作用与WHERE相同，均是用来过滤数据。

  • 使用PREWHERE时，首先只读取PREWHERE指定的列字段数据，用于数据过滤的条件判断。
  • 待数据过滤之后再读取SELECT声明的列字段以补全其余属性。

• ClickHouse实现了自动优化的功能，会在条件合适的情况下将WHERE替换为PREWHERE

  • 如果想开启这项特性，需要将optimize_move_to_prewhere设置为1

GROUP BY子句

• 聚合查询

• 能配合WITH ROLLUP、WITHCUBE和WITH TOTALS三种修饰符获取额外的汇总信息

• WITH ROLLUP

  • ROLLUP能够按照聚合键从右向左上卷数据，基于聚合函数依次生成分组小计和总计

• WITH CUBE

  • CUBE会像立方体模型一样，基于聚合键之间所有的组合生成小计信息。如果设聚合键的个数为n，则最终小计组合的个数为2的n次方

• WITH TOTALS

  • 使用TOTALS修饰符后，会基于聚合函数对所有数据进行总计

Having子句

• HAVING子句需要与GROUP BY同时出现，不能单独使用。它能够在聚合计算之后实现二次过滤数据

ORDER BY子句

• ORDER BY子句通过声明排序键来指定查询数据返回时的顺序

LIMIT BY子句

• 运行于ORDER BY之后和LIMIT之前，能够按照指定分组，最多返回前n行数据（如果数据少于n行，则按实际数量返回）

• 常用于TOP N的查询场景。LIMIT BY的常规语法如下

  • LIMIT n BY express

LIMIT子句

• LIMIT子句用于返回指定的前n行数据，常用于分页场景

• 三种语法形式

  • LIMIT n
  • LIMIT n OFFSET m
  • LIMIT m，n

• 使用LIMIT子句时有一点需要注意，如果数据跨越了多个分区，在没有使用ORDER BY指定全局顺序的情况下，每次LIMIT查询所返回的数据有可能不同。如果对数据的返回顺序敏感，则应搭配ORDER BY一同使用。