Mysql 之百万数据导入

1、方法

• 直接source sql 文件进行直接导入（不推荐）
• Load data infile命令进行导入（推荐）
• 更换引擎，将表的InnoDB引擎改为MyISAM，再导入数据（不推荐）
• 将导入数据的单条SQL语句为多条进行数据的导入（推荐）
• 存储过程 + 事务进行有序插入（有兴趣的可以自己试试）

1.1 直接导入（不推荐）

这种方式就不需要讲了，简单暴力，直接source

use example_database;
source your_disk:/yourpath/example.sql

总结：这种导入方式进行百万数据的导入，你可以尝试一下，我在半小时才导入了2万多条数据，如果是百万的话，可以估计一下时间。所以不推荐使用这种方法

1.2 Load data infile导入（推荐）

1.2.1 基本介绍：

load data infile语句从一个文本文件中以很高的速度读入一个表中。使用这个命令之前，mysqld进程（服务）必须已经在运行。

注意：

• 文件必须处于数据库目录或可被所有人读取
• 服务器主机上你必须有file的权限。

1.2.2 语法结构：

load data  [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'\' ]]
[lines terminated by'n']
[ignore number lines]
[(col_name,   )]

说明：

• 如果指定low_priority关键词，那么MySQL将会等到没有其他人读这个表的时候，才把插入数据
• 如果指定local关键词，则表明从客户主机读文件。如果local没指定，文件必须位于服务器上
• replace和ignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace，新行将代替有相同的唯一键值的现有行。如果你指定ignore，跳过有唯一键的现有行的重复行的输入
• fields关键字指定了文件记段的分割格式，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项：
  • terminated by 分隔符：意思是以什么字符作为分隔符
  • enclosed by 字段括起字符
  • escaped by 转义字符
  • terminated by 描述字段的分隔符，默认情况下是tab字符（\t）
  • enclosed by 描述的是字段的括起字符。
  • escaped by 描述的转义字符。默认的是反斜杠（backslash：\ ）
• lines 关键字指定了每条记录的分隔符默认为’\n’即为换行符

1.2.3 举例子：

（一）文件数据

"我爱你","20","相貌平常，经常耍流氓！哈哈"
"李奎","21","相貌平常，经常耍流氓！哈哈"
"王二米","20","相貌平常，经常耍流氓！哈哈"
"老三","24","很强"
"老四","34","XXXXX"
"老五","52","***%*￥*￥*￥*￥"
"小猫","45","中间省略。。。"
"小狗","12","就会叫"
"小妹","21","PP的很"
"小坏蛋","52","表里不一"
"上帝他爷","96","非常英俊"
"MM来了","10","。。。"
"歌颂党","20","社会主义好"
"人民好","20","的确是好"
"老高","10","学习很好"
"斜三","60","眼睛斜了"
"中华之子","100","威武的不行了"
"大米","63","我爱吃"
"苹果","15","好吃"

（二）示例表结构

CREATE TABLE `t0` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `name` char(20) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

（三）导入命令

# ignore忽略重复的数据
# terminated 数据是以逗号风格的
# enclosed by 字段是以""括起来的
# lines terminated 数据是分行
load data infile '/tmp/t0.txt' 
	ignore into table t0 character set gbk 
	fields 
	terminated by ',' enclosed by '"' 
	lines 
	terminated by '\n' (`name`,`age`,`description`);

（四）导入情况

mysql> select * from t0;
+----+----------+-----+----------------------------+
| id | name     | age | description                |
+----+----------+-----+----------------------------+
|  1 | 我爱你   |  20 | 相貌平常，经常耍流氓！哈哈 | 
|  2 | 李奎     |  21 | 相貌平常，经常耍流氓！哈哈 | 
|  3 | 王二米   |  20 | 相貌平常，经常耍流氓！哈哈 | 
|  4 | 老三     |  24 | 很强                       | 
|  5 | 老四     |  34 | XXXXX                      | 
|  6 | 老五     |  52 | ***%*￥*￥*￥*￥           | 
|  7 | 小猫     |  45 | 中间省略。。。             | 
|  8 | 小狗     |  12 | 就会叫                     | 
|  9 | 小妹     |  21 | PP的很                     | 
| 10 | 小坏蛋   |  52 | 表里不一                   | 
| 11 | 上帝他爷 |  96 | 非常英俊                   | 
| 12 | MM来了   |  10 | 。。。                     | 
| 13 | 歌颂党   |  20 | 社会主义好                 | 
| 14 | 人民好   |  20 | 的确是好                   | 
| 15 | 老高     |  10 | 学习很好                   | 
| 16 | 斜三     |  60 | 眼睛斜了                   | 
| 17 | 中华之子 | 100 | 威武的不行了               | 
| 18 | 大米     |  63 | 我爱吃                     | 
| 19 | 苹果     |  15 | 好吃                       | 
+----+----------+-----+----------------------------+
19 rows in set (0.00 sec)

总结：利用这种方式导入百万数据，可以试试，只需
要几十秒而已，特别快速。推荐

1.3 更换引擎

这个主要是在创建表的时候，将使用的引擎由Inoodb 改为 MyISAM 再导入

创表语句：

CREATE TABLE `t0` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `name` char(20) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE= MyISAM DEFAULT CHARSET=utf8

然后再采用上面两种方式进行数据导入的时候：

• innodb + 直接导入：插入了3万条数据，花费了半小时。
  MyISAM + 直接导入：插入了3万条数据，花费了十分钟。
• innodb + Load data infile：插入了百万条数据，花费了40几秒。
  MyISAM + Load data infile：插入了百万条数据，花费了30几秒。

总结：更换引擎的方式进行导入数据的话，明显会快一点，但是MyISAM 与 innodb 的区别还是很大的，为了后续的一些操作，不建议采用这种方式。

1.2.4 合并单条SQL语句为多条再导入

这种方法主要是把上百万条插入数据的sql，合并为1000条sql，每一条sql进行1000条数据的插入

例如：

多条sql：

insert ignore into actor values (1 , 'PENELOPE' , 'GUINESS'  ,'2006-02-15 12:34:33'),
insert ignore into actor values (2 , 'NICK' , 'WAHLBERG'  ,'2006-02-15 12:34:33');

一条sql：

insert ignore into actor values
    (1 , 'PENELOPE' , 'GUINESS'  ,'2006-02-15 12:34:33'),
    (2 , 'NICK' , 'WAHLBERG'  ,'2006-02-15 12:34:33');

具体如何进行合并，可以有两种方法：

• 直接进行手动合并（如果你觉得你脑子有问题的话）
• 利用程序，利用python、Java直接操作文件，进行合并（具体就不再讲解了）

总结：这种方式进行导入的话，按照一条sql插入1000行数据，进行1000次插入的操作这种方法用来进行100万数据导入，只花了2分钟左右，也推荐采用