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 ，那就要用到物化視圖了。