mysql - 子查询和大表。如何提高速度？

Question

我对 MySQL 并不陌生，但我绝对对此感到不知所措。

我想根据日期和大气层显示巴拿马和伯利兹的气温差异表。该查询应该根据日期和大气层匹配巴拿马和伯利兹的数据，并返回前 30 个差异，按差异程度排序。

但是，它非常慢(超过 30 秒)，因此超时。我为此数据集编写的其他一些查询也非常慢(大约 26 秒)。但如果我只运行子查询，它们只需要 1.7 秒左右。我应该注意到，下面的两个表都超过 440,000 行长，但我认为这不是很大。问题可能是我连接表的方式或创建子查询的方式。

这是我的设置:(这是导出表中的 SQL。我省略了一些列)

/**The table for Panama weather data */ 

CREATE TABLE `panama_weather_data` (
  `Id` varchar(40) NOT NULL,
  `OwmPackageId` varchar(30) NOT NULL,
  `Level` FLOAT DEFAULT NULL,
  `Dt` date DEFAULT NULL,
  `Temperature` float DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `panama_weather_data`
  ADD PRIMARY KEY (`Id`) USING BTREE;
COMMIT;

/**The table for Belize weather data*/

CREATE TABLE `belize_weather_data` (
  `Id` varchar(40) NOT NULL,
  `OwmPackageId` varchar(30) NOT NULL,
  `Level` FLOAT DEFAULT NULL,
  `Dt` date DEFAULT NULL,
  `Temperature` float DEFAULT NULL,
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `belize_weather_data`
  ADD PRIMARY KEY (`Id`) USING BTREE;
COMMIT;

/**Code to populate the tables here*/

这是我的查询:

SELECT ABS(PanamaTemperature-BelizeTemperature) AS TemperatureDif, 
PanamaAtmostphericLevel, PanamaTable.Dt
         FROM
         (SELECT CAST(panama_weather_data.Dt AS DATETIME) AS Dt,  
         panama_weather_data.Level AS PanamaAtmostphericLevel, 
         panama_weather_data.Temperature AS PanamaTemperature
         FROM panama_weather_data
         WHERE panama_weather_data.OwmPackageId = 'openweathermappkg19758'  ) 
         AS PanamaTable
         JOIN 
         (SELECT CAST(belize_weather_data.Dt AS DATETIME) AS Dt, 
         belize_weather_data.Level AS BelizeAtmosphericLevel, 
         belize_weather_data.Temperature AS BelizeTemperature
         FROM belize_weather_data
         WHERE belize_weather_data.OwmPackageId = 'openweathermappkg19758' ) 
         AS BelizeTable
         ON PanamaAtmostphericLevel = BelizeAtmosphericLevel
         AND PanamaTable.Dt = BelizeTable.Dt
         ORDER BY TemperatureDif
         LIMIT 30

我的问题确实是:有没有办法优化这个查询并使其不那么痛苦？

Answer 1

CAST(panama_weather_data.Dt AS DATETIME) AS Dt

为什么？ (这只会减慢查询速度)

Is there anyway to optimize this query

您向我们展示的 SQL SELECT 语句当然不会是我的起点。但是您没有告诉我们您将来打算如何查询数据。具体来说，您真的要在每次运行查询时检查所有数据吗？

您最大的胜利来自于不要将数据保存在单独的表中 - 它应该是两个数据集具有不同属性的单个表。

之后，下一个最大的改进将是将温差存储在表中并为其建立索引。