如何用MySQL Inner Join替代多SELECT语句改写查询

我用SELECT语句写了一个从两张表取数据的查询，能正常运行但执行和取数耗时很长（每张表仅40k行数据）。我是MySQL逻辑与查询的新手，希望用Inner Join改写这个查询。

原查询语句：

SELECT * FROM 
    (SELECT a_sno AS UID,a_name AS `Name`,a_emailid AS EmailID, 
            a_mobile AS Mobile,a_othermobile AS Broker_Mobile,
            a_vendorname AS Broker_Name,a_planname AS Subscription_Plan, 
            a_createdtime AS Date_of_Registration, 
            a_plandatetill AS Subscription_End_Date,
            CAST(
                IFNULL(
                    (SELECT a_amount 
                    FROM details_payments 
                    WHERE a_status='success' 
                    AND a_uid=ll.a_sno 
                    ORDER BY a_sno DESC LIMIT 1)
                ,'-') 
             AS CHAR) AS Subscription_Amount,
             CAST(
                IFNULL(
                    (SELECT a_confirm_time 
                    FROM details_payments 
                    WHERE a_status='success' 
                    AND a_uid=ll.a_sno ORDER BY a_sno DESC LIMIT 1)
                ,'-')
             AS CHAR) AS Date_of_Subscription,
             CAST(
                IFNULL(
                    (SELECT a_registerednotsub_rmks 
                    FROM whatsapp.details_remarks 
                    WHERE a_uid=ll.a_sno 
                    ORDER BY a_sno DESC LIMIT 1)
                ,'-')
             AS CHAR) AS Remarks
-- ,IFNULL((SELECT COUNT(*) FROM details_payments WHERE a_status='success' AND a_uid=ll.a_sno),'') AS total_count_payments
     FROM details_login ll 
     WHERE ll.a_createdtime BETWEEN "2022-05-09" AND "2022-06-14" 
     AND ll.a_activated = 1 
     AND (ll.a_planname = 'Free Trial' OR ll.a_planname = '-' OR ll.a_planname = '')
     AND(a_fyersid != '-' 
         OR (a_fyersid = '-' 
             AND ll.a_mobile !='0' 
             AND ll.a_mobile != '' 
             AND ll.a_mobile !='-' 
             AND LENGTH(ll.a_mobile) = '10' 
             AND ll.a_mobile != '6666666666' 
             AND ll.a_mobile != '7777777777'
             AND ll.a_mobile != '8888888888' 
             AND ll.a_mobile != '9999999999' 
             AND (a_mobile LIKE '6%' AND LENGTH(a_mobile) = '10')
             OR (a_mobile LIKE '7%' AND LENGTH(a_mobile) = '10') 
             OR (a_mobile LIKE '8%' AND LENGTH(a_mobile) = '10') 
             OR (a_mobile LIKE '9%' AND LENGTH(a_mobile) = '10')
         )
      )
      ORDER BY a_sno DESC) jjjjj 
      WHERE Subscription_Amount ='-' 
      GROUP BY Mobile;

改写后的查询（LEFT JOIN + 窗口函数） #

原查询的性能瓶颈在于多个逐行执行的关联子查询，改用窗口函数先批量获取每个用户的最新记录，再通过JOIN关联主表，能大幅提升效率：

SELECT 
    ll.a_sno AS UID,
    ll.a_name AS `Name`,
    ll.a_emailid AS EmailID,
    ll.a_mobile AS Mobile,
    ll.a_othermobile AS Broker_Mobile,
    ll.a_vendorname AS Broker_Name,
    ll.a_planname AS Subscription_Plan,
    ll.a_createdtime AS Date_of_Registration,
    ll.a_plandatetill AS Subscription_End_Date,
    CAST(IFNULL(p.a_amount, '-') AS CHAR) AS Subscription_Amount,
    CAST(IFNULL(p.a_confirm_time, '-') AS CHAR) AS Date_of_Subscription,
    CAST(IFNULL(r.a_registerednotsub_rmks, '-') AS CHAR) AS Remarks
FROM details_login ll
-- 关联用户最新的成功支付记录
LEFT JOIN (
    SELECT 
        a_uid,
        a_amount,
        a_confirm_time,
        ROW_NUMBER() OVER(PARTITION BY a_uid ORDER BY a_sno DESC) AS rn
    FROM details_payments
    WHERE a_status = 'success'
) p ON ll.a_sno = p.a_uid AND p.rn = 1
-- 关联用户最新的备注记录
LEFT JOIN (
    SELECT 
        a_uid,
        a_registerednotsub_rmks,
        ROW_NUMBER() OVER(PARTITION BY a_uid ORDER BY a_sno DESC) AS rn
    FROM whatsapp.details_remarks
) r ON ll.a_sno = r.a_uid AND r.rn = 1
WHERE 
    ll.a_createdtime BETWEEN '2022-05-09' AND '2022-06-14'
    AND ll.a_activated = 1
    AND ll.a_planname IN ('Free Trial', '-', '')
    AND (
        ll.a_fyersid != '-'
        OR (
            ll.a_fyersid = '-'
            AND ll.a_mobile NOT IN ('0', '', '-', '6666666666', '7777777777', '8888888888', '9999999999')
            AND LENGTH(ll.a_mobile) = 10
            AND ll.a_mobile REGEXP '^[6-9]'
        )
    )
    AND p.a_amount IS NULL -- 等价于原查询的Subscription_Amount = '-'
GROUP BY ll.a_mobile;

优化说明 #

• 窗口函数ROW_NUMBER()对支付表和备注表按用户分组，仅扫描两张表各一次，替代原查询中逐行执行的N次子查询
• 简化手机号校验逻辑：用REGEXP '^[6-9]'替代多个LIKE条件，用IN/NOT IN替代重复的OR/!=，可读性和执行效率都更高
• 原查询中Subscription_Amount='-'等价于无成功支付记录，直接用p.a_amount IS NULL判断更直接

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