MySQL ClickHouse常用表引擎超详细讲解

山德广广

表引擎

表引擎作用： 数据的存储方式和位置
支持哪些查询以及如何支持
并发数据访问
索引的使用（如果存在）
是否可以执行多线程请求
数据复制参数
常见表引擎家族说明索引备注TinyLogLog Family以列文件的形式保存在硬盘
数据写入时，追加到文件末尾不支持可用于存储小批量处理的中间数据Memory其它数据以未压缩的原始形式直接保存在内存不支持适用于少量数据的高性能查询MergeTreeMergeTree Family支持 列式存储、分区、稀疏索引、二级索引…支持单节点ClickHouse实例的默认表引擎
合并树家族

合并树家族特点：
快速插入数据并进行后续的后台数据处理
支持数据复制
支持分区
支持稀疏索引
稀疏索引原理

稀疏索引占用空间小，范围批量查询快，但单点查询较慢

MergeTree

• 擅长 插入极大量的数据到一张表
  
• 数据 能以 数据片段的形式 一个接一个地快速写入，数据片段 在后台 按一定的规则进行合并
  

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

复制代码

关键词简述ENGINE引擎ORDER BY数据排序规则PARTITION BY分区PRIMARY KEY索引规则TTL数据生命周期SETTINGS其它设置
排序键

1. ORDER BY

复制代码

（必选项）
规定了分区内的数据按照哪些字段进行按序存储
如果不需要排序，就用

1. ORDER BY tuple()

复制代码

此情况下，数据顺序是根据插入顺序
如果想要按

1. INSERT ... SELECT

复制代码

的数据顺序来存储，就设置

1. max_insert_threads=1

复制代码

若想 按数据存储顺序查出数据，可用 单线程查询
对于有序数据，数据一致性越高，压缩效率越高

主键

1. PRIMARY KEY

复制代码

（可选项）
作用：为列数据提供稀疏索引（不是唯一约束），提升列查询效率
默认情况下，主键与排序键相同；通常不需要显式

1. PRIMARY KEY

复制代码

子句，除非主键≠排序键
要求：主键列必须是排序列的前缀
例如

1. ORDER BY (a,b)

复制代码

则

1. PRIMARY KEY

复制代码

后可以是

1. (a,b)

复制代码

或

1. (a)

复制代码

sparse index

分区

1. PARTITION BY

复制代码

分区（可选项）
分区作用：缩小扫描范围，优化查询速度
并行：分区后，面对涉及跨分区的查询统计，会以分区为单位并行处理
如果不填：只会使用一个分区
数据写入与分区合并：
任何一个批次的数据写入 都会产生一个临时分区，不会纳入任何一个已有的分区。
写入后，过一段时间（约10多分钟），会自动执行合并操作，把临时分区的数据合并
可用

1. OPTIMIZE TABLE 表名 [FINAL]

复制代码

主动执行合并
通常不需要使用分区键。使用时，不建议使用比月更细粒度的分区键
分区过多=>(列式)查询时扫描文件过多=>性能低

1. -- 建表
   
2. DROP TABLE IF EXISTS t1;
   
3. CREATE TABLE t1(
   
4.   uid UInt32,
   
5.   sku_id String,
   
6.   total_amount Decimal(9,2),
   
7.   create_time Datetime
   
8. ) ENGINE = MergeTree()
   
9. PARTITION BY toYYYYMMDD(create_time)
   
10. PRIMARY KEY (uid)
    
11. ORDER BY (uid,sku_id);
    
12. -- 插数据2次
    
13. INSERT INTO t1 VALUES
    
14. (1,'sku1',1.00,'2020-06-01 12:00:00'),
    
15. (2,'sku1',9.00,'2020-06-02 13:00:00'),
    
16. (3,'sku2',6.00,'2020-06-02 12:00:00');
    
17. INSERT INTO t1 VALUES
    
18. (1,'sku1',1.00,'2020-06-01 12:00:00'),
    
19. (2,'sku1',9.00,'2020-06-02 13:00:00'),
    
20. (3,'sku2',6.00,'2020-06-02 12:00:00');
    
21. -- 插完后立即插，会发现数据写入临时分区，还未进行自动合并
    
22. SELECT * FROM t1;
    
23. ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
    
24. │   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
    
25. └─────┴────────┴──────────────┴─────────────────────┘
    
26. ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
    
27. │   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
    
28. └─────┴────────┴──────────────┴─────────────────────┘
    
29. ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
    
30. │   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
    
31. │   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
    
32. └─────┴────────┴──────────────┴─────────────────────┘
    
33. ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
    
34. │   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
    
35. │   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
    
36. └─────┴────────┴──────────────┴─────────────────────┘
    
37. -- 手动合并分区
    
38. OPTIMIZE TABLE t1 FINAL;
    
39. -- 再次查询，会看到分区已经合并
    
40. SELECT * FROM t1;
    
41. ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
    
42. │   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
    
43. │   1 │ sku1   │         1.00 │ 2020-06-01 12:00:00 │
    
44. └─────┴────────┴──────────────┴─────────────────────┘
    
45. ┌─uid─┬─sku_id─┬─total_amount─┬─────────create_time─┐
    
46. │   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
    
47. │   2 │ sku1   │         9.00 │ 2020-06-02 13:00:00 │
    
48. │   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
    
49. │   3 │ sku2   │         6.00 │ 2020-06-02 12:00:00 │
    
50. └─────┴────────┴──────────────┴─────────────────────┘

复制代码

数据生命周期

TTL：Time To Live
列TTL
当列中的值过期时，ClickHouse将用列数据类型的默认值替换它们
TTL子句不能用于键列
表TTL
当数据部分中的所有列值都过期，可以删除数据

1. DROP TABLE IF EXISTS t1;
   
2. CREATE TABLE t1 (
   
3.     d DateTime,
   
4.     -- 列生命周期（5秒）
   
5.     a Int TTL d + INTERVAL 5 SECOND
   
6. )ENGINE = MergeTree()
   
7. ORDER BY d
   
8. -- 表生命周期（1分钟）
   
9. TTL d + INTERVAL 1 MINUTE DELETE;
   
10. -- 插数据
    
11. INSERT INTO t1 VALUES (now(),2);
    
12. -- 立即查
    
13. SELECT * FROM t1;
    
14. ┌───────────────────d─┬─a─┐
    
15. │ 2022-11-01 14:39:17 │ 2 │
    
16. └─────────────────────┴───┘
    
17. -- 5秒后刷新并查询
    
18. OPTIMIZE TABLE t1 FINAL;
    
19. SELECT * FROM t1;
    
20. ┌───────────────────d─┬─a─┐
    
21. │ 2022-11-01 14:39:17 │ 0 │
    
22. └─────────────────────┴───┘
    
23. -- 1分钟后查
    
24. OPTIMIZE TABLE t1 FINAL;
    
25. SELECT * FROM t1;
    
26. -- 过期数据行被删除

复制代码

立即查，TTL列值为

1. 2

复制代码

，5秒后查值为

1. 0

复制代码

，1分钟后查此数据被删除

其它设置

常见设置说明默认值备注index_granularity索引粒度。索引中相邻的『标记』间的数据行数8192通常不用改index_granularity_bytes索引粒度，以字节为单位10Mb数据量很大 且 数据一致性很高 时 可考虑 调大索引粒度min_index_granularity_bytes允许的最小数据粒度1024b用于防止 添加索引粒度很低的表
ReplacingMergeTree

ReplacingMergeTree具有去重功能：分区内按排序键去重
数据的去重只会在数据合并期间进行
合并会在后台一个不确定的时间进行
可用

1. OPTIMIZE

复制代码

语句发起计划外的合并，但会引发数据的大量读写
ReplacingMergeTree适用于在后台清除重复的数据，但是不保证没有重复数据出现

1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
   
2. (
   
3.     name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
   
4.     name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
   
5.     ...
   
6. ) ENGINE = ReplacingMergeTree([ver])
   
7. [PARTITION BY expr]
   
8. [ORDER BY expr]
   
9. [SAMPLE BY expr]
   
10. [SETTINGS name=value, ...]

复制代码

1. ver

复制代码

是版本列，是可选参数，类型可为

1. UInt

复制代码

、

1. Date

复制代码

、

1. DateTime

复制代码

在数据合并时，ReplacingMergeTree从相同排序键的行中选择一行留下：
如果

1. ver

复制代码

列未指定，就保留最后一条
如果

1. ver

复制代码

列已指定，就保留

1. ver

复制代码

值最大的版本

1. DROP TABLE IF EXISTS t1;
   
2. CREATE TABLE t1(
   
3.   uid UInt32,
   
4.   sku_id String,
   
5.   create_time Datetime
   
6. ) ENGINE = ReplacingMergeTree(create_time)
   
7. PARTITION BY sku_id
   
8. ORDER BY (uid);
   
9. INSERT INTO t1 VALUES
   
10. (1,'s1','2022-06-01 00:00:00'),
    
11. (1,'s1','2022-06-02 11:11:11'),
    
12. (1,'s2','2022-06-02 13:00:00'),
    
13. (2,'s2','2022-06-02 12:12:12'),
    
14. (2,'s2','2022-06-02 00:00:00');
    
15. SELECT * FROM t1;
    
16. -- 插了5条数据，去重了，查出来只有3条，不同分区没有去重
    
17. ┌─uid─┬─sku_id─┬─────────create_time─┐
    
18. │   1 │ s1     │ 2022-06-02 11:11:11 │
    
19. └─────┴────────┴─────────────────────┘
    
20. ┌─uid─┬─sku_id─┬─────────create_time─┐
    
21. │   1 │ s2     │ 2022-06-02 13:00:00 │
    
22. │   2 │ s2     │ 2022-06-02 12:12:12 │
    
23. └─────┴────────┴─────────────────────┘

复制代码

SummingMergeTree

适用场景：不需要查询明细，只查询 按维度聚合求和 的场景
原理：预聚合
优点：加快聚合求和查询、节省空间
语法：

1. SummingMergeTree([columns])

复制代码

1. columns

复制代码

是可选参数，必须是数值类型，并且不可位于主键中
所选列将会被预聚合求和；若缺省，则所有非维度数字列将会被聚合求和

1. DROP TABLE IF EXISTS t1;
   
2. CREATE TABLE t1(
   
3.   uid UInt32,
   
4.   amount1 Decimal(9,2),
   
5.   amount2 Decimal(9,2)
   
6. ) ENGINE = SummingMergeTree(amount1)
   
7. ORDER BY (uid);
   
8. INSERT INTO t1 VALUES (1,1.00,2.00),(1,9.00,8.00);
   
9. SELECT * FROM t1;
   
10. ┌─uid─┬─amount1─┬─amount2─┐
    
11. │   1 │   10.00 │    2.00 │
    
12. └─────┴─────────┴─────────┘
    
13. INSERT INTO t1 VALUES (1,1.11,2.22),(2,5.00,5.00);
    
14. SELECT * FROM t1;
    
15. ┌─uid─┬─amount1─┬─amount2─┐
    
16. │   1 │   10.00 │    2.00 │
    
17. └─────┴─────────┴─────────┘
    
18. ┌─uid─┬─amount1─┬─amount2─┐
    
19. │   1 │    1.11 │    2.22 │
    
20. │   2 │    5.00 │    5.00 │
    
21. └─────┴─────────┴─────────┘
    
22. OPTIMIZE TABLE t1;
    
23. SELECT * FROM t1;
    
24. ┌─uid─┬─amount1─┬─amount2─┐
    
25. │   1 │   11.11 │    2.00 │
    
26. │   2 │    5.00 │    5.00 │
    
27. └─────┴─────────┴─────────┘

复制代码

图示

1. amount1

复制代码

会按照

1. uid

复制代码

聚合求和，而

1. amount2

复制代码

是第一条插入

1. uid

复制代码

时的值
注意
不能直接

1. SELECT amount1 FROM t1 WHERE 维度

复制代码

来得到汇总值，因为有些临时明细数据还没来得及聚合
所以仍要

1. SELECT SUM(amount1)

复制代码

日志家族

• 适用于数据量较少的表（通常小于1百万行）
  
• 数据存储在硬盘上，可存储到HDFS
  
• 写入时将数据追加到文件末尾
  
• 在
  1. INSERT

  复制代码
  期间，表会被锁定
  
• 支持并发查询
  
• 不支持索引
  
• 如果服务器异常关闭导致写操作中断，就会得数据损坏
  

1. ENGINE = Log()

复制代码

其它

内存引擎

1. ENGINE = Memory()

复制代码

• 数据以原始形态保存在内存中，服务器关闭就会使数据消失
  
• 读写操作不会相互阻塞
  
• 不支持索引
  
• 阅读是并行的
  

到此这篇关于MySQL ClickHouse常用表引擎超详细讲解的文章就介绍到这了,更多相关MySQL ClickHouse内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/article/266473.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！