clickhouse第二篇：MergeTree引擎

https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree

新建MergeTree引擎表示例：

use default;
drop table if exists app_project_click;
create table default.app_project_click (
`message_no` String,
`os` String,
`os_version` String,
`app_version` String,
`event_id` Int64,
`event_name` String,
`ts` Int64,
`flush_time` Int64,
`uid` Int64,
`project_type` String,
`project_id` Int64
index idx_project_id project_id type minmax GRANULARITY 3
) engine = MergeTree
partition by fromUnixTimestamp(intDiv(flush_time,1000), '%Y-%m-%d')
primary key message_no
settings index_granularity = 8192;

MergeTree引擎主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引、数据TTL等。MergeTree支持clickhouse所有sql语法，但有些功能与mysql并不一致，如MergeTree引擎表中主键不用于去重，即可以存在主键相同的记录。

用partition key指定分区字段，如果查询条件有分区字段，那么可以提高查询速度。但是分区会增加磁盘文件数，所以会降低那些不指定分区键的查询的速度。如果要按月分区，则可以用toYYYYMM()函数，参数是Date类型的字段。如果要按天分区，则可以用toYYYYMMDD()函数。clickhouse中时间数据类型有4种：1、Date，只有天，没有时分秒，最多到2148年。2、Date32，同Date，但最多到2299年。3、DateTime，精确到秒，最多到2106年。4、DateTime64，最多可精确到纳秒，如DateTime64(3)精确到毫秒，DateTime(6)精确到微秒，DateTime(9)精确到纳秒，最多到2299年。

用primary key指定主键，用order by指定排序字段，这两个必须至少指定一个，可以是多个字段，用括号包住即可，也可以有表达式。只指定其中一个的话，另一个会自动和这个一样。如果显式指定主键，那么主键字段必须是排序字段的前缀。

用sample by指定抽样字段。

用ttl指定到了一定时间后，对记录的处理措施，如删除、移动等。假如我们有一个表，只想存储最近一个月的数据，那么就很适合用ttl，ttl指定为create_time + toIntervalMonth(1) delete，delete是默认的处理措施，可以省略。ttl的时间必须是Date或DateTime类型。

数据存储

表由数据分片组成。每当有数据插入表中时，clickhouse都会创建新的数据分片，分片中的数据会按照order by字段排序。如果排序字段是(a,b)，那么会先按照a排序，a相同的数据又会按b排序。属于不同分区的数据会存储在不同的分片中。clickhouse为了更高效的存储会在后台合并分片。不同分区的分片不会被合并到一起。合并机制不能保证主键相同的所有行都合并到同一个分片中。

数据分片以Wide或Compact格式存储。以Wide格式存储时，每一列都会存储成一个单独的文件。而以Compact格式存储时，所有列存储成一个文件。在插入的数据比较小时，使用Compact格式存储性能比较好。默认以Wide格式存储。如果想以Compact格式存储，则需要在建表时设置min_bytes_for_wide_part或min_rows_for_wide_part，当数据分片的字节数或行数少于相应设置值时，分片就以Compact格式存储了，否则还是以Wide格式存储。min_bytes_for_wide_part、min_rows_for_wide_part设置并不是MergeTree族引擎独有的，其他族引擎也支持，如Log。

每个数据分片在逻辑上分为粒度，粒度是clickhouse在查询数据时读取的最小不可分割数据集。一个分片可能有多个粒度，每个粒度包含整数行。粒度的第一行用该行的主键值标记。clickhouse会为每一个数据分片创建一个索引文件，用于存储标记。对于每一列，不管它在不在主键中，clickhouse都会存储相同的标记。利用这些标记可以让clickhouse在列文件中找到数据。

粒度大小受表引擎的index_granularity和index_granularity_bytes控制，前者默认值8192，后者默认值10M。一个粒度的行数在[1, index_granularity]区间范围内，取决于行的大小。如果一行数据很大，那么可能一个粒度的行数小于index_granularity。如果单行数据就超过了index_granularity_bytes，那么一个粒度的大小会超过index_granularity_bytes，这种情况下，一个粒度就一行。index_granularity和index_granularity_bytes设置并不是MergeTree族引擎独有的，其他族引擎也支持，如Log。

实例分析：

假如一个表有A、B、C三列，其中A列是主键列。假如这个表有一个数据分片，有1w行记录。则A列的前8192行会被划分为一个粒度，后1808行会被划分为一个粒度，分别命名为AG1、AG2。假设A列第1行的值为a1，第8193行的值为a8193。B列的前8192行会被划分为一个粒度，后1808行会被划分为一个粒度，分别命名为BG1、BG2。C列的前8192行会被划分为一个粒度，后1808行会被划分为一个粒度，分别命名为CG1、CG2。clickhouse会为这个数据分片创建一个索引文件，如part1.index。对于AG1，clickhouse会在索引文件中创建一条记录A->AG1->a1。对于AG2，clickhouse会在索引文件中创建一条记录A->AG2->a8193。对于BG1，clickhouse会在索引文件中创建一条记录B->BG1->a1。对于BG2，clickhouse会在索引文件中创建一条记录B->BG2->a8193。对于CG1，clickhouse会在索引文件中记录一条记录C->CG1->a1。对于CG2，clickhouse会在索引文件中记录一条记录C->CG1->a8193。这样，通过a1，clickhouse就把A列的AG1、B列的BG1、C列的CG1关联起来了。如果想根据主键查找，如select B from xxx where A = a1000，由于a1 < a1000 < a8193，所以clickhouse会去找a1对应的A列的AG1、B列的BG1。之所以不找C列的CG1，是因为没有查询C列。

主键和索引

 

 

 

code: 241, message: (total) memory limit exceeded: would use 8.99 GiB (attempt to allocate chunk of 256.00 B bytes), current RSS: 8.08 GiB, maximum: 8.66 GiB. OvercommitTracker decision: Query was selected to stop by OvercommitTracker

 

code: 252, message: Too many parts (100000) in all partitions in total in table 'default.app_project_comment (927f4334-a2d5-49d5-b967-92c523da7f95)'. This indicates wrong choice of partition key. The threshold can be modified with 'max_parts_in_total' setting in <merge_tree> element in config.xml or with per-table setting