MySQL需要查询优化

问题描述：

我有一个表现较慢的查询。我知道使用从属子查询是不好的，但我想不出另一种获取我想要的数据的方式。MySQL需要查询优化

基本上，我想标记过去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;

客户表中有大约12K的记录，Invoice_Header有大约2mill记录。

我有inv_date，account_number（在这两个表中）的索引。

任何建议如何加快这一点，将不胜感激。

答

我建议：

SELECT 
    c.name, 
    c.id, 
    c.latitude, 
    c.longitude 
FROM 
    Customer AS c 
    INNER JOIN (
     SELECT account_number, count(*) AS invoice_count 
     FROM Invoice_Header 
     WHERE inv_date >= '2011-03-02' AND inv_date <= '2011-10-02' 
     GROUP BY account_number 
    ) AS lsm 
     ON c.account_number = lsm.account_number 
    LEFT JOIN (
     SELECT account_number, count(*) AS invoice_count 
     FROM Invoice_Header 
     WHERE inv_date > '2011-10-02' 
     GROUP BY account_number 
    ) AS lm 
     ON c.account_number = lm.account_number 
    WHERE 
     lsm.invoice_count >= 50 
     AND IFNULL(lm.invoice_count, 0) = 0

+1这应该也适用。 –

答

尝试运行您的查询与explain并查看是否需要其他索引。

这应该始终** **是第一步。 –

答

这应该消除相关子查询是显著更快：

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

+1打我吧 – Randy

MySQL不支持MINUS - 所以Xint0的答案似乎工作得最好... – Tom

是的，MySQL中没有Minus。 – Imdad

答

select 
    C.name, 
    C.id, 
    C.latitude, 
    C.longitude, 
    I.account_number,   
    count(IF(I.inv_date>='2011-03-02' AND I.inv_date <='2011-10-02',I.inv_date,NULL)) as inv_count_6, 
    count(IF(I.inv_date > '2011-10-02',I.inv_date,NULL)) as inv_count_1 

from Customer C 
LEFT JOIN Invoice_Header I 
ON C.account_number = I.account_number 

GROUP BY C.id, I.account_number 
HAVING inv_count_6 >= 50 AND inv_count_1=0 
WHERE I.inv_date BETWEEN '2011-03-02' AND '2011-10-02'

注：

1.发票是至少50如此条件是> = 50不> 50 。

2.You有索引添加到列inv_date

我已更改查询现在检查 – Imdad

我不认为这可能有效。您的where子句对inv_date有两个相互冲突的要求。它必须在两个日期之间和最后一个日期之后，这是不可能的。所以这个查询总是返回零结果。 –

是的，它会工作100％。我已经更新了条件。核实。可能你对mysql的语法不甚了解。我认为你对 count（IF（I.inv_date> ='2011-03-02'AND I.inv_date '2011-10-02'，I.inv_date，NULL））inv_count_1 – Imdad

MySQL需要查询优化

相关推荐