jsp网站建设项目实战,wordpress自动推荐插件,昆明网站制作公司百度推广,安庆怀宁网站建设兼职在ClickHouse主索引的实用介绍 ClickHouse release 24.1, 2024-01-30
1、简介
在本指南中#xff0c;我们将深入研究ClickHouse索引。我们将详细说明和讨论:
ClickHouse中的索引与传统的关系数据库管理系统有何不同ClickHouse是如何构建和使用表的稀疏主索引的什么是在Clic…在ClickHouse主索引的实用介绍 ClickHouse release 24.1, 2024-01-30
1、简介
在本指南中我们将深入研究ClickHouse索引。我们将详细说明和讨论:
ClickHouse中的索引与传统的关系数据库管理系统有何不同ClickHouse是如何构建和使用表的稀疏主索引的什么是在ClickHouse索引的一些最佳做法
您可以在自己的机器上选择执行本指南中给出的所有ClickHouse SQL语句和查询。有关ClickHouse的安装和入门说明请参阅快速入门。 本指南主要关注ClickHouse稀疏主索引。 对于ClickHouse辅助跳数索引请参阅教程。 1.1 Data Set
在本指南中我们将使用一个样本匿名网络流量数据集。
我们将使用样本数据集中887万行(事件)的子集。887万个事件未压缩的数据大小约为700 MB。在ClickHouse中存储时压缩为200 MB。在我们的子集中每行包含三列表示在特定时间(EventTime列)单击URL (URL列)的互联网用户(UserID列)。
有了这三列我们已经可以制定一些典型的网络分析查询如:
“某个特定用户点击次数最多的10个url是什么?”“最常点击某个特定URL的前10名用户是谁?”“用户点击特定URL的最热门时间(例如一周中的几天)是什么时候?”
1.2 测试机器
本文档中给出的所有运行时间数字都是基于在带有Apple M1 Pro芯片和16GB RAM的MacBook Pro上本地运行ClickHouse 22.2.1。(依自己的机器)
1.3 全表扫描
为了了解如何在没有主键的情况下对数据集执行查询我们通过执行以下SQL DDL语句创建了一个表(使用MergeTree 表引擎):
CREATE TABLE hits_NoPrimaryKey
(UserID UInt32,URL String,EventTime DateTime
)
ENGINE MergeTree
PRIMARY KEY tuple();接下来使用以下SQL插入语句将命中数据集的一个子集插入到表中。它使用URL表函数来加载远程托管在clickhouse.com上的完整数据集的子集:
INSERT INTO hits_NoPrimaryKey SELECTintHash32(UserID) AS UserID,URL,EventTime
FROM url(https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz, TSV, WatchID UInt64, JavaEnable UInt8, Title String, GoodEvent Int16, EventTime DateTime, EventDate Date, CounterID UInt32, ClientIP UInt32, ClientIP6 FixedString(16), RegionID UInt32, UserID UInt64, CounterClass Int8, OS UInt8, UserAgent UInt8, URL String, Referer String, URLDomain String, RefererDomain String, Refresh UInt8, IsRobot UInt8, RefererCategories Array(UInt16), URLCategories Array(UInt16), URLRegions Array(UInt32), RefererRegions Array(UInt32), ResolutionWidth UInt16, ResolutionHeight UInt16, ResolutionDepth UInt8, FlashMajor UInt8, FlashMinor UInt8, FlashMinor2 String, NetMajor UInt8, NetMinor UInt8, UserAgentMajor UInt16, UserAgentMinor FixedString(2), CookieEnable UInt8, JavascriptEnable UInt8, IsMobile UInt8, MobilePhone UInt8, MobilePhoneModel String, Params String, IPNetworkID UInt32, TraficSourceID Int8, SearchEngineID UInt16, SearchPhrase String, AdvEngineID UInt8, IsArtifical UInt8, WindowClientWidth UInt16, WindowClientHeight UInt16, ClientTimeZone Int16, ClientEventTime DateTime, SilverlightVersion1 UInt8, SilverlightVersion2 UInt8, SilverlightVersion3 UInt32, SilverlightVersion4 UInt16, PageCharset String, CodeVersion UInt32, IsLink UInt8, IsDownload UInt8, IsNotBounce UInt8, FUniqID UInt64, HID UInt32, IsOldCounter UInt8, IsEvent UInt8, IsParameter UInt8, DontCountHits UInt8, WithHash UInt8, HitColor FixedString(1), UTCEventTime DateTime, Age UInt8, Sex UInt8, Income UInt8, Interests UInt16, Robotness UInt8, GeneralInterests Array(UInt16), RemoteIP UInt32, RemoteIP6 FixedString(16), WindowName Int32, OpenerName Int32, HistoryLength Int16, BrowserLanguage FixedString(2), BrowserCountry FixedString(2), SocialNetwork String, SocialAction String, HTTPError UInt16, SendTiming Int32, DNSTiming Int32, ConnectTiming Int32, ResponseStartTiming Int32, ResponseEndTiming Int32, FetchTiming Int32, RedirectTiming Int32, DOMInteractiveTiming Int32, DOMContentLoadedTiming Int32, DOMCompleteTiming Int32, LoadEventStartTiming Int32, LoadEventEndTiming Int32, NSToDOMContentLoadedTiming Int32, FirstPaintTiming Int32, RedirectCount Int8, SocialSourceNetworkID UInt8, SocialSourcePage String, ParamPrice Int64, ParamOrderID String, ParamCurrency FixedString(3), ParamCurrencyID UInt16, GoalsReached Array(UInt32), OpenstatServiceName String, OpenstatCampaignID String, OpenstatAdID String, OpenstatSourceID String, UTMSource String, UTMMedium String, UTMCampaign String, UTMContent String, UTMTerm String, FromTag String, HasGCLID UInt8, RefererHash UInt64, URLHash UInt64, CLID UInt32, YCLID UInt64, ShareService String, ShareURL String, ShareTitle String, ParsedParams Nested(Key1 String, Key2 String, Key3 String, Key4 String, Key5 String, ValueDouble Float64), IslandID FixedString(16), RequestNum UInt32, RequestTry UInt8)
WHERE URL ! ;ClickHouse客户端的结果输出显示上面的语句向表中插入了887万行。
最后为了简化本指南后面的讨论并使图表和结果可重复我们使用FINAL关键字对表进行优化:
OPTIMIZE TABLE hits_NoPrimaryKey FINAL;通常不需要也不建议在将数据加载到表中后立即对其进行优化。为什么这对于这个例子是必要的将变得显而易见。 现在我们执行第一个web分析查询。以下是计算UserID为749927693的互联网用户点击次数最多的10个url:
SELECT URL, count(URL) as Count
FROM hits_NoPrimaryKey
WHERE UserID 749927693
GROUP BY URL
ORDER BY Count DESC
LIMIT 10;ClickHouse客户端的结果输出表明ClickHouse执行了全表扫描!我们的887万行表中的每一行都被流式传输到ClickHouse。这是不可伸缩的。
为了使这种方法更有效和更快我们需要使用具有适当主键的表。这将允许ClickHouse自动(基于主键的列)创建一个稀疏的主索引然后可以用来显著加快我们示例查询的执行速度。
相关内容
Blog: Super charging your ClickHouse queries
2、ClickHouse索引设计
2.1 针对大规模数据的索引设计
在传统的关系数据库管理系统中主索引每行包含一个条目。这将导致我们的数据集的主索引包含887万个条目。这样的索引允许快速定位特定行的位置从而提高查找查询和点更新的效率。在B()-Tree数据结构中搜索条目的平均时间复杂度为O(log n);更准确地说log_b n log_2 n / log_2 b其中b是b()-树的分支因子n是索引行数。因为b通常在几百到几千之间所以B()-Tree是非常浅的结构并且很少需要磁盘查找来定位记录。对于887万行和1000的分支因子平均需要2.3个磁盘查找。这种功能是有代价的:额外的磁盘和内存开销向表中添加新行和向索引中添加条目时的更高插入成本有时还要重新平衡B-Tree。
考虑到与B-Tree索引相关的挑战ClickHouse中的表引擎使用了一种不同的方法。ClickHouse MergeTree引擎系列经过设计和优化可以处理大量数据。这些表被设计为每秒接收数百万行插入并存储非常大(100 pb)的数据量。数据被快速地一部分一部分地写入到表中并在后台应用规则来合并这些部分。在ClickHouse中每个部分(part )都有自己的主索引。当部分被合并时被合并部分的主索引也被合并。在ClickHouse设计的非常大的规模下磁盘和内存效率是至关重要的。因此不是索引每一行一个part的主索引对于每一组行(称为“粒度,granule”)有一个索引条目(index entry称为“标记, mark”)——这种技术称为稀疏索引sparse index。
稀疏索引是可能的因为ClickHouse将part的行存储在按主键列排序的磁盘上。与直接定位单行(如基于B-Tree的索引)不同稀疏主索引允许它快速(通过对索引条目的二进制搜索)识别可能匹配查询的行组。定位的潜在匹配行的组(粒度)然后并行流到ClickHouse引擎中以便找到匹配。这种索引设计允许主索引很小(它可以而且必须完全适合主内存)同时仍然显著加快查询执行时间:特别是对于数据分析用例中典型的范围查询。
下面详细说明ClickHouse是如何构建和使用其稀疏主索引的。在本文的后面部分我们将讨论选择、删除和排序用于构建索引的表列(主键列)的一些最佳实践。
2.2 具有主键的表
创建一个复合主键表主键列为UserID和URL。
CREATE TABLE hits_UserID_URL
(UserID UInt32,URL String,EventTime DateTime
)
ENGINE MergeTree
PRIMARY KEY (UserID, URL)
ORDER BY (UserID, URL, EventTime)
SETTINGS index_granularity 8192, index_granularity_bytes 0;为了简化本指南后面的讨论并使图和结果可重现DDL说明如下 通过’ ORDER BY 子句为表指定复合排序键通过设置显式控制主索引将拥有多少索引项: index_granularity:显式设置为其默认值8192。这意味着对于每组8192行主索引将有一个索引条目例如如果表包含16384行则索引将有两个索引条目。 index_granularity_bytes:设置为0以禁用自适应索引粒度。自适应索引粒度意味着ClickHouse自动为一组n行创建一个索引条目如果其中任何一个为真: 如果n小于8192并且n行的合并行数据的大小大于或等于10mb (index_granularity_bytes的默认值)或如果n行的合并行数据大小小于10 MB但n为8192。 上面DDL语句中的主键导致基于两个指定的键列创建主索引。
接下来插入数据:
INSERT INTO hits_UserID_URL SELECTintHash32(UserID) AS UserID,URL,EventTime
FROM url(https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz, TSV, WatchID UInt64, JavaEnable UInt8, Title String, GoodEvent Int16, EventTime DateTime, EventDate Date, CounterID UInt32, ClientIP UInt32, ClientIP6 FixedString(16), RegionID UInt32, UserID UInt64, CounterClass Int8, OS UInt8, UserAgent UInt8, URL String, Referer String, URLDomain String, RefererDomain String, Refresh UInt8, IsRobot UInt8, RefererCategories Array(UInt16), URLCategories Array(UInt16), URLRegions Array(UInt32), RefererRegions Array(UInt32), ResolutionWidth UInt16, ResolutionHeight UInt16, ResolutionDepth UInt8, FlashMajor UInt8, FlashMinor UInt8, FlashMinor2 String, NetMajor UInt8, NetMinor UInt8, UserAgentMajor UInt16, UserAgentMinor FixedString(2), CookieEnable UInt8, JavascriptEnable UInt8, IsMobile UInt8, MobilePhone UInt8, MobilePhoneModel String, Params String, IPNetworkID UInt32, TraficSourceID Int8, SearchEngineID UInt16, SearchPhrase String, AdvEngineID UInt8, IsArtifical UInt8, WindowClientWidth UInt16, WindowClientHeight UInt16, ClientTimeZone Int16, ClientEventTime DateTime, SilverlightVersion1 UInt8, SilverlightVersion2 UInt8, SilverlightVersion3 UInt32, SilverlightVersion4 UInt16, PageCharset String, CodeVersion UInt32, IsLink UInt8, IsDownload UInt8, IsNotBounce UInt8, FUniqID UInt64, HID UInt32, IsOldCounter UInt8, IsEvent UInt8, IsParameter UInt8, DontCountHits UInt8, WithHash UInt8, HitColor FixedString(1), UTCEventTime DateTime, Age UInt8, Sex UInt8, Income UInt8, Interests UInt16, Robotness UInt8, GeneralInterests Array(UInt16), RemoteIP UInt32, RemoteIP6 FixedString(16), WindowName Int32, OpenerName Int32, HistoryLength Int16, BrowserLanguage FixedString(2), BrowserCountry FixedString(2), SocialNetwork String, SocialAction String, HTTPError UInt16, SendTiming Int32, DNSTiming Int32, ConnectTiming Int32, ResponseStartTiming Int32, ResponseEndTiming Int32, FetchTiming Int32, RedirectTiming Int32, DOMInteractiveTiming Int32, DOMContentLoadedTiming Int32, DOMCompleteTiming Int32, LoadEventStartTiming Int32, LoadEventEndTiming Int32, NSToDOMContentLoadedTiming Int32, FirstPaintTiming Int32, RedirectCount Int8, SocialSourceNetworkID UInt8, SocialSourcePage String, ParamPrice Int64, ParamOrderID String, ParamCurrency FixedString(3), ParamCurrencyID UInt16, GoalsReached Array(UInt32), OpenstatServiceName String, OpenstatCampaignID String, OpenstatAdID String, OpenstatSourceID String, UTMSource String, UTMMedium String, UTMCampaign String, UTMContent String, UTMTerm String, FromTag String, HasGCLID UInt8, RefererHash UInt64, URLHash UInt64, CLID UInt32, YCLID UInt64, ShareService String, ShareURL String, ShareTitle String, ParsedParams Nested(Key1 String, Key2 String, Key3 String, Key4 String, Key5 String, ValueDouble Float64), IslandID FixedString(16), RequestNum UInt32, RequestTry UInt8)
WHERE URL ! ;并优化表:
OPTIMIZE TABLE hits_UserID_URL FINAL;我们可以使用下面的查询来获取表的元数据:
SELECTpart_type,path,formatReadableQuantity(rows) AS rows,formatReadableSize(data_uncompressed_bytes) AS data_uncompressed_bytes,formatReadableSize(data_compressed_bytes) AS data_compressed_bytes,formatReadableSize(primary_key_bytes_in_memory) AS primary_key_bytes_in_memory,marks,formatReadableSize(bytes_on_disk) AS bytes_on_disk
FROM system.parts
WHERE (table hits_UserID_URL) AND (active 1)
FORMAT Vertical;ClickHouse客户端的输出显示:
表的数据以宽格式存储在磁盘上的特定目录中这意味着该目录中每个表列将有一个数据文件(和一个标记文件)。该表有887万行。所有行加起来的未压缩数据大小为733.28 MB。磁盘上所有行的压缩大小为206.94 MB。该表有一个包含1083个条目(称为“标记”)的主索引索引的大小为96.93 KB。总的来说表的数据和标记文件以及主索引文件总共占用了207.07 MB的磁盘空间。
/var/lib/clickhouse/store/fc9/fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3/all_1_9_2/ 数据按主键列的顺序存储在磁盘上。 我们在上面创建的表有:
a compound primary key (UserID, URL) anda compound sorting key (UserID, URL, EventTime) 如果我们只指定排序键那么主键将被隐式定义为等于排序键。为了提高内存效率我们显式指定了一个主键该主键只包含查询要过滤的列。基于主键的主索引完全加载到主内存中。为了在指南的图表中保持一致性为了最大化压缩比我们定义了一个单独的排序键它包括我们表的所有列(如果在一列中相似的数据彼此靠近例如通过排序那么该数据将被更好地压缩)。如果指定了排序键和主键则主键需要是排序键的前缀。 插入的行按照字典顺序(升序)按主键列(以及来自排序键的额外EventTime列)存储在磁盘上。 ClickHouse允许插入具有相同主键列值的多行。在这种情况下(参见下图中的第1行和第2行)最终顺序由指定的排序键决定因此由EventTime列的值决定。 ClickHouse是一个面向列的数据库管理系统。如下图所示:
对于磁盘上的表示每个表列都有一个数据文件(*.bin)其中该列的所有值都以压缩格式存储887万行按字典升序按主键列(和附加排序键列)存储在磁盘上即在本例中 first by UserID,then by URL,and lastly by EventTime: UserID.bin、URL.bin和EventTime.bin是存储“UserID”、“URL”和“EventTime”列值的磁盘数据文件。 由于主键定义磁盘上行的字典顺序因此一个表只能有一个主键。我们对从0开始的行进行编号以便与ClickHouse内部的行编号方案保持一致该方案也用于记录消息。 2.3 数据被组织成粒度以进行并行数据处理
出于数据处理的目的表的列值在逻辑上被划分为粒度。粒度是流入ClickHouse进行数据处理的最小的不可分割数据集。这意味着ClickHouse不是读取单个行而是始终读取(以流方式并行)一整组(粒度)行。 列值不是物理地存储在粒度中:粒度只是用于查询处理的列值的逻辑组织。 下图显示了表的887万行(列值)是如何被组织成1083个颗粒的这是表的DDL语句包含设置index_granularity(设置为默认值8192)的结果。 第一个(基于磁盘上的物理顺序)8192行(它们的列值)在逻辑上属于粒度0然后接下来的8192行(它们的列值)属于粒度1依此类推。 最后一粒(1082粒)“包含”少于8192行。我们在本指南开头的“DDL语句详细信息”中提到我们禁用了自适应索引粒度(为了简化本指南中的讨论并使图表和结果可重现)。 因此我们示例表中的所有颗粒(最后一个除外)都具有相同的大小。对于具有自适应索引粒度的表(默认情况下索引粒度是自适应的)一些粒度的大小可以小于8192行具体取决于行数据的大小。我们用橙色标记了主键列(UserID、URL)中的一些列值。这些橙色标记的列值是每个粒度的每个第一行的主键列值。正如我们将在下面看到的这些橙色标记的列值将是表的主索引中的条目。我们从0开始对颗粒进行编号以便与ClickHouse内部编号方案保持一致该方案也用于记录消息。 2.4 主索引每个颗粒有一个条目
主索引是基于上图所示的粒度创建的。该索引是一个未压缩的平面数组文件(primary.idx)包含从0开始的所谓数字索引标记。
下图显示了索引存储每个粒度的每个第一行的主键列值(上图中用橙色标记的值)。或者换句话说:主索引存储表的每8192行中的主键列值(基于主键列定义的物理行顺序)。例如 第一个索引条目(下图中的’ mark 0 )存储上图中粒度0的第一行的键列值第二个索引条目(下图中的’ mark 1 )存储上图中粒度1第一行的键列值以此类推。
总的来说我们的表有887万行和1083个颗粒索引有1083个条目: 对于具有自适应索引粒度的表主索引中还存储一个“最终”附加标记该标记记录表最后一行的主键列的值但是由于我们禁用了自适应索引粒度(为了简化本指南中的讨论以及使图表和结果可重复)我们示例表的索引不包括这个最终标记。主索引文件完全加载到主内存中。如果文件大于可用的空闲内存空间那么ClickHouse将引发一个错误。 检查主索引的内容 在自我管理的ClickHouse集群上我们可以使用文件表函数来检查示例表的主索引的内容。 为此我们首先需要从正在运行的集群中将主索引文件复制到节点的user_files_path中: 步骤1:获取包含主索引文件的part-path SELECT path FROM system.parts WHERE table hits_UserID_URL AND active 1 步骤2:获取user_files_path Linux上默认的user_files_path是’ /var/lib/clickhouse/user_files/ ’ 在Linux上你可以检查它是否被更改:$ grep user_files_path /etc/clickhouse-server/config.xml 步骤3:将主索引文件复制到user_files_path中 cp /var/lib/clickhouse/store/fc9/fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3/all_1_9_2/primary.cidx /var/lib/clickhouse/user_files/ 现在我们可以通过SQL来检查主索引的内容: 获取条目数量 SELECT count( ) FROM file(primary-hits_UserID_URL.idx, RowBinary, UserID UInt32, URL String); returns 1083获得前两个索引标记 SELECT UserID, URL FROM file(primary-hits_UserID_URL.idx, RowBinary, UserID UInt32, URL String) LIMIT 0, 2; returns 240923, http://showtopics.html%3... 4073710, http://mk.rupos3_0获取最后一个索引标记 SELECT UserID, URL FROM file(primary-hits_UserID_URL.idx, RowBinary, UserID UInt32, URL String) LIMIT 1082, 1; returns 4292714039 │ http://sosyal-mansetleri... 这与我们的示例表的主索引内容图完全匹配: 主键项( primary key entries)称为索引标记(index marks)因为每个索引项标记特定数据范围的开始。具体到示例表: UserID索引标记: 主索引中存储的UserID值按升序排序。 因此上图中的’ mark 1 表示保证粒度1中所有表行的UserID值大于或等于4,073,710。 我们将在后面看到当查询对主键的第一列进行过滤时这个全局顺序使ClickHouse能够在第一个键列的索引标记上使用二分搜索算法。 URL索引标记: 主键列UserID和URL的基数非常相似这意味着在第一列之后的所有键列的索引标记通常只指示一个数据范围只要前一个键列值至少在当前粒度内的所有表行保持相同。 例如由于上图中标记0和标记1的UserID值不同ClickHouse不能假设粒度0中所有表行的所有URL值都大于或等于http://showtopics.html%3...。然而如果标记0和标记1的UserID值在上图中是相同的(意味着UserID值对于粒度0中的所有表行保持相同)ClickHouse可以假设粒度0中所有表行的所有URL值都大于或等于’http://showtopics.html%3…。 稍后我们将更详细地讨论这对查询执行性能的影响。
2.5 主索引用于选择粒度
现在我们可以在主索引的支持下执行查询。
下面计算UserID 749927693的点击次数最多的前10个url。
SELECT URL, count(URL) AS Count
FROM hits_UserID_URL
WHERE UserID 749927693
GROUP BY URL
ORDER BY Count DESC
LIMIT 10;ClickHouse客户端的输出现在显示没有进行全表扫描只有8190行流到ClickHouse。
如果启用了跟踪日志记录那么ClickHouse服务器日志文件显示ClickHouse在1083个UserID索引标记上运行二分搜索以便识别可能包含UserID列值为749927693的行的粒度。这需要19步平均时间复杂度为O(log2n): Debug executeQuery: (from [::ffff:127.0.0.1]:39660) SELECT URL, count(URL) AS Count FROM hits_UserID_URL WHERE UserID 749927693 GROUP BY URL ORDER BY Count DESC LIMIT 10; (stage: Complete)Trace InterpreterSelectQuery: The min valid primary key position for moving to the tail of PREWHERE is 0Debug InterpreterSelectQuery: MergeTreeWhereOptimizer: condition UserID 749927693 moved to PREWHERETrace ContextAccess (default): Access granted: SELECT(UserID, URL) ON default.hits_UserID_URLTrace InterpreterSelectQuery: FetchColumns - CompleteDebug default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Key condition: (column 0 in [749927693, 749927693])Trace default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Running binary search on index range for part all_1_9_2 (1083 marks)Trace default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Found (LEFT) boundary mark: 176Trace default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Found (RIGHT) boundary mark: 177Trace default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Found continuous range in 19 stepsDebug default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Selected 1/1 parts by partition key, 1 parts by primary key, 1/1083 marks by primary key, 1 marks to read from 1 rangesTrace default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Spreading mark ranges among streams (default reading)Trace default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Reading 1 ranges in order from part all_1_9_2, approx. 8192 rows starting from 1441792Trace MergeTreeSelectProcessor: PREWHERE condition was split into 1 steps: equals(UserID, 749927693)Trace AggregatingTransform: AggregatingTrace Aggregator: An entry for key11128043768465050887 found in cache: sum_of_sizes238, median_size238Trace Aggregator: Aggregation method: key_stringTrace AggregatingTransform: Aggregated. 879 to 238 rows (from 63.93 KiB) in 0.416282801 sec. (2111.545 rows/sec., 153.57 KiB/sec.)Trace Aggregator: Merging aggregated data我们可以在上面的跟踪日志中看到1083个现有标记中只有一个满足查询。 标记176被识别(“找到的左边界标记”包括在内“找到的右边界标记”不包括在内)因此来自粒度176的所有8192行(从第1.441.792行开始-我们将在本指南的后面看到)然后被流到ClickHouse以便找到UserID列值为749927693的实际行。 我们也可以通过在示例查询中使用EXPLAIN子句来下再现:
EXPLAIN indexes 1
SELECT URL, count(URL) AS Count
FROM hits_UserID_URL
WHERE UserID 749927693
GROUP BY URL
ORDER BY Count DESC
LIMIT 10;客户机输出显示在1083个颗粒中选择了一个粒度因为它可能包含UserID列值为749927693的行。 当查询对作为复合键的一部分并且是第一个键列的列进行过滤时ClickHouse将在键列的索引标记上运行二分搜索算法。 如上所述ClickHouse使用其稀疏主索引来快速(通过二分搜索)选择可能包含匹配查询的行的粒度。
这是ClickHouse查询执行的第一阶段(粒度选择) first stage (granule selection)。
在第二阶段(数据读取) second stage (data reading)ClickHouse定位选中的粒度以便将它们的所有行流式传输到ClickHouse引擎中以便找到实际匹配查询的行。
我们将在下一节中更详细地讨论第二阶段。
2.6 标记文件用于定位粒度
下图展示了我们表的主索引文件的一部分。 如上所述通过对索引的1083个UserIDmark进行二分搜索确定了mark 176。因此它对应的粒度176可能包含UserID列值为749.927.693的行。 上图显示mark 176是第一个索引条目关联的粒度176的最小UserID值小于749.927.693并且下一个mark (mark 177)的粒度177的最小UserID值大于此值。因此只有mark 176对应的粒度176可能包含UserID列值为749.927.693的行。 为了确认(或不确认)粒度176中的某些行包含UserID列值为749.927.693属于该粒度的所有8192行都需要流式传输到ClickHouse。 要做到这一点ClickHouse需要知道粒度176的物理位置。
下图显示了三个mark文件UserID.mrk, URL.mrk, 和EventTime.mrk 。用于存储表的UserID、URL和EventTime列的粒度的物理位置。 我们已经讨论了主索引是一个扁平的未压缩数组文件(primary.idx)其中包含从0开始编号的索引 mark。
类似地标记文件也是一个平面的未压缩数组文件(*.mrk)其中包含从0开始编号的标记。
一旦ClickHouse确定并选择了可能包含查询匹配行的颗粒的索引mark就可以在标记文件中执行位置数组查找以获得粒度的物理位置。
每个特定列的标记文件条目以偏移量的形式存储两个位置:
第一个偏移量(上图中的’block_offset’)定位压缩列数据文件中的块该文件包含所选粒度的压缩版本。这个压缩块可能包含一些压缩颗粒。所定位的压缩文件块在读取时解压到主内存中。标记文件中的第二个偏移量(上图中的’granule_offset’)提供了未压缩块数据中颗粒的位置。
所有属于定位的未压缩颗粒的8192行然后流式传输到ClickHouse进行进一步处理。 对于宽格式且没有自适应索引粒度的表ClickHouse使用.mrk标记文件如上图所示其中每个条目包含两个8字节长的地址。这些条目是具有相同大小的颗粒的物理位置。 默认情况下索引粒度是自适应的但是对于我们的示例表我们禁用了自适应索引粒度(为了简化本指南中的讨论并使图表和结果可重现)。我们的表使用宽格式因为数据的大小大于min_bytes_for_wide_part(对于自管理集群默认为10 MB)。对于具有宽格式和自适应索引粒度的表ClickHouse使用.mrk2标记文件其中包含与.mrk标记文件相似的条目但每个条目都有额外的第三个值:当前条目所关联的粒度的行数。对于紧凑格式的表ClickHouse使用.mrk3标记文件。 为什么要标记文件? 为什么初级指标不直接包含与指标标记相对应的粒度的物理位置? 因为ClickHouse的设计对象是非常大的规模所以磁盘和内存效率非常重要。 主索引文件需要适合于主内存。 对于我们的示例查询ClickHouse使用主索引并选择一个可能包含与查询匹配的行的单个粒度。只有对于这一个粒度ClickHouse才需要物理位置以便对相应的行进行进一步处理。 此外这个偏移量信息只需要用于UserID和URL列。 对于查询中不使用的列例如EventTime不需要偏移信息。 对于我们的示例查询ClickHouse只需要UserID数据文件(UserID.bin)中粒子176的两个物理位置偏移量和URL数据文件(URL.bin)中粒子176的两个物理位置偏移量。 标记文件提供的间接性避免了直接在主索引中存储所有三个列的所有1083个粒度的物理位置的条目:从而避免了在主内存中有不必要的(可能未使用的)数据。 下面的图表和下面的文本说明了我们的示例查询ClickHouse如何在UserID.bin数据文件中定位粒度176 我们在本指南的前面讨论过ClickHouse选择了主索引标记176因此颗粒176可能包含我们查询的匹配行。
ClickHouse现在使用从索引中选择的标记号(176)在UserID中进行位置数组查找。UserID.mrk 标记文件以便获得定位粒度176的两个偏移量。
如图所示第一个偏移量定位于UserID.bin数据文件中的压缩文件块该数据文件又包含粒度176的压缩版本。
一旦所定位的文件块被解压缩到主内存中标记文件的第二个偏移量可用于在解压数据中定位粒度176。
ClickHouse需要从UserID.bin数据文件和URL.bin数据文件中定位(并从流传输所有值)粒度176以便执行我们的示例查询(UserID为749.927.693的互联网用户点击最多的10个url)。
上图显示了ClickHouse是如何定位UserID.bin数据文件的颗粒的。
与此同时ClickHouse对176号颗粒的URL.bin数据文件也做了同样的事情。这两个各自的粒度被对齐并流到ClickHouse引擎中进行进一步的处理即对UserID为749.927.693的所有行的每个组的URL值进行聚合和计数最后按降序输出10个最大的URL组。
3、使用多个主索引
3.1 辅助键列可能(不会)是低效的
当查询对作为复合键的一部分并且是第一个键列的列进行过滤时ClickHouse将在键列的索引标记上运行二分搜索算法。
但是如果查询过滤的列是复合键的一部分但不是第一个键列会发生什么情况? 我们将讨论这样一种场景:查询显式地不过滤第一个键列而是过滤第二个键列。 当查询在第一个键列和第一个键列之后的任何键列上进行过滤时ClickHouse将在第一个键列的索引标记上运行二进制搜索。 我们使用一个查询来计算最经常点击URL“http://public_search”的前10个用户:
SELECT UserID, count(UserID) AS Count
FROM hits_UserID_URL
WHERE URL http://public_search
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;客户端输出表明ClickHouse几乎执行了一次全表扫描尽管URL列是复合主键的一部分!ClickHouse从表的887万行中读取881万行。
如果启用了trace_logging那么ClickHouse服务器日志文件显示ClickHouse使用了1083个URL索引标记的通用排除搜索以识别那些可能包含URL列值为“http://public_search”的行的粒度:
{} Trace TCP-Session: 194d1e1a-c07d-4535-83d1-927dd95d5881 Creating query context from session context, user_id: 94309d50-4f52-5250-31bd-74fecac179db, parent context user: default{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Debug executeQuery: (from [::ffff:127.0.0.1]:52870) SELECT UserID, count(UserID) AS Count FROM hits_UserID_URL WHERE URL http://public_search GROUP BY UserID ORDER BY Count DESC LIMIT 10; (stage: Complete){aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace InterpreterSelectQuery: The min valid primary key position for moving to the tail of PREWHERE is -1{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Debug InterpreterSelectQuery: MergeTreeWhereOptimizer: condition URL http://public_search moved to PREWHERE{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace ContextAccess (default): Access granted: SELECT(UserID, URL) ON default.hits_UserID_URL{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace InterpreterSelectQuery: FetchColumns - Complete{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Debug default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Key condition: (column 1 in [http://public_search, http://public_search]){aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Used generic exclusion search over index for part all_1_9_2 with 1537 steps{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Debug default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Selected 1/1 parts by partition key, 1 parts by primary key, 1076/1083 marks by primary key, 1076 marks to read from 5 ranges{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Spreading mark ranges among streams (default reading){aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Debug MergeTreeReadPool: min_marks_for_concurrent_read23{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Debug default.hits_UserID_URL (fc9edaa0-5d1e-4d05-a62e-e73cc885f5b3) (SelectExecutor): Reading approx. 8814592 rows with 2 streams{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace MergeTreeSelectProcessor: PREWHERE condition was split into 1 steps: equals(URL, http://public_search){aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace MergeTreeSelectProcessor: PREWHERE condition was split into 1 steps: equals(URL, http://public_search){} Trace SystemLog (system.metric_log): Flushing system log, 8 entries to flush up to offset 26191{} Trace AsynchronousMetrics: MemoryTracking: was 509.94 MiB, peak 3.72 GiB, free memory in arenas 11.41 MiB, will set to 501.76 MiB (RSS), difference: -8.18 MiB{} Trace system.metric_log (6b8b71fd-9c5d-447a-8ab4-e6e231d7e170): Trying to reserve 1.00 MiB using storage policy from min volume index 0{} Trace DiskLocal: Reserved 1.00 MiB on local disk default, having unreserved 16.40 GiB.{} Trace MergedBlockOutputStream: filled checksums 202402_3453_3453_0 (state Temporary){} Trace system.metric_log (6b8b71fd-9c5d-447a-8ab4-e6e231d7e170): Renaming temporary part tmp_insert_202402_3453_3453_0 to 202402_18030_18030_0 with tid (1, 1, 00000000-0000-0000-0000-000000000000).{} Trace SystemLog (system.metric_log): Flushed system log up to offset 26191{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace AggregatingTransform: Aggregating{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace Aggregator: An entry for key1603639848120682596 found in cache: sum_of_sizes16749, median_size10233{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace Aggregator: Aggregation method: key32{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace AggregatingTransform: Aggregating{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace Aggregator: An entry for key1603639848120682596 found in cache: sum_of_sizes16749, median_size10233{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace Aggregator: Aggregation method: key32{} Trace SystemLog (system.query_log): Flushing system log, 1 entries to flush up to offset 45{} Trace system.query_log (e654c581-192f-4fe7-a0f1-01ffcca60e4d): Trying to reserve 1.00 MiB using storage policy from min volume index 0{} Trace DiskLocal: Reserved 1.00 MiB on local disk default, having unreserved 16.40 GiB.{} Trace MergedBlockOutputStream: filled checksums 202402_24_24_0 (state Temporary){} Trace system.query_log (e654c581-192f-4fe7-a0f1-01ffcca60e4d): Renaming temporary part tmp_insert_202402_24_24_0 to 202402_105_105_0 with tid (1, 1, 00000000-0000-0000-0000-000000000000).{} Trace SystemLog (system.query_log): Flushed system log up to offset 45{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace AggregatingTransform: Aggregated. 120177 to 6501 rows (from 469.44 KiB) in 3.687684001 sec. (32588.747 rows/sec., 127.30 KiB/sec.){aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace AggregatingTransform: Aggregated. 190942 to 10249 rows (from 745.87 KiB) in 3.693239639 sec. (51700.409 rows/sec., 201.95 KiB/sec.){aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace Aggregator: Merging aggregated data{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Trace Aggregator: Statistics updated for key1603639848120682596: new sum_of_sizes16750, median_size10249{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Debug MergingSortedTransform: Merge sorted 1 blocks, 10 rows in 3.692513498 sec., 2.708182381842711 rows/sec., 32.50 B/sec{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Debug executeQuery: Read 8810337 rows, 762.65 MiB in 3.736504 sec., 2357909.157865213 rows/sec., 204.11 MiB/sec.{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Debug MemoryTracker: Peak memory usage (for query): 29.38 MiB.{aaa13be3-c050-4aec-b10f-ce28b1fccc7e} Debug TCPHandler: Processed in 3.736510623 sec.在上面的示例跟踪日志中我们可以看到1083个粒度中有1076个(通过标记)被选择为可能包含具有匹配URL值的行。
这导致881万行被流式传输到ClickHouse引擎中(通过使用10个流并行)以便识别实际包含URL值“http://public_search”的行。
然而正如我们稍后将看到的在选定的1076个粒度中只有39个粒度实际上包含匹配的行。
虽然基于复合主键(UserID, URL)的主索引对于加快对具有特定UserID值的行进行查询过滤非常有用但是索引对于加快对具有特定URL值的行进行查询过滤并没有提供显著的帮助。
这样做的原因是URL列不是第一个键列因此ClickHouse在URL列的索引标记上使用通用排除搜索算法(而不是二分搜索)该算法的有效性取决于URL列与其前身键列UserID之间的基数差。
为了说明这一点我们给出了一些关于通用排除搜索如何工作的细节。
3.2 通用排除搜索算法
下面的示例说明了当粒度是通过二级列选择时ClickHouse通用排除搜索算法是如何工作的其中前一个键列具有低(er)或高(er)基数。
作为这两种情况的一个例子我们将假设:
正在搜索URL值 W3的行的查询。我们的hits表的抽象版本简化了UserID和URL的值。索引使用相同的复合主键(UserID, URL)。这意味着行首先按UserID值排序。然后按URL对具有相同UserID值的行排序。粒度大小为两个即每个粒度包含两行。
在下面的图表中我们用橙色标记了每个粒度的第一个表行的关键列值。
前一个键列具有较低的基数
假设UserID的基数较低。在这种情况下相同的UserID值很可能分布在多个表行和粒度上因此也就有了索引标记。对于具有相同UserID的索引标记索引标记的URL值按升序排序(因为表行首先按UserID排序然后按URL排序)。这允许如下所述的高效过滤: 上图中抽象样本数据的粒度选择过程有三种不同的场景:
由于标记0和1具有相同的UserID值因此URL值小于W3且直接后续索引标记的URL值也小于W3的索引标记0可以排除。注意这个排除前提确保了粒度0完全由U1 UserID 值组成这样ClickHouse就可以假设颗粒0中的最大URL值小于W3从而排除该粒度。选择URL值小于(或等于)W3且直接后续索引标记的URL值大于(或等于)W3的索引标记1因为这意味着粒度1可能包含URL为W3的行。可以排除URL值大于W3的索引标记2和3因为主索引的索引标记存储每个粒度表的第一行的键列值并且表的行在磁盘上按键列值排序因此颗粒2和3不可能包含URL值W3。
前一个键列具有较高的基数
当UserID具有高基数时相同的UserID值不太可能分布在多个表行和颗粒中。这意味着索引标记的URL值不是单调递增的: 正如我们在上图中所看到的所有URL值小于W3的标记都被选中用于将其相关粒度的行流式传输到ClickHouse引擎中。
这是因为虽然图表中的所有索引标记都属于上面描述的场景1但它们不满足前面提到的排除前提条件即直接后续索引标记具有与当前标记相同的UserID值因此不能被排除。
例如考虑索引标记0的URL值小于W3并且其直接后续索引标记的URL值也小于W3。不能排除这种情况因为直接继承索引标记1的UserID值与当前标记0的UserID值不同。
标记1、2和3也是如此。 当查询在作为复合键的一部分的列上进行过滤但不是第一个键列时ClickHouse使用的通用排除搜索算法而不是二分搜索算法在前一个键列具有低(er)基数时是最有效的。 在我们的示例数据集中两个键列(UserID、URL)具有相似的高基数并且如前所述当URL列的前一个键列具有高(er)或相似的基数时通用排除搜索算法不是很有效。
3.3 数据跳转索引注意事项
由于UserID和URL具有相似的高基数性因此在使用复合主键(UserID, URL)的表的URL列上创建辅助数据跳过索引对URL的查询过滤也没有太大好处。
例如下面两个语句创建并填充表URL列上的最小值数据跳跃索引:
ALTER TABLE hits_UserID_URL ADD INDEX url_skipping_index URL TYPE minmax GRANULARITY 4;
ALTER TABLE hits_UserID_URL MATERIALIZE INDEX url_skipping_index;ClickHouse现在创建了一个额外的索引来存储——每组4个连续的粒度(注意上面ALTER TABLE语句中的GRANULARITY 4子句)——最小和最大URL值: 第一个索引条目(上图中的’ mark 0 )存储属于表的前4个粒度的行的最小和最大URL值。
第二个索引条目(’ mark 1 )存储属于表的下4个颗粒的行的最小和最大URL值以此类推。
(ClickHouse还为数据跳过索引创建了一个特殊的标记文件用于定位与索引标记相关的粒度组。)
由于UserID和URL具有相似的高基数性所以当我们对URL执行查询过滤时这个辅助数据跳过索引无法排除被选择的粒度。
查询正在查找的特定URL值(即。‘http://public_search’)很可能是最小值和最大值之间存储的每组颗粒的索引导致ClickHouse被强制选择颗粒组(因为他们可能包含行(s)匹配的查询)。
3.4 需要使用多个主索引
因此如果我们想要显著加快过滤具有特定URL的行的示例查询那么我们需要使用针对该查询优化的主索引。
此外如果我们希望保持过滤具有特定UserID的行的示例查询的良好性能那么我们需要使用多个主索引。
以下是实现这一目标的方法。
3.5 用于创建其他主索引的选择
如果我们想显著提高我们的两个示例查询的速度——一个是过滤带有特定UserID的行另一个是过滤带有特定URL的行——那么我们需要使用以下三个选项之一来使用多个主索引:
使用不同的主键创建第二个表second table 。在现有表上创建物化视图materialized view。向现有表添加一个投影projection 。
所有这三个选项都将有效地将我们的示例数据复制到另一个表中以便重新组织表的主索引和行排序顺序。
但是对于查询和插入语句的路由这三种选项的不同之处在于附加表对用户的透明程度。
当用不同的主键创建第二个表时查询必须显式地发送到最适合查询的表版本并且必须显式地将新数据插入到两个表中以保持表同步。 在物化视图中额外的表是隐式创建的数据在两个表之间自动保持同步: 投影是最透明的选项因为除了自动保持隐式创建(和隐藏)额外表的同步的数据变化ClickHouse将自动选择查询最有效的表版本: 在下文中我们将通过实际示例详细讨论创建和使用多个主索引的这三个选项。
3.6 选项1:辅助表第二个表
我们正在创建一个新的附加表我们在主键中切换键列的顺序(与原始表相比):
CREATE TABLE hits_URL_UserID
(UserID UInt32,URL String,EventTime DateTime
)
ENGINE MergeTree
PRIMARY KEY (URL, UserID)
ORDER BY (URL, UserID, EventTime)
SETTINGS index_granularity 8192, index_granularity_bytes 0;将原始表中的所有887万行插入到附加表中: 最后对表进行优化:
OPTIMIZE TABLE hits_URL_UserID FINAL;因为我们改变了主键中列的顺序插入的行现在以不同的字典顺序存储在磁盘上(与我们的原始表相比)因此该表的1083个颗粒包含的值也与以前不同: 这是得到的主键: 现在这可以用来显著加快我们的示例查询过滤在URL列上的执行速度以便计算最常点击URL“http://public_search”的前10个用户:
SELECT UserID, count(UserID) AS Count
FROM hits_URL_UserID
WHERE URL http://public_search
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;现在ClickHouse可以更有效地执行查询而不是执行全表扫描。
对于原始表的主索引其中UserID是第一个键列URL是第二个键列ClickHouse在索引标记上使用通用排除搜索来执行该查询这不是很有效因为UserID和URL的基数相似。
将URL作为主索引的第一列ClickHouse现在在索引标记上运行二进制搜索。
...Executor): Key condition: (column 0 in [http://public_search,http://public_search])
...Executor): Running binary search on index range for part all_1_9_2 (1083 marks)
...Executor): Found (LEFT) boundary mark: 644
...Executor): Found (RIGHT) boundary mark: 683
...Executor): Found continuous range in 19 steps
...Executor): Selected 1/1 parts by partition key, 1 parts by primary key,39/1083 marks by primary key, 39 marks to read from 1 ranges
...Executor): Reading approx. 319488 rows with 2 streamsClickHouse只选择了39个索引标记而不是使用通用排除搜索时的1076个。
注意附加表经过优化以加快我们的示例查询过滤url的执行速度。
与原始表查询的糟糕性能类似我们对UserID的示例查询过滤在新的附加表上不会非常有效地运行因为UserID现在是该表主索引中的第二个关键列因此ClickHouse将使用通用排除搜索进行粒度选择这对于UserID和URL的类似高基数来说不是很有效。打开详细信息框查看详细信息。
我们现在有两个表。优化了加速对UserIDs的查询过滤加速对URLs的查询过滤:
3.7 选项2:物化视图
在现有表上创建一个物化视图。
CREATE MATERIALIZED VIEW mv_hits_URL_UserID
ENGINE MergeTree()
PRIMARY KEY (URL, UserID)
ORDER BY (URL, UserID, EventTime)
POPULATE
AS SELECT * FROM hits_UserID_URL;我们在视图的主键中切换键列的顺序(与原始表相比)物化视图由隐式创建的表支持该表的行顺序和主索引基于给定的主键定义隐式创建的表由SHOW TABLES查询列出其名称以.inner开头也可以首先显式地为物化视图创建后备表然后视图可以通过TO [db].[table]来瞄准该表。我们使用POPULATE关键字是为了立即用源表hits_UserID_URL中的所有887万行填充隐式创建的表如果新行插入到源表hits_UserID_URL中那么这些行也会自动插入到隐式创建的表中实际上隐式创建的表与我们显式创建的第二张表具有相同的行顺序和主索引: ClickHouse将隐式创建表的列数据文件(.bin)标记文件(.mrk2)和主索引(primary.idx)存储在ClickHouse服务器数据目录下的一个特殊文件夹中: 支持物化视图的隐式创建的表(和它的主索引)现在可以用来显著加快我们的示例查询过滤URL列的执行速度: SELECT UserID, count(UserID) AS Count
FROM mv_hits_URL_UserID
WHERE URL http://public_search
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;由于支持物化视图的隐式创建的表(及其主索引)实际上与我们显式创建的辅助表相同因此查询以与显式创建的表相同的有效方式执行。
3.8 方案3:投影
在我们现有的表上创建一个投影:
ALTER TABLE hits_UserID_URLADD PROJECTION prj_url_userid(SELECT *ORDER BY (URL, UserID));并实现投影:
ALTER TABLE hits_UserID_URLMATERIALIZE PROJECTION prj_url_userid;投影将创建一个隐藏表hidden table 其行顺序和主索引基于投影的给定ORDER BY子句隐藏表没有被SHOW TABLES查询列出我们使用MATERIALIZE关键字是为了立即用源表hits_UserID_URL中的所有887万行填充隐藏表如果新行插入到源表hits_UserID_URL中那么这些行也会自动插入到隐藏表中查询总是(语法上)以源表hits_UserID_URL为目标但是如果隐藏表的行顺序和主索引允许更有效地执行查询那么将使用该隐藏表请注意投影不会使使用ORDER BY的查询更有效即使ORDER BY与投影的ORDER BY语句匹配(参见https://github.com/ClickHouse/ClickHouse/issues/47333)。实际上隐式创建的隐藏表与我们显式创建的第二张表具有相同的行顺序和主索引: ClickHouse将隐藏表的列数据文件(.bin)、标记文件(.mrk2)和主索引文件(primary.idx)存储在源表的数据文件、标记文件和主索引文件旁边的一个特殊文件夹中(在下面的截图中以橙色标记): 投影创建的隐藏表(及其主索引)现在可以(隐式地)用于显著加快我们的示例查询过滤在URL列上的执行速度。注意查询在语法上以投影的源表为目标。
SELECT UserID, count(UserID) AS Count
FROM hits_UserID_URL
WHERE URL http://public_search
GROUP BY UserID
ORDER BY Count DESC
LIMIT 10;由于投影创建的隐藏表(及其主索引)实际上与我们显式创建的辅助表(第二张表)相同因此以与显式创建的表相同的有效方式执行查询。
4、高效排序关键列
在复合主键中键列的顺序可以显著地影响以下两个方面:
查询中辅助键列的过滤效率以及表数据文件的压缩比。
为了证明这一点我们将使用我们的网络流量样本数据集的一个版本其中每行包含三列表明互联网“用户”(UserID列)对URL (URL列)的访问是否被标记为机器人流量(IsRobot列)。
我们将使用包含上述所有三列的复合主键这可以用来加速典型的web分析查询
有多少(百分比)流量到一个特定的URL是来自机器人或我们有多确信某个特定用户是(不是)机器人(该用户的流量中有多少百分比被假定为(不是)机器人流量)
我们使用这个查询来计算我们想要用作复合主键中的键列的三列的基数(注意我们使用URL表函数来单独查询TSV数据而不必创建本地表)。在clickhouse客户端(clickhouse client)运行这个查询:
SELECTformatReadableQuantity(uniq(URL)) AS cardinality_URL,formatReadableQuantity(uniq(UserID)) AS cardinality_UserID,formatReadableQuantity(uniq(IsRobot)) AS cardinality_IsRobot
FROM
(SELECTc11::UInt64 AS UserID,c15::String AS URL,c20::UInt8 AS IsRobotFROM url(https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz)WHERE URL !
)我们可以看到基数之间存在很大的差异特别是在URL列和IsRobot列之间因此复合主键中这些列的顺序对于在这些列上过滤查询的有效速度和实现表的列数据文件的最佳压缩比都非常重要。
为了演示我们为机器人流量分析数据创建了两个表版本:
表hits_URL_UserID_IsRobot具有复合主键(URL, UserID, IsRobot)其中我们按基数降序排列键列表hits_IsRobot_UserID_URL与复合主键(IsRobot, UserID, URL)我们按基数升序排序键列
用复合主键(URL, UserID, IsRobot)创建表hits_URL_UserID_IsRobot:
CREATE TABLE hits_URL_UserID_IsRobot
(UserID UInt32,URL String,IsRobot UInt8
)
ENGINE MergeTree
PRIMARY KEY (URL, UserID, IsRobot);用887万行填充它:
INSERT INTO hits_URL_UserID_IsRobot SELECTintHash32(c11::UInt64) AS UserID,c15 AS URL,c20 AS IsRobot
FROM url(https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz)
WHERE URL ! ;接下来用复合主键(IsRobot, UserID, URL)创建表hits_IsRobot_UserID_URL:
CREATE TABLE hits_IsRobot_UserID_URL
(UserID UInt32,URL String,IsRobot UInt8
)
ENGINE MergeTree
PRIMARY KEY (IsRobot, UserID, URL);然后用我们用来填充前一个表的887万行来填充它:
INSERT INTO hits_IsRobot_UserID_URL SELECTintHash32(c11::UInt64) AS UserID,c15 AS URL,c20 AS IsRobot
FROM url(https://datasets.clickhouse.com/hits/tsv/hits_v1.tsv.xz)
WHERE URL ! ;4.1 对辅助键列进行有效过滤
当查询对至少一个属于复合键的列进行过滤时并且该列是第一个键列那么ClickHouse将在键列的索引标记上运行二分搜索算法。
当查询(仅)过滤复合键的一部分列但不是第一个键列时ClickHouse在键列的索引标记上使用通用排除搜索算法。
对于第二种情况复合主键中键列的顺序对通用排除搜索算法的有效性至关重要。
这是一个对表的UserID列进行过滤的查询我们将关键列(URL, UserID, IsRobot)按基数降序排列:
SELECT count(*)
FROM hits_URL_UserID_IsRobot
WHERE UserID 112304这是我们对键列(IsRobot, UserID, URL)按基数升序排序的表上的相同查询:
SELECT count(*)
FROM hits_IsRobot_UserID_URL
WHERE UserID 112304我们可以看到在按基数按升序排列键列的表上查询执行明显更加有效和快速。
这样做的原因是当通过前一个键列具有较低基数的辅助键列选择粒度时通用排除搜索算法最有效。我们在本指南的前一节中详细说明了这一点。
4.2 数据文件的最佳压缩比
这个查询比较了我们上面创建的两个表之间UserID列的压缩比:
SELECTtable AS Table,name AS Column,formatReadableSize(data_uncompressed_bytes) AS Uncompressed,formatReadableSize(data_compressed_bytes) AS Compressed,round(data_uncompressed_bytes / data_compressed_bytes, 0) AS Ratio
FROM system.columns
WHERE (table hits_URL_UserID_IsRobot OR table hits_IsRobot_UserID_URL) AND (name UserID)
ORDER BY Ratio ASC我们可以看到对于按基数升序排列键列(IsRobot、UserID、URL)的表UserID列的压缩比要高得多。
尽管在两个表中存储的数据完全相同(我们在两个表中插入了相同的887万行)复合主键中键列的顺序对表列数据文件中压缩数据所需的磁盘空间有显著影响:
在具有复合主键(URL, UserID, IsRobot)的hits_URL_UserID_IsRobot表中我们按基数降序排列键列UserID.bin数据文件占用11.79 MiB的磁盘空间在具有复合主键(IsRobot, UserID, URL)的表hits_IsRobot_UserID_URL中我们按基数升序对键列进行排序UserID.bin数据文件只占用了1.19 MiB的磁盘空间
为磁盘上的表列的数据提供良好的压缩比不仅可以节省磁盘空间而且还可以使需要从该列读取数据的查询(特别是分析查询)更快因为将列的数据从磁盘移动到主内存(操作系统的文件缓存)所需的i/o更少。
在下文中我们将说明为什么按基数升序排列主键列有利于表列的压缩比。
下面的图表描绘了主键的磁盘上的行顺序其中键列按基数顺序升序排列: 我们讨论了表的行数据存储在按主键列排序的磁盘上。
在上面的图表中表的行(它们在磁盘上的列值)首先按照它们的cl值排序具有相同cl值的行按照它们的ch值排序。而且由于第一个键列cl具有较低的基数因此很可能存在具有相同cl值的行。正因为如此ch值也可能是有序的(局部-对于具有相同cl值的行)。
如果在一列中相似的数据彼此靠近放置例如通过排序那么该数据将被更好地压缩。通常压缩算法受益于数据的运行长度(看到的数据越多压缩效果越好)和局部性(数据越相似压缩比越好)。
与上面的图相反下面的图描绘了主键的磁盘上的行顺序其中键列是按基数降序排列的: 现在表的行首先按照它们的ch值排序具有相同ch值的行按照它们的cl值排序。但是由于第一个键列ch具有很高的基数所以不太可能存在具有相同ch值的行。正因为如此cl值也不太可能被排序(局部-对于具有相同ch值的行)。
因此cl值很可能是随机顺序因此分别具有较差的局部性和压缩比。
4.3 总结
为了在查询中对辅助键列进行有效的过滤和表的列数据文件的压缩比将主键中的列按基数升序排列是有益的。
4.4 相关内容
博客: Super charging your ClickHouse queries
5、有效识别单行
虽然一般来说这不是ClickHouse的最佳用例但有时基于ClickHouse构建的应用程序需要识别ClickHouse表的单行。
一种直观的解决方案可能是使用每行具有唯一值的UUID列为了快速检索行将该列用作主键列。
为了获得最快的检索速度UUID列需要是第一个键列。
我们讨论过由于ClickHouse表的行数据存储在按主键列排序的磁盘上因此在主键或复合主键中拥有一个基数非常高的列(如UUID列)然后是基数较低的列这不利于其他表列的压缩比。
最快检索和最优数据压缩之间的折衷是使用复合主键其中UUID是低基数键列之后的最后一个键列用于确保表的某些列具有良好的压缩比。
具体的例子
一个具体的例子是Alexey Milovidov开发和博客记录的明文粘贴服务https://pastila.nl。
每次对文本区域进行更改时数据都会自动保存到ClickHouse表的一行中(每次更改一行)。
标识和检索(特定版本)粘贴内容的一种方法是使用内容的散列作为包含内容的表行的UUID。
下图显示了
当内容发生更改时(例如由于击键将文本输入到文本区域中)的行插入顺序和当使用PRIMARY KEY (hash)时插入行的数据在磁盘上的顺序: 因为hash 列被用作主键列
可以非常快速地检索特定的行但是表的行(它们的列数据)存储在磁盘上按(唯一的和随机的)哈希值升序排列。因此内容列的值以随机顺序存储没有数据局域性导致内容列数据文件的压缩比不是最优的。
为了显著提高内容列的压缩比同时仍能实现对特定行的快速检索pastila.nl 使用两个哈希(和一个复合主键)来标识特定的行:
内容的散列如上所述对于不同的数据是不同的一种对位置敏感的散列(指纹)它不会因数据的微小变化而改变。
下图显示了:
当内容发生更改时(例如由于击键将文本输入到文本区域中)的行插入顺序和当使用复合PRIMARY KEY (fingerprint, hash)时插入行的数据在磁盘上的顺序: 现在磁盘上的行首先按fingerprint排序对于具有相同指纹值的行它们的hash 值决定了最终顺序。
因为只有微小变化的数据会得到相同的指纹值所以相似的数据现在存储在磁盘上的内容列中彼此靠近。这对于内容列的压缩比是非常好的因为压缩算法通常受益于数据的局部性(数据越相似压缩比越好)。
折衷方案是检索特定行需要两个字段(fingerprint 和hash)以便最佳地利用复合PRIMARY KEY (fingerprint, hash)产生的主索引。
局部敏感哈希算法
局部敏感哈希算法Locality Sensitive Hashing (LSH)
LSH
