SQL优化-3个表-多和- 20k记录-12秒

Question

我有三张桌子可以点菜。

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

根据下面的订单，我按日期得到“折扣/订购价值/产品/税金总额”。但是，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

Answer 1

我建议您放弃选择列表中的关联子查询，并对内联视图使用联接操作。我会这样写这个查询：

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

Explain输出将显示派生表；使用MySQL 5.5和更早版本，它将被取消索引。

如果从orders表检索的行是整个表的一个小子集，那么orders表上的适当索引可能允许MySQL使用索引范围扫描操作：

... ON orders (odate, status)

而且，同样，如果这是表中的一小部分行，那么内联视图查询中的联接操作将生成一个更小的派生表，这可能会提高性能。在内联视图查询中重复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完全满足索引中的视图查询(解释将显示“使用索引”)

... ON order_products (orderID, psum)

(如果odate列的数据类型为DATE，则DATE()包装器函数是不必要的，MySQL可能可以避免“使用文件短”。如果数据类型是日期时间或时间戳数据类型，这说明了DATE()包装器函数的使用。请注意，odate列上的谓词将返回在该月的第一个午夜，直到该月30日的午夜，并包含午夜之间的值的行。通常情况下，我们只返回一整天，避免排除30日午夜。

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

(我在文字值中添加的时间组件是不必要的；添加它只是为了说明在与日期时间或时间戳比较时会考虑时间组件的概念。)