使用聚合函数优化MySQL视图

Question

我有这个视图：

CREATE VIEW invoicereport
SELECT i.ID, SUM(o.Amount * o.Price) AS Total
FROM invoice i
JOIN `order` o ON i.ID = o.InvoiceID
GROUP BY i.ID

直接运行此查询只需要0.03秒。但运行视图需要2秒，即使我执行 SELECT * FROM invoicereport WHERE ID=9000 仍需要2秒。

因此，我将视图重写为：

CREATE VIEW invoicereport
SELECT i.ID, (SELECT SUM(Amount * Price) FROM `order` WHERE InvoiceID=i.ID) AS Total
FROM invoice i

虽然子查询只会被针对ID 9000执行。但当我执行SELECT * FROM invoicereport WHERE ID=9000时，查询变得更加缓慢，需要3秒钟才能完成。

是否有其他优化此查询的方法？

- mumu

---

MySQL视图成为性能问题制造者 - John Woo

Answer 1

我认为没有必要将这些表连接起来，只需对order进行分组即可（仅是我的个人意见）。类似这样：

CREATE VIEW INVOICEREPORT
SELECT O.INVOICEID as ID, SUM(O.AMOUNT * O.PRICE) AS TOTAL
FROM `ORDER` O
GROUP BY O.INVOICEID

然后您可以将此视图与其他视图连接，并在order上使用索引。

- Ganbat Bayarbaatar

---

表格发票包含其他信息，例如客户姓名、电话等。 - mumu

尝试这样写：`select i.id, i.customer_name, i.tel, ir.total from invoice i join invoicereport ir on ir.id = i.id` - Ganbat Bayarbaatar

Answer 2

我同意Kuya John的评论。在这样一个简单的总结中，甚至不要试图将其作为视图来处理。它的简单性不需要进行连接。几年前我提供了另一个答案，其中有人正在使用现有视图，他的查询时间非常长。当我要求回到原始表格进行查询并直接编写时，查询仍然非常简单易读且极快。不要使用视图，只需直接选择语句。

像子查询一样，视图通常会导致查询时间过长，我尽量避免使用它们。

由于您还在寻找其他发票数据，因此应该没有问题...但更大的确认是...您的订单表是否具有发票ID的索引？

SELECT 
      I.ID, 
      I.Customer_Name,
      I.Tel,
      SUM(o.Amount * o.Price) AS Total
   FROM 
      invoice I
         JOIN `order` o ON I.ID = O.InvoiceID
   WHERE
      I.ID = 9000
   GROUP BY 
      I.ID

- DRapp

---

InvoiceID是一个索引。实际查询比这个复杂得多，它包含COUNT(o.ID) AS OrderCount, SUM(IF(mg.Type='Food',1,0)) AS FoodCount, SUM(IF(mg.Type='Drink',1,0)) AS DrinkCount等等。而且order与menu连接，再与menugroup连接。 - mumu