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

最新推荐文章于 2024-03-03 21:47:33 发布

Amy安

最新推荐文章于 2024-03-03 21:47:33 发布

阅读量2.4k

点赞数

分类专栏： PHP 文章标签： mysql sql server 数据

本文链接：https://blog.csdn.net/u014294325/article/details/56006803

版权

PHP 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

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

数据大概是这样的：
这里写图片描述

这里写图片描述

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

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

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

这里需要注意的是在目录中除了要处理的文件再没有其他文件夹的情况下仍会多遍历2次
var_dump( $f i l e s) 会发现该数组中会包含两个名为 “ . ” 和 “ . . ” 的元素若再处理文件时需要用到 ‘$ 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.若要使用浏览器多次跳转需要考虑重定向限制问题。处理方式不唯一，该问题也是完全可以回避的。