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 万条数据

1
超过了设置的内存限制值

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

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

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

2

报 404,应该是执行超时了

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

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

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

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 更能应付大批量数据导出。上面的方法能导出几十万乃至百万条数据,但是要注意对耗时的敏感程度。

  • 0
    点赞
  • 1
    收藏
  • 打赏
    打赏
  • 4
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论 4

打赏作者

康永叶

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值