mysql tpch_数据库MySQL与TPCH

1、安装mySQL

先安装: MySQL Server，

再安装: MySQL Client, MySQL Query Browser, MySQL Navigator, MySQL Administrator，反正能装的我都装了，这个过程很简单

2、下载TPC-H

现在的最新版是2.14，一共47MB.

3、解压到虚拟机

4、生成数据并加载到MySQL

1) 打开终端，取得root权限

2)创建MySQL用户、数据库、及授权

mysql -u root -p

mysql> CREATE USER 'tpch'@'%' IDENTIFIED BY 'tpch';

mysql> CREATE DATABASE tpch;

mysql> GRANT ALL ON tpch.* to 'tpch'@'%';

mysql> USE tpch;

3)运行tpch目录下的dss.ddl文件,创建tpch数据库中的表

mysql> \. tpch/gen/dss.ddl

4)然后在tpch文件目录下，把makefile复制并改名成makefile，接着修改makefile文件

shell> cp makefile.suite makefile

shell> vim makefile

makefile中相应项后面填写：

CC      = gcc

# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)  #                                  SQLSERVER, SYBASE  # Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS,   #                                  SGI, SUN, U2200, VMS, LINUX, WIN32   #

Current values for WORKLOAD are:  TPCH

DATABASE= SQLSERVER

MACHINE = LINUX

WORKLOAD = TPCH

5)修改tpch.h文件

修改其中的SQLSERVER段为：

#ifdef  SQLSERVER

#define GEN_QUERY_PLAN        "EXPLAIN;"

#define START_TRAN                "START TRANSACTION;\n"

#define END_TRAN                "COMMIT;\n"

#define SET_OUTPUT                ""

#define SET_ROWCOUNT                "limit %d;\n"

#define SET_DBASE                "use %s;\n"

#endif

6)执行makefile

shell> make

7)生成需要用的数据

shell> ./dbgen -s 1

-s 数据规模因子，1为1G的数据量

8)修改tpch目录下的dss.ri文件

1.删除 "CONNECT TO TPCD;"

2.删除所有的 "TPCH."(注意有个点)

3.删除所有的"COMMIT WORK;"(注意分号也要删除)

9)登录MySQL，并载入之前生成的数据进MySQL

shell> mysql -u tpch -p

mysql> use tpch;

mysql> LOAD DATA LOCAL INFILE 'customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|';

mysql> LOAD DATA LOCAL INFILE 'orders.tbl' INTO TABLE ORDERS FIELDS TERMINATED BY '|';

mysql> LOAD DATA LOCAL INFILE 'lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINATED BY '|';

mysql> LOAD DATA LOCAL INFILE 'nation.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|';

mysql> LOAD DATA LOCAL INFILE 'partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINATED BY '|';

mysql> LOAD DATA LOCAL INFILE 'part.tbl' INTO TABLE PART FIELDS TERMINATED BY '|';

mysql> LOAD DATA LOCAL INFILE 'region.tbl' INTO TABLE REGION FIELDS TERMINATED BY '|';

mysql> LOAD DATA LOCAL INFILE 'supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINATED BY '|';

10)执行tpch目录下的dss.ri

mysql> \. dss.ri 11)由于查询中使用的是小写表名，而使用dss.ddl生成的表名是大写的，所以转换表名成小写

mysql> alter table NATION rename nation;

mysql> alter table SUPPLIER rename supplier;

mysql> alter table REGION rename region;

mysql> alter table PARTSUPP rename partsupp;

mysql> alter table PART rename part;

mysql> alter table ORDERS rename orders;

mysql> alter table LINEITEM rename lineitem;

mysql> alter table CUSTOMER rename customer;