简介TPC-H V3.0.1 是面向数据仓库与商业智能领域的标准化基准测试套件适合数据库内核研发、数仓性能调优及数据分析方向的工程师与研究者使用。它通过模拟真实企业级数据仓库运行环境提供一套可复现的复杂查询负载用于衡量数据库系统在数据处理与查询响应上的性能表现是选型对比、压测验证与教学实验的常用工具。资源包共约2000个文件整体约24.07MB以27个SQL查询脚本为核心辅以大量数据分片文件、subparam参数文件、sh执行脚本及少量说明文档覆盖建表、加载、查询与参数化测试的完整链路。目前已有335人学习下载。借助这套材料读者可快速搭建TPC-H测试环境对照标准查询集验证优化效果并参考参数文件理解不同规模数据下的测试组织方式为数据库性能评估与调优实践提供直接支撑。1. 拆开 TPC-H V3.0.1一份让数据库选型不再拍脑袋的基准测试资源上周有个做数仓的朋友找我吐槽说他们选型时被某数据库的跑分报告坑了——官方宣称的查询性能在实际业务里根本跑不出来。我问他用的什么基准他说是随手写的几条 SQL。这就是问题所在没有标准化的测试集性能对比就是玄学。TPC-H V3.0.1 正是解决这个问题的资源包它是一套面向决策支持系统的基准测试标准包含 22 条复杂查询和 8 张数据表的结构定义能让你在统一口径下对比不同数据仓库产品的真实表现。这份资源适合三类人正在做数据库选型的架构师、需要验证数仓性能的 DBA、以及想深入理解 SQL 优化边界的开发者。它不教你写 SQL但给你一把尺子量出每个引擎的深浅。2. TPC-H 的 22 条查询与 8 张表从数据模型到查询语义2.1 八张表的关系骨架与业务含义TPC-H 的数据模型模拟了一个全球零部件供应商的业务场景八张表分成三类。第一类是订单域orders存订单头信息lineitem存订单行明细这两张表是数据量最大的在 SF1约 1GB 数据量时lineitem大约 600 万行。第二类是供应链域part存零件信息partsupp存供应商与零件的关联及供应成本supplier存供应商信息。第三类是客户与区域域customer存客户信息nation和region是地理维度表数据量极小但参与大量关联。这八张表的关联关系不是随意设计的。lineitem通过l_orderkey关联orders通过l_partkey和l_suppkey关联partsupppartsupp再分别关联part和supplier。customer通过c_nationkey关联nationnation通过n_regionkey关联region。这种星型与雪花混合的模型恰好覆盖了数仓中最典型的关联模式事实表与维度表的多层连接。理解这个模型的意义在于当你看到某数据库在 TPC-H 上跑得快你要知道它快在哪。是lineitem和orders的大表关联做得好还是nation、region这种小表广播效率高不同引擎的优化器策略差异会在这八张表的连接顺序和连接算法选择上暴露无遗。2.2 22 条查询的语义分类与考察重点22 条查询不是随便凑数的它们按业务语义可以分成几组。定价汇总类查询Q1、Q6考察全表扫描和聚合能力Q1 对lineitem做全表扫描后按退货标志、发货日期等分组计算金额Q6 则是带过滤条件的汇总。这两条查询最能看出列式存储和向量化执行的优势。订单优先级分析类Q3、Q5、Q10、Q12、Q14、Q19涉及多表关联加过滤考察优化器的连接顺序选择。比如 Q5 要关联customer、orders、lineitem、supplier、nation、region六张表计算某区域某年的收入。优化器如果选错驱动表性能差距可能是数量级的。供应商与零件分析类Q2、Q9、Q11、Q16、Q20、Q21考察子查询和半连接的处理能力。Q21 尤其典型它要找那些在某个国家供应过零件、但从未被该国家客户下单的供应商涉及NOT EXISTS和EXISTS的嵌套对优化器的子查询展开策略是硬考验。还有一类是影响订单分析Q4、Q17、Q18、Q22考察的是条件过滤与聚合的组合。Q18 要找出订单总量超过 300 的客户先聚合再过滤对HAVING子句的执行效率有要求。注意22 条查询中有多条包含date类型的区间过滤不同数据库对日期函数的处理方式不同这往往是性能差异的隐藏来源。2.3 数据生成器的参数与规模选择TPC-H 配套的dbgen工具通过-s参数控制数据规模SF1 约 1GBSF100 约 100GBSF1000 约 1TB。选择规模时有个血泪经验不要一上来就跑 SF1000先跑 SF1 验证查询正确性再逐步放大。因为某些数据库在小数据量下优化器会选错计划但数据量上来后反而正常反之亦然。# 生成 SF10 的数据约 10GB ./dbgen -s 10 -f # 生成后检查文件 ls -lh *.tbl # 应看到 customer.tbl、lineitem.tbl、nation.tbl 等八个文件-f参数表示覆盖已有文件不加的话如果文件已存在会报错。生成后的.tbl文件是管道符分隔的文本需要导入数据库。导入时注意lineitem的日期格式是YYYY-MM-DD某些数据库需要显式指定日期格式。2.4 查询执行计划的解读方法拿到一个数据库的 TPC-H 跑分不要只看总耗时。用EXPLAIN或EXPLAIN ANALYZE看每条查询的执行计划重点关注三个点连接算法是 hash join 还是 nested loop聚合是 hash aggregate 还是 sort aggregate有没有出现意外的全表扫描。以 Q5 为例如果执行计划显示先对lineitem做全表扫描再与orders连接而orders上有年份过滤条件那说明优化器没有把过滤条件下推。这种情况下即使总耗时看起来还行也说明优化器有改进空间。反过来如果计划显示nation和region被广播到所有节点那说明小表广播策略生效了。-- 以 Q5 为例查看执行计划 EXPLAIN ANALYZE SELECT n_name, SUM(l_extendedprice * (1 - l_discount)) AS revenue FROM customer, orders, lineitem, supplier, nation, region WHERE c_custkey o_custkey AND l_orderkey o_orderkey AND l_suppkey s_suppkey AND c_nationkey s_nationkey AND s_nationkey n_nationkey AND n_regionkey r_regionkey AND r_name ASIA AND o_orderdate DATE 1994-01-01 AND o_orderdate DATE 1995-01-01 GROUP BY n_name ORDER BY revenue DESC;这条查询的关键在于r_name ASIA这个过滤条件能否尽早生效。如果优化器先做六表连接再过滤那region表虽然小但连接顺序错了会导致中间结果集膨胀。常见做法是先用region和nation过滤出亚洲国家再与supplier和customer关联最后才碰lineitem和orders。3. 跑通 TPC-H 的完整流程从建表到出报告3.1 建表脚本的适配与修改TPC-H 标准包里的dss.ddl是通用建表语句但直接拿到目标数据库上跑通常会翻车。翻车点集中在数据类型上标准用的是DECIMAL(15,2)但某些数据库对DECIMAL的精度处理不同日期类型标准写的是DATE但有的数据库需要TIMESTAMP字符类型标准是CHAR(1)有的数据库对定长字符的处理会影响存储和比较效率。我一般会先跑一遍原始 DDL看哪里报错然后逐条改。改的时候注意lineitem表的l_shipdate、l_commitdate、l_receiptdate三个日期字段经常被用来做区间过滤如果数据库支持日期分区可以考虑按l_shipdate做范围分区。orders表的o_orderdate也是高频过滤字段同样值得考虑分区。-- 以 lineitem 为例适配后的建表语句以某列式数据库为例 CREATE TABLE lineitem ( l_orderkey BIGINT NOT NULL, l_partkey BIGINT NOT NULL, l_suppkey BIGINT NOT NULL, l_linenumber INT NOT NULL, l_quantity DECIMAL(15,2) NOT NULL, l_extendedprice DECIMAL(15,2) NOT NULL, l_discount DECIMAL(15,2) NOT NULL, l_tax DECIMAL(15,2) NOT NULL, l_returnflag CHAR(1) NOT NULL, l_linestatus CHAR(1) NOT NULL, l_shipdate DATE NOT NULL, l_commitdate DATE NOT NULL, l_receiptdate DATE NOT NULL, l_shipinstruct CHAR(25) NOT NULL, l_shipmode CHAR(10) NOT NULL, l_comment VARCHAR(44) NOT NULL ) PARTITION BY RANGE (l_shipdate);分区键的选择要看查询模式。Q1 和 Q6 都带l_shipdate过滤按这个字段分区能让分区裁剪生效。但注意分区粒度不要太细按天分区在 SF100 时会产生大量小分区反而拖慢元数据管理。按月分区是常见折中。3.2 数据导入的批量优化导入.tbl文件时逐条INSERT是最慢的方式没有之一。常见做法是用数据库自带的批量导入工具比如COPY、LOAD DATA或\copy。如果数据库支持并行导入把八个文件分到不同会话同时导入能显著缩短时间。# 以 PostgreSQL 为例用 COPY 命令导入 psql -d tpch -c \COPY lineitem FROM lineitem.tbl WITH (FORMAT csv, DELIMITER |) psql -d tpch -c \COPY orders FROM orders.tbl WITH (FORMAT csv, DELIMITER |) # 其余表类似导入顺序有讲究先导小表region、nation再导supplier、customer、part最后导partsupp、orders、lineitem。这样如果外键约束存在不会因为引用缺失而报错。导入完成后跑ANALYZE或UPDATE STATISTICS收集统计信息否则优化器可能因为不知道数据分布而选错计划。提示导入前临时关闭外键约束和触发器导入后再开启能省下大量检查时间。但记得导入后要验证数据完整性。3.3 查询执行的参数调优22 条查询不是每条都跑一遍就完事。执行前要确认几个关键参数并行度设置、内存分配、临时空间。并行度太低跑不出硬件性能太高会导致资源争抢。我一般从 CPU 核数的一半开始试比如 16 核机器先设 8 并行度看 CPU 利用率再调整。内存分配要看查询的中间结果集大小。Q9 和 Q21 的中间结果可能很大如果排序或哈希聚合的内存不够会溢写到磁盘性能断崖式下跌。监控执行过程中的临时文件写入量如果发现大量磁盘临时文件说明内存不够。-- 以某数据库为例设置查询级并行度和内存 SET max_parallel_workers_per_gather 8; SET work_mem 256MB; SET maintenance_work_mem 1GB; -- 执行 Q9 并记录耗时 \timing on \i q9.sqlwork_mem不是越大越好设太大可能导致内存溢出。一般建议单条查询的work_mem不超过总内存的 25%因为一个查询可能有多个排序或哈希节点同时消耗内存。3.4 结果校验与性能指标计算跑完查询后要校验结果正确性。TPC-H 标准包里有answers目录存放了 SF1 时 22 条查询的预期结果。把实际结果与预期结果对比行数和关键聚合值要一致。如果结果不对先检查数据导入是否完整再检查查询语句是否被意外修改。性能指标有两个总耗时和单查询耗时。总耗时是所有查询耗时之和但要注意有的测试规范要求跑两遍取第二遍的时间因为第一遍可能有冷缓存影响。单查询耗时用来定位瓶颈如果某条查询特别慢单独拿出来分析执行计划。# 简单的耗时记录脚本 for i in $(seq 1 22); do start$(date %s%N) psql -d tpch -f q${i}.sql /dev/null 21 end$(date %s%N) echo Q${i}: $(( (end - start) / 1000000 )) ms done这个脚本只记录总耗时不区分解析、执行、取数阶段。更精细的做法是用数据库自带的性能视图比如pg_stat_statements或performance_schema能看到每个阶段的耗时分解。4. 避坑指南TPC-H 跑分中常见的五个翻车现场4.1 数据生成不完整导致查询结果对不上现象Q1 返回的行数比预期少或者 Q6 的汇总金额与标准答案有偏差。原因dbgen生成数据时如果中途中断.tbl文件可能不完整。或者导入时用了HEADER选项把第一行数据当成了列名。还有一种情况是lineitem表的l_linenumber字段在导入时被当成字符串处理导致排序结果异常。解决生成数据后检查每个文件的行数lineitem在 SF1 时应该是 6001215 行。导入时确认分隔符和引号处理正确导入后跑COUNT(*)验证。如果行数对不上重新生成和导入。4.2 统计信息缺失导致优化器选错计划现象同一条查询第一次跑很慢第二次跑很快或者反过来。执行计划显示用了 nested loop 而不是 hash join。原因导入数据后没有收集统计信息优化器只能凭默认值估算行数。对于lineitem这种大表默认估算可能差几个数量级导致连接顺序和连接算法选择完全错误。解决导入后立即对每张表执行ANALYZE。如果数据库支持直方图统计对l_shipdate、o_orderdate等过滤字段额外收集。统计信息收集后再跑一遍查询看计划是否改变。4.3 并行度设置不当引发资源争抢现象提高并行度后单条查询反而变慢或者多条查询并发时总耗时增加。原因并行度超过 CPU 物理核数后线程切换开销超过并行收益。或者多个查询同时跑每个都开高并行度导致内存和 IO 争抢。解决并行度从 CPU 核数的一半开始试逐步调整。跑分时一次只跑一条查询避免并发干扰。如果必须并发测试降低单查询并行度用吞吐量而非单查询延迟作为指标。4.4 日期格式与函数差异导致查询语义偏移现象Q5 和 Q6 的结果与标准答案有细微差异比如金额差几块钱。原因不同数据库对DATE类型的隐式转换规则不同。标准查询里写的是DATE 1994-01-01但有的数据库需要TO_DATE(1994-01-01, YYYY-MM-DD)。如果格式不匹配可能把日期当成字符串比较导致边界值处理错误。解决统一用显式日期函数不要依赖隐式转换。跑之前先用SELECT验证日期过滤的边界值是否正确。如果数据库的日期函数与标准 SQL 差异大改写查询时保持语义一致。4.5 缓存与预热影响跑分可比性现象同一套硬件两次跑分结果差异超过 10%。原因第一次跑分时数据还在磁盘上第二次跑分时数据已经在内存缓存里。如果两次跑分之间没有清缓存对比就没有意义。解决跑分前清空数据库缓存和操作系统缓存。Linux 下可以用echo 3 /proc/sys/vm/drop_caches清系统缓存数据库层面重启实例或执行清缓存命令。每次跑分前都做同样的预热或清缓存操作保证条件一致。5. 进阶技巧用 TPC-H 做 SQL 改写与索引验证跑通标准查询只是起点。真正有意思的是拿 TPC-H 当试验田验证你的优化思路。比如 Q1 的原始写法是对lineitem全表扫描后聚合你可以试试加一个覆盖索引看执行计划是否变成索引扫描。但要注意列式数据库里索引的效果和行式数据库完全不同别把行式数据库的经验直接搬过来。我一般会做三组对比第一组跑原始查询第二组跑改写后的查询比如把子查询改成 JOIN第三组跑加了索引或物化视图的查询。每组跑三遍取中位数避免单次波动。对比的时候不只看总耗时还要看 CPU 时间、IO 等待时间、内存峰值。-- Q1 的原始写法 SELECT l_returnflag, l_linestatus, SUM(l_quantity) AS sum_qty, SUM(l_extendedprice) AS sum_base_price, SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price, SUM(l_extendedprice * (1 - l_discount) * (1 l_tax)) AS sum_charge, AVG(l_quantity) AS avg_qty, AVG(l_extendedprice) AS avg_price, AVG(l_discount) AS avg_disc, COUNT(*) AS count_order FROM lineitem WHERE l_shipdate DATE 1998-12-01 - INTERVAL 90 DAY GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus; -- 改写版本把日期计算提前避免每行都算 WITH filtered AS ( SELECT * FROM lineitem WHERE l_shipdate DATE 1998-09-02 ) SELECT l_returnflag, l_linestatus, SUM(l_quantity) AS sum_qty, SUM(l_extendedprice) AS sum_base_price, SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price, SUM(l_extendedprice * (1 - l_discount) * (1 l_tax)) AS sum_charge, AVG(l_quantity) AS avg_qty, AVG(l_extendedprice) AS avg_price, AVG(l_discount) AS avg_disc, COUNT(*) AS count_order FROM filtered GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus;改写版本把日期计算从WHERE子句里提出来变成常量。原始写法里DATE 1998-12-01 - INTERVAL 90 DAY是常量表达式优化器通常能折叠但如果数据库的常量折叠做得不好每行都算一次就是浪费。改写后语义不变但执行计划可能更干净。验证索引效果时注意区分聚簇索引和非聚簇索引。在l_shipdate上建索引如果查询过滤条件能命中索引行式数据库可能走索引扫描但列式数据库本身按列存储索引的收益可能不明显反而增加导入和维护开销。我一般会在建索引前后各跑一遍 Q1 和 Q6看 IO 统计里的物理读次数变化。还有一个容易被忽略的点TPC-H 的 22 条查询里有几条对l_discount和l_quantity做了算术运算如果数据库支持预计算列或表达式索引可以试试把l_extendedprice * (1 - l_discount)做成物化列。但这样会改变表结构跑标准查询时要注意兼容性。从那以后我每次做数据库选型都强制走一遍 TPC-H 的完整流程生成数据、导入、收集统计、跑 22 条查询、对比执行计划、做改写验证。这套流程走下来哪个引擎在哪个环节有短板一目了然。希望帮到你。本文还有配套的精品资源点击获取