SQL语句结合后台处理大量数据下的数据导出

需求是，在一个两三千万的日志表中，导出指定过滤条件下，有关的卡号日志信息，且日志信息按照卡号的顺序排序，同时卡号和产品ID相同时只取时间最大的那条。

刚拿到此需求时，写SQL语句直观的处理方式容易是：根据卡号和产品ID两个字段分组，然后根据卡号字段排序，最后将结果集输出，具体SQL语句在此不表。

但实际将SQL语句写好后，由于数据量庞大，分组后排序，或者先排序再分组，都会导致执行语句时间过长而无法及时响应的情况，少则半小时，多则一个多小时。

后经过思考，发现有几个点可以突破：

1、在这张表根据卡号排序，由于数据量非常庞大，排序时间也必将不少，可以通过在卡号表中过滤后排序，因为卡号表的数据肯定比日志表的数据少很多，由此可以节省一定的时间。

2、在卡号和产品ID相同时只取时间最大的那条，经过问询产品经理，实际体现的是覆盖思想，在此时间段只取一条，不会取该条的其它字段，因而时间上可以不用分组，再根据时间排序再取最大记录，直接加关键字DISTINCT取所要的字段即可，这里有卡号、类型、产品ID。

在这里，经过SQL语句的优化处理，效率是得到了大幅提升，不过还需要考虑到查询到的数据，依然可能会很大，例如两千万，因此设计用多个文件来保存查询结果集。考虑每个文件的装载量，采用了txt保存，于是打算每个文件保存60万条数据。先将数据结果集都获取下来，然后分成多个数据结果集，每个存有60万条数据，但由此引发了下面的问题：

因为数据量太大，采用实体对象映射的mybatis会产生内存溢出异常，从而导致功能无法实现。

于是又思考其它方式，既然不能一次全部获取，那么反其道而行之，能不能一批批的获取数据结果集，这样就不存在内存溢出的问题了啊，然后就有了下面的解决思路：

1、先执行SQL得到过滤条件下的数据量，再用总数量除以60万，就可以知道要拆分成多少个文件了，并可调试代码来大致估算每个文件的生成时间是多久，以便提示用户可能的耗时。获取总数量的方式如下，注意加了DISTINCT关键字：

<select id="getAllInfoCount" resultType="long">
		select count(DISTINCT TerminateID,AuthorizeType,PackageOrServiceID) from PackageAndServiceAuthorizeLog 
		<where>
			<if test="numOne != null and numOne != 0">
				and areaId=#{numOne}
			</if>
			<if test="type != -1">
				and authorizeType=#{type}
			</if>
			<if test="utcTime1 != null">
				and sendTime&gt;=#{utcTime1}
			</if>
			<if test="utcTime2 != null">
				and sendTime&lt;=#{utcTime2}
			</if>
		</where>
	</select>

2、在原有的过滤条件基础上，再添加一个字段，用来将总数据集分割成多个文件，理所当然这里就用卡号了，由此发现上面提到的第1点措施，在这里也起到了作用，而且上面的卡号是已经经过排序的了。

但到这里有个问题，因为卡号并不连续，所以在进行排序时，还需要设法将结果集变成连续的卡号序号和卡号的形式，这样处理的话，就用卡号序号字段来进行关联分割。到了这里就有了根据卡号表来排序分割的基本结果集SQL语句：

SELECT @rownum :=@rownum + 1 AS rownum, m.chipid cardid
			FROM (SELECT @rownum := 0) r, GOS_CAS.TerminalInfo m
			WHERE m.`Status` = "已激活"
			ORDER BY cardid

得到的执行结果大致如下如：

然后，where语句通过rownum进行关联，同时上面的cardid等于卡号，这样就能每次取指定卡号数量，当然，这样的话不能保证每个取出来的结果集是准确的60万，可能会有波动，但基本上相差不大。

于是，最后的Mybatis代码就如下：

<!-- 所有信息 -->
	<select id="selectAllInfoFile" resultType="com.domain.EntitlementLog">
		SELECT DISTINCT TerminateID,AuthorizeType,PackageOrServiceID
		FROM PackageAndServiceAuthorizeLog INNER JOIN (
			SELECT @rownum :=@rownum + 1 AS rownum, m.chipid cardid
			FROM (SELECT @rownum := 0) r, GOS_CAS.TerminalInfo m
			WHERE m.`Status` = "已激活"
			ORDER BY cardid
		) AS terminalInfo 
		ON terminalInfo.cardid = PackageAndServiceAuthorizeLog.TerminateID
		<where>
			<if test="numOne != null and numOne != 0">
				and PackageAndServiceAuthorizeLog.areaId=#{numOne}
			</if>
			<if test="type != -1">
				and PackageAndServiceAuthorizeLog.authorizeType=#{type}
			</if>
			<if test="utcTime1 != null">
				and PackageAndServiceAuthorizeLog.sendTime&gt;=#{utcTime1}
			</if>
			<if test="utcTime2 != null">
				and PackageAndServiceAuthorizeLog.sendTime&lt;=#{utcTime2}
			</if>
			<if test="startCardID != null">
				and terminalInfo.rownum&gt;#{startCardID}
			</if>
			<if test="endCardID != null">
				and terminalInfo.rownum&lt;=#{endCardID}
			</if>
		</where>
	</select>

至此，多个文件已经生成，不过我们还需要将多个文件压缩为一个压缩包，这样就返回一个文件路径从而方便下载。当然，值得注意的是，有可能我们的过滤条件下得到的数据并不多，那就这样操作其实是会多浪费时间的，从上面的代码也可以看出每次都要卡号表排序然后关联查询，导致每次都要遍历所有的卡号。所以，如果最开始知道结果数据不多，就直接在日志表获取反倒更好，于是核心java代码大致如下：

 long count = enMapper.getAllInfoCount(AreaID, type, Util.TimeToUTC(startTime), Util.TimeToUTC(endTime));
			if(count > 600000 && force == false){
				map.put("code", 1);
				map.put("tips", count);
			}else{
				if (count > 0) {
					List<EntitlementLog> list6;
					com.gospell.cam.domain.CardRelated cardInfo = enMapper.getTerminalRelevantInfo();
					long fileSum = (long)Math.ceil(count/600000.0); //每个文件保存60万条记录，得到要保存多少个文件
					long eachSum = cardInfo.getCount()/fileSum; //每个文件包含卡号的数量
			    	for(int j = 1; j <= fileSum; j++) {
			    		if (fileSum == 1) { //文件数很少，直接在授权日志表中进行排序处理后导出
							list6 = enMapper.selectAllInfo(AreaID, type, Util.TimeToUTC(startTime), Util.TimeToUTC(endTime));
						}else{
							list6 = enMapper.selectAllInfoFile(AreaID, type, Util.TimeToUTC(startTime), Util.TimeToUTC(endTime), eachSum*(j-1), eachSum*j);
						}
			    		//所有信息
				    	File file6 = new File("BACKUP/Export/AllInfo/AllInfo" + j + ".txt");
				    	if (file6.exists()) {
							file6.delete();
						}
					    FileWriter fw6 = new FileWriter(file6, true);
					    PrintWriter pw6 = new PrintWriter(fw6);
						for (int i = 0; i < list6.size(); i++) {
							EntitlementLog log = list6.get(i);
							pw6.print(log.getTerminateId()+",");
							if (log.getAuthorizeType()==0) {
								pw6.print("PackageID:");
							}else if (log.getAuthorizeType()==1) {
								pw6.print("ServiceID:");
							}else {
								pw6.print("PackageOrServiceID:");
							}
							pw6.print(log.getPackageOrServiceId());
							pw6.println("");
						}
						pw6.flush();
				    	fw6.flush();
				    	pw6.close();
				    	fw6.close();
					}
			    	//第一个参数是需要压缩的源路径；第二个参数是压缩文件的目的路径，这边需要将压缩的文件名字加上去
			    	Util.compress("/GOS_CAS/BACKUP/Export/AllInfo/","/GOS_CAS/BACKUP/Export/AllInfo.zip");
			    	map.put("code", 2);
					map.put("tips", fileSum);
				}
			}

对应的数据量很少时采用的selectAllInfo方法，Mybatis代码如下：

<!-- 所有信息 -->
	<select id="selectAllInfo" resultType="com.domain.EntitlementLog">
		select DISTINCT TerminateID,AuthorizeType,PackageOrServiceID from PackageAndServiceAuthorizeLog 
		<where>
			<if test="numOne != null and numOne != 0">
				and areaId=#{numOne}
			</if>
			<if test="type != -1">
				and authorizeType=#{type}
			</if>
			<if test="utcTime1 != null">
				and sendTime&gt;=#{utcTime1}
			</if>
			<if test="utcTime2 != null">
				and sendTime&lt;=#{utcTime2}
			</if>
		</where>
		ORDER BY TerminateID
	</select>

最后的最后，这里为了界面和java代码的交互逻辑，放上JS代码

$.messager.confirm("-","Maybe take a long time,Are you sure?",function(flag){
		if(flag){
			var start = $("#pStartTime").datetimebox("getValue");
			var end = $("#pEndTime").datetimebox("getValue");
			if(compareDate(start,end)){
				$.messager.alert($("#prompt").val(),$("#timeWarn").val(),"warning");
				return;
			}else{
				tipCreateFile();
				//生成总体统计报告
				tioDownloadFile();
				$.post(getUrl()+"/creatAllInfo.do?start="+start+"&end="+end+"&pn="+pn+"&proOrSer="+proOrSer+"&areaId="+areaId+"&force=false",function(data){
					if (data.code == 1) {
						 $.messager.confirm("-",'There are ' + data.tips + ' data in the send time segment, when the data exceeds 600000, it will be exported in batches, which may take dozens of minutes. You can also reset query conditions or click OK to continue!',function(flag){
							if(flag){
								$.post(getUrl()+"/creatAllInfo.do?start="+start+"&end="+end+"&pn="+pn+"&proOrSer="+proOrSer+"&areaId="+areaId+"&force=true",function(data2){
//										var address = data2.tips;
									clearTip();
									window.location.href = getUrl()+"/downloadAll.do";
								});
							}
						});
					}else if(data.code == 2){
						clearTip();
						window.location.href = getUrl()+"/downloadAll.do";
					}else if(data.code == 0){
						clearTip();
						$.messager.alert('prompt','No data, please re-select the condition!',"error");
					}else{
						clearTip();
						$.messager.alert('prompt','Failed!',"error");
					}
				});
			}
		}
	});

可以看到，当数据量很多时，进行了总数据量提示，要么多等一会儿，要么继续，至此完成了此需求。