云数据库ClickHouse二级索引-最佳实践

中国鑫

您未得到众大云收罗的授权，部门功能受到影响！


尊重的用户：

　　您好！非常感谢您能安装和关注智伍应用旗下的产物，为了产物的可连续发展和升级，众大云收罗已经开始收费。

　　向用户收费是为了给用户更可靠的保障和服务，所收取的费用重要用于产物的正常运作、研发和改进，盼望各位可以或许明白和支持。

　　别的，为了报答新老客户，众大云收罗5折优惠，原价980元，如今购买仅需490元，给您节流了490元。

　　客服QQ：2891276344，微信：ccccyyyy4444

　　购买正式版永世授权请打开下面的网址：

http://www.zhiwu55.com/authorization/csdn123_news.php?hzw_appid=6A43D6F53C406FB5EAEBC6155B3E2911

　　购买正式版授权之后全部的未授权提示将主动消散，图片也正常表现，正式版授权永世有用终身可用，后续的升级更新也是免费的，一次购买一辈子都能用，无后顾之忧！

---

提示：为了您网站的内容安全，请不要发布违背国家法律法规的内容，您现在利用的是免费试用版，可以手动删除上面的未购买授权的提示，发布这篇文章！

原标题：云数据库ClickHouse二级索引-最佳实践

弁言：阿里云数据库ClickHouse二级索引功能克日已正式发布上线，重要补充了ClickHouse在海量数据分析场景下，多维度点查本领不敷的短板。在以往服务用户的过程中，作者发现绝大部门用户对ClickHouse单表查询性能优化题目感到无从动手，借此时机，本文会先为各人睁开先容ClickHouse在单表分析查询性能优化上的几个方法，根本涵盖了OLAP范畴存储层扫描加快的全部常用本领。在办理过各种各样业务场景下的性能优化题目后，作者发现现在ClickHouse在办理多维搜刮题目上确实本领不敷，一条点查经常浪费巨大的IO、CPU资源，于是云数据库ClickHouse自研了二级索引功能来彻底办理题目，本文会具体先容二级索引的DDL语法、几个典范实用场景和特色功能。盼望可以通过本文让各人对ClickHouse在OLAP场景下的本领有更深的明白，同时论述清晰二级索引实用的搜刮场景。

存储扫描性能优化

在先容各类OLAP存储扫描性能优化技能之前，作者先在这里说明一个简朴的代价模子和一些OLAP的配景知识。本文利用最简朴的代价模子来盘算OLAP存储扫描阶段的开销：磁盘扫描读取的数据量。在雷同ClickHouse如许纯列式的存储和盘算引擎中，数据的压缩、盘算、流转都是以列块为单元按列举行的。在ClickHouse中，只能对数据列以块为单元举行定位读取，固然用户的查询是按照uid查询确定的某一条记载，但是从磁盘读取的数据量会被放大成块巨细 * 列数。本文中不思量数据缓存(BlockCache / PageCache)这些优化因素，由于Cache可以到达的优化结果不是稳固的。

排序键优化-跳跃扫描

排序键是ClickHouse最重要依靠的存储扫描加快技能，它的寄义是让存储层每个DataPart里的数据按照排序键举行严酷有序存储。正是这种有序存储的模式，构成了ClickHouse "跳跃"扫描的底子和重复数据高压缩比的本领（对于ClickHouse的MergeTree存储布局不认识的同砚可以参考往期文章《ClickHouse内核分析-MergeTree的存储布局和查询加快》）。

CREATE TABLE IF NOT EXISTS order_info ( `oid` UInt64, --订单ID `buyer_nick` String, --买家ID `seller_nick` String, --店肆ID `payment` Decimal64(4), --订单金额 `order_status` UInt8, --订单状态 ... `gmt_order_create` DateTime, --下单时间 `gmt_order_pay` DateTime, --付款时间 `gmt_update_time` DateTime, --记载变动时间 INDEX oid_idx (oid) TYPE minmax GRANULARITY 32 ) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(gmt_order_create) --以天为单元分区 ORDER BY (seller_nick, gmt_order_create, oid) --排序键 PRIMARY KEY (seller_nick, gmt_order_create) --主键 SETTINGS index_granularity = 8192;

以一个简朴的订单业务场景为例（表布局如上），order by界说了数据文件中的记载会按照店肆ID , 下单时间以及订单号组合排序键举行绝对有序存储，而primary key和index_granularity两者则界说了排序键上的索引布局长什么样子，ClickHouse为每一个有序的数据文件构造了一个"跳跃数组"作为索引，这个"跳跃数组"中的记载是从原数据中按肯定隔断抽取出来得到的（简化明白就是每隔index_granularity抽取一行记载），同时只保存primary key界说里的seller_nick, gmt_order_create两个前缀列。如下图所示，有了这个全内存的"跳跃数组"作为索引，优化器可以快速排撤除和查询无关的行号区间，大大淘汰磁盘扫描的数据量。至于为何不把oid列放到primary key中，读者可以细致思索一下缘故原由，和index_granularity设定值巨细也有关。

作者遇到过许多用户在把mysql的binlog数据迁徙到ClickHouse上做分析时，照搬照抄mysql上的主键界说，导致ClickHouse的排序键索引根本没有发挥出任何作用，查询性能重要就是靠ClickHouse牛逼的数据并行扫描本领和高效的列式盘算引擎在硬抗，这也从侧面反应出ClickHouse在OLAP场景下的绝对性能上风，没有任何索引仍旧可以很快。业务体系中的mysql重要偏重单条记载的事件更新，主键是可以简朴明白界说成oid，但是在OLAP场景下查询都必要做大数据量的扫描分析，ClickHouse必要用排序键索引来举行"跳跃"扫描，用户建表时应只管把业务记载生命周期中稳定的字段都放入排序键(一样平常distinct count越小的列放在越前)。

分区键优化-MinMax裁剪

继承上一节中的业务场景，当业务必要查询某一段时间内全部店肆的全部订单量时，primary key中界说的"跳跃"数组索引效用就不那么显着了，查询如下：

select count(*) from order_info where gmt_order_create > '2020-0802 00:00:00' and gmt_order_create < '2020-0804 15:00:00'

ClickHouse中的primary key索引有一个致命题目是，当前缀列的离散度(distinct value count)非常大时，在后续列上的过滤条件起到的"跳跃"加快作用就很薄弱了。这个实在很好明白，当"跳跃数组"中相邻的两个元组是('a', 1)和('a', 10086)时，我们可以推断出第二列在对应的行号区间内值域是[1, 10086]；若相邻的元素是('a', 1)和('b', 10086)，则第二列的值域范围就酿成(-inf, +inf)了，无法依据第二列的过滤条件举行跳过。

这时间就必要用到partition by优化了，ClickHouse中差别分区的数据是在物理上隔脱离的，同时在数据生命管理上也是独立的。partition by就似乎是多个DataPart文件聚集(数据分区)之间的"有序状态"，而上一节中的order by则是单个DataPart文件内部记载的"有序状态"。每一个DataPart文件只属于一个数据分区，同时在内存里保有partition by列的MinMax值记载。上述查询case中，优化器根据每个DataPart的gmt_order_create最大值最小值可以快速裁剪掉不相干的DataPart，这中裁剪方式对数据的筛选服从比排序键索引更高。

这里教各人一个小本领，假如业务方既要根据下单时间范围聚合分析，又要根据付款时间范围聚合分析，该怎样计划分区键呢？像这类有业务相干性的两个时间列，同时时间差距上又是有业务束缚的环境下，我们可以把partition by界说成：

(toYYYYMMDD(gmt_order_create), (gmt_order_pay - gmt_order_create)/3600/240)

如许一来DataPart在gmt_order_create和gmt_order_pay两列上就都有了MinMax裁剪索引。在计划数据分区时，各人必要留意两点：1）partition by会对DataPart起到物理隔离，以是数据分区过细的环境下会导致底层的DataPart数目膨胀，肯定水平影响那种大范围的查询性能，要控制partition by的粒度；2）partition by和order by都是让数据存放到达"有序状态"的技能，界说的时间应当只管错开利用差别的列来界说两者，order by的第一个列肯定不要重复放到partition by里，一样平常来说时间列更得当放在partition by上。

Skipping index优化-MetaScan

在ClickHouse开源版本里，用户就可以通过自界说index来加快分析查询。但是现实利用中，绝大部门用户都不明白这个文档上写的"skipping index"是个什么原理？为什么创建index之后查询一点都没有变快大概反而变慢了？？？由于"skipping index"并不是真正意义上的索引，正常的索引都是让数据按照索引key举行聚集，大概把行号按照索引key聚集起来。而ClickHouse的"skipping index"并不会做任何聚集的事变，它只是加快筛选Block的一种本领。以 INDEX oid_idx (oid) TYPE minmax GRANULARITY 32 这个索引界说为例，它会对oid列的每32个列存块做一个minmax值统计，统计效果存放在单独的索引文件里。下面的查询在没有oid skipping index的环境下，至少必要扫描5天的数据文件，才气找到对应的oid行。有了索引后，数据扫描的逻辑酿成了先扫描oid索引文件，查抄oid的minmax区间是否覆盖目的值，后续扫描主表的时间可以跳过不相干的Block，这实在就是在OLAP里常用的Block Meta Scan技能。

select * from order_info where gmt_order_create > '2020-0802 00:00:00' and gmt_order_create < '2020-0807 00:00:00' and oid = 726495;

当oid列存块的minmax区间都很大时，这个skipping index就无法起到加快作用，乃至会让查询更慢。现实在这个业务场景下oid的skipping idex是有作用的。上一节讲过在同一个DataPart内数据重要是按照店肆ID和下单时间举行排序，全部在同一个DataPart内的oid列存块minmax区间根本都是重叠的。但是ClickHouse的MergeTree另有一个隐含的有序状态：那就是同一个partition下的多个DataPart是处于按写入时间分列的有序状态，而业务体系里的oid是一个自增序列，刚好写入ClickHouse的数据oid根本也是按照时间递增的趋势，差别DataPart之间的oid列存块minmax就根本是错开的。

不难明白，skipping index对查询的加快结果是一个常数级别的，索引扫描的时间是和数据量成正比的。除了minmax范例的skipping index，另有set范例索引，它实用的场景也是要求列值随着写入时间有显着局部性。剩下的bloom_filter、ngrambf_v1、tokenbf_v1则是通过把完备的字符串列大概字符串列分词后的token用bloom_filter天生高压缩比的署名来举行清除Block，在长字符串的场景下有肯定加快空间。

Prewhere优化-两阶段扫描

前面三节讲到的全部性能优化技能根本都是依靠数据的"有序性"来加快扫描满意条件的Block，这也意味着满意查询条件的数据自己就是存在某种"局部性"的。正常的业务场景中只要满意条件的数据自己存在"局部性"就肯定能通过上面的三种方法来加快查询。在计划业务体系时，我们乃至应该刻意去创造出更多的"局部性"，比方本文例子中的oid假如是计划成"toYYYYMMDDhh(gmt_order_create)+店肆ID+递增序列"，那就可以在DataPart内部筛选上得到"局部性"，查询会更快，读者们可以深入思索下这个题目。

在OLAP场景下最难搞定的题目就是满意查询条件的数据没有任何"局部性"：查询条件掷中的数据大概行数不多，但是分布非常散乱，每一个列存块中都有一两条记载满意条件。这种环境下由于列式存储的关系，只要块中有一条记载掷中体系就必要读取完备的块，末了几百上千倍的数据量必要从磁盘中读取。固然这是个极度环境，许多时间环境是一部门列存块中没有满意条件的记载，一部门列存块中包罗少量满意条件的记载，团体出现随机无序。这种场景下固然可以对查询条件列加上雷同lucene的倒排索引快速定位到掷中记载的行号聚集，但索引是有额外存储、构建本钱的，更好的方法是接纳两阶段扫描来加快。

以下面的查询为例，正常的实行逻辑中存储层扫描会把5天内的全部列数据从磁盘读取出来，然后盘算引擎再按照order_status列过滤出满意条件的行。在两阶段扫描的框架下，prewhere表达式会下推到存储扫描过程中实行，优先扫描出order_status列存块，查抄是否有记载满意条件，再把满意条件行的其他列读取出来，当没有任何记载满意条件时，其他列的块数据就可以跳过不读了。

--通例 select * from order_info where where order_status = 2 --订单取消 and gmt_order_create > '2020-0802 00:00:00' and gmt_order_create < '2020-0807 00:00:00'； --两阶段扫描 select * from order_info where prewhere order_status = 2 --订单取消 where gmt_order_create > '2020-0802 00:00:00' and gmt_order_create < '2020-0807 00:00:00'；

这种两阶段扫描的头脑就是优先扫描筛选率高的列举行过滤，再按需扫描其他列的块。在OLAP几百列的大宽表分析场景下，这种加快方式淘汰的IO结果黑白常显着的。但是瓶颈也是确定的，至少必要把某个单列的数据全部扫描出来。现在各人在利用prewhere加快的时间最好是根据数据分布环境来挑选最有筛选率同时扫描数据量最少的过滤条件。当遇上那种每个Block中都有一两条记载满意查询条件的极度环境时，实验利用ClickHouse的物化视图来办理吧。物化视图的作用不但是预聚合盘算，也可以让数据换个排序键重新有序存储一份。另有一种zorder的技能也能缓解一部门此类题目，有爱好的同砚可以本身相识一下。

小结

前面四节分别先容了ClickHouse中四种差别的查询加快技能，当满意查询条件的数据有显着的"局部性"时，各人可以通过前三种低本钱的本领来加快查询。末了先容了针对数据分布非常散乱的场景下，prewhere可以缓解多列分析的IO压力。现实上这四种优化本领都是可以联合利用的，本文拆开论述只是为了方便各人明白它们的本质。连续上一节的题目，当数据分布非常散乱，同时查询掷中的记载又只有多少条的场景下，就算利用prewhere举行两阶段扫描，它的IO放大题目也仍旧黑白常显着的。简朴的例子就是查询某个特定买家id(buyer_nick)的购买记载，买家id在数据表中的分布是完全散乱的，同时买家id列全表扫描的代价过大。以是阿里云ClickHouse推出了二级索引功能，专门来办理这种少量效果的搜刮题目。这里怎样界说效果的少呢？一样平常列存体系中一个列存块包罗靠近10000行记载，当满意搜刮条件的记载数比列存块小一个数目级时(筛选率凌驾100000:1)，二级索引才气发挥比力显着的性能上风。

二级索引多维搜刮

ClickHouse的二级索引在计划的时间对标的就是ElasticSearch的多维搜刮本领，支持多索引列条件交并差检索。同时对比ElasticSearch又有更贴近ClickHouse的易用性优化，总体特点概括如下：

• 多列团结索引 & 表达式索引

• 函数下推

• In Set Clause下推

• 多值索引 & 字典索引

• 高压缩比 1:1 vs lucene 8.7

• 向量化构建 4X vs lucene 8.7

通例索引

二级索引在创建表时的界说语句示比方下：

CREATE TABLE IF NOT EXISTS index_test ( id UInt64, d DateTime, x UInt64, y UInt64, tag String, KEY tag_idx tag TYPE range, --单列索引 KEY d_idx toStartOfHour(d) TYPE range, --表达式索引 KEY combo_idx (toStartOfHour(d)，x, y) TYPE range, --多列团结索引 ) ENGINE = MergeTree() ORDER BY id;

其他二级索引相干的修改DDL如下：

--删除索引界说 Alter table index_test DROP KEY tag_idx; --增长索引界说 Alter table index_test ADD KEY tag_idx tag TYPE range; --扫除数据分区下的索引文件 Alter table index_test CLEAR KEY tag_idx tag in partition partition_expr; --重新构建数据分区下的索引文件 Alter table index_test MATERIALIZE KEY tag_idx tag in partition partition_expr;

支持多列索引的目标是淘汰特定查询pattern下的索引效果归并，针对QPS要求特殊高的查询用户可以创建针对性的多列索引到达极致的检索性能。而表达式索引重要是方便用户举行自由的检索粒度变更，思量以下两个典范场景：

1）索引中的时间列在搜刮条件中，只会以小时粒度举行过滤，这种环境下用户可以对toStartOfHour(time)表达式创建索引，可以肯定水平加快索引构建，同时对time列的时间过滤条件都可以主动转换下推索引。

2）索引中的id列是由UUID构成，UUID险些是可以包管永世distinct的字符串序列，直接对id构建索引会导致索引文件太大。这时用户可以利用前缀函数截取UUID来构建索引，如prefix8(id)是截取8个byte的前缀，对应的另有prefix4和prefix16，prefixUTF4、prefixUTF8、prefixUTF16则是用来截取UTF编码的。

值得留意的是，用户对表达式构建索引后，原列上的查询条件也可以正常下推索引，不必要特意改写查询。同样用户对原列构建索引，过滤条件上对原列加了表达式的环境下，优化器也都可以正常下推索引。

In Set Clause下推则是一个关联搜刮的典范场景，作者常常遇到此类场景：user的属性是一张单独的大宽表，user的举动记载又是另一张单独的表，对user的搜刮必要先从user举动记载表中聚合过滤出满意条件的user id，再用user ids附属性表中取出明细记载。这种in subquery的场景下，ClickHouse也可以主动下推索引举行加快。

多值索引

多值索引重要针对的是array范例列上has()/hasAll()/hasAny()条件的搜刮加快，array列时标签分析里常用的数据范例，每条记载会attach对个标签，存放在一个array列里。对这个标签列的过滤以往只能通过暴力扫描过滤，ClickHouse二级索引专门扩展了多值索引范例办理此类题目，索引界说示比方下：

CREATE TABLE IF NOT EXISTS index_test ( id UInt64, tags Array(String), KEY tag_idx tag TYPE array --多值索引 ) ENGINE = MergeTree() ORDER BY id; --包罗单个标签 select * from index_test where has(tags, 'aaa'); --包罗全部标签 select * from index_test where hasAll(tags, ['aaa', 'bbb']); --包罗恣意标签 select * from index_test where has(tags, ['aaa', 'ccc']);

字典索引

字典索引重要是针对那种利用两个array范例列模仿Map的场景举行检索加快，key和value是两个单独的array列，通过元素的position逐一对应举行kv关联。ClickHouse二级索引专门为这种场景扩展了检索函数和索引范例支持，索引界说示比方下：

CREATE TABLE IF NOT EXISTS index_test ( id UInt64, keys Array(String), vals Array(UInt32), KEY kv_idx (keys, vals) TYPE map --字典索引 ) ENGINE = MergeTree() ORDER BY id; --指定key的value等值条件 map['aaa'] = 32 select * from index_test where hasPairEQ(keys, vals, ('aaa', 32)); --指定key的value大于条件 map['aaa'] > 32 select * from index_test where hasPairGT(keys, vals, ('aaa', 32)); --指定key的value大于即是条件 map['aaa'] >= 32 select * from index_test where hasPairGTE(keys, vals, ('aaa', 32)); --指定key的value小于条件 map['aaa'] < 32 select * from index_test where hasPairLT(keys, vals, ('aaa', 32)); --指定key的value小于即是条件 map['aaa'] <= 32 select * from index_test where hasPairLTE(keys, vals, ('aaa', 32));

索引构建性能

作者对ClickHouse的二级索引构建性能和索引压缩率做了全方位多场景下的测试，重要对比的是lucene 8.7的倒排索引和BKD索引。ElasticSearch底层的索引就是接纳的lucene，这里的性能数据读者可以作个参考，但并不代表ElasticSearch和ClickHouse二级索引功能端到端的性能程度。由于ClickHouse的二级索引是在DataPart merge的时间举行构建，相识ClickHouse MergeTree存储引擎的同砚应该明确MergeTree存在写放大的环境(一条记载merge多次)，同时merge又完满是异步的举动。

日记trace_id场景

mock数据方法：

substringUTF8(cast (generateUUIDv4() as String), 1, 16)

数据量：1E (ClickHouse数据文件1.5G)

构建耗时：

ClickHouse 65.32s vs Lucene 487.255s

索引文件巨细：

ClickHouse 1.4G vs Lucene 1.3G

字符串罗列场景

mock数据方法：

cast((10000000 + rand(number) % 1000) as String)

数据量：1E (ClickHouse数据文件316M)

构建耗时：

ClickHouse 37.19s vs Lucene 46.279s

索引文件巨细：

ClickHouse 160M vs Lucene 163M

数值散列场景

mock数据方法：

toFloat64(rand(100000000))

数据量：1E (ClickHouse数据文件564M)

构建耗时：

ClickHouse 32.20s vs Lucene BKD 86.456s

索引文件巨细：

ClickHouse 801M vs Lucene BKD 755M

数值罗列场景

mock数据方法：

rand(1000)

数据量：1E (ClickHouse数据文件275M)

构建耗时：

ClickHouse 12.81s vs Lucene BKD 78.0s

索引文件巨细：

ClickHouse 160M vs Lucene BKD 184M

结语

二级索引功能的重要目标是为了补充ClickHouse在搜刮场景下的不敷，在分析场景下ClickHouse现在原有的技能已经比力丰富。盼望通过本文各人对OLAP查询优化有更深的明白，接待各人实验利用二级索引来办理多维搜刮题目，积极反馈利用体验题目。

作者：仁劼

本文为阿里云原创内容，未经答应不得转载返回搜狐，检察更多

责任编辑：