mysql 导入大量数据

Posted by wxianfeng Sat, 03 Jul 2010 17:30:00 GMT



想让你的本地mysql数据库某个表迅速产生大量数据吗，例如 500 万条 ， 来进行性能测试 ， 我就遇到了这个情况 ， 导入过程如下 ：

测试环境 : windows xp + mysql 5.1

myisam 引擎的表导入:

表结构如下:

members(id,name.password,city,created_at,updated_at)

id是int型,name,password,city 都是varchar型,created_at,updated_at 是 datetime类型,其中 city字段 有索引

1,关闭表的索引

ALTER TABLE members DISABLE KEYS;


关闭keys ， 会在导入过程中对新的record不建索引，加快导入速度

2,导入数据

load data infile 'E:/members.txt' into table members fields terminated by ","   optionally enclosed by '"'  lines terminated by "\r\n" (name,password,city,created_at,updated_at);


fields terminated by "," 字段分隔符, lines terminated by "\r\n" 行分隔符, 注意windows下的默认行换行符是 \r\n ,UNIX 下的换行符是 \n , optionally enclosed by '"' 对于 字符型字段 加双引号 ， 不是指字符型的，例如int 则不加双引号

members.txt 文件内容形式:

"wxianfeng","wxianfeng","beijing","2010-06-30 00:07:45","2010-06-30 00:07:48"
"kaili","kaili","suzhou","2010-06-30 00:07:45","2010-06-30 00:07:48"


最后 (name,password,city,created_at,updated_at) 指定依次导入的字段

那么 members.txt 又是怎么来的呢 ， 你可以导出一个表瞧瞧就知道了 , 导出方法如下:

select * from members into outfile 'E:/members.txt' fields terminated by "," optionally enclosed by '"';


那么下面就是你有什么办法快速生成上面的 members.txt 了，我用ruby写了个脚本生成了 500 万条数据：

# members(name,password,city,created_at,updated_at)

citys = %w(beijing shanghai guangzhou nanjing wuhan xihan hangzhou dalian jinan hefei)

arr = ('0'..'9').to_a + ('a'..'z').to_a

File.open("E:/members_1.txt","a+") do |file|
  2_000_000.times do |ele|
    p "generate --- #{ele}"
    str = ''
    5.times { |t| str << arr[rand(arr.size)] }
    time = Time.now.strftime("%Y-%m-%d %H:%M:%S")
    file.write("\"#{str}\",\"#{str}\",\"#{citys[rand(citys.size)]}\",\"#{time}\",\"#{time}\"\n")
  end
end

最后生成 500 万条数据后 ， 导入的速度非常之快 ， 据官方介绍 load data infile 的方式是sql导入速度的20倍 ，快的根本原因我想应该还是生成的文件小，纯粹数据的形式，容易快速加载到内存里

3，再开启 KEYS

ALTER TABLE members ENABLE KEYS;

innodb 引擎表的导入:

1,导入前关闭表的唯一性校验

SET UNIQUE_CHECKS=0;

2,关闭自动提交

innodb型的表是事务安全的，每一条sql执行后都会自动提交，关闭后将会提交导入速度

SET AUTOCOMMIT=0;

3,导入数据

和之前 myisam 表的导入方式相同， 但是有一点不同的是，生成members .txt 数据的时候 ，把id也生成，因为innodb类型的表是按照主键顺序保存的，对于给定有序的文本，比没有顺序的要快的多

4，开启之前的 唯一性和事务自动提交功能

SET UNIQUE_CHECKS=1;
SET AUTOCOMMIT=1;


最主要的还是要使用 load data infile 的导入方式，无论怎样， 你不要使用 单条 insert ，一条一条的插入， 这样网络io开销会巨大，速度很慢 ， 实在不行，你应该构造 一个insert into 多个value的方式来导入 ， 也会快很多,这个就是默认的mysqldump导出表数据所用的方法，还可以发现 在导入之前 ， 它进行了锁表:

LOCK TABLES `members` WRITE; 
insert into members values (......),(......),(....)
UNLOCK TABLES;

ref: 参考了 网易出的那本mysql书籍

This entry was posted on Sat, 03 Jul 2010 17:30:00 GMT and Posted in . You can follow any any response to this entry through the Atom feed. You can leave a comment or a trackback from your own site.

Tags


Trackbacks

Use the following link to trackback from your own site:
http://wxianfeng.com/trackbacks?article_id=28

Comments

Leave a comment