ClickHouse大数据分析应用

一、序言

面向大数据量查询数据库，优点是在较大数据量（千万级）的前提下具有较好的查询性能。

1、应用场景

ClickHouse应用于OLAP（在线分析处理）领域，具体来说满足如下特点使用此技术比较合适：

• 事务型数据库表通过连表查询转换成宽表
• 聚合（统计）计算使用较多
• 对查询效率要求较高，有限时间范围内能够容忍非幂等性查询（最终一致性）

2、学习姿势

大多数学习ClickHouse是从OLTP数据库开始的，比如Mysql数据库。对于千万级别的数据，以InnoDB为存储引擎的表，仅仅是统计表行数这一需求，执行效率很低，对于一些聚合函数，相应延迟同样无法接受。

提高数据库硬件水平，一定程度上能够改善查询效率问题，但仍然不能彻底解决查询效率问题。ClickHouse一推出就大火更加印证开发者在较大数据量的前提下希望有个合理查询效率的需求是多么的急切。

以典型的Mysql数据库读写分离为例，横向对比ClickHouse，对比Mysql为何查询慢以及ClickHouse为何查询要快，在此基础上综合考虑OLTP如何与OLAP协同工作。

二、知识储备

（一）磁盘IO

1、数据量与查询效率

数据量在超过一定边界后，查询效率急剧下降，造成查询效率低下的主要原因是磁盘IO。比如Mysql数据库，通过服务器优化（增加硬件资源消耗），能够提高一定的性能，并不能从软件层次有效提高查询效率。

千万级别的大表，查询性能较低，主要涉及磁盘这块，影响因素有两条：一是数据索引定位；二是磁盘IO。

（二）性能对比

1、磁盘工作机制

操作系统从磁盘读取数据到内存中，大体经过如下过程：索引到数据存储位置；以页为单位IO数据。其中数据索引完毕，IO过程相对较快（速度与内存IO不是一个数量级）。

磁盘页IO表示在磁盘页上命中一条记录与全部命中，IO时间相同。实际使用过程中，查询一条记录与多条连续记录有时候时间相似（底层逻辑都是从磁盘IO一个磁盘页的数据）。

2、按行（列）存储

通过简单示例比较按行存储与按列存储对查询的影响，主要以磁盘IO最为技术指标。测试数据量为千万级别。

CREATE TABLE `human_name` (
  `id` bigint(20) NOT NULL COMMENT 'ID',
  `name` varchar(32) DEFAULT NULL COMMENT '名称',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `delete_time` datetime DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='人名信息表';

通过不同的场景，对比不同存储方式在磁盘IO上的消耗，进而比较查询效率。

（1）通过id查询name

行存储在索引上节约时间；列存储在磁盘IO上节约时间，数据量较小可以忽略差异，本回合二者持平。

（2）通过批id查询name

批查询是指有限区间查询或者有限集合查询，数据量百条以内。有限区间查询与有限集合查询，对应的数据量较小，性能表现差别不大。仔细分析过程，二者仍然存在明显的差异。

区间查询的效率比有限集合查询效率要高，原因如下：区间查询数据存储是连续的，单次数据索引，单页磁盘IO（数据量较小），紧凑的数据查询，按行存储略占优势，考虑到是查询单个字段，因此磁盘数据索引次数均为一次（按列查询多少列即索引多少次）。

集合查询由于查询条件非连续，需要单独索引并完成磁盘IO，集合中有N个元素（随机）需要索引N次，以页为单位的磁盘IO

（3）通过id查询整行数据

按列存储通常比按行存储的查询效率要高，对于宽表（几十列以上的聚合表），效果更加明显。对于查询，更多的需求是查询某列数据或者某几列数据，按列存储的数据库能够大大减少磁盘数据的扫描范围以及磁盘与内存之间的IO，从IO层面提高了查询效率。

极端情况

数据库存储id和name数据，两者都是非空的必选数据，这种情况下按行（列）存储从IO层面来讲是相似的，数据在磁盘上扫描范围和读写IO差不多。通过id查询name或者批量id查询name，借助于哈希索引，按行存储可能具有O(1)的时间复杂度。

实际数据不可能这么纯粹，行记录通常会有保存时间、修改时间、删除时间、部分核心字段的修改时间，数据量较少时，附属字段对查询的影响较小，一旦数据量超过一定阀值，对查询的影响逐步凸显。按列存储能够忽略附属字段的磁盘扫描与IO。

综合来讲，从查询的角度来讲，按列存储要优于按行存储。

三、基础知识

（一）表结构

clickhouse使用的表结构与常见的关系数据库有一定的区别。

1、排序

在合并树家族引擎中，表排序属性是必选项。通过ORDER BY关键字设置分区内数据的排序策略，数据在导入或者保存时按照排序策略有序存储，有序数据直接存储在磁盘中，查询时具有较高的效率。

排序列也是索引列，高频用作查询条件的字段添加到排序列有利于提高查询效率。

2、主键

主键的定义比较奇怪，仅仅是起到过滤查询索引的作用，没有唯一约束的效果。

当设置有主键时，主键字段必需包含在排序属性中，且从左到右依次展开。

3、默认值

Null类型几乎总是会拖累性能，原因如下：空值无法被索引；需要使用额外的特殊占位符单独处理。按列存储每列数据个数一致有利于数据查询。

数据在导入之前需要做空值处理，将空值替换成与业务无关的数据。

（二）表引擎

clickhouse表引擎非常丰富，其中最常用的是合并树家族引擎。

1、MergeTree

MergeTree引擎能够实现较大数据量的查询需求，由于主键没有唯一索引约束，存在重复行的情况。在数据迁移的过程中，不可避免会出现重复数据导入的情况，业务上能够容忍部分重复数据，或者从应用端处理重复数据，可以选择此引擎。

CREATE TABLE test_tbl (
  id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = MergeTree()
	 PARTITION BY toYYYYMMDD(create_time)
     ORDER BY  (create_time)
     PRIMARY KEY (id)
     TTL create_time + INTERVAL 1 MONTH
     SETTINGS index_granularity=8192;

MergeTree引擎必需指定排序字段。

MergeTree的主键的作用是加速查询，不是类似MySQL保持记录唯一。

2、ReplacingMergeTree

ReplacingMergeTree引擎用来去除重复行，此处的去重有三个层次的含义：在分区内去重；以主键字段为比较对象；数据去重实践只会在合并时发生。

-- 强制后台合并，去重时所在表停止服务
optimize table test_tbl_replacing final;

ReplacingMergeTree提供了主键去重的能力，但是仍旧有以下限制：

• optimize是后台动作，无法预测具体执行时间点；
• 在没有彻底optimize之前，不能确定是否仍有重复数据；
• 手动执行optimize在海量数据场景下要消耗大量时间，无法满足业务即时查询的需求；
• 在分布式场景下，相同primary key的数据可能被sharding到不同节点上，不同shard间可能无法去重；

ReplacingMergeTree更多用于确保数据最终被去重，无法保证查询过程中主键不重复。

ReplacingMergeTree(create_time)填入参数为版本字段，重复记录保留版本号最大最在行；允许为空，默认保留重复行最后插入的记录。

去重深刻理解

这里的去重并不能达到关系型数据库严格意义去重的目的，使用时需要注意这个现象。另外不能以非黑即白的想法考虑这个问题，ClickHouse在提高查询速度时做了一定的妥协。

3、SummingMergeTree

SummingMergeTree提供的是一种预聚合引擎，等效为以order by字段为单位分组，然后执行聚合求和操作，不过这些结果是提前计算好了的，查询时不需要实时计算。

如果聚合的值不满足要求，可以在查询结果集上通过聚合函数再次聚合，此时属于实时计算。

（三）内置函数

常见的内置函数需要特别指出，新建表模式、数据导入等方面会有应用。

1、格式化日期

格式化分区函数常用于表的分区设置，以天为单位的分区是常见的分区设置。

select toYYYYMMDD(now())

2、哈希函数

以name字段的哈希字符串作为分区策略。

CREATE TABLE default.test02 (
`id` UInt16,
`name` String,
`create_time` Date) ENGINE = MergeTree() 
PARTITION BY LOWER(hex(MD5(name))) 
PRIMARY KEY id
ORDER BY (id,create_time);

表可以不设置主键，一旦设置主键，那么表必选排序属性必需以主键的顺序依次展开。

直接用原始字符串字段值作为分区策略也是可行的，考虑到字符串的值域范围比较广，用哈希函数处理会比较安全。

3、日期函数

获取各种日期函数，如果不指定时区，默认读取宿主机的时区信息。

SELECT toDateTime(now()) AS t1,
       toDate(now()) AS t2,
       toDate(now(), 'Asia/Shanghai') AS t3,
       toString(now()) AS t4

四、安装与配置

版本选择长期支持版本20.8，采用手动安装的方式进行。

（一）安装

rpm -ivh clickhouse-server-20.8.19.4-2.noarch.rpm
rpm -ivh clickhouse-client-20.8.19.4-2.noarch.rpm
rpm -ivh clickhouse-common-static-20.8.19.4-2.x86_64.rpm

（二）配置

1、正则替换注释

使用模式<!-[\s\S]*?->$查询配置XML配置文件中所有注释。

# 格式化XML文件
xmllint --format config.xml 

2、服务端配置文件

服务端配置文件有两个config.xml和users.xml，前者是只读配置，后者可以在运行时动态修改。

五、小结

ClickHouse生态在快速迭代，很多亮眼的功能尚处于开发中或者测试中，这里选取部分特性与大家分享。