Join Order Benchmark (JOB)

Join Order Benchmark (JOB) 通过对一个真实世界的高相关性数据集 (IMDb 的一个快照) 运行 113 个分析查询，来考验查询优化器。自推出以来，JOB 基准测试已成为评估关系型数据库查询优化器性能的事实标准，其中包括基数估计和 JOIN 顺序优化。与假设数据均匀分布且彼此独立的合成基准测试不同，JOB 使用带有数据倾斜和相关性的真实数据，因此对 JOIN 顺序和基数估计而言是一个颇具挑战性的测试。

该数据集包含 21 个表，共约 7400 万行；在 ClickHouse 中压缩后约占 1.15 GiB。

这 113 个查询被分为 33 个家族 (1–33) 。同一家族中的查询 (a、b、c、…) 具有相同的 JOIN 图，但选择谓词不同。

参考资料

• How Good Are Query Optimizers, Really? (Leis 等，VLDB 2015)
• Join Order Benchmark 代码仓库

JOB 数据集是 IMDb 的一个快照，包含 21 张表。表定义可在 ClickHouse 软件源中的 init_cloud.sql 中查看。

每张表都使用 MergeTree 引擎，并按主键列 id 排序，这与原始 PostgreSQL schema 保持一致，即每张表都声明了 id integer NOT NULL PRIMARY KEY。PostgreSQL 中可为空的列会映射为 Nullable(...) 类型。

创建这些表：

curl -O https://raw.githubusercontent.com/ClickHouse/ClickHouse/master/tests/benchmarks/job/init_cloud.sql
clickhouse client --query "CREATE DATABASE IF NOT EXISTS job"
clickhouse client --database job --queries-file init_cloud.sql

数据来自 JOB 使用的原始 IMDb 快照，按每个表一个 CSV 文件 (aka_name.csv、title.csv 等) 的形式分发。这些 CSV 使用带有 ESCAPE '\' 的 PostgreSQL COPY 语义：反斜杠仅在带引号的字段内用于转义引号字符；在引号外，反斜杠则是字面字符。ClickHouse 需要 RFC 4180 格式的 CSV (引号成对重复，不使用反斜杠转义) ，因此必须先对这些文件重新编码。

convert_csv.py 用于执行该重新编码。它从 stdin 读取原始 CSV，并将标准 CSV 写入 stdout，将字段中嵌入的引号重复一遍，同时保留未加引号的空字段 (ClickHouse 会将其映射为 Nullable 列中的 NULL) 。

要根据原始 CSV 构建这些表：

• 创建这些表 (见上文) 。
• 按照代码仓库中的说明，将 IMDb 数据集下载为 imdb.tgz 文件。
• 转换并导入数据：

set -euo pipefail

for table in aka_name aka_title cast_info char_name comp_cast_type company_name \
             company_type complete_cast info_type keyword kind_type link_type \
             movie_companies movie_info movie_info_idx movie_keyword movie_link \
             name person_info role_type title; do
    echo "Loading ${table} ..."
    python3 convert_csv.py < "${table}.csv" > "${table}.clean.csv"
    clickhouse client --database job --query "INSERT INTO ${table} FORMAT CSV" < "${table}.clean.csv"
done

这些表填充完数据后，可将其导出为 Parquet，以便后续更快地重新导入，例如： clickhouse client --database job --query "SELECT * FROM title ORDER BY id FORMAT Parquet" > title.parquet.

各表的详细大小：

(ClickHouse 中的压缩后大小取自 system.tables.total_bytes，并基于上述表定义。)

这 113 个 JOB 查询可在 ClickHouse 软件源的此处找到。 运行这些查询所用的设置位于 settings.json 中。 有关已知问题以及针对特定查询的说明，请参阅 README。

这些查询会按名称引用表，因此请针对 job database 运行它们 (例如，使用 clickhouse client --database job) 。

示例查询 (1a) ：

SELECT MIN(mc.note) AS production_note,
       MIN(t.title) AS movie_title,
       MIN(t.production_year) AS movie_year
FROM company_type AS ct,
     info_type AS it,
     movie_companies AS mc,
     movie_info_idx AS mi_idx,
     title AS t
WHERE ct.kind = 'production companies'
  AND it.info = 'top 250 rank'
  AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
  AND (mc.note LIKE '%(co-production)%'
       OR mc.note LIKE '%(presents)%')
  AND ct.id = mc.company_type_id
  AND t.id = mc.movie_id
  AND t.id = mi_idx.movie_id
  AND mc.movie_id = mi_idx.movie_id
  AND it.id = mi_idx.info_type_id;

ClickHouse 会跟踪每个已发布版本中 JOB 查询的性能。您可以在 ClickHouse 版本基准测试 页面查看全部 113 个 JOB 查询的运行时间，了解性能随时间推移的变化。