说明
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 '|';