mysql快速导入数据
mysql一般导入导出数据有两种方式,1是导出insert语句,然后插入,2是导出outfile,然后load data infile

实验用的的表如下

show create table 20130208load;

CREATE TABLE `20130208load` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `content` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `20130208load_idx_uid` (`uid`)
) ENGINE=InnoDB

表里有30w数据

导出为outfile的方式为

SELECT * INTO OUTFILE 'F:\\temp\\20130122handler.txt'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM 20130122handler;

导出的文件7M,里面就是逗号分割的数据

导出为insert文件的方式为

.\mysqldump.exe -u root -proot crap 20130208load > F:\temp\20130208load.sql

导出的文件8M,可以看到它导出的insert语句是insert into values()()()......的形式

下面准备导入到另一个数据allcrap_test中

1 insert 方法

mysql> use allcrap_test;
Database changed
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source F:\\temp\\20130208load.sql

Query OK, 36537 rows affected (1.34 sec)
Records: 36537  Duplicates: 0  Warnings: 0

Query OK, 35535 rows affected (1.17 sec)
Records: 35535  Duplicates: 0  Warnings: 0

Query OK, 35537 rows affected (1.19 sec)
Records: 35537  Duplicates: 0  Warnings: 0

Query OK, 35536 rows affected (1.20 sec)
Records: 35536  Duplicates: 0  Warnings: 0

Query OK, 35544 rows affected (1.20 sec)
Records: 35544  Duplicates: 0  Warnings: 0

Query OK, 35540 rows affected (1.08 sec)
Records: 35540  Duplicates: 0  Warnings: 0

Query OK, 35542 rows affected (1.17 sec)
Records: 35542  Duplicates: 0  Warnings: 0

Query OK, 35534 rows affected (1.19 sec)
Records: 35534  Duplicates: 0  Warnings: 0

Query OK, 14695 rows affected (0.56 sec)
Records: 14695  Duplicates: 0  Warnings: 0

分为9次insert,差不多有10s

这里其实完全可以先把secondary索引去掉,然后插入,然后重建索引.
首先改下sqldump

.\mysqldump.exe --no-create-info -u root -proot crap 20130208load > F:\temp\20130208load_2.sql

加上了--no-create-info选项,没有导出DDL语句

这次先drop掉那个索引
alter table 20130208load drop index 20130208load_idx_uid
清空表(这是为了删除第一次导入的数据)
TRUNCATE table 20130208load

然后source命令

mysql> source F:\\temp\\20130208load_2.sql;
Query OK, 36537 rows affected (0.75 sec)
Records: 36537  Duplicates: 0  Warnings: 0

Query OK, 35535 rows affected (0.73 sec)
Records: 35535  Duplicates: 0  Warnings: 0

Query OK, 35537 rows affected (1.13 sec)
Records: 35537  Duplicates: 0  Warnings: 0

Query OK, 35536 rows affected (0.64 sec)
Records: 35536  Duplicates: 0  Warnings: 0

Query OK, 35544 rows affected (0.66 sec)
Records: 35544  Duplicates: 0  Warnings: 0

Query OK, 35540 rows affected (0.64 sec)
Records: 35540  Duplicates: 0  Warnings: 0

Query OK, 35542 rows affected (0.67 sec)
Records: 35542  Duplicates: 0  Warnings: 0

Query OK, 35534 rows affected (0.92 sec)
Records: 35534  Duplicates: 0  Warnings: 0

Query OK, 14695 rows affected (0.33 sec)
Records: 14695  Duplicates: 0  Warnings: 0

这次明显快多了,大概6.47s.

最后把secondary索引加上去就行了

alter table 20130208load add index 20130208load_idx_uid(uid);

2 然后试下load data infile
创建用于load data infile的表
create table 20130208load_infile like crap.20130208load

有索引的情况下load

load data INFILE 'F:\\temp\\20130208load.txt'
INTO table 20130208load_infile
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

9s