问题描述

谁能解答下为什么这两个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,100

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 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显示