<div id="article_content" class="article_content clearfix"> <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/kdoc_html_views-1a98987dfd.css"> <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/ck_htmledit_views-25cebea3f9.css"> <div id="content_views" class="htmledit_views"> <p>官网：<a href="https://clickhouse.tech/docs/zh/engines/table-engines/" title="表引擎 | ClickHouse文档">表引擎 | ClickHouse文档</a></p> <p>表引擎在 <a href="https://so.csdn.net/so/search?q=ClickHouse&amp;spm=1001.2101.3001.7020" target="_blank" class="hl hl-1" data-report-click="{&quot;spm&quot;:&quot;1001.2101.3001.7020&quot;,&quot;dest&quot;:&quot;https://so.csdn.net/so/search?q=ClickHouse&amp;spm=1001.2101.3001.7020&quot;,&quot;extra&quot;:&quot;{\&quot;searchword\&quot;:\&quot;ClickHouse\&quot;}&quot;}" data-tit="ClickHouse" data-pretit="clickhouse">ClickHouse</a> 中的作用十分关键，直接决定了数据如何存储和读取、是否支持并发读写、是否支持 index、支持的 query 种类、是否支持主备复制等。</p> <h1><a name="t0"></a>1.表引擎概述</h1> <p>ClickHouse 提供了大约 28 种表引擎，各有各的用途，比如有 Log 系列用来做小表数据分析，MergeTree 系列用来做大数据量分析，而 Integration 系列则多用于外表数据集成。再考虑复制表Replicated 系列，分布式表 Distributed 等，纷繁复杂，新用户上手选择时常常感到迷惑。</p> <p>ClickHouse表引擎一共分为四个系列，分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交，根据场景组合使用。最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。对于大多数正式的任务，推荐使用MergeTree族中的引擎。</p> <p>Log、Special、Integration 主要用于特殊用途，场景相对有限。MergeTree 系列才是官方主推的存储引擎，支持几乎所有 ClickHouse 核心功能。</p> <p>一共分为四个系列，<span style="color:#f33b45;"><strong>分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交。</strong></span></p> <p>表引擎（即表的类型）决定了：</p> <ul><li>数据的存储方式和位置，写到哪里以及从哪里读取数据</li><li>支持哪些查询以及如何支持。</li><li>并发数据访问。</li><li>索引的使用（如果存在）。</li><li>是否可以执行多线程请求。</li><li>数据复制参数。</li></ul> <h1><a name="t1"></a>2.表引擎类型</h1> <h2><a name="t2"></a>2.1.Log系列</h2> <p>Log系列表引擎功能相对简单，主要用于快速写入小表（1百万行左右的表），然后全部读出的场景。当你需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。</p> <p>几种Log表引擎的共性是：</p> <ol><li>数据被顺序append写到磁盘上；</li><li>不支持delete、update；</li><li>不支持index；</li><li>不支持原子性写；</li><li>insert会阻塞select操作。</li></ol> <p>该类型的引擎有：</p> <ol><li>TinyLog</li><li>StripeLog</li><li>Log</li></ol> <p>主要特点：</p> <ol><li>数据存储在磁盘上。</li><li>写入时将数据追加在文件末尾。</li><li>不支持突变操作。</li><li>不支持索引。意味着 SELECT 在范围查询时效率不高。</li><li>非原子地写入数据。如果某些事情破坏了写操作，例如服务器的异常关闭，你将会得到一张包含了损坏数据的表。</li></ol> <p>它们彼此之间的区别是： 是否支持并发读写，性能问题，列存储问题</p> <ol><li>TinyLog：不支持并发读取数据文件，查询性能较差；格式简单，适合用来暂存中间数据；</li><li>StripLog：支持并发读取数据文件，查询性能比 TinyLog 好；将所有列存储在同一个大文件中，减少了文件个数；</li><li>Log：支持并发读取数据文件，查询性能比 TinyLog 好；每个列会单独存储在一个独立文件中。</li></ol> <h2><a name="t3"></a>2.2.Integration系列</h2> <p>该系统表引擎主要用于将外部数据导入到 ClickHouse 中，或者在 ClickHouse 中直接操作外部数据源。</p> <ul><li>Kafka：将Kafka Topic中的数据直接导入到ClickHouse；</li><li>MySQL：将Mysql作为存储引擎，直接在ClickHouse中对MySQL表进行select等操作；</li><li>JDBC/ODBC：通过指定jdbc、odbc连接串读取数据源；</li><li>HDFS：直接读取HDFS上的特定格式的数据文件；</li></ul> <p>用于与其他的数据存储与处理系统集成的引擎。支持<a href="https://clickhouse.tech/docs/zh/engines/table-engines/integrations/jdbc/" title="JDBC表引擎">JDBC表引擎</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/integrations/odbc/" title="ODBC">ODBC</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/integrations/hdfs/" title="HDFS">HDFS</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/integrations/kafka/" title="Kafka">Kafka</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/integrations/mysql/" title="MySQL">MySQL</a>。</p> <h2><a name="t4"></a><a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/join/" title="2.3.Special系列">2.3.Special系列</a></h2> <p>Special系列的表引擎，大多是为了特定场景而定制的。这里也挑选几个简单介绍，不做详述。</p> <ul><li>Memory：将数据存储在内存中，重启后会导致数据丢失。查询性能极好，适合于对于数据持久性没有要求的1亿一下的小表。在ClickHouse中，通常用来做临时表。</li><li>Buffer：为目标表设置一个内存buffer，当buffer达到了一定条件之后会flush到磁盘。</li><li>File：直接将本地文件作为数据存储；</li><li>Null：写入数据被丢弃、读取数据为空；</li></ul> <p>包含：<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/join/" title="关联表引擎">关联表引擎</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/generate/" title="随机数生成">随机数生成</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/materializedview/" title="MaterializedView">MaterializedView</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/null/" title="Null">Null</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/url/" title="URL(URL,格式)">URL(URL,格式)</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/memory/" title="内存表">内存表</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/distributed/" title="分布（Distributed）">分布（Distributed）</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/merge/" title="合并（Merge）">合并（Merge）</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/dictionary/" title="字典">字典</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/file/" title="文件(输入格式)">文件(输入格式)</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/external-data/" title="用于查询处理的外部数据">用于查询处理的外部数据</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/buffer/" title="缓冲区">缓冲区</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/view/" title="视图">视图</a>、<a href="https://clickhouse.tech/docs/zh/engines/table-engines/special/set/" title="设置">设置</a></p> <p>例如：Merge引擎：在同一个server上，多个相同结构的物理表，可以被整合成一张大的逻辑表，这张逻辑表的数据，就是包含了这些物理表中的所有数据。</p> <pre data-index="0"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> my_table_merge(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id UInt16,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> name String</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">) ENGINE <span class="hljs-operator">=</span> <span class="hljs-keyword">Merge</span>(currentDatbase(),<span class="hljs-string">'^my_merge'</span>);</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>distributed：在不同的server上，多个相同结构的物理表，可以被整合成一张大的逻辑表，这张逻辑表的数据，就是包含了这些物理表中的所有数据。类似于分布式。插入的数据会分布在不同的server上。</p> <pre data-index="1"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> my_table_distributed(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id UInt16,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> name String</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">) ENGINE <span class="hljs-operator">=</span> Distributed(perftest_3shards_1replicas,defaultdb1,my_aidtributed,id); </div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <h2><a name="t5"></a><a href="https://clickhouse.tech/docs/zh/engines/table-engines/mergetree-family/versionedcollapsingmergetree/#sidebar-sidebar-4-3-1" title="2.4.MergeTree系列">2.4.MergeTree系列</a></h2> <p>表引擎是ClickHouse设计实现中的一大特色。可以说，是表引擎决定了一张数据表最终的“性格”，比如数据表拥有何种特性、数据以何种形式被存储以及如何被加载。ClickHouse拥有非常庞大的表引擎体系，其共拥有<strong><span style="color:#f33b45;">合并树、外部存储、内存、文件、接口和其他6大类20多种表引擎</span></strong>。而在这众多的表引擎中，又属合并树（MergeTree）表引擎及其家族系列（*MergeTree）最为强大，在生产环境的绝大部分场景中，都会使用此系列的表引擎。因为只有合并树系列的表引擎才支持<strong><span style="color:#f33b45;">主键索引（一级索引，二级索引，跳数索引/稀疏索引）、数据分区、数据副本和数据采样</span></strong>这些特性，同时也只有此系列的表引擎<span style="color:#f33b45;"><strong>支持ALTER相关操作。</strong></span></p> <p>合并树家族自身也拥有多种表引擎的变种。其中MergeTree作为家族中最基础的表引擎，提供了主键索引、数据分区、数据副本和数据采样等基本能力，而家族中其他的表引擎则在MergeTree的基础之上各有所长。例如<strong><span style="color:#f33b45;">R<strong>e</strong>placingMergeTree表引擎具有删除重复数据的特性，</span></strong>而<strong><span style="color:#f33b45;">SummingMergeTree表引擎则会按照排序键自动聚合数据</span></strong>。如果给合并树系列的表引擎加上Replicated前缀，又会得到一组支持数据副本的表引擎，例如ReplicatedMergeTree、ReplicatedReplacingMergeTree、ReplicatedSummingMergeTree等。</p> <p>合并树表引擎家族如表所示：正交</p> <div class="table-box"><table align="center" border="1" cellpadding="1" cellspacing="1" style="width:500px;"><thead><tr><th>项目</th><th>类别</th><th>基础</th></tr></thead><tbody><tr><td>Replicated<br> 支持数据副本</td><td>Replacing<br> Summing<br> Aggregating<br> Collapsing<br> VersionedCollapsing<br> Graghite</td><td>MergeTree<br> 基础表引擎</td></tr></tbody></table></div> <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第一：MergeTree 表引擎主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引、数据TTL等。MergeTree 支持所有ClickHouse SQL 语法，但是有些功能与 MySQL 并不一致，比如在MergeTree 中主键并不用于去重。</p> <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第二：为了解决 MergeTree 相同主键无法去重的问题，ClickHouse 提供了 ReplacingMergeTree 引擎，用来做去重。ReplacingMergeTree 确保数据最终被去重，但是无法保证查询过程中主键不重复。因为相同主键的数据可能被 shard 到不同的节点，但是 compaction 只能在一个节点中进行，而且optimize 的时机也不确定。</p> <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第三：CollapsingMergeTree 引擎要求在建表语句中指定一个标记列 Sign（插入的时候指定为1，删除的时候指定为-1），后台 Compaction 时会将主键相同、Sign 相反的行进行折叠，也即删除。来消除ReplacingMergeTree 的限制。<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第四：为了解决 CollapsingMergeTree 乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree 表引擎在建表语句中新增了一列 Version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且 Version 相同、Sign 相反的行，在 Compaction 时会被删除。<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第五：ClickHouse 通过 SummingMergeTree 来支持对主键列进行预先聚合。在后台 Compaction时，会将主键相同的多行进行 sum 求和，然后使用一行数据取而代之，从而大幅度降低存储空间占用，提升聚合计算性能。<br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第六：AggregatingMergeTree 也是预先聚合引擎的一种，用于提升聚合计算的性能。与SummingMergeTree 的区别在于：SummingMergeTree 对非主键列进行 sum 聚合，而AggregatingMergeTree 则可以指定各种聚合函数。</p> <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;虽然合并树的变种很多，但MergeTree表引擎才是根基。作为合并树家族系列中最基础的表引擎，MergeTree具备了该系列其他表引擎共有的基本特征，所以吃透了MergeTree表引擎的原理，就能够掌握该系列引擎的精髓。</p> <h3><a name="t6"></a>2.4.1. MergeTree的创建方式</h3> <p>创建MergeTree数据表的方法，与普通的数据表的方法大致相同，但需要将ENGINE参数声明为MergeTree()，其完整的语法如下所示：</p> <pre data-index="2"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> [IF <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span>] [db_name.]table_name(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> name1 [type] [<span class="hljs-keyword">DEFAULT</span><span class="hljs-operator">|</span>MATERIALIZED<span class="hljs-operator">|</span>ALIAS expr],</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> name2[type] [<span class="hljs-keyword">DEFAULT</span><span class="hljs-operator">|</span>MATERIALIZED<span class="hljs-operator">|</span>ALIAS expr],</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> 省略...</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)ENGINE <span class="hljs-operator">=</span> MergeTree()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> [<span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> expr]</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> [<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> expr]</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> [<span class="hljs-keyword">PRIMARY</span> KEY expr]</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> [SAMPLE <span class="hljs-keyword">BY</span> expr]</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> [SETTINGS name<span class="hljs-operator">=</span><span class="hljs-keyword">value</span>，省略．..]</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>MergeTree表引擎除了常规参数之外，还拥有一些独有的配置选项。接下来会着重介绍其中几个重要的参数，包括它们的使用方法和工作原理。但是在此之前，还是先介绍一遍它们的作用。</p> <p>1、<strong>PARTITION BY [选填]：分区键，用于指定表数据以何种标准进行分区。</strong>分区键既可以是单个列字段，也可以通过元组的形式使用多个列字段，同时它也支持使用列表达式。如果不声明分区键，则ClickHouse会生成一个名为all的分区。合理使用数据分区，可以有效减少查询时数据文件的扫描范围。</p> <pre data-index="3"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> my_table_m1(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id UInt8,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> name String</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">) engine <span class="hljs-operator">=</span> MergeTree()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">partition</span> <span class="hljs-keyword">by</span> (id,name)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> id;</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>2、<strong>ORDER BY [必填]：排序键，用于指定在一个数据片段内，数据以何种标准排序。</strong>默认情况下主键（PRIMARY KEY）与排序键相同。排序键既可以是单个列字段，例如ORDER BY CounterID，也可以通过元组的形式使用多个列字段，例如ORDER BY（CounterID, EventDate）。当使用多个列字段排序时，以ORDERBY（CounterID, EventDate）为例，在单个数据片段内，数据首先会以CounterID排序，相同CounterID的数据再按EventDate排序。</p> <p>3、<strong>PRIMARY KEY [选填]：主键，顾名思义，声明后会依照主键字段生成一级索引，用于加速表查询。</strong>默认情况下，主键与排序键(ORDER BY)相同，所以通常直接使用ORDER BY代为指定主键，无须刻意通过PRIMARY KEY声明。所以在一般情况下，在单个数据片段内，数据与一级索引以相同的规则升序排列。与其他数据库不同，MergeTree主键允许存在重复数据（ReplacingMergeTree可以去重）。</p> <p>4、<strong>SAMPLE BY [选填]：抽样表达式，用于声明数据以何种标准进行采样。</strong>如果使用了此配置项，那么在主键的配置中也需要声明同样的表达式，抽样表达式需要配合SAMPLE子查询使用，这项功能对于选取抽样数据十分有用。例如：</p> <pre data-index="4"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">省略...</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)ENGINE<span class="hljs-operator">=</span> MergeTree(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span>(CounterID，EventDate,intHash32(UserID)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">SAMPLE <span class="hljs-keyword">BY</span> intHash32(UserID)</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>5、<strong>SETTINGS: index_granularity [选填]：</strong>index_granularity对于MergeTree而言是一项非常重要的参数，它表示索引的粒度，默认值为 8192。也就是说，MergeTree的索引在默认情况下，<strong>每间隔8192行数据才生成一条索引，</strong>其具体声明方式如下所示：</p> <pre data-index="5"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">省略...</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)ENGINE<span class="hljs-operator">=</span> MergeTree(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">省略...</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">SETTINGS index_granularity <span class="hljs-operator">=</span> <span class="hljs-number">8192</span>;</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>8192是一个神奇的数字，在 ClickHouse 中大量数值参数都有它的影子，可以被其整除（例如最小压缩块大小min_compress_block_size:65536）。通常情况下并不需要修改此参数，但理解它的工作原理有助于我们更好地使用MergeTree。</p> <p>6、<strong>SETTINGS: index_granularity_bytes [选填]：</strong>在19.11版本之前，ClickHouse只支持固定大小的索引间隔，由index_granularity控制，默认为8192。在新版本中，它增加了自适应间隔大小的特性，即根据每一批次写入数据的体量大小，动态划分间隔大小。而数据的体量大小，正是由index_granularity_bytes参数控制的，默认为10M(10×1024×1024)，设置为0表示不启动自适应功能。每条记录 1kb</p> <p>7、<strong>SETTINGS: enable_mixed_granularity_parts [选填]：</strong>设置是否开启自适应索引间隔的功能，默认开启。</p> <p>8、<strong>SETTINGS: merge_with_ttl_timeout [选填]：</strong>从19.6 版本开始，MergeTree 提供了数据 TTL 的功能，可以选择性的让某个列，或者某个表设置自动过期时间。</p> <p>9、<strong>SETTINGS: storage_policy [选填]：</strong>从19.15 版本开始，MergeTree 提供了多路径的存储策略，为应对大数据量的存储提供了方案。</p> <p>案例：</p> <pre data-index="6" class="set-code-hide" name="code"><code class="language-sql hljs"><ol class="hljs-ln" style="width:1254px"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">create</span> database if <span class="hljs-keyword">not</span> <span class="hljs-keyword">exists</span> mydb;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">use mydb;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">drop</span> <span class="hljs-keyword">table</span> if <span class="hljs-keyword">exists</span> mydb.my_table_merge1;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> mydb.my_table_merge1(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id UInt8,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> name String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> <span class="hljs-type">date</span> DateTime</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">) engine <span class="hljs-operator">=</span> MergeTree()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="12"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">partition</span> <span class="hljs-keyword">by</span> toYYYYMM(<span class="hljs-type">date</span>)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="13"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> id;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="14"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="15"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> mydb.my_table_merge1 <span class="hljs-keyword">values</span> (<span class="hljs-number">1</span>, <span class="hljs-string">'aa'</span>, <span class="hljs-string">'2021-01-02 22:14:52'</span>), (<span class="hljs-number">2</span>,<span class="hljs-string">'bb'</span>, <span class="hljs-string">'2021-02-02 16:14:52'</span>), (<span class="hljs-number">3</span>, <span class="hljs-string">'cc'</span>, <span class="hljs-string">'2021-01-02 12:45:52'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="16"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> mydb.my_table_merge1 <span class="hljs-keyword">values</span> (<span class="hljs-number">4</span>, <span class="hljs-string">'aa'</span>, <span class="hljs-string">'2021-03-02 22:14:52'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="17"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> mydb.my_table_merge1 <span class="hljs-keyword">values</span> (<span class="hljs-number">5</span>, <span class="hljs-string">'bb'</span>, <span class="hljs-string">'2021-03-03 22:14:52'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="18"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> mydb.my_table_merge1 <span class="hljs-keyword">values</span> (<span class="hljs-number">6</span>, <span class="hljs-string">'cc'</span>, <span class="hljs-string">'2021-03-04 22:14:52'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="19"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> mydb.my_table_merge1;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="20"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> mydb.my_table_merge1 <span class="hljs-keyword">values</span> (<span class="hljs-number">4</span>, <span class="hljs-string">'aa'</span>, <span class="hljs-string">'2021-01-02 22:14:52'</span>), (<span class="hljs-number">5</span>,<span class="hljs-string">'bb'</span>, <span class="hljs-string">'2021-02-02 16:14:52'</span>), (<span class="hljs-number">6</span>, <span class="hljs-string">'cc'</span>, <span class="hljs-string">'2021-01-02 12:45:52'</span>);</div></div></li></ol></code><div class="hide-preCode-box"><span class="hide-preCode-bt" data-report-view="{&quot;spm&quot;:&quot;1001.2101.3001.7365&quot;}"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></span></div><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>结果如图一图二所示：</p> <pre data-index="7" class="set-code-hide" name="code"><code class="language-bash hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">hadoop1 :) select * from mydb.my_table_merge1;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">SELECT * FROM mydb.my_table_merge1</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─<span class="hljs-built_in">id</span>─┬─name─┬────────────────<span class="hljs-built_in">date</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ 1 │ aa │ 2021-01-02 22:14:52 │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ 3 │ cc │ 2021-01-02 12:45:52 │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└────┴──────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─<span class="hljs-built_in">id</span>─┬─name─┬────────────────<span class="hljs-built_in">date</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ 4 │ aa │ 2021-01-02 22:14:52 │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ 6 │ cc │ 2021-01-02 12:45:52 │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="12"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└────┴──────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="13"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─<span class="hljs-built_in">id</span>─┬─name─┬────────────────<span class="hljs-built_in">date</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="14"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ 2 │ bb │ 2021-02-02 16:14:52 │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="15"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└────┴──────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="16"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─<span class="hljs-built_in">id</span>─┬─name─┬────────────────<span class="hljs-built_in">date</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="17"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ 5 │ bb │ 2021-02-02 16:14:52 │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="18"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└────┴──────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="19"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─<span class="hljs-built_in">id</span>─┬─name─┬────────────────<span class="hljs-built_in">date</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="20"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ 4 │ aa │ 2021-03-02 22:14:52 │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="21"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└────┴──────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="22"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─<span class="hljs-built_in">id</span>─┬─name─┬────────────────<span class="hljs-built_in">date</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="23"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ 5 │ bb │ 2021-03-03 22:14:52 │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="24"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└────┴──────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="25"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─<span class="hljs-built_in">id</span>─┬─name─┬────────────────<span class="hljs-built_in">date</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="26"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ 6 │ cc │ 2021-03-04 22:14:52 │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="27"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└────┴──────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="28"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="29"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">9 rows <span class="hljs-keyword">in</span> <span class="hljs-built_in">set</span>. Elapsed: 0.003 sec. </div></div></li></ol></code><div class="hide-preCode-box"><span class="hide-preCode-bt" data-report-view="{&quot;spm&quot;:&quot;1001.2101.3001.7365&quot;}"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></span></div><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <pre data-index="8" class="set-code-hide" name="code"><code class="language-bash hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">[root@hadoop1 my_table_merge1]<span class="hljs-comment"># pwd</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">/var/lib/clickhouse/data/mydb/my_table_merge1</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">[root@hadoop1 my_table_merge1]<span class="hljs-comment"># ll -a</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">total 4</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 10 root root 188 Jun 5 22:43 .</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 4 root root 41 Jun 5 22:42 ..</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:42 202101_1_1_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:43 202101_6_6_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:42 202102_2_2_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:43 202102_7_7_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:42 202103_3_3_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="12"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:42 202103_4_4_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="13"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:42 202103_5_5_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="14"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 6 Jun 5 22:42 detached</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="15"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 1 Jun 5 22:42 format_version.txt</div></div></li></ol></code><div class="hide-preCode-box"><span class="hide-preCode-bt" data-report-view="{&quot;spm&quot;:&quot;1001.2101.3001.7365&quot;}"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></span></div><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>当执行<span style="color:#3399ea;"> </span><span style="color:#f33b45;">optimize table mydb.my_table_merge1 final;</span>后，如下图所示，会新生成202101_1_6_1、202102_2_7_1、202103_3_5_1文件夹。查询合并后的数据显示也会有所变化。</p> <pre data-index="9" class="set-code-hide" name="code"><code class="hljs language-cobol"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">hadoop</span><span class="hljs-number">1</span> :) <span class="hljs-keyword">select</span> <span class="hljs-operator">*</span> <span class="hljs-keyword">from</span> mydb.my_<span class="hljs-keyword">table</span>_<span class="hljs-keyword">merge</span><span class="hljs-number">1</span>;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">SELECT</span> <span class="hljs-operator">*</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">FROM m</span>ydb.my_<span class="hljs-keyword">table</span>_<span class="hljs-keyword">merge</span><span class="hljs-number">1</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─id─┬─name─┬────────────────<span class="hljs-keyword">date</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ <span class="hljs-number">1</span> │ aa │ <span class="hljs-number">2021</span>-<span class="hljs-number">01</span>-<span class="hljs-number">02</span> <span class="hljs-number">22</span>:<span class="hljs-number">14</span>:<span class="hljs-number">52</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ <span class="hljs-number">3</span> │ cc │ <span class="hljs-number">2021</span>-<span class="hljs-number">01</span>-<span class="hljs-number">02</span> <span class="hljs-number">12</span>:<span class="hljs-number">45</span>:<span class="hljs-number">52</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ <span class="hljs-number">4</span> │ aa │ <span class="hljs-number">2021</span>-<span class="hljs-number">01</span>-<span class="hljs-number">02</span> <span class="hljs-number">22</span>:<span class="hljs-number">14</span>:<span class="hljs-number">52</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ <span class="hljs-number">6</span> │ cc │ <span class="hljs-number">2021</span>-<span class="hljs-number">01</span>-<span class="hljs-number">02</span> <span class="hljs-number">12</span>:<span class="hljs-number">45</span>:<span class="hljs-number">52</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└────┴──────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="12"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─id─┬─name─┬────────────────<span class="hljs-keyword">date</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="13"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ <span class="hljs-number">2</span> │ bb │ <span class="hljs-number">2021</span>-<span class="hljs-number">02</span>-<span class="hljs-number">02</span> <span class="hljs-number">16</span>:<span class="hljs-number">14</span>:<span class="hljs-number">52</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="14"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ <span class="hljs-number">5</span> │ bb │ <span class="hljs-number">2021</span>-<span class="hljs-number">02</span>-<span class="hljs-number">02</span> <span class="hljs-number">16</span>:<span class="hljs-number">14</span>:<span class="hljs-number">52</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="15"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└────┴──────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="16"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─id─┬─name─┬────────────────<span class="hljs-keyword">date</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="17"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ <span class="hljs-number">4</span> │ aa │ <span class="hljs-number">2021</span>-<span class="hljs-number">03</span>-<span class="hljs-number">02</span> <span class="hljs-number">22</span>:<span class="hljs-number">14</span>:<span class="hljs-number">52</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="18"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ <span class="hljs-number">5</span> │ bb │ <span class="hljs-number">2021</span>-<span class="hljs-number">03</span>-<span class="hljs-number">03</span> <span class="hljs-number">22</span>:<span class="hljs-number">14</span>:<span class="hljs-number">52</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="19"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ <span class="hljs-number">6</span> │ cc │ <span class="hljs-number">2021</span>-<span class="hljs-number">03</span>-<span class="hljs-number">04</span> <span class="hljs-number">22</span>:<span class="hljs-number">14</span>:<span class="hljs-number">52</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="20"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└────┴──────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="21"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="22"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">9 rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>. Elapsed: <span class="hljs-number">0.003</span> sec. </div></div></li></ol></code><div class="hide-preCode-box"><span class="hide-preCode-bt" data-report-view="{&quot;spm&quot;:&quot;1001.2101.3001.7365&quot;}"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></span></div><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <pre data-index="10" class="set-code-hide" name="code"><code class="language-bash hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">[root@hadoop1 my_table_merge1]<span class="hljs-comment"># ll -a</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">total 4</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 13 root root 248 Jun 5 22:49 .</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 4 root root 41 Jun 5 22:42 ..</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:42 202101_1_1_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:49 202101_1_6_1</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:43 202101_6_6_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:42 202102_2_2_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:49 202102_2_7_1</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:43 202102_7_7_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:42 202103_3_3_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="12"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:49 202103_3_5_1</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="13"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:42 202103_4_4_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="14"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:42 202103_5_5_0</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="15"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 6 Jun 5 22:42 detached</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="16"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 1 Jun 5 22:42 format_version.txt</div></div></li></ol></code><div class="hide-preCode-box"><span class="hide-preCode-bt" data-report-view="{&quot;spm&quot;:&quot;1001.2101.3001.7365&quot;}"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></span></div><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>在这里需要了解下这种目录名称的含义：<span style="color:#f33b45;">其组成为PartitionID_ MinBlockNum_MaxBlockNum_Level</span>，以202101_1_6_1为例，202101表示分区目录的ID; 1_6分别表示最小的数据块编号与最大的数据块编号；而最后的_1则表示目前合并的层级。接下来开始分别解释它们的含义：</p> <ol><li>PartitionID：分区ID</li><li>MinBlockNum和MaxBlockNum：顾名思义，最小数据块编号与最大数据块编号。ClickHouse在这里的命名似乎有些歧义，很容易让人与稍后会介绍到的数据压缩块混淆。但是本质上它们毫无关系，这里的BlockNum是一个整型的自增长编号。如果将其设为n的话，那么计数n在单张MergeTree数据表内全局累加，n从1开始，每当新创建一个分区目录时，计数n就会累积加1。对于一个新的分区目录而言，MinBlockNum与MaxBlockNum取值一样，同等于n，例如201905_1_1_0、201906_2_2_0以此类推。但是也有例外，当分区目录发生合并时，对于新产生的合并目录MinBlockNum与MaxBlockNum有着另外的取值规则。</li><li>Level：合并的层级，可以理解为某个分区被合并过的次数，或者这个分区的年龄。数值越高表示年龄越大。Level计数与BlockNum有所不同，它并不是全局累加的。对于每一个新创建的分区目录而言，其初始值均为0。之后，以分区为单位，如果相同分区发生合并动作，则在相应分区内计数累积加1。</li></ol> <p>接下来进入到其中一个目录，以202101_1_6_1为例：</p> <pre data-index="11" class="set-code-hide" name="code"><code class="language-bash hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">[root@hadoop1 202101_1_6_1]<span class="hljs-comment"># ll -a</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">total 48</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 2 root root 221 Jun 5 22:49 .</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">drwxr-x---. 13 root root 248 Jun 5 22:49 ..</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 384 Jun 5 22:49 checksums.txt</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 78 Jun 5 22:49 columns.txt</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 1 Jun 5 22:49 count.txt</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 43 Jun 5 22:49 date.bin</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 48 Jun 5 22:49 date.mrk2</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 30 Jun 5 22:49 id.bin</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 48 Jun 5 22:49 id.mrk2</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="12"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 8 Jun 5 22:49 minmax_date.idx</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="13"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 38 Jun 5 22:49 name.bin</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="14"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 48 Jun 5 22:49 name.mrk2</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="15"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 4 Jun 5 22:49 partition.dat</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="16"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">-rw-r-----. 1 root root 2 Jun 5 22:49 primary.idx</div></div></li></ol></code><div class="hide-preCode-box"><span class="hide-preCode-bt" data-report-view="{&quot;spm&quot;:&quot;1001.2101.3001.7365&quot;}"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></span></div><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>一张数据表的完整物理结构分为3个层级，依次是数据表目录、分区目录及各分区下具体的数据文件。接下来就逐一介绍它们的作用。</p> <p>（1）<strong>partition：</strong>分区目录，余下各类数据文件（primary.idx、[Column].mrk、[Column]. bin等）都是以分区目录的形式被组织存放的，属于相同分区的数据，最终会被合并到同一个分区目录，而不同分区的数据，永远不会被合并在一起。</p> <p>（2）<strong>checksums.txt：</strong>校验文件，使用二进制格式存储。它保存了余下各类文件(primary. idx、count.txt等)的size大小及size的哈希值，用于快速校验文件的完整性和正确性。</p> <p>（3）<strong>columns.txt：</strong>列信息文件，使用明文格式存储。用于保存此数据分区下的列字段信息，例如：</p> <pre data-index="12"><code class="hljs language-cobol"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">column</span>s <span class="hljs-keyword">format</span> version: <span class="hljs-number">1</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-comment">3 colu</span>mns:</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">`id` UInt<span class="hljs-number">8</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">`name` <span class="hljs-keyword">String</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">`<span class="hljs-keyword">date</span>` DateTime</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>（4）<strong>count.txt：</strong>计数文件，使用明文格式存储。用于记录当前数据分区目录下数据的总行数，例如：</p> <p>（5）<strong>primary.idx：</strong>一级索引文件，使用二进制格式存储。用于存放稀疏索引，一张MergeTree表只能声明一次一级索引（通过ORDER BY或者PRIMARY KEY）。借助稀疏索引，在数据查询的时能够排除主键条件范围之外的数据文件，从而有效减少数据扫描范围，加速查询速度。</p> <p>（6）<strong>[Column].bin：</strong>数据文件，使用压缩格式存储，默认为LZ4压缩格式，用于存储某一列的数据。由于MergeTree采用列式存储，所以每一个列字段都拥有独立的 .bin 数据文件，并以列字段名称命名（例如CounterID.bin、EventDate.bin等）。</p> <p>（7）<strong>[Column].mrk：</strong>列字段标记文件，使用二进制格式存储。标记文件中保存了．bin文件中数据的偏移量信息。标记文件与稀疏索引对齐，又与 .bin 文件一一对应，所以MergeTree通过标记文件建立了primary.idx稀疏索引与 .bin 数据文件之间的映射关系。即首先通过稀疏索引（primary.idx）找到对应数据的偏移量信息（.mrk），再通过偏移量直接从．bin文件中读取数据。由于．mrk标记文件与．bin文件一一对应，所以MergeTree中的每个列字段都会拥有与其对应的．mrk标记文件（例如CounterID.mrk、EventDate.mrk等）。</p> <p>（8）<strong>[Column].mrk2：</strong>如果使用了自适应大小的索引间隔，则标记文件会以 .mrk2 命名。它的工作原理和作用与 .mrk 标记文件相同。</p> <p>（9）<strong>partition.dat 与 minmax_[Column].idx：</strong>如果使用了分区键，例如 PARTITION BYtoYYYYMM(date) ，则会额外生成 partition.dat 与 minmax 索引文件 minmax_date.idx，它们均使用二进制格式存储。partition.dat 用于保存当前分区下分区表达式最终生成的值；而 minmax_date.idx用于记录当前分区下分区字段对应原始数据的最小和最大值。例如date字段对应的原始数据为2019-05-01、2019-05-05，分区表达式为PARTITION BY toYYYYMM(date)。partition.dat中保存的值将会是2019-05，而 minmax_date.idx 中保存的值将会是 2019-05-012019-05-05。在这些分区索引的作用下，进行数据查询时能够快速跳过不必要的数据分区目录，从而减少最终需要扫描的数据范围。</p> <p>（10）<strong>skp_idx_[Column].idx与skp_idx_[Column].mrk：</strong>如果在建表语句中声明了二级索引，则会额外生成相应的二级索引与标记文件，它们同样也使用二进制存储。二级索引在ClickHouse中又称跳数索引，目前拥有minmax、set、ngrambf_v1和tokenbf_v1四种类型。这些索引的最终目标与一级稀疏索引相同，都是为了进一步减少所需扫描的数据范围，以加速整个查询过程。</p> <p></p> <h3><a name="t7"></a>2.4.2.数据TTL</h3> <p>TTL： time to live 生命周期TTL ，顾名思义，它表示数据的存活时间。在MergeTree中，可以为某个列字段或整张表设置TTL。当时间到达时，如果是列字段级别的TTL，则会删除这一列的数据；如果是表级别的TTL，则会删除整张表的数据；如果同时设置了列级别和表级别的TTL，则会以先到期的那个为主。</p> <p><span style="color:#f33b45;"><strong>注意：使用当前系统时间来判断时间间隔。比方说今天是2021-06-06。有设置过期时间是1天。有插入2021-06-01的数据，会自动被清理</strong></span></p> <ul><li>针对单独的列指定 TTL ： 如果超过了 TTL之后，当前类的数据被清空，全部置为默认值</li><li>针对表指定 TTL： 如果这个TTL到期了， 整个表清空</li></ul> <p>1.列级别的TTL</p> <pre data-index="13"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> ttl_table_v1(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> create_time DateTime,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> code String TTL create_time <span class="hljs-operator">+</span> <span class="hljs-type">INTERVAL</span> <span class="hljs-number">10</span> <span class="hljs-keyword">SECOND</span>,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> type UInt8 TTL create_time <span class="hljs-operator">+</span> <span class="hljs-type">INTERVAL</span> <span class="hljs-number">10</span> <span class="hljs-keyword">SECOND</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)ENGINE <span class="hljs-operator">=</span>MergeTree</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> toYYYYMM(create_time)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> id;</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>触发TTL的命令：optimize TABLE ttl_table_v1 FINAL;</p> <p>可以对TTL进行修改：ALTER TABLE ttl_table_v1 MODIFY COLUMN code String TTL create_time + INTERVAL 1 DAY;</p> <p>2.表级别的TTL</p> <pre data-index="14"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> ttl_table_v2(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> create_time DateTime,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> code String TTL create_time <span class="hljs-operator">+</span> <span class="hljs-type">INTERVAL</span> <span class="hljs-number">10</span> <span class="hljs-keyword">SECOND</span>,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> type UInt8 TTL</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)ENGINE <span class="hljs-operator">=</span>MergeTree</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> toYYYYMM(create_time)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> create_time</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">TTL create_time <span class="hljs-operator">+</span> <span class="hljs-type">INTERVAL</span> <span class="hljs-number">1</span> <span class="hljs-keyword">DAY</span>;</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>可以对TTL进行修改：</p> <p>同样也可以对表级别的TTL进行修改：ALTER TABLE ttl_table_v2 MODIFY TTL create_time + INTERVAL 3 DAY;</p> <p>虽然现在没有提供删除 TTL 声明的方法，但是提供了控制全局 TTL 合并任务的启停方法：SYSTEM STOP/START TTL MERGES;</p> <h3><a name="t8"></a>2.4.3.ReplacingMergeTree(去重)</h3> <p>虽然 MergeTree 拥有主键，但是它的主键却没有唯一键的约束。这意味着即便多行数据的主键相同，它们还是能够被正常写入。在某些使用场合，用户并不希望数据表中含有重复的数据。ReplacingMergeTree 就是在这种背景下为了数据去重而设计的，它能够在合并分区时删除重复的数据。它的出现，确实也在一定程度上解决了重复数据的问题。为什么说是“一定程度”？--&gt;<span style="color:#f33b45;">基于同一分区，才会进行去重</span>。</p> <p>创建一张 ReplacingMergeTree 表的方法与创建普通 MergeTree 表无异，只需要替换 Engine：</p> <pre data-index="15"><code class="language-sql hljs">ENGINE <span class="hljs-operator">=</span> ReplacingMergeTree(ver);</code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>例如：</p> <pre data-index="16" class="set-code-hide" name="code"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> mydb.replace_table (</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id UInt16,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> name String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> create_time <span class="hljs-type">Date</span>,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> comment Nullable(String)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">) ENGINE <span class="hljs-operator">=</span> ReplacingMergeTree()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> create_time</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> (id,name)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">primary</span> key(id)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">SETTINGS index_granularity<span class="hljs-operator">=</span><span class="hljs-number">8192</span>;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="12"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="13"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> mydb.replace_table <span class="hljs-keyword">values</span>(<span class="hljs-number">0</span>,<span class="hljs-string">'张三'</span>,<span class="hljs-string">'2019-12-12'</span>, <span class="hljs-string">'a'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="14"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> mydb.replace_table <span class="hljs-keyword">values</span>(<span class="hljs-number">0</span>,<span class="hljs-string">'张三'</span>,<span class="hljs-string">'2019-12-12'</span>, <span class="hljs-string">'b'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="15"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> mydb.replace_table <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>,<span class="hljs-string">'张三'</span>,<span class="hljs-string">'2019-12-13'</span>, <span class="hljs-string">'c'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="16"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> mydb.replace_table <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>,<span class="hljs-string">'李四'</span>,<span class="hljs-string">'2019-12-13'</span>, <span class="hljs-string">'d'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="17"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> mydb.replace_table <span class="hljs-keyword">values</span>(<span class="hljs-number">2</span>,<span class="hljs-string">'张三'</span>,<span class="hljs-string">'2019-12-14'</span>, <span class="hljs-string">'e'</span>);</div></div></li></ol></code><div class="hide-preCode-box"><span class="hide-preCode-bt" data-report-view="{&quot;spm&quot;:&quot;1001.2101.3001.7365&quot;}"><img class="look-more-preCode contentImg-no-view" src="https://csdnimg.cn/release/blogv2/dist/pc/img/newCodeMoreWhite.png" alt="" title=""></span></div><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>使用optimize TABLE mydb.replace_table FINAL;</p> <p>总结如下：</p> <ol><li><span style="color:#f33b45;">使用ORBER BY排序键作为判断重复数据的唯一键，注意primary key只是用来加索引的。例如这里，只会删除第二条数据，第四条数据不会被删除。</span></li><li><span style="color:#f33b45;">只有在合并分区的时候才会触发删除重复数据的逻辑。</span></li><li><span style="color:#f33b45;">以数据分区为单位删除重复数据。当分区合并时，同一分区内的重复数据会被删除；不同分区之间的重复数据不会被删除。</span></li><li><span style="color:#f33b45;">在进行数据去重时，因为分区内的数据已经基于ORBER BY进行了排序，所以能够找到那些相邻的重复数据。</span></li><li><span style="color:#f33b45;">数据去重策略有两种：</span> <ol><li><span style="color:#f33b45;">如果没有设置ver版本号，则保留同一组重复数据中的最后一行</span></li><li><span style="color:#f33b45;">如果设置了ver版本号，则保留同一组重复数据中ver字段取值最大的那一行</span></li></ol></li></ol> <h3><a name="t9"></a>2.4.4.SummingMergeTree</h3> <p>假设有这样一种查询需求：终端用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的（GROUP BY条件明确，且不会随意改变）。</p> <p>对于这样的查询场景，在ClickHouse中如何解决呢？最直接的方案就是使用 MergeTree 存储数据，然后通过GROUP BY 聚合查询，并利用 SUM 聚合函数汇总结果。这种方案存在两个问题。</p> <ol><li>存在额外的存储开销：终端用户不会查询任何明细数据，只关心汇总结果，所以不应该一直保存所有的明细数据。</li><li>存在额外的查询开销：终端用户只关心汇总结果，虽然MergeTree性能强大，但是每次查询都进行实时聚合计算也是一种性能消耗。</li></ol> <p>SummingMergeTree 就是为了应对这类查询场景而生的。顾名思义，<span style="color:#f33b45;">它能够在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分组下的多行数据汇总合并成一行，这样既减少了数据行，又降低了后续汇总查询的开销。</span></p> <p>在先前介绍 MergeTree 原理时曾提及，在 MergeTree 的每个数据分区内，数据会按照 ORDER BY 表达式排序。主键索引也会按照 PRIMARY KEY 表达式取值并排序。而 ORDER BY 可以指代主键，所以在一般情形下，只单独声明 ORDER BY 即可。此时，ORDER BY 与 PRIMARY KEY 定义相同，数据排序与主键索引相同。</p> <p>如果需要同时定义 ORDER BY 与 PRIMARY KEY，通常只有一种可能，那便是明确希望 ORDER BY 与PRIMARY KEY不同。这种情况通常只会在使用 SummingMergeTree 或 AggregatingMergeTree 时才会出现。这是为何呢？<span style="color:#f33b45;">这是因为 SummingMergeTree 与 AggregatingMergeTree 的聚合都是根据ORDER BY 进行的。</span>由此可以引出两点原因：主键与聚合的条件定义分离，为修改聚合条件留下空间。</p> <pre data-index="17"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> summing_table(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> city String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> v1 UInt32,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> v2 Float64,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> create_time DateTime</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">) ENGINE<span class="hljs-operator">=</span>SummingMergeTree()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> toYYYYMM(create_time)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> (id,city)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PRIMARY</span> KEY id;</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <pre data-index="18"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> summing_table <span class="hljs-keyword">values</span>(<span class="hljs-string">'A001'</span>, <span class="hljs-string">'wuhan'</span>, <span class="hljs-number">10</span>, <span class="hljs-number">20</span>, <span class="hljs-string">'2019-08-10 17:00:00'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> summing_table <span class="hljs-keyword">values</span>(<span class="hljs-string">'A001'</span>, <span class="hljs-string">'wuhan'</span>, <span class="hljs-number">20</span>, <span class="hljs-number">30</span>, <span class="hljs-string">'2019-08-20 17:00:00'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> summing_table <span class="hljs-keyword">values</span>(<span class="hljs-string">'A001'</span>, <span class="hljs-string">'zhuhai'</span>, <span class="hljs-number">20</span>, <span class="hljs-number">30</span>, <span class="hljs-string">'2019-08-10 17:00:00'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> summing_table <span class="hljs-keyword">values</span>(<span class="hljs-string">'A001'</span>, <span class="hljs-string">'wuhan'</span>, <span class="hljs-number">10</span>, <span class="hljs-number">20</span>, <span class="hljs-string">'2019-02-10 09:00:00'</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> summing_table <span class="hljs-keyword">values</span>(<span class="hljs-string">'A002'</span>, <span class="hljs-string">'wuhan'</span>, <span class="hljs-number">60</span>, <span class="hljs-number">50</span>, <span class="hljs-string">'2019-10-10 17:00:00'</span>);</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>执行：optimize TABLE mydb.summing_table FINAL；</p> <pre data-index="19"><code class="hljs language-cobol"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─id───┬─city──┬─v<span class="hljs-number">1</span>─┬─v<span class="hljs-number">2</span>─┬─────────create_<span class="hljs-keyword">time</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ A<span class="hljs-number">001</span> │ wuhan │ <span class="hljs-number">10</span> │ <span class="hljs-number">20</span> │ <span class="hljs-number">2019</span>-<span class="hljs-number">02</span>-<span class="hljs-number">10</span> <span class="hljs-number">09</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└──────┴───────┴────┴────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─id───┬─city───┬─v<span class="hljs-number">1</span>─┬─v<span class="hljs-number">2</span>─┬─────────create_<span class="hljs-keyword">time</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ A<span class="hljs-number">001</span> │ wuhan │ <span class="hljs-number">30</span> │ <span class="hljs-number">50</span> │ <span class="hljs-number">2019</span>-<span class="hljs-number">08</span>-<span class="hljs-number">10</span> <span class="hljs-number">17</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ A<span class="hljs-number">001</span> │ zhuhai │ <span class="hljs-number">20</span> │ <span class="hljs-number">30</span> │ <span class="hljs-number">2019</span>-<span class="hljs-number">08</span>-<span class="hljs-number">10</span> <span class="hljs-number">17</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└──────┴────────┴────┴────┴─────────────────────┘</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─id───┬─city──┬─v<span class="hljs-number">1</span>─┬─v<span class="hljs-number">2</span>─┬─────────create_<span class="hljs-keyword">time</span>─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ A<span class="hljs-number">002</span> │ wuhan │ <span class="hljs-number">60</span> │ <span class="hljs-number">50</span> │ <span class="hljs-number">2019</span>-<span class="hljs-number">10</span>-<span class="hljs-number">10</span> <span class="hljs-number">17</span>:<span class="hljs-number">00</span>:<span class="hljs-number">00</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└──────┴───────┴────┴────┴─────────────────────┘</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>可以看到，第一条数据和第三条数据进行了汇总。而不同分区之间，数据没有被汇总合并。</p> <p>总结如下：</p> <ol><li><span style="color:#f33b45;">用ORBER BY排序键作为聚合数据的条件Key。</span></li><li><span style="color:#f33b45;">只有在合并分区的时候才会触发汇总的逻辑。</span></li><li><span style="color:#f33b45;">以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。</span></li><li><span style="color:#f33b45;">如果在定义引擎时指定了columns汇总列（非主键的数值类型字段），则SUM汇总这些列字段；如果未指定，则聚合所有非主键的数值类型字段。</span></li><li><span style="color:#f33b45;">在进行数据汇总时，因为分区内的数据已经基于ORBER BY排序，所以能够找到相邻且拥有相同聚合Key的数据。</span></li><li><span style="color:#f33b45;">在汇总数据时，同一分区内，相同聚合Key的多行数据会合并成一行。其中，汇总字段会进行SUM计算；对于那些非汇总字段，则会使用第一行数据的取值。</span></li><li><span style="color:#f33b45;">支持嵌套结构，但列字段名称必须以Map后缀结尾。嵌套类型中，默认以第一个字段作为聚合Key。除第一个字段以外，任何名称以Key、Id或Type为后缀结尾的字段，都将和第一个字段一起组成复合Key。</span></li></ol> <h3><a name="t10"></a>2.4.5.AggregatingMergeTree</h3> <p>AggregatingMergeTree没有任何额外的设置参数，在分区合并时，在每个数据分区内，会按照ORDERBY聚合。而使用何种聚合函数，以及针对哪些列字段计算，则是通过定义AggregateFunction数据类型实现的。以下面的语句为例：</p> <pre data-index="20"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> agg_table(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> city String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> code AggregateFunction(uniq, String),</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> <span class="hljs-keyword">value</span> AggregateFunction(sum, UInt32),</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> create_time DateTime</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)ENGINE<span class="hljs-operator">=</span> AggregatingMergeTree()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> toYYYYMM(create_time)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> (id,city)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PRIMARY</span> KEY id;</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>由于单独使用会比较繁琐，所有在实际情况中一般使用AggregatingMergeTree的物化视图。如下：</p> <pre data-index="21"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> agg_table_basic(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> city String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> code String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> <span class="hljs-keyword">value</span> UInt32</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)ENGINE<span class="hljs-operator">=</span>MergeTree()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> city</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> (id,city);</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>通常会使用MergeTree作为底表，用于存储全量的明细数据，并以此对外提供实时查询。接着，新建一张物化视图：</p> <pre data-index="22"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> MATERIALIZED <span class="hljs-keyword">VIEW</span> agg_view</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">ENGINE<span class="hljs-operator">=</span> AggregatingMergeTree()</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> city</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> (id,city)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">AS</span> <span class="hljs-keyword">SELECT</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">id,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">city,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">uniqState(code) <span class="hljs-keyword">AS</span> code,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">sumState(<span class="hljs-keyword">value</span>) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">value</span></div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="10"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">FROM</span> agg_table_basic</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="11"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> id,city;</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>插入语句：</p> <pre data-index="23"><code class="language-sql hljs"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">TABLE</span> agg_table_basic <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'A000'</span>, <span class="hljs-string">'wuhan'</span>, <span class="hljs-string">'code1'</span>, <span class="hljs-number">100</span>), (<span class="hljs-string">'A000'</span>, <span class="hljs-string">'wuhan'</span>, <span class="hljs-string">'code2'</span>, <span class="hljs-number">200</span>), (<span class="hljs-string">'AO00'</span>, <span class="hljs-string">'zhuhai'</span>, <span class="hljs-string">'code1'</span>, <span class="hljs-number">200</span>);</code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>查询操作与结果：</p> <pre data-index="24"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">select</span> id ,sumMerge(<span class="hljs-keyword">value</span>),uniqMerge(code) <span class="hljs-keyword">from</span> agg_view <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> id,city;</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> </div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">┌─id───┬─sumMerge(<span class="hljs-keyword">value</span>)─┬─uniqMerge(code)─┐</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ A000 │ <span class="hljs-number">300</span> │ <span class="hljs-number">2</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">│ AO00 │ <span class="hljs-number">200</span> │ <span class="hljs-number">1</span> │</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">└──────┴─────────────────┴─────────────────┘</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>总结如下：</p> <ol><li><span style="color:#f33b45;">用ORBER BY排序键作为聚合数据的条件Key。</span></li><li><span style="color:#f33b45;">使用AggregateFunction字段类型定义聚合函数的类型以及聚合的字段。</span></li><li><span style="color:#f33b45;">只有在合并分区的时候才会触发聚合计算的逻辑。</span></li><li><span style="color:#f33b45;">以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并计算，而不同分区之间的数据则不会被计算。</span></li><li><span style="color:#f33b45;">在进行数据计算时，因为分区内的数据已经基于ORBER BY排序，所以能够找到那些相邻且拥有相同聚合Key的数据。</span></li><li><span style="color:#f33b45;">在聚合数据时，同一分区内，相同聚合Key的多行数据会合并成一行。对于那些非主键、非AggregateFunction类型字段，则会使用第一行数据的取值。</span></li><li><span style="color:#f33b45;">AggregateFunction类型的字段使用二进制存储，在写入数据时，需要调用*State函数；而在查询数据时，则需要调用相应的*Merge函数。其中，*表示定义时使用的聚合函数。</span></li><li><span style="color:#f33b45;">AggregatingMergeTree通常作为物化视图的表引擎，与普通MergeTree搭配使用。</span></li></ol> <h3><a name="t11"></a>2.4.6.VersionedCollapsingMergeTree</h3> <p>在定义 VersionedCollapsingMergeTree 的时候，除了需要指定 sign 标记字段以外，还需要指定一个UInt8 类型的 ver 版本号字段：</p> <pre data-index="25"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> ver_collpase_table(</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> id String,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> code Int32,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> create_time DateTime,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> sign Int8,</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"> ver UInt8</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="7"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line">)ENGINE <span class="hljs-operator">=</span> VersionedCollapsingMergeTree(sign,ver)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="8"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> toYYYYMM(create_time)</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="9"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> id;</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>在定义 ver 字段之后，VersionedCollapsingMergeTree 会自动将ver作为排序条件并增加到 ORDER BY 的末端。以上面的ver_collpase_table 表为例，在每个数据分区内，数据会按照 ORDER BY id , ver DESC排序。所以无论写入时数据的顺序如何，在折叠处理时，都能回到正确的顺序。</p> <p>案例：这里是根据order by字段进行merge。</p> <pre data-index="26"><code class="language-sql hljs"><ol class="hljs-ln" style="width:100%"><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="1"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">TABLE</span> ver_collpase_table <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'A000'</span>,<span class="hljs-number">101</span>,<span class="hljs-string">'2019-02-20 00:00:00'</span>,<span class="hljs-number">-1</span>, <span class="hljs-number">1</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="2"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">TABLE</span> ver_collpase_table <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'A000'</span>,<span class="hljs-number">102</span>,<span class="hljs-string">'2019-02-20 00:00:00'</span>,<span class="hljs-number">1</span>, <span class="hljs-number">1</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="3"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">TABLE</span> ver_collpase_table <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'A000'</span>,<span class="hljs-number">101</span>,<span class="hljs-string">'2019-02-20 00:00:00'</span>,<span class="hljs-number">-1</span>, <span class="hljs-number">1</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="4"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">TABLE</span> ver_collpase_table <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'A000'</span>,<span class="hljs-number">102</span>,<span class="hljs-string">'2019-02-20 00:00:00'</span>,<span class="hljs-number">1</span>, <span class="hljs-number">1</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="5"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">TABLE</span> ver_collpase_table <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'A000'</span>,<span class="hljs-number">103</span>,<span class="hljs-string">'2019-02-20 00:00:00'</span>,<span class="hljs-number">1</span>, <span class="hljs-number">2</span>);</div></div></li><li><div class="hljs-ln-numbers"><div class="hljs-ln-line hljs-ln-n" data-line-number="6"></div></div><div class="hljs-ln-code"><div class="hljs-ln-line"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">TABLE</span> ver_collpase_table <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'A000'</span>,<span class="hljs-number">103</span>,<span class="hljs-string">'2019-02-20 00:00:00'</span>,<span class="hljs-number">-1</span>, <span class="hljs-number">2</span>);</div></div></li></ol></code><div class="hljs-button {2}" data-title="复制" onclick="hljs.copyCode(event)"></div></pre> <p>即在同一版本中的数据才能被删除。例如，只运行第五条数据和第一条数据，则不会被删除。</p> <p></p> <h1><a name="t12"></a>总结：</h1> <ul><li>支持数据去重的ReplacingMergeTree</li><li>支持预先聚合计算的SummingMergeTree与AggregatingMergeTree，</li><li>以及支持数据更新且能够折叠数据的CollapsingMergeTree与VersionedCollapsingMergeTree。</li><li>这些MergeTree系列的表引擎，都用ORDER BY作为条件Key，在分区合并时触发各自的处理逻辑。</li></ul> <p></p> <p></p> </div><div data-report-view="{&quot;mod&quot;:&quot;1585297308_001&quot;,&quot;spm&quot;:&quot;1001.2101.3001.6548&quot;,&quot;dest&quot;:&quot;https://blog.csdn.net/Yuan_CSDF/article/details/117601042&quot;,&quot;extend1&quot;:&quot;pc&quot;,&quot;ab&quot;:&quot;new&quot;}"><div></div></div> </div>