Clickhouse二级索引与MySQL普通索引相似吗

Question

我对何时使用辅助索引有点困惑。我有下面的代码脚本来定义MergeTree表，该表有十亿行。

create table t_mt(
id UInt8,
name String,
job String,
birthday Date,
salary UINT8
) engine = MergeTable
primary key id
order by (id)

我会实时运行以下聚合查询：

select job, count(1), avg(salary) 
from t_mt 
group by job 
where salary > 20000

在上面的查询中，我使用了条件过滤器：salary > 20000和按job分组。我想问一下，在salary列上定义辅助索引是否是一种好的做法。

我在这里要问的基本问题是，我是否可以将Clickhouse二级索引视为MySQL普通索引。也就是说，如果我想按某个列进行筛选，那么我可以在该列上创建(辅助(索引，以加快查询速度。

Answer 1

否，MySQL使用b-tree索引，这将随机搜索的复杂性降低到O(log(N))，其中N是表中的行

Clickhouse二级索引使用了另一种方法，它是一种数据跳过索引

当你尝试执行像SELECT ... WHERE field [operation] values这样的查询时，它包含来自二级索引的字段，并且二级索引支持将operation应用于field的比较，clickhouse会读取二级索引颗粒，并尝试快速检查数据部分是否会跳过搜索值，如果没有，则clickhouse将从数据部分读取整列颗粒

因此，辅助索引不适用于分区内数据部分之间没有单调分布的具有高基数的列

关注https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetre-data_skipping-indexes获取详细信息

Answer 2

clickhouse中的这种行为可以通过使用按(salary，id(排序的物化视图(当您将行写入原始表时，它将自动填充(来有效地实现。按薪资查询要比跳过索引快得多。

create materialized  view t_mt_by_salary partition by toYear(birthday) order by (salary, id)
populate  as select  id, name , job , birthday , salary from t_mt;
select * from t_mt_by_salary where salary > 20000

拥有MySQL类型的二级索引是没有意义的，因为像clickhouse这样的柱状OLAP在这些类型的查询中比MySQL快得多。从理论上讲，加载二级索引和进行查找可以解决O(N log N(的复杂性，但在实际操作中，当您遇到磁盘查找的瓶颈时，可能不会比完全扫描更好。

如果您的查询中有一些罕见的值或数据中有额外的结构(与索引的相关性(，则跳过索引(clickhouse辅助索引(会有所帮助。例如，假设您筛选薪资>200000，但99.9%的工资低于200000-然后跳过指数告诉你，例如，下一个区块的最高工资是19400，所以你不需要阅读这个区块。

另一方面，如果你需要加载大约5%的数据，随机分布在8000行颗粒(块(中，那么你可能需要扫描几乎所有的颗粒。但您仍然可以使用按薪资排序的物化视图进行非常快速的查询。