2 zjg448281779 zjg448281779 于 2015.07.15 20:37 提问

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

图片说明

3个回答

u012216727
u012216727   Ds   Rxr 2015.07.15 21:23

这语句也太长了吧,你只说哪里不同性能不同就可以了

zjg448281779
zjg448281779 有9999999那个子查询,关联在INNER JOIN bs_item bi ON a.skuid = bi.skuid后面就快,关联在其他地方都慢,这是为什么
2 年多之前 回复
danielinbiti
danielinbiti   Ds   Rxr 2015.07.15 21:54

看LEFT JOIN结合的表的记录数,如果结合的记录数越少越快,表记录少的放前面越快。

zjg448281779
zjg448281779 但是我把有999999的那个子查询放在主表的后面速度也很慢 只有放在INNER JOIN bs_item bi ON a.skuid = bi.skuid这后面才快
2 年多之前 回复
strutce
strutce   Ds   Rxr 2015.07.16 00:03

LEFT JOIN 哪个在前哪个当主表来关联数据,前的全部显示,后的只显示有关联到的数据其他null显示

Csdn user default icon
上传中...
上传图片
插入图片