一次选择中的 MySQL 查询选择性能和两个日期范围

Question

我有一张表来存储商店中的人数，如下所示:

id primary int 
id_store int 
date date 
time_begin time 
time_end time 
girl int 
boy int 
man int 
deleted int 
KEY id_store
KEY date 
KEY time_begin

数据:

id  store  date        time_begin  time_end   girl  boy  man  deleted
1   10     2015-01-01  09:00:00    09:05:00   5     7    8    0 
2   10     2015-01-01  09:05:00    09:10:00   3     2    1    0 
3   10     2015-01-01  09:10:00    09:15:00   5     4    7    0  
4   10     2015-01-01  09:15:00    09:20:00   5     3    8    0 
5   20     2015-01-01  09:00:00    09:05:00   7     8    2    0 
6   20     2015-01-01  09:00:00    09:05:00   5     7    8    0 
7   20     2015-01-01  09:05:00    09:10:00   3     2    1    0 
8   20     2015-01-01  09:10:00    09:15:00   5     4    7    0 
9   20     2015-01-01  09:15:00    09:20:00   5     3    8    0 

可以有1000家店铺，每5分钟会有1条记录保存每个店铺的girl/boy/man。该表可以包含超过 10 亿条记录。

我想总结一些商店在日期/时间范围内的所有人口统计数据，这是我的查询:

SELECT id_store, SUM(girl) girl ,SUM(boy) boy,SUM(man) man 
FROM report_demography 
WHERE 
date between "2015-08-01" and "2015-08-31" 
and time_begin >= "09:00:00" AND time_begin < "22:00:00" 
AND deleted = 0 AND FIND_IN_SET(id_store,'10,20,30,40,50')
GROUP BY id_store

运行此查询需要 2.51 分钟(此表中的总行数为 900K)。无论如何改进这个查询？

还有一件事:我想在此查询中结合上个月的人口统计结果，我使用 union all 但似乎这不是最佳解决方案。

这是解释选择: (id_station 与 id_store 相同)

最好的问候

Answer 1

这是我试过的两个例子:

CREATE TABLE report_demography (
id int(11) NOT NULL AUTO_INCREMENT,
id_station int(11) NOT NULL,
datee date NOT NULL,
girl int(11) NOT NULL,
boy int(11) NOT NULL,
man int(11) NOT NULL,
deleted int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
KEY id_station (id_station),
KEY datee (datee),
KEY time_begin (time_begin),
KEY deleted (deleted),
KEY main_condition (deleted,id_station,datee,time_begin)
) ENGINE=InnoDB AUTO_INCREMENT=969628 DEFAULT CHARSET=utf8;

我试过两种情况:
- 使用完整条件:

SELECT id_station, SUM(girl) girl ,SUM(boy) boy,SUM(man) man
FROM report_demography
WHERE datee BETWEEN "2014-01-01" AND "2015-07-01"
AND time_begin >= "09:00:00" AND time_begin < "22:00:00"
AND deleted = 0
AND id_station IN (668,782,672,670,139,878,671,785,736,737,740,787,138,141,789,669,835,783,780,781,788,784,809,786)
GROUP BY id_station

大约 0.7 秒 => 最好的情况
解释:

- 不使用 id_station(选择所有商店):

SELECT id_station, SUM(girl) girl ,SUM(boy) boy,SUM(man) man
FROM report_demography1
WHERE datee BETWEEN "2014-01-01" AND "2015-07-01"
AND time_begin >= "09:00:00" AND time_begin < "22:00:00"
AND deleted = 0
GROUP BY id_station

大约是 03:17:99 秒 => 更糟的情况
解释选择:

似乎案例 2 在额外列中使用 where 而不是索引，在这种情况下如何使用正确的索引？