1、安装mySQL
先安装: MySQL Server，
再安装: MySQL Client, MySQL Query Browser, MySQL Navigator, MySQL Administrator，反正能装的我都装了，这个过程很简单
2、下载TPC-H
现在的最新版是2.14，一共47MB.
http://www.tpc.org/tpch/spec/tpch_2_14_0.zip
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;
