ClickHouse常见的引擎分区索引使用

1.日志引擎

日志引擎特点
1.数据存储在磁盘上
2.写入时将数据追加在文件末尾
3.不支持突变操作
4.不支持索引
5.非原子地写入数据
6.引擎不支持 ALTER UPDATE 和 ALTER DELETE 操作

建表语法示例

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = StripeLog

1.1StripeLog

建表语法示例：
CREATE TABLE stripe_log_table
(
    create_date DateTime,
    message_type String,
    message String
)
ENGINE = StripeLog;

插入数据：
INSERT INTO stripe_log_table VALUES (now(),'REGULAR','The first regular message');
INSERT INTO stripe_log_table VALUES (now(),'REGULAR','The second regular message'),(now(),'WARNING','The first warning message');

1.2Log

建表语法示例：

CREATE TABLE log_table
(
    create_date DateTime,
    message_type String,
    message String
)
ENGINE = Log;

1.3TinyLog

建表语法示例：

CREATE TABLE tiny_log_table
(
    create_date DateTime,
    message_type String,
    message String
)
ENGINE = TinyLog;

2.合并引擎

2.1MergeTree(重要引擎)

1.存储的数据按主键排序
2.可以使用分区
3.支持数据副本
4.支持数据采样

2.1.1建表语法

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, ...]

参数详解

ENGINE:引擎名和参数
ENGINE = MergeTree(). MergeTree 引擎没有参数。

ORDER BY:排序键
可以是一组列的元组或任意的表达式。 例如: ORDER BY (CounterID, EventDate) 。
如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。
如果不需要排序，可以使用 ORDER BY tuple(). 

PARTITION BY:分区键 ，可选项
        大多数情况下，不需要分使用区键。即使需要使用，也不需要使用比月更细粒度的分区键。分区不会加快查询（这与 ORDER BY 表达式不同）。
        永远也别使用过细粒度的分区键。不要使用客户端指定分区标识符或分区字段名称来对数据进行分区（而是将分区字段标识或名称作为 ORDER BY 表达式的第一列来指定分区）。

        要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 "YYYYMM" 。

PRIMARY KEY:如果要 选择与排序键不同的主键，在这里指定，可选项
        默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。 因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。

SAMPLE BY:用于抽样的表达式，可选项
        如果要用抽样表达式，主键中必须包含这个表达式。例如： SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。

TTL: 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项
        表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：
TTL date + INTERVAl 1 DAY

        规则的类型 DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK 'xxx') 或 卷（TO VOLUME 'xxx'）。默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。

SETTINGS — 控制 MergeTree 行为的额外参数，可选项：

index_granularity :索引粒度。索引中相邻的『标记』间的数据行数。默认值8192 。参考数据存储。
index_granularity_bytes:索引粒度，以字节为单位，默认值: 10Mb。如果想要仅按数据行数限制索引粒度, 请设置为0(不建议)。
min_index_granularity_bytes:允许的最小数据粒度，默认值：1024b。该选项用于防止误操作，添加了一个非常低索引粒度的表。参考数据存储
enable_mixed_granularity_parts:是否启用通过 index_granularity_bytes 控制索引粒度的大小。在19.11版本之前, 只有 index_granularity 配置能够用于限制索引粒度的大小。当从具有很大的行（几十上百兆字节）的表中查询数据时候，index_granularity_bytes 配置能够提升ClickHouse的性能。如果您的表里有很大的行，可以开启这项配置来提升SELECT 查询的性能。
use_minimalistic_part_header_in_zookeeper:ZooKeeper中数据片段存储方式 。如果use_minimalistic_part_header_in_zookeeper=1 ，ZooKeeper 会存储更少的数据。
min_merge_bytes_to_use_direct_io:使用直接 I/O 来操作磁盘的合并操作时要求的最小数据量。合并数据片段时，ClickHouse 会计算要被合并的所有数据的总存储空间。如果大小超过了 min_merge_bytes_to_use_direct_io 设置的字节数，则 ClickHouse 将使用直接 I/O 接口（O_DIRECT 选项）对磁盘读写。如果设置 min_merge_bytes_to_use_direct_io = 0 ，则会禁用直接 I/O。默认值：10 * 1024 * 1024 * 1024 字节。
merge_with_ttl_timeout: TTL合并频率的最小间隔时间，单位：秒。默认值: 86400 (1 天)。
write_final_mark:是否启用在数据片段尾部写入最终索引标记。默认值: 1（不要关闭）。
merge_max_block_size: 在块中进行合并操作时的最大行数限制。默认值：8192
storage_policy:存储策略。 参见 使用具有多个块的设备进行数据存储.
min_bytes_for_wide_part,min_rows_for_wide_part 在数据片段中可以使用Wide格式进行存储的最小字节数/行数。您可以不设置、只设置一个，或全都设置。参考：数据存储
max_parts_in_total:所有分区中最大块的数量(意义不明)
max_compress_block_size:在数据压缩写入表前，未压缩数据块的最大大小。您可以在全局设置中设置该值(参见max_compress_block_size)。建表时指定该值会覆盖全局设置。
min_compress_block_size:在数据压缩写入表前，未压缩数据块的最小大小。您可以在全局设置中设置该值(参见min_compress_block_size)。建表时指定该值会覆盖全局设置。
max_partitions_to_read:一次查询中可访问的分区最大数。您可以在全局设置中设置该值(参见max_partitions_to_read)。

2.1.2创建表示例

CREATE TABLE index_test
(
    i_id UInt64,
    root_id UInt64,
    parent_id UInt64,
    path String,
    index_id UInt64,
    task_id UInt64,
    cluster_id UInt64,
    host_id UInt64,
    scence_inst_id UInt64,
    code String,
    update_date DateTime,
    v1 String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(update_date)
PRIMARY KEY (i_id)
ORDER BY (i_id,index_id,update_date)
SETTINGS index_granularity = 8192;

注意:主键必须是 order by 字段的前缀字段，和索引生成依据有关

2.1.3二级索引创建

CREATE TABLE index_test
(
    i_id UInt64,
    root_id UInt64,
    parent_id UInt64,
    path String,
    index_id UInt64,
    task_id UInt64,
    cluster_id UInt64,
    host_id UInt64,
    scence_inst_id UInt64,
    code String,
    update_date DateTime,
    v1 String,
    INDEX idx_update_date update_date TYPE minmax GRANULARITY 3,
    INDEX idx_index_id index_id TYPE minmax GRANULARITY 3    
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(update_date)
PRIMARY KEY (i_id)
ORDER BY (i_id,index_id,update_date)
SETTINGS index_granularity = 8192;

二级索引的种类:minmax,set,ngrambf_v1,tokenbf_v1,不再展开叙述

2.1.4数据TTL设置

2.1.4.1列级别TTL

列级设置TTl示例:
CREATE TABLE index_test
(
    i_id UInt64,
    root_id UInt64,
    parent_id UInt64,
    path String,
    index_id UInt64,
    task_id UInt64,
    cluster_id UInt64,
    host_id UInt64,
    scence_inst_id UInt64,
    code String,
    update_date DateTime,
    v1 String TTL update_date + INTERVAL 1 DAY,
    INDEX idx_update_date update_date TYPE minmax GRANULARITY 3,
    INDEX idx_index_id index_id TYPE minmax GRANULARITY 3    
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(update_date)
PRIMARY KEY (i_id)
ORDER BY (i_id,index_id,update_date)
SETTINGS index_granularity = 8192;

或者用函数设置:  v1 String TTL update_date + toIntervalDay(1)

添加或者修改表字段的TTL设置:
ALTER TABLE index_test MODIFY COLUMN v1 String TTL update_date + toIntervalDay(10);

2.1.4.2表级别TTL

表级设置TTL示例:
CREATE TABLE index_test
(
    i_id UInt64,
    root_id UInt64,
    parent_id UInt64,
    path String,
    index_id UInt64,
    task_id UInt64,
    cluster_id UInt64,
    host_id UInt64,
    scence_inst_id UInt64,
    code String,
    update_date DateTime,
    v1 String,
    INDEX idx_update_date update_date TYPE minmax GRANULARITY 3,
    INDEX idx_index_id index_id TYPE minmax GRANULARITY 3    
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(update_date)
PRIMARY KEY (i_id)
ORDER BY (i_id,index_id,update_date)
TTL update_date + toIntervalDay(15)
SETTINGS index_granularity = 8192;

添加或者修改表级别的TTL设置:
ALTER TABLE index_test MODIFY TTL update_date + toIntervalMonth(1);

2.1.4.3手动合并TTL表数据

由于主设置的TTL不会马上进行失效，需要手动合并可以使用如下语句:

OPTIMIZE TABLE index_test FINAL;

2.2Memory(内存引擎)

内存引擎查询速度快速，断电丢失数据，一般适用临时表或者高性能测试用

2.2.1创建临时表使用示例

CREATE  TABLE temp_autorun_t_index
ENGINE=Memory
AS 
SELECT path,code,v1 FROM autorun_t_index limit 10;