SQL查询优化：如何复用同一子查询同时获取COUNT与SUM统计结果

优化方案 #

核心问题说明 #

原有语句存在两个明显的性能损耗点：

• 逐行执行4次wp_usermeta子查询获取用户扩展字段，数据量较大时会产生大量重复查询开销
• 订单计数、消费总额的统计逻辑完全复用同一份子查询结果，相当于wp_postmeta的过滤逻辑重复执行了两次，额外浪费了一倍的查询资源

优化后SQL #

SELECT 
    u.ID,
    u.user_email AS mail, 
    u.user_login AS userName, 
    u.user_registered AS signUpDate, 
    MAX(CASE WHEN um.meta_key = 'first_name' THEN um.meta_value END) AS firstName,
    MAX(CASE WHEN um.meta_key = 'last_name' THEN um.meta_value END) AS lastName,
    MAX(CASE WHEN um.meta_key = 'billing_phone' THEN um.meta_value END) AS billingPhone,
    MAX(CASE WHEN um.meta_key = 'shipping_phone' THEN um.meta_value END) AS shippingPhone,
    COALESCE(o.orderCount, 0) AS orderCount,
    COALESCE(o.moneySpent, 0) AS moneySpent
FROM wp_users u
LEFT JOIN wp_usermeta um ON um.user_id = u.ID
LEFT JOIN (
    SELECT 
        pm_customer.meta_value AS user_id,
        COUNT(pm_total.meta_value) AS orderCount,
        SUM(pm_total.meta_value + 0) AS moneySpent
    FROM wp_postmeta pm_customer
    LEFT JOIN wp_postmeta pm_total 
        ON pm_total.post_id = pm_customer.post_id 
        AND pm_total.meta_key = '_order_total'
    WHERE pm_customer.meta_key = '_customer_user'
    GROUP BY pm_customer.meta_value
) o ON o.user_id = u.ID
GROUP BY u.ID

优化点说明 #

• 用单次LEFT JOIN关联wp_usermeta表，配合CASE WHEN条件聚合提取不同meta_key对应的字段值，仅需扫描一次wp_usermeta表即可获取所有用户的扩展字段，性能远高于原有逐行子查询写法
• 把重复执行两次的订单统计逻辑合并为一个预聚合子查询：一次性计算所有用户的订单数和消费总额，再和用户表关联，避免了每个用户都重复查询两次wp_postmeta表的问题
• 新增COALESCE兼容无订单用户的返回值，默认返回0和原有逻辑一致
• 求和逻辑里加+0是为了把meta_value的字符串类型自动转为数值计算，避免求和异常

进一步性能优化建议 #

可以给对应表增加联合索引，进一步降低查询IO开销：

• wp_usermeta加联合索引 (user_id, meta_key)
• wp_postmeta加联合索引 (meta_key, post_id)、(post_id, meta_key)

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