ClickHouseMergeTree表引擎和建表语句
1. clickhouse使⽤场景
ClickHouse是由俄罗斯Yandex公司开发的、⾯向列的数据库管理系统(DBMS)，主要⾯向OLAP场景，⽤于在线分析处理查询，可以使⽤SQL查询实时⽣成数据分析结果。列式存储的好处就是当我们对列进⾏聚合等操作时，效率会⼤⼤优于⾏式存储，⽽且由于每⼀列的类型都是相同的，所以对于数据存储更容易进⾏压缩，⽽且可以对不同类型的列选择更合适的压缩算法，节约资源。
clickhouse的设计也处处体现了俄罗斯的暴⼒美学，它不仅仅是⼀个数据库，还是⼀个数据库管理系统，后⾯我们在介绍基于SQL的⽤户管理、权限管理、资源管理，以及clickhouse本⾝在数据压缩、并⾏化计算等⽅⾯的特⾊，就会明⽩为什么会说clickhouse是⼀个数据库管理系统了。
clickhouse虽然在很多⽅⾯表现出了优异的性能，尤其是是在⼤数据量情况下的⾼效查询效率（ck和其他数据库的查询性能⽐较，可参考，但也并不意味着就适合所有数据库使⽤场景。ck的使⽤场景如下：
数据量较⼤。在ck官⽹的测试数据案例以及诸多⼤⼚的实践中，数据量⾄少都是在百万条以上（⼤多数都是亿级）。如果使⽤传统数据库，如mysql、oracle、pg等数据库已经可以满⾜业务需求，那么就没必要强⾏引⼊ck，⽽且ck对⽤户暴露了很多使⽤配置，对开发⼈员的要求更⾼，运维成本更⾼。
ck的请求绝⼤多数都是读请求。换句话说就是写请求很少，或者说不频繁（插⼊数据都是集中写）。例如，⽹站的⾏为⽇志分析，可以在凌晨把前⼀天的数据同步到ck中。当然，这并不是说ck的写⼊数据性能很弱，只是不适合细⽔长流的写操作。
批量(> 1000⾏)更新，⽽不是单个⾏更新，或者根本就没有更新/修改操作。这也是ck和hive类似，但是⼜不同的地⽅。hive不⽀持alter、delete语句修改数据，ck⽀持此类操作，但是⾮常不建议针对极少数数据、频繁的做alter操作，尤其是在服务器⽐较忙碌的时候。最好是和hive⼀样，对分区做增删操作。
读取很多⾏，但只提取了列的⼀⼩部分。换句话说就是，我的表很⼤、列很宽，但是每次查询的时候只使⽤其中部分字段，按需索取，所以如果不是必要，不建议在ck中使⽤ select * 的操作，尤其是宽表。
宽表，即有很多列。换句话说就是数据在写⼊ck之前，已经打宽，避免在ck内做表关联操作，并不是说ck不⽀持join操作，只是相对查询⽽⾔性能会受影响（实际上ck的⼤表和⼩表join也并不⽐其他关系型数据库差）。
查询相对较少(通常每台服务器每秒有数百个查询，甚⾄更少)，这也就意味着ck不能直接作为业务系统的查询数据库，尤其是⾯向C端⽤户的业务系统，这主要是ck对服务器的CPU消耗极⾼导致的。所以c
k多⽤在监控数据分析、⽇志分析、业务数据分析等场景，服务于平台和企业内部，例如，电商平台需要监控每三分钟刷新的热销商品销量排⾏。
对于简单的查询，⼤约允许50毫秒的延迟。这是由于ck的稀疏索引导致的，使得ck对于通过键检索单⾏的点查询不那么有效。不同于hash索引，使⽤ where = * 条件可以直接定位到要查的数据，稀疏索引是通过对数据排序，然后建⽴等距采样点得到的，所以即使是= 精准查询，也要多次⽐较得到。
列中的数据相对较⼩，多为数字和短字符串。例如，URL、销量等。如果存⽂章、图⽚甚⾄视频就不合适了。
在处理单个查询时需要⾼吞吐量(每台服务器每秒可处理数⼗亿⾏)。例如6中的热销商品监控。
事务不是必须的。这是ck和mysql等数据库⼀个极⼤的不同点，ck不⽀持事务，也就说⽆法保证数据⼀致性。好消息是Yandex已经把事务⽀持排在远期⽬标了。
对数据⼀致性要求低。⼀⽅⾯是因为ck不⽀持事务，还有⼀⽅⾯是因为ck的副本表在同步数据的时候不能保证数据⼀致性。例如，修改了副本A的数据，副本B的数据还没有完全同步好，此时如果分别查了副本A和副本B的数据，结果就不⼀致了。⽽且因为没有事务⽀持，如果是插⼊⼀万条记录，在写⼊三千条的时候，来了⼀个查询，查询结果是会把三千条涵盖进去的。所以ck在写⼊数据的时候为了保
证数据⼀致性，有⼀种⽅案就是先建⽴⼀个临时表保存要插⼊的数据，然后把原表的历史数据也同步到临时表中，再rename原表为备份表、rename临时表为正式表，这样就可以保证数据⼀致性了，如果有问题只需要把备份表再rename为正式表就可以回滚了。
每个查询都有⼀个⼤表。除了他以外，其他的表都很⼩。即尽量不要做⼤表和⼤表关联的操作。
查询结果明显⼩于源数据。换句话说，数据被过滤或聚合，因此结果适合单个服务器的RAM。
2. ClickHouse表引擎
ClickHouse⽀持不同的表引擎，主要有MergeTree家族表引擎、Log家族表引擎、集成表引擎，以及⼀些特殊的表引擎。表引擎的主要作⽤是：
决定数据存储的⽅式和位置，向何处写⼊，从何处读取。如Log引擎数据是存在内存中。
⽀持哪些查询以及如何⽀持。例如，有些功能只有MergeTree系列表引擎才⽀持。
并发数据访问。
索引的使⽤(如果存在)。
是否可以执⾏多线程请求。
数据复制参数。
其中MergeTree家族的表引擎是ClickHouse数据存储能⼒的核⼼，它们为弹性和⾼性能数据检索提供了⼤多数特性：列式存储、⾃定义分区、稀疏主索引、⼆级索引等。
3. MergeTree引擎建表语句
MergeTree表引擎是MergeTree家族表引擎最具代表性的表引擎，也是使⽤最为⼴泛的表引擎。MergeTree表引擎可以被认为是单节点ClickHouse实例的默认表引擎，因为它适⽤于各种各样的⽤例。建表语句如下：
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,
...
PROJECTION projection_name_1 (SELECT<COLUMN LIST EXPR>[GROUP BY][ORDER BY]),
PROJECTION projection_name_2 (SELECT<COLUMN LIST EXPR>[GROUP BY][ORDER BY])
)ENGINE= MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
[DELETE|TO DISK'xxx'|TO VOLUME 'xxx'[,...]]
[WHERE conditions]
[GROUP BY key_expr [SET v1 = aggr_func(v1)[, v2 = aggr_func(v2)...]]]]
[SETTINGS name=value,...]
ClickHouse的建表语句和hive类似，⽽且ck也是库名和⽤户名相互独⽴的。ck环境启动以后会有⼀个默认的default库，所以在创建表的时候，如果不指定库名，默认就是在default库下⾯创建。
ON CLUSTER 表⽰在哪个集上创建表，只有在分布式环境中才会使⽤，单节点表不需要考虑。和hive不同，ck在创建分布式表和副本表的时候需要在每个节点上单独执⾏建表语句（分布式表需要先创建local表），对于⼀个很⼤的集，如果每次建表都要在所有节点上执⾏，⽆疑是很⿇烦的，⽽且⼀般情况下为了做HA、负载均衡，并且⽅便使⽤，只会对应⽤端暴露⼀个负载虚拟IP，就⽆法在每个节点上执⾏建表语句，此时就可以在ck的配置⽂件中创建⼀个同步DDL语句的集（需要依赖zookeeper），这样⼀旦执⾏DDL语句就可以通过zookeeper同步到所有节点上执⾏。
ck的建表语句字段定义格式和其他关系型数据库⼀样，也是 字段名+字段类型 定义，也可以设置默认值，过期时间等。ck的数据类型可参考。
ENGINE = MergeTree() 表⽰定义表引擎是 MergeTree ，其他表引擎也是如此，只是名称和参数不同。在⼀些历史版本中，可能会见到带参数的MergeTree引擎建表语句，只是把后⾯的分区等建表参数作为了MergeTree()的参数⽽已，ck已经废弃了这种⽤法。
mysql中delete语句PARTITION BY 定义分区字段，和hive⼀样，MergeTree表的不同分区数据也是存在不同的⽬录中，和hive每个分区数据是有不同的⼩⽂件组成不同，MergeTree的分区数据⼀般是有不同的列数据⽂件组成的，后⾯会详细介绍。
PRIMARY KEY 定义主键字段，⼀般是通过ORDER BY字段定义，不单独定义，除⾮是和ORDER BY字段不同时才会单独定义主键字段，且必须是ORDER BY前⾯的字段，和其他数据库不同的是MergeTree的主键字段是可以重复的。
ORDER BY 定义排序字段，也是MergeTree引擎最为重要的建表参数，通过建表语句也可以发现，其他定义字段都不是必须的，只有ORDER BY是必须的，当然如果实在不需要定义ORDER BY字段，可以使⽤ ORDER BY tuple() 语法建表。在使⽤insert … select 语句插⼊数据时，如果想保证数据存储顺序和插⼊顺序⼀致，除了不指定ORDER BY外，还需要设置 max_insert_threads = 1。如果没有定
义主键字段，ORDER BY 字段就是主键，使⽤中多是不单独定义PRIMARY KEY，但是在 CollapsingMergeTree 和SummingMergeTree 表引擎中，分开定义主键和排序键可能会更有意义。需要注意，即使单独定义主键和排序列，主键列也要位于排序列的前⾯。MergeTree 表数据的存储顺序就是按照 ORDER BY 字段顺序存储的，因为ck采⽤的是稀疏索引，所以定义合适的ORDER BY 字段对于查询效率尤为重要。默认情况下ck的主键不能为空，可以通过设置allow_nullable_key选项，允许主键为空，但是强烈建议不要这样做，尤其在<= 21.8版本中，可能会导致数据库崩溃。
如图1，有⼀个ORDER BY CounterID, Date 的 MergeTree表，ck在存储数据的时候⾸先按照ORDER BY顺序存储数据，然后按照等距（图1是每七条记录）采样⼀组主键元素，并把采样数据单独保存为⼀个⽂件。这样在查询的时候只需要先在主键数据⽂件进⾏查，到对应块的偏移距离，然后按照偏移量到数据⽂件中对应的块查就可以了。例如，查 CounterID in (‘a’, ‘h’) 的数据，会⾸先检索出 0、1、2、6、7号块的数据，然后再筛选2、6、7块中符合条件的数据（0、1块数据显然都是符合的）。
SAMPLE BY 采样字段，⼀般在对数据结果准确性要求不⾼的时候使⽤，⽐如计算平均值，如果数据量较⼤，只会对定义的采样字段采样数据计算，但是采样字段必须定义在主键中，如 SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))。
TTL 定义数据有效期，和redis类似，redis作为缓存服务器可以存储具有时间有效期特性的数据，如验证码数据。ck可以定义列级TTL，也可以定义表级TTL，如果是列级TTL，则该列值到期后会被置为默认值，如果所有列都到期，则删除，注意TTL列不能作为键，创建TTL列表：
CREATE TABLE example_table
(
d DateTime,
a Int TTL d +INTERVAL1MONTH,
b Int TTL d +INTERVAL1MONTH,
c String
)
ENGINE= MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;
相对于列级TTL，⼀般更常⽤表级TTL，可⽤来存储对历史数据不再关注的数据，⽽且表TTL可以把过期数据移动到磁盘或者卷（关于卷和磁盘的区别参见10），例如在训练模型进⾏在线迁移学习的时候，会⼀直使⽤新数据优化模型，不再关注历史数据等。创建表级TTL表：
CREATE TABLE example_table
(
d DateTime,
a Int
)
ENGINE= MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d +INTERVAL1MONTH[DELETE],
d +INTERVAL1 WEEK TO VOLUME 'aaa',
d +INTERVAL2 WEEK TO DISK'bbb';
SETTINGS ⽤来设置⼀些附加参数，⼤多数情况下都不需要设置，可能⽤到的参数如下：
index_granularity 索引粒度（按⾏），默认是8192，即每8192⾏数据采样⼀条索引数据，例如，图1的索引粒度是7.
index_granularity_bytes 索引粒度（按字节），默认是10Mb，表⽰⾃适应间隔⼤⼩的特性，即根据每⼀批写⼊数据的体量⼤⼩，动态划分间隔⼤⼩。
min_index_granularity_bytes 允许的最⼩索引粒度（按字节），默认值是 1024 b，防⽌意外地创建index_granularity_bytes⾮常⼩的保护机制。
enable_mixed_granularity_parts 开启或者关闭index_granularity_bytes ，对于⾏数据很⼤的表，开启此选项可提升查询效率。
storage_policy （存储策略）：MergeTree家族表引擎可以在多个块设备上存储数据，这对于区分冷热场景的数据查询降低成本⾮常有⽤。例如，对于电商数据，最近半年的数据查询频率会⽐较⾼，响应要快，可以使⽤SSD存储，半年以上的数据（长尾数据），查询很少，可以响应慢⼀些，也就是可以使⽤低成本的存储介质，如HDD。
在继续介绍之前，⾸先区分⼏个概念：
① disk（磁盘）：挂载到⽂件系统的块设备。
② default disk：服务器中指定的存储路径。
③ volume（卷）：相同disk的有序集合，类似于JBOD。
④ Storage policy（存储策略）：⼀组volume以及在volume之间移动数据的规则。
ck⽀持设置按照分区移动数据，也可以通过alter语句⼿动执⾏：
ALTER TABLE hits MOVE PART '20190301_14343_16206_438'TO VOLUME 'slow'
ALTER TABLE hits MOVE PARTITION'2019-09-01'TO DISK'fast_ssd'
如果想通过配置实现数据的⾃动转移，可参考在配置⽂件中设置。
关于ck服务器的存储策略和资源信息可通过 system.storage_policies 和 system.disks 表查看。
min_bytes_for_wide_part, min_rows_for_wide_part 数据⽂件中可以以宽格式存储的最⼩字节/⾏数。宽格式指不同的列存储在不同的⽂件中，这也是默认的存储格式，对于⼀些⼩表，可以设置该值，使得所有列存储在同⼀个⽂件中，从⽽增加⼩⽽频繁的插⼊性能。
max_partitions_to_read 限制⼀个查询中可以访问的最⼤分区数，默认是不限制。
4. MergeTree表数据存储结构

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。