clickhouse引擎--MergeTree引擎一.MergeTree引擎

81 阅读 0 评论 54 点赞

我是靠谱客的博主超帅帽子，这篇文章主要介绍clickhouse引擎--MergeTree引擎一.MergeTree引擎，现在分享给大家，希望可以做个参考。

MergeTree引擎

一.MergeTree引擎

一.MergeTree引擎

MergeTree系列的表引擎是clickhouse数据存储功能的核心，它们提供了用于弹性和高性能数据检索的大多数功能：列存储，自定义分区，稀疏的主索引，辅助数据跳过索引等

主要特点：

存储按主键排序的数据（必须有order by（主键,…））
如果指定了分区键，则可以使用分区
数据复制支持
数据采样支持

复制代码

create table tb_merge_tree(
uid UInt8,
name String,
birthday Date,
city String,
gender String
)engine=MergeTree()
primary key uid
order by (uid,birthday);
insert into tb_merge_tree values
(1,'zs',toDate(now()),'HZ','M'),
(2,'ls','2021-09-27','HZ','M'),
(3,'ww','2021-08-18','SH','F'),
(4,'zl','1999-09-27','JS','F');
select * from tb_merge_tree;
┌─uid─┬─name─┬───birthday─┬─city─┬─gender─┐
│
1 │ zs
│ 2021-08-30 │ HZ
│ M
│
│
2 │ ls
│ 2021-09-27 │ HZ
│ M
│
│
3 │ ww
│ 2021-08-18 │ SH
│ F
│
│
4 │ zl
│ 1999-09-27 │ JS
│ F
│
└─────┴──────┴────────────┴──────┴────────┘
**看一下底层存储**
[root@spark-140 test1]# cd tb_merge_tree/
[root@spark-140 tb_merge_tree]# ll
total 12
drwxr-x--- 2 root root 4096 Aug 30 17:18 all_1_1_0
//存着表的所有数据
drwxr-x--- 2 root root 4096 Aug 30 17:18 detached
-rw-r----- 1 root root
1 Aug 30 17:18 format_version.txt
[root@spark-140 tb_merge_tree]# cd all_1_1_0/
[root@spark-140 all_1_1_0]# ll
total 28
-rw-r----- 1 root root 187 Aug 30 17:18 checksums.txt
//数据安全校验
-rw-r----- 1 root root 109 Aug 30 17:18 columns.txt
//列信息文件，存放列字段信息
-rw-r----- 1 root root
1 Aug 30 17:18 count.txt
//记录当前数据的条数
-rw-r----- 1 root root 174 Aug 30 17:18 data.bin
//数据文件
-rw-r----- 1 root root 176 Aug 30 17:18 data.mrk3
//列字段标记文件
-rw-r----- 1 root root
10 Aug 30 17:18 default_compression_codec.txt
-rw-r----- 1 root root
2 Aug 30 17:18 primary.idx
//一级索引文件，用于存放稀疏索引
[root@spark-140 all_1_1_0]# cat count.txt4
insert into tb_merge_tree values
(1,'zs',toDate(now()),'HZ','M'),
(2,'ls','2021-09-27','HZ','M'),
(3,'ww','2021-08-18','SH','F'),
(4,'zl','1999-09-27','JS','F');
//再次执行一次插入操作
select * from tb_merge_tree;
┌─uid─┬─name─┬───birthday─┬─city─┬─gender─┐
│
1 │ zs
│ 2021-08-30 │ HZ
│ M
│
│
2 │ ls
│ 2021-09-27 │ HZ
│ M
│
│
3 │ ww
│ 2021-08-18 │ SH
│ F
│
│
4 │ zl
│ 1999-09-27 │ JS
│ F
│
└─────┴──────┴────────────┴──────┴────────┘
┌─uid─┬─name─┬───birthday─┬─city─┬─gender─┐
│
1 │ zs
│ 2021-08-30 │ HZ
│ M
│
│
2 │ ls
│ 2021-09-27 │ HZ
│ M
│
│
3 │ ww
│ 2021-08-18 │ SH
│ F
│
│
4 │ zl
│ 1999-09-27 │ JS
│ F
│
└─────┴──────┴────────────┴──────┴────────┘
//主键没有唯一要求，是可以重复的
[root@spark-140 tb_merge_tree]# ll
total 16
drwxr-x--- 2 root root 4096 Aug 30 17:18 all_1_1_0
drwxr-x--- 2 root root 4096 Aug 30 17:26 all_2_2_0
//第二次插入操作的文件
drwxr-x--- 2 root root 4096 Aug 30 17:18 detached
-rw-r----- 1 root root
1 Aug 30 17:18 format_version.txt
[root@spark-140 tb_merge_tree]# cd all_2_2_0/
[root@spark-140 all_2_2_0]# ll
total 28
-rw-r----- 1 root root 187 Aug 30 17:26 checksums.txt
-rw-r----- 1 root root 109 Aug 30 17:26 columns.txt
-rw-r----- 1 root root
1 Aug 30 17:26 count.txt
-rw-r----- 1 root root 174 Aug 30 17:26 data.bin
-rw-r----- 1 root root 176 Aug 30 17:26 data.mrk3
-rw-r----- 1 root root
10 Aug 30 17:26 default_compression_codec.txt
-rw-r----- 1 root root
2 Aug 30 17:26 primary.idx
[root@spark-140 all_2_2_0]# cat count.txt
4
**合并这两个数据块**
optimize table tb_merge_tree [final];
//final关键字是强制合并的意思，用于多个表合并时
select * from tb_merge_tree;
┌─uid─┬─name─┬───birthday─┬─city─┬─gender─┐
│
1 │ zs
│ 2021-08-30 │ HZ
│ M
│
│
1 │ zs
│ 2021-08-30 │ HZ
│ M
│
│
2 │ ls
│ 2021-09-27 │ HZ
│ M
│
│
2 │ ls
│ 2021-09-27 │ HZ
│ M
│
│
3 │ ww
│ 2021-08-18 │ SH
│ F
│
│
3 │ ww
│ 2021-08-18 │ SH
│ F
│
│
4 │ zl
│ 1999-09-27 │ JS
│ F
│
│
4 │ zl
│ 1999-09-27 │ JS
│ F
│
└─────┴──────┴────────────┴──────┴────────┘
这时再看一下底层存储
[root@spark-140 tb_merge_tree]# ll
total 20
drwxr-x--- 2 root root 4096 Aug 30 17:18 all_1_1_0
drwxr-x--- 2 root root 4096 Aug 30 17:31 all_1_2_1
//这是合并操作的文件
drwxr-x--- 2 root root 4096 Aug 30 17:26 all_2_2_0
drwxr-x--- 2 root root 4096 Aug 30 17:18 detached
-rw-r----- 1 root root
1 Aug 30 17:18 format_version.txt
[root@spark-140 tb_merge_tree]# cd all_1_2_1/
//all_1_2_1数字分别代表什么
1代表最低版本，2代表最高版本，1代表合并操作一次
[root@spark-140 all_1_2_1]# ll
total 28-rw-r----- 1 root root 187 Aug 30 17:31 checksums.txt
-rw-r----- 1 root root 109 Aug 30 17:31 columns.txt
-rw-r----- 1 root root
1 Aug 30 17:31 count.txt
-rw-r----- 1 root root 205 Aug 30 17:31 data.bin
-rw-r----- 1 root root 176 Aug 30 17:31 data.mrk3

-rw-r----- 1 root root
10 Aug 30 17:31 default_compression_codec.txt
-rw-r----- 1 root root
2 Aug 30 17:31 primary.idx
[root@spark-140 all_1_2_1]# cat count.txt
8
create table tb_merge_tree_partition(
uid UInt8,
name String,
birthday DateTime
)engine=MergeTree()
partition by toDate(birthday)
order by uid;
insert into tb_merge_tree_partition values
(1,'zs','2020-09-27 10:00:00'),
(2,'ls','2020-09-27 11:00:00'),
(3,'ww','2020-08-18 10:00:00'),
(4,'zl','2020-08-18 11:00:00');
select * from tb_merge_tree_partition;
┌─uid─┬─name─┬────────────birthday─┐
│
1 │ zs
│ 2020-09-27 10:00:00 │
│
2 │ ls
│ 2020-09-27 11:00:00 │
└─────┴──────┴─────────────────────┘
┌─uid─┬─name─┬────────────birthday─┐
│
3 │ ww
│ 2020-08-18 10:00:00 │
│
4 │ zl
│ 2020-08-18 11:00:00 │
└─────┴──────┴─────────────────────┘
insert into tb_merge_tree_partition values
(11,'zss','2020-09-27 10:00:00'),
(12,'lss','2020-09-27 11:00:00'),
(13,'www','2020-08-18 10:00:00'),
(14,'zll','2020-08-18 11:00:00');
select * from tb_merge_tree_partition;
┌─uid─┬─name─┬────────────birthday─┐
│
1 │ zs
│ 2020-09-27 10:00:00 │
│
2 │ ls
│ 2020-09-27 11:00:00 │
└─────┴──────┴─────────────────────┘
┌─uid─┬─name─┬────────────birthday─┐
│
3 │ ww
│ 2020-08-18 10:00:00 │
│
4 │ zl
│ 2020-08-18 11:00:00 │
└─────┴──────┴─────────────────────┘
┌─uid─┬─name─┬────────────birthday─┐
│
11 │ zss
│ 2020-09-27 10:00:00 │
│
12 │ lss
│ 2020-09-27 11:00:00 │
└─────┴──────┴─────────────────────┘
┌─uid─┬─name─┬────────────birthday─┐
│
13 │ www
│ 2020-08-18 10:00:00 │
│
14 │ zll
│ 2020-08-18 11:00:00 │
└─────┴──────┴─────────────────────┘
**看一下底层存储**
cd /data/clickhouse/data/test1/tb_merge_tree_partition
[root@spark-140 tb_merge_tree_partition]# ll
total 24
drwxr-x--- 2 root root 4096 Aug 30 19:58 20200818_2_2_0
drwxr-x--- 2 root root 4096 Aug 30 19:59 20200818_4_4_0
drwxr-x--- 2 root root 4096 Aug 30 19:58 20200927_1_1_0
drwxr-x--- 2 root root 4096 Aug 30 19:59 20200927_3_3_0
drwxr-x--- 2 root root 4096 Aug 30 19:58 detached
-rw-r----- 1 root root
1 Aug 30 19:58 format_version.txt
**合并操作**
optimize table tb_merge_tree_partition final;
select * from tb_merge_tree_partition;
┌─uid─┬─name─┬────────────birthday─┐
│
1 │ zs
│ 2020-09-27 10:00:00 │
│
2 │ ls
│ 2020-09-27 11:00:00 │
│
11 │ zss
│ 2020-09-27 10:00:00 │
│
12 │ lss
│ 2020-09-27 11:00:00 │
└─────┴──────┴─────────────────────┘
┌─uid─┬─name─┬────────────birthday─┐
│
3 │ ww
│ 2020-08-18 10:00:00 │
│
4 │ zl
│ 2020-08-18 11:00:00 │
│
13 │ www
│ 2020-08-18 10:00:00 │
│
14 │ zll
│ 2020-08-18 11:00:00 │
└─────┴──────┴─────────────────────┘
[root@spark-140 tb_merge_tree_partition]# ll
total 28
drwxr-x--- 2 root root 4096 Aug 30 19:58 20200818_2_2_0
drwxr-x--- 2 root root 4096 Aug 30 20:01 20200818_2_4_1
drwxr-x--- 2 root root 4096 Aug 30 19:59 20200818_4_4_0
drwxr-x--- 2 root root 4096 Aug 30 19:58 20200927_1_1_0
drwxr-x--- 2 root root 4096 Aug 30 20:02 20200927_1_3_1
drwxr-x--- 2 root root 4096 Aug 30 19:59 20200927_3_3_0
drwxr-x--- 2 root root 4096 Aug 30 19:58 detached
-rw-r----- 1 root root
1 Aug 30 19:58 format_version.txt

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
create table tb_merge_tree(
uid UInt8,
name String,
birthday Date,
city String,
gender String
)engine=MergeTree()
primary key uid
order by (uid,birthday);
insert into tb_merge_tree values
(1,'zs',toDate(now()),'HZ','M'),
(2,'ls','2021-09-27','HZ','M'),
(3,'ww','2021-08-18','SH','F'),
(4,'zl','1999-09-27','JS','F');
select * from tb_merge_tree;
┌─uid─┬─name─┬───birthday─┬─city─┬─gender─┐
│
1 │ zs
│ 2021-08-30 │ HZ
│ M
│
│
2 │ ls
│ 2021-09-27 │ HZ
│ M
│
│
3 │ ww
│ 2021-08-18 │ SH
│ F
│
│
4 │ zl
│ 1999-09-27 │ JS
│ F
│
└─────┴──────┴────────────┴──────┴────────┘
**看一下底层存储**
[root@spark-140 test1]# cd tb_merge_tree/
[root@spark-140 tb_merge_tree]# ll
total 12
drwxr-x--- 2 root root 4096 Aug 30 17:18 all_1_1_0
//存着表的所有数据
drwxr-x--- 2 root root 4096 Aug 30 17:18 detached
-rw-r----- 1 root root
1 Aug 30 17:18 format_version.txt
[root@spark-140 tb_merge_tree]# cd all_1_1_0/
[root@spark-140 all_1_1_0]# ll
total 28
-rw-r----- 1 root root 187 Aug 30 17:18 checksums.txt
//数据安全校验
-rw-r----- 1 root root 109 Aug 30 17:18 columns.txt
//列信息文件，存放列字段信息
-rw-r----- 1 root root
1 Aug 30 17:18 count.txt
//记录当前数据的条数
-rw-r----- 1 root root 174 Aug 30 17:18 data.bin
//数据文件
-rw-r----- 1 root root 176 Aug 30 17:18 data.mrk3
//列字段标记文件
-rw-r----- 1 root root
10 Aug 30 17:18 default_compression_codec.txt
-rw-r----- 1 root root
2 Aug 30 17:18 primary.idx
//一级索引文件，用于存放稀疏索引
[root@spark-140 all_1_1_0]# cat count.txt4
insert into tb_merge_tree values
(1,'zs',toDate(now()),'HZ','M'),
(2,'ls','2021-09-27','HZ','M'),
(3,'ww','2021-08-18','SH','F'),
(4,'zl','1999-09-27','JS','F');
//再次执行一次插入操作
select * from tb_merge_tree;
┌─uid─┬─name─┬───birthday─┬─city─┬─gender─┐
│
1 │ zs
│ 2021-08-30 │ HZ
│ M
│
│
2 │ ls
│ 2021-09-27 │ HZ
│ M
│
│
3 │ ww
│ 2021-08-18 │ SH
│ F
│
│
4 │ zl
│ 1999-09-27 │ JS
│ F
│
└─────┴──────┴────────────┴──────┴────────┘
┌─uid─┬─name─┬───birthday─┬─city─┬─gender─┐
│
1 │ zs
│ 2021-08-30 │ HZ
│ M
│
│
2 │ ls
│ 2021-09-27 │ HZ
│ M
│
│
3 │ ww
│ 2021-08-18 │ SH
│ F
│
│
4 │ zl
│ 1999-09-27 │ JS
│ F
│
└─────┴──────┴────────────┴──────┴────────┘
//主键没有唯一要求，是可以重复的
[root@spark-140 tb_merge_tree]# ll
total 16
drwxr-x--- 2 root root 4096 Aug 30 17:18 all_1_1_0
drwxr-x--- 2 root root 4096 Aug 30 17:26 all_2_2_0
//第二次插入操作的文件
drwxr-x--- 2 root root 4096 Aug 30 17:18 detached
-rw-r----- 1 root root
1 Aug 30 17:18 format_version.txt
[root@spark-140 tb_merge_tree]# cd all_2_2_0/
[root@spark-140 all_2_2_0]# ll
total 28
-rw-r----- 1 root root 187 Aug 30 17:26 checksums.txt
-rw-r----- 1 root root 109 Aug 30 17:26 columns.txt
-rw-r----- 1 root root
1 Aug 30 17:26 count.txt
-rw-r----- 1 root root 174 Aug 30 17:26 data.bin
-rw-r----- 1 root root 176 Aug 30 17:26 data.mrk3
-rw-r----- 1 root root
10 Aug 30 17:26 default_compression_codec.txt
-rw-r----- 1 root root
2 Aug 30 17:26 primary.idx
[root@spark-140 all_2_2_0]# cat count.txt
4
**合并这两个数据块**
optimize table tb_merge_tree [final];
//final关键字是强制合并的意思，用于多个表合并时
select * from tb_merge_tree;
┌─uid─┬─name─┬───birthday─┬─city─┬─gender─┐
│
1 │ zs
│ 2021-08-30 │ HZ
│ M
│
│
1 │ zs
│ 2021-08-30 │ HZ
│ M
│
│
2 │ ls
│ 2021-09-27 │ HZ
│ M
│
│
2 │ ls
│ 2021-09-27 │ HZ
│ M
│
│
3 │ ww
│ 2021-08-18 │ SH
│ F
│
│
3 │ ww
│ 2021-08-18 │ SH
│ F
│
│
4 │ zl
│ 1999-09-27 │ JS
│ F
│
│
4 │ zl
│ 1999-09-27 │ JS
│ F
│
└─────┴──────┴────────────┴──────┴────────┘
这时再看一下底层存储
[root@spark-140 tb_merge_tree]# ll
total 20
drwxr-x--- 2 root root 4096 Aug 30 17:18 all_1_1_0
drwxr-x--- 2 root root 4096 Aug 30 17:31 all_1_2_1
//这是合并操作的文件
drwxr-x--- 2 root root 4096 Aug 30 17:26 all_2_2_0
drwxr-x--- 2 root root 4096 Aug 30 17:18 detached
-rw-r----- 1 root root
1 Aug 30 17:18 format_version.txt
[root@spark-140 tb_merge_tree]# cd all_1_2_1/
//all_1_2_1数字分别代表什么
1代表最低版本，2代表最高版本，1代表合并操作一次
[root@spark-140 all_1_2_1]# ll
total 28-rw-r----- 1 root root 187 Aug 30 17:31 checksums.txt
-rw-r----- 1 root root 109 Aug 30 17:31 columns.txt
-rw-r----- 1 root root
1 Aug 30 17:31 count.txt
-rw-r----- 1 root root 205 Aug 30 17:31 data.bin
-rw-r----- 1 root root 176 Aug 30 17:31 data.mrk3

-rw-r----- 1 root root
10 Aug 30 17:31 default_compression_codec.txt
-rw-r----- 1 root root
2 Aug 30 17:31 primary.idx
[root@spark-140 all_1_2_1]# cat count.txt
8
create table tb_merge_tree_partition(
uid UInt8,
name String,
birthday DateTime
)engine=MergeTree()
partition by toDate(birthday)
order by uid;
insert into tb_merge_tree_partition values
(1,'zs','2020-09-27 10:00:00'),
(2,'ls','2020-09-27 11:00:00'),
(3,'ww','2020-08-18 10:00:00'),
(4,'zl','2020-08-18 11:00:00');
select * from tb_merge_tree_partition;
┌─uid─┬─name─┬────────────birthday─┐
│
1 │ zs
│ 2020-09-27 10:00:00 │
│
2 │ ls
│ 2020-09-27 11:00:00 │
└─────┴──────┴─────────────────────┘
┌─uid─┬─name─┬────────────birthday─┐
│
3 │ ww
│ 2020-08-18 10:00:00 │
│
4 │ zl
│ 2020-08-18 11:00:00 │
└─────┴──────┴─────────────────────┘
insert into tb_merge_tree_partition values
(11,'zss','2020-09-27 10:00:00'),
(12,'lss','2020-09-27 11:00:00'),
(13,'www','2020-08-18 10:00:00'),
(14,'zll','2020-08-18 11:00:00');
select * from tb_merge_tree_partition;
┌─uid─┬─name─┬────────────birthday─┐
│
1 │ zs
│ 2020-09-27 10:00:00 │
│
2 │ ls
│ 2020-09-27 11:00:00 │
└─────┴──────┴─────────────────────┘
┌─uid─┬─name─┬────────────birthday─┐
│
3 │ ww
│ 2020-08-18 10:00:00 │
│
4 │ zl
│ 2020-08-18 11:00:00 │
└─────┴──────┴─────────────────────┘
┌─uid─┬─name─┬────────────birthday─┐
│
11 │ zss
│ 2020-09-27 10:00:00 │
│
12 │ lss
│ 2020-09-27 11:00:00 │
└─────┴──────┴─────────────────────┘
┌─uid─┬─name─┬────────────birthday─┐
│
13 │ www
│ 2020-08-18 10:00:00 │
│
14 │ zll
│ 2020-08-18 11:00:00 │
└─────┴──────┴─────────────────────┘
**看一下底层存储**
cd /data/clickhouse/data/test1/tb_merge_tree_partition
[root@spark-140 tb_merge_tree_partition]# ll
total 24
drwxr-x--- 2 root root 4096 Aug 30 19:58 20200818_2_2_0
drwxr-x--- 2 root root 4096 Aug 30 19:59 20200818_4_4_0
drwxr-x--- 2 root root 4096 Aug 30 19:58 20200927_1_1_0
drwxr-x--- 2 root root 4096 Aug 30 19:59 20200927_3_3_0
drwxr-x--- 2 root root 4096 Aug 30 19:58 detached
-rw-r----- 1 root root
1 Aug 30 19:58 format_version.txt
**合并操作**
optimize table tb_merge_tree_partition final;
select * from tb_merge_tree_partition;
┌─uid─┬─name─┬────────────birthday─┐
│
1 │ zs
│ 2020-09-27 10:00:00 │
│
2 │ ls
│ 2020-09-27 11:00:00 │
│
11 │ zss
│ 2020-09-27 10:00:00 │
│
12 │ lss
│ 2020-09-27 11:00:00 │
└─────┴──────┴─────────────────────┘
┌─uid─┬─name─┬────────────birthday─┐
│
3 │ ww
│ 2020-08-18 10:00:00 │
│
4 │ zl
│ 2020-08-18 11:00:00 │
│
13 │ www
│ 2020-08-18 10:00:00 │
│
14 │ zll
│ 2020-08-18 11:00:00 │
└─────┴──────┴─────────────────────┘
[root@spark-140 tb_merge_tree_partition]# ll
total 28
drwxr-x--- 2 root root 4096 Aug 30 19:58 20200818_2_2_0
drwxr-x--- 2 root root 4096 Aug 30 20:01 20200818_2_4_1
drwxr-x--- 2 root root 4096 Aug 30 19:59 20200818_4_4_0
drwxr-x--- 2 root root 4096 Aug 30 19:58 20200927_1_1_0
drwxr-x--- 2 root root 4096 Aug 30 20:02 20200927_1_3_1
drwxr-x--- 2 root root 4096 Aug 30 19:59 20200927_3_3_0
drwxr-x--- 2 root root 4096 Aug 30 19:58 detached
-rw-r----- 1 root root
1 Aug 30 19:58 format_version.txt