Mysql查询11000条记录只需要20分钟。如何优化下面的mysql查询select查询在where子句中不存在

Question

SELECT DISTINCT ACA.Application_No, AC.FirstName,AC.Id,AC.LastName,AC.MobileNo,CL.leadId        FROM ABSLI_PAYMENT_TRANSACTION APT             INNER JOIN ABSLI_CUSTOMER_APPLICATION ACA ON ACA.Policy_No=APT.policyId             INNER JOIN ABSLI_CUSTOMER AC ON AC.Id=ACA.CustomerId             LEFT JOIN ABSLI_CUSTOMER_LEAD CL ON CL.policyId = ACA.Policy_No             INNER JOIN ABSLI_Policy_Status_Tracking pst ON pst.policyId = APT.policyId        WHERE APT.paymentStatus='Y'             AND NOT EXISTS (SELECT 1 FROM ABSLI_SERVICE_STATUS WHERE PolicyNo=APT.policyId AND NAME = 'APEX_Validate')             AND ACA.Application_No NOT IN (SELECT RT.ApplicationNumber FROM ABSLI_REFUND_TRANSACTION RT WHERE RT.Status != 'Retain')        ORDER BY pst.updatedDate DESC;

Answer 1

关联子查询在较大的数据集上可能成本高昂，您可能需要尝试转换

...
AND NOT EXISTS (SELECT 1 FROM ABSLI_SERVICE_STATUS WHERE PolicyNo=APT.policyId AND NAME = 'APEX_Validate')
...

类似于

...
LEFT JOIN ABSLI_SERVICE_STATUS AS ss ON APT.policyId = ss.PolicyNo AND ss.NAME = 'APEX_Validate'
...
AND ss.NAME IS NULL

通常，我建议使用 ss 中的“id”字段进行 IS NULL 检查，但 NAME 显然存在(来自您的查询)并且不能被'APEX_Validate' 同时为 NULL。另外，如果 PolicyNo, Name 上有复合索引，则可能可以在不访问表本身的情况下使用该索引。)