MySql:查询优化(多个左连接)

Question

当 where 子句 包含约 50 000 个 id 时，以下查询需要一个多小时的时间来执行...同样的查询在有几千个 id 时运行得非常快...

Select '300_new_files_2012-02-29.xml' as Filelist,
Sum(coalesce(temp_decmetadata.glare_none, 0)) as glare_none,
Sum(coalesce(temp_decmetadata.glare_sun, 0)) as glare_sun,
Sum(coalesce(temp_decmetadata.precipitation_clear, 0)) as precipitation_clear,
Sum(coalesce(temp_decmetadata.precipitation_heavyFog, 0)) as precipitation_heavyFog,
Sum(coalesce(temp_decmetadata.precipitation_mediumRain, 0)) as precipitation_mediumRain,
Sum(coalesce(temp_decmetadata.spray_clear, 0)) as spray_clear,
Sum(coalesce(temp_decmetadata.spray_heavySpray, 0)) as spray_heavySpray,
Sum(coalesce(temp_kafasccmobjectdata.laneAssignment_farRight, 0)) as laneAssignment_farRight,
Sum(coalesce(temp_kafasccmobjectdata.laneAssignment_leftAdjacent, 0)) as laneAssignment_leftAdjacent,
Sum(coalesce(temp_kafasccmobjectdata.type_motorbike, 0)) as type_motorbike,
Sum(coalesce(temp_kafasccmobjectdata.type_truck, 0)) as type_truck,
Sum(coalesce(temp_kafasccmobjectdata.type_van, 0)) as type_van,
Sum(coalesce(temp_visionmetadata.countryCode_SK, 0)) as countryCode_SK,
Sum(coalesce(temp_visionmetadata.countryCode_SM, 0)) as countryCode_SM,
Sum(coalesce(temp_visionmetadata.countryCode_TR, 0)) as countryCode_TR,
Sum(coalesce(temp_visionmetadata.countryCode_UA, 0)) as countryCode_UA,
Sum(coalesce(temp_visionmetadata.countryCode_US, 0)) as countryCode_US,
Sum(coalesce(temp_visionmetadata.countryCode_ZA, 0)) as countryCode_ZA,
Sum(coalesce(temp_visionmetadata.local_illumination_cast_shadows, 0)) as local_illumination_cast_shadows,
Sum(coalesce(temp_visionmetadata.local_illumination_oncoming, 0)) as local_illumination_oncoming,
Sum(coalesce(temp_visionmetadata.roadCharacteristics_flat, 0)) as roadCharacteristics_flat,
Sum(coalesce(temp_visionmetadata.roadCharacteristics_hilly, 0)) as roadCharacteristics_hilly,
Sum(coalesce(temp_visionmetadata.roadLocation_city, 0)) as roadLocation_city,
Sum(coalesce(temp_visionmetadata.roadLocation_cityResidential, 0)) as roadLocation_cityResidential,
Sum(coalesce(temp_visionmetadata.sky_overcast, 0)) as sky_overcast,
Sum(coalesce(temp_visionmetadata.sky_partlyCloudy, 0)) as sky_partlyCloudy,
Sum(coalesce(temp_visionmetadata.timeOfDay_day, 0)) as timeOfDay_day,
Sum(coalesce(temp_visionmetadata.timeOfDay_Sunny_daytime, 0)) as timeOfDay_Sunny_daytime
from files
left join 
(
 Select id,
 count(case when glare like '%none%' then 1 end) as glare_none,
 count(case when glare like '%sun%' then 1 end) as glare_sun,
 count(case when precipitation like '%clear%' then 1 end) as precipitation_clear,
 count(case when precipitation like '%heavyFog%' then 1 end) as precipitation_heavyFog,
 count(case when spray like '%clear%' then 1 end) as spray_clear,
 count(case when spray like '%heavySpray%' then 1 end) as spray_heavySpray,
 from decmetadata
 where id in (1264853,1264854,1264855,1264856,1264857,.....)
 group by id 
) temp_decmetadata on temp_decmetadata.id = files.id 
left join 
(
 Select id,
 count(case when laneAssignment like '%farRight%' then 1 end) as laneAssignment_farRight,
 count(case when laneAssignment like '%leftAdjacent%' then 1 end) as laneAssignment_leftAdjacent,
 count(case when type like '%car%' then 1 end) as type_car,
 count(case when type like '%motorbike%' then 1 end) as type_motorbike,
 count(case when type like '%truck%' then 1 end) as type_truck,
 count(case when type like '%van%' then 1 end) as type_van
 from kafasccmobjectdata
 where id in (1264853,1264854,1264855,1264856,1264857,.....)
 group by id 
) temp_kafasccmobjectdata on temp_kafasccmobjectdata.id = files.id 
left join 
(
 Select id,
 count(case when countryCode like '%SK%' then 1 end) as countryCode_SK,
 count(case when countryCode like '%SM%' then 1 end) as countryCode_SM,
 count(case when countryCode like '%TR%' then 1 end) as countryCode_TR,
 count(case when countryCode like '%UA%' then 1 end) as countryCode_UA,
 count(case when countryCode like '%US%' then 1 end) as countryCode_US,
 count(case when countryCode like '%ZA%' then 1 end) as countryCode_ZA,
 count(case when sky like '%lowSun%' then 1 end) as local_illumination_cast_shadows,
 count(case when comments like '%oncomingevent%' then 1 end) as local_illumination_oncoming,
 count(case when roadCharacteristics like '%flat%' then 1 end) as roadCharacteristics_flat,
 count(case when roadCharacteristics like '%hilly%' then 1 end) as roadCharacteristics_hilly,
 count(case when roadLocation like '%city%' then 1 end) as roadLocation_city,
 count(case when roadLocation like '%cityResidential%' then 1 end) as roadLocation_cityResidential,
 count(case when roadLocation like '%countryRural%' then 1 end) as roadLocation_countryRural,
 count(case when sky like '%overcast%' then 1 end) as sky_overcast,
 count(case when sky like '%partlyCloudy%' then 1 end) as sky_partlyCloudy,
 count(case when timeOfDay like '%day%' then 1 end) as timeOfDay_day,
 count(case when sky like '%clear%' and timeofday ='day' then 1 end) as timeOfDay_Sunny_daytime
 from visionmetadata
 where id in (1264853,1264854,1264855,1264856,1264857,....)
 group by id 
) temp_visionmetadata on temp_visionmetadata.id = files.id 
where files.id in (1264853,1264854,1264855,1264856,1264857,....)

附上“Explain Select ...”的结果:

任何想法/建议/更正？？？

任何帮助将不胜感激!!!

Answer 1

是的...

首先:如何创建一个结构来保存您想要的所有 ID，而不是将 50,000 个数字写入查询 3 次或更多次？

第二:尝试消除代码检查中的前导 %。 (并在这些列上创建良好的索引)

第三:我几乎从不建议这样做 - 但可能会预先计算总数并将它们非规范化存储。