问题描述
- 谁能解答下为什么这两个SQL,只是关联顺序不一样,性能差别那么大
-
第一个 :
SELECT
a.request_detail_id,
a.vendorid,
a.request_id,
a.s1,
a.s2,
a.s3,
a.s4,
a.profit,
a.ratio,
a.requestqty,
a.stockqty,
a.sponroadqty,
a.reqonroadqty,
a.reqpurqty,
a.piqty,
a.ispurchase,
a.remark,
a.skuid,
a.v_req_detail_id,
a.is_rollback_collection,
a.v_req_detail_id_sort,
a.reqdeliverydate,
b.sku,
b.description,
bi.purspec,
bi.purdesc,
a.purpkgid,
a.skulabel,
a.skulabel skulable,
a.purpkg,
a.stopkg,
a.purnote1,
a.purnote2,
-- IFNULL(sup.minqty ,- 1) AS minqty,
o. STATUS,
o.shipping_date,
SUM(
CASE
WHEN o.shipping_date >= '2015-06-15' THEN
d.quantity
ELSE
0
END
) AS quantityT,
SUM(
CASE
WHEN o.shipping_date >= '2015-05-16'
AND o.shipping_date < '2015-06-15' THEN
d.quantity
ELSE
0
END
) AS quantityS,SUM( CASE WHEN o.shipping_date >= '2015-04-16' AND o.shipping_date < '2015-05-16' THEN d.quantity ELSE 0 END ) AS quantityN
FROM
rs_request_detail a
LEFT JOIN rs_request rq ON rq.request_id = a.request_id
INNER JOIN bs_item_locale b ON a.skuid = b.skuid
AND b.lang_id = 1
INNER JOIN bs_item bi ON a.skuid = bi.skuid
LEFT JOIN (
SELECT
psi.skuid,
IFNULL(
MIN(IF ( psi.minqty = 0, 999999999, psi.minqty ) ) ,- 1 ) AS minqty FROM pr_supplier_item psi LEFT JOIN pr_supplier ps ON ps.supplierid = psi.supplierid AND ps.inactive = 1 WHERE psi.itemstatus = 4 GROUP BY skuid
) AS sup ON sup.skuid = a.skuid
LEFT JOIN so_order_detail d ON d.skuid = b.skuid
LEFT JOIN so_order o ON o.order_id = d.order_id AND o. STATUS = 4 and o.shipping_date < '2015-05-16'WHERE
(1 = 1)
AND a.request_id = 15259
AND b.lang_id = 1
GROUP BY
a.request_detail_id
ORDER BY
a.v_req_detail_id_sort DESC,
a.v_req_detail_id ASC,
a.reqpurqty DESC
LIMIT 0,100SELECT
a.request_detail_id,
a.vendorid,
a.request_id,
a.s1,
a.s2,
a.s3,
a.s4,
a.profit,
a.ratio,
a.requestqty,
a.stockqty,
a.sponroadqty,
a.reqonroadqty,
a.reqpurqty,
a.piqty,
a.ispurchase,
a.remark,
a.skuid,
a.v_req_detail_id,
a.is_rollback_collection,
a.v_req_detail_id_sort,
a.reqdeliverydate,
b.sku,
b.description,
bi.purspec,
bi.purdesc,
a.purpkgid,
a.skulabel,
a.skulabel skulable,
a.purpkg,
a.stopkg,
a.purnote1,
a.purnote2,
IFNULL(sup.minqty ,- 1) AS minqty,
o. STATUS,
o.shipping_date,
SUM(
CASE
WHEN o.shipping_date >= '2015-06-15' THEN
d.quantity
ELSE
0
END
) AS quantityT,
SUM(
CASE
WHEN o.shipping_date >= '2015-05-16'
AND o.shipping_date < '2015-06-15' THEN
d.quantity
ELSE
0
END
) AS quantityS,SUM( CASE WHEN o.shipping_date >= '2015-04-16' AND o.shipping_date < '2015-05-16' THEN d.quantity ELSE 0 END ) AS quantityN
FROM
rs_request_detail a
LEFT JOIN rs_request rq ON rq.request_id = a.request_id
INNER JOIN bs_item_locale b ON a.skuid = b.skuid
AND b.lang_id = 1
INNER JOIN bs_item bi ON a.skuid = bi.skuidLEFT JOIN so_order_detail d ON d.skuid = b.skuid
LEFT JOIN so_order o ON o.order_id = d.order_id AND o. STATUS = 4 and o.shipping_date < '2015-05-16'
LEFT JOIN (
SELECT
psi.skuid,
IFNULL(
MIN(IF ( psi.minqty = 0, 999999999, psi.minqty ) ) ,- 1 ) AS minqty FROM pr_supplier_item psi LEFT JOIN pr_supplier ps ON ps.supplierid = psi.supplierid AND ps.inactive = 1 WHERE psi.itemstatus = 4 GROUP BY skuid
) AS sup ON sup.skuid = a.skuid
WHERE
(1 = 1)
AND a.request_id = 15259
AND b.lang_id = 1
GROUP BY
a.request_detail_id
ORDER BY
a.v_req_detail_id_sort DESC,
a.v_req_detail_id ASC,
a.reqpurqty DESC
LIMIT 0,100
解决方案
这语句也太长了吧,你只说哪里不同性能不同就可以了
解决方案二:
看LEFT JOIN结合的表的记录数,如果结合的记录数越少越快,表记录少的放前面越快。
解决方案三:
LEFT JOIN 哪个在前哪个当主表来关联数据,前的全部显示,后的只显示有关联到的数据其他null显示