获取唯一与回头客的WP SQL查询执行超时报错求助

原查询试图区分近1年下单的客户是新客户还是回头客，但因表别名歧义和全表扫描效率极低导致查询超时，返回500错误。以下是针对性优化方案：

1. 先添加必要索引（关键前提） #

WooCommerce订单表数据量较大时，必须依赖索引避免全表扫描：

-- 给订单表添加类型+日期的联合索引，快速筛选指定时间范围的订单
CREATE INDEX idx_posts_type_date ON biotech_posts(post_type, post_date);

-- 给元数据表添加订单ID+客户标识键的联合索引，快速定位客户ID
CREATE INDEX idx_postmeta_order_customer ON biotech_postmeta(post_id, meta_key);

2. 优化后的查询语句 #

方案一：兼容低版本MySQL（用LEFT JOIN替代IN子查询） #

SELECT
    current_pm.meta_value AS customer_id,
    -- is_new=1为新客户（近1年首次下单），is_new=0为回头客（历史有过订单）
    CASE WHEN past_pm.meta_value IS NOT NULL THEN 0 ELSE 1 END AS is_new
FROM
    biotech_posts current_p
JOIN
    biotech_postmeta current_pm 
        ON current_p.ID = current_pm.post_id 
        AND current_pm.meta_key = '_customer_user'
LEFT JOIN (
    -- 提前筛选出1年前有过订单的所有客户ID（去重）
    SELECT DISTINCT pm.meta_value
    FROM biotech_posts p
    JOIN biotech_postmeta pm 
        ON p.ID = pm.post_id 
        AND pm.meta_key = '_customer_user'
    WHERE p.post_type = 'shop_order'
        AND p.post_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
) past_pm ON current_pm.meta_value = past_pm.meta_value
WHERE
    current_p.post_type = 'shop_order'
    AND current_p.post_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY current_pm.meta_value; -- 去重得到唯一客户列表

方案二：MySQL 8.0+推荐（用窗口函数简化逻辑） #

通过窗口函数直接计算客户首次下单时间，快速判断新老客户：

WITH customer_first_order AS (
    SELECT
        pm.meta_value AS customer_id,
        MIN(p.post_date) AS first_order_date
    FROM
        biotech_posts p
    JOIN
        biotech_postmeta pm 
            ON p.ID = pm.post_id 
            AND pm.meta_key = '_customer_user'
    WHERE
        p.post_type = 'shop_order'
    GROUP BY pm.meta_value
)
SELECT
    customer_id,
    CASE WHEN first_order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) THEN 1 ELSE 0 END AS is_new
FROM customer_first_order
WHERE first_order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

优化说明 #

• 原查询的子查询会对每一条近1年订单重复执行全表扫描，效率极低；优化后仅扫描一次历史数据
• 用不同表别名区分当前/历史订单，避免字段歧义
• 索引加持下，查询能快速定位目标数据，彻底解决超时问题

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