我有一个包含每天温度的表(大表)和一个包含期间开始和结束日期的表(小表)。现在想知道每个时段的平均气温，但是查询时间比较长。可以改进吗？

注意: 升级到 5.6.19-1~exp1ubuntu2 版本后，较长的响应时间消失，可能是 5.6.8 之前的 MySQL 版本中的错误引起的(参见 Quassnoi 的评论)

用随机数据重建日表和周期表:

create table days (
  day int not null auto_increment primary key,
  temperature float not null);

insert into days values(null,rand()),(null,rand()),
  (null,rand()),(null,rand()),(null,rand()),(null,rand()),
  (null,rand()),(null,rand()); # 8 rows

insert into days select null, d1.temperature
  from days d1, days d2, days d3, days d4,
  days d5, days d6, days d7; # 2M rows

create table periods(id int not null auto_increment primary key,
  first int not null,
  last int not null,
  index(first) using btree,
  index(last) using btree,
  index(first,last) using btree);

# add 10 periods of 1-11 days each
insert into periods(first,last)
  select floor(rand(day)*2000000), floor(rand(day)*2000000 + rand()*10)
  from days limit 10;

列出每个时段的全天温度没有问题(在 1 毫秒内返回):

select id, temperature
  from periods join days on day >= first and day <= last;

现在，使用 GROUP BY，它实际上很慢(~1750 毫秒)

# ALT1
select id, avg(temperature)
  from periods join days on day >= first and day <= last group by id;

将 <= 和="">= 替换为 BETWEEN 会略微加快速度(~1600 毫秒):

# ALT2
select id, avg(temperature)
  from periods join days on day between first and last group by id;

事实证明，单个周期的结果会立即返回(1ms):

select id, (select avg(temperature)
  from days where day >= first and day <= last) from periods
  where id=1;

但是，如果没有 WHERE，它需要多达 4200 毫秒，平均每个周期为 420 毫秒!

# ALT3
select id,
  (select avg(temperature) from days where day >= first and day <= last)
  from periods;

是什么导致查询如此缓慢——甚至(很多)比获取单个时间段的结果慢 10 倍以上，尽管 periods 表只有 10 行？有什么方法可以优化这个查询吗？

编辑:一些更多信息:

mysql> select @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 5.5.41-0ubuntu0.14.04.1 |
+-------------------------+

# ALT1
mysql> explain select id, avg(temperature) from periods join days on day >= first and day <= last group by id;
+----+-------------+---------+-------+--------------------+---------+---------+------+---------+----------------------------------------------+
| id | select_type | table   | type  | possible_keys      | key     | key_len | ref  | rows    | Extra                                        |
+----+-------------+---------+-------+--------------------+---------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | periods | index | first,last,first_2 | first_2 | 8       | NULL |      10 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | days    | ALL   | PRIMARY            | NULL    | NULL    | NULL | 2097596 | Using where; Using join buffer               |
+----+-------------+---------+-------+--------------------+---------+---------+------+---------+----------------------------------------------+

# ALT1 without GROUP BY
mysql> explain select id, temperature from periods join days on day >= first and day <= last;
+----+-------------+---------+-------+--------------------+---------+---------+------+---------+------------------------------------------------+
| id | select_type | table   | type  | possible_keys      | key     | key_len | ref  | rows    | Extra                                          |
+----+-------------+---------+-------+--------------------+---------+---------+------+---------+------------------------------------------------+
|  1 | SIMPLE      | periods | index | first,last,first_2 | first_2 | 8       | NULL |      10 | Using index                                    |
|  1 | SIMPLE      | days    | ALL   | PRIMARY            | NULL    | NULL    | NULL | 2097596 | Range checked for each record (index map: 0x1) |
+----+-------------+---------+-------+--------------------+---------+---------+------+---------+------------------------------------------------+

# ALT2
mysql> explain select id, avg(temperature) from periods join days on day between first and last group by id;
+----+-------------+---------+-------+--------------------+---------+---------+------+---------+----------------------------------------------+
| id | select_type | table   | type  | possible_keys      | key     | key_len | ref  | rows    | Extra                                        |
+----+-------------+---------+-------+--------------------+---------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | periods | index | first,last,first_2 | first_2 | 8       | NULL |      10 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | days    | ALL   | PRIMARY            | NULL    | NULL    | NULL | 2097596 | Using where; Using join buffer               |
+----+-------------+---------+-------+--------------------+---------+---------+------+---------+----------------------------------------------+

# ALT3
mysql> explain select id, (select avg(temperature) from days where day >= first and day <= last) from periods;
+----+--------------------+---------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type        | table   | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+--------------------+---------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | PRIMARY            | periods | index | NULL          | first_2 | 8       | NULL |      10 | Using index |
|  2 | DEPENDENT SUBQUERY | days    | ALL   | PRIMARY       | NULL    | NULL    | NULL | 2097596 | Using where |
+----+--------------------+---------+-------+---------------+---------+---------+------+---------+-------------+

# ALT3 with where
mysql> explain select id, (select avg(temperature) from days where day >= first and day <= last) from periods where id = 1;
+----+--------------------+---------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type        | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+--------------------+---------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY            | periods | const | PRIMARY       | PRIMARY | 4       | const |    1 |             |
|  2 | DEPENDENT SUBQUERY | days    | range | PRIMARY       | PRIMARY | 4       | NULL  |   10 | Using where |
+----+--------------------+---------+-------+---------------+---------+---------+-------+------+-------------+

EDIT2: FROM 中嵌套查询的执行计划，如 Lennart 所建议的(查询执行时间 3 毫秒)

mysql> explain select id,avg(temperature) from (select id,temperature from periods join days on day between first and last) as t group by id;
+----+-------------+------------+-------+--------------------+---------+---------+------+----------+------------------------------------------------+
| id | select_type | table      | type  | possible_keys      | key     | key_len | ref  | rows     | Extra                                          |
+----+-------------+------------+-------+--------------------+---------+---------+------+----------+------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL               | NULL    | NULL    | NULL |       50 | Using temporary; Using filesort                |
|  2 | DERIVED     | periods    | index | first,last,first_2 | first_2 | 8       | NULL |       10 | Using index                                    |
|  2 | DERIVED     | days       | range | PRIMARY,day        | PRIMARY | 4       | NULL |        5 | Range checked for each record (index map: 0x3) |
+----+-------------+------------+-------+--------------------+---------+---------+------+----------+------------------------------------------------+

最佳答案

这是一个丑陋的把戏，因为:

select id, temperature 
from periods join days 
    on day between first and last;

很快，我们可以尝试激发优化器首先对其进行评估。单独使用子查询是不够的:

select id, avg(temperature) 
from (
    select id, temperature 
    from periods 
    join days 
        on day between first and last
) as t 
group by id;
[...]
10 rows in set (1.67 sec)

但是，在子查询中调用非确定性函数似乎可以解决问题:

select id, avg(temperature) 
from (
    select id, temperature, rand() 
    from periods 
    join days 
        on day between first and last
) as t 
group by id;
[...]
10 rows in set (0.00 sec)

除非关键和必要，否则我会远离这些技巧。随着优化器变得更好(也许是下一个修复)，它可能会跳过对 rand() 的调用，突然间你的旧计划和性能又恢复了。

如果您确实使用了此类技巧，请确保在代码中仔细记录它们，以便在不再需要时可以清除它们。

MariaDB [test]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.0.20-MariaDB |
+-----------------+
1 row in set (0.00 sec)

explain select id, avg(temperature) from periods join days on day between first and last group by id;
| id   | select_type | table   | type  | possible_keys      | key     | key_len | ref  | rows    | Extra                                           |
|    1 | SIMPLE      | periods | index | first,last,first_2 | first_2 | 8       | NULL |      10 | Using index; Using temporary; Using filesort    |
|    1 | SIMPLE      | days    | ALL   | PRIMARY            | NULL    | NULL    | NULL | 2094315 | Using where; Using join buffer (flat, BNL join) |

explain select id, avg(temperature) from (select id, temperature from periods join days on day between first and last) as t group by id;
| id   | select_type | table   | type  | possible_keys      | key     | key_len | ref  | rows    | extra                                           |
|    1 | SIMPLE      | periods | index | first,last,first_2 | first_2 | 8       | NULL |      10 | Using index; Using temporary; Using filesort    |
|    1 | SIMPLE      | days    | ALL   | PRIMARY            | NULL    | NULL    | NULL | 2094315 | Using where; Using join buffer (flat, BNL join) |


explain select id, avg(temperature) from (select id, temperature, rand() from periods join days on day between first and last) as t group by id;
| id   | select_type | table      | type  | possible_keys      | key     | key_len | ref  | rows    | Extra                                          |
|    1 | PRIMARY     | <derived2> | ALL   | NULL               | NULL    | NULL    | NULL |       2 | Using temporary; Using filesort                |
|    2 | DERIVED     | periods    | index | first,last,first_2 | first_2 | 8       | NULL |      10 | Using index                                    |
|    2 | DERIVED     | days       | ALL   | PRIMARY            | NULL    | NULL    | NULL | 2094315 | Range checked for each record (index map: 0x1) |

关于mysql - 使用 group by 优化 MySQL 范围查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31144439/