1000万条数据导入mysql_MySQL

bitsCN.com

1000万条数据导入mysql

 

今天需要将一个含有1000万条数据的文本内容插入到数据库表中，最初自然想到的是使用Insertinto '表名'values(),(),()...这种插入方式，但是发现这种方式对1000万条数据量的情况，明显效率低下，于是选用了直接将文本内容导入数据表的方法：

LOADDATA LOCAL INFILE '/home/xyw/result.txt' INTO TABLEdomainlib_tmp(domain);

这种方式可以将本地的数据文件'result.txt'直接导入到domainlib_tmp表的domain字段中，这种方式是选择性的导入数据，即只是填充字段domain，如果想填充表中的所有字段，可以使用：

LOADDATA LOCAL INFILE '/home/xyw/result.txt' INTO TABLE domainlib_tmpFIELDS TERMINATED BY '/t' LINES TERMINATED BY '/n';

TERMINATEDBY '/t'表示用/t来分割字段；LINESTERMINATED BY '/n'表示用/n来分割一行。如果您不指定FIELDS子句，则默认值为假设您写下如下语句时的值：

FIELDSTERMINATED BY '/t' ENCLOSED BY '' ESCAPED BY '//'

如果您不指定LINES子句，则默认值为假设您写下如下语句时的值：

LINESTERMINATED BY '/n' STARTING BY ''

windows和linux的文本终止符不相同（windows使用/r/n；linux使用/n），我在这一块就遇到了问题，我在windows下生成了一个文件，然后在linux下使用，发现填充后的每条记录的结尾都有一个未知字符，导致查询失败，最后我使用的方法是把文本的内容重新复制到linux下新建的文本中，然后导入linux下新建的文本内容就可以解决。而且我发现在导入部分字段数据时，无法使用TERMINATEDBY，即下面这种方式会报错：

LOADDATA LOCAL INFILE '/home/xyw/seco.txt' INTO TABLE tmp2(admin) FIELDSTERMINATED by '/t' LINES TERMINATED by '/r/n';

有关Loaddata infile的使用方法自己查阅mysql手册吧。

注意：在导入数据之前一定要把表中的索引删除，否则会特别特别慢。

下面说一下mysql的优化问题。

我发现在使用load data infile时，数据表引擎使用MyISAM比InnoDB快很多！而且数据的查询速度MyISAM也要比InnoDB好，所以我把我的mysql配置文件中默认使用的引擎改成了MyISAM。

有关MyISAM和InnoDB差异的文章，请参考：

MySQLMyISAM和InnoDB对比

 

mysql的优化需要修改mysql配置文件：my.cnf

Mysql配置原则：

配置合理的MySQL服务器,尽量在应用本身达到一个MySQL最合理的使用

针对 MyISAM或 InnoDB不同引擎进行不同定制性配置

针对不同的应用情况进行合理配置

针对 my.cnf进行配置,后面设置是针对内存为2G的服务器进行的合理设置

公共选项：

选项

缺省值

推荐值

说明

max_connections

100

1024

MySQL服务器同时处理的数据库连接的最大数量

query_cache_size

0（不打开）

16M

查询缓存区的最大长度,按照当前需求,一倍一倍 增加,本选项比较重要

sort_buffer_size

512K

16M

每个线程的排序缓存大小,一般按照内存可以设置为2M以上,推荐是16M,该选项对排序orderby,group by起作用

record_buffer

128K

16M

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区,可以设置为2M以上

table_cache

64

512

为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量.MySQL对每个唯一打开的表需要2个文件描述符。

 

MyISAM选项：

选项

缺省值

推荐值

说明

key_buffer_size

8M

256M

用来存放索引区块的缓存值,建议128M以上,不要大于内存的30%

read_buffer_size

128K

16M

用来做MyISAM表全表扫描的缓冲大小.为从数据表顺序读取数据的读操作保留的缓存区的长度

myisam_sort_buffer_size

16M

128M

设置,恢复,修改表的时候使用的缓冲大小,值不要设的太大

 

InnoDB选项

选项

缺省值

推荐值

说明

innodb_buffer_pool_size

32M

1G

InnoDB使用一个缓冲池来保存索引和原始数据,这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少,一般是内存的一半,不超过2G,否则系统会崩溃,这个参数非常重要

innodb_additional_mem_po

ol_size

2M

128M

InnoDB用来保存metadata信息,如果内存是4G,最好本值超过200M

innodb_flush_log_at_trx_co

mmit

1

0

0代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘;1为执行完没执行一条SQL马上commit;2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上.对速度影响比较大,同时也关系数据完整性

innodb_log_file_size

8M

256M

在日志组中每个日志文件的大小,一般是innodb_buffer_pool_size的25%,官方推荐是innodb_buffer_pool_size的40-50%,设置大一点来避免在日志文件覆写上不必要的缓冲池刷新行为

innodb_log_buffer_size

128K

8M

用来缓冲日志数据的缓冲区的大小.推荐是8M,官方推荐该值小于16M,最好是1M-8M之间

 

其他有关my.cnf选项的介绍参考：

mysql配置文件my.cnf详解[部分]

下面给出我的配置：（只是mysqld部分）

################自定义配置################

character-set-server=utf8

default-storage-engine=MyISAM

#default-storage-engine=InnoDB

max_connections=1024

max_connections=1024

query_cache_size=64M

#table_cache=256

#tmp_table_size=35M

#***MyISAM Specific options

key_buffer_size=512M

read_buffer_size=32M

read_rnd_buffer_size=32M

sort_buffer_size=32M

#***INNODB Specific options ***

innodb_additional_mem_pool_size=128M

innodb_flush_log_at_trx_commit=0

innodb_log_buffer_size=8M

innodb_buffer_pool_size=1G

innodb_log_file_size=256M

innodb_thread_concurrency=18

######################################

 

bitsCN.com