使用 ClickHouse 的合并请求分析
合并请求分析功能 显示了项目中已合并的合并请求的统计数据，并暴露了记录级别的元数据。 聚合包括：
- 平均合并时间：从创建时间到合并时间的持续时间。
- 月度聚合：12 个月已合并合并请求的图表。
图表下方，用户可以看到分页的合并请求列表，每页显示 12 个月。
您可以按以下条件筛选：
- 作者
- 负责人
- 标签
- 里程碑
- 源分支
- 目标分支
当前性能问题
- 聚合查询需要专门的索引，这会占用额外的磁盘空间（仅索引扫描）。
- 查询整个 12 个月的数据很慢（语句超时）。相反，前端每月请求数据（12 个数据库查询）。
- 即使有专门的索引，由于合并请求数量庞大，在组级别启用该功能也不可行。
示例查询
获取某个月份合并的合并请求数量：
SELECT COUNT(*)
FROM "merge_requests"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE (NOT EXISTS
(SELECT 1
FROM "banned_users"
WHERE (merge_requests.author_id = banned_users.user_id)))
AND "merge_request_metrics"."target_project_id" = 278964
AND "merge_request_metrics"."merged_at" >= '2022-12-01 00:00:00'
AND "merge_request_metrics"."merged_at" <= '2023-01-01 00:00:00'merge_request_metrics 表被反规范化（通过添加 target_project_id）以提高首页加载时间。该查询本身适用于较小的日期范围，但随着日期范围的增加，可能会超时。
添加额外的筛选器后，查询变得更复杂，因为它还必须筛选 merge_requests 表：
SELECT COUNT(*)
FROM "merge_requests"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE (NOT EXISTS
(SELECT 1
FROM "banned_users"
WHERE (merge_requests.author_id = banned_users.user_id)))
AND "merge_requests"."author_id" IN
(SELECT "users"."id"
FROM "users"
WHERE (LOWER("users"."username") IN (LOWER('ahegyi'))))
AND "merge_request_metrics"."target_project_id" = 278964
AND "merge_request_metrics"."merged_at" >= '2022-12-01 00:00:00'
AND "merge_request_metrics"."merged_at" <= '2023-01-01 00:00:00'要计算平均合并时间，我们还会查询合并请求创建时间和合并时间之间的总时间。
SELECT EXTRACT(epoch
FROM SUM(AGE(merge_request_metrics.merged_at, merge_request_metrics.created_at)))
FROM "merge_requests"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE (NOT EXISTS
(SELECT 1
FROM "banned_users"
WHERE (merge_requests.author_id = banned_users.user_id)))
AND "merge_requests"."author_id" IN
(SELECT "users"."id"
FROM "users"
WHERE (LOWER("users"."username") IN (LOWER('ahegyi'))))
AND "merge_request_metrics"."target_project_id" = 278964
AND "merge_request_metrics"."merged_at" >= '2022-08-01 00:00:00'
AND "merge_request_metrics"."merged_at" <= '2022-09-01 00:00:00'
AND "merge_request_metrics"."merged_at" > "merge_request_metrics"."created_at"
LIMIT 1在 ClickHouse 中存储合并请求数据
在 ClickHouse 中存储和查询合并请求数据还有其他几种用例。在本文档中，我们专注于这个特定功能。
核心数据存在于 merge_request_metrics 和 merge_requests 数据库表中。某些筛选器需要连接额外的表：
banned_users：过滤掉被禁用用户创建的合并请求。labels：一个合并请求可以有一个或多个分配的标签。assignees：一个合并请求可以有一个或多个负责人。merged_at：merged_at列位于merge_request_metrics表中。
merge_requests 表包含可以直接筛选的数据：
- 作者：通过
author_id列。 - 里程碑：通过
milestone_id列。 - 源分支。
- 目标分支。
- 项目：通过
project_id列。
保持 ClickHouse 数据最新
不幸的是，复制或同步 merge_requests 表是不够的。需要向关联表进行单独查询，才能将一个反规范的 merge_requests 行插入到 ClickHouse 数据库中。
变更检测的实现并不简单。我们可以采取一些折中方案：
- 该功能适用于 GitLab Premium 和 GitLab Ultimate 客户。我们不必同步所有数据，而只需同步属于授权组的
merge_requests记录。 - 数据变更（通常）通过
MergeRequest服务进行，其中更新updated_at时间戳列是一致的。可以实现某种增量同步过程。 - 我们只需要查询已合并的合并请求。合并后，记录很少会更改。
数据库表结构
数据库表结构使用反规范化，使所有必需的列都存在于一个数据库表中。这消除了对 JOIN 的需求。
CREATE TABLE merge_requests
(
`id` UInt64,
`project_id` UInt64 DEFAULT 0 NOT NULL,
`author_id` UInt64 DEFAULT 0 NOT NULL,
`milestone_id` UInt64 DEFAULT 0 NOT NULL,
`label_ids` Array(UInt64) DEFAULT [] NOT NULL,
`assignee_ids` Array(UInt64) DEFAULT [] NOT NULL,
`source_branch` String DEFAULT '' NOT NULL,
`target_branch` String DEFAULT '' NOT NULL,
`merged_at` DateTime64(6, 'UTC') NOT NULL,
`created_at` DateTime64(6, 'UTC') DEFAULT now() NOT NULL,
`updated_at` DateTime64(6, 'UTC') DEFAULT now() NOT NULL
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (project_id, merged_at, id);与 活动数据示例 类似，我们使用 ReplacingMergeTree 引擎。合并请求记录的多个列可能会更改，因此保持表最新很重要。
数据库表按 project_id, merged_at, id 列排序。这种排序优化了表数据以适应我们的用例：在项目中查询 merged_at 列。
重写计数查询
首先，让我们为表生成一些数据。
INSERT INTO merge_requests (id, project_id, author_id, milestone_id, label_ids, merged_at, created_at)
SELECT id, project_id, author_id, milestone_id, label_ids, merged_at, created_at
FROM generateRandom('id UInt64, project_id UInt8, author_id UInt8, milestone_id UInt8, label_ids Array(UInt8), merged_at DateTime64(6,
UTC
), created_at DateTime64(6,
UTC
)')
LIMIT 1000000;一些整数数据类型被转换为 UInt8，因此它们在不同行中很可能具有相同的值。
原始的计数查询只聚合了一个月的数据。使用 ClickHouse，我们可以尝试聚合整年的数据。
基于 PostgreSQL 的计数查询：
SELECT COUNT(*)
FROM "merge_requests"
INNER JOIN "merge_request_metrics" ON "merge_request_metrics"."merge_request_id" = "merge_requests"."id"
WHERE (NOT EXISTS
(SELECT 1
FROM "banned_users"
WHERE (merge_requests.author_id = banned_users.user_id)))
AND "merge_request_metrics"."target_project_id" = 278964
AND "merge_request_metrics"."merged_at" >= '2022-12-01 00:00:00'
AND "merge_request_metrics"."merged_at" <= '2023-01-01 00:00:00'ClickHouse 查询：
SELECT
toYear(merged_at) AS year,
toMonth(merged_at) AS month,
COUNT(*)
FROM merge_requests
WHERE
project_id = 200
AND merged_at BETWEEN '2022-01-01 00:00:00'
AND '2023-01-01 00:00:00'
GROUP BY year, month与生成的数据相比，查询处理的行数显著减少。ORDER BY 子句（主键）有助于查询执行：
11 行在结果集中。已用时间：0.010 秒。
已处理 8.19 千行，131.07 KB（783.45 千行/秒，12.54 MB/秒）。重写平均合并时间查询
查询将平均合并时间计算为：duration(created_at, merged_at) / merge_request_count。计算分为两个单独的步骤：
- 请求月度计数和月度持续时间值。
- 对计数求和以获得年度计数。
- 对持续时间求和以获得年度持续时间。
- 将持续时间除以计数。
在 ClickHouse 中，我们可以通过一个查询计算平均合并时间：
SELECT
SUM(
dateDiff('second', merged_at, created_at) / 3600 / 24
) / COUNT(*) AS mean_time_to_merge -- mean_time_to_merge is in days
FROM merge_requests
WHERE
project_id = 200
AND merged_at BETWEEN '2022-01-01 00:00:00'
AND '2023-01-01 00:00:00'筛选
上面的数据库查询可以用作基础查询。您可以添加更多筛选器。例如，筛选标签和里程碑：
SELECT
toYear(merged_at) AS year,
toMonth(merged_at) AS month,
COUNT(*)
FROM merge_requests
WHERE
project_id = 200
AND milestone_id = 15
AND has(label_ids, 118)
AND -- array includes 118
merged_at BETWEEN '2022-01-01 00:00:00'
AND '2023-01-01 00:00:00'
GROUP BY year, month优化特定筛选器通常通过数据库索引完成。这个特定查询读取了 8000 行：
1 行在结果集中。已用时间：0.016 秒。
已处理 8.19 千行，589.99 KB（505.38 千行/秒，36.40 MB/秒）。在 milestone_id 上添加索引：
ALTER TABLE merge_requests
ADD
INDEX milestone_id_index milestone_id TYPE minmax GRANULARITY 10;
ALTER TABLE
merge_requests MATERIALIZE INDEX milestone_id_index;在生成的数据上，添加索引并没有提高性能。
被禁用用户筛选
GitLab 中最近添加的功能会过滤掉作者被管理员禁用的合并请求。被禁用的用户在实例级别通过 banned_users 数据库表进行跟踪。
方案 1：枚举被禁用用户 ID
这不需要对 ClickHouse 数据库架构进行结构更改。我们可以查询项目中的被禁用用户，并在查询时过滤掉这些值。
获取被禁用的用户（在 PostgreSQL 中）：
SELECT user_id FROM banned_users在 ClickHouse 中
SELECT
toYear(merged_at) AS year,
toMonth(merged_at) AS month,
COUNT(*)
FROM merge_requests
WHERE
author_id NOT IN (1, 2, 3, 4) AND -- banned users
project_id = 200
AND milestone_id = 15
AND has(label_ids, 118) AND -- array includes 118
merged_at BETWEEN '2022-01-01 00:00:00'
AND '2023-01-01 00:00:00'
GROUP BY year, month这种方法的问题是，被禁用用户的数量可能会显著增加，这会使查询变大且变慢。
方案 2：复制 banned_users 表
假设 banned_users 表不会增长到数百万行，我们可以尝试定期将整个表同步到 ClickHouse。通过这种方法，可以在 ClickHouse 数据库查询中使用一个基本一致的 banned_users 表：
SELECT
toYear(merged_at) AS year,
toMonth(merged_at) AS month,
COUNT(*)
FROM merge_requests
WHERE
author_id NOT IN (SELECT user_id FROM banned_users) AND
project_id = 200 AND
milestone_id = 15 AND
has(label_ids, 118) AND -- array includes 118
merged_at BETWEEN '2022-01-01 00:00:00' AND '2023-01-01 00:00:00'
GROUP BY year, month或者，可以将 banned_users 表存储为 字典 以进一步提高查询性能。
方案 3：修改功能
对于分析计算，可以接受删除这个特定筛选器。这种方法假设包含被禁用用户的合并请求不会显著影响统计数据。