PHP + XlsWriter百万数据导入导出方案：性能优化与代码实战

<?php
class LargeDataExporter
{
    private $config = [
        'memory_limit' => '2048M',
        'max_execution_time' => 0,
        'chunk_size' => 50000,
        'temp_dir' => __DIR__ . '/temp/'
    ];

    publicfunction exportToExcel($dataSource, $filename)
    {
        // 1. 设置环境
        ini_set('memory_limit', $this->config['memory_limit']);
        set_time_limit(0);

        // 2. 使用流式写入
        $excel = new \Vtiful\Kernel\Excel([
            'path' => $this->config['temp_dir']
        ]);

        // 3. 分块写入数据
        $filePath = $this->config['temp_dir'] . $filename;
        $fileObject = $excel->fileName($filePath, 'sheet1');

        // 写入表头
        $headers = ['ID', '姓名', '邮箱', '电话', '地址'];
        $fileObject->header($headers);

        // 4. 分批获取数据并写入
        $offset = 0;
        while ($chunkData = $dataSource->getChunk($offset, $this->config['chunk_size'])) {
            $fileObject->data($chunkData);
            $offset += $this->config['chunk_size'];

            // 释放内存
            unset($chunkData);
            gc_collect_cycles();
        }

        // 5. 输出文件
        $fileObject->output();

        // 6. 分段下载
        $this->streamDownload($filePath);
    }

    privatefunction streamDownload($filePath)
    {
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . basename($filePath) . '"');
        header('Cache-Control: max-age=0');

        $chunkSize = 1024 * 1024; // 1MB chunks
        $handle = fopen($filePath, 'rb');

        while (!feof($handle)) {
            echo fread($handle, $chunkSize);
            ob_flush();
            flush();
        }

        fclose($handle);

        // 删除临时文件
        unlink($filePath);
    }
}

<?php
class LargeDataImporter
{
    private $config = [
        'chunk_size' => 20000,
        'batch_insert_size' => 1000,
        'temp_dir' => __DIR__ . '/temp/'
    ];

    publicfunction importFromExcel($filePath)
    {
        // 1. 分片读取Excel
        $excel = new \Vtiful\Kernel\Excel(['path' => '']);

        try {
            // 2. 获取总行数
            $data = $excel->openFile($filePath)
                         ->openSheet()
                         ->getSheetData();

            $totalRows = count($data);
            $successCount = 0;
            $errorRows = [];

            // 3. 分块处理
            for ($startRow = 1; $startRow <= $totalRows; $startRow += $this->config['chunk_size']) {
                $chunkData = $this->readChunk($filePath, $startRow, $this->config['chunk_size']);

                // 4. 批量插入数据库
                $batchData = [];
                foreach ($chunkData as $rowIndex => $row) {
                    if ($this->validateRow($row)) {
                        $batchData[] = $this->formatRow($row);

                        // 达到批次大小就插入
                        if (count($batchData) >= $this->config['batch_insert_size']) {
                            $this->batchInsert($batchData);
                            $successCount += count($batchData);
                            $batchData = [];
                        }
                    } else {
                        $errorRows[] = $startRow + $rowIndex;
                    }
                }

                // 插入剩余数据
                if (!empty($batchData)) {
                    $this->batchInsert($batchData);
                    $successCount += count($batchData);
                }

                // 释放内存
                unset($chunkData, $batchData);
                gc_collect_cycles();

                // 进度记录
                $this->logProgress($startRow, $totalRows);
            }

            return [
                'total' => $totalRows - 1, // 减去表头
                'success' => $successCount,
                'errors' => $errorRows
            ];

        } catch (\Exception $e) {
            thrownew \Exception("导入失败: " . $e->getMessage());
        }
    }

    privatefunction readChunk($filePath, $startRow, $chunkSize)
    {
        $excel = new \Vtiful\Kernel\Excel(['path' => '']);

        return $excel->openFile($filePath)
                    ->openSheet()
                    ->setSkipRows($startRow - 1)
                    ->getSheetData($chunkSize);
    }

    privatefunction batchInsert($data)
    {
        // 使用批量插入优化
        $placeholders = [];
        $values = [];

        foreach ($data as $row) {
            $placeholders[] = '(' . implode(',', array_fill(0, count($row), '?')) . ')';
            $values = array_merge($values, array_values($row));
        }

        $sql = "INSERT INTO large_table (field1, field2, field3) VALUES " . 
               implode(',', $placeholders) . 
               " ON DUPLICATE KEY UPDATE updated_at = NOW()";

        // 使用PDO预处理执行
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($values);
    }
}

-- 1. 创建适合批量导入的表结构
CREATETABLE large_data (
    idBIGINTUNSIGNEDNOTNULL AUTO_INCREMENT,
    nameVARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    address TEXT,
    created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
    updated_at TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_email (email),
    INDEX idx_created (created_at)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

-- 2. 导入时临时调整设置
SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;
-- 执行导入操作
SET unique_checks = 1;
SET foreign_key_checks = 1;
COMMIT;

<?php
class ExcelMassHandler
{
    private $excel;
    private $db;
    private $config;

    publicfunction __construct(array $config = [])
    {
        $this->config = array_merge([
            'export_chunk' => 50000,
            'import_chunk' => 20000,
            'batch_insert' => 1000,
            'temp_dir' => sys_get_temp_dir() . '/excel_mass/',
            'memory_limit' => '2048M',
            'time_limit' => 0
        ], $config);

        $this->initEnvironment();
        $this->initDirectories();
        $this->excel = new \Vtiful\Kernel\Excel(['path' => $this->config['temp_dir']]);
    }

    privatefunction initEnvironment()
    {
        ini_set('memory_limit', $this->config['memory_limit']);
        set_time_limit($this->config['time_limit']);

        // 关闭输出缓冲
        if (ob_get_level()) {
            ob_end_clean();
        }
    }

    privatefunction initDirectories()
    {
        if (!is_dir($this->config['temp_dir'])) {
            mkdir($this->config['temp_dir'], 0777, true);
        }
    }

    /**
     * 流式导出
     */
    publicfunction streamExport(callable $dataCallback, array $headers, $filename)
    {
        $tempFile = $this->config['temp_dir'] . uniqid('export_') . '.xlsx';

        try {
            $fileObject = $this->excel->fileName($tempFile)->header($headers);

            $page = 1;
            do {
                $data = $dataCallback($page, $this->config['export_chunk']);

                if (!empty($data)) {
                    $fileObject->data($data);
                    $page++;

                    // 进度回调
                    if (isset($this->config['progress_callback'])) {
                        call_user_func($this->config['progress_callback'], $page * $this->config['export_chunk']);
                    }

                    unset($data);
                    gc_collect_cycles();
                }

            } while (!empty($data));

            $fileObject->output();

            // 返回文件路径供下载
            return $tempFile;

        } catch (\Exception $e) {
            if (file_exists($tempFile)) {
                unlink($tempFile);
            }
            throw $e;
        }
    }

    /**
     * 异步导出（推荐用于超大数据）
     */
    publicfunction asyncExport(callable $dataCallback, array $headers, $filename)
    {
        // 生成任务ID
        $taskId = uniqid('export_');
        $taskFile = $this->config['temp_dir'] . $taskId . '.task';

        // 保存任务信息
        file_put_contents($taskFile, json_encode([
            'filename' => $filename,
            'headers' => $headers,
            'status' => 'pending',
            'created_at' => time()
        ]));

        // 异步执行（使用消息队列或后台进程）
        $this->dispatchAsyncTask($taskId, $dataCallback);

        return $taskId;
    }

    /**
     * 分片导入
     */
    publicfunction chunkImport($filePath, callable $processCallback, $skipHeader = true)
    {
        if (!file_exists($filePath)) {
            thrownew \Exception("文件不存在");
        }

        // 获取文件信息
        $fileSize = filesize($filePath);
        $maxMemory = $this->convertToBytes(ini_get('memory_limit'));

        // 根据文件大小自动调整分片大小
        $chunkSize = $this->calculateChunkSize($fileSize, $maxMemory);

        $excel = new \Vtiful\Kernel\Excel(['path' => '']);
        $data = $excel->openFile($filePath)->openSheet();

        $startRow = $skipHeader ? 1 : 0;
        $totalRows = count($data->getSheetData());
        $processed = 0;

        while ($startRow < $totalRows) {
            $chunkData = $data->setSkipRows($startRow)->getSheetData($chunkSize);

            if (empty($chunkData)) {
                break;
            }

            // 处理数据块
            $result = $processCallback($chunkData, $startRow);

            $processed += count($chunkData);
            $startRow += $chunkSize;

            // 进度报告
            $progress = round(($processed / $totalRows) * 100, 2);
            $this->log("处理进度: {$progress}% ({$processed}/{$totalRows})");

            unset($chunkData);
            gc_collect_cycles();

            // 防止内存溢出
            if (memory_get_usage(true) > $maxMemory * 0.7) {
                $chunkSize = max(1000, $chunkSize / 2);
            }
        }

        return [
            'total' => $totalRows - ($skipHeader ? 1 : 0),
            'processed' => $processed
        ];
    }

    privatefunction calculateChunkSize($fileSize, $maxMemory)
    {
        // 简单估算：每行约2KB，保留100MB内存空间
        $safeMemory = $maxMemory - 100 * 1024 * 1024;
        $estimatedRowSize = 2 * 1024; // 2KB per row
        $calculated = floor($safeMemory / $estimatedRowSize);

        return min(max(1000, $calculated), 100000);
    }

    privatefunction convertToBytes($value)
    {
        $unit = strtolower(substr($value, -1));
        $number = (int) substr($value, 0, -1);

        switch ($unit) {
            case'g': return $number * 1024 * 1024 * 1024;
            case'm': return $number * 1024 * 1024;
            case'k': return $number * 1024;
            default: return (int) $value;
        }
    }

    privatefunction log($message)
    {
        error_log('[' . date('Y-m-d H:i:s') . '] ' . $message . PHP_EOL, 3, $this->config['temp_dir'] . 'import.log');
    }
}

<?php
// 1. 导出示例
$exporter = new ExcelMassHandler();

// 数据回调函数
$dataCallback = function ($page, $chunkSize) use ($db) {
    $offset = ($page - 1) * $chunkSize;
    $stmt = $db->prepare("SELECT * FROM large_table LIMIT ? OFFSET ?");
    $stmt->execute([$chunkSize, $offset]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
};

$headers = ['ID', '姓名', '邮箱', '电话', '创建时间'];
$file = $exporter->streamExport($dataCallback, $headers, '百万数据导出.xlsx');

// 2. 导入示例
$importer = new ExcelMassHandler([
    'import_chunk' => 50000,
    'batch_insert' => 2000
]);

$result = $importer->chunkImport('large_data.xlsx', function ($chunkData) use ($db) {
    // 处理并验证数据
    $validData = [];
    foreach ($chunkData as $row) {
        if ($this->validateRow($row)) {
            $validData[] = $row;
        }
    }

    // 批量插入
    if (!empty($validData)) {
        $this->batchInsertToDB($validData);
    }

    return count($validData);
});

; php.ini 配置
memory_limit = 2048M
max_execution_time = 0
max_input_time = -1
post_max_size = 1024M
upload_max_filesize = 1024M

; OPcache 优化
opcache.enable=1
opcache.memory_consumption=256
opcache.max_accelerated_files=10000

client_max_body_size 1024m;
proxy_read_timeout 600s;
fastcgi_read_timeout 600s;