记一次SQL查询优化

合理的优化，即使面对大表，也是获得很好的查询性能。

表行数有52296296，是个大表，存储了各类商品每天的销售数据，每天都会往里面append数据，随着表的增大，组里的伙伴发现查询非常缓慢，已经达到调用方无法忍受的状态。我特此做了将优化过程做了记录，力图将优化过程的基本思路做个记录。

未合理调优前，查询如下语句

SELECT
	brand_name,
	item_number,
	sum(monthly_sales_num) AS monthly_sales_num
FROM
	dm_ecp_goods_sales
WHERE
	shop_type IN ('TB_TMALL', 'TB_JISHI')
AND YEAR = 2016
AND MONTH = 10
AND DAY = 25
GROUP BY
	brand_name,
	item_number
ORDER BY
	monthly_sales_num DESC
LIMIT 20;

需要117.456s。分析上述SQL，可以发现使用sum聚合行数，使用过了IN和where过滤搜索，使用过了group by聚合特定列，还使用了order by做top20的搜索。
分解优化步骤：
1 探索过滤条件的优化，能够尽快减少聚合计算量是非常关键的
先运行

SELECT
	brand_name,
	item_number,
	monthly_sales_num
FROM
	dm_ecp_goods_sales
WHERE
	shop_type IN ('TB_TMALL', 'TB_JISHI')
AND YEAR = 2016
AND MONTH = 10
AND DAY = 25

共用时75.630s，看来瓶颈已经在过滤查询这边出现了。先不建索引，调整下查询顺序，将IN操作放到最后，先过滤时间，如下

SELECT
	brand_name,
	item_number,
	monthly_sales_num
FROM
	dm_ecp_goods_sales
WHERE
YEAR = 2016
AND MONTH = 10
AND DAY = 25
AND shop_type IN ('TB_TMALL', 'TB_JISHI')

用时66.829s，提高了近10s，初步判断正确。为了体现顺序不当引起的耗时，再次调整时间的顺序：

SELECT
	brand_name,
	item_number,
	monthly_sales_num
FROM
	dm_ecp_goods_sales
WHERE
AND DAY = 25
AND MONTH = 10
YEAR = 2016
AND shop_type IN ('TB_TMALL', 'TB_JISHI')

用时63.440s，快了一点，说明覆盖率上没有特别的优势，主要是表中YEAR，MONTH，DAY使用的都是char类型，字符串比对明显快不了，改用ymd字段（int型）

SELECT
	brand_name,
	item_number,
	monthly_sales_num
FROM
	dm_ecp_goods_sales
WHERE
ymd=20161025
AND shop_type IN ('TB_TMALL', 'TB_JISHI')

用时57.693s，提高了不少了，但还是不能忍受。看来必须动用索引了，创建ymd和shopType组合索引，要注意顺序KEY idx_ymd_shopType (ymd,shop_type) USING BTREE。再执行上述SQL，用时25.123s，又提高了近一倍。查看下是否使用了索引

mysql> EXPLAIN SELECT brand_name, item_number, monthly_sales_num FROM dm_ecp_goods_sales WHERE ymd=20161025 AND shop_type IN ('TB_TMALL', 'TB_JISHI');

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| :-------- |
| 1 | SIMPLE | dm_ecp_goods_sales | range | idx_ymd_shopType | idx_ymd_shopType | 68| NULL | 393794 | Using index condition |
既然都已经使用了索引，为什么还要这么久，我看了下，返回结果集有197365条。这么多，看来是因为网络传输的问题了。好的，重新执行一开始的问题SQL：

SELECT
	brand_name,
	item_number,
	sum(monthly_sales_num) AS monthly_sales_num
FROM
	dm_ecp_goods_sales
WHERE
	ymd=20161025
AND shop_type IN ('TB_TMALL', 'TB_JISHI')
GROUP BY
	brand_name,
	item_number
ORDER BY
	monthly_sales_num DESC
LIMIT 20;

用时1.872s，哈哈，立马就可以接受了（因为是统计型数据，如果前端再加上结果缓存，这样的性能完全可以接受）。因为减少了网络传输，所以内部聚合也是小case。

这里，由于使用的where查询和group by字段没有重复，很难满足group by的前缀索引使用，这里对这个机制不够熟悉，暂时没有考虑到如何优化。

###归纳
当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：
1.执行where xx对全表数据做筛选，返回第1个结果集。
2.针对第1个结果集使用group by分组，返回第2个结果集。
3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
4.针对第3个结集执行having xx进行筛选，返回第4个结果集。
5.针对第4个结果集排序