[var]
表引擎作用： 数据的存储方式和位置
支持哪些查询以及如何支持
并发数据访问
索引的使用（如果存在）
是否可以执行多线程请求
数据复制参数
单节点ClickHouse实例的默认表引擎
[var]
合并树家族特点：
快速插入数据并进行后续的后台数据处理
支持数据复制
支持分区
支持稀疏索引
稀疏索引原理
稀疏索引占用空间小，范围批量查询快，但单点查询较慢
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, …]
其它设置
排序键
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 t1;
CREATE TABLE t1(
uid UInt32,
sku_id String,
total_amount Decimal(9,2),
create_time Datetime
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY (uid)
ORDER BY (uid,sku_id);
— 插数据2次
INSERT INTO t1 VALUES
(1,’sku1′,1.00,’2020-06-01 12:00:00′),
(2,’sku1′,9.00,’2020-06-02 13:00:00′),
(3,’sku2′,6.00,’2020-06-02 12:00:00′);
INSERT INTO t1 VALUES
(1,’sku1′,1.00,’2020-06-01 12:00:00′),
(2,’sku1′,9.00,’2020-06-02 13:00:00′),
(3,’sku2′,6.00,’2020-06-02 12:00:00′);
— 插完后立即插，会发现数据写入临时分区，还未进行自动合并
SELECT * FROM t1;
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│ 1 │ sku1 │ 1.00 │ 2020-06-01 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│ 1 │ sku1 │ 1.00 │ 2020-06-01 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│ 2 │ sku1 │ 9.00 │ 2020-06-02 13:00:00 │
│ 3 │ sku2 │ 6.00 │ 2020-06-02 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│ 2 │ sku1 │ 9.00 │ 2020-06-02 13:00:00 │
│ 3 │ sku2 │ 6.00 │ 2020-06-02 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
— 手动合并分区
OPTIMIZE TABLE t1 FINAL;
— 再次查询，会看到分区已经合并
SELECT * FROM t1;
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│ 1 │ sku1 │ 1.00 │ 2020-06-01 12:00:00 │
│ 1 │ sku1 │ 1.00 │ 2020-06-01 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
│ 2 │ sku1 │ 9.00 │ 2020-06-02 13:00:00 │
│ 2 │ sku1 │ 9.00 │ 2020-06-02 13:00:00 │
│ 3 │ sku2 │ 6.00 │ 2020-06-02 12:00:00 │
│ 3 │ sku2 │ 6.00 │ 2020-06-02 12:00:00 │
└─────┴────────┴──────────────┴─────────────────────┘
数据生命周期
TTL：Time To Live
列TTL
当列中的值过期时，ClickHouse将用列数据类型的默认值替换它们
TTL子句不能用于键列
表TTL
当数据部分中的所有列值都过期，可以删除数据
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
d DateTime,
— 列生命周期（5秒）
a Int TTL d + INTERVAL 5 SECOND
)ENGINE = MergeTree()
ORDER BY d
— 表生命周期（1分钟）
TTL d + INTERVAL 1 MINUTE DELETE;
— 插数据
INSERT INTO t1 VALUES (now(),2);
— 立即查
SELECT * FROM t1;
┌───────────────────d─┬─a─┐
│ 2022-11-01 14:39:17 │ 2 │
└─────────────────────┴───┘
— 5秒后刷新并查询
OPTIMIZE TABLE t1 FINAL;
SELECT * FROM t1;
┌───────────────────d─┬─a─┐
│ 2022-11-01 14:39:17 │ 0 │
└─────────────────────┴───┘
— 1分钟后查
OPTIMIZE TABLE t1 FINAL;
SELECT * FROM t1;
— 过期数据行被删除
立即查，TTL列值为 2 ，5秒后查值为 0 ，1分钟后查此数据被删除
其它设置
用于防止 添加索引粒度很低的表
ReplacingMergeTree
ReplacingMergeTree具有去重功能：分区内按排序键去重
数据的去重只会在数据合并期间进行
合并会在后台一个不确定的时间进行
可用 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, …]
ver 是版本列，是可选参数，类型可为 UInt 、 Date 、 DateTime 在数据合并时，ReplacingMergeTree从相同排序键的行中选择一行留下：
如果 ver 列未指定，就保留最后一条
如果 ver 列已指定，就保留 ver 值最大的版本
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
uid UInt32,
sku_id String,
create_time Datetime
) ENGINE = ReplacingMergeTree(create_time)
PARTITION BY sku_id
ORDER BY (uid);
INSERT INTO t1 VALUES
(1,’s1′,’2022-06-01 0′),
(1,’s1′,’2022-06-02 11:11:11′),
(1,’s2′,’2022-06-02 13:00:00′),
(2,’s2′,’2022-06-02 12:12:12′),
(2,’s2′,’2022-06-02 0′);
SELECT * FROM t1;
— 插了5条数据，去重了，查出来只有3条，不同分区没有去重
┌─uid─┬─sku_id─┬─────────create_time─┐
│ 1 │ s1 │ 2022-06-02 11:11:11 │
└─────┴────────┴─────────────────────┘
┌─uid─┬─sku_id─┬─────────create_time─┐
│ 1 │ s2 │ 2022-06-02 13:00:00 │
│ 2 │ s2 │ 2022-06-02 12:12:12 │
└─────┴────────┴─────────────────────┘
SummingMergeTree
适用场景：不需要查询明细，只查询 按维度聚合求和 的场景
原理：预聚合
优点：加快聚合求和查询、节省空间
语法： SummingMergeTree([columns])
columns 是可选参数，必须是数值类型，并且不可位于主键中
所选列将会被预聚合求和；若缺省，则所有非维度数字列将会被聚合求和
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
uid UInt32,
amount1 Decimal(9,2),
amount2 Decimal(9,2)
) ENGINE = SummingMergeTree(amount1)
ORDER BY (uid);
INSERT INTO t1 VALUES (1,1.00,2.00),(1,9.00,8.00);
SELECT * FROM t1;
┌─uid─┬─amount1─┬─amount2─┐
│ 1 │ 10.00 │ 2.00 │
└─────┴─────────┴─────────┘
INSERT INTO t1 VALUES (1,1.11,2.22),(2,5.00,5.00);
SELECT * FROM t1;
┌─uid─┬─amount1─┬─amount2─┐
│ 1 │ 10.00 │ 2.00 │
└─────┴─────────┴─────────┘
┌─uid─┬─amount1─┬─amount2─┐
│ 1 │ 1.11 │ 2.22 │
│ 2 │ 5.00 │ 5.00 │
└─────┴─────────┴─────────┘
OPTIMIZE TABLE t1;
SELECT * FROM t1;
┌─uid─┬─amount1─┬─amount2─┐
│ 1 │ 11.11 │ 2.00 │
│ 2 │ 5.00 │ 5.00 │
└─────┴─────────┴─────────┘
图示 amount1 会按照 uid 聚合求和，而 amount2 是第一条插入 uid 时的值
注意
不能直接 SELECT amount1 FROM t1 WHERE 维度 来得到汇总值，因为有些临时明细数据还没来得及聚合
所以仍要 SELECT SUM(amount1)
[var]
适用于数据量较少的表（通常小于1百万行） 数据存储在硬盘上，可存储到HDFS 写入时将数据追加到文件末尾 在 INSERT 期间，表会被锁定 支持并发查询 不支持索引 如果服务器异常关闭导致写操作中断，就会得数据损坏ENGINE = Log()
[var]
内存引擎
ENGINE = Memory()
数据以原始形态保存在内存中，服务器关闭就会使数据消失 读写操作不会相互阻塞 不支持索引 阅读是并行的查看更多关于MySQL ClickHouse常用表引擎超详细讲解的详细内容...