原来的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，真的是惊呆了，这差距也太大了吧。。。
总结：多表查询，在查询条件比较多的情况下，应该先在各个表内做筛选，将筛选之后的结果表再做关联查询。