PHP 高效导出百万以上数据 Excel(CSV) [ 2.0 版本 ]

如果你导出的Excel没有什么高级用法的话，只是做导出数据用那么建议使用本方法,要比PHPexcel要高效的多。
这边用的是用的多进程的方式来完成导出的。比如我现在要导出200W数据，我就按20W数据分配一个子进程的方式，这样就需要分配10个子进程， 每个进程完成20W数据的导出。然后把导入的后的数据合并下就完成了200W数据的导入。看下面具体代码：

<?php
namespace console\controllers;

use Yii;
use yii\console\Controller;
use common\helpers\CommonFun;

class ExportController extends Controller
{
    protected $csvPath;
    protected $user;
    protected $size = 1000;
    protected $step = 200000;

    public function beforeAction($action)
    {
        parent::beforeAction($action);
        $this->csvPath = dirname(Yii::$app->basePath) . '/csv';
        $this->user = 'user';
        return true;
    }

    //多进程的脚本
    public function actionSend($count)
    {
        if (!$count) exit('count is zero');
        $taskStartTime = microtime(true);
        //创建目录
        if (!file_exists($this->csvPath)) {
            CommonFun::recursionMkDir($this->csvPath);
        }
        $totalNum = ceil($count/$this->step);
        $childs = array();
        for($i=0; $i< $totalNum; $i++) {
            $pid = pcntl_fork();
            if ($pid == -1) {
                exit('Could not fork');
            } elseif($pid) {
                echo "I'm the Parent $i\n";
                $childs[] = $pid;
            } else {
                //子进程处理业务
                $this->handelChildProcess($i,$count);
            }
        }

        while (count($childs) > 0) {
            foreach ($childs as $key => $pid) {
                $res = pcntl_waitpid($pid, $status, WNOHANG);
                //-1代表error, 大于0代表子进程已退出,返回的是子进程的pid,非阻塞时0代表没取到退出子进程
                if ($res == -1 || $res > 0) {
                    echo "$key=> $pid\n";
                    unset($childs[$key]);
                }
            }
            sleep(1);
        }
        $lastTime = $this->getElapsedTime($taskStartTime);
        Yii::info("toallastTime|"  . $lastTime, __METHOD__);
        exit("success|$lastTime");
    }

    public function getRows($start) {
        $size = $this->size;
        $end = $start + $size;
        $userList = Yii::$app->db_uums->createCommand('SELECT  field1,field2,field3,field4, field5  FROM `user` where (field4=2 or field4=4) LIMIT '. $size .  ' OFFSET '  . $start)->queryAll();
        foreach ($userList as  $value) {
            yield $value;
        }
    }

    public function handelChildProcess($processKey, $totalCount)
    {
        echo "process $processKey start \n";
        $taskStartTime = microtime(true);
        $pageTotal = ceil($this->step/$this->size);
        for ($i=1; $i <= $pageTotal; $i++) {
            //计算起始位置
            $start = $processKey * $this->step + ($i-1) * $this->size;
            if ($start > $totalCount) {
                $lastTime = $this->getElapsedTime($taskStartTime);
                Yii::info("lastTime|process" . $processKey . $lastTime, __METHOD__);
                echo "process $processKey end\n";
                exit('超过总数了');
            }
            $userList = $this->getRows($start);
            foreach ($userList as $key => $value) {
                $this->writeRow($value, $this->csvPath . '/' . $this->user . $processKey . '.csv');
            }
            sleep(1);
        }
        $lastTime = $this->getElapsedTime($taskStartTime);
        Yii::info("lastTime|process" . $processKey . '|' . $lastTime, __METHOD__);
        echo "process $processKey end\n";
        exit($lastTime);
    }

    public function getElapsedTime($startTime) {
        $endTime = microtime(true);
        $elapsedTime = number_format($endTime- $startTime, 4);
        return $elapsedTime;
    }


    public function writeRow($row, $file) {
        $row = array_map(function($v){
            return iconv('utf-8', 'gbk//IGNORE', $v);
        },$row);
        $handle = fopen($file,'a');
        fputcsv($handle, $row);
    }
}

执行代码：

php yii export/send 2000000

经测试导入200W数据只花了不到220s，看下面测试结果

I'm the Parent 0
process 0 start 
I'm the Parent 1
process 1 start 
process 2 start 
I'm the Parent 2
I'm the Parent 3
process 3 start 
process 4 start 
I'm the Parent 4
I'm the Parent 5
process 5 start 
process 6 start 
I'm the Parent 6
I'm the Parent 7
process 7 start 
process 8 start 
I'm the Parent 8
process 9 start 
I'm the Parent 9
process 1 end
220.7269process 2 end
220.7191process 0 end
221.1873process 7 end
220.44370=> 47965
1=> 47966
2=> 47967
7=> 47972
process 5 end
221.4519process 3 end
221.7290process 6 end
221.4796process 8 end
221.38053=> 47968
5=> 47970
6=> 47971
8=> 47973
process 4 end
222.59114=> 47969
process 9 end
222.75869=> 47974
success|225.7930% 

导出的数据：

➜  csv git:(master) ✗ wc -l *     
   200199 user0.csv
   200200 user1.csv
   200200 user2.csv
   200200 user3.csv
   200202 user4.csv
   200200 user5.csv
   200200 user6.csv
   200200 user7.csv
   200200 user8.csv
   200206 user9.csv
  2002007 total