千万级别的数据导出Redis、php

要处理千万级别数据涵盖了多表联查、条件筛选、Redis缓存优化、yield生成器优化内存，以及将数据导出到Excel文件。

方案概述

1. 多表分步查询：避免直接的多表联查，通过逐表查询并在应用层进行数据组合，以减少数据库的锁定风险

2. Redis 缓存：在多表联查过程中使用 Redis 进行中间结果的缓存，减少数据库查询次数。

3. yield生成器：通过 PHP 的 yield 生成器逐条处理数据，减少内存占用。

4. 分批导出：使用 PhpSpreadsheet 进行数据的分批写入，控制内存占用。

MySQL 表结构示例

假设有三张表 orders, customers, 和 products，分别存储订单信息、客户信息和商品信息。

CREATE TABLE customers (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2)
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    product_id BIGINT,
    quantity INT,
    order_date DATETIME,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

1. 分步查询与数据组合

首先分步查询每个表的数据，然后在应用层面合并这些数据，避免复杂的 SQL JOIN 操作

function fetchOrders($pdo, $batchSize, $offset, $conditions) {
    $sql = "SELECT * FROM orders WHERE $conditions LIMIT :limit OFFSET :offset";
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':limit', $batchSize, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function fetchCustomers($pdo, $customerIds) {
    $inQuery = implode(',', array_fill(0, count($customerIds), '?'));
    $sql = "SELECT * FROM customers WHERE id IN ($inQuery)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($customerIds);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function fetchProducts($pdo, $productIds) {
    $inQuery = implode(',', array_fill(0, count($productIds), '?'));
    $sql = "SELECT * FROM products WHERE id IN ($inQuery)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($productIds);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function combineData($orders, $customers, $products) {
    $customerMap = array_column($customers, null, 'id');
    $productMap = array_column($products, null, 'id');
    foreach ($orders as &$order) {
        $order['customer_name'] = $customerMap[$order['customer_id']]['name'] ?? '';
        $order['product_name'] = $productMap[$order['product_id']]['name'] ?? '';
        $order['price'] = $productMap[$order['product_id']]['price'] ?? 0.0;
    }
    return $orders;
}

2. 使用 Redis 缓存中间数据

在进行下一步的查询和数据处理之前，缓存中间结果，减少重复查询。

function cacheOrdersInRedis($redis, $orders, $batchSize) {
    foreach ($orders as $order) {
        $redis->rpush('orders_cache', json_encode($order));
    }
}

function getOrdersFromRedis($redis, $batchSize) {
    return array_map('json_decode', $redis->lrange('orders_cache', 0, $batchSize - 1));
}

3. 使用 yield 优化内存使用

通过 yield 逐条处理数据，避免内存占用过多。

function orderGenerator($redis, $batchSize) {
    while (true) {
        $orders = getOrdersFromRedis($redis, $batchSize);
        if (empty($orders)) {
            break;
        }

        foreach ($orders as $order) {
            yield $order;
        }

        // 删除已经处理的数据
        $redis->ltrim('orders_cache', $batchSize, -1);
    }
}

4. 分批导出到 Excel

使用 PhpSpreadsheet 分批写入数据到 Excel 文件中。

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

function exportToExcel($filename, $dataGenerator) {
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();

    $row = 1;
    foreach ($dataGenerator as $dataRow) {
        $col = 1;
        foreach ($dataRow as $value) {
            $sheet->setCellValueByColumnAndRow($col, $row, $value);
            $col++;
        }
        $row++;
    }

    $writer = new Xlsx($spreadsheet);
    $writer->save($filename);
}

5. 综合实现

$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "pass");
$redis = new Redis();
$redis->connect('127.0.0.1', 6379);

$batchSize = 10000;
$cacheKey = "orders_cache";
$filename = "export.xlsx";

// 假设要筛选最近一个月的订单
$conditions = "order_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)";

// 清空 Redis 缓存
$redis->del($cacheKey);

$offset = 0;
while ($orders = fetchOrders($pdo, $batchSize, $offset, $conditions)) {
    // 获取关联数据
    $customerIds = array_column($orders, 'customer_id');
    $productIds = array_column($orders, 'product_id');

    $customers = fetchCustomers($pdo, $customerIds);
    $products = fetchProducts($pdo, $productIds);

    // 合并数据
    $combinedData = combineData($orders, $customers, $products);

    // 缓存数据到 Redis
    cacheOrdersInRedis($redis, $combinedData, $batchSize);

    $offset += $batchSize;
}

// 使用 `yield` 生成器逐条获取数据并导出
$dataGenerator = orderGenerator($redis, $batchSize);
exportToExcel($filename, $dataGenerator);

echo "Data export completed.";

优化点

1. 分步查询：避免直接进行多表 JOIN，减少数据库锁表风险。

2. Redis 缓存：中间结果缓存到 Redis，减少重复的数据库查询，提高效率。

3. yield 生成器：逐条处理数据，避免一次性加载大量数据到内存中，优化内存使用。

4. 分批导出：通过 PhpSpreadsheet 分批写入 Excel，控制内存消耗。

这样可以有效避免大规模联表查询带来的锁表问题，同时保持数据处理的效率和内存的合理使用。