首页 > 代码库 > 导出数据优化技巧

导出数据优化技巧

因为在公司业务需要,经常会有导数据的情况,有的时候需要查询统计的信息比较多,需要查询多个表去取值,有时候可能跨库到sqlserver中,sqlserver中不同的城市对应不同的库,加上数据量比较大,如果不采取合适的方案会对服务器负载影响很大,加上大家都是那个服务器上做操作,很容易使服务器崩溃。

 

所以总结了网上的一些优化技巧,使之符合公司业务需求。当然要配合sql优化。

加了进度条和脚本运行时间统计,使导数据过程更加有乐趣。

 

废话不多说,贴部分代码供大家参考:

<?php
set_time_limit(0);   //设置进行完程序才结束, 不因时间过长中断
ini_set(‘memory_limit‘, ‘-1‘); //设置脚本可以使用的内存大小为php配置的最大内存
$start_time = microtime(true); //脚本运行开始时间,~

$fp = fopen("57city.csv", ‘a+‘);
$header_data = ["大区","城市","楼盘名称","楼盘ID","物业类型","销售状态","楼盘级别","完善状态","楼盘封闭标识","已建楼栋数","封闭楼栋数"];
fputcsv($fp, $header_data);



$Db= new DbRDHandle(‘proj_table‘);
$citys = [合肥,南昌,南宁,宁波,无锡,西安,佛山,珠海,扬州,兰州,南通,厦门,徐州,昆明,惠州,昆山,北海,汕头,三亚,中山,江门,柳州,镇江,桂林,上海,长沙,常州,东莞,福州,贵阳,海南,深圳,重庆,成都,杭州,武汉,苏州,南京,广州];
//按城市连接sqlserver库(连库时间会很长,所以采取尽量少连接策略)
foreach ($citys as $item => $city){
  $sql = "............";
  $res = $Db->Query($sql,MYSQL_ASSOC);
  $count = $res[0][‘num‘] ? $res[0][‘num‘] : 0;
  $num = 0; //计数器
  //每隔$limit行,刷新一下输出buffer,不要太大也不要太小
  $limit = 100000
  $slavedbinfo = getslavedbinfo($city);
  $SqlServerRDHandle = new SqlServerRDHandle($slavedbinfo,‘proj_sql_server‘);
  //分页查询,每取1000条数据sleep 1
  for ($i = 0; $i < $count; $i += 1000) {
  $j = $i + 1000;
  $sql = "SELECT City,operastion,Saling,house_level,ProjName,Newcode FROM proj_table WHERE City=$city AND N_or_E=‘N‘ AND operastion in (‘1‘,‘2‘,‘3‘,‘4‘) AND is_approve=‘Y‘ AND Saling in (‘1‘,‘2‘) limit $i,$j";
  $res = $Db->Query($sql,MYSQL_ASSOC);
  if (is_array($res) && count($res) > 0) {
  $count = count($res);
  foreach ($res as $key => $val) {
$num++;
//刷新一下输出buffer,防止由于数据过多造成问题
if ($limit == $num) {
ob_flush();
flush();
$num = 0;
}
$sql1 = "SELECT level FROM field_~~~ WHERE newcode=‘".$val[‘Newcode‘]."‘";
$level_info = $Db->Query($sql1,MYSQL_ASSOC);
$res[$key][‘level‘] = $level_info[0][‘level‘] ? $level_info[0][‘level‘] : ‘‘;
unset($level_info);

$sql2 = "SELECT belong FROM cities WHERE city_name=‘".$val[‘City‘]."‘";
$belong_info = $Db->Query($sql2,MYSQL_ASSOC);
$res[$key][‘belong‘] = $belong_info[0][‘belong‘] ? $belong_info[0][‘belong‘] : ‘‘;
unset($belong_info);
       
        $sql3 = "SELECT sealIden FROM proj_~~~ WHERE newcode=‘".$val[‘Newcode‘]."‘";
        $sealIden_info = $Db->Query($sql3,MYSQL_ASSOC);
        $res[$key][‘sealIden‘] = $sealIden_info[0][‘sealIden‘] ? $sealIden_info[0][‘sealIden‘] : ‘‘;
        unset($sealIden_info);

        $sql4 = "SELECT count(id) AS build FROM proj_~~~ with(nolock) WHERE newcode=‘".$v[‘Newcode‘]."‘";
        $res4 = $SqlServerRDHandle->Query($sql4,‘MSSQL_ASSOC‘);
        $res[$key][‘build‘] = $res4[0][‘build‘] !== false ? $res4[0][‘build‘] : ‘‘;
        unset($res4);

        $sql5 = "SELECT count(id) AS sealIdenNum FROM proj_~~~ with(nolock) WHERE sealIden=‘Y‘ AND newcode=‘".$v[‘Newcode‘]."‘";
        $res5 = $SqlServerRDHandle->Query($sql5,‘MSSQL_ASSOC‘);
        $res[$key][‘sealIdenNum‘] = $res5[0][‘sealIdenNum‘] !== false ? $res5[0][‘sealIdenNum‘] : ‘‘;
        unset($res5);
        $row = [];
        $row[] = $res[$key][‘belong‘];
        $row[] = $res[$key][‘City‘];
        $row[] = $res[$key][‘ProjName‘];
        $row[] = $res[$key][‘Newcode‘]."\t";
        $row[] = $operastion[$res[$key][‘operastion‘]];
        $row[] = $saling[$res[$key][‘Saling‘]];
        $row[] = $res[$key][‘house_level‘];
        $row[] = $level[$res[$key][‘level‘]];
        $row[] = $sealIden[$res[$key][‘sealIden‘]];
        $row[] = $res[$key][‘build‘];
        $row[] = $res[$key][‘sealIdenNum‘];
        fputcsv($fp, $row);


        unset($row);
        unset($res[$key]);
        //数据导出百分比
       $percent = intval((($key+1) / $count) * 100);
       //隐藏光标,显示进度条
       printf("\033[?25lmprogress: \033[41m\033[1m %d%% %s\r\033[0m", $percent, str_repeat(‘ ‘, $percent));

      }
    }
  }
  sleep(1);

}
fclose($fp);
echo "\n";
if ($start_time != ‘‘) {
//输出运行时间
echo "runtime: ";
$time_diff = microtime(true) - $start_time;
$runtime = secToTime($time_diff);
echo $runtime . "\n";
}
echo "Done.\n";
printf("\033[?25h"); //显示光标

/**
* @param $time
* @return bool|string
*/
function secToTime($time)
{
if (is_numeric($time)) {
$value = [‘days‘=>0, ‘hours‘=>0, ‘minutes‘=>0, ‘seconds‘=>0];
if ($time >= 86400) {
$value[‘days‘] = floor($time / 86400);
$time = $time % 86400;
}
if ($time >= 3600) {
$value[‘hours‘] = floor($time / 3600);
$time = $time % 3600;
}
if ($time >= 60) {
$value[‘minutes‘] = floor($time / 60);
$time = $time % 60;
}
$value[‘seconds‘] = round($time, 3);
$runtime = $value[‘days‘] . . $value[‘hours‘] . 小时. $value[‘minutes‘] . . $value[‘seconds‘] . ;
return $runtime;
} else {
return false;
}
}
 
 




 

导出数据优化技巧