mysql - 加速 MySQL 数据库多重连接的技巧

Question

我有一个去年的天气值数据库(每分钟 1 行)，其中包含超过 1/2 百万行。我编写了一系列 SQL 查询，这些查询查找每天某一列的最大值或最小值等，然后查找它发生的时间。然后，数据被写入临时表，同时找到下一个变量的相同信息。所有变量都已写入临时表，查询将连接这些表以提供每日高点和低点表。运行大约需要 4 分钟。有没有一种方法可以提高我的编码效率？到目前为止，这是我能想到的最好的办法，因为我没有接受过这方面的培训。谢谢!丹尼尔

CREATE TEMPORARY TABLE IF NOT EXISTS table1 AS (SELECT a.date, a.time AS max_temp_time, a.temperature as max_temp
FROM Live a
INNER JOIN (

SELECT DATE, MAX( temperature ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.temperature = aMax.MTotal
GROUP BY DATE

                                         );

Drop table if exists records.Daily_Records;                                             
CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT a.date as mindewdate, a.time AS min_dewpoint_time, a.dew_point_temperature as min_dew
FROM Live a
INNER JOIN (

SELECT DATE, MIN( dew_point_temperature ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.dew_point_temperature = aMax.MTotal
GROUP BY mindewdate

                                         );


CREATE TEMPORARY TABLE IF NOT EXISTS table3 AS (SELECT a.date as maxdewdate, a.time AS max_dewpoint_time, a.dew_point_temperature as max_dew
FROM Live a
INNER JOIN (

SELECT DATE, MAX( dew_point_temperature ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.dew_point_temperature = aMax.MTotal
GROUP BY maxdewdate

                                         );


CREATE TEMPORARY TABLE IF NOT EXISTS table4 AS (SELECT a.date as mintempdate, a.time AS min_temp_time, a.temperature as min_temp
FROM Live a
INNER JOIN (

SELECT DATE, MIN( temperature ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.temperature = aMax.MTotal
GROUP BY mintempdate

                                         );



CREATE TEMPORARY TABLE IF NOT EXISTS table5 AS (SELECT a.date as wchilldate, a.time AS windchill_time, a.windchill
FROM Live a
INNER JOIN (

SELECT DATE, MIN( windchill ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.windchill = aMax.MTotal
GROUP BY wchilldate

                                         );


CREATE TEMPORARY TABLE IF NOT EXISTS table6 AS (SELECT a.date as heat_index_date, a.time AS heat_index_time, a.heat_index
FROM Live a
INNER JOIN (

SELECT DATE, MAX( heat_index ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.heat_index = aMax.MTotal
GROUP BY heat_index_date

                                         );


CREATE TEMPORARY TABLE IF NOT EXISTS table7 AS (SELECT a.date as max_outdoor_humidity_date, a.time AS max_hum_time, a.outdoor_humidity as max_hum
FROM Live a
INNER JOIN (

SELECT DATE, MAX( outdoor_humidity ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.outdoor_humidity = aMax.MTotal
GROUP BY max_outdoor_humidity_date

                                         ); 


CREATE TEMPORARY TABLE IF NOT EXISTS table8 AS (SELECT a.date as        min_outdoor_humidity_date, a.time AS min_hum_time, a.outdoor_humidity as min_hum
FROM Live a
INNER JOIN (

SELECT DATE, MIN( outdoor_humidity ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.outdoor_humidity = aMax.MTotal
GROUP BY min_outdoor_humidity_date

                                         );

CREATE TEMPORARY TABLE IF NOT EXISTS table9 AS (SELECT a.date as max_rain_rate_date, a.time AS rainrate_time, a.rain_rate as rainrate
FROM Live a
INNER JOIN (

SELECT DATE, MAX( rain_rate ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.rain_rate = aMax.MTotal
GROUP BY max_rain_rate_date

                                         ); 


CREATE TEMPORARY TABLE IF NOT EXISTS table10 AS (SELECT a.date as max_daily_rainfall_date, a.time AS max_daily_rainfall_time, a.daily_rainfall as rainfall
FROM Live a
INNER JOIN (

SELECT DATE, MAX( daily_rainfall ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.daily_rainfall = aMax.MTotal
GROUP BY max_daily_rainfall_date

                                         );  


CREATE TEMPORARY TABLE IF NOT EXISTS table11 AS (SELECT a.date as max_gust_windspeed_date, a.time AS gust_time, a.gust_windspeed as gust
FROM Live a
INNER JOIN (

SELECT DATE, MAX( gust_windspeed ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.gust_windspeed = aMax.MTotal
GROUP BY max_gust_windspeed_date

                                         ); 


CREATE TEMPORARY TABLE IF NOT EXISTS table12 AS (SELECT a.date as max_average_windspeed_date, a.time AS wind_time, a.average_windspeed as wind
FROM Live a
INNER JOIN (

SELECT DATE, MAX( average_windspeed ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.average_windspeed = aMax.MTotal
GROUP BY max_average_windspeed_date

                                         );     

CREATE TEMPORARY TABLE IF NOT EXISTS table13 AS (SELECT a.date as max_barometer_date, a.time AS max_barometer_time, a.barometer as max_barometer
FROM Live a
INNER JOIN (

SELECT DATE, MAX( barometer ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.barometer = aMax.MTotal
GROUP BY max_barometer_date

                                         );  


CREATE TEMPORARY TABLE IF NOT EXISTS table14 AS (SELECT a.date as min_barometer_date, a.time AS min_barometer_time, a.barometer as min_barometer
FROM Live a
INNER JOIN (

SELECT DATE, MIN( barometer ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.barometer = aMax.MTotal
GROUP BY min_barometer_date

                                         );   


CREATE TEMPORARY TABLE IF NOT EXISTS table15 AS (SELECT a.date as max_actual_solar_reading_date, a.time AS max_solar_time, a.actual_solar_reading as solar
FROM Live a
INNER JOIN (

SELECT DATE, MAX( actual_solar_reading ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.actual_solar_reading = aMax.MTotal
GROUP BY max_actual_solar_reading_date

                                         );            



CREATE TEMPORARY TABLE IF NOT EXISTS table16 AS (SELECT a.date as davis_vp_uv_date, a.time AS max_uv_time, a.davis_vp_uv as uv
FROM Live a
INNER JOIN (

SELECT DATE, MAX( davis_vp_uv ) MTotal
FROM Live
GROUP BY DATE
)aMax ON a.date = aMax.date
AND a.davis_vp_uv = aMax.MTotal
GROUP BY davis_vp_uv_date

                                         ); 




DROP TABLE IF EXISTS records.Daily_Records;                                            
CREATE TABLE records.Daily_Records AS (SELECT date, max_temp, max_temp_time,min_temp,min_temp_time, windchill,windchill_time, max_dew, max_dewpoint_time, min_dew,min_dewpoint_time,heat_index,heat_index_time,max_hum,max_hum_time,min_hum,min_hum_time,rainrate,rainrate_time,rainfall,max_daily_rainfall_time,gust,gust_time,wind,wind_time,max_barometer,max_barometer_time,min_barometer,min_barometer_time,solar,max_solar_time,uv,max_uv_time
FROM `table1`                                       
JOIN `table2` on table2.mindewdate = table1.date
JOIN `table3` on table3.maxdewdate = table1.date
JOIN `table4` on table4.mintempdate = table1.date
JOIN `table5` on table5.wchilldate = table1.date
JOIN `table6` on table6.heat_index_date = table1.date
JOIN `table7` on table7.max_outdoor_humidity_date = table1.date
JOIN `table8` on table8.min_outdoor_humidity_date = table1.date
JOIN `table10` on table10.max_daily_rainfall_date = table1.date
JOIN `table11` on table11.max_gust_windspeed_date = table1.date
JOIN `table12` on table12.max_average_windspeed_date = table1.date
JOIN `table13` on table13.max_barometer_date = table1.date
JOIN `table14` on table14.min_barometer_date = table1.date
JOIN `table15` on table15.max_actual_solar_reading_date = table1.date
JOIN `table16` on table16.davis_vp_uv_date = table1.date);

Answer 1

检查Does MySQL index foreign key columns automatically?

使用InnoDB引擎，所有的外键都将得到正确的支持和索引，从而带来重要的速度提升。