2.数据加载

　　MySQL的大容量文本数据文件导入方法是在交互界面中用load data local infile语句导入，语法是：

　　load data local infile 文件名 into table 表名 fields terminated by 列分隔符 lines terminated by 行分隔符。Local关键字表示从客户端加载，由于本测试的服务器和客户端装在同一台机器，所以local也可以省略。

　　为了提高导入性能，需要用set autocommit=off命令关闭自动提交，同时为了减少innodb数据文件的扩展频率，设定每次自动扩展1GB。

mysql> use tpch
Database changed
mysql> set global innodb_autoextend_increment= 1024;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)
mysql> load data local infile '/user1/app/oradata/tpch2/customer.tbl' into table customer fields terminated by '|' lines terminated by '|\r\n';
Query OK, 1500000 rows affected (16.91 sec)
Records: 1500000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.58 sec)

mysql> load data local infile '/user1/app/oradata/tpch2/orders.tbl' into table orders fields terminated by '|' lines terminated by '|\r\n';
Query OK, 15000000 rows affected (2 min 54.38 sec)
Records: 15000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (10.79 sec)

mysql> load data local infile '/user1/app/oradata/tpch2/partsupp.tbl' into table partsupp fields terminated by '|' lines terminated by '|\r\n';
Query OK, 8000000 rows affected (1 min 40.01 sec)
Records: 8000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (7.74 sec)

mysql> load data local infile '/user1/app/oradata/tpch2/region.tbl' into table region fields terminated by '|' lines terminated by '|\r\n';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data local infile '/user1/app/oradata/tpch2/supplier.tbl' into table supplier fields terminated by '|' lines terminated by '|\r\n';
Query OK, 100000 rows affected (1.04 sec)
Records: 100000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> load data local infile '/user1/app/oradata/tpch2/lineitem.tbl' into table lineitem fields terminated by '|' lines terminated by '|\r\n';
Query OK, 59986052 rows affected (14 min 48.84 sec)
Records: 59986052  Deleted: 0  Skipped: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (36.71 sec)

 　　我们看到，MySQL插入10GB的数据大约需要20分钟。越大的表，提交的时间也越长。

　　另一种插入方法是通过表之间复制数据，我们利用tpch数据库中的数据复制一份到每个表独立innodb表空间的tpch2数据库，对比从外部文件导入，二者差别不大。

　　前面提到，INNODB数据引擎存储表有2种方式，第一种：所有表和索引存储在一个共享的表空间中，第2种，每个表及其上的所有索引存储在一个独立的数据文件中。

　　默认情况下，MySQL采用第一种方式，如果要使用第二种方式，需要设置innodb_file_per_table参数，有2种设置办法，可以动态在MySQL命令行设置，设置后立即生效，也可以在配置文件中设置，服务器重新启动后自动生效。用户可根据自己使用的需要决定用哪种方式。由于设置innodb_file_per_table参数基本上没有不良影响，这里我们在my.cnf中设置。设置以后重新启动服务器，以前在共享表空间中的innodb表依然可以访问，但新建的表都是独立表空间了。由于独立数据文件的位置默认在/var/lib/mysql下的数据库名目录下，我们创建数据库后关闭MySQL服务器，将tpch2目录移动到空间较大的/user1/mysql_db目录下，然后再在原来的位置建立一个软连接。

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> create database tpch2;
Query OK, 1 row affected (0.00 sec)
[root@redflag11012501 tpch]# mysqladmin -u root shutdown
mysqladmin: Unknown OS character set 'GB18030'.
mysqladmin: Switching to the default character set 'latin1'.
[root@redflag11012501 tpch]# pwd
/var/lib/mysql/tpch
[root@redflag11012501 tpch]# su - mysql
-bash-3.2$ mv  /var/lib/mysql/tpch2 /user1/mysql_db
-bash-3.2$ ln -s /user1/mysql_db/tpch2 tpch2
-bash-3.2$ ll
总计 120
drwx--x--x 2 mysql mysql  4096 06-02 17:59 mysql
-rw-rw---- 1 mysql mysql   150 06-04 19:42 mysql-bin.000015
-rw-rw---- 1 mysql mysql   677 06-08 11:03 mysql-bin.000016
-rw-rw---- 1 mysql mysql   126 06-08 11:22 mysql-bin.000017
-rw-rw---- 1 mysql mysql   126 06-08 14:00 mysql-bin.000018
-rw-rw---- 1 mysql mysql   218 06-09 12:58 mysql-bin.000019
-rw-rw---- 1 mysql mysql    95 06-08 21:19 mysql-bin.index
drwx------ 2 mysql mysql  4096 06-02 17:59 performance_schema
-rw-rw---- 1 mysql mysql 14288 06-10 15:16 redflag11012501.err
-rw-rw---- 1 mysql mysql 50512 06-10 12:33 redflag11012501-slow.log
-rw-r--r-- 1 mysql mysql   121 06-02 17:59 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysql mysql   121 06-02 17:59 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x 2 mysql mysql  4096 06-02 17:59 test
drwx------ 2 mysql mysql  4096 06-10 12:33 tpch
lrwxrwxrwx 1 mysql mysql    21 06-10 15:19 tpch2 -> /user1/mysql_db/tpch2
-bash-3.2$ exit
logout
[root@redflag11012501 tpch]# service mysql start
Starting MySQL......[确定]

mysql> use tpch
Database changed

mysql> create table tpch2.partsupp as select * from partsupp;
Query OK, 8000000 rows affected (1 min 37.88 sec)
Records: 8000000  Duplicates: 0  Warnings: 0

mysql> create table tpch2.part as select * from part;
Query OK, 2000000 rows affected (20.01 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

mysql> create table tpch2.orders as select * from orders;
Query OK, 15000000 rows affected (2 min 55.07 sec)
Records: 15000000  Duplicates: 0  Warnings: 0

mysql> create table tpch2.customer as select * from customer;
Query OK, 1500000 rows affected (24.22 sec)
Records: 1500000  Duplicates: 0  Warnings: 0

mysql> create table tpch2.supplier as select * from supplier;
Query OK, 100000 rows affected (2.58 sec)
Records: 100000  Duplicates: 0  Warnings: 0

mysql> create table tpch2.nation as select * from nation;
Query OK, 25 rows affected (0.05 sec)
Records: 25  Duplicates: 0  Warnings: 0

mysql> create table tpch2.region as select * from region;
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create table tpch2.lineitem as select * from lineitem;
Query OK, 59986052 rows affected (13 min 29.94 sec)
Records: 59986052  Duplicates: 0  Warnings: 0