ClickHouse（四）表引擎 · Clickhouse學習筆記

<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&spm=1001.2101.3001.7020" target="_blank" class="hl hl-1" data-report-click="{"spm":"1001.2101.3001.7020","dest":"https://so.csdn.net/so/search?q=ClickHouse&spm=1001.2101.3001.7020","extra":"{\"searchword\":\"ClickHouse\"}"}" 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>        第一：MergeTree 表引擎主要用于海量數據分析，支持數據分區、存儲有序、主鍵索引、稀疏索引、數據TTL等。MergeTree 支持所有ClickHouse SQL 語法，但是有些功能與 MySQL 并不一致，比如在MergeTree 中主鍵并不用于去重。</p> <p>        第二：為了解決 MergeTree 相同主鍵無法去重的問題，ClickHouse 提供了 ReplacingMergeTree 引擎，用來做去重。ReplacingMergeTree 確保數據最終被去重，但是無法保證查詢過程中主鍵不重復。因為相同主鍵的數據可能被 shard 到不同的節點，但是 compaction 只能在一個節點中進行，而且optimize 的時機也不確定。</p> <p>        第三：CollapsingMergeTree 引擎要求在建表語句中指定一個標記列 Sign（插入的時候指定為1，刪除的時候指定為-1），后臺 Compaction 時會將主鍵相同、Sign 相反的行進行折疊，也即刪除。來消除ReplacingMergeTree 的限制。<br>         第四：為了解決 CollapsingMergeTree 亂序寫入情況下無法正常折疊問題，VersionedCollapsingMergeTree 表引擎在建表語句中新增了一列 Version，用于在亂序情況下記錄狀態行與取消行的對應關系。主鍵相同，且 Version 相同、Sign 相反的行，在 Compaction 時會被刪除。<br>         第五：ClickHouse 通過 SummingMergeTree 來支持對主鍵列進行預先聚合。在后臺 Compaction時，會將主鍵相同的多行進行 sum 求和，然后使用一行數據取而代之，從而大幅度降低存儲空間占用，提升聚合計算性能。<br>         第六：AggregatingMergeTree 也是預先聚合引擎的一種，用于提升聚合計算的性能。與SummingMergeTree 的區別在于：SummingMergeTree 對非主鍵列進行 sum 聚合，而AggregatingMergeTree 則可以指定各種聚合函數。</p> <p>        雖然合并樹的變種很多，但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="{"spm":"1001.2101.3001.7365"}"><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="{"spm":"1001.2101.3001.7365"}"><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="{"spm":"1001.2101.3001.7365"}"><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="{"spm":"1001.2101.3001.7365"}"><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="{"spm":"1001.2101.3001.7365"}"><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="{"spm":"1001.2101.3001.7365"}"><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 就是在這種背景下為了數據去重而設計的，它能夠在合并分區時刪除重復的數據。它的出現，確實也在一定程度上解決了重復數據的問題。為什么說是“一定程度”？--><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="{"spm":"1001.2101.3001.7365"}"><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="{"mod":"1585297308_001","spm":"1001.2101.3001.6548","dest":"https://blog.csdn.net/Yuan_CSDF/article/details/117601042","extend1":"pc","ab":"new"}"><div></div></div> </div>