mysql – SQL优化 – 3个表 – 多个SUM – 20k记录 – 12秒
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql – SQL优化 – 3个表 – 多个SUM – 20k记录 – 12秒，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4298字，纯文字阅读大概需要7分钟。
内容图文
我订购了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
按照下面的顺序,我按日期获得“折扣/订单价值/产品总数/税额”.但是对于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;
Exlpand：
+----+--------------------+-------+--------+-----------------+-----------+---------+-----------------------------+-------+----------------------------------------------+
| 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列表中的相关子查询,并使用连接操作进行内联视图.我写这样的查询：
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)
而且,如果这是表中的一小部分行,那么内联视图查询中的JOIN操作将产生一个小得多的派生表,这可以提高性能.在内联视图查询中的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完全从索引中满足视图查询(EXPLAIN将显示“使用索引”)
... ON order_products (orderID, psum)
(如果odate列的数据类型是DATE,那么DATE()包装函数是不必要的,并且MySQL可以避免“使用filesort”.如果数据类型是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秒全部内容，希望文章能够帮你解决mysql – SQL优化 – 3个表 – 多个SUM – 20k记录 – 12秒所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。