我尝试运行此查询，但挂起了mysql服务器。它创造了大约500万条记录。甚至解释查询也不会及时返回任何内容。
Explain SELECT COUNT(*) FROM
(SELECT COUNT(DISTINCT Oms_Cart_Id ) Carts,Buyer_Id
FROM Reporting.Order_Details WHERE Buyer_Id IN
( SELECT Buyer_Id
FROM Reporting.Order_Details
WHERE Payment_Receive_Date>='2015-01-18 00:00:00' AND
Payment_Receive_Date<='2015-01-24 23:59:59'
GROUP BY Buyer_Id )
AND Payment_Receive_Date>='2014-09-01 00:00:00'
AND Payment_Receive_Date<='2015-01-24 23:59:59'
GROUP BY 2) X
WHERE Carts>1
LIMIT 0, 1000 ;
最佳答案
（是的，大脑受伤）
摆脱无用的LIMIT，只会返回一行。
将外部WHERE变成内部HAVING（没有充分的理由）
将IN（SELECT ...）变成JOIN。我开始进行自我联接，但是GROUP BY表示子查询可能只传递了几个Buyer_Id。所以，我做了JOIN（SELECT ...）
结果：
SELECT COUNT(*)
FROM
( SELECT COUNT(DISTINCT a.Oms_Cart_Id ) Carts,
a.Buyer_Id
FROM Reporting.Order_Details a
JOIN
( SELECT Buyer_Id
FROM Reporting.Order_Details
WHERE Payment_Receive_Date>='2015-01-18 00:00:00'
AND Payment_Receive_Date<='2015-01-24 23:59:59'
GROUP BY Buyer_Id
) AS b USING(Buyer_Id)
WHERE a.Payment_Receive_Date>='2014-09-01 00:00:00'
AND a.Payment_Receive_Date<='2015-01-24 23:59:59'
GROUP BY 2
HAVING Carts > 1
) X ;
Order_Details将从以下两个方面受益：INDEX(Buyer_Id)-用于JOIN。 （如果您已经有一个以Buyer_Id开头的索引，请不要添加该索引。）INDEX(Payment_Receive_Date, Buyer_Id)-子查询如果您确实只执行COUNT（*），也许可以进一步简化：
SELECT COUNT(DISTINCT Buyer_Id)
FROM Reporting.Order_Details a
JOIN
( SELECT Buyer_Id
FROM Reporting.Order_Details
WHERE Payment_Receive_Date>='2015-01-18 00:00:00'
AND Payment_Receive_Date<='2015-01-24 23:59:59'
GROUP BY Buyer_Id
) AS b USING(Buyer_Id)
WHERE Payment_Receive_Date>='2014-09-01 00:00:00'
AND Payment_Receive_Date<='2015-01-24 23:59:59'
GROUP BY 2
HAVING COUNT(DISTINCT a.Oms_Cart_Id) > 1