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和group by job。我想问一下，在salary列上定义二级索引是否是一种好的做法。

这里我要问的基本问题是，我是否可以将Clickhouse二级指数视为MySQL正常指数。也就是说，如果我想按某一列进行过滤，那么我可以在该列上创建(二级)索引，以加快查询速度。

Answer 1

不需要，MySQL使用b-tree索引将随机寻道的复杂度降低到O(log(N))，其中N是表中的行

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

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

因此，二级索引不适用于基数较高且分区内数据部分之间没有单调分布的列

有关详情，请访问https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/mergetree/#table_engine-mergetree-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)，但在实践中可能不会比完全扫描更好，因为您遇到了磁盘查找的瓶颈。

如果您的查询中有一些稀有的值或数据中有额外的结构(与索引相关)，则跳过索引(单击二级索引)是有帮助的。例如，假设你过滤了大于200000的薪水，但99.9%的薪水低于200000-然后跳过索引告诉你，例如，下一块中的最高工资是19400，所以你不需要阅读这个块。

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