需要 MySQL 查询优化

Question

我有一个执行缓慢的查询。我知道使用依赖子查询不好，但我想不出另一种方法来获取我想要的数据。

基本上，我想标记在过去 6 个月内至少有 50 张发票但本月没有发票的客户。

这是我目前拥有的:

select
    Customer.name,
    Customer.id,
    Customer.latitude,
    Customer.longitude
from
    Customer
where
    EXISTS (
        SELECT 
            *
        FROM
            Invoice_Header
        WHERE
            Invoice_Header.inv_date BETWEEN '2011-03-02' AND '2011-10-02'
        AND
            Invoice_Header.account_number = Customer.account_number
        HAVING COUNT(invoice_num) > 50
    )
    AND NOT EXISTS (
        SELECT * 
        FROM 
            Invoice_Header 
        WHERE
            InvHead.inv_date > '2011-10-02'
        AND
            InvHead.account_number = Customer.account_number
    )
Group by name;

Customer 表大约有 12k 条记录，Invoice_Header 大约有 2mill 条记录。

我在 inv_date、account_number 上有索引(在两个表中)。

如有任何关于如何加快这一过程的建议，我们将不胜感激。

Answer 1

这应该消除相关的子查询并且明显更快:

SELECT c.name, c.id, c.latitude, c.longitude
FROM Customer c
INNER JOIN (
  SELECT account_number
  FROM Invoice_Header ih
  WHERE ih.inv_date BETWEEN '2011-03-02' AND '2011-10-02'
  GROUP BY account_number
  HAVING COUNT(*) > 50
  MINUS
  SELECT DISTINCT account_number
  FROM Invoice_Header ih
  WHERE ih.inv_date > '2011-10-02'
) tbl
ON tbl.account_number = c.account_number