Clickhouse的联合索引

Clickhouse 有了单独的键索引，为什么还需要有联合索引呢？了解过mysql的兄弟们应该都知道这个事。
对sql比较熟悉的兄弟们估计看见这个联合索引心里大概有点数了，不过clickhouse的联合索引相比mysql的又有些不一样了，mysql 很遵循最左匹配原则，但是clickhouse 又似乎有些不一样，具体哪里不一样呢，那么我们还是和上一篇 Q220240407–clickhouse 中的索引大白话一样，通过引用这张表hits_UserID_URL 来揭开下这个谜底。

一：联合索引
回到hits_UserID_URL的建表语句如下，我们当时创建表的时候，我们的主键就指定了UserID 和URL 如下：

CREATE TABLE hits_UserID_URL
(
UserID UInt32,
URL String,
EventTime DateTime
)
ENGINE = MergeTree
PRIMARY KEY (UserID, URL)
ORDER BY (UserID, URL, EventTime);
这样的主键大于1的情况，生成的那个primary.idx 索引文件(参考上一篇) 也就是会每一行都会生成n列记录(n>1),这样的索引文件我们就可以称之为联合索引文件是吧，故而UserID 和URL就形成了一个联合索引。其实为什么需要联合索引呢？那肯定是说在实际业务中这两个列经常放在一起查询，所以才会考虑给这两个列加索引。

但是联合索引的效果就真的好吗，这个取决于第一列索引的基数，什么是基数呢？说白了，就是某个列的值去重之后的个数，如果个数多说明基数高，如果个数少就说明基数低；那为什么联合索引的效果是取决于基数呢？因为联合索引在找数据的时候是使用排除搜索法的，下面我们来讲讲排除搜索法

1.1: 排除搜索法

排除搜索法顾名思义就是排除掉一些数据，然后搜索一波：

因为userId 和url 是按照字典顺序升序排列的，假如左边的键userID 的基数比较低，那么相同的userId可能会分布在很多的grandule 里面，也就可能分布在索引标记上，如下所示：

所以假如我们查询url <=w3 的数据，那么我们就可以看看相邻的两个grandule的最后一个url的值和第一个url的值；如果第一个url的值是w3, 第二个url 的值是w4，那么我们需要搜索的grandule 就只有前面的第一个grandule 和 之前的grandule 里面了，如法炮制，对着前面的所有的grandule，使用二分法，就可以在小于等于 o(logn)的时间复杂度找到对应的grandule；而且需要加载的grandule 非常少，这个效率挺高

但是，哈哈哈，凡事都怕但是，在实际生产环境中，userID和url的基数可能都很高，那么相同的userID 值可能就不会分布在多个表行和grandule 中，所以索引文件中的url 值就不太可能单调递增，如下所示：

这个时候使用排除搜索法估计就要全表扫描了，使用不了二分法了；这个查询效率就贼低了，所以如果我们需要提高查找效率，就要使用多个主键索引了，而不是单纯的用联合索引。

二： 使用多个主键索引

2.1: 创建另一张表

最常见的方法就是我们再建另一张表，这张表的主键索引我们设置为url，如下所示：

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;

INSERT INTO hits_URL_UserID
SELECT * from hits_UserID_URL;

OPTIMIZE TABLE hits_URL_UserID FINAL;
这个当然可以加快查询，但是如果我们的系统已经上线了的话，这个改动还要应用系统那边改，不太实用

2.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;

使用populate 的关键字是让原表的所有值钱的数据都导入这个物化视图，后续有的数据也会同步到这个物化视图；

但是这个的麻烦点也和刚刚的第一种方法一样，需要应用系统改动适配查询物化视图表，但是物化视图在做聚合方面很有用，如下所示：

–agg+物化视图本地表
–drop table if exists glab.bi_dws_lbs_adapter_agg_mv_local on cluster glab_cluster;

create materialized view glab.bi_dws_lbs_adapter_agg_mv_local on cluster glab_cluster(
id String,
idType AggregateFunction(argMax, String, UInt64 ),
geohash AggregateFunction(argMax, String, UInt64 ),
locType AggregateFunction(argMax, Int64, UInt64 ),
wifimac AggregateFunction(argMax, String, UInt64 ),
ip AggregateFunction(argMax, String, UInt64 ),
baseStation AggregateFunction(argMax, String, UInt64 ),
connectType AggregateFunction(argMax, Int64, UInt64 ),
equipmentId AggregateFunction(argMax, String, UInt64 ),
time AggregateFunction(max, UInt64 )
)
engine = AggregatingMergeTree()
partition by tuple()
order by id
as
select
id,
argMaxState ( id_type, ts ) AS idType,
argMaxState ( geohash12, ts ) AS geohash,
argMaxState ( loc_type, ts ) AS locType,
argMaxState ( wifimac, ts ) AS wifimac,
argMaxState ( ip, ts ) AS ip,
argMaxState ( base_station, ts ) AS baseStation,
argMaxState ( connect_type, ts ) AS connectType,
argMaxState ( equipment_id, ts ) AS equipmentId,
maxState( ts ) AS time
from glab.bi_dws_lbs_adapter_local
where geohash12 != ‘’ AND geohash12 IS NOT NULL
group by id;

–agg+物化视图集群表
CREATE TABLE glab.bi_dws_lbs_adapter_agg_mv on cluster glab_cluster as bi_dws_lbs_adapter_agg_mv_local
ENGINE = Distributed(‘glab_cluster’, ‘glab’, ‘bi_dws_lbs_adapter_agg_mv_local’, murmurHash3_32(id))

参考链接：https://cf.cloudglab.cn/pages/viewpage.action?pageId=226211113

2.3: 使用投影projection

上述两个方法都是需要应用系统适配，但是这个projection 就很好的解决了这个问题，BI这边改动就行，不需要应用改动，如下所示：

ALTER TABLE hits_UserID_URL
ADD PROJECTION prj_url_userid
(
SELECT *
ORDER BY (URL, UserID)
);

ALTER TABLE hits_UserID_URL
MATERIALIZE PROJECTION prj_url_userid; (使用materalize 是为了让原始的数据形成另一个索引)

所以当系统查询慢的情况下，如果就是因为联合索引的问题，我们可以使用projection，成本最小；如果需要查询诸如max(ts) 的geohash ，那就要用到物化视图了。