记一次简单的SQL优化

原来的sql是这样写的

SELECT
    d.ONSALE_BARCODE,
    d.ONSALE_NAME,
    c.ONSALE_ID,
    CAST(
        AVG(c.CUSTOMARY_PRICE) AS DECIMAL (18, 1)
    ) AS CUSTOMARY_PRICE,
    CAST(
        AVG(c.CONSTANT_PRICE) AS DECIMAL (18, 1)
    ) AS CONSTANT_PRICE,
    CAST(
        AVG(c.RETAIL_ITEM_PRICE) AS DECIMAL (18, 1)
    ) AS RETAIL_ITEM_PRICE,
    CAST(
        AVG(c.RETAIL_PACKAGE_PRICE) AS DECIMAL (18, 1)
    ) AS RETAIL_PACKAGE_PRICE
FROM
    yzd_retailer a
LEFT JOIN sur_main b ON a.USER_ID = b.USER_ID
LEFT JOIN sur_main_sail c ON c.SUR_ID = b.SUR_ID
LEFT JOIN ret_on_sale d ON d.ONSALE_ID = c.ONSALE_ID
WHERE
    a.RET_AREA IN (01, 10, 20, 30, 40, 50, 60)
AND a.RET_MARKET IN (0, 1)
AND a.RET_TYPE IN (0, 1, 2, 3, 4, 5, 6)
AND a.RET_SCALE IN (0, 1, 2)
AND c.sur_id IS NOT NULL
GROUP BY
    c.ONSALE_ID

上面的sql执行需要60S，作为菜鸟的我以为无法优化了。结果在项目里跑的时候会出现504超时。。。让网站维护人员修改超时时间，结果没成功，眼看明天就要交了，这怎么行，于是只能预想着重新写方法了，不靠sql处理了。然而我并没有死心，觉得mysql应该可以再优化吧，不至于这几十万的数据就变得这么慢了，应该是我sql的问题，于是我就想方设法的改sql,突然想到，我应该试试先根据条件把零售户表筛选完，在放入关联查询。于是就做了如下修改

SELECT
    d.ONSALE_BARCODE,
    d.ONSALE_NAME,
    c.ONSALE_ID,
    CAST(
        AVG(c.CUSTOMARY_PRICE) AS DECIMAL (18, 1)
    ) AS CUSTOMARY_PRICE,
    CAST(
        AVG(c.CONSTANT_PRICE) AS DECIMAL (18, 1)
    ) AS CONSTANT_PRICE,
    CAST(
        AVG(c.RETAIL_ITEM_PRICE) AS DECIMAL (18, 1)
    ) AS RETAIL_ITEM_PRICE,
    CAST(
        AVG(c.RETAIL_PACKAGE_PRICE) AS DECIMAL (18, 1)
    ) AS RETAIL_PACKAGE_PRICE
FROM
    (
        SELECT
            *
        FROM
            yzd_retailer
        WHERE
            RET_AREA IN (01, 10, 20, 30, 40, 50, 60)
        AND RET_MARKET IN (0, 1)
        AND RET_TYPE IN (0, 1, 2, 3, 4, 5, 6)
        AND RET_SCALE IN (0, 1, 2)
    ) a,
    sur_main b,
    sur_main_sail c,
    ret_on_sale d
WHERE
    a.USER_ID = b.USER_ID
AND c.SUR_ID = b.SUR_ID
AND d.ONSALE_ID = c.ONSALE_ID
GROUP BY
    c.ONSALE_ID

奇迹发生了，竟然只用了0.5S，真的是惊呆了，这差距也太大了吧。。。

总结：多表查询，在查询条件比较多的情况下，应该先在各个表内做筛选，将筛选之后的结果表再做关联查询。