记一次简单的SQL优化

原来的sql是这样写的

 1 SELECT
 2     d.ONSALE_BARCODE,
 3     d.ONSALE_NAME,
 4     c.ONSALE_ID,
 5     CAST(
 6         AVG(c.CUSTOMARY_PRICE) AS DECIMAL (18, 1)
 7     ) AS CUSTOMARY_PRICE,
 8     CAST(
 9         AVG(c.CONSTANT_PRICE) AS DECIMAL (18, 1)
10     ) AS CONSTANT_PRICE,
11     CAST(
12         AVG(c.RETAIL_ITEM_PRICE) AS DECIMAL (18, 1)
13     ) AS RETAIL_ITEM_PRICE,
14     CAST(
15         AVG(c.RETAIL_PACKAGE_PRICE) AS DECIMAL (18, 1)
16     ) AS RETAIL_PACKAGE_PRICE
17 FROM
18     yzd_retailer a
19 LEFT JOIN sur_main b ON a.USER_ID = b.USER_ID
20 LEFT JOIN sur_main_sail c ON c.SUR_ID = b.SUR_ID
21 LEFT JOIN ret_on_sale d ON d.ONSALE_ID = c.ONSALE_ID
22 WHERE
23     a.RET_AREA IN (01, 10, 20, 30, 40, 50, 60)
24 AND a.RET_MARKET IN (0, 1)
25 AND a.RET_TYPE IN (0, 1, 2, 3, 4, 5, 6)
26 AND a.RET_SCALE IN (0, 1, 2)
27 AND c.sur_id IS NOT NULL
28 GROUP BY
29     c.ONSALE_ID

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

 1 SELECT
 2     d.ONSALE_BARCODE,
 3     d.ONSALE_NAME,
 4     c.ONSALE_ID,
 5     CAST(
 6         AVG(c.CUSTOMARY_PRICE) AS DECIMAL (18, 1)
 7     ) AS CUSTOMARY_PRICE,
 8     CAST(
 9         AVG(c.CONSTANT_PRICE) AS DECIMAL (18, 1)
10     ) AS CONSTANT_PRICE,
11     CAST(
12         AVG(c.RETAIL_ITEM_PRICE) AS DECIMAL (18, 1)
13     ) AS RETAIL_ITEM_PRICE,
14     CAST(
15         AVG(c.RETAIL_PACKAGE_PRICE) AS DECIMAL (18, 1)
16     ) AS RETAIL_PACKAGE_PRICE
17 FROM
18     (
19         SELECT
20             *
21         FROM
22             yzd_retailer
23         WHERE
24             RET_AREA IN (01, 10, 20, 30, 40, 50, 60)
25         AND RET_MARKET IN (0, 1)
26         AND RET_TYPE IN (0, 1, 2, 3, 4, 5, 6)
27         AND RET_SCALE IN (0, 1, 2)
28     ) a,
29     sur_main b,
30     sur_main_sail c,
31     ret_on_sale d
32 WHERE
33     a.USER_ID = b.USER_ID
34 AND c.SUR_ID = b.SUR_ID
35 AND d.ONSALE_ID = c.ONSALE_ID
36 GROUP BY
37     c.ONSALE_ID

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

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