BI系统(OLAP):面向数据仓库,专注数据分析、决策功能的系统和解决方案
OLAP三类架构:
ROLAP(关系型OLAP):直接使用关系模型构建,常用星型模型或雪花模型
MOLAP(多维OLAP):借助预聚合结果,使用空间换取时间的形式提升查询性能(数据立方体会有一定的滞后性,不能实时进行数据分析,切有数据膨胀风险)
HOLAP(混合OLAP)
ClickHouse(Click Stream Data WareHouse)
不足:
- 不支持事务
- 不擅长根据主键按行粒度进行查询,不能把ClickHouse当作Key-Value数据库使用
- 不擅长按行删除数据
特点:
1)完备的DBMS功能
2)列式存储/数据压缩
3)向量化(使用到CPU的SIMD指令,单条指令操作多条数据)
4)关系模型SQL,且大小写敏感
5)多样化表引擎
6)多线程与分布式:由于SIMD不适合用于带有多分支判断的场景,使用了多线程技术实现提速
分区:纵向扩展,利用多线程原理
分片:横向扩展,利用分布式原理(1个分片只对应一个服务节点)
7)多主架构Multi-Master,避免了单点故障问题,适用多数据中心,异地多活的场景
8)可通过分布式表实现分布式查询
核心模块:
Column:内存中的一列数据由Column对象表示。
Field:表示一个单值
DataType:负责序列化工作,并不直接负责数据的读取,而是转由从Column或Field对象获取
Block:由<数据对象,数据类型,列名称>组成的三元组,即Column,DataType和列名称字符串
Column提供了数据的读取能力,DataType知道如何正反序列化,由Block对象基础上实现了进一步的抽象和封装,简化了使用过程,仅通过Block对象就可以完成一系列数据操作
INSERT 查询最终转换为Block数据块,单个数据块的写入过程具有原子性,每个数据块最多可以写入1048576行数据(由max_insert_block_size参数控制)
基于Block:
(1)IBlockInputStream:负责数据的读取和关系运算
(2)IBlockOutputStream:负责将数据输出到下一个环节
Table:直接使用IStorage接口指代数据表
Parser:负责创建AST对象
Interpreter:负责解释AST,并进一步创建查询的执行管道。向Service服务层一样,起到串联整个查询过程的作用,会根据解释器的类型,聚合它所需要的资源,最终返回IBlock对象
IFunction:普通函数
IAggregateFunction:聚合函数
内置的实用工具:
clickhouse-local:可独立运行大部分SQL查询,可以理解为ClickHouse服务的单机版微内核,是一个轻量级应用程序
clickhouse-benchmark:基准测试小工具
ClickHouse基础类型:
(1)数值类型:
- Int
有符号整数类型:Int8、Int16、Int32、Int64
无符号整数类型:UInt8、UInt16、UInt32、UInt64
- Float
Float32:有效精度7,相当于Float
Float64:有效精度16,相当于Double
- Decimal
Decimal(总位数,小数位数)
Decimal132(S):Decimal(1~9,S)
Decimal64(S):Decimal(10~18,S)
Decimal128(S):Decimal(19~38,S)
(2)字符串类型:
- String
- FixedString:明确的字符串长度,使用null字符填充末尾字符
FixedString(N)
- UUID
如果UUID类型的字段在写入数据时没有被赋值,则会依照格式实用0填充
(3)时间类型:
- DateTime(包含时、分、秒信息,精确到秒)
- DateTime64(可以记录亚秒)
- Date(只精确到天)
(4)复合类型:
- Array
并不需要主动声明数组的元素类型,会以最小存储代价为原则,使用最小可表达的数据类型
同一个数组内可以包含多个数据类型,但各个类型必须兼容
- Tuple
由1~n个元素组成,每个元素允许设置不同的数据类型,且不要求兼容
定义时需要指明元素类型,例如:Tuple<String,Int8>
- Enum
Enum8、Enum16
出于性能考虑,用枚举类型而不用String类型,后续所有的操作都会使用到Int类型的Value值,而不是Key对应的String类型
例如:
create table enum_test(
c1 Enum8('ready' = 1, 'start' = 2)
) ENGINE = Memory
- Nested:嵌套表结构
一张表可以定义任意多个嵌套类型字段,每个字段只支持一级,且嵌套表不能继续使用嵌套类型
例如:
dept Nested(
id UInt8,
name . String
)
嵌套类型本质时一个多维数组,每个字段都是一个数组。插入数据也需要使用数组方式,如:
INSERT INTO xxx values ([10000,20000,30000],[‘XXX’,'YYY',''ZZZ])
(5)特殊类型:
- Nullable:辅助修饰符,表示某个类型的数据可以是空值
Nullable(UInt8)
注意点:
只能和基础类型搭配使用
应该慎用Nullable类型,性能会变慢
每个列字段的数据会被存储在对应的[Column].bin文件中,如果一个列字段被Nullable类型修饰后,会额外生成一个[Column].null.bin文件,专门保存它的Null值,意味着读取和写入需要一倍的额外文件操作
- Domain:分为IPv4和IPv6
使用Domain而不是用String的原因:
(1)支持格式检查,避免错误的IP格式写入
(2)存储更紧凑,占用空间小,查询性能更快
默认值表达式:表一旦定义了默认值,便不再强制要求定义数据类型,会根据默认值进行类型判断
DEFAULT
MATERIALIZED
ALIAS
临时表:
特点:
(1)生命周期的会话绑定的
(2)临时表不属于任何数据库
如果出现临时表和普通表同名的情况,同名表会优先读取临时表
分区表:只有合并树(MergeTree)家族系列的表引擎才支持数据分区
视图:
- 普通视图:只有一层简单的查询代理
- 物化视图:拥有独立的存储
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE=engine] [POPULATE] AS SELECT .....
POPULATE修饰符决定了物化视图初始化策略:如果使用了会将源表中已存在的数据一并导入
物化视图并不支持同步删除,如果在源表中删除了数据,物化视图的数据仍然会保留
物化视图本质是一张特殊表结构:
.inner. 特殊前缀的数据表
表的基本操作:
- 追加新字段:ALTER TABLE tb_name ADD COLUMN name
- 修改数据类型:ALTER TABLE tb_name MODIFY COLUMN name
- 修改备注:ALTER TABLE tb_name COMMENT COLUMN name 'xxx'
- 删除已有字段:ALTER TABLE tb_name DROP COLUMN name
- 移动数据表:表的移动只能在单个节点范围内
RENAME TABLE db1.tb_name1 TO db2.tb_name2
分区的基本操作:
- 查询分区信息:SELECT partition_id,name,table,database from system.parts
- 删除分区:ALTER TABLE tb_name DROP PARTITION partition_expr
- 复制分区:ALTER TABLE B REPLACE partition_expr FROM A
- 重置分区数据:ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr
- 卸载与装载分区:都是DETACH关键字操作(分区卸载后,物理数据并没有删除,而是转移到了当前数据表的detached子目录下)
删除与修改(Mutation查询:DELETE & UPDATE)
特点:
(1)“重操作”,适用于批量数据的修改和删除
(2)不支持事务,一旦执行会立刻对现有数据产生影响,无法回滚
(3)Mutation语句执行是一个异步后台过程,语句提交后会立即返回,并不代表已经执行完成
分布式DDL执行:on cluster xxx
数据库引擎:
- Ordinary:默认引擎
- Dictionary:字典引擎
- Memory:内存引擎
- Lazy:日志引擎
- MYSQL:MYSQL引擎
数据字典:
以键值和属性映射的形式定义数据,非常适合保存常量或经常使用的维度表数据,避免不必要的JOIN查询
7种类型字典的特点总结:
名称 | 存储结构 | 字典键类型 | 支持数据的来源 | 特点 |
flat(性能最好) | 数组 | UInt64 | Local file Executable file HTTP DBMS | 数组的初始大小1024,上限500000 |
hashed | 散列 | UInt64 | Local file Executable file HTTP DBMS | 通过散列结构保存,没有存储上限的制约 |
range_hashed | 散列并按时间排序 | UInt64 和 时间 | Local file Executable file HTTP DBMS | 增加了时间区间的特定,数据会散列存储,并按照时间存储 |
complex_key_hashed | 散列 | 复合型key | Local file Executable file HTTP DBMS | 在hashed基础上,将单个数值型key型换成了复合型 |
ip_trie | 层次结构 | 复合型key (单个String) | Local file Executable file HTTP DBMS | 专门用于IP前缀查询的场景 |
cache(性能不稳定) | 固定大小数组 | UInt64 | Executable file HTTP ClickHouse MYSQL | 固定长度的向量数组保存,定长向量数组又称为cells 数组长度由size_in_cells指定,而size_in_cells的取值大小必须是2的整数倍 查找方法:先查询缓存,如果没有,则从源头加载到cells中,因此不稳定 |
complex_key_cache | 固定大小数组 | 复合型key | Executable file HTTP ClickHouse MYSQL |
数据源:
- 文件类型
本地文件
可执行文件,cat命令
远程文件,post请求
- 数据库类型
扩展字典的数据更新策略:
<lifetime>
<min>300</min>
<max>360</max>
</lifetime>
当min和max都为0,将禁用字典更新
自动更新具体原理:
只有源数据实质变化后才更新,判断依据是数据源是否被修改的标识(previous)
ClickHouse每隔5秒会启动一次数据刷新的判断,以此对比每个数据字典前后两次previous值是否相同,不同则更新数据
previous值的获取手段:
(1)文件数据源:系统的文件修改时间
(2)Mysql(InnoDB)、ClickHouse和ODBC:invalidate_query中定义的SQL语句
(3)MYSQL(MYISAM):show table status
字典数据查询:dictGet
整型数据函数:dictGetUInt8、dictGetUInt16、dictGetUInt32、dictGetUInt64、dictGetInt8、dictGetInt16、dictGetInt32、dictGetInt64
浮点数据函数:dictGetFloat32、dictGetFloat64
日期数据函数:dictGetDate、dictGetDateTime
字符串数据函数:dictGetString、dictGetUUID
MergeTree原理解析
合并树:支持主键索引、数据分区、数据副本和数据采样这些特性,同时也支持ALTER相关操作
MergeTree在写入一批数据时,数据总会以数据片段的形式写入磁盘,且数据片段不可修改。为了避免片段过多,CK会通过后台线程,定期合并这些数据片段,属于相同分区的数据片段会被合成一个新片段
合并树引擎家族:
MergeTree的创建参数:
(1)PARTITION BY:分区键可以是单个列字段,也可以是元组形式的多个列字段。如果不声明分区键,CK会生成一个名为all的分区
(2)ORDER BY:用于指定在一个数据片段内,数据以何种标准排序
(3)PRIMARY KEY:声明后会依据主键字段生成一级索引,用于加速表的查询
(4)SAMPLE BY:用于声明数据以何种标准进行采样
(5)SETTINGS:index_granularity,默认值8192,每隔8192行数据才生成一条索引
(6)SETTING:index_granularity_bytes,默认值8192,自适应间隔大小,每一批次写入数据的体量大小
(7)SETTING:enable_mixed_granularity_parts:是否开启自适应索引间隔功能
(8)SETTING:merge_with_ttl_timeout
(9)SETTING:storage_policy:多路径存储策略
MergeTree存储结构:
分区键命名规则:
PartitionID_MinBlockNum_MaxBlockNum_Level
(1)PartitionId:分区ID
(2)MinBlockNum和MaxBlockNum:最小数据块和最大数据块编号
(3)Level:某个分区被合并的次数
分区合并过程:
数据在写入后分区才会被创建
CK在后台将属于相同的分区的多个目录合并成一个新的目录,已存在的目录并不会立即删除,而是在默认8分钟后通过后台任务删除
索引粒度:
一个具体的数据即为:MarkRange,与索引编号对应,使用start和end两个属性表示区间范围
索引生成规则:
索引数据查询步骤:
(1)生成查询条件区间
(2)递归判断交集:依次对MarkRange的数值区间与条件区间做交集判断
- 如果不存在交集,则剪枝
- 如果存在交集,且MarkRange步长大于8(end-start),则将区间进一步拆分成8个子区间,并重复递归
- 如果存在交集,切MarkRange不可再分解,则记录MarkRange返回
(3)合并MarkRange区间:将最终匹配的MarkRange聚合在一起并合并
二级索引(跳级索引)
参数:granularity
granularity定义了聚合信息汇总的粒度,一行跳级索引跳过了多少个index_granularity区间的数据
index_granularity定义了数据的粒度
例子:
INDEX a ID TYPE minmax GRANULARITY 5
INDEX b (length(ID)*8) TYPE set(2) GRANULARITY 5
INDEX c (ID,Code) TYPE ngrambf_v1(3,256,2,9) GRANULARITY 5
INDEX d ID TYPE tokenbf_v1(256,2,0) GRANULARITY 5
- minmax:索引记录了一段数据内的最小和最大极值
- set:记录了声明字段或表达式的取值(唯一值,无重复),set(max_rows)表示在一个indx_granularity内,索引最多记录的数据行数,max_rows表示一个阈值
- ngrambf_v1:数据短语的布隆过滤器,只支持String和FixedString数据类型
ngrambf_v1(token长度,布隆过滤器大小,布隆过滤器中使用的Hash函数个数,Hash函数的随机种子)
- tokenbf_v1:是ngrambf_v1的变种。会自动按照非字符的、数字的字符串分割token
基于MergeTree的数据存储:
每个列字段都对应.bin文件
(1)数据压缩:目前支持LZ4、ZSTD、Multiple和Delta几种算法
(2)数据会事先依照ORDER BY的声明排序
(3)数据以压缩数据块的形式被组织写入到.bin文件中
压缩块示意图:
写入过程规则:(每个压缩块大小在64KB~1MB之间)
(1)单个批次数据size<64KB:继续获取下一批数据,累积size>=64KB,生成下一个压缩数据块
(2)单个批次数据64KB <= size <= 1MB:如果单个批次数据大小恰好在64KB与1MB之间,则直接生成下一个压缩数据块
(3)单个批次数据size> 1MB:如果单个批次数据超过1MB,则先按照1MB大小截断并生成下一个数据块,剩余依照上述规则执行
数据标记(.mrk):关联索引与.bin文件
(1)记录了章节对应的页码信息
(2)是一段文字在某一页中的起始位置信息
标记示意图:
分别记录了此段数据内,数据块的起始偏移量、以及解压后的偏移量
其他特性:
(1)TTL:
列级别、表级别
用法:TTL time_col . + INTERVAL xx DAY(SECOND、MINUTE等)
强制触发TTL清理:optimize TABLE table_name 触发一个分区合并/optimize TABLE ttl_table_v1 FINAL 触发所有分区合并
控制全局TTL合并任务启停方法:SYSTEM STOP/START TTL MERGES
TTL运行原理:
在写入数据时,会以数据分区为单位,在每个分区目录内生成一个名为ttl.txt的文件。在文件中通过JSON配置保存了TTL相关信息:
- columns用于保存列级别TTL信息
- table用于保存表级别TTL信息
- min和max保存了当前数据分区内,TTL指定日期字段的最小值、最大值分别与INTERVAL表达式计算后的时间戳
TTL处理逻辑:
- MergeTree以分区目录为单位,通过ttl.txt文件记录过期时间,并将其作为后续判断的依据
- 每当写入一批数据时,都会基于INTERVAL表达式计算结果为这个分区生成ttl.txt文件
- 只有在MergeTree合并分区时,才会触发删除TTL过期数据的逻辑
- 在选择删除分区时,会使用贪婪算法,尽可能找到会最早过期的,同时年纪又最老的分区
- 如果一个分区内某一列数据因为TTL到期全部删除了,那么在合并之后生成的新分区目录中,将不会包含这个列字段的数据文件(.bin和.mrk)
TTL合并频率由MergeTree的merge_with_ttl_timeout参数控制,维护者一个专有的TTL任务队列
除了被动触发TTL合并,也可以使用optimize命令强制触发合并
(2)多路径存储策略:
将分区目录写入多块磁盘目录
- 默认策略
- JBOD策略:适合服务器挂载多块磁盘,但没有做RAID的场景,每执行一次INSERT或者MERGE,所产生的新分区会轮询写入各个磁盘
- HOT/COLD策略:适合服务器挂载多块磁盘,HOT区使用SSD高性能存储媒介,COLD区使用了HDD这类高容量存储媒介
MergeTree系列表引擎
ReplacingMergeTree
在MergeTree基础上,添加了主键唯一性约束(只是一定程度解决了重复数据的问题)
以分区为单位删除重复数据,只有在相同的数据分区内重复数据才能被删除,而不同数据分区之间的数据依然不能被删除
处理逻辑:
- 使用order by排序键作为判断重复数据的唯一键
- 只有在合并分区的时候才会触发删除重复数据的逻辑
- 以数据分区为单位删除重复数据。当分区合并之后,同一分区内的重复数据会被删除;不同分区之间的重复数据不会被删除
- 在进行数据去重时,因为分区内的数据已经基于order by进行了排序,所以能够找到那些相邻的重复数据
- 数据去重策略有两种:
- 如果没有设置ver版本号,则保留同一组重复数据中的最后一行
- 如果设置了ver版本号,则保留同一组重复数据中ver字段取值最大的那一行
SummingMergeTree
专注场景:只需要查询数据的汇总结果,不关心明细数据
在合并分区的时候按照预先定义的条件聚合汇总数据,将同一份组下的多行数据汇总合并成一行
SummingMergeTree会根据ORDER BY表达式取值进行聚合操作
处理逻辑:
- 用ORDER BY排序键作为聚合数据的条件Key
- 只有在合并分区的时候才会触发汇总的逻辑
- 以数据分区为单位来聚合数据
- 如果在定义引擎时指定了columns汇总列,则SUM汇总这些字段;如果未指定,则聚合所有非主键的数值类型字段
- 在进行数据汇总时,因为分区内的数据已经基于ORDER BY排序,所以能够找到相邻且拥有相同聚合Key的数据
- 在汇总数据时,同一分区内,相同聚合Key的多行数据会合并成一行。
- 支持嵌套结构,但列字段名称必须以Map后缀结尾。默认以第一个字段作为聚合Key,除了第一个字段,任何名称为Key、Id或Type为后缀结尾的字段,都将和第一个字段一起组成复合Key
AggregatingMergeTree
些许立方体,在合并分区的时候,按照预先定义的条件聚合数据
在分区合并时,在每个数据分区内,会按照ORDER BY聚合。而使用何种聚合函数,以及针对哪些列字段计算,则是通过定义AggregateFunction数据类型实现的
例如:xxx属性 AggregateFunction(uniq,String)
AggregateFunction能够以二进制的形式存储中间结果。在写入数据时,需要调用*State函数;而在查询数据时,则需要调用相应的*Merge函数
常用于与物理视图结合使用,物化视图作为上层的查询视图
处理逻辑:
- 用ORDER BY排序键作为聚合数据的条件Key
- 使用AggregateFunction字段类型定义聚合函数的类型以及聚合的字段
- 只有在合并分区时才会触发聚合计算逻辑
- 以数据分区为单位来聚合数据。当分区合并时,同一数据分区内聚合Key相同的数据会被合并计算,而不同分区之间的数据则不会被计算
- 在进行数据计算时,因为分区内的数据已经基于ORDER BY排序,所以能够找到那些相邻且拥有相同聚合Key的数据
- 在聚合数据时,同一分区内,相同聚合Key的多行数据会合并成一行
- AggregateFunction类型的字段使用二进制存储
- AggregatingMergeTree通常作为物化视图的表引擎,与普通MergeTree搭配使用
CollapsingMergeTree
以增代删,合并时相互抵消
通过定义一个sign标记位字段,记录数据行的状态(1表示有效、-1表示数据需要删除)
注意点:
(1)折叠数据并不是实时发生的,如果不想查到被删的数据
方法一:optimize TABLE table_name FINAL 命令强制分区合并
方法二:改变查询方式,WHERE SUM(sign) > 0
(2)只有相同分区内的数据才有可能被折叠
(3)只有按照正常顺序写入才能被正常折叠(多线程可能会存在问题)
VersionedCollapsingMergeTree
是CollapsingMergeTree的变种,基于版本号,对数据的写入没有要求,在同一个分区,任意顺序的数据都能够完成折叠操作
其他常见类型表引擎
外部存储表引擎:能够直接读取其他系统数据
内存系列表引擎:能够充当临时存储载体或消息通道
日志文件表引擎:简单易用
接口系列表引擎:能够串联已有的数据表,起到粘合剂的作用
外部存储表引擎:
- HDFS:ENGINE=HDFS(hdfs_uri,format)
- MYSQL:ENGINE=MYSQL(‘host:port’,'database','table','user','password'[, replace_query, 'on_duplicate_clause'])
- JDBC: 除了MYSQL,也能够和PostgreSQL、SQLite和H2数据对接 ENGINE=JDBC('jdbc:url','database','table')
- KAFKA
kafka表引擎在执行查询之后会删除表内的数据,因此正确的kafka引擎使用需配合物化视图
- File:ENGINE=File(format)
内部类型表引擎:
- Memory:将Memory会作为测试表使用,写入之后,磁盘上不会创建数据文件
- Set:会被写到内存,然后被同步到磁盘文件中。不能直接使用SELECt对其进行查询,Set表引擎只能间接作为IN查询的右侧条件使用
SELECT xxx as a WHERE a IN set_1
- Join:与Set类似,但是既可以JOIN查询,也可以直接查询使用
- Buffer:并发数很高,导致MergeTree表的合并速度慢于速度,可以使用Buffer表缓解
日志类型:
场景:一次写入多次查询
特点:不支持索引、分区、不支持并发读写
- TinyLog:性能最低的表引擎(存储结构:数据文件、元数据),不支持分区,没有.mrk标记文件,无法支持.bin文件的并行读取
- StripeLog:(存储结构:data.bin、index.mrk、sizes.json),因为拥有标记文件,支持并行查询
- Log:Log表引擎中性能最好的表引擎,每个列拥有自己的.bin文件
接口类型:
- Merge:负责合并多个查询结果集。ENGINE=Merge(database,table_name)
- Dictionary:可以取代字典函数,字典内的数据被加载后,会全部保存到内存中。ENGINE=Dictionary(dict_name)
- Distributed:海量数据分片
其他类型:
- Live View:事件监听器,能够将一条SQL查询结果作为监控目标,当目标数据增加时,LiveView及时发出相应
CREATE LIVE VIEW xxx AS xxxSQLxxx
WATCH xxx
- Null:和Linux的/dev/null类似,如果用户向Null表吸入数据,永远不会存储,可以用来测试。ENGINE=Null
- URL:等价于HTTP客户端,当执行SELECT时,底层会转换为GET请求的远程调用;当执行INSERT查询的时候,会转换为POST请求
数据查询
ClickHouse对于SQL语句的解析是大小写敏感的
WITH子句(CTE 公共表达式)
- 定义变量
- 调用函数
- 定义子查询
- 在子查询重复使用WITH
FROM子句
FROM子句可以省略,省略后数据从虚拟组取数(system.one)
SAMPLE子句
数据量大且可抽样条件下可以使用
SAMPLE子句的采样机制是一种等幂设计,数据不发生变化的情况下,相同采样规则总能返回相同数据
用法:SMAPLE BY intHash32(xxx)
SAMPLE BY 所声明的表达式必须同时包含在主键的声明内
SAMPLE KEY必须是Int类型
- SAMPLE factor:采样因子
- SAMPLE rows:表示按样本数量采样,其中rows表示采样多少行数据
- SAMPLE factor OFFSET n:按因子系数和偏移量采样
ARRAY JOIN子句
ARRAY JOIN 子句允许在数据表的内部,与数组或嵌套类型的多字段进行JOIN操作,从而将一行数组展开为多行
连接精度:
ALL
ANY:匹配一条即返回
ASOF:模糊连接
a ASOF INNER JOIN b ON a.id=b.id AND a.time=b.time 类似:a.id=b.id AND a.time >= b.time
ASOF支持使用USING简写形式,最后一个字段被自动转换为asof_column模糊连接条件
a ASOF INNER JOIN b USING(id,time)
性能优化:
- 大表在左,小表在右
- 在大量维度属性补全的查询场景下,建议使用字典代替
- join_use_nulls参数可以指定空值的处理策略。0表示空值由默认数据类型的默认值填充;1表示由Null填充
PREWHERE基本原理:
只会读取pREWHERE指定的列字段数据,用于数据过滤的条件判断。待数据过滤之后再读取SELECT声明的列字段以补全其余属性
不会自动优化的场景:
- 使用了常量表达式
- 使用了默认值ALIAS类型的字段
- 包含了arryJoin、globalIn、globalNotIn或者indexHint的查询
- SELECT查询的列字段与WHERE谓词相同
- 使用了主键字段
GROUP BY
只有聚合函数的场景下可省略group by
- WITH ROLLUP:能够按照聚合键从右向左上卷数据,基于聚合函数依次生成份组小计和总计
- WITH CUBE:所有聚合键之间生成小计和总计
- WITH TOTALS:对所有数据输出总计
ORDER BY
- NULLS LAST:NULL值排在最后
- NULLS FIRST:NULL值排在最前
LIMIT BY:常用于TOP N场景,按照指定分组,最多返回前n行数据
LIMIT n BY express
LIMIT n OFFSET y BY express
LIMIT y,n BY express
LIMIT
LIMIT n
LIMIT n OFFSET m
LIMIT m,n
如果数据跨越了多个分区,在没有使用ORDER BY指定全局顺序的情况下,每次LIMIT查询所返回的数据有可能不同。如果对数据发挥值顺序敏感,则应该搭配ORDER BY一同使用
查询SQL执行计划:
clickhouse-client \
-h . dh7.nauu.com \
--send_logs_level=trace <<< 'SELECT * FROM hits_v1' > /dev/null'
副本与分片
副本与分片的关系:
如果想使用副本,就要使用到Replicated*引擎
Replicated 支持数据副本 + xxxTree
副本的特点:
(1)依赖Zookeeper,实现多个副本之间的同步
(2)表级别的副本
(3)多主架构
(4)Block数据块,写入具有原子性和唯一性
ReplicatedMergeTree原理
运营了ZK的能力,实现副本实例之间的协同,包括:主副本选举、副本状态感知、操作日志分发、任务队列和BlockID去重判断
在INSERT、MERGE分区和MUTATION操作时,都会涉及ZK通信,在通信过程中不涉及任何表数据的传输
ZK监听节点的分类:
(1)元数据
- /metadata:保存元数据信息
- /columns:保存列字段信息
- /replicas:保存副本名称
(2)判断标识
- /leader_election:用于主副本选举工作
- /blocks:记录Block数据块的Hash信息摘要
- /block_numbers:按照分区的写入顺序,以相同的顺序记录partition_id
- /quorum:记录quorum的数量,当至少quorum数量的副本写入成功后,整个操作才算成功
(3)操作日志
- /log:常规操作日志节点(INSERT、MERGE和DROP PARTITION),保存了副本需要执行的任务指令,每条指令名称以log-为前缀递增(CK会封装为LogEntry对象)
- /mutations: 当执行ALERT、DELETE和ALERT UPDATE时,每条指令以递增的数据形式保存(CK会封装MutationEntry对象)
- /replicas/{replica_name}/*:每个副本各自节点下的一组监听节点,用于指导副本在本地执行具体任务指令
协同过程
- INSERT 核心执行流程
(1)创建第一个副本实例
(2)创建第二个副本实例
(3)向第一个副本实例写入数据
(4)由第一个副本实例推送Log日志
(5)第二个副本实例拉取Log日志
(6)第二个副本实例向其他副本发起下载请求
(7)第一个副本实例响应数据下载
(8)第二个副本实例下载数据并完成本地写入
- MERGE 核心执行流程
无论MERGE操作从哪个副本发起,合并计划都会交由主副本来执行
(1)创建远程连接,尝试与主副本通信
(2)主副本接收通信
(3)由主副本指定MERGE计划并推送Log日志
(4)各个副本分别拉取Log日志
(5)各个副本分别在本地执行MERGE
- MUTATION核心执行流程
无论MUTATION操作从哪个副本发起,都会由主副本进行响应
(1)推送MUTATION日志
(2)所有副本实例各自监听MUTATION日志
(3)由主副本响应MUTATION日志并推送Log日志
(4)各个副本实例分别拉取Log日志
(5)各个副本分别在本地执行MUTATION
- ALTER核心执行流程
(1)修改共享元数据
(2)监听共享元数据变更并各自执行本地修改
(3)确认所有副本完成修改
- 分布式DDL核心执行流程
(1)推送DDL日志
(2)拉取日志并执行
(3)确认执行进度
Distributed分布式表:
一个分片表由两部分组成
- 本地表:通常以_local为后缀进行命名,本地表时承接数据的载体
- 分布式表:通常以_all为后缀进行命名
Distributed表引擎采用了读时检查机制,表结构不兼容,只有查询时才会抛出错误,在创建时并不会检查
ENGINE = Distributed(cluster, database,table [, sharding_key])
集群名、数据库、表名、分片键
分片的键需要返回一个整型的取
使用了ON CLUSTER语句的分布式DDL,意味着集群每个分片节点上,都会创建一张Distributed表,从其中任意一端都可以发起对所有分片的读、写请求
分片的选择:slot = shard_value % sum_weight
- 将数据写入到分片的核心逻辑
(1)在第一个分片节点写入本地分片数据
(2)第一个分片建立远端连接,准备发送远端分片数据
(3)第一个分片向远端发送数据
(4)第二个分片接收数据并写入本地
(5)由第一个分片确认完成写入
谁执行谁负责:调用到哪个节点,哪个节点负责切片,并发送数据给其他节点
如果分片中还有副本,有两种复制方式:
(1)借助Distributed表引擎,由它将数据写入副本(Distributed节点需要同时负责分片和副本的数据写入操作,很有可能成为写入的单点瓶颈)
(2)借助ReplicatedMergeTree表引擎实现副本数据分发(多个replica副本之间的数据复制会交给ReplicatedMergeTree自己处理,不再由Distributed负责)
多副本查询核心流程
- 多副本路由规则:
load_balancing = random / nearest_hostname / in_order / first_or_random
(1)random:默认的负载均衡(全局累加器errors_count,random会选择错误数量最少的replica)
(2)nearest_hostname:在上面的规则基础上,如果多个副本errors_count,会选择集群配置中host名称与当前host最相似的一个
(3)in_order:基于上面的规则,如果多个replica的errors_count计数相同,则按照集群配置中replica的定义顺序逐个选择
(4)first_or_random:基于上面的规则,如果多个replica的errors_count计数相同,首先会选择集群配置中第一个定义的replica
- 核心流程:
分布式SQL ------> 拆分本地子查询 ------> 结果合并
可能存在的问题:
- 分布式执行结果可能存在错误
- 用IN进行优化的分布式查询,查询请求会被放大N的平方倍,其中N等于集群内分片节点的数量(如果有10个分片节点,一次查询会导致100次查询请求)
GLOBAL查询优化:(使用了内存表临时保存数据)
例如:SELECT uniq(id) FROM test_query_all WHERE repo=100 AND id GLOBAL IN (SELECT id FROM test_query_all WHERE repo=200)
过程如下:
(1)将IN子句单独提出,发起一次分布式查询
(2)将分布式表转local本地表后,分别在本地和远端分片执行查询
(3)将IN子句查询的结果进行汇总,并放入一张临时的内存表进行保存
(4)将内存表发送到远端分片节点
(5)将分布式表转为本地表后,开始执行完成的SQL语句,IN子句直接使用临时内存表数据
熔断机制:
- 根据事件周期的累积用户量熔断
- 根据单词查询的用量熔断
数据备份:
1. 导出文件备份
2. 通过快照表备份(建立一张与原表结构相同的数据表,然后再使用INSERT INTO SELECT句式,点对点将数据从原表写入备份表)
3. 【分区备份】FREEZE:ALTER TABLE tb_name FREEZE PARTITION partiton_expr,统一保存到CK根目录/shadow/N子目录下;还原需借助ATTACH
4. 【基于ReplicatedMergeTree的分区备份】FETCH:ALTER TABLE tb_name FETCH PARTITION partition_expr FROM zk_path
FETCH通过指定zk_path找到
ReplicatedMergeTree的所有副本实例,然乎从中国选择最合适的副本,并下载相应的分区数据
服务表:SYSTEM系统表中,三个支撑了CK指标查询:
(1)metrics:查询总次数,正在发生的合并操作总次数
(2)events:累积信息汇总
(3)asynchronous_metrics:后台异步信息,包括:当前分配的内存、执行队列中的任务数量等
查询日志:
(1)query_log:记录了已经执行的查询记录
(2)query_thread_log:记录了所有线程的执行查询的信息
(3)part_log:记录了MergeTree系统表引擎的分区操作日志
(4)text_log:记录了CK运行中产生的一系列打印日志,包括INFO、DEBUG、Trace
(5)metric_log:将SYSTEM中的metrics和events表数据汇总在一起