目录
- 表引擎
- 合并树家族
- MergeTree
- 排序键
- 主键
- 分区
- 数据生命周期
- 其它设置
- ReplacingMergeTree
- SummingMergeTree
- 日志家族
- 其它
表引擎
表引擎作用： 数据的存储方式和位置
支持哪些查询以及如何支持
并发数据访问
索引的使用（如果存在）
是否可以执行多线程请求
数据复制参数
常见表引擎 | 家族 | 说明 | 索引 | 备注 |
TinyLog | Log Family | 以列文件的形式保存在硬盘 数据写入时，追加到文件末尾 | 不支持 | 可用于存储小批量处理的中间数据 |
Memory | 其它 | 数据以未压缩的原始形式直接保存在内存 | 不支持 | 适用于少量数据的高性能查询 |
MergeTree | MergeTree Family | 支持 列式存储、分区、稀疏索引、二级索引… | 支持 | 单节点ClickHouse实例的默认表引擎 |
合并树家族
合并树家族特点：
快速插入数据并进行后续的后台数据处理
支持数据复制
支持分区
支持稀疏索引
稀疏索引原理
稀疏索引占用空间小，范围批量查询快，但单点查询较慢
MergeTree
- 擅长 插入极大量的数据到一张表
- 数据 能以 数据片段的形式 一个接一个地快速写入，数据片段 在后台 按一定的规则进行合并
| CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]( | |
| name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1], | |
| name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2], | |
| ... | |
| INDEX index_name expr1 TYPE type1(...) GRANULARITY value1, | |
| INDEX index_name 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, ...] |
关键词 | 简述 |
ENGINE | 引擎 |
ORDER BY | 数据排序规则 |
PARTITION BY | 分区 |
PRIMARY KEY | 索引规则 |
TTL | 数据生命周期 |
SETTINGS | 其它设置 |
排序键
ORDER BY（必选项）
规定了分区内的数据按照哪些字段进行按序存储
如果不需要排序，就用ORDER BY tuple()
此情况下，数据顺序是根据插入顺序
如果想要按INSERT ... SELECT的数据顺序来存储，就设置max_insert_threads=1
若想 按数据存储顺序查出数据，可用 单线程查询
对于有序数据，数据一致性越高，压缩效率越高
主键
PRIMARY KEY（可选项）
作用：为列数据提供稀疏索引（不是唯一约束），提升列查询效率
默认情况下，主键与排序键相同；通常不需要显式PRIMARY KEY子句，除非主键≠排序键
要求：主键列必须是排序列的前缀
例如ORDER BY (a,b)则PRIMARY KEY后可以是(a,b)或(a)
sparse index
分区
PARTITION BY分区（可选项）
分区作用：缩小扫描范围，优化查询速度
并行：分区后，面对涉及跨分区的查询统计，会以分区为单位并行处理
如果不填：只会使用一个分区
数据写入与分区合并：
任何一个批次的数据写入 都会产生一个临时分区，不会纳入任何一个已有的分区。
写入后，过一段时间（约10多分钟），会自动执行合并操作，把临时分区的数据合并
可用OPTIMIZE TABLE 表名 [FINAL]主动执行合并
通常不需要使用分区键。使用时，不建议使用比月更细粒度的分区键
分区过多=>(列式)查询时扫描文件过多=>性能低
| -- 建表 | |
| DROP TABLE IF EXISTS t; | |
| CREATE TABLE t( | |
| uid UInt, | |
| sku_id String, | |
| total_amount Decimal(,2), | |
| create_time Datetime | |
| ) ENGINE = MergeTree() | |
| PARTITION BY toYYYYMMDD(create_time) | |
| PRIMARY KEY (uid) | |
| ORDER BY (uid,sku_id); | |
| -- 插数据次 | |
| INSERT INTO t VALUES | |
| (,'sku1',1.00,'2020-06-01 12:00:00'), | |
| (,'sku1',9.00,'2020-06-02 13:00:00'), | |
| (,'sku2',6.00,'2020-06-02 12:00:00'); | |
| INSERT INTO t VALUES | |
| (,'sku1',1.00,'2020-06-01 12:00:00'), | |
| (,'sku1',9.00,'2020-06-02 13:00:00'), | |
| (,'sku2',6.00,'2020-06-02 12:00:00'); | |
| -- 插完后立即插，会发现数据写入临时分区，还未进行自动合并 | |
| SELECT * FROM t; | |
| ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐ | |
| │ │ sku1 │ 1.00 │ 2020-06-01 12:00:00 │ | |
| └─────┴────────┴──────────────┴─────────────────────┘ | |
| ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐ | |
| │ │ sku1 │ 1.00 │ 2020-06-01 12:00:00 │ | |
| └─────┴────────┴──────────────┴─────────────────────┘ | |
| ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐ | |
| │ │ sku1 │ 9.00 │ 2020-06-02 13:00:00 │ | |
| │ │ sku2 │ 6.00 │ 2020-06-02 12:00:00 │ | |
| └─────┴────────┴──────────────┴─────────────────────┘ | |
| ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐ | |
| │ │ sku1 │ 9.00 │ 2020-06-02 13:00:00 │ | |
| │ │ sku2 │ 6.00 │ 2020-06-02 12:00:00 │ | |
| └─────┴────────┴──────────────┴─────────────────────┘ | |
| -- 手动合并分区 | |
| OPTIMIZE TABLE t FINAL; | |
| -- 再次查询，会看到分区已经合并 | |
| SELECT * FROM t; | |
| ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐ | |
| │ │ sku1 │ 1.00 │ 2020-06-01 12:00:00 │ | |
| │ │ sku1 │ 1.00 │ 2020-06-01 12:00:00 │ | |
| └─────┴────────┴──────────────┴─────────────────────┘ | |
| ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐ | |
| │ │ sku1 │ 9.00 │ 2020-06-02 13:00:00 │ | |
| │ │ sku1 │ 9.00 │ 2020-06-02 13:00:00 │ | |
| │ │ sku2 │ 6.00 │ 2020-06-02 12:00:00 │ | |
| │ │ sku2 │ 6.00 │ 2020-06-02 12:00:00 │ | |
| └─────┴────────┴──────────────┴─────────────────────┘ |
数据生命周期
TTL：Time To Live
列TTL
当列中的值过期时，ClickHouse将用列数据类型的默认值替换它们
TTL子句不能用于键列
表TTL
当数据部分中的所有列值都过期，可以删除数据
| DROP TABLE IF EXISTS t; | |
| CREATE TABLE t ( | |
| d DateTime, | |
| -- 列生命周期（秒） | |
| a Int TTL d + INTERVAL SECOND | |
| )ENGINE = MergeTree() | |
| ORDER BY d | |
| -- 表生命周期（分钟） | |
| TTL d + INTERVAL MINUTE DELETE; | |
| -- 插数据 | |
| INSERT INTO t VALUES (now(),2); | |
| -- 立即查 | |
| SELECT * FROM t; | |
| ┌───────────────────d─┬─a─┐ | |
| │-11-01 14:39:17 │ 2 │ | |
| └─────────────────────┴───┘ | |
| --秒后刷新并查询 | |
| OPTIMIZE TABLE t FINAL; | |
| SELECT * FROM t; | |
| ┌───────────────────d─┬─a─┐ | |
| │-11-01 14:39:17 │ 0 │ | |
| └─────────────────────┴───┘ | |
| --分钟后查 | |
| OPTIMIZE TABLE t FINAL; | |
| SELECT * FROM t; | |
| -- 过期数据行被删除 |
立即查，TTL列值为2，5秒后查值为0，1分钟后查此数据被删除
其它设置
常见设置 | 说明 | 默认值 | 备注 |
index_granularity | 索引粒度。索引中相邻的『标记』间的数据行数 | 8192 | 通常不用改 |
index_granularity_bytes | 索引粒度，以字节为单位 | 10Mb | 数据量很大 且 数据一致性很高 时 可考虑 调大索引粒度 |
min_index_granularity_bytes | 允许的最小数据粒度 | 1024b | 用于防止 添加索引粒度很低的表 |
ReplacingMergeTree
ReplacingMergeTree具有去重功能：分区内按排序键去重
数据的去重只会在数据合并期间进行
合并会在后台一个不确定的时间进行
可用OPTIMIZE语句发起计划外的合并，但会引发数据的大量读写
ReplacingMergeTree适用于在后台清除重复的数据，但是不保证没有重复数据出现
| CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] | |
| ( | |
| name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], | |
| name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], | |
| ... | |
| ) ENGINE = ReplacingMergeTree([ver]) | |
| [PARTITION BY expr] | |
| [ORDER BY expr] | |
| [SAMPLE BY expr] | |
| [SETTINGS name=value, ...] |
ver是版本列，是可选参数，类型可为UInt、Date、DateTime在数据合并时，ReplacingMergeTree从相同排序键的行中选择一行留下：
如果ver列未指定，就保留最后一条
如果ver列已指定，就保留ver值最大的版本
| DROP TABLE IF EXISTS t; | |
| CREATE TABLE t( | |
| uid UInt, | |
| sku_id String, | |
| create_time Datetime | |
| ) ENGINE = ReplacingMergeTree(create_time) | |
| PARTITION BY sku_id | |
| ORDER BY (uid); | |
| INSERT INTO t VALUES | |
| (,'s1','2022-06-01 00:00:00'), | |
| (,'s1','2022-06-02 11:11:11'), | |
| (,'s2','2022-06-02 13:00:00'), | |
| (,'s2','2022-06-02 12:12:12'), | |
| (,'s2','2022-06-02 00:00:00'); | |
| SELECT * FROM t; | |
| -- 插了条数据，去重了，查出来只有3条，不同分区没有去重 | |
| ┌─uid─┬─sku_id─┬─────────create_time─┐ | |
| │ │ s1 │ 2022-06-02 11:11:11 │ | |
| └─────┴────────┴─────────────────────┘ | |
| ┌─uid─┬─sku_id─┬─────────create_time─┐ | |
| │ │ s2 │ 2022-06-02 13:00:00 │ | |
| │ │ s2 │ 2022-06-02 12:12:12 │ | |
| └─────┴────────┴─────────────────────┘ |
SummingMergeTree
适用场景：不需要查询明细，只查询 按维度聚合求和 的场景
原理：预聚合
优点：加快聚合求和查询、节省空间
语法：SummingMergeTree([columns])
columns是可选参数，必须是数值类型，并且不可位于主键中
所选列将会被预聚合求和；若缺省，则所有非维度数字列将会被聚合求和
| DROP TABLE IF EXISTS t; | |
| CREATE TABLE t( | |
| uid UInt, | |
| amount Decimal(9,2), | |
| amount Decimal(9,2) | |
| ) ENGINE = SummingMergeTree(amount) | |
| ORDER BY (uid); | |
| INSERT INTO t VALUES (1,1.00,2.00),(1,9.00,8.00); | |
| SELECT * FROM t; | |
| ┌─uid─┬─amount─┬─amount2─┐ | |
| │ │ 10.00 │ 2.00 │ | |
| └─────┴─────────┴─────────┘ | |
| INSERT INTO t VALUES (1,1.11,2.22),(2,5.00,5.00); | |
| SELECT * FROM t; | |
| ┌─uid─┬─amount─┬─amount2─┐ | |
| │ │ 10.00 │ 2.00 │ | |
| └─────┴─────────┴─────────┘ | |
| ┌─uid─┬─amount─┬─amount2─┐ | |
| │ │ 1.11 │ 2.22 │ | |
| │ │ 5.00 │ 5.00 │ | |
| └─────┴─────────┴─────────┘ | |
| OPTIMIZE TABLE t; | |
| SELECT * FROM t; | |
| ┌─uid─┬─amount─┬─amount2─┐ | |
| │ │ 11.11 │ 2.00 │ | |
| │ │ 5.00 │ 5.00 │ | |
| └─────┴─────────┴─────────┘ |
图示amount1会按照uid聚合求和，而amount2是第一条插入uid时的值
注意
不能直接SELECT amount1 FROM t1 WHERE 维度来得到汇总值，因为有些临时明细数据还没来得及聚合
所以仍要SELECT SUM(amount1)
日志家族
- 适用于数据量较少的表（通常小于1百万行）
- 数据存储在硬盘上，可存储到HDFS
- 写入时将数据追加到文件末尾
- 在INSERT期间，表会被锁定
- 支持并发查询
- 不支持索引
- 如果服务器异常关闭导致写操作中断，就会得数据损坏
ENGINE = Log()
其它
内存引擎
ENGINE = Memory()
- 数据以原始形态保存在内存中，服务器关闭就会使数据消失
- 读写操作不会相互阻塞
- 不支持索引
- 阅读是并行的