优化MySQL与PHP数据库查询：Magento定时任务轻量化改造咨询

Absolutely! Let's break down your current code and tweak it step by step to cut down MySQL load significantly—especially since it runs every minute via Cron. Here's how to optimize it:

Key Issues in the Original Code #

• N+1 Database Queries: For every order, you run a separate query to fetch its gift cards. This adds up fast if you have multiple orders per minute.
• Unnecessary Data Fetching: You're selecting all attributes for orders and gift cards, which wastes memory and database bandwidth.
• Individual Record Saves: Each $card->save() hits the database once; batch updates will cut down round trips drastically.

Optimized Code #

<?php
ini_set('memory_limit', '128M');
define('MAGENTO_ROOT', getcwd());
$currentTime = time();
$fromDate = date('Y-m-d H:i:s', $currentTime - 60); // Orders from the last 60 seconds
$curDate = date('Y-m-d');

$mageFilename = '/app/Mage.php';
require_once $mageFilename;
Mage::app();

// 1. Fetch ONLY the order IDs we need (no extra attributes)
$orderIds = Mage::getResourceModel('sales/order_collection')
    ->addAttributeToSelect('entity_id')
    ->addAttributeToFilter('updated_at', [
        'from' => $fromDate,
        'to' => date('Y-m-d H:i:s', $currentTime)
    ])
    ->getColumnValues('entity_id');

if (empty($orderIds)) {
    exit; // No orders to process, exit early
}

// 2. Batch-fetch ALL related gift cards in a single query
$giftCards = Mage::getModel('giftcards/giftcards')->getCollection()
    ->addFieldToSelect(['card_id', 'order_id', 'card_status', 'mail_delivery_date', 'card_type'])
    ->addFieldToFilter('order_id', ['in' => $orderIds]);

// 3. Prepare batch updates to avoid individual saves
$statusUpdates = [];
$connection = Mage::getSingleton('core/resource')->getConnection('core_write');
$giftCardTable = Mage::getSingleton('core/resource')->getTableName('giftcards/giftcards');

foreach ($giftCards as $card) {
    $shouldActivate = false;
    $shouldDeactivate = false;

    if ($card->getCardStatus() == 0) {
        $deliveryDate = $card->getMailDeliveryDate();
        if ((is_null($deliveryDate) || $curDate == $deliveryDate) && $card->getCardType() != 'offline') {
            $shouldActivate = true;
        }
    } elseif ($card->getCardStatus() == 1) {
        $deliveryDate = $card->getMailDeliveryDate();
        if (!is_null($deliveryDate) && $curDate < $deliveryDate) {
            $shouldDeactivate = true;
        }
    }

    if ($shouldActivate) {
        $statusUpdates[] = [
            'card_id' => $card->getCardId(),
            'card_status' => 1
        ];
        // Send the email immediately (if needed; could batch this too if performance is critical)
        $card->send();
    } elseif ($shouldDeactivate) {
        $statusUpdates[] = [
            'card_id' => $card->getCardId(),
            'card_status' => 0
        ];
    }
}

// 4. Execute batch update in one go
if (!empty($statusUpdates)) {
    $data = [];
    foreach ($statusUpdates as $update) {
        $data[] = "({$update['card_id']}, {$update['card_status']})";
    }
    $sql = "UPDATE {$giftCardTable} SET card_status = CASE card_id " .
        implode(' ', array_map(function($item) {
            list($id, $status) = explode(', ', trim($item, '()'));
            return "WHEN {$id} THEN {$status}";
        }, $data)) .
        " END WHERE card_id IN (" . implode(', ', array_column($statusUpdates, 'card_id')) . ")";
    $connection->query($sql);
}
?>

What Changed & Why #

• Early Exit: If there are no orders updated in the last minute, we exit immediately to avoid unnecessary database calls.
• Reduced Data Fetching: We only select the exact fields needed (entity_id for orders, critical fields for gift cards) instead of *, cutting down on data transfer and memory usage.
• Single Gift Card Query: Instead of querying gift cards per order, we fetch all relevant cards in one query using order_id IN ($orderIds).
• Batch Updates: Instead of saving each card individually, we compile all status changes into a single SQL UPDATE statement. This turns potentially dozens of database calls into one.
• Conditional Logic Cleanup: Streamlined the status check logic to make it easier to follow and reduce redundant code.

Bonus Tips #

• Add Indexes: Ensure updated_at on the sales_order table and order_id/card_status on the gift cards table are indexed. This will speed up your filter queries.
• Log Instead of Print: Replace print statements with Magento's logging system (Mage::log()) so you can monitor execution without cluttering output.
• Adjust Cron Frequency: If you don't need to check every minute, consider increasing the interval (e.g., every 5 minutes) to reduce overall load.

内容的提问来源于stack exchange，提问作者James