mysql 导入大量数据

Posted by wxianfeng Sat, 03 Jul 2010 05: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 05: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