Mysql Group加入优化问题

Question

我正在尝试优化此查询，它从 building_rent_prices 和 building_weather 返回多行，然后将它们分组并计算它们字段的平均值。到目前为止，这些表都在一百万行以下，但需要几秒钟，有谁知道我如何从复合索引优化它或重写查询？我假设它应该能够成为 100 毫秒或更快的查询，但到目前为止它似乎不能

SELECT b.*
     , AVG(r.rent)
     , AVG(w.high_temp)
  FROM buildings b
  LEFT 
  JOIN building_rent_prices r
    ON r.building_id = b.building_id 
  LEFT 
  JOIN building_weather w
    ON w.building_id = b.building_id 
 WHERE w.date BETWEEN CURDATE() AND CURDATE + INTERVAL 4 DAY
   AND r.date BETWEEN CURDATE() AND CURDATE + INTERVAL 10 day
 GROUP  
    BY b.building_id
 ORDER  
    BY AVG(r.rent) / b.square_feet DESC
 LIMIT 10;  

解释如下:

1 个简单的 building_rent_prices 范围

1 个简单的建筑物 eq_ref

1 个简单的建筑天气引用

在哪里使用；使用索引；使用临时的；使用文件排序

在哪里使用

在哪里使用；使用索引

我正在创建表中处理一些测试数据

CREATE TABLE building(
building_id INT PRIMARY KEY AUTO_INCREMENT, 
name VARCHAR(255),
square_feet INT
);

CREATE TABLE building_weather(
building_weather_id INT PRIMARY KEY AUTO_INCREMENT, 
building_id INT,
weather_date DATE,
high_temp INT
);

CREATE TABLE building_rates(
building_rate_id INT PRIMARY KEY AUTO_INCREMENT, 
building_id INT,
weather_date DATE,
rate double
);

ALTER TABLE building_rates INDEX(building_id);
ALTER TABLE buildings INDEX(building_id);
ALTER TABLE building_weather INDEX(building_id);

根据 DRapp 没有索引的答案，这似乎在 1 秒内完成(我仍然需要测试它是否有效)

select 
  B.*, 
  BRP.avgRent, 
  BW.avgTemp
   from 
   ( select building_id,
            AVG( rent ) avgRent
         from
            building_rent_prices
         where
            date BETWEEN CURDATE() AND CURDATE() + 10
         group by
            building_id
         order by
            building_id ) BRP
     JOIN buildings B
        on BRP.building_id = B.building_id
     left join ( select building_id,
                        AVG( hi_temp ) avgTemp
                     from building_weather 
                     where date BETWEEN CURDATE() AND CURDATE() + 10
                     group by building_id) BW
        on BRP.building_id =  BW.building_id
   GROUP BY BRP.building_id
 ORDER BY BRP.avgRent / 1 DESC
   LIMIT 10;

Answer 1

让我们详细看一下这个查询。您想要报告每栋建筑的两种不同类型的平均值。您需要在单独的子查询中计算它们。否则，您将遇到笛卡尔组合爆炸。

一个是十一天的平均租金价格。您使用此子查询获取该数据:

          SELECT building_id, AVG(rent) rent
            FROM building_rent_prices
           WHERE date BETWEEN CURDATE() AND CURDATE() + INTERVAL 10 DAY
           GROUP BY building_id

这个子查询可以通过 compound covering index 来优化在 building_rent_prices 上，由 (date, building_id, rent) 组成。

接下来是五天的平均气温。

          SELECT building_id, AVG(high_temp) high_temp
            FROM building_weather
           WHERE date BETWEEN CURDATE() AND CURDATE() + INTERVAL 4 DAY
           GROUP BY building_id

这可以通过 building_weather 的复合覆盖索引进行优化，该索引由 (date, building_id, high_temp) 组成。

最后，您需要将这两个子查询连接到您的buildings 表以生成最终结果集。

SELECT buildings.*, a.rent, b.high_temp
  FROM buildings
  LEFT JOIN (
          SELECT building_id, AVG(rent) rent
            FROM building_rent_prices
           WHERE date BETWEEN CURDATE() AND CURDATE() + INTERVAL 10 DAY
           GROUP BY building_id
       ) AS a ON buildings.building_id = a.building_id
  LEFT JOIN (
          SELECT building_id, AVG(high_temp) high_temp
            FROM building_weather
           WHERE date BETWEEN CURDATE() AND CURDATE() + INTERVAL 4 DAY
           GROUP BY building_id
       ) AS b ON buildings.building_id = b.building_id
 ORDER BY a.rent / buildings.square_feet DESC
 LIMIT 10

一旦优化了两个子查询，这个子查询就不需要任何东西，除了 building_id 主键。

总而言之，要加快此查询的速度，请创建 building_rent_prices 和 building_weather 查询中提到的两个复合索引。