mysql - 使用长日期范围条件提高 mysql 查询速度

Question

我有两个表:table_car 和 table_order:

我想要这样的表格:

table_order 有组合键 (id_type,date)

SELECT "September" AS `Range`,
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_items',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_items',
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_amount',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_amount' 
FROM table_order 
WHERE id_type IN(1,2,3,4) AND `date` >= "2015-09-01" AND `date` <= "2015-09-30"
UNION
SELECT 
"August" AS `Range`,
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_items',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_items',
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_amount',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_amount' 
FROM table_order 
WHERE id_type IN(1,2,3,4) AND `date` >= "2015-08-01" AND `date` <= "2015-08-31"

有没有其他查询速度更快的方法？假设我有一个包含十亿条记录的表，并且可以在条件下执行长日期范围查询，例如 date >= "2015-01-01"和 date <= "2016-01-01"。
您可以在此处找到示例:http://sqlfiddle.com/#!9/1787e/1

Answer 1

使用GROUP BY:

SELECT date_format(`date`, '%M') AS `Range`,
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_items',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_items',
SUM(IF(id_type IN(1,2), items,0)) AS 'RedandBlue_amount',
SUM(IF(id_type IN(3,4), items,0)) AS 'YellowandGreen_amount' 
FROM `order`
WHERE id_type IN(1,2,3,4) AND `date` >= "2015-08-01" AND `date` <= "2015-09-30"
group by year(`date`), month(`date`)
order by year(`date`) desc, month(`date`) desc;

http://sqlfiddle.com/#!9/d76254/2

或者，如果您总是只去两个月，请向工会查询。但是如果范围可以大很多那么我建议使用修改后的，这样写起来更简单，如果你只选择表格中相对较小的部分，分组应该不会成为性能瓶颈。

GROUP BY 如果将年-月存储在单独的列中，变体可以变得更快，因此不需要函数，并相应地修改索引。您的变体无需修改即可很好地使用索引。

您的示例(在 fiddle 上)没有显示索引使用情况，因为表中的行太少，查询无论如何都必须读取所有行。但是你可以添加一个 covering index (id_type, date, items)(只需扩展您已有的就足够了，无需保留较短的)这有助于两个版本的查询。

只需检查说明:

• http://sqlfiddle.com/#!9/d76254/1
• http://sqlfiddle.com/#!9/d76254/2