[PHP知识库]php查询mysql大批量数据导出excel

背景

公司的管理后台的列表导出 excel 功能，一次性导出上万条数据就会崩溃报错，而列表查询一天的数据行可能就有十几万条，无法满足需求，所以需要优化。

报错分析

运行环境：LNMP（ThinkPHP5框架）

代码：

    public function exportExcel()
    {
        //set_time_limit(0); //调试步骤1
        //ini_set('memory_limit', '-1'); //调试步骤2
        $data = $this->index(true); //mysql数据库返回的数据
        //var_dump(1);exit(); //调试步骤3
        $rows = [];
        foreach ($data as $val) {
            //导出前对数据做一些处理
        }
        $excel = new Excel(); //调用 excel 插件
        $excel->setColumnMean([])->setData($rows)->download('用户列表-'.date('YmdHis'));
    }

1.直接导出接近 100 万条数据

超过了设置的内存限制值

2.如上代码，打开调试步骤3，看是否在查询数据库这一步报错的

报错结果同上，说明在查询数据库这一步超过了设置的内存限制值

3.代码恢复，打开调试步骤2，不限内存，再导出

报 404，应该是执行超时了

4.代码恢复，打开调试步骤2 和 3，再导出

还是报 404，说明在查询数据库这一步已经执行超时了

5.代码恢复，打开调试步骤1 和 2，不限执行时间不限内存，再导出

报 404

6.代码恢复，打开调试步骤1 和 2 和 3，再导出

报 404

问题总结：

mysql 一次性查询大批量数据，加载到 php 内存，导致内存溢出并且执行容易超时。

解决办法

关键词：非缓冲查询

    public function exportExcel()
    {
        $head = []; //excel 内容表头字段
        $sql = $this->index(true); //返回待执行的 sql 语句
        $this->putCsv($sql, '用户列表'.date('YmdHis'), $head); //导出 csv 文件
    }

    private function putCsv($sql, $mark, $head)
    {
        set_time_limit(0); //不限执行时长，避免超时

        try {
            $filePath = ROOT_PATH . DS . 'runtime' . DS . 'temp' . DS . $mark . '.csv';
            $fp = fopen($filePath, 'w'); //生成临时文件
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));//转码,防止乱码
            fputcsv($fp, $head);

            $dbConfig = config('db_data'); //数据库具体配置
            $dsn = "mysql:dbname={$dbConfig['database']};host={$dbConfig['hostname']};port={$dbConfig['hostport']}";
            $pdo = new \PDO($dsn, $dbConfig['username'], $dbConfig['password']); //直接使用PDO
            $pdo->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); //关键一步，使用非缓冲查询
            $rows = $pdo->query($sql);

            if ($rows) {
               while ($v = $rows->fetch(\PDO::FETCH_ASSOC)) {
                   //导出前对数据做一些处理
                   fputcsv($fp, $v);
               }
            }
            fclose($fp);

            //输出压缩文件提供下载
            header("Cache-Control: max-age=0");
            header("Content-Description: File Transfer");
            header('Content-Disposition: attachment; filename=' . basename($filePath)); //文件名
            header("Content-Type: text/csv;charset=utf8");
            header("Content-Transfer-Encoding: binary");
            header('Content-Length: ' . filesize($filePath));
            @readfile($filePath); //输出文件
            unlink($filePath); //删除临时文件
        } catch (\Exception $e) {
            Log::error('用户列表导出报错:'.$e->getMessage());
            if (isset($filePath) && file_exists($filePath)) unlink($filePath); //删除临时文件
            $this->error('导出失败，请稍后再试');
        }
    }

原本代码里 sql 查询是用的 tp5 封装的 db 库，db 库其实也是对 PDO 的再封装（看下面 tp5 的代码）。db 库默认采用的缓冲查询，即每次都将查询结果一次过加载到 php 内存中。而非缓冲查询则是逐一加载到 php 内存中，适合查询大批量数据的场景。具体参考官方文档：Buffered and Unbuffered queries

thinkphp/library/think/db/Query.php

    /**
     * 查找记录
     * @access public
     * @param array|string|Query|\Closure $data
     * @return Collection|false|\PDOStatement|string
     * @throws DbException
     * @throws ModelNotFoundException
     * @throws DataNotFoundException
     */
    public function select($data = null)
    {
        if ($data instanceof Query) {
            return $data->select();
        } elseif ($data instanceof \Closure) {
            call_user_func_array($data, [ & $this]);
            $data = null;
        }
        // 分析查询表达式
        $options = $this->parseExpress();

        if (false === $data) {
            // 用于子查询 不查询只返回SQL
            $options['fetch_sql'] = true;
        } elseif (!is_null($data)) {
            // 主键条件分析
            $this->parsePkWhere($data, $options);
        }

        $resultSet = false;
        if (empty($options['fetch_sql']) && !empty($options['cache'])) {
            // 判断查询缓存
            $cache = $options['cache'];
            unset($options['cache']);
            $key       = is_string($cache['key']) ? $cache['key'] : md5($this->connection->getConfig('database') . '.' . serialize($options) . serialize($this->bind));
            $resultSet = Cache::get($key);
        }
        if (false === $resultSet) {
            // 生成查询SQL
            $sql = $this->builder->select($options);
            // 获取参数绑定
            $bind = $this->getBind();
            if ($options['fetch_sql']) {
                // 获取实际执行的SQL语句
                return $this->connection->getRealSql($sql, $bind);
            }

            $options['data'] = $data;
            if ($resultSet = $this->trigger('before_select', $options)) {
            } else {
                // 执行查询操作
                $resultSet = $this->query($sql, $bind, $options['master'], $options['fetch_pdo']);

                if ($resultSet instanceof \PDOStatement) {
                    // 返回PDOStatement对象
                    return $resultSet;
                }
            }

            if (isset($cache) && false !== $resultSet) {
                // 缓存数据集
                $this->cacheData($key, $resultSet, $cache);
            }
        }

        // 数据列表读取后的处理
        if (!empty($this->model)) {
            // 生成模型对象
            if (count($resultSet) > 0) {
                foreach ($resultSet as $key => $result) {
                    /** @var Model $model */
                    $model = $this->model->newInstance($result);
                    $model->isUpdate(true);

                    // 关联查询
                    if (!empty($options['relation'])) {
                        $model->relationQuery($options['relation']);
                    }
                    // 关联统计
                    if (!empty($options['with_count'])) {
                        $model->relationCount($model, $options['with_count']);
                    }
                    $resultSet[$key] = $model;
                }
                if (!empty($options['with'])) {
                    // 预载入
                    $model->eagerlyResultSet($resultSet, $options['with']);
                }
                // 模型数据集转换
                $resultSet = $model->toCollection($resultSet);
            } else {
                $resultSet = $this->model->toCollection($resultSet);
            }
        } elseif ('collection' == $this->connection->getConfig('resultset_type')) {
            // 返回Collection对象
            $resultSet = new Collection($resultSet);
        }
        // 返回结果处理
        if (!empty($options['fail']) && count($resultSet) == 0) {
            $this->throwNotFound($options);
        }
        return $resultSet;
    }

上面的 $this->query(); 最终跳到
thinkphp/library/think/db/Connection.php

    /**
     * 执行查询 返回数据集
     * @access public
     * @param string        $sql sql指令
     * @param array         $bind 参数绑定
     * @param bool          $master 是否在主服务器读操作
     * @param bool          $pdo 是否返回PDO对象
     * @return mixed
     * @throws PDOException
     * @throws \Exception
     */
    public function query($sql, $bind = [], $master = false, $pdo = false)
    {
        $this->initConnect($master);
        if (!$this->linkID) {
            return false;
        }

        // 记录SQL语句
        $this->queryStr = $sql;
        if ($bind) {
            $this->bind = $bind;
        }

        Db::$queryTimes++;
        try {
            // 调试开始
            $this->debug(true);

            // 预处理
            $this->PDOStatement = $this->linkID->prepare($sql);

            // 是否为存储过程调用
            $procedure = in_array(strtolower(substr(trim($sql), 0, 4)), ['call', 'exec']);
            // 参数绑定
            if ($procedure) {
                $this->bindParam($bind);
            } else {
                $this->bindValue($bind);
            }
            // 执行查询
            $this->PDOStatement->execute();
            // 调试结束
            $this->debug(false, '', $master);
            // 返回结果集
            return $this->getResult($pdo, $procedure);
        } catch (\PDOException $e) {
            if ($this->isBreak($e)) {
                return $this->close()->query($sql, $bind, $master, $pdo);
            }
            throw new PDOException($e, $this->config, $this->getLastsql());
        } catch (\Throwable $e) {
            if ($this->isBreak($e)) {
                return $this->close()->query($sql, $bind, $master, $pdo);
            }
            throw $e;
        } catch (\Exception $e) {
            if ($this->isBreak($e)) {
                return $this->close()->query($sql, $bind, $master, $pdo);
            }
            throw $e;
        }
    }

另外，改用了性能更好的 csv，亲测 csv 比 PhpSpreadsheet 更能应付大批量数据导出。上面的方法能导出几十万乃至百万条数据，但是要注意对耗时的敏感程度。