Clickhouse 使用简介及MergeTree存储分析

建表,排序,索引及注意事项

https://clickhouse.tech/docs/zh/operations/table_engines/mergetree/

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

我们以 (CounterID, Date) 以主键。排序好的索引的图示会是下面这样:

image.png

如果指定查询如下:

  • CounterID in ('a', 'h'),服务器会读取标记号在 [0, 3)[6, 8) 区间中的数据。
  • CounterID IN ('a', 'h') AND Date = 3,服务器会读取标记号在 [1, 3)[7, 8) 区间中的数据。
  • Date = 3,服务器会读取标记号在 [1, 10] 区间中的数据。

上面例子可以看出使用索引通常会比全表描述要高效。

稀疏索引会引起额外的数据读取。当读取主键单个区间范围的数据时,每个数据块中最多会多读 index_granularity * 2 行额外的数据。大部分情况下,当 index_granularity = 8192 时,ClickHouse的性能并不会降级。

稀疏索引让你能操作有巨量行的表。因为这些索引是常驻内存(RAM)的。

ClickHouse 不要求主键惟一。所以,你可以插入多条具有相同主键的行。

主键的选择

https://clickhouse.tech/docs/zh/operations/table_engines/mergetree/#zhu-jian-de-xuan-ze

主键中列的数量并没有明确的限制。依据数据结构,你应该让主键包含多些或少些列。这样可以:

  • 改善索引的性能。

    如果当前主键是 (a, b) ,然后加入另一个 c 列,满足下面条件时,则可以改善性能: - 有带有 c 列条件的查询。 - 很长的数据范围( index_granularity 的数倍)里 (a, b) 都是相同的值,并且这种的情况很普遍。换言之,就是加入另一列后,可以让你的查询略过很长的数据范围。

  • 改善数据压缩。

    ClickHouse 以主键排序片段数据,所以,数据的一致性越高,压缩越好。

  • CollapsingMergeTreeSummingMergeTree 引擎里,数据合并时,会有额外的处理逻辑。

    在这种情况下,指定一个跟主键不同的 排序键 也是有意义的。

长的主键会对插入性能和内存消耗有负面影响,但主键中额外的列并不影响 SELECT 查询的性能。

选择跟排序键不一样主键

指定一个跟排序键(用于排序数据片段中行的表达式) 不一样的主键(用于计算写到索引文件的每个标记值的表达式)是可以的。 这种情况下,主键表达式元组必须是排序键表达式元组的一个前缀。

分区

https://clickhouse.tech/docs/zh/operations/table_engines/custom_partitioning_key/

(
    VisitDate Date, 
    Hour UInt8, 
    ClientID UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(VisitDate)
ORDER BY Hour;

分区键也可以是表达式元组(类似 [主键]例如:

ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/name', 'replica1', Sign)
PARTITION BY (toMonday(StartDate), EventType)
ORDER BY (CounterID, StartDate, intHash32(UserID));

上例中,我们设置按一周内的事件类型分区。

新数据插入到表中时,这些数据会存储为按主键排序的新片段(块)。插入后 10-15 分钟,同一分区的各个片段会合并为一整个片段。

可以通过 [system.parts]表查看表片段和分区信息。例如,假设我们有一个 visits 表,按月分区。对 system.parts 表执行 SELECT

SELECT 
    partition,
    name, 
    active
FROM system.parts 
WHERE table = 'visits'
──partition─┬─name───────────┬─active─┐
│ 201901    │ 201901_1_3_1   │      0 │
│ 201901    │ 201901_1_9_2   │      1 │
│ 201901    │ 201901_8_8_0   │      0 │
│ 201901    │ 201901_9_9_0   │      0 │
│ 201902    │ 201902_4_6_1   │      1 │
│ 201902    │ 201902_10_10_0 │      1 │
│ 201902    │ 201902_11_11_0 │      1 │
└───────────┴────────────────┴────────┘

partition 列存储分区的名称。此示例中有两个分区:201901201902。在 [ALTER ... PARTITION]语句中你可以使用该列值来指定分区名称。

name 列为分区中数据片段的名称。在 [ALTER ATTACH PART] 语句中你可以使用此列值中来指定片段名称。

这里我们拆解下第一部分的名称:201901_1_3_1

  1. 201901 是分区名称。
  2. 1 是数据块的最小编号。
  3. 3 是数据块的最大编号。
  4. 1 是块级别(即在由块组成的合并树中,该块在树中的深度)。

注意

旧类型表的片段名称为:20190117_20190123_2_2_0(最小日期 - 最大日期 - 最小块编号 - 最大块编号 - 块级别)。

active 列为片段状态。1 激活状态;0 非激活状态。非激活片段是那些在合并到较大片段之后剩余的源数据片段。损坏的数据片段也表示为非活动状态。

正如在示例中所看到的,同一分区中有几个独立的片段(例如,201901_1_3_1201901_1_9_2)。这意味着这些片段尚未合并。ClickHouse 大约在插入后15分钟定期报告合并操作,合并插入的数据片段。此外,你也可以使用 OPTIMIZE 语句直接执行合并。例:

OPTIMIZE TABLE visits PARTITION 201902;
┌─partition─┬─name───────────┬─active─┐
│ 201901    │ 201901_1_3_1   │      0 │
│ 201901    │ 201901_1_9_2   │      1 │
│ 201901    │ 201901_8_8_0   │      0 │
│ 201901    │ 201901_9_9_0   │      0 │
│ 201902    │ 201902_4_6_1   │      0 │
│ 201902    │ 201902_4_11_2  │      1 │
│ 201902    │ 201902_10_10_0 │      0 │
│ 201902    │ 201902_11_11_0 │      0 │
└───────────┴────────────────┴────────┘

非激活片段会在合并后的10分钟左右删除。

查看片段和分区信息的另一种方法是进入表的目录:/var/lib/clickhouse/data/<database>/<table>/。例如:

/var/lib/clickhouse/data/default/visits$ ls -l
total 40
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  1 16:48 201901_1_3_1
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:17 201901_1_9_2
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 15:52 201901_8_8_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 15:52 201901_9_9_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:17 201902_10_10_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:17 201902_11_11_0
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 16:19 201902_4_11_2
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  5 12:09 201902_4_6_1
drwxr-xr-x 2 clickhouse clickhouse 4096 Feb  1 16:48 detached
  • 物理存储
[root@izm5eja0h0h0p04ol7ijrpz 20200319_10_10_0]# pwd
/var/lib/clickhouse/data/logs/logs/20200319_10_10_0
[root@izm5eja0h0h0p04ol7ijrpz 20200319_10_10_0]# ll
total 104
-rw-r----- 1 clickhouse clickhouse 831 Mar 19 23:01 checksums.txt
-rw-r----- 1 clickhouse clickhouse 234 Mar 19 23:01 columns.txt
-rw-r----- 1 clickhouse clickhouse  34 Mar 19 23:01 content.bin
-rw-r----- 1 clickhouse clickhouse  48 Mar 19 23:01 content.mrk2
-rw-r----- 1 clickhouse clickhouse   1 Mar 19 23:01 count.txt
-rw-r----- 1 clickhouse clickhouse  30 Mar 19 23:01 create_time.bin
-rw-r----- 1 clickhouse clickhouse  48 Mar 19 23:01 create_time.mrk2
-rw-r----- 1 clickhouse clickhouse  38 Mar 19 23:01 ip.bin
-rw-r----- 1 clickhouse clickhouse  48 Mar 19 23:01 ip.mrk2
-rw-r----- 1 clickhouse clickhouse  32 Mar 19 23:01 level.bin
-rw-r----- 1 clickhouse clickhouse  48 Mar 19 23:01 level.mrk2
-rw-r----- 1 clickhouse clickhouse  34 Mar 19 23:01 log_file_name.bin
-rw-r----- 1 clickhouse clickhouse  48 Mar 19 23:01 log_file_name.mrk2
-rw-r----- 1 clickhouse clickhouse   8 Mar 19 23:01 minmax_partition_date.idx
-rw-r----- 1 clickhouse clickhouse   4 Mar 19 23:01 partition.dat
-rw-r----- 1 clickhouse clickhouse  30 Mar 19 23:01 partition_date.bin
-rw-r----- 1 clickhouse clickhouse  48 Mar 19 23:01 partition_date.mrk2
-rw-r----- 1 clickhouse clickhouse  34 Mar 19 23:01 primary.idx
-rw-r----- 1 clickhouse clickhouse  34 Mar 19 23:01 service_name.bin
-rw-r----- 1 clickhouse clickhouse  48 Mar 19 23:01 service_name.mrk2
-rw-r----- 1 clickhouse clickhouse  33 Mar 19 23:01 thread_name.bin
-rw-r----- 1 clickhouse clickhouse  48 Mar 19 23:01 thread_name.mrk2
-rw-r----- 1 clickhouse clickhouse  30 Mar 19 23:01 timestamp.bin
-rw-r----- 1 clickhouse clickhouse  48 Mar 19 23:01 timestamp.mrk2
-rw-r----- 1 clickhouse clickhouse  27 Mar 19 23:01 trace_id.bin
-rw-r----- 1 clickhouse clickhouse  48 Mar 19 23:01 trace_id.mrk2

文件夹 '201901_1_1_0','201901_1_7_1' 等是片段的目录。每个片段都与一个对应的分区相关,并且只包含这个月的数据(本例中的表按月分区)。

detached 目录存放着使用 DETACH 语句从表中分离的片段。损坏的片段也会移到该目录,而不是删除。服务器不使用detached目录中的片段。可以随时添加,删除或修改此目录中的数据 – 在运行 ATTACH 语句前,服务器不会感知到。

注意
在操作服务器时,你不能手动更改文件系统上的片段集或其数据,因为服务器不会感知到这些修改。对于非复制表,可以在服务器停止时执行这些操作,但不建议这样做。对于复制表,在任何情况下都不要更改片段文件。

注意
那些有相同分区表达式值的数据片段才会合并。这意味着 你不应该用太精细的分区方案(超过一千个分区)。否则,会因为文件系统中的文件数量和需要找开的文件描述符过多,导致 SELECT 查询效率不佳。

弹性支持

ClickHouse 集群由独立的分片组成,每一个分片由多个副本组成。集群不是弹性的,因此在添加新的分片后,数据不会自动在分片之间重新平衡。相反,集群负载将变得不均衡。该实现为你提供了更多控制,对于相对较小的集群,例如只有数十个节点的集群来说是很好的。但是对于我们在生产中使用的具有数百个节点的集群来说,这种方法成为一个重大缺陷。我们应该实现一个表引擎,使得该引擎能够跨集群扩展数据,同时具有动态复制的区域,这些区域能够在集群之间自动拆分和平衡。

The ClickHouse cluster consists of independent shards, and each shard consists of replicas. The cluster is not elastic, so after adding a new shard, data is not rebalanced between shards automatically. Instead, the cluster load will be uneven. This implementation gives you more control, and it is fine for relatively small clusters such as tens of nodes. But for clusters with hundreds of nodes that we are using in production, this approach becomes a significant drawback. We should implement a table engine that will span its data across the cluster with dynamically replicated regions that could be split and balanced between clusters automatically.

表结构设计参考

CREATE TABLE logs.logs ( 
    ip String, 
    log_file_name String,
    service_name String,
    thread_name String,
    trace_id String,
    timestamp DateTime,
    partition_date UInt32,
    level String, 
    content String,
    create_time DateTime
) ENGINE = MergeTree() PARTITION BY partition_date
ORDER BY (create_time,service_name, trace_id)
SETTINGS index_granularity = 8192

Distributed(cluster_name, db_name, table_name[, sharding_key])

sharding_key=rand()随机写

CREATE TABLE logs.logs_view
( 
    ip String, 
    log_file_name String,
    service_name String,
    thread_name String,
    trace_id String,
    timestamp DateTime,
    partition_date UInt32,
    level String, 
    content String,
    create_time DateTime
)ENGINE =Distributed(log_collector,logs, logs, rand());
  • 新建数据
insert into logs.logs(ip,log_file_name,service_name,thread_name,partition_date,level,content,create_time,timestamp)values('192.168.1.1','log.log','sparrow','thread',20200319,'level','content','2020-03-19 22:59:00','2020-03-19 22:58:00') ;

删除分区 delete parition

https://clickhouse.tech/docs/zh/query_language/alter/#alter_manipulations-with-partitions

  ALTER TABLE testdb.testtable DROP PARTITION '201907'

性能及使用建议

性能测试对比

https://clickhouse.tech/benchmark.html

https://clickhouse.yandex/docs/en/introduction/performance/

数据的写入性能

我们建议每次写入不少于1000行的批量写入,或每秒不超过一个写入请求。当使用tab-separated格式将一份数据写入到MergeTree表中时,写入速度大约为50到200MB/s。如果您写入的数据每行为1Kb,那么写入的速度为50,000到200,000行每秒。如果您的行更小,那么写入速度将更高。为了提高写入性能,您可以使用多个INSERT进行并行写入,这将带来线性的性能提升。

常用命令行

clickhouse-client -h 192.168.1.195 -u root --password=123456

clickhouse-client -m
clickhouse-client --multiline //开启多行模式
create database logs
show databases;

select * from system.clusters; //查询集群状态

安装配置

  • click house config.xml
<?xml version="1.0"?>
<yandex>
    <logger>
        <level>trace</level>
        <log>/var/log/clickhouse-server/clickhouse-server.log</log>
        <errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
        <size>1000M</size>
        <count>10</count>
    </logger>
    <http_port>8123</http_port>
    <tcp_port>9000</tcp_port>
    <interserver_http_port>9009</interserver_http_port>
    <interserver_http_host>my-host-name</interserver_http_host>

    <listen_host>0.0.0.0</listen_host>
    <max_connections>4096</max_connections>
    <keep_alive_timeout>3000</keep_alive_timeout>
    <max_concurrent_queries>1000</max_concurrent_queries>
    <max_table_size_to_drop>0</max_table_size_to_drop>
    <uncompressed_cache_size>21474836480</uncompressed_cache_size>
    <mark_cache_size>25769803776</mark_cache_size>
    <path>/data/clickhouse/</path>
    <tmp_path>/data/clickhouse/tmp/</tmp_path>
    <user_files_path>/data/clickhouse/user_files/</user_files_path>
    <users_config>users.xml</users_config>
    <default_profile>default</default_profile>
    <default_database>default</default_database>

    <remote_servers incl="clickhouse_remote_servers"/>
    <zookeeper incl="zookeeper-servers" optional="true" />
    <macros incl="macros" optional="true" />
    <builtin_dictionaries_reload_interval>3600</builtin_dictionaries_reload_interval>
    <format_schema_path>/data/clickhouse/format_schemas/</format_schema_path>
    <include_from>/etc/clickhouse-server/metrika.xml</include_from>
</yandex>

metrika.xml

<yandex>
<clickhouse_remote_servers>
     <log_collector>
        <shard>
            <internal_replication>true</internal_replication>
            <replica>
                <host>192.168.2.254</host>
                <port>9000</port>
                <user>root</user>
                <password>123456</password>
            </replica>
        </shard>

        <shard>
            <internal_replication>true</internal_replication>
            <replica>
                <host>122.168.2.255</host>
                <port>9000</port>
                <user>log_collector</user>
                <password>123456</password>
            </replica>
        </shard>
    </log_collector>
</clickhouse_remote_servers>


<zookeeper-servers>
  <node index="1">
    <host>192.168.209.78</host>
    <port>2181</port>
  </node>
  <node index="2">
    <host>192.168.192.166</host>
    <port>2181</port>
  </node>
  <node index="3">
    <host>192.168.209.79</host>
    <port>2181</port>
  </node>
  <node index="4">
    <host>192.168.192.179</host>
    <port>2181</port>
  </node>
  <node index="5">
    <host>192.168.209.92</host>
    <port>2181</port>
  </node>
  <node index="6">
    <host>192.168.209.94</host>
    <port>2181</port>
  </node>
  <node index="7">
    <host>192.168.192.184</host>
    <port>2181</port>
  </node>
</zookeeper-servers>

<macros>
    <replica>logs_01</replica>
</macros>


<networks>
   <ip>::/0</ip>
</networks>


<clickhouse_compression>
<case>
  <min_part_size>10000000000</min_part_size>
  <min_part_size_ratio>0.01</min_part_size_ratio>
  <method>lz4</method>
</case>
</clickhouse_compression>
</yandex>

users.xml

<?xml version="1.0"?>
<yandex>
    <profiles>
        <default>
            <max_memory_usage>25769803776</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
        </default>

        <readonly>
            <readonly>1</readonly>
            <max_memory_usage>25769803776</max_memory_usage>
            <use_uncompressed_cache>0</use_uncompressed_cache>
            <load_balancing>random</load_balancing>
        </readonly>
    </profiles>

    <users>
        <default>
            <password></password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
        </default>

       <log_collector>
            <password>123456</password>
            <networks incl="networks" replace="replace">
                <ip>::/0</ip>
            </networks>
            <profile>default</profile>
            <quota>default</quota>
            <allow_databases>
                  <database>logs</database>
            </allow_databases>
        </log_collector>
        <!-- Example of user with readonly access. -->
        <readonly>
            <password></password>
            <networks incl="networks" replace="replace">
                <ip>::1</ip>
                <ip>127.0.0.1</ip>
            </networks>
            <profile>readonly</profile>
            <quota>default</quota>
        </readonly>
    </users>

    <quotas>
        <default>
            <interval>
                <duration>3600</duration>
                <queries>0</queries>
                <errors>0</errors>
                <result_rows>0</result_rows>
                <read_rows>0</read_rows>
                <execution_time>0</execution_time>
            </interval>
        </default>
    </quotas>
</yandex>

服务启动

sudo systemctl start 
clickhouse-server.service
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 212,816评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,729评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 158,300评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,780评论 1 285
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,890评论 6 385
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,084评论 1 291
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,151评论 3 410
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,912评论 0 268
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,355评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,666评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,809评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,504评论 4 334
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,150评论 3 317
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,882评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,121评论 1 267
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,628评论 2 362
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,724评论 2 351

推荐阅读更多精彩内容