<?php
namespace app\common\service\wareHousFee\jingdong\wareHousingFees;
use app\common\model\wareHousFee\jingdong\wareHousingFees\WareHousingFeesItemizationModel;
use app\common\model\wareHousFee\jingdong\wareHousingFees\WareHousingFeesQuoteModel;
use think\facade\Db;
use think\facade\Log;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;
class WareHousingFeeService
{
// 存储内存表名称
protected $memoryTableName = '';
public function __construct() {}
/**
* @notes 京东仓储服务费核对逻辑（内存表优化版）
* @param string $yearMonth
* @return bool
* @throws DataNotFoundException
* @throws DbException
* @throws ModelNotFoundException
* @author 胡军
* @date 2025/06/27
*/
public function wareHousingFeeVerifyDo(string $yearMonth): bool
{
// 1. 获取月份时间范围
$monthTimeRange = $this->getMonthTimeRange($yearMonth);
if (!$monthTimeRange) {
Log::error("无效的月份格式: {$yearMonth}");
return false;
}
// 2. 预加载报价数据
$quoteList = WareHousingFeesQuoteModel::where('service_type', 'IN', ['出库单', '退供单', '退货单'])
->column('first_three_items,additional_items', 'service_type');
if (empty($quoteList)) {
Log::warning("京东仓储服务费报价表为空，月份: {$yearMonth}");
return false;
}
// 3. 配置参数（从环境变量获取或使用默认值）
$batchSize = 500; // 批次大小即每次读取的数据条数
$reconnectInterval = 200; // 每10万条重新连接一次数据库（200批次=10万条）
$gcInterval = 20; // 垃圾回收间隔
$updateCount = 0; // 处理计数
$batchCount = 0; // 批次计数
$status = true; // 整体状态
// 性能监控变量
$startTime = microtime(true);
try {
// 4. 创建唯一命名的内存表
$this->memoryTableName = 'mem_warehouse_summary_' . uniqid();
$this->createMemoryTable($this->memoryTableName);
//Log::info("创建内存表 {$this->memoryTableName} 成功");
// 5. 创建基础查询
$query = WareHousingFeesItemizationModel::whereBetween('business_time', [
$monthTimeRange['startTime'],
$monthTimeRange['endTime']
]);
// 6. 分批次处理数据（只做分组统计，不计算最终结果）
$query->chunk($batchSize, function($items) use (
&$batchCount,
$reconnectInterval,
$gcInterval
) {
$batchCount++;
// 7. 分组统计（按单据号和单据类型分组）
$groupedData = [];
foreach ($items as $item) {
$key = $item->document_number . '|' . $item->document_type;
if (!isset($groupedData[$key])) {
$groupedData[$key] = [
'document_number' => $item->document_number,
'document_type' => $item->document_type,
'total_quantity' => 0,
'total_settlement_amount' => 0
];
}
$groupedData[$key]['total_quantity'] += $item->quantity;
$groupedData[$key]['total_settlement_amount'] += $item->settlement_amount;
}
// 8. 将当前批次的分组结果累加到内存表
$this->accumulateToMemoryTable($groupedData);
// 9. 内存管理（释放不再使用的变量）
unset($items, $groupedData);
// 10. 定期执行垃圾回收 每10万条执行一次垃圾回收
if ($batchCount % $gcInterval === 0) {
gc_collect_cycles();
}
// 11. 定期重连数据库（避免长连接超时）
if ($batchCount % $reconnectInterval === 0) {
$this->reconnectDatabase();
Log::info("京东仓储服务费用核对已处理 {$batchCount} 批次,进行数据库重连");
}
});
// 12. 从内存表获取完整的分组数据
// TODO：此处有内存溢出的风险 如果100万条数据都存在内存中，可能会导致内存溢出，严重影响性能甚至崩溃！待优化...
$fullGroupedData = Db::table($this->memoryTableName)->select();
// 13. 计算理论金额和差额
$updateData = [];
foreach ($fullGroupedData as $data) {
$docType = $data['document_type'];
$totalQty = $data['total_quantity'];
$settlementAmount = $data['total_settlement_amount'];
$theoreticalAmount = 0;
switch ($docType) {
case '出库单':
if (isset($quoteList['出库单'])) {
$quote = $quoteList['出库单'];
$theoreticalAmount = ($totalQty <= 3)
? $quote['first_three_items']
: $quote['first_three_items'] + ($totalQty - 3) * $quote['additional_items'];
}
break;
case '退供单':
if (isset($quoteList['退供单'])) {
$quote = $quoteList['退供单'];
$theoreticalAmount = $quote['first_three_items'] * $totalQty;
}
break;
case '退货单':
if (isset($quoteList['退货单'])) {
$quote = $quoteList['退货单'];
$theoreticalAmount = ($totalQty <= 3)
? $quote['first_three_items']
: $quote['first_three_items'] + ($totalQty - 3) * $quote['additional_items'];
}
break;
}
if ($theoreticalAmount == 0) {
Log::warning("京东仓储费计算失败: {$data['document_number']}, 单据类型: {$docType}");
continue;
}
$balance = $settlementAmount - $theoreticalAmount;
$updateData[] = [
'document_number' => $data['document_number'],
'document_type' => $docType,
'total_quantity' => $totalQty,
'total_settlement_amount' => $settlementAmount,
'theoretical_amount' => $theoreticalAmount,
'balance' => $balance,
'yearMonth' => $yearMonth
];
$updateCount++;
}
// 14. 批量更新到结果表
if (!empty($updateData)) {
$this->batchUpsert($updateData);
Log::info("成功更新 {$updateCount} 条仓储费核对结果");
}
} catch (\Throwable $e) {
Log::error("京东仓储费核对系统运行异常: {$e->getMessage()}");
$status = false;
} finally {
// 15. 确保删除内存表（即使处理失败）
if ($this->memoryTableName) {
$this->dropMemoryTable($this->memoryTableName);
}
Db::close(); // 最终关闭数据库连接
}
// 16. 生成最终统计日志
$peakMemory = round(memory_get_peak_usage() / 1024 / 1024, 2);
$totalTime = round(microtime(true) - $startTime, 2);
Log::info("京东仓储费核对完成: {$yearMonth}, 处理单据: {$updateCount}条, 峰值内存: {$peakMemory}MB, 总耗时: {$totalTime}秒");
return $status;
}
/**
* 创建内存表用于存储中间结果
* @param string $tableName 内存表名称
*/
private function createMemoryTable(string $tableName)
{
$sql = "CREATE TABLE IF NOT EXISTS `{$tableName}` (
document_number VARCHAR(50) NOT NULL,
document_type VARCHAR(20) NOT NULL,
total_quantity DECIMAL(12,2) NOT NULL DEFAULT 0,
total_settlement_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
PRIMARY KEY (document_number, document_type)
) ENGINE=MEMORY"; // 使用内存引擎提高性能
try {
Db::execute($sql);
} catch (\Exception $e) {
Log::error("创建内存表失败: {$e->getMessage()}");
throw new \Exception("无法创建内存表: {$tableName}");
}
}
/**
* 将分组结果累加到内存表
* @param array $groupedData 分组后的数据
*/
private function accumulateToMemoryTable(array $groupedData)
{
if (empty($groupedData)) return;
// 使用事务确保原子性
Db::startTrans();
try {
foreach ($groupedData as $data) {
// 使用UPSERT操作（存在则更新，不存在则插入）
Db::execute("
INSERT INTO `{$this->memoryTableName}`
(document_number, document_type, total_quantity, total_settlement_amount)
VALUES (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
total_quantity = total_quantity + VALUES(total_quantity),
total_settlement_amount = total_settlement_amount + VALUES(total_settlement_amount)
", [
$data['document_number'],
$data['document_type'],
$data['total_quantity'],
$data['total_settlement_amount']
]);
}
Db::commit();
} catch (\Exception $e) {
Db::rollback();
Log::error("内存表数据累加失败: {$e->getMessage()}");
throw $e;
}
}
/**
* 删除内存表
* @param string $tableName 要删除的内存表名称
*/
private function dropMemoryTable(string $tableName)
{
try {
Db::execute("DROP TABLE IF EXISTS `{$tableName}`");
Log::info("内存表 {$tableName} 已删除");
} catch (\Exception $e) {
Log::error("删除内存表失败: {$e->getMessage()}");
}
}
/**
* 数据库重连方法，用于长时间运行的任务，避免连接超时
*/
private function reconnectDatabase()
{
try {
$connection = \think\facade\Db::connect();
$connection->close(); // 关闭当前连接
$connection->connect(); // 重新建立连接
} catch (\Exception $e) {
Log::error('【京东仓储服务费核对数据库重连失败】' . $e->getMessage());
}
}
/**
* 获取月份时间范围（返回包含开始和结束时间的数组）
* @param string $yearMonth 年月字符串（格式：YYYY-MM）
* @return array|null
*/
private function getMonthTimeRange(string $yearMonth): ?array
{
if (!preg_match('/^\d{4}-\d{2}$/', $yearMonth)) {
return null;
}
$startTime = date('Y-m-01 00:00:00', strtotime($yearMonth));
$endTime = date('Y-m-t 23:59:59', strtotime($yearMonth));
return ['startTime' => $startTime, 'endTime' => $endTime];
}
/**
* 批量插入或更新数据（使用INSERT ON DUPLICATE KEY UPDATE语法）
* @param array $data 要更新的数据
*/
private function batchUpsert(array $data): void
{
if (empty($data)) return;
// 按1000条数据分块处理，避免SQL语句过长
$chunks = array_chunk($data, 1000);
foreach ($chunks as $chunk) {
$values = [];
$params = [];
// 构建SQL语句的VALUES部分
foreach ($chunk as $row) {
$values[] = "(?, ?, ?, ?, ?, ?, ?)";
$params[] = $row['document_number'];
$params[] = $row['document_type'];
$params[] = $row['total_quantity'];
$params[] = $row['total_settlement_amount'];
$params[] = $row['theoretical_amount'];
$params[] = $row['balance'];
$params[] = $row['yearMonth'];
}
// 构建完整的INSERT ON DUPLICATE KEY UPDATE语句
$sql = "INSERT INTO la_storage_service_yes
(document_number, document_type, total_quantity, total_settlement_amount, theoretical_amount, balance, yearMonth)
VALUES " . implode(',', $values) . "
ON DUPLICATE KEY UPDATE
document_type = VALUES(document_type),
total_quantity = VALUES(total_quantity),
total_settlement_amount = VALUES(total_settlement_amount),
theoretical_amount = VALUES(theoretical_amount),
balance = VALUES(balance),
yearMonth = VALUES(yearMonth)";
try {
Db::execute($sql, $params);
} catch (\Exception $e) {
Log::error("京东仓储费批量更新失败: {$e->getMessage()}\nSQL语句: {$sql}");
throw $e;
}
}
}
}
我使用了mysql的内存表去临时存储数据，但是10万条数据就报错了：
[2025-07-07T17:31:46+08:00][error] 内存表数据累加失败: SQLSTATE[HY000]: General error: 1114 The table 'mem_warehouse_summary_686b93f20f83d' is full
[2025-07-07T17:31:46+08:00][error] 京东仓储费核对系统运行异常: SQLSTATE[HY000]: General error: 1114 The table 'mem_warehouse_summary_686b93f20f83d' is full
[2025-07-07T17:31:46+08:00][info] 内存表 mem_warehouse_summary_686b93f20f83d 已删除
[2025-07-07T17:31:46+08:00][info] 京东仓储费核对完成: 2025-05, 处理单据: 0条, 峰值内存: 3.9MB, 总耗时: 16.81秒
我该如何优化我的代码呢？
最新发布
在使用Python的pypyodbc包向Access数据库批量写入数据时，遇到一个错误：[HY001]ODBCMicrosoftAccessDriver无法完成查询，提示超过2GB限制。然而，Access文件实际大小仅为160MB。尝试分批写入1万条数据后问题解决，且将提交事务移到每次插入后不再引发错误。这表明可能是批量提交时的内存管理问题导致的错误。
扫一扫
2464
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
