mysql - 优化每个客户从销售表中购买的精选产品详细信息

Question

我有 4 个表:客户、产品、销售和 Sale_Items。我使用下面的查询从中提取数据。

SELECT (
            SELECT c.name 
            FROM Customers c
            WHERE s.customer_id=c.id
            ) customer
    ,(
        Select group_concat(description)
        FROM (
            SELECT si.id
                ,si.sale_id
                ,concat("x", si.Qty, " ", p.name, " ",(si.total)) description
            FROM Sale_Items si
            LEFT JOIN Products p ON p.id = si.product_id
            ) p
        where s.id = Sale_ID
        GROUP BY Sale_ID
        ) detail,
                            s.total
FROM Sales s

查询产生了结果，但只有 2000 条记录，它变得很慢(需要 114 秒才能完成)

Customer    Product            Total
--------------------------------------
James       x1 ItemA 10.00  75.00
            x3 ItemB 15.00
            x1 ItemC 20.00

Mark        x2 ItemA 10.00  50.00
            x2 ItemB 15.00

Bisi        x1 ItemC 20.00  30.00
            x2 ItemA 10.00

我怎样才能让它更快？

这里已经进行了尝试

https://www.db-fiddle.com/f/pkL2HtsT659EXgRSevFSAm/4

Answer 1

如果我们想坚持使用相关子查询，我们可以消除内联 View p。

将从 Sales 检索到的每一行都会具体化。外部查询的 WHERE 子句中的谓词不会被“推送”到 View 中。因此，物化 View (或 MySQL 术语中的“派生表”)将是一个完整的集合，我们将从中挑选出几行。我们将对 Sales 中的每一行重复此操作。

展开该派生表应该会给我们带来一些性能优势。对于从 Sales 返回的少量行并定义了合适的索引，这将是合理的方法。也就是说，如果我们使用 WHERE 子句限制外部查询检查的行数。对于大量行，这些相关子查询会降低性能。

SELECT ( SELECT c.name 
           FROM Customers c
          WHERE c.id = s.customer_id
       ) AS customer

     , ( SELECT GROUP_CONCAT(CONCAT('x',si.Qty,' ',p.name,' ',si.total) ORDER BY p.name SEPARATOR '\r\n')
           FROM Sale_Items si
           LEFT
           JOIN Products p
             ON p.id = si.product_id
          WHERE si.sale_id = s.id
       ) AS detail

     , s.total

  FROM Sales s

 WHERE ... 

 ORDER
    BY ...

如果查询返回来自Sales的所有行，并且我们正在执行整个bloomin'集，那么我倾向于避免相关子查询。 (这是因为子查询是针对外部查询返回的每一行执行的。就性能而言，这些子查询将吃掉我们的午餐，并返回大量行。)

假设id在客户中是唯一的，我们通常使用连接操作会更好。

SELECT c.name AS customer
     , d.detail
     , s.total
  FROM Sales s
  LEFT
  JOIN Customers c
    ON c.id = s.customer_id
  LEFT
  JOIN ( SELECT si.sale_id
              , GROUP_CONCAT(CONCAT('x',si.Qty,' ',p.name,' ',si.total) ORDER BY p.name SEPARATOR '\r\n') AS detail
           FROM Sale_Items si
           LEFT
           JOIN Products p
             ON p.id = si.product_id
          GROUP
             BY si.sale_id
       ) d
    ON d.sale_id = s.id
 ORDER
    BY ...

内联 View d 对于大型集合来说会很昂贵；但至少我们只执行一次该查询，将结果具体化到“派生表”中。然后外部查询可以运行，并从派生表中检索行。