我有 3 个订单表。

1-) orders

orderID ⎜odate ⎜ fullname ⎜address ⎜ ordersum ⎜ status_id

2-) order_products

id ⎜orderID ⎜count⎜ psum ⎜cost

3-) order_sum

orderID ⎜shipping ⎜tax ⎜coupon ⎜ discount

对于下面的订单，我按日期获得“折扣/订单值(value)/产品总和/税总和”。但是查询 20000 条记录需要 12 秒。我该如何优化它？

SELECT
    DATE(o.odate) AS ODate,
    COUNT(o.orderID) AS OTotal,
    (
        SELECT
            SUM(op.psum)
        FROM
            order_products op
        INNER JOIN orders oo ON oo.orderID = op.orderID
        WHERE
            DATE(oo.odate) = DATE(o.odate)
        AND oo.status_id NOT IN (24, 26, 27, 28, 29)
    ) AS ProductSum,
    SUM(os.shipping) / 118 * 100 AS Shipping,
    SUM(os.tax) AS Tax,
    SUM(o.ordersum) AS allPayments,
    SUM(os.coupon) AS CouponDiscount,
    SUM(os.discount) AS Discount,
    (
        SUM(o.ordersum) / COUNT(DISTINCT o.orderID)
    ) AS BasketAVG
FROM
    orders o
JOIN order_sum os ON os.orderID = o.orderID
WHERE
    o.status_id NOT IN (24, 26, 27, 28, 29)
AND o.odate BETWEEN '2014-12-01'
AND '2014-12-30'
GROUP BY
    DATE(o.odate)
ORDER BY
    o.odate ASC;

扩展:

+----+--------------------+-------+--------+-----------------+-----------+---------+-----------------------------+-------+----------------------------------------------+
| id | select_type        | table | type   | possible_keys   | key       | key_len | ref                         | rows  | Extra                                        |
+----+--------------------+-------+--------+-----------------+-----------+---------+-----------------------------+-------+----------------------------------------------+
|  1 | PRIMARY            | o     | ALL    | PRIMARY,orders  | NULL      | NULL    | NULL                        | 10645 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | os    | eq_ref | PRIMARY,ordersum| PRIMARY   | 4       | cikolat_system.o.orderID    |     1 |                                              |
|  2 | DEPENDENT SUBQUERY | oo    | ALL    | PRIMARY,orders  | NULL      | NULL    | NULL                        | 10645 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | op    | ref    | oproducts2      | oproducts2| 5       | cikolat_system.oo.orderID   |     1 | Using where                                  |
+----+--------------------+-------+--------+-----------------+-----------+---------+-----------------------------+-------+----------------------------------------------+

SQLFiddle:http://sqlfiddle.com/#!2/81c921/1

最佳答案

我建议您放弃 SELECT 列表中的相关子查询，并使用连接操作到内联 View 。我会这样写查询:

SELECT DATE(o.odate)                AS ODate
     , COUNT(o.orderID)             AS OTotal
     , ps.ProductSum                AS ProductSum
     , SUM(os.shipping) / 118 * 100 AS Shipping
     , SUM(os.tax)                  AS Tax
     , SUM(o.ordersum)              AS allPayments
     , SUM(os.coupon)               AS CouponDiscount
     , SUM(os.discount)             AS Discount
     , SUM(o.ordersum)
       / COUNT(DISTINCT o.orderID)  AS BasketAVG
  FROM orders o
  JOIN order_sum os
    ON os.orderID = o.orderID
  LEFT
  JOIN ( SELECT op.orderID
              , SUM(op.psum) AS ProductSum
           FROM order_products op
          GROUP BY op.orderID 
       ) ps
    ON ps.orderID = o.orderID
 WHERE o.status_id NOT IN (24, 26, 27, 28, 29)
   AND o.odate BETWEEN '2014-12-01' AND '2014-12-30'
 GROUP BY DATE(o.odate)
 ORDER BY DATE(o.odate) ASC

解释输出将显示派生表； MySQL 5.5 及更早版本将取消索引。

如果从 orders 表中检索到的行是整个表的一小部分，则 orders 表上的适当索引可能允许 MySQL 使用索引范围扫描操作:

... ON orders (odate, status)

而且，如果这是表中行的一小部分，那么内联 View 查询中的 JOIN 操作将生成一个小得多的派生表，这可能会提高性能。在内联 View 查询中对 orders 表重复谓词，例如:

  LEFT
  JOIN ( SELECT op.orderID
              , SUM(op.psum) AS ProductSum
           FROM order_products op
           JOIN orders oo
             ON oo.orderID = op.orderID
          WHERE oo.status_id NOT IN (24, 26, 27, 28, 29)
            AND oo.odate BETWEEN '2014-12-01' AND '2014-12-30'
          GROUP BY op.orderID
       ) ps

此外，order_products 上的覆盖索引将允许 MySQL 完全从索引满足 View 查询(EXPLAIN 将显示“使用索引”)

... ON order_products (orderID, psum)

(如果 odate 列的数据类型是 DATE，那么 DATE() 包装函数是不必要的，MySQL 可以避免“使用文件排序”。如果数据类型是 DATETIME 或 TIMESTAMP 数据类型，这解释了 DATE() 包装函数的使用。请注意，odate 列上的谓词将返回值介于每月第一天午夜到每月 30 日午夜之间的行。通常，我们只返回整日，并避免排除 30 日午夜。

   AND o.odate >= '2014-12-01 00:00:00'
   AND o.odate <  '2014-12-30 00:00:00'

(我在文字值中添加时间部分是不必要的；添加只是为了说明在与日期时间或时间戳进行比较时考虑时间部分的想法。)

关于mysql - SQL 优化 - 3 个表 - 多个 SUM - 20k 记录 - 12 秒，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/27707214/