【记录】百万量级数据处理

左手的ㄟ右手 2022-07-12 09:20 181阅读 0赞

某次需求将一个文件夹内的几千个文件中的数据导入到SqlServer，每个文件有1000+条数据  
基本想法是用php遍历一下读出来再写进MySQL（SqlServer是服务器上的不对外，同在服务器的MySQL对外），最后从MySQL导入到SqlServer。

数据大概是这样的：  
![这里写图片描述][SouthEast]

![这里写图片描述][SouthEast 1]

理想很丰满 现实很骨感  
在简单的思考后开始着手去做 结果碰到了不少坑 在这里记录一下并标记一些细节地方

首先想到对数据库进行基本的处理  
将每个文件中多余的第一行及最后一行去掉后另外保存  
将每行数据规范化

$files= scandir($dir);
      for($i=0;$i<count($files);$i++){
        //handlefile
      }

这里需要注意的是 在目录中除了要处理的文件再没有其他文件夹的情况下 仍会多遍历2次  
var\_dump( f i l e s ) 会 发 现 该 数 组 中 会 包 含 两 个 名 为 “ . ” 和 “ . . ” 的 元 素 若 再 处 理 文 件 时 需 要 用 到 ‘ files)会发现该数组中会包含两个名为“ . ”和“..”的元素 若再处理文件时需要用到\` files)会发现该数组中会包含两个名为“.”和“..”的元素若再处理文件时需要用到‘i\`则需考虑先处理下数组 剔除掉待处理文件以外的元素

处理好后得到纯净的数据文本 接下来只要循环读取然后写进数据库就可以了

public function insert()
    	{
    		$fall =fopen("./file/data/all.txt",'r');
    		$i=0;
    		$c = 0;
    		while(!feof($fall)){
    			
    			$i++;
    			$code = fgets($fall);
    			
    			$c = doit($i,$c,$code);
    			$c++;
    			
    			ob_flush();flush();
    		}
    
    		fclose($fall); 
    		echo "OK";      
    
    	}
    
    	function doit($i,$c,$code){
    		$gcode = $code;
    		$dir="./file/data/d/";
    		$files= scandir($dir);
    		$dir2="./file/data/c/";
    		if((int)$files[$c] !=0){
    
    			$fname = $dir.$files[$c];
    			$f1=fopen($fname,'r');
    			while(!feof($f1)){
    
    				$line=fgets($f1);
    		
    				$datas = explode(' ',$line);//这里按空格分割
    				if(isset($datas[0]) || 
    				isset($datas[2]) || 
    				isset($datas[3]) || 
    				isset($datas[4]) || 
    				isset($datas[5]) || 
    				isset($datas[6])){ 
    				
    					//do INSERT
    					
    					if($e = mysql_error()){
    						echo $e;
    						exit;
    					}
    				}
    				ob_flush();flush();
    			}
    		}else{
    			$c++;
    			doit($i,$c,$code);
    		}	
    		return $c;
    	}

（实际情况复杂了一点 用了递归）

随后便出现了MySQL以及PHP的内存问题及超时问题  
在网上查资料修改了一些MySQL及PHP的配置  
在代码中加入了

ini_set('max_execution_time', '0');
    		ini_set('memory_limit','2048M');
    		set_time_limit(0);

`mysql> set global max_allowed_packet = 2*1024*1024*10`

解决后继续 花了几个小时终于导完了

做了一下简单的查询发现了问题 有一个字段固定只有6位字符的 但按 where code=‘xxxxxx’查询查询不到 按code like ‘%xxxxxx%’却可以 那么这里面一定夹杂了看不见的字符 查 len(code)发现有8位…  
那么这个问题是怎么产生的呢  
前面的数据可以看到 用文本文档打开或用notepad这类编辑器打开看到的都是那样 我便以为里面同时包含空格和tab制表符 而实际上全都是tab。。 *(:зゝ∠)*  
于是在处理时造成了上面的问题

最后折腾一番用SSMA从MySQL导入到了SqlServer，这里要注意SSMA的版本 太低的版本会识别不了高版本的MySQL ODBC驱动程序 | 如SSMA 2008就识别不了MySQL5.1 的驱动只能识别3.5的

最后当然要检查数据了 做了一些查询对照原始数据。。 结果发现有一部分差的很远且没有规律 27.21这样的数会变成8846.95这样的。。。/(ㄒoㄒ)/~~

一步步反推回来看发现 在第二步处理数据时就出现这个问题了即从一个标题文件中循环读取标题然后每个标题对应数据文件夹中的一个文件的全部数据 缘由不明 总之需要重新开始了

经过首次尝试后 发现数据写入数据库的耗时太长 平均一秒钟只写进30条 后修改了方案  
当然再此之前对数据做了准确的规范化处理

ob_start();
    		error_reporting(E_ALL);
    		ini_set('display_errors', '1');
    		ini_set('output_buffering', 'On');
    		ini_set('max_execution_time', '0');
    		ini_set('memory_limit','2048M');
    		set_time_limit(0);
    		
    		$num = 0;
    		$add_num = 50;
    		$num = $_REQUEST['n'];
    		$sql = "INSERT INTO table (v1,v2,v3,v4,v5,v6,v7,v8) VALUES ";
    		$dir="./file/data/b/";
    		$i=1;
    		
    		$files = scandir($dir);
    		sort($files);
    		if($num+$add_num>count($files)){
    			$nnum = count($files);
    		}else{
    			$nnum = (int)$num+(int)$add_num;
    		}
    		
    		
    		for($i=$num;$i<$nnum;$i++){
    			if(strlen($files[$i])>5){
    				$fname = $dir.$files[$i];
    				$f1=fopen($fname,'r');
    				while(!feof($f1)){
    
    					$line=fgets($f1);
    
    					$datas = explode('	',$line);//这里以Tab分割
    					if(isset($datas[0]) || isset($datas[1]) || isset($datas[2]) || isset($datas[3]) || isset($datas[4]) || isset($datas[5]) || isset($datas[6])){ 
    						$sql .= "('".$datas[0]."','".$datas[1]."','".$datas[2]."','".$datas[3]."','".$datas[4]."','".$datas[5]."','".$datas[6]."','".substr($files[$i],0,6)."'),";
    
    					}
    				
    				}
    			}
    			//ob_flush();flush();	
    		}
    		$sql = substr($sql,0,strlen($sql)-1);
    
    		mysql_query（$sql）;
    
    		if($e = mysql_error()){
    			echo "<br>==========================================<br>";
    			echo $e;
    			echo "<br>==========================================<br>";
    			exit;
    		}
    		header('Location:'.MURL.'&n='.$i);
    		ob_end_flush();

每读取50个文件的数据组成一个SQL语句提交一次执行然后再跳转当前页带参数以50递增  
每50个文件的量大约只花了3-4秒就导入进去了

实际运行后又发现问题 当页面刷到`$i`为1050后 即20次后停止了 从1050再开始后到2100又停止了跳转  
这应该是浏览器的重定向限制问题 经查询果然如此

浏览器版本 限制次数  
chrome 20  
opera 20  
safari 15  
firefox 20  
ie7 8 10  
ie9 110  
（我用的chrome）

这里的想到的方案是每20次后新开一个窗口打开 然后关掉旧窗口（JS实现）  
由于该案例文件数还不是很多 手动3次就好了  
随后检查数据 存在的数据都没有问题 虽然此前已设置了字段都不允许为空 但每个文件的数据导入完会多出一条空白数据只有标题有值 其余为空值 后发现每个文件的最后都多了一行空行，而这个空行用count(file($filename))统计是不会算在里面的 但指针却会指向

最后删除了等同文件数的空记录数

大功告成 *(:зゝ∠)*

PS：这次百万量级的数据导入还是碰到了各个层面上不少问题的 也获得了许多经验  
由于时间限制 处理用的程序没有进行进一步的优化 期间还试图尝试用C\#或Java实现但也碰到一些问题 为免过杂 最终不了了之  
总览整个过程 日后考虑用CSV代替TXT存储数据 用Python来实现文件IO操作很快， 然后开多线程访问PHP或.Net页面进行数据库插入，同时还可以显示进度，当然Python操作数据库也是可以的。方法很多，视具体需求来用。

**总结经验：**  
1.大量数据导入前要对原始数据做准确的规范化处理 源文件是TXT的要特别注意分隔符。可使用逗号分号及tab制表符不建议使用空格。

2.按行读取时 文件中的空行会被指针指向读取出来 但统计行数时不会包含在内。若后续处理需要依据行数可考虑处理掉空行。用C\#的话可以整个文件一次性读进DataTable且不会有空行问题。

3.遍历文件夹内的文件会多出不属于文件的部分，要注意过滤。对于会重复访问遍历的数组要进行排序以保证顺序永远一致。

4.递归函数的传入参数不要直接使用，先赋值给一个变量，在函数中使用那个变量。（这其实也是规范）

5.长时间执行PHP或执行数据库操作要考虑**内存，缓存及超时**问题，进行合理的内存容量调整和超时配置，控制好缓存的释放（IIS中设置CGI的超时时间过长会引发电脑高度频繁卡顿，从任务管理器CPU和内存的消耗情况中看不出问题）。另外长时间的执行是否是正常现象也需要考虑，若不合理就要调整代码了。

6.若插入的数据有可依据的字段 可先将这批字段值插入数据库作为索引。能加快执行速度。

7.不同类数据库之间数据的传输善用工具 要考虑工具本身及数据库驱动程序的各类问题。

8.若要使用浏览器多次跳转需要考虑重定向限制问题。处理方式不唯一，该问题也是完全可以回避的。

[SouthEast]: /images/20220710/d49ff8b613c847138a9ccf7de5eebc35.png
[SouthEast 1]: /images/20220710/125eb1095ee64f4ea9805252f7e2a49a.png