php - 如何处理百万条数据写入到数据库

Question

在一个文本文件里存了一百万条数据，一行一条，

我需要把符合条件的每一行数据写入到数据库里，

按照以前的做法就是读取文件里的数据，然后存入数组，然后foreach数组进行一条一条的处理（符合条件的写入到数据库），

但是面对上百万条数据，如果我再继续这样做看来是自寻死路，可是处理大数据真是大姑娘我坐花轿可是头一回，一点经验也没有，

从网上找资料说用php进程/线程来解决，我对进程与线程是一头污水，请大牛们进来分享一下这方面的经验，是怎么处理大数据的，如果通过进程/线程又是怎么实现的呢？

Answer 1

百万条…不算大数字啊…

数据处理的瓶颈基本在IO，你可以直接单线程读写（特别是你还要做数据库插入，随便添加个index你的插入就成为瓶颈了）。

但是你为什么要存入数组啊同学！你准备把文件内容全部塞进内存吗啊？读一条，判断一次，OK就存数据库不OK就丢掉，就这么简单，存什么数组…

还有你真的准备用PHP做数据处理吗…

Answer 2

按一千万行的数据来计算，假设你对 PHP 最熟悉，开发速度最快，假设你要写入到 MySQL 中。

1. 用 shell 将一千万行文件切分成 100 个文件,这样每个文件有10万行,具体做法可以 man split。

2. 写 php 脚本,脚本内容是读一个文件,然后输出有效的数据。注意数据格式，严格按照表中字段的顺序来写，字段之间用半角分号隔开，行之间用 \n 隔开。具体参数可配置，参见 MySQL 的 load data 命令参数。注意是 php cli 方式运行的，不要拿 Apache 或者 其他 web server 下面跑这个东西。如果按行读不知道怎么搞可以直接用 php 的 file() 函数，生成的 sql 语句通过 error_log($sql, 3, "/path/to/dataa") 函数写入到文件中。同时可以 echo 一些调试信息，以备后续检查。

3. 写 shell 脚本调用 php 处理日志.脚本可以类似来写

   /path/to/php/bin/php -f genMySQLdata.php source=loga out=dataa > /errora.log & /path/to/php/bin/php -f genMySQLdata.php source=logb out=datab > /errorb.log & /path/to/php/bin/php -f genMySQLdata.php source=logc out=datac > /errorc.log & ....重复一百行，机器配置低可以分批写，每个写 10 行也行。这个脚本内容很有规律吧，本身也可以用 php 来生成。时间又省了。 在机器上执行这个 shell 脚本，实际上就启动多个 PHP 进程来生成数据。配置够牛的话，就等于你启动了 100 个 PHP 进程来处理数据。速度又快了。

4. 继续写 shell 脚本，内容是打开 MySQL 用 load data 来载入数据。

   mysql -h127.0.0.1 -uUser -ppwd -P3306 -D dbname -e 'load data infile "/path/to/dataa" into table TableName(Field1, Field1, Field1);' 其中的 field1 ... 要跟生成数据的顺序对应，这个命令可以直接执行，也可以放到 shell 里面重复写 N 行,然后执行 shell 脚本。

PS：注意编码

Answer 3

如果是一次性把这100万数据导入mysql就完事了，用mysql的load data就可以搞的，我用load data 导入以前csdn泄漏的帐号密码650万左右的数据也就2分钟多点。。。
如果是多次复用程序，想做成脚本，可以每次读取10万个，foreach外面显式开启事务（ps:循环插入必须显式开启事务，性能比较好，一次性写入后，再统一commit，10万条速度提升最起码百倍甚至千倍，磁盘io也低），变量用完记得unset，插入100万数据那是小case。
也可以用insert into values(1,2),(2,3)...拼接的方式，性能最快。不过注意sql语句长度有限制，可以一次性插入1000条。 不显式开启事务，foreach里insert是最垃圾的做法，最慢，io压力也最大，因为每次insert，都有一次昂贵的系统调用fsync(). 循环100万次相当于调用100万次fsync. 显示开启事务，每10万次一次commit调用一次fsync，100万次只调用10次fsync。

Answer 4

这个场景,瓶颈不再php上,一百万数据的插入所需时间很短的,
不用一次全读内存,一次读一条,符合条件的拼成sql,不要着急插mysql,
等拼个几百条在插一次,速度快很多,注意最后拼出来的sql大小不要超过max_ allowed_ packet,

Answer 5

用python吧,先用python读取文本生成sql，再用source导入，OK！

f = open("/data/data.txt")
t = open("/data/sql.txt", 'w+');
s = '';
i = 0;
line = f.readline();
while line:
    a  = line.split("\t");
    t.write("INSERT INTO `table`(`id`, `add_time`) VALUES");
    b = "('%s', '%s');" %(a[0], a[2]);
    line = f.readline(); 
    if line == '':
        b = "('%s', '%s');" %(a[0], a[2]);
    t.write(b);
f.close();
t.close();

Answer 6

补充一下，记得把Index都删了，然后load data in file.最后再把Index加上，这样会快很多

Answer 7

其实你为什么不试一下写个线程来处理数据的导入呢，在导入之前我建议开启事物、我一般比较喜欢在程序中开启事物。

Answer 8

一般数据库都提供 buck insert的功能吧~

Answer 9

LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.7/en/load-data.html

不应该从文件筛选数据插入数据库，而应该整文件插入数据库，然后从数据库筛选，那样要简单多了。

数据库不就是干那个的吗？