ClickHouse Join Order BenchmarkJOB实践指南基于 IMDB 数据集的查询优化器压力测试【免费下载链接】ClickHouseClickHouse® is a real-time analytics database management system项目地址: https://gitcode.com/GitHub_Trending/cli/ClickHouseJoin Order BenchmarkJOB是数据库查询优化器领域的经典压力测试基准它由 113 条分析型查询构成全部运行在一个真实世界、高度相关的数据集IMDB 电影数据库快照之上。本文将以 ClickHouse 仓库中的 tests/benchmarks/job/README.md 为核心结合仓库内的建表脚本、CSV 转换工具、查询样例与CREATE TABLE源码实现完整讲解如何在 ClickHouse 中加载 JOB 数据、规避空结果与 NULL 兼容性问题并正确复现这一基准。读完本文你将掌握 JOB 在 ClickHouse 下的完整落地路径与关键坑点。一、什么是 Join Order BenchmarkJOBJoin Order Benchmark 的核心目标是用真实的高相关数据集“折磨”查询优化器它取自 Leis 等人在 VLDB 2015 发表的论文How Good Are Query Optimizers, Really?中提出的基准全部查询围绕 IMDB 快照构建表之间存在大量 join 关系与列相关性因此 join 顺序的选择会显著影响执行性能。ClickHouse 仓库将其纳入 tests/benchmarks 目录与 TPC-H、TPC-DS 并列用于评估和驱动自身查询优化器的演进。在 tests/benchmarks/README.md 中每个基准子目录都遵循统一的结构约定文件 / 目录说明init.sql建表语句CREATE TABLE 定义settings.json运行查询时应使用的 ClickHouse 设置用于保持 SQL 标准兼容queries/查询文件对应到 JOB 子目录即 tests/benchmarks/job/init.sql、tests/benchmarks/job/settings.json 与 tests/benchmarks/job/queries后者内含 113 个查询文件从1a.sql一直到33c.sql。JOB 查询按主题分组命名例如1a.sql、2c.sql、32a.sql等每个查询都带有明确的筛选条件与多表 join 结构是对优化器代价模型、join 重排与执行引擎的综合检验。二、加载数据schema 与 NULL 语义2.1 init.sql 是原始 schematests/benchmarks/job/init.sql 是 JOB 基准的原始、未修改schema包含 21 张 IMDB 表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。文件中的列定义是 PostgreSQL 风格CREATE TABLE cast_info ( id integer NOT NULL PRIMARY KEY, person_id integer NOT NULL, movie_id integer NOT NULL, person_role_id integer, note text, nr_order integer, role_id integer NOT NULL );关键点在于这些列除非显式声明NOT NULL否则都是可空nullable的。例如person_role_id、note、nr_order没有NOT NULL约束意味着它们可能包含 NULL 值。2.2 为什么必须设置 data_type_default_nullable1ClickHouse 的默认行为与 PostgreSQL 相反在没有显式说明时列默认为非空。因此直接执行init.sql会导致原本可空的列被建成非空列而 IMDB 数据行中包含真实 NULL 值加载时就会失败。解决办法是传入data_type_default_nullable1设置让所有未显式标注NOT NULL的列自动建为Nullable类型。README 给出的建表命令为clickhouse client --data_type_default_nullable1 --queries-file init.sql该设置同样固化在基准自带的 tests/benchmarks/job/settings.json 中作为运行 JOB 查询时推荐的统一配置{ settings: { data_type_default_nullable: 1 } }从源码层面看这一设置由CREATE TABLE的执行器直接消费。在 src/Interpreters/InterpreterCreateQuery.cpp 中构建列类型时会读取该设置bool make_columns_nullable mode LoadingStrictnessLevel::SECONDARY_CREATE !already_normalized_on_initiator !is_restore_from_backup context_-getSettingsRef()[Setting::data_type_default_nullable];即当data_type_default_nullable为真时CREATE TABLE解析出的每一列都会通过getColumnType(...)被包装为 Nullable 类型除非列声明中明确写了NOT NULL。这解释了为什么init.sql中那些未标注NOT NULL的列能够在 ClickHouse 中正确保存 NULL并与 IMDB 数据一致。注意源码中的条件也提示了该设置的生效边界它在加载/主创建路径SECONDARY_CREATE之前的加载严格度级别生效且跳过已规范化与备份恢复等内部场景。2.3 ClickHouse Cloud使用 init_cloud.sql在 ClickHouse Cloud 上README 建议改用 tests/benchmarks/job/init_cloud.sql 而非init.sql。该文件是同一 schema 的“显式 ClickHouse 类型翻译版”用于绕开 cloud 共享 catalog 中的一个已知问题上游 issue 编号为 bug-97287。与原始init.sql相比init_cloud.sql做了两类关键翻译类型映射PostgreSQL 类型显式映射为 ClickHouse 类型——integer NOT NULL→Int32可空integer→Nullable(Int32)text/character varying→String可空列则Nullable(String)。存储引擎与排序键每张表统一使用ENGINE MergeTree ORDER BY id把原始 schema 中的id integer NOT NULL PRIMARY KEY对应为 ClickHouse 的排序键sorting keyORDER BY id这是 ClickHouse 主键语义稀疏索引、用于裁剪与 PostgreSQL 主键语义的自然对应。例如cast_info在init_cloud.sql中表现为CREATE TABLE cast_info ( id Int32, person_id Int32, movie_id Int32, person_role_id Nullable(Int32), note Nullable(String), nr_order Nullable(Int32), role_id Int32) ENGINE MergeTree ORDER BY id;2.4 数据文件的预处理convert_csv.pyJOB 的 IMDB 数据集以 PostgreSQL COPY 导出的 CSV 形式提供。该格式与 ClickHouse 的 CSV 解析器存在一个微妙差异Postgres 的 CSV 只在带引号的字段内部把反斜杠当作转义字符例如5 9\解析为5 9在引号之外反斜杠是普通字面字符例如未加引号的值可能在分隔符前以\结尾。Python 标准库csv模块会在所有位置应用escapechar会破坏后一种情况因此 ClickHouse 仓库专门提供了 tests/benchmarks/job/convert_csv.py 作为预处理工具它用一个小型状态机按 Postgres 语义逐字段解析并重新输出为标准双引号转义doubled-quote的 RFC 4180 CSVClickHouse 才能正确解析。该脚本的工程细节非常值得借鉴空字段保留为空的处理未加引号的空字段被保留为空字符串ClickHouse 对Nullable列会自动映射为 NULL——这与init.sql/init_cloud.sql的可空列设计正好闭环。快速路径优化对于整行既不包含也不包含\的物理行它必然不含有未闭合引号或转义属于合法 CSV脚本会原样透传而不做任何解析显著加速海量纯文本行的处理。跨行字段状态解析器支持引号字段跨越多行记录未完整时返回等待更多输入并在输入结束时检测“未终止的引号字段或尾部转义”向 stderr 报错并返回退出码 1防止静默截断。使用方式为标准的管道式文本处理cat imdb.csv | python3 convert_csv.py imdb_rfc4180.csv之后即可用 ClickHouse 的INSERT ... FROM INFILE或clickhouse-client --query装载转换后的 CSV。三、运行 JOB 查询数据加载完成后即可逐个执行 tests/benchmarks/job/queries 中的查询文件。每个文件是一条完整的分析查询例如1a.sql查询多部影片的制片注记、片名与年份涉及company_type、info_type、movie_companies、movie_info_idx、title五表 join并带有LIKE模糊匹配与多条件过滤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;而2c.sql则在company_name、keyword、movie_companies、movie_keyword、title五张表上通过等值连接与字符串过滤如cn.country_code [sm]、k.keyword character-name-in-title做聚合。可以看到JOB 查询大量使用“星型链式”的混合 join 模式表与表之间存在高度相关的选择谓词这正是对优化器 join 顺序枚举能力与代价估计精度的严苛检验。运行单个查询可使用 clickhouse-clientclickhouse client --data_type_default_nullable1 --queries-file queries/1a.sql结合 tests/benchmarks/job/settings.json 中的data_type_default_nullable1设置可以保证查询环境与建表环境一致。若需要批量评估全部 113 个查询可以将queries/目录中的文件按需拼接例如cat queries/*.sql | clickhouse client --multiquery并配合 ClickHouse 的clickhouse-benchmark参见 programs/benchmark进行时间与吞吐统计。四、已知问题清单Known ProblemsREADME 明确记录了两类已知问题在复现基准时务必留意ClickHouse Cloud 必须使用init_cloud.sql由于 cloud 共享 catalog 存在已知缺陷上游 issue bug-97287在 Cloud 上直接执行init.sql会失败。init_cloud.sql将同一 schema 翻译为显式 ClickHouse 类型Int32/Nullable(...)/String统一MergeTree ORDER BY id以规避该问题详见 tests/benchmarks/job/init_cloud.sql。自建部署不受影响可继续使用原始init.sql并依赖data_type_default_nullable1。部分原始查询返回空结果113 个查询中有 5 个——2c、5a、5b、10b、32a——在 IMDB 数据快照上会返回空结果这是预期行为并非 ClickHouse 缺陷。原因在于 JOB 数据集与原始查询之间存在数据不匹配问题可复现性问题详见上游 gregrahn/join-order-benchmark 仓库的 issue #11。因此在统计基准结果时建议将这 5 条查询单独标记或排除避免将其计入执行计划质量评估例如2c.sql中的过滤条件k.keyword character-name-in-title与cn.country_code [sm]在当前数据快照上没有同时满足的记录因而结果为空。五、在 ClickHouse 中复现 JOB 的完整流程小结准备 IMDB/JOB 数据集文件来自 Join Order Benchmark 仓库的 IMDB Data Set。用 tests/benchmarks/job/convert_csv.py 将 Postgres-COPY CSV 预处理为 RFC 4180 标准 CSV。选择建表脚本本地/自建 ClickHouse 用 tests/benchmarks/job/init.sql并配合--data_type_default_nullable1ClickHouse Cloud 直接用 tests/benchmarks/job/init_cloud.sql。导入数据确保可空列正确接收 NULL。逐个或批量执行 tests/benchmarks/job/queries 中的 113 条查询评估 join 顺序与执行计划注意2c、5a、5b、10b、32a预期返回空结果。通过上述步骤你可以在 ClickHouse 上完整复现这一经典的查询优化器压力测试并借助 tests/benchmarks/README.md 中约定的init.sqlsettings.jsonqueries/结构将其扩展为项目内统一的 SQL 基准目录。JOB 的全部核心资源——原始与云端两种 schema、113 条查询、预处理脚本与推荐设置——都已固化在 ClickHouse 仓库的 tests/benchmarks/job 目录下可直接对照使用。【免费下载链接】ClickHouseClickHouse® is a real-time analytics database management system项目地址: https://gitcode.com/GitHub_Trending/cli/ClickHouse创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考