要处理千万级别数据涵盖了多表联查、条件筛选、Redis缓存优化、yield
生成器优化内存,以及将数据导出到Excel文件。
方案概述
-
多表分步查询:避免直接的多表联查,通过逐表查询并在应用层进行数据组合,以减少数据库的锁定风险
-
Redis 缓存:在多表联查过程中使用 Redis 进行中间结果的缓存,减少数据库查询次数。
-
yield
生成器:通过 PHP 的yield
生成器逐条处理数据,减少内存占用。 -
分批导出:使用
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.";
优化点
-
分步查询:避免直接进行多表 JOIN,减少数据库锁表风险。
-
Redis 缓存:中间结果缓存到 Redis,减少重复的数据库查询,提高效率。
-
yield
生成器:逐条处理数据,避免一次性加载大量数据到内存中,优化内存使用。 -
分批导出:通过
PhpSpreadsheet
分批写入 Excel,控制内存消耗。
这样可以有效避免大规模联表查询带来的锁表问题,同时保持数据处理的效率和内存的合理使用。