OLTP

   全称 OnLine Transaction Processing，联机事务处理系统, 就是对数据的增删改查等操作。存储的是业务数据，来记录某类业务事件的发生，比如下单、支付、注册、等等。
   典型代表有Mysql、 Oracle等数据库，对应的网站、系统应用后端数据库 针对事务进行操作，对响应时间要求高，面向前台应用的，应用比较简单，数据量相对较少，是GB级别的。
   面向群体：业务人员

OLAP

   当数据积累到一定的程度，需要对过去发生的事情做一个总结分析时，就需要把过去一段时间内产生的数据拿出来进行统计分析，从中获取想要的信息，为公司做决策提供支持，这个就是做OLAP了。
   OnLine Analytical Processing，联机分析处理系统,存储的是历史数据，对应的风控平台、BI平台、数据可视化等系统就属于OLAP。
   OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策，并且提供直观易懂的查询结果
   典型代表有 Hive、ClickHouse
   针对基于查询的分析系统，基础数据来源于生产系统中的操作数据，数据量非常大，常规是TB级别的
   面向群体：分析决策人员

OLAP场景的关键特征

   基本业务量到达这个分库分表量级，则离不开数据大屏、推荐系统、画像系统等搭建
   需要搭建上面的系统，则离不开海量数据进行存储-分析-统计
   海量数据下 TB、PB级别数据存储，靠Mysql进行存储-分析-统计无疑是灾难

• 绝大多数是读请求
• 数据以相当大的批次(> 1000行)更新，而不是单行更新;或者根本没有更新。
• 已添加到数据库的数据不能修改。
• 对于读取，从数据库中提取相当多的行，但只提取列的一小部分。
• 宽表，即每个表包含着大量的列
• 查询相对较少(通常每台服务器每秒查询数百次或更少)
• 对于简单查询，允许延迟大约50毫秒
• 列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)
• 处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)
• 事务不是必须的
• 对数据一致性要求低
• 每个查询有一个大表。除了他以外，其他的都很小。
• 查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中

   很容易可以看出，OLAP场景与其他通常业务场景(例如,OLTP或K/V)有很大的不同， 因此想要使用OLTP或Key-Value数据库去高效的处理分析查询场景，并不是非常完美的适用方案。例如，使用OLAP数据库去处理分析请求通常要优于使用MongoDB或Redis去处理分析请求。

列式数据库更适合OLAP场景的原因
   列式数据库更适合于OLAP场景(对于大多数查询而言，处理速度至少提高了100倍)，下面详细解释了原因(通过图片更有利于直观理解)：

行式

列式

行式存储

   传统的OLTP关系型数据库 都是行存储。
   一行中的数据在存储介质中以连续存储形式存在。
   适合随机的增删改查操作，或者在行中选取所有属性的查询操作，结合索引提升性能。

缺点
   查询的全部记录的某几个字段，但由于这些字段在各行数据单元中，而整个行特别大(字段特别多)，程序必须不断读取每一条的行记录取对应的字段，使得读取效率大大降低。

例子
   找某个订单的话，就很方便，订单的全部信息都能获取
   但找全部订单总金额就需要遍历多个数据

列式存储

   存储结构化数据时，在底层的存储介质上，数据是以列的方式来组织
   存储完若干条记录的首个字段后，再存储这些记录的第二个字段，然后再第三个字段、第四个字段...
   查询时只有涉及到的列会被读取，而不需要全表扫描，可以显著减少IO消耗，并降低查询响应时间

缺点
   列存储数据更新成本较高，一般适合读多写少的场景，适合 OLAP 分析型系统。

例子
   查询全部成交额，只需要把订单金额拿出来即可

行/列存储对比

• 行/列存储描述的是底层存储介质上，数据的组织形式，哪种组织对应哪种业务需求
• 列存储在聚合、统计等操作性能会优于行存储
• 列存储将多行记录的列连续存储在一起，一列接着一列
• 列式存储是同个数据类型，会进行数据压缩率更高，更省空间
• 列存储数据更新成本较高，一般适合读多写少的场景，适合 OLAP 分析型系统

ClickHouse

特点
• 不依赖Hadoop 生态、安装和维护简单
• 擅长对列的聚合、计数等统计操作性能强劲
• 对列存储和压缩的采用更好的算法，更好节省成本
• 拓展性强，在生产中经过实战测试，从单服务器部署到具有数千个节点的集群的线性水平可扩展性
• 具有企业级安全功能和故障安全机制，可防止数据因应用程序错误和人为错误而损坏
• 支持主流的大部分SQL语法和函数
• 吞吐能力强，官方测试支持，支持多种存储引擎，满足多数业务场景

应用场景
• 互联网电商
• 在线教育
• 金融等领域用
• 户行为数据记录和分析
• 搭建数据可视化平台

性能对比

有谁在用
• 国内：阿里、字节、腾讯 、虎牙、青云、新浪等
• 国外：优步、Ebay、Spotify、思科、等

支持多种部署方式
• Linux：源码部署、Docker部署
• Window：安装Docker、安装Linux虚拟机

Docker安装

# 安装并运行Docker。
yum install docker-io -y
systemctl start docker

# 检查安装结果。
docker info

# 启动使用Docker
systemctl start docker     #运行Docker守护进程
systemctl stop docker      #停止Docker守护进程
systemctl restart docker   #重启Docker守护进程

# 修改镜像仓库
vim /etc/docker/daemon.json
{
  "registry-mirrors": ["https://dockerpull.com"]
}

器容器化部署ClickHouse

端口

   默认http端口是8123
   tcp端口是9000
   同步端口9009

web可视化界面
   http://192.168.10.63:8123/play

命令
   默认数据库最初为空，用于执行未指定数据库的命令

强烈不推荐

• 在任何其他情况下不能将ClickHouse服务器暴露给公共互联网
• ClickHouse体验平台界面实际上是通过ClickHouse 的HTTP API接口实现的
• 确保它只在私有网络上侦听，并由正确配置的防火墙监控。

docker部署ClickHouse

mkdir -pv /mydata/docker/clickhouse/log
mkdir -pv /mydata/docker/clickhouse/data

docker run -d --name clickhouse --ulimit nofile=262144:262144 \
-p 8123:8123 -p 9000:9000 -p 9009:9009 --privileged=true \
-v /mydata/docker/clickhouse/log:/var/log/clickhouse-server \
-v /mydata/docker/clickhouse/data:/var/lib/clickhouse clickhouse/clickhouse-server:22.2.3.5

数据库可视化工具DBeaver连接ClickHouse

   可视化工具DBeaver下载Windows (zip)

创建数据库和表

### 创建数据库 soulboy_shop
CREATE DATABASE soulboy_shop

### 创建表 clickstream
# ClickHouse 有自己的数据类型，每个表都必须指定一个Engine引擎属性来确定要创建的表的类型
# 引擎决定了数据的存储方式和存储位置、支持哪些查询、对并发的支持

CREATE TABLE soulboy_shop.clickstream (
    customer_id String, 
    time_stamp Date, 
    click_event_type String,
    page_code FixedString(20),  
    source_id UInt64,
    money Decimal(2,1),
    is_new Bool
) 
ENGINE = MergeTree()
ORDER BY (time_stamp)

### 删除表
DROP TABLE IF EXISTS soulboy_shop.clickstream

### 查看表结构
DESCRIBE soulboy_shop.clickstream

### 插入数据
INSERT INTO soulboy_shop.clickstream
VALUES ('customer1', '2021-10-02', 'add_to_cart', 'home_enter', 568239, 3.8, 1) 

### 查询数据
SELECT * FROM soulboy_shop.clickstream

### 条件查询数据
SELECT * FROM soulboy_shop.clickstream WHERE time_stamp >= '2001-11-01'

### 更新和删除 
在OLAP数据库中，可变数据（Mutable data）通常是不被欢迎的，早期ClickHouse是不支持，后来版本才有
不支持事务，建议批量操作，不要高频率小数据量更新删除
删除和更新是一个异步操作的过程，语句提交立刻返回，但不一定已经完成了

# 判断是否完成（is_done字段为1代表sql语句执行完成）   
SELECT database, table, command, create_time, is_done FROM system.mutations LIMIT 20

# 更新
ALTER TABLE soulboy_shop.clickstream UPDATE click_event_type = 'pay' where customer_id = 'customer1'; 

# 删除
ALTER TABLE soulboy_shop.clickstream delete where customer_id = 'customer2';

数据类型

• ASCII码：一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间
• UTF-8编码：一个英文字符等于一个字节，一个中文（含繁体）等于三个字节

查看所有数据类型

• 不同CH版本存在不一样的数据类型，毕竟是火热项目，有些尽管新增但是也很少没用上,记住常用的即可
• case_insensitive 选项为1 表示大小写不敏感，字段类型不区分大小写
• 为0 表示大小写敏感，即字段类型需要严格区分大小写

select * from system.data_type_families

##################### 整型 
### 有符号整型范围
Int8 — [-128 : 127]

Int16 — [-32768 : 32767]

Int32 — [-2147483648 : 2147483647]

Int64 — [-9223372036854775808 : 9223372036854775807]

Int128 — [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]

Int256 — [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]

### 无符号整型范围
UInt8 — [0 : 255]

UInt16 — [0 : 65535]

UInt32 — [0 : 4294967295]

UInt64 — [0 : 18446744073709551615]

UInt128 — [0 : 340282366920938463463374607431768211455]

UInt256 — [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935]


### Decimal类型
例子：Decimal(10,2) 小数部分2位，整数部分 8位（10-2)
整数部分超过了会报错，小数部分超过了会截取丢失


CREATE TABLE soulboy_shop.clickstream1 (
    customer_id String, 
    time_stamp Date, 
    click_event_type String,
    page_code FixedString(20),  
    source_id UInt64,
    money Decimal(2,1)
) 
ENGINE = MergeTree()
ORDER BY (time_stamp)


INSERT INTO soulboy_shop.clickstream1
VALUES ('customer1', '2021-10-02', 'add_to_cart', 'home_enter', 568239,2.11 ) 


### Decimal类型
CREATE TABLE soulboy_shop.clickstream1 (
    customer_id String, 
    time_stamp Date, 
    click_event_type String,
    page_code FixedString(20),  
    source_id UInt64,
    money Decimal(2,1)
) 
ENGINE = MergeTree()
ORDER BY (time_stamp)

INSERT INTO soulboy_shop.clickstream1
VALUES ('customer1', '2021-10-02', 'add_to_cart', 'home_enter', 568239,2.11 ) 

### 字符串类型
通用唯一标识符(UUID)是由一组32位数的16进制数字所构成，用于标识记录

# 建表和插入例子
CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLog
INSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'

### 枚举类型
包括 Enum8 和 Enum16 类型，Enum 保存 'string'= integer 的对应关系
在 ClickHouse 中，尽管用户使用的是字符串常量，但所有含有 Enum 数据 类型的操作都是按照包含整数的值来执行。这在性能方面比使用 String 数据类型更有效。

Enum8 用 'String'= Int8 对描述。
Enum16 用 'String'= Int16 对描述。

# 创建一个带有一个枚举 Enum8('home' = 1, 'detail' = 2, 'pay'=3) 类型的列
CREATE TABLE t_enum
(
    page_code Enum8('home' = 1, 'detail' = 2,'pay'=3)
)
ENGINE = TinyLog
插入, page_code 这列只能存储类型定义中列出的值：'home'或`'detail' 或 'pay'。如果您尝试保存任何其他值，ClickHouse 抛出异常

# 插入成功
INSERT INTO t_enum VALUES ('home'), ('detail')

# 插入报错
INSERT INTO t_enum VALUES ('home1')

# 查询
SELECT * FROM t_enum

mysql数据类型对比

常见SQL语法

   ClickHouse语法和常规SQL语法类似，多数都是支持的

注意事项

   每次更新或者删除，会废弃目标数据的原有分区，而重建新分区
   如果只更新一条数据，那么需要重建一个分区，开销大
   如果更新100条数据，而这100条可能落在3个分区上，则需重建3个分区
   相对来说一次更新一批数据的整体效率远高于一次更新一行

### 创建数据库 soulboy_shop
CREATE DATABASE soulboy_shop

### 创建表 clickstream
# ClickHouse 有自己的数据类型，每个表都必须指定一个Engine引擎属性来确定要创建的表的类型
# 引擎决定了数据的存储方式和存储位置、支持哪些查询、对并发的支持

CREATE TABLE soulboy_shop.clickstream (
    customer_id String, 
    time_stamp Date, 
    click_event_type String,
    page_code FixedString(20),  
    source_id UInt64,
    money Decimal(2,1),
    is_new Bool
) 
ENGINE = MergeTree()
ORDER BY (time_stamp)

### 删除表
DROP TABLE IF EXISTS soulboy_shop.clickstream

### 查看表结构
DESCRIBE soulboy_shop.clickstream

### 插入数据
INSERT INTO soulboy_shop.clickstream
VALUES ('customer1', '2021-10-02', 'add_to_cart', 'home_enter', 568239, 3.8, 1) 

### 查询数据
SELECT * FROM soulboy_shop.clickstream

### 条件查询数据
SELECT * FROM soulboy_shop.clickstream WHERE time_stamp >= '2001-11-01'

### 更新和删除 
在OLAP数据库中，可变数据（Mutable data）通常是不被欢迎的，早期ClickHouse是不支持，后来版本才有
不支持事务，建议批量操作，不要高频率小数据量更新删除
删除和更新是一个异步操作的过程，语句提交立刻返回，但不一定已经完成了

# 判断sql语句是否执行完成（is_done字段为1代表sql语句执行完成）   
SELECT database, table, command, create_time, is_done FROM system.mutations LIMIT 20

# 更新
ALTER TABLE soulboy_shop.clickstream UPDATE click_event_type = 'pay' where customer_id = 'customer1'; 

# 删除
ALTER TABLE soulboy_shop.clickstream delete where customer_id = 'customer2';

分片-分区-副本

分区

   分区是表的分区，把一张表的数据分成N多个区块，分区后的表还是一张表，数据处理还是由自己来完成

   PARTITION BY，指的是一个表按照某一列数据（比如日期）进行分区，不同分区的数据会写入不同的文件中

   建表时加入partition概念，可以按照对应的分区字段，允许查询在指定了分区键的查询，减少表的扫描范围，提升性能

create table soulboy_shop.order_merge_tree( 
    id UInt32,
    sku_id String,
    out_trade_no String,
    total_amount Decimal(16,2), 
    create_time Datetime
) engine =MergeTree()
  partition by toYYYYMMDD(create_time) 
  order by (id,sku_id)
  primary key (id);

分片

   shard分片是把数据库横向扩展（Scale Out）到多个物理节点上的一种有效的方式

   复用了数据库的分区概念，相当于在原有的分区下作为第二层分区，ClickHouse会将数据分为多个分片，并且分布到不同节点上，再通过 Distributed 表引擎把数据拼接起来一同使用

   Sharding机制使得ClickHouse可以横向线性拓展，构建大规模分布式集群，但需要避免数据倾斜问题(热点数据)

副本

   两个相同数据的表, 作 用是为了数据备份与安全，保障数据的高可用性，

   即使一台 ClickHouse 节点宕机，那么也可以从其他服务器获得相同的数据

   类似Mysql主从架构，主节点宕机，从节点也能提供服务

表引擎

Mysql的表引擎

ClickHouse提供了多种的表引擎，不同的表引擎也代表有不同的功能

• 数据的存储方式和位置，写到哪里以及从哪里读取数据
• 支持哪些查询以及如何支持。
• 并发数据访问。
• 索引的使用（如果存在）。
• 是否可以执行多线程请求。
• 数据复制参数

Log系列

   最小功能的轻量级引擎，当需要快速写入许多小表并在以后整体读取它们时效果最佳，一次写入多次查询

• TinyLog
• StripLog
• Log

MergeTree系列

   ClickHouse最强大的表引擎，有多个不同的种类
   适用于高负载任务的最通用和功能最强大的表引擎，可以快速插入数据并进行后续的后台数据处理
   支持主键索引、数据分区、数据副本等功能特性和一些其他引擎不支持的其他功能

• MergeTree、ReplacingMergeTree
• SummingMergeTree、AggregatingMergeTree
• CollapsingMergeTree、VersionedCollapsingMergeTree、GraphiteMergeTree

外部存储引擎系列

   能够直接从其它的存储系统读取数据，例如直接读取 HDFS 的文件或者 MySQL 数据库的表，这些表引擎只负责元数据管理和数据查询

• HDFS、Mysql
• Kafka、JDBC

Memory引擎

   原生数据直接存储内存，性能高，重启则消失，读写不会阻塞，不支持索引，主要是测试使用

Distributed引擎

   分布式引擎本身不存储数据, 但可以在多个服务器上进行分布式查询。 读是自动并行的。读取时，远程服务器表的索引（如果有的话）会被使用

File引擎

   数据源是以 Clickhouse 支持的一种输入格式（TabSeparated，Native等）存储数据的文件
   从 ClickHouse 导出数据到文件,将数据从一种格式转换为另一种格式

Merge引擎

   Merge 引擎 (不要跟 MergeTree 引擎混淆) 本身不存储数据，但可用于同时从任意多个其他的表中读取数据。读是自动并行的，不支持写入(读取时)，那些被真正读取到数据的表的索引（如果有的话）会被使用

MergeTree

• 中文名叫合并树，MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。
• 数据可以以【数据片段】的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。
• 相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多
• 这种数据片段反复合并的特性，也正是合并树名称的由来

MergeTree特点

语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

合并树MergeTree建表和数据实操
   新的数据写入会有临时分区产生，不加入已有分区

   写入完成后经过一定时间（10到15分钟），ClickHouse会自动化执行合并操作，将临时分区的数据合并到已有分区当中

   optimize的合并操作是在后台执行的，无法预测具体执行时间点，除非是手动执行

optimize table xxx final;
optimize table soulboy_shop.order_merge_tree final;

   在数据量比较大的情况，尽量不要使用该命令，执行optimize要消耗大量时间

###  创建表 order_merge_tree
create table soulboy_shop.order_merge_tree( 
    id UInt32,
    sku_id String,
    out_trade_no String,
    total_amount Decimal(16,2), 
    create_time Datetime
) engine =MergeTree()
  order by (id,sku_id)
  partition by toYYYYMMDD(create_time) 
  primary key (id);

### 查看表结构
DESCRIBE soulboy_shop.order_merge_tree

### 表数据写入
* 查询
* 再插入
* 查询，有多个分区
* 合并
* 查询，分区减少

insert into soulboy_shop.order_merge_tree values 
(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
(2,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
(3,'sku_3','542323',55.02,'2023-03-01 18:00:00'), 
(4,'sku_1','54222',2000.3,'2023-04-01 19:00:00'), 
(5,'sku_2','53423',120.2,'2023-04-01 19:00:00'), 
(6,'sku_4','65432',600.01,'2023-04-02 11:00:00');

# 分区合并验证
docker exec -it clickhouse /bin/bash
clickhouse-client
491cfd7182f5 :) SELECT * FROM soulboy_shop.order_merge_tree

SELECT *
FROM soulboy_shop.order_merge_tree

Query id: 839b0acd-48d1-4689-8956-9ea362d4bd13

┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  1 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
│  2 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
│  3 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  4 │ sku_1  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
│  5 │ sku_2  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  6 │ sku_4  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘

ReplacingMergeTree

   MergeTree的拓展，该引擎和 MergeTree 的不同之处在它会删除【排序键值】相同重复项,根据OrderBy字段

   数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。

   有一些数据可能仍未被处理，尽管可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写

   因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现

   注意去重访问：如果是有多个分区表，只在分区内部进行去重，不会跨分区

语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

去重合并树ReplaceMergeTree建表实操

   建表：ver表示的列只能是UInt*,Date和DateTime 类型

   删除策略：ReplacingMergeTree() 填入的参数为版本字段，重复数据就会保留版本字段值最大的。 如果不填写版本字段，默认保留插入顺序的最后一条数据

### 创建表 order_relace_merge_tree
create table soulboy_shop.order_relace_merge_tree( 
    id UInt32,
    sku_id String,
    out_trade_no String,
    total_amount Decimal(16,2), 
    create_time Datetime
) engine =ReplacingMergeTree(id)
  order by (sku_id)
  partition by toYYYYMMDD(create_time) 
  primary key (sku_id);

 insert into soulboy_shop.order_relace_merge_tree values 
(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
(2,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
(3,'sku_3','542323',55.02,'2023-03-01 18:00:00'), 
(4,'sku_5','54222',2000.3,'2023-04-01 19:00:00'), 
(5,'sku_6','53423',120.2,'2023-04-01 19:00:00'), 
(6,'sku_7','65432',600.01,'2023-04-02 11:00:00');

insert into soulboy_shop.order_relace_merge_tree values 
(11,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
(21,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
(31,'sku_3','542323',55.02,'2023-03-01 18:00:00'), 
(41,'sku_5','54222',2000.3,'2023-04-01 19:00:00'), 
(51,'sku_8','53423',120.2,'2023-04-01 19:00:00'), 
(61,'sku_9','65432',600.01,'2023-04-02 11:00:00');

SELECT * FROM soulboy_shop.order_relace_merge_tree;


┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  1 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
│  2 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
│  3 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│ 11 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
│ 21 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
│ 31 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  4 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
│  5 │ sku_6  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│ 41 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
│ 51 │ sku_8  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  6 │ sku_7  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│ 61 │ sku_9  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘

# 手工合并
optimize table soulboy_shop.order_relace_merge_tree final;

SELECT * FROM soulboy_shop.order_relace_merge_tree;

┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│ 11 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
│ 21 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
│ 31 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│ 41 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
│  5 │ sku_6  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
│ 51 │ sku_8  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  6 │ sku_7  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
│ 61 │ sku_9  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘

SummingMergeTree

   聚合引擎SummingMergeTree，该引擎继承自 MergeTree，区别在于，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有 相同OrderBy排序键 的行合并为一行，该行包含了被合并的行中具有数值类型的列的汇总值。

   类似group by后使用聚合函数的效果，这个可以显著的减少存储空间并加快数据查询的速度

   推荐将该引擎和 MergeTree 一起使用。例如在准备做数据报表的时候，将完整的数据存储在 MergeTree 表中，并且使用SummingMergeTree 来存储聚合数据，这样做可以避免因为使用不正确的 排序健组合方式而丢失有价值的数据

   只需要查询汇总结果，不关心明细数据

   设计聚合统计表，字段全部是维度（商品）、度量（销量）或者时间戳即可，非相关的字段可以不添加

   获取汇总值，不能直接 select 对应的字段，而需要使用 sum 进行聚合，因为自动合并的部分可能没进行，会导致一些还没来得及聚合的临时明细数据少

   在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分区下的【相同排序】的多行数据汇总合并成一行，既减少了数据行节省空间，又降低了后续汇总查询的开销

语法

(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = SummingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

聚合引擎SummingMergeTree建表实操

SummingMergeTree 的参数

• columns 包含了将要被汇总的列的列名的元组。可选参数。
• 所选的【列必须是数值类型】，具有 相同OrderBy排序键 的行合并为一行
• 如果没有指定 columns，ClickHouse 会把非维度列且是【数值类型的列】都进行汇总

### 创建表 order_summing_merge_tree
create table soulboy_shop.order_summing_merge_tree( 
    id UInt32,
    sku_id String,
    out_trade_no String,
    total_amount Decimal(16,2), 
    create_time Datetime
) engine =SummingMergeTree(total_amount)
  order by (id,sku_id)
  partition by toYYYYMMDD(create_time) 
  primary key (id);



### 插入数据
insert into soulboy_shop.order_summing_merge_tree values 
(1,'sku_1','aabbcc',5600.00,'2023-03-01 16:00:00') ,
(2,'sku_2','23241',4.02,'2023-03-01 17:00:00'),
(3,'sku_3','542323',55.02,'2023-03-01 18:00:00'), 
(4,'sku_5','54222',2000.3,'2023-04-01 19:00:00'), 
(5,'sku_6','53423',120.2,'2023-04-01 19:00:00'), 
(6,'sku_7','65432',600.01,'2023-04-02 11:00:00');

### 第一次查询
491cfd7182f5 :) SELECT * FROM soulboy_shop.order_summing_merge_tree;

SELECT *
FROM soulboy_shop.order_summing_merge_tree

Query id: 1d03663c-4e48-4a58-b5b8-4196418bce35

┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  1 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
│  2 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
│  3 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  4 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
│  5 │ sku_6  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  6 │ sku_7  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘

### 二次插入数据
insert into soulboy_shop.order_summing_merge_tree values 
(1,'sku_1','aabbccbb',5600.00,'2023-03-01 23:09:00')
insert into soulboy_shop.order_summing_merge_tree values 
(1,'sku_1','aabbccbb',5600.00,'2023-03-01 23:09:00')

### 二次查询
491cfd7182f5 :) SELECT * FROM soulboy_shop.order_summing_merge_tree;

SELECT *
FROM soulboy_shop.order_summing_merge_tree

Query id: 43711bf8-6a7f-4da0-bfbc-0c05882342ec

┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  1 │ sku_1  │ aabbcc       │         5600 │ 2023-03-01 16:00:00 │
│  2 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
│  3 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  1 │ sku_1  │ aabbccbb     │         5600 │ 2023-03-01 23:09:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  1 │ sku_1  │ aabbccbb     │         5600 │ 2023-03-01 23:09:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  4 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
│  5 │ sku_6  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  6 │ sku_7  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘

### 手工合并数据
optimize table soulboy_shop.order_summing_merge_tree final;

### 三次查询数据（发现已合并 sku_1  16800 ）
491cfd7182f5 :) SELECT * FROM soulboy_shop.order_summing_merge_tree; 0J9 KI8M

SELECT *
FROM soulboy_shop.order_summing_merge_tree

Query id: a95f4507-20ca-4771-a186-8a5e46328e82

┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  1 │ sku_1  │ aabbcc       │        16800 │ 2023-03-01 16:00:00 │
│  2 │ sku_2  │ 23241        │         4.02 │ 2023-03-01 17:00:00 │
│  3 │ sku_3  │ 542323       │        55.02 │ 2023-03-01 18:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  4 │ sku_5  │ 54222        │       2000.3 │ 2023-04-01 19:00:00 │
│  5 │ sku_6  │ 53423        │        120.2 │ 2023-04-01 19:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no─┬─total_amount─┬─────────create_time─┐
│  6 │ sku_7  │ 65432        │       600.01 │ 2023-04-02 11:00:00 │
└────┴────────┴──────────────┴──────────────┴─────────────────────┘

### 查询汇总数据（查询时聚合）
select sku_id,sum(total_amount) from soulboy_shop.order_summing_merge_tree group by sku_id

ClickHouse高可用集群分片-副本

ClickHouse的副本

• 两个相同数据的表, 作用是为了数据备份与安全，保障数据的高可用性
• 副本是表级别的，不是整个服务器级的，服务器里可以同时有复制表和非复制表。
• 副本不依赖分片，每个分片有它自己的独立副本。

副本写入流程

• 复制是多主异步
• 数据会先插入到执行该语句的服务器上，然后被复制到其他服务器。
• 由于它是异步的，在其他副本上最近插入的数据会有一些延迟。
  • 如果部分副本不可用，则数据在其可用时再写入
  • 副本可用的情况下，则【延迟时长】是通过网络传输、压缩数据块所需的时间。
• 默认情况下，INSERT 语句仅等待一个副本写入成功后返回，如果数据只成功写入一个副本，而后该副本所在的服务器不再存在，则存储的数据会丢失
• 要启用数据写入多个副本才确认返回，使用 insert_quorum 进行配置，但是会影响性能
• 对于 INSERT 和 ALTER 语句操作数据的会在压缩的情况下被复制，而 CREATE，DROP，ATTACH，DETACH 和 RENAME 语句只会在单个服务器上执行，不会被复制。

ReplicatedMergeTree(副本合并树表引擎)

• 如果有两个副本的话,相当于分布在两台clickhosue节点中的两个表
• 这个两个表具有协调功能, 无论是哪个表执行insert或者alter操作,都会同步到另外一张表，副本就是相互同步数据的表
• 副本同步需要借助zookeeper实现数据的同步, 副本节点会在zk上进行监听，但数据同步过程是不经过zk的
• zookeeper要求3.4.5以及以上版本

语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = ReplicatedMergeTree('{zoo_path}', '{replica_name}')
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]


### 示例

CREATE TABLE tb_order
(
    EventDate DateTime,
    CounterID UInt32,
    UserID UInt32
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/01/tb_order', 'tb_order_01')
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)

只有MergeTree 系列里的表可支持副本

ClickHouse高可用架构准备

   高可用架构需要ZooKeeper
   副本同步需要借助ZooKeeper实现数据的同步，副本节点会在ZooKeeper上进行监听，但数据同步过程是不经过ZooKeeper的

环境准备

Docker部署ZooKeeper

docker run -d --name zookeeper -p 2181:2181 -t zookeeper:3.7.0

RPM包部署ClickHouse(版本：ClickHouse 22.1.2.2)

安装ClickHouse 22.1.2.2

# 配置DNS域名解析
vim /etc/hosts
192.168.10.63   ClickHouse
192.168.10.64   ClickHouse2

# 在2个节点分别上传rpm包至如下路径
[root@ClickHouse tmp]# pwd
/tmp
[root@ClickHouse tmp]# ls
clickhouse-client-22.1.2.2-2.noarch.rpm
clickhouse-common-static-22.1.2.2-2.x86_64.rpm
clickhouse-common-static-dbg-22.1.2.2-2.x86_64.rpm
clickhouse-server-22.1.2.2-2.noarch.rpm

# 安装（默认回车）
[root@ClickHouse tmp]# rpm -ivh *.rpm

# 启动
systemctl start clickhouse-server

# 停止
systemctl stop clickhouse-server

# 重启
systemctl restart clickhouse-server

# 状态查看
sudo systemctl status clickhouse-server

# 查看端口占用，如果命令不存在 yum install -y lsof
lsof -i :8123

# 关闭防火墙
systemctl stop firewalld

# 查看日志 、错误日志
tail -f /var/log/clickhouse-server/clickhouse-server.log
tail -f /var/log/clickhouse-server/clickhouse-server.err.log

# 开启远程访问，取消下面的注释
vim /etc/clickhouse-server/config.xml

# 编辑配置文件
<listen_host>0.0.0.0</listen_host>

# 重启
systemctl restart clickhouse-server

ClickHouse可视化工具连接

ClickHouse副本配置实操

【每个节点】都需要配置

# 进入配置目录
cd /etc/clickhouse-server

# 编辑配置文件（找到zookeeper节点，增加下面的，如果有多个zookeeper则按照顺序加即可）
vim /etc/clickhouse-server/config.xml
<zookeeper>
          <node>
                <host>192.168.10.60</host>
                <port>2181</port>
          </node>
</zookeeper>

# 重启
systemctl restart clickhouse-server

# 状态查看
sudo systemctl status clickhouse-server

# 查看端口占用，如果命令不存在 yum install -y lsof
lsof -i :8123

# 查看日志 、错误日志
tail -f /var/log/clickhouse-server/clickhouse-server.log
tail -f /var/log/clickhouse-server/clickhouse-server.err.log

【每个机器】上创建表
    副本只能同步数据，不能同步表结构，需要在每台机器上手动建表

# 节点一（192.168.10.63），zookeeper路径一致，副本名称不一样
CREATE TABLE tb_product
(
    userId UInt32
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/1/tb_product', 'product-replica-1')
ORDER BY (userId)

# 节点二（192.168.10.64），zookeeper路径一致，副本名称不一样
CREATE TABLE tb_product
(
    userId UInt32
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/1/tb_product', 'product-replica-2')
ORDER BY (userId)

# 查询zk配置
select * from system.zookeeper where path='/';

ClickHouse2 :) select * from system.zookeeper where path='/'

Query id: c0f47288-9774-4bae-943e-64e9c210d22c

┌─name───────┬─value─┬─czxid─┬─mzxid─┬───────────────ctime─┬───────────────mtime─┬─version─┬─cversion─┬─aversion─┬─ephemeralOwner─┬─dataLength─┬─numChildren─┬─pzxid─┬─path─┐
│ zookeeper  │       │     0 │     0 │ 1970-01-01 08:00:00 │ 1970-01-01 08:00:00 │       0 │       -2 │        0 │              0 │          0 │           2 │     0 │ /    │
│ clickhouse │       │     4 │     4 │ 2024-10-04 13:22:30 │ 2024-10-04 13:22:30 │       0 │        2 │        0 │              0 │          0 │           2 │    27 │ /    │
└────────────┴───────┴───────┴───────┴─────────────────────┴─────────────────────┴─────────┴──────────┴──────────┴────────────────┴────────────┴─────────────┴───────┴──────┘

【任意节点】插入和查询数据
   在任意节点插入数据，在另外一个节点观察数据是否可以同步

# ClickHouse1插入数据
clickhouse-client --host=192.168.10.64
INSERT into tb_product values(1),(2),(3)

# ClickHouse2查询数据
clickhouse-client --host=192.168.10.64
ClickHouse2 :) select * from tb_product

SELECT *
FROM tb_product

Query id: df0236ee-b358-44fc-b93f-7d1048e6f3dc

┌─userId─┐
│      1 │
│      2 │
│      3 │
└────────┘

高可用架构之分片和Distributed表引擎

什么是ClickHouse的副本

   两个相同数据的表, 作 用是为了数据备份与安全，保障数据的高可用性，

什么是ClickHouse的分片

• 数据分片-允许多台机器/节点同并行执行查询，实现了分布式并行计算
• 分片间的数据是不同的，不同的服务器存储同一张表的不同部分,作用是为了水平切分表,缓解单节点的压力

分布式表引擎Distributed

   Distributed表引擎主要是用于分布式，自身不存储任何数据，数据都分散存储在某一个分片上，能够自动路由数据至集群中的各个节点，需要和其他数据表引擎一起协同工作

   一张分布式表底层会对应多个分片数据表，由具体的分片表存储数据，分布式表与分片表是一对多的关系

   分布式表主要有本地表（xxx_local）和分布式表（xxx_all）两部分组成

语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE = Distributed(cluster, database, table[, sharding_key[, policy_name]])
[SETTINGS name=value, ...]

多分片表查询
   本着谁执行谁负责的原则，向ClickHouse发起分布式表执行SELECT * FROM distributed_table

   它会转为如下形式SELECT * FROM local_table，先执行本地分片，再发送远端各分片执行

   最后合并结果为临时表返回

分片配置详解 config.xml
   /etc/clickhouse-server/config.xml

<remote_servers>
    <logs>
        <shard>
            <!-- 可选的。写数据时分片权重。 默认: 1. -->
            <weight>1</weight>
            <!-- 可选的。是否只将数据写入其中一个副本。默认值:false(将数据写入所有副本)。 -->
            <internal_replication>false</internal_replication>
            
            <replica>
<!-- 可选的。负载均衡副本的优先级，请参见（load_balancing 设置)。默认值:1(值越小优先级越高)。 -->
                <priority>1</priority>
                <host>example01-01-1</host>
                <port>9000</port>
            </replica>
            
            <replica>
                <host>example01-01-2</host>
                <port>9000</port>
            </replica>
        </shard>

        <shard>
            <weight>2</weight>
            <internal_replication>false</internal_replication>

            <replica>
                <host>example01-02-1</host>
                <port>9000</port>
            </replica>
            
            <replica>
                <host>example01-02-2</host>
                <secure>1</secure>
                <port>9440</port>
            </replica>
        </shard>
    </logs>
</remote_servers>

【每个机器】上配置
   /etc/clickhouse-server/config.xml

# 编辑配置文件
vim /etc/clickhouse-server/config.xml

<!-- 2_Shard 1_Replica -->
    <cluster_2shards_1replicas>
        <!-- shard1  -->
        <shard>
            <replica>
                <host>192.168.10.63</host>
                <port>9000</port>
            </replica>
        </shard>
        
        <!-- shard2  -->
        <shard>
            <replica>
                <host>192.168.10.64</host>
                <port> 9000</port>
            </replica>
        </shard>
        
    </cluster_2shards_1replicas>


# 每个节点重启ClickHouse
systemctl restart clickhouse-server

# 判断配置是否成功（重启ClickHouse后能查询到） 2个节点都可以查看到
clickhouse-client --host=192.168.10.64
select * from system.clusters

┌─cluster─────────────────────────────────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name─────┬─host_address──┬─port─┬─is_local─┬─user────┬─default_database─┬─errors_count─┬─slowdowns_count─┬─estimated_recovery_time─┐
│ cluster_2shards_1replicas                       │         1 │            1 │           1 │ 192.168.10.63 │ 192.168.10.63 │ 9000 │        0 │ default │                  │            0 │               0 │                       0 │
│ cluster_2shards_1replicas                       │         2 │            1 │           1 │ 192.168.10.64 │ 192.168.10.64 │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
│ test_cluster_one_shard_three_replicas_localhost │         1 │            1 │           1 │ 127.0.0.1     │ 127.0.0.1     │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
│ test_cluster_one_shard_three_replicas_localhost │         1 │            1 │           2 │ 127.0.0.2     │ 127.0.0.2     │ 9000 │        0 │ default │                  │            0 │               0 │                       0 │
│ test_cluster_one_shard_three_replicas_localhost │         1 │            1 │           3 │ 127.0.0.3     │ 127.0.0.3     │ 9000 │        0 │ default │                  │            0 │               0 │                       0 │
│ test_cluster_two_shards                         │         1 │            1 │           1 │ 127.0.0.1     │ 127.0.0.1     │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
│ test_cluster_two_shards                         │         2 │            1 │           1 │ 127.0.0.2     │ 127.0.0.2     │ 9000 │        0 │ default │                  │            0 │               0 │                       0 │
│ test_cluster_two_shards_internal_replication    │         1 │            1 │           1 │ 127.0.0.1     │ 127.0.0.1     │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
│ test_cluster_two_shards_internal_replication    │         2 │            1 │           1 │ 127.0.0.2     │ 127.0.0.2     │ 9000 │        0 │ default │                  │            0 │               0 │                       0 │
│ test_cluster_two_shards_localhost               │         1 │            1 │           1 │ localhost     │ ::1           │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
│ test_cluster_two_shards_localhost               │         2 │            1 │           1 │ localhost     │ ::1           │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
│ test_shard_localhost                            │         1 │            1 │           1 │ localhost     │ ::1           │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
│ test_shard_localhost_secure                     │         1 │            1 │           1 │ localhost     │ ::1           │ 9440 │        0 │ default │                  │            0 │               0 │                       0 │
│ test_unavailable_shard                          │         1 │            1 │           1 │ localhost     │ ::1           │ 9000 │        1 │ default │                  │            0 │               0 │                       0 │
│ test_unavailable_shard                          │         2 │            1 │           1 │ localhost     │ ::1           │    1 │        0 │ default │                  │            0 │               0 │                       0 │
└─────────────────────────────────────────────────┴───────────┴──────────────┴─────────────┴───────────────┴───────────────┴──────┴──────────┴─────────┴──────────────────┴──────────────┴─────────────────┴─────────────────────────┘

建表实操

# 【选一个节点】 创建好本地表后，在1个节点创建，会自动同步到其他节点
create table default.soulboy_order on cluster cluster_2shards_1replicas
(id Int8,name String) engine =MergeTree order by id;


# 【选一个节点】 创建分布式表名 soulboy_order_all，在1个节点创建，会自动同步到其他节点
create table soulboy_order_all on cluster cluster_2shards_1replicas (
id Int8,name String
)engine = Distributed(cluster_2shards_1replicas,default, soulboy_order,hiveHash(id));

# 通过分布式表插入
insert into soulboy_order_all values(1,'里昂'),(2,'笨象'),(3,'巨象'),(4,'程书远');

# 【任意节点查询-分布式，全部数据】
SELECT * from soulboy_order_all
┌─id─┬─name─┐
│  1 │ 里昂 │
│  3 │ 巨象 │
└────┴──────┘
┌─id─┬─name───┐
│  2 │ 笨象   │
│  4 │ 程书远 │
└────┴────────┘

# 【ClickHouse1查询，部分数据】
SELECT * from soulboy_order
┌─id─┬─name───┐
│  2 │ 笨象   │
│  4 │ 程书远 │
└────┴────────┘

# 【ClickHouse2查询，部分数据】
SELECT * from soulboy_order
┌─id─┬─name─┐
│  1 │ 里昂 │
│  3 │ 巨象 │
└────┴──────┘

向集群中写入数据的两种方法

SpringBoot2.X整合ClickHouse实操

   HTTP接口允许您在任何编程语言的任何平台上使用ClickHouse。我们使用它在Java和Perl以及shell脚本中工作。在其他部门中，HTTP接口用于Perl、Python和Go。HTTP接口比原生接口受到更多的限制，但它具有更好的兼容性。

在线创建项目
    start.spring.io

引入依赖

<!-- Spring Web -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<!-- test -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<!-- clickhouse-jdbc -->
		<dependency>
			<groupId>ru.yandex.clickhouse</groupId>
			<artifactId>clickhouse-jdbc</artifactId>
			<version>0.1.55</version>
		</dependency>
		<!--mybatis plus-->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.4.0</version>
		</dependency>

application.properties

server.port=8080

spring.datasource.driver-class-name=ru.yandex.clickhouse.ClickHouseDriver
spring.datasource.url=jdbc:clickhouse://192.168.10.63:8123/default

mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

logging.level.root=INFO

ClickHouse项目数据统计需求分析

需求背景

   访问商品日志记录数据，经过Flink实时计算轻度聚合之后，存储到ClickHouse

前端显示图标组件（Charts）

库表和数据准备

建表语句

CREATE TABLE default.visit_stats
(
    `product_id` UInt64,
    `is_new` UInt16,
    `province` String,
    `city` String,
    `pv` UInt32,
    `visit_time` DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(visit_time)
ORDER BY (
 product_id,
 is_new,
 province,
 city
 );

插入数据

### 0代表老用户，1代表新用户，PV是访问量（轻度聚合后）、访问时间
INSERT into visit_stats values
('1','1','广东','广州',14323,'2023-01-01 12:11:13'),
('1','0','广东','广州',4232,'2023-02-12 16:16:13'),
('1','1','广东','佛山',54323,'2023-03-06 16:11:13'),
('1','0','广东','东莞',42341,'2023-03-02 16:12:13'),
('1','1','广东','梅州',52422,'2023-03-09 12:11:13'),

('2','1','广东','广州',14323,'2021-03-01 12:11:13'),
('2','0','广东','深圳',425232,'2023-04-12 16:16:13'),
('2','1','广东','佛山',543323,'2022-06-06 16:11:13'),
('2','0','广东','东莞',42341,'2021-05-02 16:12:13'),
('2','1','广东','梅州',52422,'2022-01-09 12:11:13'),

('3','1','北京','北京',13132,'2023-01-01 12:11:13'),
('3','0','广东','广州',533232,'2022-02-16 16:16:13'),
('4','1','浙江','杭州',663643,'2023-12-06 12:11:13'),
('4','0','广东','东莞',4142,'2023-11-02 16:12:13'),
('5','1','湖南','长沙',52123,'2022-01-09 12:11:13'),
('4','0','湖南','衡阳',4142,'2024-05-02 16:12:13'),
('5','1','广东','中山',52123,'2024-01-09 12:11:13'),

('2','1','上海','上海',14323,'2021-03-01 12:11:13'),
('5','0','浙江','宁波',425232,'2023-04-12 16:16:13'),
('3','1','广东','佛山',543323,'2022-06-06 16:11:13'),
('2','0','湖南','长沙',42341,'2021-05-02 16:12:13'),
('2','1','广东','深圳',52422,'2022-01-09 12:11:13')

查询

SELECT * FROM visit_stats;

函数编写统计SQL

函数
   ClickHouse中至少存在两种类型的函数 - 常规函数（它们称之为«函数»）和聚合函数。 常规函数的工作就像分别为每一行执行一次函数计算一样（对于每一行，函数的结果不依赖于其他行）。 聚合函数则从各行累积一组值（即函数的结果以来整个结果集）。

统计需求
   某个商品在一定的时间范围内（天级别），各地区（城市级别）访问分布

# 连接
clickhouse-client --host=192.168.10.63

# 某个商品在一定的时间范围内（天级别），各地区（城市级别）访问分布
select province,city, sum(pv) pv_count  
from visit_stats where  product_id =1 
and toYYYYMMDD(visit_time) BETWEEN '20200101' and '20241212' 
group by province,city order by pv_count desc

┌─province─┬─city─┬─pv_count─┐
│ 广东     │ 佛山 │    54323 │
│ 广东     │ 梅州 │    52422 │
│ 广东     │ 东莞 │    42341 │
│ 广东     │ 广州 │    18555 │
└──────────┴──────┴──────────┘

# 全部商品在一定的时间范围内（天级别），各地区（城市级别）访问分布
select province,city, sum(pv) pv_count  
from visit_stats where toYYYYMMDD(visit_time) BETWEEN '20200101' and '20241212' 
group by province,city order by pv_count desc

┌─province─┬─city─┬─pv_count─┐
│ 广东     │ 佛山 │  1140969 │
│ 浙江     │ 杭州 │   663643 │
│ 广东     │ 广州 │   566110 │
│ 广东     │ 深圳 │   477654 │
│ 浙江     │ 宁波 │   425232 │
│ 广东     │ 梅州 │   104844 │
│ 湖南     │ 长沙 │    94464 │
│ 广东     │ 东莞 │    88824 │
│ 广东     │ 中山 │    52123 │
│ 上海     │ 上海 │    14323 │
│ 北京     │ 北京 │    13132 │
│ 湖南     │ 衡阳 │     4142 │
└──────────┴──────┴──────────┘

统计需求

   某个商品，多天内的访问曲线图（天级别）

### 某个商品，多天内的访问曲线图（天级别）
select 
  toYYYYMMDD(visit_time) date_time_str, 
  sum(pv) pv_count 
 from visit_stats
 where product_id = 1 and toYYYYMMDD(visit_time) BETWEEN '20200101' and '20241212' 
 group by date_time_str ORDER BY date_time_str desc

┌─date_time_str─┬─pv_count─┐
│      20230309 │    52422 │
│      20230306 │    54323 │
│      20230302 │    42341 │
│      20230212 │     4232 │
│      20230101 │    14323 │
└───────────────┴──────────┘

SpringBoot2.X整合ClickHouse基础模块搭建

响应工具类JsonData（封装响应结果给前端）

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class JsonData {

    /**
     * 状态码 0 表示成功
     */

    private Integer code;
    /**
     * 数据
     */
    private Object data;
    /**
     * 描述
     */
    private String msg;


    /**
     * 成功，不传入数据
     *
     * @return
     */
    public static JsonData buildSuccess() {
        return new JsonData(0, null, null);
    }

    /**
     * 成功，传入数据
     *
     * @param data
     * @return
     */
    public static JsonData buildSuccess(Object data) {
        return new JsonData(0, data, null);
    }

    /**
     * 失败，传入描述信息
     *
     * @param msg
     * @return
     */
    public static JsonData buildError(String msg) {
        return new JsonData(-1, null, msg);
    }


    /**
     * 自定义状态码和错误信息
     *
     * @param code
     * @param msg
     * @return
     */
    public static JsonData buildCodeAndMsg(int code, String msg) {
        return new JsonData(code, null, msg);
    }


}

Model层

VisitStatsDO类

import lombok.Data;

@Data
public class VisitStatsDO {

    /**
     * 商品
     */
    private Long productId;

    /**
     * 访问时间
     */
    private String visitTime;


    /**
     * 1是新访客，0是老访客
     */
    private Integer isNew;

    /**
     * 访问量
     */
    private Integer pv;

    /**
     * 省份
     */
    private String province;

    /**
     * 城市
     */
    private String city;

    /**
     * ========度量值=========
     */
    private Long pvCount=0L;

    /**
     * 时间的字符串映射，天、小时
     */
    private String dateTimeStr;
}

Mapper接口

VisitStatsMapper接口类

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.xdclass.demo.model.VisitStatsDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface VisitStatsMapper extends BaseMapper<VisitStatsDO> {

    /**
     * 统计总条数
     * @param productId
     * @return
     */
    int countTotal(@Param("productId") long productId);

    /**
     * 分页查询
     * @param productId
     * @param from
     * @param size
     * @return
     */
    List<VisitStatsDO> pageVisitRecord(@Param("productId") Long productId , @Param("from") int from, @Param("size") int size);



    /**
     * 查询地区访问分布
     * @param productId
     * @param startTime
     * @param endTime
     * @return
     */
    List<VisitStatsDO> queryRegionTrendWithMultiDay(@Param("productId")Long productId, @Param("startTime") String startTime,  @Param("endTime")String endTime);

    /**
     * 查询访问趋势，多天内
     * @param productId
     * @param startTime
     * @param endTime
     * @return
     */
    List<VisitStatsDO> queryVisitTrendWithMultiDay(@Param("productId")Long productId, @Param("startTime") String startTime,  @Param("endTime")String endTime);
}

VisitStatsMapper.xml
   D:\Project\ClickHouse\src\main\resources\mapper\VisitStatsMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="net.xdclass.demo.mapper.VisitStatsMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="net.xdclass.demo.model.VisitStatsDO">
        <result column="product_id" property="productId"/>
        <result column="is_new" property="isNew"/>
        <result column="province" property="province"/>
        <result column="city" property="city"/>
        <result column="pv_count" property="pvCount"/>
        <result column="visit_time" property="visitTime"/>
        <result column="date_time_str" property="dateTimeStr"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        product_id,is_new,province,city,pv,visit_time
    </sql>

    <!-- 统计总条数 -->
    <select id="countTotal" resultType="java.lang.Integer">
        select count(1) from visit_stats  where   product_id=#{productId}
    </select>

    <!-- 分页查找 -->
    <select id="pageVisitRecord" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from visit_stats where product_id=#{productId}
        order by visit_time desc limit #{from},#{size}
    </select>

    <!-- 某个商品，多天内的访问曲线图（天级别）: 按城市查询 -->
    <select id="queryRegionTrendWithMultiDay" resultMap="BaseResultMap">
        select province ,city, sum(pv) pv_count from visit_stats
        where product_id = #{productId} and toYYYYMMDD(visit_time) BETWEEN #{startTime} and #{endTime}
        GROUP BY province ,city  order by pv_count desc
    </select>

    <!-- 某个商品，多天内的访问曲线图（天级别）: 按时间查询 -->
    <select id="queryVisitTrendWithMultiDay" resultMap="BaseResultMap">
        select toYYYYMMDD(visit_time) date_time_str,sum(pv) pv_count from visit_stats
        where product_id = #{productId} and toYYYYMMDD(visit_time) BETWEEN #{startTime} and #{endTime}
        GROUP BY date_time_str order by date_time_str desc
    </select>

</mapper>

Service层

VisitStatsService接口

import net.xdclass.demo.controller.request.VisitRecordPageRequest;
import net.xdclass.demo.controller.request.VisitTrendQueryRequest;
import net.xdclass.demo.model.VisitStatsDO;

import java.util.List;
import java.util.Map;

public interface VisitStatsService {

    /**
     * 分页查询
     * @param pageRequest
     * @return
     */
    Map<String, Object> pageVisitRecord(VisitRecordPageRequest pageRequest);


    /**
     * 某个商品，多天内的访问曲线图（天级别） 按城市 or 时间
     * @param queryRequest
     * @return
     */
    List<VisitStatsDO> queryVisitTrend(VisitTrendQueryRequest queryRequest);
}

VisitStatsServiceImpl 实现类

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class VisitStatsServiceImpl implements VisitStatsService {

    @Autowired
    private VisitStatsMapper visitStatsMapper;

    /**
     * 分页查询
     * @param pageRequest
     * @return
     */
    @Override
    public Map<String, Object> pageVisitRecord(VisitRecordPageRequest pageRequest) {
        Map<String, Object> data = new HashMap<>(3);
        //获取商品id
        long productId = pageRequest.getProductId();
        //当前页数
        int page = pageRequest.getPage();
        //每页多少条
        int size = pageRequest.getSize();
        //统计总记录数
        int count = visitStatsMapper.countTotal(productId);
        //分页查询
        int from = (page - 1) * size;
        List<VisitStatsDO> visitStatsDOS = visitStatsMapper.pageVisitRecord(productId, from, size);
        //将信息封装到Map中返回
        data.put("total", count); //总记录数
        data.put("current_page", page); //当前页
        data.put("data", visitStatsDOS); //当前页数据
        //计算总页数
        int totalPage;
        if (count % size == 0) {
            totalPage = count / size;
        } else {
            totalPage = count / size + 1;
        }
        data.put("total_page", totalPage);
        return data;
    }

    /**
     * 某个商品，多天内的访问曲线图（天级别） 支持按照城市 or 时间 查询
     * @param queryRequest
     * @return
     */
    @Override
    public List<VisitStatsDO> queryVisitTrend(VisitTrendQueryRequest queryRequest) {
        long productId = queryRequest.getProductId();
        String type = queryRequest.getType();
        List<VisitStatsDO> list = null;

        //判断type 城市或者时间
        if (type.equalsIgnoreCase("region")) {
            list = visitStatsMapper.queryRegionTrendWithMultiDay(productId,queryRequest.getStartTime(),queryRequest.getEndTime());
        } else if (type.equalsIgnoreCase("day")) {
            list = visitStatsMapper.queryVisitTrendWithMultiDay(productId,queryRequest.getStartTime(),queryRequest.getEndTime());
        }
        return list;
    }
}

Controller层

分页查询参数接收类 VisitRecordPageRequest

import lombok.Data;

@Data
public class VisitRecordPageRequest {

    //商品id
    private long productId;

    //显示当前第几页
    private int page;

    //每页显示多少条
    private int size;
}

分页查询参数接收类 VisitTrendQueryRequest

import lombok.Data;

@Data
public class VisitTrendQueryRequest {

    private long productId;

    private String startTime;

    private String endTime;

    /**
     * 查询类型: 时间分布 or 城市分布
     */
    private String type;
}

DataController类

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/api/v1/data")
public class DataController {

    @Autowired
    private VisitStatsService visitStatsService;

    /**
     * 分页查询
     * @param pageRequest
     * @return
     */
    @RequestMapping("page")
    public JsonData queryVisitRecord(@RequestBody VisitRecordPageRequest pageRequest) {
        Map<String, Object> map = visitStatsService.pageVisitRecord(pageRequest);
        return JsonData.buildSuccess(map);
    }


    /**
     * 某个商品，多天内的访问曲线图（天级别）
     * @param queryRequest
     * @return
     */
    @RequestMapping("trend")
    public JsonData queryVisitTrend(@RequestBody VisitTrendQueryRequest queryRequest){
        List<VisitStatsDO> list = visitStatsService.queryVisitTrend(queryRequest);
        return JsonData.buildSuccess(list);
    }


}

测试结果

分页测试
   http://192.168.10.88:8080/api/v1/data/page

某个商品，多天内的访问曲线图（天级别） 按照城市查询
   http://192.168.10.88:8080/api/v1/data/trend

某个商品，多天内的访问曲线图（天级别） 按照时间查询
   http://192.168.10.88:8080/api/v1/data/trend

建表和引擎选择归纳

   主要是数据仓库用比较多，一般都是MergeTree系列引擎，没有最好，根据业务选择最合适的

• MergeTree

  • 去重合并树引擎ReplacingMergeTree
    • 可以保证幂等性处理
  • 聚合合并树引擎SummingMergeTree
    • 可以进行预聚合，性能更高
    • 重复作业会导致数据累计

• Log系列

  • 说明：最小功能的​轻量级引擎，​当需要快速写入许多小表并在以后整体读取它们时效果最佳，即一次写入多次查询，使用大量的小表存储数据，性能会很低，生产环境使用少，练习为主
  • 这些引擎是为了需要写入许多小数据量（少于一百万行）的表的场景而开发的
  • 将数据存储在磁盘上，不支持索引，数据写入时追加写到文件末尾
  • 该引擎没有并发控制，不允许同时读写，当向表中写入数据时，这张表的查询会被阻塞，直至写入完成
  • 不支持原子写，如果写过程中断，可能会损坏表数据
  • 包括：TinyLog、Log、StripLog

• 主键选择

  • 主键可以重复，也可以是多列的组合
  • 如果未指明主键，则使用order by 字段作为主键
  • 业务里面 where条件常用的列，频率高的字段排前面

• 排序键选择

  • 业务里面 where条件常用的列，频率高的字段排前面

• 分区键选择

  • 常规根据时间进行分区，分区字段类型建议是DateTime类型，不要是Long类型
  • 不能分区粒度太细，比如秒级别分区键，则插入很容易报错，

常见注意事项和异常问题排查

• 注意点一

  • 查询要使用的列，避免select * 全部字段，浪费IO资源

• 注意点二

  • 避免大量的小批量数据，插入更新操作，会导致分区过多
    • 每次插入1条，产生了一个分区，大量写入产生大量临时分区和合并操作浪费资源

• 注意点三

  • JOIN操作时一定要把数据量小的表放在右边，无论是Left Join 、Right Join还是Inner Join，右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表

• 注意点四

  • 批量写入数据时，控制每个批次的数据中涉及到的分区的数量，无序的数据导致涉及的分区太多，建议写入之前最好对需要导入的数据进行排序

• 注意点五

  • 写入分布式表还是本地表? 建议：数据量不大，写入本地表和分布式表都行

分布式表不存储数据，本地表存储数据的表

大量数据，日新增500万行以上，分布式表在写入时会在本地节点生成临时数据，会产生写放大，所以会对CPU及内存造成一些额外消耗，服务器merge的工作量增加, 导致写入速度变慢；

数据写入默认是异步的（可以开启同步写但性能会影响），先在分布式表所在的机器进行落盘, 然后异步的发送到本地表所在机器进行存储，中间没有一致性的校验，短时间内可能造成不一致，且如果分布式表所在机器时如果机器出现down机, 会存在数据丢失风险。

建议大数据量尽量少使用分布式表进行写操作，可以根据业务规则均衡的写入本地表；
  
必须用的话尽量只用读，因为写分布式表对性能影响非常大

• 注意点六

  • 单sql查询可以压榨CPU ，但并发多条查询则不是很强
  • 一个分区查询占据一个CPU，业务需要查询表的多个分区可以多个CPU并行处理

• 注意点七

  • 没有完整的事务支持，不支持Transaction
  • OLAP类业务由于数据量非常大，建议数据批量写入，尽量避免数据更新或少更新

• 注意点八

  • 在分布式模式下，ClickHouse会将数据分为多个分片，并且分布到不同节点上，有哪种分片策略
  • ClickHouse提供了丰富的sharding策略，让业务可以根据实际需求选用
    • random随机分片:写入数据会被随机分发到分布式集群中的某个节点上
    • constant固定分片:写入数据会被分发到固定一个节点上
    • hash column value分片：按照某一列的值进行hash分片

常见异常问题

• 错误码 300，Too many parts

写入频率过快，使用了不合理的分区键导致总的 part 数目太多，好比-soulboy，直接拿精确到秒的 timestamp 来作为分区键来进行分区，GG了。

• 错误码252，Too many partitions for single INSERT block (more than 100)

同一批次写入里包括大于100个分区值，clickhouse认为这样会存在性能问题

让数据是按照天/小时分区的，一批数据里的日期跨度为一年，单次插入可能产生365个分区，导致后台异步合并数据出现问题，也避免跨度过大

解决方案：单批次写入数据，要控制写入分区过多

参数：max_partitions_per_insert_block 限制单个插入块中的最大分区数，默认是100

ClickHouse高性能查询原因剖析-稀疏索引

密集索引
   在密集索引中，数据库中的每个键值都有一个索引记录，可以加快搜索速度，但需要更多空间来存储索引记录本身，索引记录包含键值和指向磁盘上实际记录的指针，索引项是连续的。

稀疏索引
   在稀疏索引中，不会为每个关键字创建索引记录，而是为数据记录文件的每个存储块设一个键-指针对，存储块意味着块内存储单元连续

案例

• Mysql的MyISAM引擎里面， 使用均为稀疏索引；
• Mysql的Innodb引擎里面，如果有主键，则主键为密集索引
• Kafka里面的索引文件也是采用稀疏索引进行构造消息索引
• ClickHouse的合并树MergeTree引擎是稀疏索引，默认index_granularity设置8192，新版本的提供了自适应粒度大小的特性

建表语句最后加这个，可以调整
SETTINGS index_granularity = 8192

总结

• ClickHouse一级索引就是【稀疏索引】，可以大幅减少索引占用的空间
  • 默认的索引力度8192，假如1亿行数据只需要存储12208行索引，占用空间小，clickhouse中`一级索引的数据是常驻内存的，取用速度极快
  • 稀疏索引所占空间小，并且插入和删除时所需维护的开销也小，缺点是速度比密集索引慢一点
• 密集索引空间占用多，比稀疏索引更快的定位一条记录，缺点就是会占用较多的空间
• 不变思想：时间换空间、空间换时间

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的写入速度

什么是LSM-Tree

   全称 Log-Structured Merge-Tree 日志结构合并树，但不是树，而是利用磁盘顺序读写能力，实现一个多层读写的存储结构

• 是一种分层，有序，面向磁盘的数据结构，核心思想是利用了磁盘批量的顺序写要远比随机写性能高出很多
• 大大提升了数据的写入能力，但会牺牲部分读取性能为代价
• HBase、LevelDB、ClickHouse这些NoSQL存储都是采用的类LSM树结构
• 在 NoSQL 系统里非常常见，基本已经成为必选方案, 为了解决快速读写的问题去设计的

分两个部分理解

• Log-Structured日志结构的，打印日志是一行行往下写，不需要更改，只需要在后边追加就好了
• Merge-tree，合并就是把多个合成一个，自上而下

LSM-tree 是一个多层结构，就更一个喷泉树一样，上小下大

• 专门为 key-value 存储系统设计的，最主要的就两个个功能
  • 写入put（k，v）
  • 查找 get（k）得到v
• 首先是内存的 C0 层，保存了所有最近写入的 （k，v），这个内存结构是有序的，且可以随时原地更新，同时支持随时查询
• 接下去的 C1 到 Cn 层都在磁盘上，每一层都是一个有序的存储结构
• 降低一点读性能，通过牺牲小部分读性能，换来高性能写

写入流程

• put 操作，首先追加到写前日志（Write Ahead Log），然后加到C0 层
• 当 C0 层的数据达到一定大小，就把 C0 层 和 C1 层合并，这个过程就是Compaction（合并）
• 合并出来的新的C1 会顺序写磁盘，替换掉原来的C1
• 当 C1 层达到一定大小，会和下层继续合并，合并后删除旧的，留下新的

查询流程

• 最新的数据在 C0 层，最老的数据在 Cn 层
• 查询也是先查 C0 层，如果没有要查的数据，再查 C1，逐层查下去直到最后一层（通过牺牲小部分读性能，换来高性能写）

缺点

• 读放大：读取数据时实际读取的数据量大于真正的数据量，在LSM树中需要先在C0查看当前key是否存在，不存在继续从Cn层中寻找
• 写放大：写入数据时实际写入的数据量大于真正的数据量，在LSM树中写入时可能触发Compact操作，导致实际写入的数据量远大于该key的数据量