数据库性能标准测试工具-tpch

mtain 2023年01月11日 34次浏览

说明

tpch是TPC(Transaction Processing Performance Council)组织提供的工具包。用于进行OLAP测试,以评估商业分析中决策支持系统(DSS)的性能。它包含了一整套面向商业的ad-hoc查询和并发数据修改,强调测试的是数据库、平台和I/O性能,关注查询能力。

TPC-H是业界常用的一套基准,由TPC委员会制定发布,用于评测数据库的分析型查询能力。TPC-H查询包含8张数据表、22条复杂的SQL查询,大多数查询包含若干表Join、子查询和Group by聚合等。

官网:https://www.tpc.org/
Github:https://github.com/electrum/tpch-dbgen.git

安装使用

1. 编译

yum install gcc-c++
git clone https://github.com/electrum/tpch-dbgen.git
cd tpch-dbgen
make

2. 使用

# 生成数据
./dbgen -s 1

MySQL

DDL表结构

注:删除了原有的外键

CREATE TABLE NATION  ( N_NATIONKEY  INTEGER NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            N_REGIONKEY  INTEGER NOT NULL,
                            N_COMMENT    VARCHAR(152));

CREATE TABLE REGION  ( R_REGIONKEY  INTEGER NOT NULL,
                            R_NAME       CHAR(25) NOT NULL,
                            R_COMMENT    VARCHAR(152));

CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
                          P_NAME        VARCHAR(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR(25) NOT NULL,
                          P_SIZE        INTEGER NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE DECIMAL(15,2) NOT NULL,
                          P_COMMENT     VARCHAR(23) NOT NULL );

CREATE TABLE SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
                             S_NAME        CHAR(25) NOT NULL,
                             S_ADDRESS     VARCHAR(40) NOT NULL,
                             S_NATIONKEY   INTEGER NOT NULL,
                             S_PHONE       CHAR(15) NOT NULL,
                             S_ACCTBAL     DECIMAL(15,2) NOT NULL,
                             S_COMMENT     VARCHAR(101) NOT NULL);

CREATE TABLE PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL,
                             PS_SUPPKEY     INTEGER NOT NULL,
                             PS_AVAILQTY    INTEGER NOT NULL,
                             PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
                             PS_COMMENT     VARCHAR(199) NOT NULL );

CREATE TABLE CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL,
                             C_NAME        VARCHAR(25) NOT NULL,
                             C_ADDRESS     VARCHAR(40) NOT NULL,
                             C_NATIONKEY   INTEGER NOT NULL,
                             C_PHONE       CHAR(15) NOT NULL,
                             C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
                             C_MKTSEGMENT  CHAR(10) NOT NULL,
                             C_COMMENT     VARCHAR(117) NOT NULL);

CREATE TABLE ORDERS  ( O_ORDERKEY       INTEGER NOT NULL,
                           O_CUSTKEY        INTEGER NOT NULL,
                           O_ORDERSTATUS    CHAR(1) NOT NULL,
                           O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  CHAR(15) NOT NULL,  
                           O_CLERK          CHAR(15) NOT NULL, 
                           O_SHIPPRIORITY   INTEGER NOT NULL,
                           O_COMMENT        VARCHAR(79) NOT NULL);

CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER 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);




ALTER TABLE REGION
ADD PRIMARY KEY (R_REGIONKEY);


ALTER TABLE NATION
ADD PRIMARY KEY (N_NATIONKEY);


ALTER TABLE PART
ADD PRIMARY KEY (P_PARTKEY);


ALTER TABLE SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);



ALTER TABLE PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);


ALTER TABLE CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);

ALTER TABLE CUSTOMER
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references NATION(N_NATIONKEY);


ALTER TABLE LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);


ALTER TABLE ORDERS
ADD PRIMARY KEY (O_ORDERKEY);



数据导入

use tpch;
load data local infile '/data/tpch-data/nation.tbl' into table nation fields terminated by '|';
load data local infile '/data/tpch-data/customer.tbl' into table customer fields terminated by '|';
load data local infile '/data/tpch-data/lineitem.tbl.1' into table lineitem fields terminated by '|';
load data local infile '/data/tpch-data/lineitem.tbl.10' into table lineitem fields terminated by '|';
load data local infile '/data/tpch-data/lineitem.tbl.2' into table lineitem fields terminated by '|';
load data local infile '/data/tpch-data/lineitem.tbl.3' into table lineitem fields terminated by '|';
load data local infile '/data/tpch-data/lineitem.tbl.4' into table lineitem fields terminated by '|';
load data local infile '/data/tpch-data/lineitem.tbl.5' into table lineitem fields terminated by '|';
load data local infile '/data/tpch-data/lineitem.tbl.6' into table lineitem fields terminated by '|';
load data local infile '/data/tpch-data/lineitem.tbl.7' into table lineitem fields terminated by '|';
load data local infile '/data/tpch-data/lineitem.tbl.8' into table lineitem fields terminated by '|';
load data local infile '/data/tpch-data/lineitem.tbl.9' into table part fields terminated by '|';
load data local infile '/data/tpch-data/orders.tbl.1' into table orders fields terminated by '|';
load data local infile '/data/tpch-data/orders.tbl.10' into table orders fields terminated by '|';
load data local infile '/data/tpch-data/orders.tbl.2' into table orders fields terminated by '|';
load data local infile '/data/tpch-data/orders.tbl.3' into table orders fields terminated by '|';
load data local infile '/data/tpch-data/orders.tbl.4' into table orders fields terminated by '|';
load data local infile '/data/tpch-data/orders.tbl.5' into table orders fields terminated by '|';
load data local infile '/data/tpch-data/orders.tbl.6' into table orders fields terminated by '|';
load data local infile '/data/tpch-data/orders.tbl.7' into table orders fields terminated by '|';
load data local infile '/data/tpch-data/orders.tbl.8' into table orders fields terminated by '|';
load data local infile '/data/tpch-data/orders.tbl.9' into table orders fields terminated by '|';
load data local infile '/data/tpch-data/partsupp.tbl.1' into table partsupp fields terminated by '|';
load data local infile '/data/tpch-data/partsupp.tbl.10' into table partsupp fields terminated by '|';
load data local infile '/data/tpch-data/partsupp.tbl.2' into table partsupp fields terminated by '|';
load data local infile '/data/tpch-data/partsupp.tbl.3' into table partsupp fields terminated by '|';
load data local infile '/data/tpch-data/partsupp.tbl.4' into table partsupp fields terminated by '|';
load data local infile '/data/tpch-data/partsupp.tbl.5' into table partsupp fields terminated by '|';
load data local infile '/data/tpch-data/partsupp.tbl.6' into table partsupp fields terminated by '|';
load data local infile '/data/tpch-data/partsupp.tbl.7' into table partsupp fields terminated by '|';
load data local infile '/data/tpch-data/partsupp.tbl.8' into table partsupp fields terminated by '|';
load data local infile '/data/tpch-data/partsupp.tbl.9' into table partsupp fields terminated by '|';
load data local infile '/data/tpch-data/part.tbl' into table part fields terminated by '|';
load data local infile '/data/tpch-data/region.tbl' into table region fields terminated by '|';
load data local infile '/data/tpch-data/supplier.tbl' into table supplier fields terminated by '|';