PHP大批量数据导入,后端优化方案

  • 需求:十万级的csv手机号码导入,要求系统负载低于0.3,不阻塞PHP-FPM进程,让用户依然能访问网页其他页面
  • 思路:将导入操作放到后台进程处理,前台通过ajax轮询查询导入进度。
  • 分析:接收提交的控制器以非阻塞方式调用后台进程,这里打算用thinkphp的cli模式(不懂百度),提交的控制器执行exec()方法后立即返回200。
  • 开始代码:

PHP

function importTaskData(){
 $progress = 2;
 $id = $_GET['id'];
 $now_time = $_POST['now_time'];  //接收前端发送的唯一编码用作进度条

//拼接shell命令
 $shell_cmd = 'php /var/www/html/cli.php /BGCC/ImportAndExport/zzz/id/';   //zzz()方法
 $shell_cmd .= $id;
 $shell_cmd .= '/now_time/';
 $shell_cmd .= $now_time;
 $shell_cmd .= '/db_name/';
 $shell_cmd .= $_SESSION['db_name'];
 $shell_cmd .= '/file_name/';

$file_name_11 = $_FILES["client_phones"]["tmp_name"];

$dst_file = TMP_UPLOAD ."client_phones_".time().".csv";

copy($file_name_11,$dst_file);

$dst_file = str_replace('/','%2F',$dst_file);

$shell_cmd .= $dst_file.' > /dev/null &';


exec($shell_cmd);
echo json_encode(array('code'=>0,'msg'=>''));exit;


}


public function zzz(){
 while(file_exists('./Upload/progress/upload.lock')){   //控制进程只有一个用户同时导入
 sleep(2);
 }
 $lock_file = fopen('./Upload/progress/upload.lock','w');   //生成控制锁,倒入结束后销毁
 fwrite($lock_file,'locked');
 fclose($lock_file);

set_time_limit(0);
 @ini_set('memory_limit','512M');
 
 $progress = 0;
 $id = $_GET['id']; //todo ..
 $now_time = $_GET['now_time'];//todo ..
 $db_name = $_GET['db_name'];
 $tmp_file = str_replace('%2F','/',$_GET['file_name']);

 
//.....中间省略部分代码,只展示核心

 $sql = "insert into $table($fields) values ";
 $value = "";
 $total = $black = $repeat = $valid = $invalid = $phone_length = 0;
 $count_of_size = sizeof($arrData);
 $num = 0;
 foreach( $arrData AS $key=>&$val ){
 //$val[$field_key["phone1"]] = trim($val[$field_key["phone1"]]);
 $val[$field_key["phone1"]] = str_replace("-","",trim($val[$field_key["phone1"]]));
 $val[$field_key["phone2"]] = str_replace("-","",trim($val[$field_key["phone2"]]));
 $val["phone_length"] = strlen($val[$field_key["phone1"]]);
 if( in_array($val[$field_key["phone1"]],$arrDNC) ){
 $black++;
 continue;
 }elseif( $arrExist[$val[$field_key["phone1"]]] == 'Y'){
 $repeat++;
 continue;
 }elseif( $val["phone_length"] < '11' || $val["phone_length"] > '13'){
 $phone_length++;
 continue;
 }else{
 if( is_numeric($val[$field_key["phone1"]]) ){
 //$valid++;
 if(!$str){
 $str = "(";
 }else{
 $str .= ",(";
 }
 
 for($i=0;$i<=($count-2);$i++){
 $str .= "'" .str_replace("'","",$val[$i]). "',"; 
 }
 $str .= "'" .date("Y-m-d H:i:s"). "'"; 
 $str .= ")";


 //echo $sql;break;
 //$result = $source->execute($sql);
 
 if($key % 1000 == 0){  //每千行插入一次   每插入一次休眠0.1s

$sql = "insert into $table($fields) values ".$str;
 $result = $conn->query($sql);
 unset($str);

usleep(100000);
 $intint = ceil($key / $count_rows * 100);
 $progress_file = fopen('./Upload/progress/'.$now_time.'.txt','w');
 fwrite($progress_file,$intint.'%');
 fclose($progress_file);
 }elseif($count_rows - $key == 1){
 //echo $str;exit;
 $sql = "insert into $table($fields) values ".$str;

$result = $conn->query($sql);

unset($str);
 $intint = ceil($key / $count_rows * 100);   //生成进度信息供前端读取
 $progress_file = fopen('./Upload/progress/'.$now_time.'.txt','w');
 fwrite($progress_file,$intint.'%');
 fclose($progress_file);
 }
 if($result){
 $num += 1;
 }
 
 }
 
 
 }

//unlink('./Upload/progress/'.$now_time.'.txt');
 if( $num > 0){
 //$total = count($arrPhones);
 //$num = $result+$result2+$result3+$result4+$result5+$result6+$result7+$result8+$result9;
 $fail = $count_rows - $num;
 
 $zzz = json_encode(array('code'=>1,'success'=>true,'msg'=>"导入成功!总共处理${count_rows}个号码,${black}个在黑名单中,${repeat}个重复号码,${invalid}个非法号码,最后成功导入${num}个号码!,长度不符${phone_length}"));
 $progress_file = fopen('./Upload/progress/'.$now_time.'.txt','w');
 fwrite($progress_file,$zzz);
 fclose($progress_file);
 sleep(3);
 unlink('./Upload/progress/'.$now_time.'.txt');
 unlink('./Upload/progress/upload.lock');
 }else{
 $zzz = json_encode(array('code'=>0,'msg'=>'您导入了重复的号码或者导入号码出现未知错误!'));
 $progress_file = fopen('./Upload/progress/'.$now_time.'.txt','w');
 fwrite($progress_file,$zzz);
 fclose($progress_file);
 sleep(3);
 unlink('./Upload/progress/'.$now_time.'.txt');
 unlink('./Upload/progress/upload.lock');
 }

}




发表评论

电子邮件地址不会被公开。 必填项已用*标注