2024年8款数据库数据分析能力（TPC

作者 | 叶正盛随着数据量爆炸性的增长，数据库类产品创新层出不穷，为了更好的做技术选型参考，笔者对国内主流 8 种数据库进行了 TPC-H（数据分析）性能评测，被测试的数据库包括：MySQL、PostgreSQL、Oracle、达梦、ClickHouse、Doris、Starrocks、DuckDB。测试方法TPC-H：国际数据库标准测试组织 TPC 推出的面向决策支持系统的数据库方法，主要用于数据分析场景性能评测，TPC-H 总共需要运行 22 条数据分析型 SQL。本次测试属于 TPC-H 自由测试，仅测试查询 SQL，没有执行更新和事务。数据集：TPC-H v3.0.1 测试数据集，200GB（非报告标准 size）。计时方法：统计每条 SQL 执行时间，全部累加。设置 SQL 最长执行时间为 300 秒 (5 分钟)，如果执行出错或者超时，则本条 SQL 时间按 600 秒计算。测试硬件为了公平起见，本次 TPC-H 测试使用了完全相同的硬件，所有产品都是单机部署，重点是测试各个产品的单机处理能力。1 台阿里云 ECSi4r.4xlarge处理器：16 核 2.7 GHz 主频的 Intel (®) Xeon (®) 可扩展处理器（Ice Lake ），内存：128GB硬盘：1 块本地 Nvme SSD，空间 894GB操作系统：CentOS7.9测试结论接下来详细描述 TPC-H 测试方法和 8 种数据库的测试成绩TPC-H 数据模型简介TPC-H 主要模拟供应商和采购商之间的订单交易，模型总共 8 张表region：区域表，固定 5 条记录nation：国家表，固定 25 条记录supplier：供应商表，数据量因子 *10000 条记录customer：客户表，数据量因子 *150000 条记录part：商品表，数据量因子 *200000 条记录partsupp：供应商物件表：数据量因子 *800000 条记录orders：订单表，数据量因子 *1500000 条记录lineitem：订单明细表，数据量因子 *6000000 条记录（最大的表，占总数据率 70%）TPC-H 测试的数据量大小可以任意指定，本次测试采用了 200GB 的规模，最大的 lineitem 表数据量约 12 亿条 (200*6000000)。模型建表以下是 MySQL 语法参考，其他数据库可以根据语法修改create database tpch; use tpch; CREATE TABLE `customer` ( `c_custkey` int(11) NOT NULL, `c_name` varchar(25) NOT NULL, `c_address` varchar(40) NOT NULL, `c_nationkey` int(11) NOT NULL, `c_phone` varchar(15) NOT NULL, `c_acctbal` decimal(15,2) NOT NULL, `c_mktsegment` varchar(10) NOT NULL, `c_comment` varchar(117) NOT NULL, PRIMARY KEY (`c_custkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `lineitem` ( `l_orderkey` bigint(20) NOT NULL, `l_partkey` int(11) NOT NULL, `l_suppkey` int(11) NOT NULL, `l_linenumber` bigint(20) 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` varchar(1) NOT NULL, `l_linestatus` varchar(1) NOT NULL, `l_shipdate` date NOT NULL, `l_commitdate` date NOT NULL, `l_receiptdate` date NOT NULL, `l_shipinstruct` varchar(25) NOT NULL, `l_shipmode` varchar(10) NOT NULL, `l_comment` varchar(44) NOT NULL, PRIMARY KEY (`l_orderkey`,`l_linenumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `orders` ( `o_orderkey` bigint(20) NOT NULL, `o_custkey` int(11) NOT NULL, `o_orderstatus` varchar(1) NOT NULL, `o_totalprice` decimal(15,2) NOT NULL, `o_orderdate` date NOT NULL, `o_orderpriority` varchar(15) NOT NULL, `o_clerk` varchar(15) NOT NULL, `o_shippriority` bigint(20) NOT NULL, `o_comment` varchar(79) NOT NULL, PRIMARY KEY (`O_ORDERKEY`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `part` ( `p_partkey` int(11) NOT NULL, `p_name` varchar(55) NOT NULL, `p_mfgr` varchar(25) NOT NULL, `p_brand` varchar(10) NOT NULL, `p_type` varchar(25) NOT NULL, `p_size` int(11) NOT NULL, `p_container` varchar(10) NOT NULL, `p_retailprice` decimal(15,2) NOT NULL, `p_comment` varchar(23) NOT NULL, PRIMARY KEY (`p_partkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; CREATE TABLE `partsupp` ( `ps_partkey` int(11) NOT NULL, `ps_suppkey` int(11) NOT NULL, `ps_availqty` int(11) NOT NULL, `ps_supplycost` decimal(15,2) NOT NULL, `ps_comment` varchar(199) NOT NULL, KEY `IDX_PARTSUPP_SUPPKEY` (`PS_SUPPKEY`), PRIMARY KEY (`ps_partkey`,`ps_suppkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `supplier` ( `s_suppkey` int(11) NOT NULL, `s_name` varchar(25) NOT NULL, `s_address` varchar(40) NOT NULL, `s_nationkey` int(11) NOT NULL, `s_phone` varchar(15) NOT NULL, `s_acctbal` decimal(15,2) NOT NULL, `s_comment` varchar(101) NOT NULL, PRIMARY KEY (`s_suppkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `nation` ( `n_nationkey` int(11) NOT NULL, `n_name` varchar(25) NOT NULL, `n_regionkey` int(11) NOT NULL, `n_comment` varchar(152) DEFAULT NULL, PRIMARY KEY (`n_nationkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `region` ( `r_regionkey` int(11) NOT NULL, `r_name` varchar(25) NOT NULL, `r_comment` varchar(152) DEFAULT NULL, PRIMARY KEY (`r_regionkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;TPC-H 评测 SQL 简介TPC-H 标准共有 22 条查询 SQL，主要考验数据库的如下数据分析能力：多表 Join子查询分组汇总（Group By，SUM）过滤计算（like 模糊查询,Case when 等）视图关联查询以 TPC-H 第 21 号 SQL 举例，一条 SQL 包括了多表关联、子查询、Group by、Order By 几种数据分析中常见的运算：#TPC-H 21号SQL select s_name, count(*) as numwait from supplier, lineitem l1, orders, nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey ) and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate ) and s_nationkey = n_nationkey and n_name = 'RUSSIA' group by s_name order by numwait desc, s_name;tpc-h 完整的 22 条 sql 可以参考 dbgen 工具包内容，不同的数据库需要根据语法特性微调。测试数据生成TPC-H 官方提供了测试数据集构建标准程序，下载地址，下载后可以直接编译运行https://www.tpc.org/TPC_Documents_Current_Versions/download_programs/tools-download-request5.asp?bm_type=TPC-H&bm_vers=3.0.1&mode=CURRENT-ONLY配置 makefile 内容CC=gcc，DATABASE=ORACLE,MACHINE=LINUX,WORKLOAD=TPCH，具体参考如下：## CHANGE NAME OF ANSI COMPILER HERE ################ CC = gcc # Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata) # SQLSERVER, SYBASE, ORACLE, VECTORWISE # Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS, # SGI, SUN, U2200, VMS, LINUX, WIN32 # Current values for WORKLOAD are: TPCH DATABASE=ORACLE MACHINE = LINUX WORKLOAD = TPCH# 执行编译，生成dbgen可以执行文件 make接下来可以用 dbgen 生成测试数据集，dbgen 有非常多的参数，可以根据需要灵活使用，如果是 100GB 以内的小数据量，可以直接单线程一键生成# -s后面跟上数据集大小，单位是GB ./dbgen -s 100dbgen 执行完成后，在目录下会产生以下数据文件，都是 csv 格式，字段间分隔符是"|"customer.tbl nation.tbl region.tbl supplier.tbl part.tbl partsupp.tbl lineitem.tbl orders.tbl如果超过 100GB 的数据量，建议几个大表并行生成数据，尤其是订单和明细表 orders,lineitem 这个两个大表，如下：-T 表示单独生成某个表的数据，-C 表示计划分割为几个文件，-S 表示当前生成第几份数据，通过分割文件可以并行生成数据，提升效率，后面导入到数据库也可以直接并行加速。./dbgen -s 200 -T o -C 10 -S 1 & ./dbgen -s 200 -T o -C 10 -S 2 & ./dbgen -s 200 -T o -C 10 -S 3 & ./dbgen -s 200 -T o -C 10 -S 4 & ./dbgen -s 200 -T o -C 10 -S 5 & ./dbgen -s 200 -T o -C 10 -S 6 & ./dbgen -s 200 -T o -C 10 -S 7 & ./dbgen -s 200 -T o -C 10 -S 8 & ./dbgen -s 200 -T o -C 10 -S 9 & ./dbgen -s 200 -T o -C 10 -S 10 &以下是各种数据库的测试明细MySQL v8.0.32导入数据# 为了优化导入性能，可以做以下优化，都是危险操作，千万不要在生产数据库操作。1. 关闭Binlog 2. 关闭REDO_LOG 3. 关闭Double Write使用 mysql 的 load data 指令导入数据load data local infile '/data/tpch3/dbgen/customer.tbl' into table customer fields terminated by '|'; load data local infile '/data/tpch3/dbgen/lineitem.tbl' into table lineitem fields terminated by '|'; load data local infile '/data/tpch3/dbgen/nation.tbl' into table nation fields terminated by '|'; load data local infile '/data/tpch3/dbgen/orders.tbl' into table orders fields terminated by '|'; load data local infile '/data/tpch3/dbgen/partsupp.tbl' into table partsupp fields terminated by '|'; load data local infile '/data/tpch3/dbgen/part.tbl' into table part fields terminated by '|'; load data local infile '/data/tpch3/dbgen/region.tbl' into table region fields terminated by '|'; load data local infile '/data/tpch3/dbgen/supplier.tbl' into table supplier fields terminated by '|';执行 TPC-H SQL（操作省略）确实是小试牛刀，MySQL 根本不适合 TPC-H，即使设置了并行查询，但是只有在比较简单的单表 SQL 查询中可以，加上 group by 或者 join 后基本都只能是单线程操作，MySQL 最后只完成了 Q2，其他 21 条 SQL 都查询超时总结：这个成绩真惨不忍睹，如果你要把 MySQL 用于查询分析，那基本是死翘翘。PostgreSQL v15.7功能最强大的开源关系型数据库 PostgreSQL，看看是否能完成任务。PostgreSQL 可以采用 copy 导入 csv 文件，语法如下：COPY customer FROM '/data/tpch3/dbgen/customer.tbl' delimiter '|' ;需要注意的是 TPC-H dbgen 生成的 csv 格式最后多了一个"|"，PostgreSQL COPY 无法直接导入，可以在 TPCH 所有表增加一个临时的字段，或者用 shell 脚本批量把最后的"|"去除，类似下面的脚本for i in `ls *.tbl`; do sed 's/|$//' $i > ${i/tbl/csv}; done#大表设置并行 alter table LINEITEM set (parallel_workers=16); alter table ORDERS set (parallel_workers=16); alter table PARTSUPP set (parallel_workers=16); alter table PART set (parallel_workers=16); #关闭表autovacuum ALTER TABLE LINEITEM SET (autovacuum_enabled = off); ALTER TABLE ORDERS SET (autovacuum_enabled = off); ALTER TABLE PARTSUPP SET (autovacuum_enabled = off); ALTER TABLE PART SET (autovacuum_enabled = off); ALTER TABLE CUSTOMER SET (autovacuum_enabled = off); ALTER TABLE SUPPLIER SET (autovacuum_enabled = off);执行结果总结：22 条 SQL，PostgreSQL 执行成功 19 条，有 3 条 SQL 执行超时，比 MySQL 好了很多，对于一些 HTAP 场景，可以满足基本需求，超时的 3 条 SQL 都是有 3 张以上表的 Join，PostgreSQL 在并行查询性能方面算法还有待提升。Oracle19c老牌商业数据库，本次测试的是普通 Oracle19c，没有使用 Exadata，因此只使用行存模式。#软件从官方下载 https://www.oracle.com/database/technologies/oracle-database-software-downloads.html #安装手册 https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/running-rpm-packages-to-install-oracle-database.html#设置表不记录log，可以加速导入 alter table lineitem NOLOGGING; alter table partsupp NOLOGGING; alter table orders NOLOGGING; alter table part NOLOGGING;导入 csv 数据可以使用 Oracle 自带的 sqlldr 工具sqlldr userid=test/test@ORCLPDB1 control=load_tpch_customer.ctl rows=1000000 bindsize=20971520 direct=trueload_tpch_customer.ctl 文件示例LOAD DATA INFILE 'customer.tbl' INTO TABLE customer TRAILING NULLCOLS ( C_CUSTKEY terminated by '|', C_NAME terminated by '|', C_ADDRESS terminated by '|', C_NATIONKEY terminated by '|', C_PHONE terminated by '|', C_ACCTBAL terminated by '|', C_MKTSEGMENT terminated by '|', C_COMMENT terminated by '|' )#设置大表开启并行查询 alter table LINEITEM parallel(degree 16); alter table PART parallel(degree 4); alter table CUSTOMER parallel(degree 4); alter table ORDERS parallel(degree 4); alter table PARTSUPP parallel(degree 4); #收集表统计信息，数据导入完毕后执行 EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'TEST',tabname=> 'LINEITEM',estimate_percent=>0.1,block_sample=> TRUE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO'); EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'TEST',tabname=> 'PART',estimate_percent=>1,block_sample=> TRUE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO'); EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'TEST',tabname=> 'SUPPLIER',estimate_percent=>1,block_sample=> TRUE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO'); EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'TEST',tabname=> 'PARTSUPP',estimate_percent=>1,block_sample=> TRUE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO'); EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'TEST',tabname=> 'CUSTOMER',estimate_percent=>1,block_sample=> TRUE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO'); EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'TEST',tabname=> 'ORDERS',estimate_percent=>1,block_sample=> TRUE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO'); EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'TEST',tabname=> 'NATION',estimate_percent=>1,block_sample=> TRUE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO'); EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> 'TEST',tabname=> 'REGION',estimate_percent=>1,block_sample=> TRUE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO');执行结果总结：Oracle19c 完成了所有任务，性能中等，Join 表现很稳定，不会出现内存不足的现象。由于 Oracle 列存压缩仅在 Exadata 支持，本次使用的是行存模式，并不能展现出 Oracle 的最强能力。Oracle 在大表扫描方面使用并行全表查询，硬盘性能大约 3GB/s，因此需要全表访问大表 lineitem（140GB）的任务，基本都是 50 秒以上。达梦 8 v20240408达梦是老牌国产数据库，超过 20 年历史，以 Oracle 兼容性著称达梦 8 安装安装比较方便，可以在官网直接下（https://www.dameng.com），根据安装文档一步一步安装即可可以自带的 dmfldr 工具导入 csv 数据，语法和 Oracle 的 sqlldr 类似./dmfldr userid=TEST/Dameng_123:5236 control=\'load_tpch_customer.ctl\' direct=true#load_tpch_customer.ctl 文件示例LOAD DATA INFILE 'customer.tbl' INTO TABLE customer ( C_CUSTKEY terminated by '|', C_NAME terminated by '|', C_ADDRESS terminated by '|', C_NATIONKEY terminated by '|', C_PHONE terminated by '|', C_ACCTBAL terminated by '|', C_MKTSEGMENT terminated by '|', C_COMMENT terminated by '|' )#设置并行 SP_SET_PARA_VALUE (1,'MAX_PARALLEL_DEGREE',16);执行结果总结：22 条 SQL，达梦执行成功 11 条，另外 11 条 SQL 执行超时或出错，超时或出差的 11 条 SQL 都是有 3 张以上表的 Join，达梦在 Hash Join 的内存管理方面欠佳，即使设置了内存大小，但还是很容易报全局内存不足。ClickHouse v2024.04ClickHouse 是非常流行的开源列式数据仓库，由俄罗斯 Yandex 公司研发，目前是 ClickHouse 公司在推动发展。ClickHouse 以惊人的单表查询能力著称，很好的弥补了大数据平台 OLAP 交互式分析能力。由于 ClickHouse 并不能执行标准 TPC-H 的 SQL 语法，所以需要在查询语法上做一些修改，具体参考如下文章https://www.cnblogs.com/syw20170419/p/16421131.htmlClickHouse 数据导入非常简单，可以直接使用 Insert into table from infile 语法，如下所示SET format_csv_delimiter = '|'； INSERT INTO customer from infile '/data/tpch3/dbgen/customer.tbl' FORMAT CSV;执行结果：总结：ClickHouse 数据导入非常快，单线程可以到达 300MB/s，总共完成 20 个任务，2 个任务失败，单表查询表现出了优秀的性能力，两表 Join 以上性能急剧下滑，复杂查询容易出现内存不足而运行出错。ClickHouse 有众多的使用者，遇到普通问题在网上都可以找到解决方案。Doris 2.1.2Apache Doris 是一款基于 MPP 架构的高性能、实时的分析型数据库。Apache Doris 最早是诞生于百度广告报表业务的 Palo 项目，2017 年正式对外开源，2018 年 7 月由百度捐赠给 Apache 基金会进行孵化，之后在 Apache 导师的指导下由孵化器项目管理委员会成员进行孵化和运营。2022 年 6 月，Apache Doris 成功从 Apache 孵化器毕业，正式成为 Apache 顶级项目（Top-Level Project，TLP）。目前 Apache Doris 社区已经聚集了来自不同行业数百家企业的 600 余位贡献者，并且每月活跃贡献者人数也超过 120 位。Doris 采用 MySQL 协议，高度兼容 MySQL 语法，整体上是 FE（前端节点）+BE（后端计算与数据存储节点）的 MPP 架构，如下图所示。doris 可以使用 stream load 的模式导入 csv 数据，性能可以达到 200MB/scurl --location-trusted -u admin:admin -T /data/tpch3/dbgen/customer.tbl -H "column_separator:|" -H "columns: c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment, temp" :8030/api/tpch200/customer/_stream_load #如果导入的csv文件很大，可以先设置最大导入的文件大小 curl -X POST :8030/api/update_config?streaming_load_max_mb=10240执行结果：总结：Doris 数据导入非常快，并且轻松完成了所有任务，本次测试内存 128GB，Doris 导入列式压缩后实际空间大约 60GB，因此大部分请求都是在内存中可以计算完成，如果数据量更大，Doris 可能会发生内存不足的现象。StarrocksStarrocks 与 Doris 在早期是同一个内核引擎，也是 FE+BE 的架构，现在已经是独立开源产品演进。Starrocks 最新版本支持存储计算完全分离的架构，可以把数据存储在 S3、HDFS、MinIO 等共享对象存储上，通过增加无数据的 CN 节点完成计算扩展，如下图所示。本次测试采用单机 FE+BE 架构部署starrocks 操作与 doris 基本一致，可以使用 stream load 的模式导入 csv 数据，性能可以达到 200MB/scurl --location-trusted -u admin:admin -T /data/tpch3/dbgen/customer.tbl -H "column_separator:|" -H "columns: c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment, temp" :8030/api/tpch200/customer/_stream_load执行结果：总结：Starrocks 数据导入非常快，并且轻松完成了所有任务。DuckDB（0.10.2）DuckDB 是 2019 年荷兰人发布的一个高性能的嵌入式开源分析性数据，它的定位类似面向 OLAP 的 SQLite，非常轻巧，不需要安装，可以嵌入到多种语言中。比如在 java 中，只需要引入 duckdb 的 jar 包，就可以在代码里创建一个本地文件存储的 duckdb 数据仓库。DuckDB 性能非常出色，本次测试中表现非常亮眼，一个仅 15MB 的小型嵌入式数据库达到了大型 MPP 系统的性能。duckdb 在 tpc-h 和 tpc-ds 方面都附加的插件，可以直接使用，非常方便https://duckdb.org/docs/extensions/tpch#安装tpch插件 INSTALL tpch; #加载插件 LOAD tpch; #生成200GB数据 CALL dbgen(sf = 200); #运行具体的TPC-H任务 PRAGMA tpch(1); PRAGMA tpch(2); ... PRAGMA tpch(22);使用默认的 tpc-h 生成数据比较慢，也可以使用 COPY 语法导入使用 TPC-H 官方工具生成的数据，不需要知道分隔符，duckdb 默认会自动识别 CSV 的格式，非常方便。COPY customer FROM '/data/tpch3/dbgen/customer.tbl';执行结果：总结：整体表现非常优秀，轻松完成了所有任务，作为一个后起新秀，嵌入式的 OLAP 数据库，表现非常出色，如果数据库可以在单机内管理，duckdb 是非常有竞争力的产品。总 结从测试结果看：Starrocks、Doris、DuckDB 是目前 OLAP 引擎中第一梯队，可以轻松处理 TPC-H 的任务。ClickHouse 是非常流行的开源数据仓库，在单表查询方面比较强，但是多表关联计算能力容易较弱，并且容易发生内存不足现象。MySQL、PostgreSQL、达梦数据库主要还是面向 OLTP 场景，在本次 TPC-H 测试中表现欠佳。Oracle 作为老牌数据库，测试中表现非常稳定，数据量在增加也不会出现内存不足的现象，但是由于行式存储引擎的缺陷，在性能上与列式存储引擎有巨大的差距，更适合复杂的 HTAP 场景。为了公平起见，本次 TPC-H 测试使用了完全相同的硬件，所有产品都是单机部署，重点是测试各个产品的单机处理能力，没有测试 Starrocks、Doris、ClickHouse 这些 MPP 产品的分布式处理能力。计划在未来的工作中继续完善。关于作者叶正盛，NineData 创始人 &CEO，资深数据库专家，原阿里云数据库产品管理与解决方案部总经理。NineData（www.ninedata.cloud）是云原生数据管理平台，提供数据库 DevOps（SQL IDE、SQL 审核与发布、性能优化、数据安全管控）、数据复制（迁移、同步、ETL）、备份等功能，可以帮助用户更安全、高效使用数据。