投影
投影以优化查询执行的方式存储数据,此功能对于以下情况很有用:
- 对不是主键一部分的列运行查询
- 预先聚合列,这将减少计算和 IO
您可以为表定义一个或多个投影,在查询分析期间,ClickHouse 会选择要扫描数据最少的投影,而无需修改用户提供的查询。
投影将在内部创建一个新的隐藏表,这意味着需要更多 IO 和磁盘空间。例如,如果投影定义了不同的主键,则原始表中的所有数据都将被复制。
您可以在此页面上查看有关投影在内部工作方式的更多技术细节:页面。
不使用主键的示例过滤
创建表
CREATE TABLE visits_order
(
`user_id` UInt64,
`user_name` String,
`pages_visited` Nullable(Float64),
`user_agent` String
)
ENGINE = MergeTree()
PRIMARY KEY user_agent
使用 ALTER TABLE
,我们可以将投影添加到现有表中
ALTER TABLE visits_order ADD PROJECTION user_name_projection (
SELECT
*
ORDER BY user_name
)
ALTER TABLE visits_order MATERIALIZE PROJECTION user_name_projection
插入数据
INSERT INTO visits_order SELECT
number,
'test',
1.5 * (number / 2),
'Android'
FROM numbers(1, 100);
即使在原始表中 user_name
未定义为 PRIMARY_KEY
,投影也将允许我们快速按 user_name
过滤。在查询时,ClickHouse 确定如果使用投影,将处理更少的数据,因为数据按 user_name
排序。
SELECT
*
FROM visits_order
WHERE user_name='test'
LIMIT 2
为了验证查询是否正在使用投影,我们可以查看 system.query_log
表。在 projections
字段中,我们有使用的投影名称,如果没有使用,则为空。
SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'
示例预聚合查询
使用投影创建表
CREATE TABLE visits
(
`user_id` UInt64,
`user_name` String,
`pages_visited` Nullable(Float64),
`user_agent` String,
PROJECTION projection_visits_by_user
(
SELECT
user_agent,
sum(pages_visited)
GROUP BY user_id, user_agent
)
)
ENGINE = MergeTree()
ORDER BY user_agent
插入数据
INSERT INTO visits SELECT
number,
'test',
1.5 * (number / 2),
'Android'
FROM numbers(1, 100);
INSERT INTO visits SELECT
number,
'test',
1. * (number / 2),
'IOS'
FROM numbers(100, 500);
我们将使用 GROUP BY
字段 user_agent
执行第一个查询,此查询不会使用定义的投影,因为预聚合不匹配。
SELECT
user_agent,
count(DISTINCT user_id)
FROM visits
GROUP BY user_agent
为了使用投影,我们可以执行选择部分或全部预聚合和 GROUP BY
字段的查询。
SELECT
user_agent
FROM visits
WHERE user_id > 50 AND user_id < 150
GROUP BY user_agent
SELECT
user_agent,
sum(pages_visited)
FROM visits
GROUP BY user_agent
如前所述,我们可以查看 system.query_log
表。在 projections
字段中,我们有使用的投影名称,如果没有使用,则为空。
SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'
操作投影
以下是可用于操作投影的命令
ADD PROJECTION
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY] )
- 将投影描述添加到表的元数据。
DROP PROJECTION
ALTER TABLE [db.]name [ON CLUSTER cluster] DROP PROJECTION [IF EXISTS] name
- 从表的元数据中删除投影描述,并将投影文件从磁盘中删除。实现为变异。
MATERIALIZE PROJECTION
ALTER TABLE [db.]table [ON CLUSTER cluster] MATERIALIZE PROJECTION [IF EXISTS] name [IN PARTITION partition_name]
- 查询将重新构建分区 partition_name
中的投影 name
。实现为变异。
CLEAR PROJECTION
ALTER TABLE [db.]table [ON CLUSTER cluster] CLEAR PROJECTION [IF EXISTS] name [IN PARTITION partition_name]
- 从磁盘删除投影文件,但不删除描述。实现为变异。
ADD
、DROP
和 CLEAR
命令在某种程度上是轻量级的,因为它们只更改元数据或删除文件。
此外,它们是复制的,通过 ClickHouse Keeper 或 ZooKeeper 同步投影元数据。
仅支持使用*MergeTree
引擎(包括复制变体)的表的操作投影。