云数据库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 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需要用排序键索引来进行"跳跃"扫描，用户建表时应尽量把业务记录生命