求优化：获取WooCommerce产品近一年订单数的高效方案

优化的MySQL查询方案 #

原查询使用子查询对每个产品单独统计，导致多次重复查询，性能低下。改用JOIN+分组统计的方式，仅需一次查询即可获取所有产品的订单数和销售额：

SELECT 
    p.ID AS prodID,
    p.post_title AS product_name,
    COUNT(DISTINCT oi.order_id) AS order_count,
    SUM(oim_total.meta_value) AS total_sales
FROM posts p
INNER JOIN postmeta pm ON p.ID = pm.post_id 
LEFT JOIN woocommerce_order_items oi 
    ON oi.order_item_type = 'line_item'
LEFT JOIN woocommerce_order_itemmeta oim_prod 
    ON oi.order_item_id = oim_prod.order_item_id 
    AND oim_prod.meta_key = '_product_id' 
    AND oim_prod.meta_value = p.ID
LEFT JOIN woocommerce_order_itemmeta oim_total 
    ON oi.order_item_id = oim_total.order_item_id 
    AND oim_total.meta_key = '_line_total'
LEFT JOIN posts o ON oi.order_id = o.ID 
    AND o.post_type = 'shop_order' 
    AND o.post_date >= CURDATE() - INTERVAL 1 YEAR 
    AND o.post_status IN ('wc-delivered', 'wc-completed')
WHERE 
    p.post_type = 'product' 
    AND p.post_status = 'publish'
    AND pm.meta_key = '_price'
GROUP BY p.ID, p.post_title
ORDER BY p.post_date DESC;

• COUNT(DISTINCT oi.order_id)统计该产品关联的独立订单数
• SUM(oim_total.meta_value)计算总销售额（_line_total为订单项税后总价，需税前可替换为_line_subtotal）
• 保留原查询中的产品筛选条件，可根据需求调整字段或筛选规则

高效的PHP实现方案 #

避免循环逐个查询产品订单，采用批量获取+批量统计的思路，大幅提升性能：

// 1. 获取所有符合条件的产品ID
$product_args = array(
    'post_type'      => 'product',
    'posts_per_page' => -1,
    'post_status'    => 'publish',
    'meta_query'     => array(
        array(
            'key'     => '_price',
            'compare' => 'EXISTS'
        )
    ),
    'fields'         => 'ids' // 仅获取ID，减少内存占用
);
$product_ids = get_posts($product_args);

if (empty($product_ids)) {
    echo "无符合条件的产品";
    return;
}

// 2. 批量统计所有产品的近一年订单数据
global $wpdb;
$product_ids_str = implode(',', array_map('intval', $product_ids));
$start_date = date('Y-m-d H:i:s', strtotime('-1 year'));

$stats = $wpdb->get_results($wpdb->prepare("
    SELECT 
        oim.meta_value AS product_id,
        COUNT(DISTINCT oi.order_id) AS order_count,
        SUM(oim_total.meta_value) AS total_sales
    FROM woocommerce_order_items oi
    INNER JOIN woocommerce_order_itemmeta oim 
        ON oi.order_item_id = oim.order_item_id 
        AND oim.meta_key = '_product_id' 
        AND oim.meta_value IN ($product_ids_str)
    INNER JOIN woocommerce_order_itemmeta oim_total 
        ON oi.order_item_id = oim_total.order_item_id 
        AND oim_total.meta_key = '_line_total'
    INNER JOIN posts o 
        ON oi.order_id = o.ID 
        AND o.post_type = 'shop_order' 
        AND o.post_date >= %s 
        AND o.post_status IN ('wc-delivered', 'wc-completed')
    GROUP BY oim.meta_value
", $start_date), ARRAY_A);

// 3. 将统计结果转为以产品ID为键的映射数组
$stats_map = array();
foreach ($stats as $stat) {
    $stats_map[$stat['product_id']] = array(
        'order_count' => $stat['order_count'],
        'total_sales' => $stat['total_sales'] ?: 0
    );
}

// 4. 组合产品信息与统计数据
$products_with_stats = array();
foreach ($product_ids as $prod_id) {
    $product = wc_get_product($prod_id);
    $products_with_stats[] = array(
        'id'           => $prod_id,
        'name'         => $product->get_name(),
        'price'        => $product->get_price(),
        'order_count'  => $stats_map[$prod_id]['order_count'] ?? 0,
        'total_sales'  => $stats_map[$prod_id]['total_sales'] ?? 0
    );
}

// 输出结果（可根据需求调整展示方式）
echo '<pre>';
print_r($products_with_stats);
echo '</pre>';

• 先批量获取产品ID，避免加载大量产品对象
• 用原生SQL一次性统计所有产品的订单数据，仅执行一次查询
• 通过数组映射快速匹配产品与统计结果，避免循环查询的性能损耗
• 使用wc_get_product获取产品的标准字段，确保数据符合WooCommerce规范

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