大数据量插入问题

以前开发的系统，目前要大数据插入操作。3万多条数据，每一条数据20个字段。数据表按月分配。一月一表。

数据库设计时，知道要大量插入，所以没有加主键，只对一个唯一字段修改为unique类型。

代码逻辑。
读取csv文件一行数据 用fgetcsv函数读取
插入该条数据，
判断返回值
if==0
代表本月有重复，不理。
if==1
代表本月没重复，根据ID查找上个表。看是否有值。

结果==0 
上表无重复 跳过

结果==1
修改上表内容，并删除刚插入数据。

这样的逻辑 在上月表中有5W数据，本月表7W数据时。插入3W数据时，IE会出现404错误。不知道如何进一步优化数据库，能让添加更快呢？或者如何能避免IE死掉的问题。
先感谢耐心看完的前辈们。等待高手指点一二。

22 个解决方案

#1

关注，我也遇到这样的问题。

#2

用存储过程吧，将步骤N都写进一个存储过程里，然后读取文件将数据以参数传入，这样会快些

#3

大量數據插入可能PHP會執行超時引各種問題，先設置PHP
max_execution_time  =  600 ;每个PHP页面运行的最大时间值(秒)，默认30秒

max_input_time = 600 ;每个PHP页面接收数据所需的最大时间，默认60秒

memory_limit  = 128M ;每个PHP页面所吃掉的最大内存，默认8M

#4

哦。对，忘了补充了。apache里该设置的都设置了。

#5

引用 3 楼 tfxg 的回复:

大量數據插入可能PHP會執行超時引各種問題，先設置PHP 
max_execution_time  =  600 ;每个PHP页面运行的最大时间值(秒)，默认30秒 

max_input_time = 600 ;每个PHP页面接收数据所需的最大时间，默认60秒 

memory_limit  = 128M ;每个PHP页面所吃掉的最大内存，默认8M 

這是php.ini的設置

#6

mysqli 支持prepared语句的使用，它们对于大量具有不同数据的相同查询和插入时可以提高执行速度.不知道这个对楼主有没有用! 下面是一个例子,你可查一下php手册
<?php 
$mysqli = new mysqli("localhost", "my_user", "my_password", "world"); 

if (mysqli_connect_errno()) { 
    printf("Connect failed: %s\n", mysqli_connect_error()); 
    exit(); 
} 

/* prepare statement */ 
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country ORDER BY Name LIMIT 5")) { 
    $stmt->execute(); 

    /* bind variables to prepared statement */ 
    $stmt->bind_result($col1, $col2); 

    /* fetch values */ 
    while ($stmt->fetch()) { 
        printf("%s %s\n", $col1, $col2); 
    } 

    /* close statement */ 
    $stmt->close(); 
} 
/* close connection */ 
$mysqli->close(); 

?> 

#7

恩。。是。就是都设置过了。 一时概念混淆了。。呵呵

#8

恩。谢谢。sqli到是听说过。研究下。

#9

引用 6 楼 iwantnet 的回复:

mysqli 支持prepared语句的使用，它们对于大量具有不同数据的相同查询和插入时可以提高执行速度.不知道这个对楼主有没有用! 下面是一个例子,你可查一下php手册 
<?php 
$mysqli = new mysqli("localhost", "my_user", "my_password", "world"); 

if (mysqli_connect_errno()) { 
    printf("Connect failed: %s\n", mysqli_connect_error()); 
    exit(); 
} 

/* prepare statement */ 
if ($stmt = $mysqli->prep…

就是最好不要改写过的东西。。太多而且这系统用的都是公司的db类，换起来有点。。。不过还是谢谢。

#10

你可以不改其它的模块.就把这个模块单独的改一下.看一看效果如果.如果效果好,可以以使用呀
.

#11

这样的操作，还是在服务器端用cron在后台操作算了……

#12

刚才我在本机试了一下，insert 30000记录，
$db = new mysqli('127.0.0.1','root','123456','testdb');
for($i=1;$i<=30000;$i++)
{
$va="abcdefghigklmn";
$vb="abcdefghigklmn";
$vc="abcdefghigklmn";
$vd="abcdefghigklmn";
$ve="abcdefghigklmn";
$vf="abcdefghigklmn";
$vgg="abcdefghigklmn";
$vh="abcdefghigklmn";
$vi="abcdefghigklmn";
$vj="abcdefghigklmn";
$vk="abcdefghigklmn";
$vl="abcdefghigklmn";
$vm="abcdefghigklmn";
$vn="abcdefghigklmn";
$vo="abcdefghigklmn";
$vp="abcdefghigklmn";
$vq="abcdefghigklmn";
$vr="abcdefghigklmn";
$vs="abcdefghigklmn";
$vt="abcdefghigklmn";
$sql="insert into testdb (va,vb,vc,vd,ve,vf,vgg,vh,vi,vj,vk,vl,vm,vn,vo,vp,vq,vr,vs,vt) values('$va','$vb','$vc','$vd','$ve','$vf','$vgg','$vh','$vi','$vj','$vk','$vl','$vm','$vn','$vo','$vp','$vq','$vr','$vs','$vt')";
$db->query($sql);
页面执行时间: 7427.5 毫秒
我是就是普通的pc机呀。感觉不慢，还可以！

#13

为什么不直接用 LOAD DATA INFILE 直接把数据加载到数据库表中

#14

关注中，若这问题解决了，请楼主分享下哈，嘿嘿

#15

恩。这个方法是很新鲜，受益了，但是我要对数据与另外一张表进行判断，重复的修改等。用那个方法就不容易做到了吧？

#16

引用 12 楼 iwantnet 的回复:

刚才我在本机试了一下，insert 30000记录， 
$db = new mysqli('127.0.0.1','root','123456','testdb'); 
for($i=1;$i <=30000;$i++) 
{ 
$va="abcdefghigklmn"; 
$vb="abcdefghigklmn"; 
$vc="abcdefghigklmn"; 
$vd="abcdefghigklmn"; 
$ve="abcdefghigklmn"; 
$vf="abcdefghigklmn"; 
$vgg="abcdefghigklmn"; 
$vh="abcdefghigklmn"; 
$vi="abcdefghigklmn"; 
$vj="abcdefghigklmn"; 
$vk="abcdefghigklmn"; 
…

辛苦了。今天着手试一下，有结果会告诉你的。呵呵。谢谢

#17

引用 12 楼 iwantnet 的回复:

刚才我在本机试了一下，insert 30000记录， 
$db = new mysqli('127.0.0.1','root','123456','testdb'); 
for($i=1;$i <=30000;$i++) 
{ 
$va="abcdefghigklmn"; 
$vb="abcdefghigklmn"; 
$vc="abcdefghigklmn"; 
$vd="abcdefghigklmn"; 
$ve="abcdefghigklmn"; 
$vf="abcdefghigklmn"; 
$vgg="abcdefghigklmn"; 
$vh="abcdefghigklmn"; 
$vi="abcdefghigklmn"; 
$vj="abcdefghigklmn"; 
$vk="abcdefghigklmn"; 
…

抽空试了下。sqli比普通的sql要慢。。本地机时这样的。

#18

因为我是用的二种方法。一个是prepared,一个是mysqli,普通的我没有试过.

#19

先生成SQL语句,然后再执行就OK了,3万条小菜了.几秒钟搞定.

#20

$sql="insert into testdb (va,vb,vc,vd,ve,vf,vgg,vh,vi,vj,vk,vl,vm,vn,vo,vp,vq,vr,vs,vt) values('$va','$vb','$vc','$vd','$ve','$vf','$vgg','$vh','$vi','$vj','$vk','$vl','$vm','$vn','$vo','$vp','$vq','$vr','$vs','$vt')";
$db->query($sql); 

不要执行 30000次$db->query($sql)
1次可以了，sql写成insert into testdb (va,vb,vc,vd,ve,vf,vgg,vh,vi,vj,vk,vl,vm,vn,vo,vp,vq,vr,vs,vt) values('$va','$vb','$vc','$vd','$ve','$vf','$vgg','$vh','$vi','$vj','$vk','$vl','$vm','$vn','$vo','$vp','$vq','$vr','$vs','$vt')，('$va','$vb','$vc','$vd','$ve','$vf','$vgg','$vh','$vi','$vj','$vk','$vl','$vm','$vn','$vo','$vp','$vq','$vr','$vs','$vt')。。。。。。。。。。。。。。。

#21

限制每次处理的条数，500到1000好点，还有跳转时用JS好点

#22

生成 .sql 档案或直接用 .csv
再用 LOAD DATA INFILE , 或者 mysql 命令行加载才是最快的方法

设定了unique key 字段就不需要再在程序中判断是否重复
mysql 会自己根据index跳过重复记录